1# This Source Code Form is subject to the terms of the Mozilla Public 2# License, v. 2.0. If a copy of the MPL was not distributed with this 3# file, You can obtain one at http://mozilla.org/MPL/2.0/. 4# 5# This Source Code Form is "Incompatible With Secondary Licenses", as 6# defined by the Mozilla Public License, v. 2.0. 7 8package Bugzilla::DB::Schema::Pg; 9 10############################################################################### 11# 12# DB::Schema implementation for PostgreSQL 13# 14############################################################################### 15 16use strict; 17use base qw(Bugzilla::DB::Schema); 18use Storable qw(dclone); 19 20#------------------------------------------------------------------------------ 21sub _initialize { 22 23 my $self = shift; 24 25 $self = $self->SUPER::_initialize(@_); 26 27 # Remove FULLTEXT index types from the schemas. 28 foreach my $table (keys %{ $self->{schema} }) { 29 if ($self->{schema}{$table}{INDEXES}) { 30 foreach my $index (@{ $self->{schema}{$table}{INDEXES} }) { 31 if (ref($index) eq 'HASH') { 32 delete($index->{TYPE}) if (exists $index->{TYPE} 33 && $index->{TYPE} eq 'FULLTEXT'); 34 } 35 } 36 foreach my $index (@{ $self->{abstract_schema}{$table}{INDEXES} }) { 37 if (ref($index) eq 'HASH') { 38 delete($index->{TYPE}) if (exists $index->{TYPE} 39 && $index->{TYPE} eq 'FULLTEXT'); 40 } 41 } 42 } 43 } 44 45 $self->{db_specific} = { 46 47 BOOLEAN => 'smallint', 48 FALSE => '0', 49 TRUE => '1', 50 51 INT1 => 'integer', 52 INT2 => 'integer', 53 INT3 => 'integer', 54 INT4 => 'integer', 55 56 SMALLSERIAL => 'serial unique', 57 MEDIUMSERIAL => 'serial unique', 58 INTSERIAL => 'serial unique', 59 60 TINYTEXT => 'varchar(255)', 61 MEDIUMTEXT => 'text', 62 LONGTEXT => 'text', 63 64 LONGBLOB => 'bytea', 65 66 DATETIME => 'timestamp(0) without time zone', 67 68 }; 69 70 $self->_adjust_schema; 71 72 return $self; 73 74} #eosub--_initialize 75#-------------------------------------------------------------------- 76 77sub get_create_database_sql { 78 my ($self, $name) = @_; 79 # We only create as utf8 if we have no params (meaning we're doing 80 # a new installation) or if the utf8 param is on. 81 my $create_utf8 = Bugzilla->params->{'utf8'} 82 || !defined Bugzilla->params->{'utf8'}; 83 my $charset = $create_utf8 ? "ENCODING 'UTF8' TEMPLATE template0" : ''; 84 return ("CREATE DATABASE $name $charset"); 85} 86 87sub get_rename_column_ddl { 88 my ($self, $table, $old_name, $new_name) = @_; 89 if (lc($old_name) eq lc($new_name)) { 90 # if the only change is a case change, return an empty list, since Pg 91 # is case-insensitive and will return an error about a duplicate name 92 return (); 93 } 94 my @sql = ("ALTER TABLE $table RENAME COLUMN $old_name TO $new_name"); 95 my $def = $self->get_column_abstract($table, $old_name); 96 if ($def->{TYPE} =~ /SERIAL/i) { 97 # We have to rename the series also. 98 push(@sql, "ALTER SEQUENCE ${table}_${old_name}_seq 99 RENAME TO ${table}_${new_name}_seq"); 100 } 101 return @sql; 102} 103 104sub get_rename_table_sql { 105 my ($self, $old_name, $new_name) = @_; 106 if (lc($old_name) eq lc($new_name)) { 107 # if the only change is a case change, return an empty list, since Pg 108 # is case-insensitive and will return an error about a duplicate name 109 return (); 110 } 111 112 my @sql = ("ALTER TABLE $old_name RENAME TO $new_name"); 113 114 # If there's a SERIAL column on this table, we also need to rename the 115 # sequence. 116 # If there is a PRIMARY KEY, we need to rename it too. 117 my @columns = $self->get_table_columns($old_name); 118 foreach my $column (@columns) { 119 my $def = $self->get_column_abstract($old_name, $column); 120 if ($def->{TYPE} =~ /SERIAL/i) { 121 my $old_seq = "${old_name}_${column}_seq"; 122 my $new_seq = "${new_name}_${column}_seq"; 123 push(@sql, "ALTER SEQUENCE $old_seq RENAME TO $new_seq"); 124 push(@sql, "ALTER TABLE $new_name ALTER COLUMN $column 125 SET DEFAULT NEXTVAL('$new_seq')"); 126 } 127 if ($def->{PRIMARYKEY}) { 128 my $old_pk = "${old_name}_pkey"; 129 my $new_pk = "${new_name}_pkey"; 130 push(@sql, "ALTER INDEX $old_pk RENAME to $new_pk"); 131 } 132 } 133 134 return @sql; 135} 136 137sub get_set_serial_sql { 138 my ($self, $table, $column, $value) = @_; 139 return ("SELECT setval('${table}_${column}_seq', $value, false) 140 FROM $table"); 141} 142 143sub _get_alter_type_sql { 144 my ($self, $table, $column, $new_def, $old_def) = @_; 145 my @statements; 146 147 my $type = $new_def->{TYPE}; 148 $type = $self->{db_specific}->{$type} 149 if exists $self->{db_specific}->{$type}; 150 151 if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { 152 die("You cannot specify a DEFAULT on a SERIAL-type column.") 153 if $new_def->{DEFAULT}; 154 } 155 156 $type =~ s/\bserial\b/integer/i; 157 158 # On Pg, you don't need UNIQUE if you're a PK--it creates 159 # two identical indexes otherwise. 160 $type =~ s/unique//i if $new_def->{PRIMARYKEY}; 161 162 push(@statements, "ALTER TABLE $table ALTER COLUMN $column 163 TYPE $type"); 164 165 if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { 166 push(@statements, "CREATE SEQUENCE ${table}_${column}_seq 167 OWNED BY $table.$column"); 168 push(@statements, "SELECT setval('${table}_${column}_seq', 169 MAX($table.$column)) 170 FROM $table"); 171 push(@statements, "ALTER TABLE $table ALTER COLUMN $column 172 SET DEFAULT nextval('${table}_${column}_seq')"); 173 } 174 175 # If this column is no longer SERIAL, we need to drop the sequence 176 # that went along with it. 177 if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) { 178 push(@statements, "ALTER TABLE $table ALTER COLUMN $column 179 DROP DEFAULT"); 180 push(@statements, "ALTER SEQUENCE ${table}_${column}_seq 181 OWNED BY NONE"); 182 push(@statements, "DROP SEQUENCE ${table}_${column}_seq"); 183 } 184 185 return @statements; 186} 187 1881; 189