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