1
# -*- Mode: perl; indent-tabs-mode: nil -*-
3
# The contents of this file are subject to the Mozilla Public
4
# License Version 1.1 (the "License"); you may not use this file
5
# except in compliance with the License. You may obtain a copy of
6
# the License at http://www.mozilla.org/MPL/
8
# Software distributed under the License is distributed on an "AS
9
# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
10
# implied. See the License for the specific language governing
11
# rights and limitations under the License.
13
# The Original Code is the Bugzilla Bug Tracking System.
15
# The Initial Developer of the Original Code is Netscape Communications
16
# Corporation. Portions created by Netscape are
17
# Copyright (C) 1998 Netscape Communications Corporation. All
20
# Contributor(s): Andrew Dunstan <andrew@dunslane.net>,
21
# Edward J. Sabol <edwardjsabol@iname.com>
22
# Max Kanat-Alexander <mkanat@bugzilla.org>
24
package Bugzilla::DB::Schema::Pg;
26
###############################################################################
28
# DB::Schema implementation for PostgreSQL
30
###############################################################################
33
use base qw(Bugzilla::DB::Schema);
34
use Storable qw(dclone);
36
#------------------------------------------------------------------------------
41
$self = $self->SUPER::_initialize(@_);
43
# Remove FULLTEXT index types from the schemas.
44
foreach my $table (keys %{ $self->{schema} }) {
45
if ($self->{schema}{$table}{INDEXES}) {
46
foreach my $index (@{ $self->{schema}{$table}{INDEXES} }) {
47
if (ref($index) eq 'HASH') {
48
delete($index->{TYPE}) if (exists $index->{TYPE}
49
&& $index->{TYPE} eq 'FULLTEXT');
52
foreach my $index (@{ $self->{abstract_schema}{$table}{INDEXES} }) {
53
if (ref($index) eq 'HASH') {
54
delete($index->{TYPE}) if (exists $index->{TYPE}
55
&& $index->{TYPE} eq 'FULLTEXT');
61
$self->{db_specific} = {
63
BOOLEAN => 'smallint',
72
SMALLSERIAL => 'serial unique',
73
MEDIUMSERIAL => 'serial unique',
74
INTSERIAL => 'serial unique',
76
TINYTEXT => 'varchar(255)',
82
DATETIME => 'timestamp(0) without time zone',
86
$self->_adjust_schema;
91
#--------------------------------------------------------------------
93
sub get_rename_column_ddl {
94
my ($self, $table, $old_name, $new_name) = @_;
95
if (lc($old_name) eq lc($new_name)) {
96
# if the only change is a case change, return an empty list, since Pg
97
# is case-insensitive and will return an error about a duplicate name
100
my @sql = ("ALTER TABLE $table RENAME COLUMN $old_name TO $new_name");
101
my $def = $self->get_column_abstract($table, $old_name);
102
if ($def->{TYPE} =~ /SERIAL/i) {
103
# We have to rename the series also, and fix the default of the series.
104
push(@sql, "ALTER TABLE ${table}_${old_name}_seq
105
RENAME TO ${table}_${new_name}_seq");
106
push(@sql, "ALTER TABLE $table ALTER COLUMN $new_name
107
SET DEFAULT NEXTVAL('${table}_${new_name}_seq')");
112
sub get_rename_table_sql {
113
my ($self, $old_name, $new_name) = @_;
114
if (lc($old_name) eq lc($new_name)) {
115
# if the only change is a case change, return an empty list, since Pg
116
# is case-insensitive and will return an error about a duplicate name
119
return ("ALTER TABLE $old_name RENAME TO $new_name");
122
sub _get_alter_type_sql {
123
my ($self, $table, $column, $new_def, $old_def) = @_;
126
my $type = $new_def->{TYPE};
127
$type = $self->{db_specific}->{$type}
128
if exists $self->{db_specific}->{$type};
130
if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) {
131
die("You cannot specify a DEFAULT on a SERIAL-type column.")
132
if $new_def->{DEFAULT};
133
$type =~ s/serial/integer/i;
136
# On Pg, you don't need UNIQUE if you're a PK--it creates
137
# two identical indexes otherwise.
138
$type =~ s/unique//i if $new_def->{PRIMARYKEY};
140
push(@statements, "ALTER TABLE $table ALTER COLUMN $column
143
if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) {
144
push(@statements, "CREATE SEQUENCE ${table}_${column}_seq");
145
push(@statements, "SELECT setval('${table}_${column}_seq',
148
push(@statements, "ALTER TABLE $table ALTER COLUMN $column
149
SET DEFAULT nextval('${table}_${column}_seq')");
152
# If this column is no longer SERIAL, we need to drop the sequence
153
# that went along with it.
154
if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) {
155
push(@statements, "ALTER TABLE $table ALTER COLUMN $column
157
# XXX Pg actually won't let us drop the sequence, even though it's
158
# no longer in use. So we harmlessly leave behind a sequence
160
#push(@statements, "DROP SEQUENCE ${table}_${column}_seq");