Update to 4.2.

3 files changed, 408 insertions, 30 deletions

diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm
index 6011cecfc..f2d5b8be0 100644
--- a/Bugzilla/DB/Schema/Oracle.pm
+++ b/Bugzilla/DB/Schema/Oracle.pm
@@ -351,17 +351,10 @@ sub get_rename_column_ddl {
     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, "RENAME ${table}_${old_name}_SEQ TO 
-                      ${table}_${new_name}_seq");
-        my $serial_sql =
-                       "CREATE OR REPLACE TRIGGER ${table}_${new_name}_TR "
-                     . " BEFORE INSERT ON ${table} "
-                     . " FOR EACH ROW "
-                     . " BEGIN "
-                     . "   SELECT ${table}_${new_name}_SEQ.NEXTVAL "
-                     . "   INTO :NEW.${new_name} FROM DUAL; "
-                     . " END;";
-        push(@sql, $serial_sql);
+        my $old_seq = "${table}_${old_name}_SEQ";
+        my $new_seq = "${table}_${new_name}_SEQ";
+        push(@sql, "RENAME $old_seq TO $new_seq");
+        push(@sql, $self->_get_create_trigger_ddl($table, $new_name, $new_seq));
         push(@sql, "DROP TRIGGER ${table}_${old_name}_TR");
     }
     if ($def->{TYPE} =~ /varchar|text/i && $def->{NOTNULL} ) {
@@ -371,6 +364,53 @@ sub get_rename_column_ddl {
     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.
+        return ();
+    }
+
+    my @sql = ("ALTER TABLE $old_name RENAME TO $new_name");
+    my @columns = $self->get_table_columns($old_name);
+    foreach my $column (@columns) {
+        my $def = $self->get_column_abstract($old_name, $column);
+        if ($def->{TYPE} =~ /SERIAL/i) {
+            # If there's a SERIAL column on this table, we also need
+            # to rename the sequence.
+            my $old_seq = "${old_name}_${column}_SEQ";
+            my $new_seq = "${new_name}_${column}_SEQ";
+            push(@sql, "RENAME $old_seq TO $new_seq");
+            push(@sql, $self->_get_create_trigger_ddl($new_name, $column, $new_seq));
+            push(@sql, "DROP TRIGGER ${old_name}_${column}_TR");
+        }
+        if ($def->{TYPE} =~ /varchar|text/i && $def->{NOTNULL}) {
+            push(@sql, _get_notnull_trigger_ddl($new_name, $column));
+            push(@sql, "DROP TRIGGER ${old_name}_${column}");
+        }
+    }
+
+    return @sql;
+}
+
+sub get_drop_table_ddl {
+    my ($self, $name) = @_;
+    my @sql;
+
+    my @columns = $self->get_table_columns($name);
+    foreach my $column (@columns) {
+        my $def = $self->get_column_abstract($name, $column);
+        if ($def->{TYPE} =~ /SERIAL/i) {
+            # If there's a SERIAL column on this table, we also need
+            # to remove the sequence.
+            push(@sql, "DROP SEQUENCE ${name}_${column}_SEQ");
+        }
+    }
+    push(@sql, "DROP TABLE $name CASCADE CONSTRAINTS PURGE");
+
+    return @sql;
+}
+
 sub _get_notnull_trigger_ddl {
       my ($table, $column) = @_;
 
@@ -398,19 +438,24 @@ sub _get_create_seq_ddl {
                    . " NOMAXVALUE "
                    . " NOCYCLE "
                    . " NOCACHE";
-     my $serial_sql = "CREATE OR REPLACE TRIGGER ${table}_${column}_TR "
-                    . " BEFORE INSERT ON ${table} "
-                    . " FOR EACH ROW "
-                    . " BEGIN "
-                    . "   SELECT ${seq_name}.NEXTVAL "
-                    . "   INTO :NEW.${column} FROM DUAL; "
-                    . " END;";
     push (@ddl, $seq_sql);
-    push (@ddl, $serial_sql);
+    push(@ddl, $self->_get_create_trigger_ddl($table, $column, $seq_name));
 
     return @ddl;
 }
 
+sub _get_create_trigger_ddl {
+    my ($self, $table, $column, $seq_name) = @_;
+    my $serial_sql = "CREATE OR REPLACE TRIGGER ${table}_${column}_TR "
+                   . " BEFORE INSERT ON $table "
+                   . " FOR EACH ROW "
+                   . " BEGIN "
+                   . "   SELECT ${seq_name}.NEXTVAL "
+                   . "   INTO :NEW.$column FROM DUAL; "
+                   . " END;";
+    return $serial_sql;
+}
+
 sub get_set_serial_sql { 
     my ($self, $table, $column, $value) = @_; 
     my @sql;
diff --git a/Bugzilla/DB/Schema/Pg.pm b/Bugzilla/DB/Schema/Pg.pm
index 0b10acbb0..ef6e5671d 100644
--- a/Bugzilla/DB/Schema/Pg.pm
+++ b/Bugzilla/DB/Schema/Pg.pm
@@ -100,11 +100,9 @@ sub get_rename_column_ddl {
     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')");
+        # We have to rename the series also.
+        push(@sql, "ALTER SEQUENCE ${table}_${old_name}_seq 
+                         RENAME TO ${table}_${new_name}_seq");
     }
     return @sql;
 }
@@ -116,7 +114,30 @@ sub get_rename_table_sql {
         # is case-insensitive and will return an error about a duplicate name
         return ();
     }
-    return ("ALTER TABLE $old_name RENAME TO $new_name");
+
+    my @sql = ("ALTER TABLE $old_name RENAME TO $new_name");
+
+    # If there's a SERIAL column on this table, we also need to rename the
+    # sequence.
+    # If there is a PRIMARY KEY, we need to rename it too.
+    my @columns = $self->get_table_columns($old_name);
+    foreach my $column (@columns) {
+        my $def = $self->get_column_abstract($old_name, $column);
+        if ($def->{TYPE} =~ /SERIAL/i) {
+            my $old_seq = "${old_name}_${column}_seq";
+            my $new_seq = "${new_name}_${column}_seq";
+            push(@sql, "ALTER SEQUENCE $old_seq RENAME TO $new_seq");
+            push(@sql, "ALTER TABLE $new_name ALTER COLUMN $column
+                             SET DEFAULT NEXTVAL('$new_seq')");
+        }
+        if ($def->{PRIMARYKEY}) {
+            my $old_pk = "${old_name}_pkey";
+            my $new_pk = "${new_name}_pkey";
+            push(@sql, "ALTER INDEX $old_pk RENAME to $new_pk");
+        }
+    }
+
+    return @sql;
 }
 
 sub get_set_serial_sql {
@@ -148,7 +169,8 @@ sub _get_alter_type_sql {
                               TYPE $type");
 
     if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) {
-        push(@statements, "CREATE SEQUENCE ${table}_${column}_seq");
+        push(@statements, "CREATE SEQUENCE ${table}_${column}_seq
+                                  OWNED BY $table.$column");
         push(@statements, "SELECT setval('${table}_${column}_seq',
                                          MAX($table.$column))
                              FROM $table");
@@ -161,10 +183,9 @@ sub _get_alter_type_sql {
     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");
+        push(@statements, "ALTER SEQUENCE ${table}_${column}_seq 
+                           OWNED BY NONE");
+        push(@statements, "DROP SEQUENCE ${table}_${column}_seq");
     }
 
     return @statements;
diff --git a/Bugzilla/DB/Schema/Sqlite.pm b/Bugzilla/DB/Schema/Sqlite.pm
new file mode 100644
index 000000000..aad1f17bc
--- /dev/null
+++ b/Bugzilla/DB/Schema/Sqlite.pm
@@ -0,0 +1,312 @@
+# -*- 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 Everything Solved, Inc.
+# Portions created by the Initial Developer are Copyright (C) 2010 the
+# Initial Developer. All Rights Reserved.
+#
+# Contributor(s):
+#   Max Kanat-Alexander <mkanat@bugzilla.org>
+
+use strict;
+package Bugzilla::DB::Schema::Sqlite;
+use base qw(Bugzilla::DB::Schema);
+
+use Bugzilla::Error;
+use Bugzilla::Util qw(generate_random_password);
+
+use Storable qw(dclone);
+
+use constant FK_ON_CREATE => 1;
+
+sub _initialize {
+
+    my $self = shift;
+
+    $self = $self->SUPER::_initialize(@_);
+
+    $self->{db_specific} = {
+        BOOLEAN =>      'integer',
+        FALSE =>        '0', 
+        TRUE =>         '1',
+
+        INT1 =>         'integer',
+        INT2 =>         'integer',
+        INT3 =>         'integer',
+        INT4 =>         'integer',
+
+        SMALLSERIAL =>  'SERIAL',
+        MEDIUMSERIAL => 'SERIAL',
+        INTSERIAL =>    'SERIAL',
+
+        TINYTEXT =>     'text',
+        MEDIUMTEXT =>   'text',
+        LONGTEXT =>     'text',
+
+        LONGBLOB =>     'blob',
+
+        DATETIME =>     'DATETIME',
+    };
+
+    $self->_adjust_schema;
+
+    return $self;
+
+}
+
+#################################
+# General SQLite Schema Helpers #
+#################################
+
+sub _sqlite_create_table {
+    my ($self, $table) = @_;
+    return scalar Bugzilla->dbh->selectrow_array(
+        "SELECT sql FROM sqlite_master WHERE name = ? AND type = 'table'",
+        undef, $table);
+}
+
+sub _sqlite_table_lines {
+    my $self = shift;
+    my $table_sql = $self->_sqlite_create_table(@_);
+    $table_sql =~ s/\n*\)$//s;
+    # The $ makes this work even if people some day add crazy stuff to their
+    # schema like multi-column foreign keys.
+    return split(/,\s*$/m, $table_sql);
+}
+
+# This does most of the "heavy lifting" of the schema-altering functions.
+sub _sqlite_alter_schema {
+    my ($self, $table, $create_table, $options) = @_;
+    
+    # $create_table is sometimes an array in the form that _sqlite_table_lines
+    # returns.
+    if (ref $create_table) {
+        $create_table = join(',', @$create_table) . "\n)";
+    }
+    
+    my $dbh = Bugzilla->dbh;
+    
+    my $random = generate_random_password(5);
+    my $rename_to = "${table}_$random";
+
+    my @columns = $dbh->bz_table_columns_real($table);
+    push(@columns, $options->{extra_column}) if $options->{extra_column};
+    if (my $exclude = $options->{exclude_column}) {
+        @columns = grep { $_ ne $exclude } @columns;
+    }
+    my @insert_cols = @columns;
+    my @select_cols = @columns;
+    if (my $rename = $options->{rename}) {
+        foreach my $from (keys %$rename) {
+            my $to = $rename->{$from};
+            @insert_cols = map { $_ eq $from ? $to : $_ } @insert_cols;
+        }
+    }
+    
+    my $insert_str = join(',', @insert_cols);
+    my $select_str = join(',', @select_cols);
+    my $copy_sql = "INSERT INTO $table ($insert_str)"
+                   . " SELECT $select_str FROM $rename_to";
+                   
+    # We have to turn FKs off before doing this. Otherwise, when we rename
+    # the table, all of the FKs in the other tables will be automatically
+    # updated to point to the renamed table. Note that PRAGMA foreign_keys
+    # can only be set outside of a transaction--otherwise it is a no-op.
+    if ($dbh->bz_in_transaction) {
+        die "can't alter the schema inside of a transaction";
+    }
+    my @sql = (
+        'PRAGMA foreign_keys = OFF',
+        'BEGIN EXCLUSIVE TRANSACTION',
+        @{ $options->{pre_sql} || [] },
+        "ALTER TABLE $table RENAME TO $rename_to",
+        $create_table,
+        $copy_sql,
+        "DROP TABLE $rename_to",
+        'COMMIT TRANSACTION',
+        'PRAGMA foreign_keys = ON',
+    );    
+}
+
+# For finding a particular column's definition in a CREATE TABLE statement.
+sub _sqlite_column_regex {
+    my ($column) = @_;
+    # 1 = Comma at start
+    # 2 = Column name + Space
+    # 3 = Definition
+    # 4 = Ending comma
+    return qr/(^|,)(\s\Q$column\E\s+)(.*?)(,|$)/m;
+}
+
+#############################
+# Schema Setup & Alteration #
+#############################
+
+sub get_create_database_sql {
+    # If we get here, it means there was some error creating the
+    # database file during bz_create_database in Bugzilla::DB,
+    # and we just want to display that error instead of doing
+    # anything else.
+    Bugzilla->dbh;
+    die "Reached an unreachable point";
+}
+
+sub _get_create_table_ddl {
+    my $self = shift;
+    my ($table) = @_;
+    my $ddl = $self->SUPER::_get_create_table_ddl(@_);
+
+    # TheSchwartz uses its own driver to access its tables, meaning
+    # that it doesn't understand "COLLATE bugzilla" and in fact
+    # SQLite throws an error when TheSchwartz tries to access its
+    # own tables, if COLLATE bugzilla is on them. We don't have
+    # to fix this elsewhere currently, because we only create
+    # TheSchwartz's tables, we never modify them.
+    if ($table =~ /^ts_/) {
+        $ddl =~ s/ COLLATE bugzilla//g;
+    }
+    return $ddl;
+}
+
+sub get_type_ddl {
+    my $self = shift;
+    my $def = dclone($_[0]);
+    
+    my $ddl = $self->SUPER::get_type_ddl(@_);
+    if ($def->{PRIMARYKEY} and $def->{TYPE} =~ /SERIAL/i) {
+        $ddl =~ s/\bSERIAL\b/integer/;
+        $ddl =~ s/\bPRIMARY KEY\b/PRIMARY KEY AUTOINCREMENT/;
+    }
+    if ($def->{TYPE} =~ /text/i or $def->{TYPE} =~ /char/i) {
+        $ddl .= " COLLATE bugzilla";
+    }
+    # Don't collate DATETIME fields.
+    if ($def->{TYPE} eq 'DATETIME') {
+        $ddl =~ s/\bDATETIME\b/text COLLATE BINARY/;
+    }
+    return $ddl;
+}
+
+sub get_alter_column_ddl {
+    my $self = shift;
+    my ($table, $column, $new_def, $set_nulls_to) = @_;
+    my $dbh = Bugzilla->dbh;
+    
+    my $table_sql = $self->_sqlite_create_table($table);
+    my $new_ddl = $self->get_type_ddl($new_def);
+    # When we do ADD COLUMN, columns can show up all on one line separated
+    # by commas, so we have to account for that.
+    my $column_regex = _sqlite_column_regex($column);
+    $table_sql =~ s/$column_regex/$1$2$new_ddl$4/
+        || die "couldn't find $column in $table:\n$table_sql";
+    my @pre_sql = $self->_set_nulls_sql(@_);
+    return $self->_sqlite_alter_schema($table, $table_sql,
+                                       { pre_sql => \@pre_sql });
+}
+
+sub get_add_column_ddl {
+    my $self = shift;
+    my ($table, $column, $definition, $init_value) = @_;
+    # SQLite can use the normal ADD COLUMN when:
+    # * The column isn't a PK
+    if ($definition->{PRIMARYKEY}) {
+        if ($definition->{NOTNULL} and $definition->{TYPE} !~ /SERIAL/i) {
+            die "You can only add new SERIAL type PKs with SQLite";
+        }
+        my $table_sql = $self->_sqlite_new_column_sql(@_);
+        # This works because _sqlite_alter_schema will exclude the new column
+        # in its INSERT ... SELECT statement, meaning that when the "new"
+        # table is populated, it will have AUTOINCREMENT values generated
+        # for it.
+        return $self->_sqlite_alter_schema($table, $table_sql);
+    }
+    # * The column has a default one way or another. Either it
+    #   defaults to NULL (it lacks NOT NULL) or it has a DEFAULT
+    #   clause. Since we also require this when doing bz_add_column (in
+    #   the way of forcing an init_value for NOT NULL columns with no
+    #   default), we first set the init_value as the default and then
+    #   alter the column.
+    if ($definition->{NOTNULL} and !defined $definition->{DEFAULT}) {
+        my %with_default = %$definition;
+        $with_default{DEFAULT} = $init_value;
+        my @pre_sql =
+            $self->SUPER::get_add_column_ddl($table, $column, \%with_default);
+        my $table_sql = $self->_sqlite_new_column_sql(@_);
+        return $self->_sqlite_alter_schema($table, $table_sql,
+            { pre_sql => \@pre_sql, extra_column => $column });
+    }
+    
+    return $self->SUPER::get_add_column_ddl(@_);
+}
+
+sub _sqlite_new_column_sql {
+    my ($self, $table, $column, $def) = @_;
+    my $table_sql = $self->_sqlite_create_table($table);
+    my $new_ddl = $self->get_type_ddl($def);
+    my $new_line = "\t$column\t$new_ddl";
+    $table_sql =~ s/^(CREATE TABLE \w+ \()/$1\n$new_line,/s
+        || die "Can't find start of CREATE TABLE:\n$table_sql";
+    return $table_sql;
+}
+
+sub get_drop_column_ddl {
+    my ($self, $table, $column) = @_;
+    my $table_sql = $self->_sqlite_create_table($table);
+    my $column_regex = _sqlite_column_regex($column);
+    $table_sql =~ s/$column_regex/$1/
+        || die "Can't find column $column: $table_sql";
+    # Make sure we don't end up with a comma at the end of the definition.
+    $table_sql =~ s/,\s+\)$/\n)/s;
+    return $self->_sqlite_alter_schema($table, $table_sql,
+                                       { exclude_column => $column });
+}
+
+sub get_rename_column_ddl {
+    my ($self, $table, $old_name, $new_name) = @_;
+    my $table_sql = $self->_sqlite_create_table($table);
+    my $column_regex = _sqlite_column_regex($old_name);
+    $table_sql =~ s/$column_regex/$1\t$new_name\t$3$4/
+        || die "Can't find $old_name: $table_sql";
+    my %rename = ($old_name => $new_name);
+    return $self->_sqlite_alter_schema($table, $table_sql,
+                                       { rename => \%rename });
+}
+
+################
+# Foreign Keys #
+################
+
+sub get_add_fks_sql {
+    my ($self, $table, $column_fks) = @_;
+    my @clauses = $self->_sqlite_table_lines($table);
+    my @add = $self->_column_fks_to_ddl($table, $column_fks);
+    push(@clauses, @add);
+    return $self->_sqlite_alter_schema($table, \@clauses);
+}
+
+sub get_drop_fk_sql {
+    my ($self, $table, $column, $references) = @_;
+    my @clauses = $self->_sqlite_table_lines($table);
+    my $fk_name = $self->_get_fk_name($table, $column, $references);
+    
+    my $line_re = qr/^\s+CONSTRAINT $fk_name /s;
+    grep { $line_re } @clauses
+        or die "Can't find $fk_name: " . join(',', @clauses);
+    @clauses = grep { $_ !~ $line_re } @clauses;
+    
+    return $self->_sqlite_alter_schema($table, \@clauses);
+}
+
+
+1;