| # -*- Mode: perl; indent-tabs-mode: nil -*- |
| # |
| # The contents of this file are subject to the Mozilla Public |
| # License Version 1.1 (the "License"); you may not use this file |
| # except in compliance with the License. You may obtain a copy of |
| # the License at http://www.mozilla.org/MPL/ |
| # |
| # Software distributed under the License is distributed on an "AS |
| # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or |
| # implied. See the License for the specific language governing |
| # rights and limitations under the License. |
| # |
| # The Original Code is the Bugzilla Bug Tracking System. |
| # |
| # The Initial Developer of the Original Code is Netscape Communications |
| # Corporation. Portions created by Netscape are |
| # Copyright (C) 1998 Netscape Communications Corporation. All |
| # Rights Reserved. |
| # |
| # Contributor(s): Andrew Dunstan <andrew@dunslane.net>, |
| # Edward J. Sabol <edwardjsabol@iname.com> |
| # Max Kanat-Alexander <mkanat@bugzilla.org> |
| |
| package Bugzilla::DB::Schema::Pg; |
| |
| ############################################################################### |
| # |
| # DB::Schema implementation for PostgreSQL |
| # |
| ############################################################################### |
| |
| use strict; |
| use base qw(Bugzilla::DB::Schema); |
| use Storable qw(dclone); |
| |
| #------------------------------------------------------------------------------ |
| sub _initialize { |
| |
| my $self = shift; |
| |
| $self = $self->SUPER::_initialize(@_); |
| |
| # Remove FULLTEXT index types from the schemas. |
| foreach my $table (keys %{ $self->{schema} }) { |
| if ($self->{schema}{$table}{INDEXES}) { |
| foreach my $index (@{ $self->{schema}{$table}{INDEXES} }) { |
| if (ref($index) eq 'HASH') { |
| delete($index->{TYPE}) if (exists $index->{TYPE} |
| && $index->{TYPE} eq 'FULLTEXT'); |
| } |
| } |
| foreach my $index (@{ $self->{abstract_schema}{$table}{INDEXES} }) { |
| if (ref($index) eq 'HASH') { |
| delete($index->{TYPE}) if (exists $index->{TYPE} |
| && $index->{TYPE} eq 'FULLTEXT'); |
| } |
| } |
| } |
| } |
| |
| $self->{db_specific} = { |
| |
| BOOLEAN => 'smallint', |
| FALSE => '0', |
| TRUE => '1', |
| |
| INT1 => 'integer', |
| INT2 => 'integer', |
| INT3 => 'integer', |
| INT4 => 'integer', |
| |
| SMALLSERIAL => 'serial unique', |
| MEDIUMSERIAL => 'serial unique', |
| INTSERIAL => 'serial unique', |
| |
| TINYTEXT => 'varchar(255)', |
| MEDIUMTEXT => 'text', |
| LONGTEXT => 'text', |
| |
| LONGBLOB => 'bytea', |
| |
| DATETIME => 'timestamp(0) without time zone', |
| |
| }; |
| |
| $self->_adjust_schema; |
| |
| return $self; |
| |
| } #eosub--_initialize |
| #-------------------------------------------------------------------- |
| |
| sub get_rename_column_ddl { |
| my ($self, $table, $old_name, $new_name) = @_; |
| if (lc($old_name) eq lc($new_name)) { |
| # if the only change is a case change, return an empty list, since Pg |
| # is case-insensitive and will return an error about a duplicate name |
| return (); |
| } |
| my @sql = ("ALTER TABLE $table RENAME COLUMN $old_name TO $new_name"); |
| my $def = $self->get_column_abstract($table, $old_name); |
| if ($def->{TYPE} =~ /SERIAL/i) { |
| # We have to rename the series also, and fix the default of the series. |
| push(@sql, "ALTER TABLE ${table}_${old_name}_seq |
| RENAME TO ${table}_${new_name}_seq"); |
| push(@sql, "ALTER TABLE $table ALTER COLUMN $new_name |
| SET DEFAULT NEXTVAL('${table}_${new_name}_seq')"); |
| } |
| return @sql; |
| } |
| |
| sub get_rename_table_sql { |
| my ($self, $old_name, $new_name) = @_; |
| if (lc($old_name) eq lc($new_name)) { |
| # if the only change is a case change, return an empty list, since Pg |
| # is case-insensitive and will return an error about a duplicate name |
| return (); |
| } |
| return ("ALTER TABLE $old_name RENAME TO $new_name"); |
| } |
| |
| sub _get_alter_type_sql { |
| my ($self, $table, $column, $new_def, $old_def) = @_; |
| my @statements; |
| |
| my $type = $new_def->{TYPE}; |
| $type = $self->{db_specific}->{$type} |
| if exists $self->{db_specific}->{$type}; |
| |
| if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { |
| die("You cannot specify a DEFAULT on a SERIAL-type column.") |
| if $new_def->{DEFAULT}; |
| $type =~ s/serial/integer/i; |
| } |
| |
| # On Pg, you don't need UNIQUE if you're a PK--it creates |
| # two identical indexes otherwise. |
| $type =~ s/unique//i if $new_def->{PRIMARYKEY}; |
| |
| push(@statements, "ALTER TABLE $table ALTER COLUMN $column |
| TYPE $type"); |
| |
| if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { |
| push(@statements, "CREATE SEQUENCE ${table}_${column}_seq"); |
| push(@statements, "SELECT setval('${table}_${column}_seq', |
| MAX($table.$column)) |
| FROM $table"); |
| push(@statements, "ALTER TABLE $table ALTER COLUMN $column |
| SET DEFAULT nextval('${table}_${column}_seq')"); |
| } |
| |
| # If this column is no longer SERIAL, we need to drop the sequence |
| # that went along with it. |
| if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) { |
| push(@statements, "ALTER TABLE $table ALTER COLUMN $column |
| DROP DEFAULT"); |
| # XXX Pg actually won't let us drop the sequence, even though it's |
| # no longer in use. So we harmlessly leave behind a sequence |
| # that does nothing. |
| #push(@statements, "DROP SEQUENCE ${table}_${column}_seq"); |
| } |
| |
| return @statements; |
| } |
| |
| 1; |