From 17844d976d7036582f7b8204bdde7ac2429b1a38 Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Fri, 18 Mar 2005 11:21:33 +0000 Subject: Bug 285723: Cross-DB bz_add_column (Part of Bug 285111) Patch By Max Kanat-Alexander r=Tomas.Kopal, a=justdave --- Bugzilla/DB.pm | 77 +++++++++++++++++++++++++++++++++++++++++----- Bugzilla/DB/Schema.pm | 79 +++++++++++++++++++++++++++++++++++++++++++++--- Bugzilla/DB/Schema/Pg.pm | 42 +++++++++++++++++++++++-- 3 files changed, 183 insertions(+), 15 deletions(-) diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index c0a498e4c..07e23f0e7 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -323,6 +323,31 @@ sub bz_setup_database { # Schema Modification Methods ##################################################################### +sub bz_add_column { + my ($self, $table, $name, $new_def) = @_; + + # You can't add a NOT NULL column to a table with + # no DEFAULT statement. + if ($new_def->{NOTNULL} && !exists $new_def->{DEFAULT}) { + die "Failed adding the column ${table}.${name}:\n You cannot add" + . " a NOT NULL column with no default to an existing table.\n"; + } + + my $current_def = $self->bz_column_info($table, $name); + + if (!$current_def) { + my @statements = $self->_bz_real_schema->get_add_column_ddl( + $table, $name, $new_def); + print "Adding new column $name to table $table ...\n"; + foreach my $sql (@statements) { + $self->do($sql); + } + $self->_bz_real_schema->set_column($table, $name, $new_def); + $self->_bz_store_real_schema; + } +} + + # XXX - Need to make this cross-db compatible # XXX - This shouldn't print stuff to stdout sub bz_add_field ($$$) { @@ -617,8 +642,11 @@ sub _bz_init_schema_storage { print "Initializing the new Schema storage...\n"; my $store_me = $self->_bz_schema->serialize_abstract(); my $schema_version = $self->_bz_schema->SCHEMA_VERSION; - $self->do("INSERT INTO bz_schema (schema_data, version) VALUES (?,?)", - undef, ($store_me, $schema_version)); + my $sth = $self->prepare("INSERT INTO bz_schema " + ." (schema_data, version) VALUES (?,?)"); + $sth->bind_param(1, $store_me, $self->BLOB_TYPE); + $sth->bind_param(2, Bugzilla::DB::Schema::SCHEMA_VERSION); + $sth->execute(); } # Sanity check elsif ($table_size > 1) { @@ -650,7 +678,7 @@ sub _bz_real_schema { # XXX - Should I do the undef check here instead of in checksetup? $self->{private_real_schema} = - _bz_schema->deserialize_abstract($data, $version); + $self->_bz_schema->deserialize_abstract($data, $version); return $self->{private_real_schema}; } @@ -680,9 +708,12 @@ sub _bz_store_real_schema { # that we read from the database. So we use the actual hash # member instead of the subroutine call. If the hash # member is not defined, we will (and should) fail. - my $store_me = $self->{_bz_real_schema}->serialize_abstract(); - $self->do("UPDATE bz_schema SET schema_data = ?, version = ?", - undef, $store_me, Bugzilla::DB::Schema::SCHEMA_VERSION); + my $store_me = $self->{private_real_schema}->serialize_abstract(); + my $sth = $self->prepare("UPDATE bz_schema + SET schema_data = ?, version = ?"); + $sth->bind_param(1, $store_me, $self->BLOB_TYPE); + $sth->bind_param(2, Bugzilla::DB::Schema::SCHEMA_VERSION); + $sth->execute(); } 1; @@ -714,7 +745,10 @@ Bugzilla::DB - Database access routines, using L # Get the results my @result = $sth->fetchrow_array; - # Schema Changes + # Schema Modification + $dbh->bz_add_column($table, $name, \%definition); + + # Schema Modification (DEPRECATED) $dbh->bz_add_field($table, $column, $definition); $dbh->bz_change_field_type($table, $column, $newtype); $dbh->bz_drop_field($table, $column); @@ -1004,9 +1038,36 @@ These methods return information about data in the database. $column = name of column containing serial data type (scalar) Returns: Last inserted ID (scalar) + =head2 Schema Modification Methods -These methods modify the current Bugzilla schema. +These methods modify the current Bugzilla Schema. + +Where a parameter says "Abstract index/column definition", it returns/takes +information in the formats defined for indexes and columns in +C. + +=over 4 + +=item C + + Description: Adds a new column to a table in the database. Prints out + a brief statement that it did so, to stdout. + Note that you cannot add a NOT NULL column that has no + default -- the database won't know what to set all + the NOT NULL values to. + Params: $table = the table where the column is being added + $name = the name of the new column + \%definition = Abstract column definition for the new column + Returns: nothing + +=back + + +=head2 Deprecated Schema Modification Methods + +These methods modify the current Bugzilla schema, for MySQL only. +Do not use them in new code. =over 4 diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index d246ad542..d4a59ed78 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -32,6 +32,8 @@ package Bugzilla::DB::Schema; use strict; use Bugzilla::Error; +use Bugzilla::Util; + use Storable qw(dclone freeze thaw); =head1 NAME @@ -1155,7 +1157,14 @@ sub get_type_ddl { my $type = $finfo->{TYPE}; die "A valid TYPE was not specified for this column." unless ($type); + my $default = $finfo->{DEFAULT}; + # Replace any abstract default value (such as 'TRUE' or 'FALSE') + # with its database-specific implementation. + if ( defined $default && exists($self->{db_specific}->{$default}) ) { + $default = $self->{db_specific}->{$default}; + } + my $fkref = $self->{enable_references} ? $finfo->{REFERENCES} : undef; my $type_ddl = $self->{db_specific}{$type} || $type; $type_ddl .= " NOT NULL" if ($finfo->{NOTNULL}); @@ -1348,6 +1357,26 @@ sub _get_create_index_ddl { } #eosub--_get_create_index_ddl #-------------------------------------------------------------------------- + +sub get_add_column_ddl { +=item C + + Description: Generate SQL to add a column to a table. + Params: $table - The table containing the column. + $column - The name of the column being added. + \%definition - The new definition for the column, + in standard C format. + Returns: An array of SQL statements. + +=cut + my ($self, $table, $column, $definition) = @_; + + my $statement = "ALTER TABLE $table ADD COLUMN $column " . + $self->get_type_ddl($definition); + + return ($statement); +} + sub get_column_abstract { =item C @@ -1367,7 +1396,8 @@ sub get_column_abstract { # Prevent a possible dereferencing of an undef hash, if the # table doesn't exist. if (exists $self->{abstract_schema}->{$table}) { - return $self->{abstract_schema}->{$table}{FIELDS}{$column}; + my %fields = (@{ $self->{abstract_schema}{$table}{FIELDS} }); + return $fields{$column}; } return undef; } @@ -1390,11 +1420,52 @@ sub get_index_abstract { # Prevent a possible dereferencing of an undef hash, if the # table doesn't exist. if (exists $self->{abstract_schema}->{$table}) { - return $self->{abstract_schema}->{$table}{INDEXES}{$index}; + my %indexes = (@{ $self->{abstract_schema}{$table}{INDEXES} }); + return $indexes{$index}; } return undef; } +sub set_column { + +=item C + + Description: Changes the definition of a column in this Schema object. + If the column doesn't exist, it will be added. + The table that you specify must already exist in the Schema. + NOTE: This does not affect the database on the disk. + Use the C "Schema Modification Methods" + if you want to do that. + Params: $table - The name of the table that the column is on. + $column - The name of the column. + \%new_def - The new definition for the column, in + C format. + Returns: nothing + +=cut + + my ($self, $table, $column, $new_def) = @_; + + my $fields = \@{ $self->{schema}{$table}{FIELDS} }; + my $abstract_fields = \@{ $self->{abstract_schema}{$table}{FIELDS} }; + + my $field_position = lsearch($fields, $column) + 1; + # If the column doesn't exist, then add it. + if (!$field_position) { + push(@$fields, $column); + push(@$fields, $new_def); + push(@$abstract_fields, $column); + push(@$abstract_fields, $new_def); + } + # We're modifying an existing column. + else { + splice(@$fields, $field_position, 1, $new_def); + splice(@$abstract_fields, $field_position, 1, $new_def); + } + + $self->_adjust_schema(); +} + =head1 SERIALIZATION/DESERIALIZATION @@ -1439,14 +1510,14 @@ sub serialize_abstract { sub deserialize_abstract { my ($class, $serialized, $version) = @_; - my %thawed_hash = thaw($serialized); + my $thawed_hash = thaw($serialized); # At this point, we have no backwards-compatibility # code to write, so $version is ignored. # For what $version ought to be used for, see the # "private" section of the SCHEMA_VERSION docs. - return $class->new(undef, \%thawed_hash); + return $class->new(undef, $thawed_hash); } 1; diff --git a/Bugzilla/DB/Schema/Pg.pm b/Bugzilla/DB/Schema/Pg.pm index 795ad53ff..3a45744bb 100644 --- a/Bugzilla/DB/Schema/Pg.pm +++ b/Bugzilla/DB/Schema/Pg.pm @@ -44,12 +44,14 @@ sub _initialize { if ($self->{schema}{$table}{INDEXES}) { foreach my $index (@{ $self->{schema}{$table}{INDEXES} }) { if (ref($index) eq 'HASH') { - delete($index->{TYPE}) if ($index->{TYPE} eq 'FULLTEXT'); + 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 ($index->{TYPE} eq 'FULLTEXT'); + delete($index->{TYPE}) if (exists $index->{TYPE} + && $index->{TYPE} eq 'FULLTEXT'); } } } @@ -85,5 +87,39 @@ sub _initialize { return $self; } #eosub--_initialize -#------------------------------------------------------------------------------ +#-------------------------------------------------------------------- + +# Overridden because Pg has such weird ALTER TABLE problems. +sub get_add_column_ddl { + my ($self, $table, $column, $definition) = @_; + + my @statements; + my $specific = $self->{db_specific}; + + my $type = $definition->{TYPE}; + $type = $specific->{$type} if exists $specific->{$type}; + push(@statements, "ALTER TABLE $table ADD COLUMN $column $type"); + + my $default = $definition->{DEFAULT}; + # Replace any abstract default value (such as 'TRUE' or 'FALSE') + # with its database-specific implementation. + if (defined $default) { + $default = $specific->{$default} if exists $specific->{$default}; + push(@statements, "ALTER TABLE $table ALTER COLUMN $column " + . " SET DEFAULT $default"); + } + + if ($definition->{NOTNULL}) { + push(@statements, "ALTER TABLE $table ALTER COLUMN $column " + . " SET NOT NULL"); + } + + if ($definition->{PRIMARYKEY}) { + push(@statements, "ALTER TABLE $table ALTER COLUMN $column " + . " SET ADD PRIMARY KEY"); + } + + return @statements; +} + 1; -- cgit v1.2.3-24-g4f1b