| # This Source Code Form is subject to the terms of the Mozilla Public |
| # License, v. 2.0. If a copy of the MPL was not distributed with this |
| # file, You can obtain one at http://mozilla.org/MPL/2.0/. |
| # |
| # This Source Code Form is "Incompatible With Secondary Licenses", as |
| # defined by the Mozilla Public License, v. 2.0. |
| |
| =head1 NAME |
| |
| Bugzilla::DB::Pg - Bugzilla database compatibility layer for PostgreSQL |
| |
| =head1 DESCRIPTION |
| |
| This module overrides methods of the Bugzilla::DB module with PostgreSQL |
| specific implementation. It is instantiated by the Bugzilla::DB module |
| and should never be used directly. |
| |
| For interface details see L<Bugzilla::DB> and L<DBI>. |
| |
| =cut |
| |
| package Bugzilla::DB::Pg; |
| |
| use 5.10.1; |
| use strict; |
| use warnings; |
| |
| use Bugzilla::Error; |
| use Bugzilla::Version; |
| use DBD::Pg; |
| |
| # This module extends the DB interface via inheritance |
| use parent qw(Bugzilla::DB); |
| |
| use constant BLOB_TYPE => { pg_type => DBD::Pg::PG_BYTEA }; |
| |
| sub new { |
| my ($class, $params) = @_; |
| my ($user, $pass, $host, $dbname, $port) = |
| @$params{qw(db_user db_pass db_host db_name db_port)}; |
| |
| # The default database name for PostgreSQL. We have |
| # to connect to SOME database, even if we have |
| # no $dbname parameter. |
| $dbname ||= 'template1'; |
| |
| # construct the DSN from the parameters we got |
| my $dsn = "dbi:Pg:dbname=$dbname"; |
| $dsn .= ";host=$host" if $host; |
| $dsn .= ";port=$port" if $port; |
| |
| # This stops Pg from printing out lots of "NOTICE" messages when |
| # creating tables. |
| $dsn .= ";options='-c client_min_messages=warning'"; |
| |
| my $attrs = { pg_enable_utf8 => Bugzilla->params->{'utf8'} }; |
| |
| my $self = $class->db_new({ dsn => $dsn, user => $user, |
| pass => $pass, attrs => $attrs }); |
| |
| # all class local variables stored in DBI derived class needs to have |
| # a prefix 'private_'. See DBI documentation. |
| $self->{private_bz_tables_locked} = ""; |
| # Needed by TheSchwartz |
| $self->{private_bz_dsn} = $dsn; |
| |
| bless ($self, $class); |
| |
| return $self; |
| } |
| |
| # if last_insert_id is supported on PostgreSQL by lowest DBI/DBD version |
| # supported by Bugzilla, this implementation can be removed. |
| sub bz_last_key { |
| my ($self, $table, $column) = @_; |
| |
| my $seq = $table . "_" . $column . "_seq"; |
| my ($last_insert_id) = $self->selectrow_array("SELECT CURRVAL('$seq')"); |
| |
| return $last_insert_id; |
| } |
| |
| sub sql_group_concat { |
| my ($self, $text, $separator, $sort, $order_by) = @_; |
| $sort = 1 if !defined $sort; |
| $separator = $self->quote(', ') if !defined $separator; |
| |
| # PostgreSQL 8.x doesn't support STRING_AGG |
| if (vers_cmp($self->bz_server_version, 9) < 0) { |
| my $sql = "ARRAY_ACCUM($text)"; |
| if ($sort) { |
| $sql = "ARRAY_SORT($sql)"; |
| } |
| return "ARRAY_TO_STRING($sql, $separator)"; |
| } |
| |
| if ($order_by && $text =~ /^DISTINCT\s*(.+)$/i) { |
| # Since Postgres (quite rightly) doesn't support "SELECT DISTINCT x |
| # ORDER BY y", we need to sort the list, and then get the unique |
| # values |
| return "ARRAY_TO_STRING(ANYARRAY_UNIQ(ARRAY_AGG($1 ORDER BY $order_by)), $separator)"; |
| } |
| |
| # Determine the ORDER BY clause (if any) |
| if ($order_by) { |
| $order_by = " ORDER BY $order_by"; |
| } |
| elsif ($sort) { |
| # We don't include the DISTINCT keyword in an order by |
| $text =~ /^(?:DISTINCT\s*)?(.+)$/i; |
| $order_by = " ORDER BY $1"; |
| } |
| |
| return "STRING_AGG(${text}::text, $separator${order_by}::text)" |
| } |
| |
| sub sql_istring { |
| my ($self, $string) = @_; |
| |
| return "LOWER(${string}::text)"; |
| } |
| |
| sub sql_position { |
| my ($self, $fragment, $text) = @_; |
| |
| return "POSITION(${fragment}::text IN ${text}::text)"; |
| } |
| |
| sub sql_like { |
| my ($self, $fragment, $column, $not) = @_; |
| $not //= ''; |
| |
| return "${column}::text $not LIKE " . $self->sql_like_escape($fragment) . " ESCAPE '|'"; |
| } |
| |
| sub sql_ilike { |
| my ($self, $fragment, $column, $not) = @_; |
| $not //= ''; |
| |
| return "${column}::text $not ILIKE " . $self->sql_like_escape($fragment) . " ESCAPE '|'"; |
| } |
| |
| sub sql_not_ilike { |
| return shift->sql_ilike(@_, 'NOT'); |
| } |
| |
| # Escapes any % or _ characters which are special in a LIKE match. |
| # Also performs a $dbh->quote to escape any quote characters. |
| sub sql_like_escape { |
| my ($self, $fragment) = @_; |
| |
| $fragment =~ s/\|/\|\|/g; # escape the escape character if it appears |
| $fragment =~ s/%/\|%/g; # percent and underscore are the special match |
| $fragment =~ s/_/\|_/g; # characters in SQL. |
| |
| return $self->quote("%$fragment%"); |
| } |
| |
| sub sql_regexp { |
| my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; |
| $real_pattern ||= $pattern; |
| |
| $self->bz_check_regexp($real_pattern) if !$nocheck; |
| |
| return "${expr}::text ~* $pattern"; |
| } |
| |
| sub sql_not_regexp { |
| my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; |
| $real_pattern ||= $pattern; |
| |
| $self->bz_check_regexp($real_pattern) if !$nocheck; |
| |
| return "${expr}::text !~* $pattern" |
| } |
| |
| sub sql_limit { |
| my ($self, $limit, $offset) = @_; |
| |
| if (defined($offset)) { |
| return "LIMIT $limit OFFSET $offset"; |
| } else { |
| return "LIMIT $limit"; |
| } |
| } |
| |
| sub sql_from_days { |
| my ($self, $days) = @_; |
| |
| return "TO_TIMESTAMP('$days', 'J')::date"; |
| } |
| |
| sub sql_to_days { |
| my ($self, $date) = @_; |
| |
| return "TO_CHAR(${date}::date, 'J')::int"; |
| } |
| |
| sub sql_date_format { |
| my ($self, $date, $format) = @_; |
| |
| $format = "%Y.%m.%d %H:%i:%s" if !$format; |
| |
| $format =~ s/\%Y/YYYY/g; |
| $format =~ s/\%y/YY/g; |
| $format =~ s/\%m/MM/g; |
| $format =~ s/\%d/DD/g; |
| $format =~ s/\%a/Dy/g; |
| $format =~ s/\%H/HH24/g; |
| $format =~ s/\%i/MI/g; |
| $format =~ s/\%s/SS/g; |
| |
| return "TO_CHAR($date, " . $self->quote($format) . ")"; |
| } |
| |
| sub sql_date_math { |
| my ($self, $date, $operator, $interval, $units) = @_; |
| |
| return "$date $operator $interval * INTERVAL '1 $units'"; |
| } |
| |
| sub sql_string_concat { |
| my ($self, @params) = @_; |
| |
| # Postgres 7.3 does not support concatenating of different types, so we |
| # need to cast both parameters to text. Version 7.4 seems to handle this |
| # properly, so when we stop support 7.3, this can be removed. |
| return '(CAST(' . join(' AS text) || CAST(', @params) . ' AS text))'; |
| } |
| |
| # Tell us whether or not a particular sequence exists in the DB. |
| sub bz_sequence_exists { |
| my ($self, $seq_name) = @_; |
| my $exists = $self->selectrow_array( |
| 'SELECT 1 FROM pg_statio_user_sequences WHERE relname = ?', |
| undef, $seq_name); |
| return $exists || 0; |
| } |
| |
| sub bz_explain { |
| my ($self, $sql) = @_; |
| my $explain = $self->selectcol_arrayref("EXPLAIN ANALYZE $sql"); |
| return join("\n", @$explain); |
| } |
| |
| ##################################################################### |
| # Custom Database Setup |
| ##################################################################### |
| |
| sub bz_check_server_version { |
| my $self = shift; |
| my ($db) = @_; |
| my $server_version = $self->SUPER::bz_check_server_version(@_); |
| my ($major_version, $minor_version) = $server_version =~ /^0*(\d+)\.0*(\d+)/; |
| # Pg 9.0 requires DBD::Pg 2.17.2 in order to properly read bytea values. |
| # Pg 9.2 requires DBD::Pg 2.19.3 as spclocation no longer exists. |
| if ($major_version >= 9) { |
| local $db->{dbd}->{version} = ($minor_version >= 2) ? '2.19.3' : '2.17.2'; |
| local $db->{name} = $db->{name} . " ${major_version}.$minor_version"; |
| Bugzilla::DB::_bz_check_dbd(@_); |
| } |
| } |
| |
| sub bz_setup_database { |
| my $self = shift; |
| $self->SUPER::bz_setup_database(@_); |
| |
| my ($has_plpgsql) = $self->selectrow_array("SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql'"); |
| $self->do('CREATE LANGUAGE plpgsql') unless $has_plpgsql; |
| |
| if (vers_cmp($self->bz_server_version, 9) < 0) { |
| # Custom Functions for Postgres 8 |
| my $function = 'array_accum'; |
| my $array_accum = $self->selectrow_array( |
| 'SELECT 1 FROM pg_proc WHERE proname = ?', undef, $function); |
| if (!$array_accum) { |
| print "Creating function $function...\n"; |
| $self->do("CREATE AGGREGATE array_accum ( |
| SFUNC = array_append, |
| BASETYPE = anyelement, |
| STYPE = anyarray, |
| INITCOND = '{}' |
| )"); |
| } |
| |
| $self->do(<<'END'); |
| CREATE OR REPLACE FUNCTION array_sort(ANYARRAY) |
| RETURNS ANYARRAY LANGUAGE SQL |
| IMMUTABLE STRICT |
| AS $$ |
| SELECT ARRAY( |
| SELECT $1[s.i] AS each_item |
| FROM |
| generate_series(array_lower($1,1), array_upper($1,1)) AS s(i) |
| ORDER BY each_item |
| ); |
| $$; |
| END |
| } |
| else { |
| # Custom functions for Postgres 9.0+ |
| |
| # -Copyright © 2013 Joshua D. Burns (JDBurnZ) and Message In Action LLC |
| # JDBurnZ: https://github.com/JDBurnZ |
| # Message In Action: https://www.messageinaction.com |
| # |
| #Permission is hereby granted, free of charge, to any person obtaining a copy of |
| #this software and associated documentation files (the "Software"), to deal in |
| #the Software without restriction, including without limitation the rights to |
| #use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of |
| #the Software, and to permit persons to whom the Software is furnished to do so, |
| #subject to the following conditions: |
| # |
| #The above copyright notice and this permission notice shall be included in all |
| #copies or substantial portions of the Software. |
| # |
| #THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR |
| #IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS |
| #FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR |
| #COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER |
| #IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN |
| #CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| $self->do(q| |
| DROP FUNCTION IF EXISTS anyarray_uniq(anyarray); |
| CREATE OR REPLACE FUNCTION anyarray_uniq(with_array anyarray) |
| RETURNS anyarray AS $BODY$ |
| DECLARE |
| -- The variable used to track iteration over "with_array". |
| loop_offset integer; |
| |
| -- The array to be returned by this function. |
| return_array with_array%TYPE := '{}'; |
| BEGIN |
| IF with_array IS NULL THEN |
| return NULL; |
| END IF; |
| |
| -- Iterate over each element in "concat_array". |
| FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP |
| IF NOT with_array[loop_offset] = ANY(return_array) THEN |
| return_array = ARRAY_APPEND(return_array, with_array[loop_offset]); |
| END IF; |
| END LOOP; |
| |
| RETURN return_array; |
| END; |
| $BODY$ LANGUAGE plpgsql; |
| |); |
| } |
| |
| # PostgreSQL doesn't like having *any* index on the thetext |
| # field, because it can't have index data longer than 2770 |
| # characters on that field. |
| $self->bz_drop_index('longdescs', 'longdescs_thetext_idx'); |
| # Same for all the comments fields in the fulltext table. |
| $self->bz_drop_index('bugs_fulltext', 'bugs_fulltext_comments_idx'); |
| $self->bz_drop_index('bugs_fulltext', |
| 'bugs_fulltext_comments_noprivate_idx'); |
| |
| # PostgreSQL also wants an index for calling LOWER on |
| # login_name, which we do with sql_istrcmp all over the place. |
| $self->bz_add_index('profiles', 'profiles_login_name_lower_idx', |
| {FIELDS => ['LOWER(login_name)'], TYPE => 'UNIQUE'}); |
| |
| # Now that Bugzilla::Object uses sql_istrcmp, other tables |
| # also need a LOWER() index. |
| _fix_case_differences('fielddefs', 'name'); |
| $self->bz_add_index('fielddefs', 'fielddefs_name_lower_idx', |
| {FIELDS => ['LOWER(name)'], TYPE => 'UNIQUE'}); |
| _fix_case_differences('keyworddefs', 'name'); |
| $self->bz_add_index('keyworddefs', 'keyworddefs_name_lower_idx', |
| {FIELDS => ['LOWER(name)'], TYPE => 'UNIQUE'}); |
| _fix_case_differences('products', 'name'); |
| $self->bz_add_index('products', 'products_name_lower_idx', |
| {FIELDS => ['LOWER(name)'], TYPE => 'UNIQUE'}); |
| |
| # bz_rename_column and bz_rename_table didn't correctly rename |
| # the sequence. |
| $self->_fix_bad_sequence('fielddefs', 'id', 'fielddefs_fieldid_seq', 'fielddefs_id_seq'); |
| # If the 'tags' table still exists, then bz_rename_table() |
| # will fix the sequence for us. |
| if (!$self->bz_table_info('tags')) { |
| my $res = $self->_fix_bad_sequence('tag', 'id', 'tags_id_seq', 'tag_id_seq'); |
| # If $res is true, then the sequence has been renamed, meaning that |
| # the primary key must be renamed too. |
| if ($res) { |
| $self->do('ALTER INDEX tags_pkey RENAME TO tag_pkey'); |
| } |
| } |
| |
| # Certain sequences got upgraded before we required Pg 8.3, and |
| # so they were not properly associated with their columns. |
| my @tables = $self->bz_table_list_real; |
| foreach my $table (@tables) { |
| my @columns = $self->bz_table_columns_real($table); |
| foreach my $column (@columns) { |
| # All our SERIAL pks have "id" in their name at the end. |
| next unless $column =~ /id$/; |
| my $sequence = "${table}_${column}_seq"; |
| if ($self->bz_sequence_exists($sequence)) { |
| my $is_associated = $self->selectrow_array( |
| 'SELECT pg_get_serial_sequence(?,?)', |
| undef, $table, $column); |
| next if $is_associated; |
| print "Fixing $sequence to be associated" |
| . " with $table.$column...\n"; |
| $self->do("ALTER SEQUENCE $sequence OWNED BY $table.$column"); |
| # In order to produce an exactly identical schema to what |
| # a brand-new checksetup.pl run would produce, we also need |
| # to re-set the default on this column. |
| $self->do("ALTER TABLE $table |
| ALTER COLUMN $column |
| SET DEFAULT nextval('$sequence')"); |
| } |
| } |
| } |
| } |
| |
| sub _fix_bad_sequence { |
| my ($self, $table, $column, $old_seq, $new_seq) = @_; |
| if ($self->bz_column_info($table, $column) |
| && $self->bz_sequence_exists($old_seq)) |
| { |
| print "Fixing $old_seq sequence...\n"; |
| $self->do("ALTER SEQUENCE $old_seq RENAME TO $new_seq"); |
| $self->do("ALTER TABLE $table ALTER COLUMN $column |
| SET DEFAULT NEXTVAL('$new_seq')"); |
| return 1; |
| } |
| return 0; |
| } |
| |
| # Renames things that differ only in case. |
| sub _fix_case_differences { |
| my ($table, $field) = @_; |
| my $dbh = Bugzilla->dbh; |
| |
| my $duplicates = $dbh->selectcol_arrayref( |
| "SELECT DISTINCT LOWER($field) FROM $table |
| GROUP BY LOWER($field) HAVING COUNT(LOWER($field)) > 1"); |
| |
| foreach my $name (@$duplicates) { |
| my $dups = $dbh->selectcol_arrayref( |
| "SELECT $field FROM $table WHERE LOWER($field) = ?", |
| undef, $name); |
| my $primary = shift @$dups; |
| foreach my $dup (@$dups) { |
| my $new_name = "${dup}_"; |
| # Make sure the new name isn't *also* a duplicate. |
| while (1) { |
| last if (!$dbh->selectrow_array( |
| "SELECT 1 FROM $table WHERE LOWER($field) = ?", |
| undef, lc($new_name))); |
| $new_name .= "_"; |
| } |
| print "$table '$primary' and '$dup' have names that differ", |
| " only in case.\nRenaming '$dup' to '$new_name'...\n"; |
| $dbh->do("UPDATE $table SET $field = ? WHERE $field = ?", |
| undef, $new_name, $dup); |
| } |
| } |
| } |
| |
| ##################################################################### |
| # Custom Schema Information Functions |
| ##################################################################### |
| |
| # Pg includes the PostgreSQL system tables in table_list_real, so |
| # we need to remove those. |
| sub bz_table_list_real { |
| my $self = shift; |
| |
| my @full_table_list = $self->SUPER::bz_table_list_real(@_); |
| # All PostgreSQL system tables start with "pg_" or "sql_" |
| my @table_list = grep(!/(^pg_)|(^sql_)/, @full_table_list); |
| return @table_list; |
| } |
| |
| 1; |
| |
| =head2 Functions |
| |
| =over |
| |
| =item C<sql_like_escape> |
| |
| =over |
| |
| =item B<Description> |
| |
| The postgres versions of the sql_like methods use the ANSI SQL LIKE |
| statements to perform substring searching. To prevent issues with |
| users attempting to search for strings containing special characters |
| associated with LIKE, we escape them out so they don't affect the search |
| terms. |
| |
| =item B<Params> |
| |
| =over |
| |
| =item C<$fragment> - The string fragment in need of escaping and quoting |
| |
| =back |
| |
| =item B<Returns> |
| |
| The fragment with any pre existing %,_,| characters escaped out, wrapped in |
| percent characters and quoted. |
| |
| =back |
| |
| =back |
| |
| =head1 B<Methods in need of POD> |
| |
| =over |
| |
| =item sql_date_format |
| |
| =item bz_explain |
| |
| =item bz_sequence_exists |
| |
| =item bz_last_key |
| |
| =item sql_position |
| |
| =item sql_like |
| |
| =item sql_ilike |
| |
| =item sql_not_ilike |
| |
| =item sql_limit |
| |
| =item sql_not_regexp |
| |
| =item sql_string_concat |
| |
| =item sql_date_math |
| |
| =item sql_to_days |
| |
| =item bz_check_server_version |
| |
| =item sql_from_days |
| |
| =item bz_table_list_real |
| |
| =item sql_regexp |
| |
| =item sql_istring |
| |
| =item sql_group_concat |
| |
| =item bz_setup_database |
| |
| =back |
