diff options
| -rw-r--r-- | Bugzilla/DB/Schema.pm | 53 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Sqlite.pm | 185 |
2 files changed, 218 insertions, 20 deletions
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index 56e763d20..ae5db8ffe 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -1867,12 +1867,8 @@ sub _hash_identifier { sub get_add_fks_sql { my ($self, $table, $column_fks) = @_; - my @add; - foreach my $column (keys %$column_fks) { - my $def = $column_fks->{$column}; - my $fk_string = $self->get_fk_ddl($table, $column, $def); - push(@add, $fk_string); - } + my @add = $self->_column_fks_to_ddl($table, $column_fks); + my @sql; if ($self->MULTIPLE_FKS_IN_ALTER) { my $alter = "ALTER TABLE $table ADD " . join(', ADD ', @add); @@ -1886,6 +1882,17 @@ sub get_add_fks_sql { return @sql; } +sub _column_fks_to_ddl { + my ($self, $table, $column_fks) = @_; + my @ddl; + foreach my $column (keys %$column_fks) { + my $def = $column_fks->{$column}; + my $fk_string = $self->get_fk_ddl($table, $column, $def); + push(@ddl, $fk_string); + } + return @ddl; +} + sub get_drop_fk_sql { my ($self, $table, $column, $references) = @_; my $fk_name = $self->_get_fk_name($table, $column, $references); @@ -2051,7 +2058,7 @@ sub _get_create_table_ddl { push(@col_lines, "\t$field\t" . $self->get_type_ddl($finfo)); if ($self->FK_ON_CREATE and $finfo->{REFERENCES}) { my $fk = $finfo->{REFERENCES}; - my $fk_ddl = "\t" . $self->get_fk_ddl($table, $field, $fk); + my $fk_ddl = $self->get_fk_ddl($table, $field, $fk); push(@fk_lines, $fk_ddl); } } @@ -2176,7 +2183,8 @@ sub get_alter_column_ddl { =cut - my ($self, $table, $column, $new_def, $set_nulls_to) = @_; + my $self = shift; + my ($table, $column, $new_def, $set_nulls_to) = @_; my @statements; my $old_def = $self->get_column_abstract($table, $column); @@ -2213,17 +2221,7 @@ sub get_alter_column_ddl { # If we went from NULL to NOT NULL. if (!$old_def->{NOTNULL} && $new_def->{NOTNULL}) { - my $setdefault; - # Handle any fields that were NULL before, if we have a default, - $setdefault = $default if defined $default; - # But if we have a set_nulls_to, that overrides the DEFAULT - # (although nobody would usually specify both a default and - # a set_nulls_to.) - $setdefault = $set_nulls_to if defined $set_nulls_to; - if (defined $setdefault) { - push(@statements, "UPDATE $table SET $column = $setdefault" - . " WHERE $column IS NULL"); - } + push(@statements, $self->_set_nulls_sql(@_)); push(@statements, "ALTER TABLE $table ALTER COLUMN $column" . " SET NOT NULL"); } @@ -2245,6 +2243,23 @@ sub get_alter_column_ddl { return @statements; } +sub _set_nulls_sql { + my ($self, $table, $column, $new_def, $set_nulls_to) = @_; + my $setdefault; + # Handle any fields that were NULL before, if we have a default, + $setdefault = $new_def->{DEFAULT} if defined $new_def->{DEFAULT}; + # But if we have a set_nulls_to, that overrides the DEFAULT + # (although nobody would usually specify both a default and + # a set_nulls_to.) + $setdefault = $set_nulls_to if defined $set_nulls_to; + my @sql; + if (defined $setdefault) { + push(@sql, "UPDATE $table SET $column = $setdefault" + . " WHERE $column IS NULL"); + } + return @sql; +} + sub get_drop_index_ddl { =item C<get_drop_index_ddl($table, $name)> diff --git a/Bugzilla/DB/Schema/Sqlite.pm b/Bugzilla/DB/Schema/Sqlite.pm index 171f6217c..a18fd5c80 100644 --- a/Bugzilla/DB/Schema/Sqlite.pm +++ b/Bugzilla/DB/Schema/Sqlite.pm @@ -24,6 +24,7 @@ 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); @@ -64,6 +65,79 @@ sub _initialize { } +################################# +# 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); +} + +# This does most of the "heavy lifting" of the schema-altering functions. +sub _sqlite_alter_schema { + my ($self, $table, $create_table, $options) = @_; + + 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, @@ -78,7 +152,7 @@ sub get_type_ddl { my $def = dclone($_[0]); my $ddl = $self->SUPER::get_type_ddl(@_); - if ($def->{PRIMARYKEY} and $def->{TYPE} eq 'SERIAL') { + if ($def->{PRIMARYKEY} and $def->{TYPE} =~ /SERIAL/i) { $ddl =~ s/\bSERIAL\b/integer/; $ddl =~ s/\bPRIMARY KEY\b/PRIMARY KEY AUTOINCREMENT/; } @@ -92,4 +166,113 @@ sub get_type_ddl { 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 @add = $self->_column_fks_to_ddl($table, $column_fks); + my $table_sql = $self->_sqlite_create_table($table); + my $add_lines = join("\n,", @add) . "\n"; + $table_sql =~ s/\)$/$add_lines)/s + || die "Can't find end of CREATE TABLE: $table_sql"; + return $self->_sqlite_alter_schema($table, $table_sql); +} + +sub get_drop_fk_sql { + my ($self, $table, $column, $references) = @_; + my $table_sql = $self->_sqlite_create_table($table); + my $fk_name = $self->_get_fk_name($table, $column, $references); + $table_sql =~ s/^\s+CONSTRAINT $fk_name.*?ON DELETE \S+,?$//ms + || die "Can't find $fk_name: $table_sql"; + return $self->_sqlite_alter_schema($table, $table_sql); +} + + 1; |