diff options
Diffstat (limited to 'Bugzilla/DB/Schema')
| -rw-r--r-- | Bugzilla/DB/Schema/Mysql.pm | 603 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Oracle.pm | 772 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Pg.pm | 286 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Sqlite.pm | 414 |
4 files changed, 1082 insertions, 993 deletions
diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm index 79814140a..0b8ee59c3 100644 --- a/Bugzilla/DB/Schema/Mysql.pm +++ b/Bugzilla/DB/Schema/Mysql.pm @@ -34,198 +34,218 @@ use base qw(Bugzilla::DB::Schema); # THIS CONSTANT IS ONLY USED FOR UPGRADES FROM 2.18 OR EARLIER. DON'T # UPDATE IT TO MODERN COLUMN NAMES OR DEFINITIONS. use constant BOOLEAN_MAP => { - bugs => {everconfirmed => 1, reporter_accessible => 1, - cclist_accessible => 1, qacontact_accessible => 1, - assignee_accessible => 1}, - longdescs => {isprivate => 1, already_wrapped => 1}, - attachments => {ispatch => 1, isobsolete => 1, isprivate => 1}, - flags => {is_active => 1}, - flagtypes => {is_active => 1, is_requestable => 1, - is_requesteeble => 1, is_multiplicable => 1}, - fielddefs => {mailhead => 1, obsolete => 1}, - bug_status => {isactive => 1}, - resolution => {isactive => 1}, - bug_severity => {isactive => 1}, - priority => {isactive => 1}, - rep_platform => {isactive => 1}, - op_sys => {isactive => 1}, - profiles => {mybugslink => 1, newemailtech => 1}, - namedqueries => {linkinfooter => 1, watchfordiffs => 1}, - groups => {isbuggroup => 1, isactive => 1}, - group_control_map => {entry => 1, membercontrol => 1, othercontrol => 1, - canedit => 1}, - group_group_map => {isbless => 1}, - user_group_map => {isbless => 1, isderived => 1}, - products => {disallownew => 1}, - series => {public => 1}, - whine_queries => {onemailperbug => 1}, - quips => {approved => 1}, - setting => {is_enabled => 1} + bugs => { + everconfirmed => 1, + reporter_accessible => 1, + cclist_accessible => 1, + qacontact_accessible => 1, + assignee_accessible => 1 + }, + longdescs => {isprivate => 1, already_wrapped => 1}, + attachments => {ispatch => 1, isobsolete => 1, isprivate => 1}, + flags => {is_active => 1}, + flagtypes => { + is_active => 1, + is_requestable => 1, + is_requesteeble => 1, + is_multiplicable => 1 + }, + fielddefs => {mailhead => 1, obsolete => 1}, + bug_status => {isactive => 1}, + resolution => {isactive => 1}, + bug_severity => {isactive => 1}, + priority => {isactive => 1}, + rep_platform => {isactive => 1}, + op_sys => {isactive => 1}, + profiles => {mybugslink => 1, newemailtech => 1}, + namedqueries => {linkinfooter => 1, watchfordiffs => 1}, + groups => {isbuggroup => 1, isactive => 1}, + group_control_map => + {entry => 1, membercontrol => 1, othercontrol => 1, canedit => 1}, + group_group_map => {isbless => 1}, + user_group_map => {isbless => 1, isderived => 1}, + products => {disallownew => 1}, + series => {public => 1}, + whine_queries => {onemailperbug => 1}, + quips => {approved => 1}, + setting => {is_enabled => 1} }; # Maps the db_specific hash backwards, for use in column_info_to_column. use constant REVERSE_MAPPING => { - # Boolean and the SERIAL fields are handled in column_info_to_column, - # and so don't have an entry here. - TINYINT => 'INT1', - SMALLINT => 'INT2', - MEDIUMINT => 'INT3', - INTEGER => 'INT4', - - # All the other types have the same name in their abstract version - # as in their db-specific version, so no reverse mapping is needed. + + # Boolean and the SERIAL fields are handled in column_info_to_column, + # and so don't have an entry here. + TINYINT => 'INT1', + SMALLINT => 'INT2', + MEDIUMINT => 'INT3', + INTEGER => 'INT4', + + # All the other types have the same name in their abstract version + # as in their db-specific version, so no reverse mapping is needed. }; #------------------------------------------------------------------------------ sub _initialize { - my $self = shift; + my $self = shift; + + $self = $self->SUPER::_initialize(@_); - $self = $self->SUPER::_initialize(@_); + $self->{db_specific} = { - $self->{db_specific} = { + BOOLEAN => 'tinyint', + FALSE => '0', + TRUE => '1', - BOOLEAN => 'tinyint', - FALSE => '0', - TRUE => '1', + INT1 => 'tinyint', + INT2 => 'smallint', + INT3 => 'mediumint', + INT4 => 'integer', - INT1 => 'tinyint', - INT2 => 'smallint', - INT3 => 'mediumint', - INT4 => 'integer', + SMALLSERIAL => 'smallint auto_increment', + MEDIUMSERIAL => 'mediumint auto_increment', + INTSERIAL => 'integer auto_increment', - SMALLSERIAL => 'smallint auto_increment', - MEDIUMSERIAL => 'mediumint auto_increment', - INTSERIAL => 'integer auto_increment', + TINYTEXT => 'tinytext', + MEDIUMTEXT => 'mediumtext', + LONGTEXT => 'mediumtext', + TEXT => 'text', - TINYTEXT => 'tinytext', - MEDIUMTEXT => 'mediumtext', - LONGTEXT => 'mediumtext', - TEXT => 'text', + LONGBLOB => 'longblob', - LONGBLOB => 'longblob', + NATIVE_DATETIME => 'datetime', + DATETIME => 'timestamp', + DATE => 'date', + }; - NATIVE_DATETIME => 'datetime', - DATETIME => 'timestamp', - DATE => 'date', - }; + $self->_adjust_schema; - $self->_adjust_schema; + return $self; - return $self; +} #eosub--_initialize -} #eosub--_initialize #------------------------------------------------------------------------------ sub _get_create_table_ddl { - # Returns a "create table" SQL statement. - my($self, $table) = @_; - my $charset = Bugzilla::DB::Mysql->utf8_charset; - my $collate = Bugzilla::DB::Mysql->utf8_collate; - my $row_format = Bugzilla::DB::Mysql->default_row_format($table); - my @parts = ( - $self->SUPER::_get_create_table_ddl($table), - 'ENGINE = InnoDB', - "CHARACTER SET $charset COLLATE $collate", - "ROW_FORMAT=$row_format", - ); - return join(' ', @parts); -} #eosub--_get_create_table_ddl + + # Returns a "create table" SQL statement. + my ($self, $table) = @_; + my $charset = Bugzilla::DB::Mysql->utf8_charset; + my $collate = Bugzilla::DB::Mysql->utf8_collate; + my $row_format = Bugzilla::DB::Mysql->default_row_format($table); + my @parts = ( + $self->SUPER::_get_create_table_ddl($table), 'ENGINE = InnoDB', + "CHARACTER SET $charset COLLATE $collate", "ROW_FORMAT=$row_format", + ); + return join(' ', @parts); +} #eosub--_get_create_table_ddl + #------------------------------------------------------------------------------ sub _get_create_index_ddl { - # Extend superclass method to create FULLTEXT indexes on text fields. - # Returns a "create index" SQL statement. - my($self, $table_name, $index_name, $index_fields, $index_type) = @_; + # Extend superclass method to create FULLTEXT indexes on text fields. + # Returns a "create index" SQL statement. + + my ($self, $table_name, $index_name, $index_fields, $index_type) = @_; - my $sql = "CREATE "; - $sql .= "$index_type " if ($index_type eq 'UNIQUE' - || $index_type eq 'FULLTEXT'); - $sql .= "INDEX \`$index_name\` ON $table_name \(" . - join(", ", @$index_fields) . "\)"; + my $sql = "CREATE "; + $sql .= "$index_type " + if ($index_type eq 'UNIQUE' || $index_type eq 'FULLTEXT'); + $sql .= "INDEX \`$index_name\` ON $table_name \(" + . join(", ", @$index_fields) . "\)"; - return($sql); + return ($sql); + +} #eosub--_get_create_index_ddl -} #eosub--_get_create_index_ddl #-------------------------------------------------------------------- sub get_create_database_sql { - my ($self, $name) = @_; - # We only create as utf8 if we have no params (meaning we're doing - # a new installation) or if the utf8 param is on. - my $charset = Bugzilla::DB::Mysql->utf8_charset; - my $collate = Bugzilla::DB::Mysql->utf8_collate; - return ("CREATE DATABASE $name CHARACTER SET $charset COLLATE $collate"); + my ($self, $name) = @_; + + # We only create as utf8 if we have no params (meaning we're doing + # a new installation) or if the utf8 param is on. + my $charset = Bugzilla::DB::Mysql->utf8_charset; + my $collate = Bugzilla::DB::Mysql->utf8_collate; + return ("CREATE DATABASE $name CHARACTER SET $charset COLLATE $collate"); } # MySQL has a simpler ALTER TABLE syntax than ANSI. sub get_alter_column_ddl { - my ($self, $table, $column, $new_def, $set_nulls_to) = @_; - my $old_def = $self->get_column($table, $column); - my %new_def_copy = %$new_def; - if ($old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) { - # If a column stays a primary key do NOT specify PRIMARY KEY in the - # ALTER TABLE statement. This avoids a MySQL error that two primary - # keys are not allowed. - delete $new_def_copy{PRIMARYKEY}; - } - - my @statements; - - push(@statements, "UPDATE $table SET $column = $set_nulls_to - WHERE $column IS NULL") if defined $set_nulls_to; - - # Calling SET DEFAULT or DROP DEFAULT is *way* faster than calling - # CHANGE COLUMN, so just do that if we're just changing the default. - my %old_defaultless = %$old_def; - my %new_defaultless = %$new_def; - delete $old_defaultless{DEFAULT}; - delete $new_defaultless{DEFAULT}; - if (!$self->columns_equal($old_def, $new_def) - && $self->columns_equal(\%new_defaultless, \%old_defaultless)) - { - if (!defined $new_def->{DEFAULT}) { - push(@statements, - "ALTER TABLE $table ALTER COLUMN $column DROP DEFAULT"); - } - else { - push(@statements, "ALTER TABLE $table ALTER COLUMN $column - SET DEFAULT " . $new_def->{DEFAULT}); - } + my ($self, $table, $column, $new_def, $set_nulls_to) = @_; + my $old_def = $self->get_column($table, $column); + my %new_def_copy = %$new_def; + if ($old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) { + + # If a column stays a primary key do NOT specify PRIMARY KEY in the + # ALTER TABLE statement. This avoids a MySQL error that two primary + # keys are not allowed. + delete $new_def_copy{PRIMARYKEY}; + } + + my @statements; + + push( + @statements, "UPDATE $table SET $column = $set_nulls_to + WHERE $column IS NULL" + ) if defined $set_nulls_to; + + # Calling SET DEFAULT or DROP DEFAULT is *way* faster than calling + # CHANGE COLUMN, so just do that if we're just changing the default. + my %old_defaultless = %$old_def; + my %new_defaultless = %$new_def; + delete $old_defaultless{DEFAULT}; + delete $new_defaultless{DEFAULT}; + if (!$self->columns_equal($old_def, $new_def) + && $self->columns_equal(\%new_defaultless, \%old_defaultless)) + { + if (!defined $new_def->{DEFAULT}) { + push(@statements, "ALTER TABLE $table ALTER COLUMN $column DROP DEFAULT"); } else { - my $new_ddl = $self->get_type_ddl(\%new_def_copy); - push(@statements, "ALTER TABLE $table CHANGE COLUMN - $column $column $new_ddl"); + push( + @statements, "ALTER TABLE $table ALTER COLUMN $column + SET DEFAULT " . $new_def->{DEFAULT} + ); } + } + else { + my $new_ddl = $self->get_type_ddl(\%new_def_copy); + push( + @statements, "ALTER TABLE $table CHANGE COLUMN + $column $column $new_ddl" + ); + } - if ($old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY}) { - # Dropping a PRIMARY KEY needs an explicit DROP PRIMARY KEY - push(@statements, "ALTER TABLE $table DROP PRIMARY KEY"); - } + if ($old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY}) { + + # Dropping a PRIMARY KEY needs an explicit DROP PRIMARY KEY + push(@statements, "ALTER TABLE $table DROP PRIMARY KEY"); + } - return @statements; + return @statements; } sub get_drop_fk_sql { - my ($self, $table, $column, $references) = @_; - my $fk_name = $self->_get_fk_name($table, $column, $references); - my @sql = ("ALTER TABLE $table DROP FOREIGN KEY $fk_name"); - my $dbh = Bugzilla->dbh; - - # MySQL requires, and will create, an index on any column with - # an FK. It will name it after the fk, which we never do. - # So if there's an index named after the fk, we also have to delete it. - if ($dbh->bz_index_info_real($table, $fk_name)) { - push(@sql, $self->get_drop_index_ddl($table, $fk_name)); - } - - return @sql; + my ($self, $table, $column, $references) = @_; + my $fk_name = $self->_get_fk_name($table, $column, $references); + my @sql = ("ALTER TABLE $table DROP FOREIGN KEY $fk_name"); + my $dbh = Bugzilla->dbh; + + # MySQL requires, and will create, an index on any column with + # an FK. It will name it after the fk, which we never do. + # So if there's an index named after the fk, we also have to delete it. + if ($dbh->bz_index_info_real($table, $fk_name)) { + push(@sql, $self->get_drop_index_ddl($table, $fk_name)); + } + + return @sql; } sub get_drop_index_ddl { - my ($self, $table, $name) = @_; - return ("DROP INDEX \`$name\` ON $table"); + my ($self, $table, $name) = @_; + return ("DROP INDEX \`$name\` ON $table"); } # A special function for MySQL, for renaming a lot of indexes. @@ -235,29 +255,31 @@ sub get_drop_index_ddl { # that contains the new index name. # The indexes in %indexes must be in hashref format. sub get_rename_indexes_ddl { - my ($self, $table, %indexes) = @_; - my @keys = keys %indexes or return (); - - my $sql = "ALTER TABLE $table "; - - foreach my $old_name (@keys) { - my $name = $indexes{$old_name}->{NAME}; - my $type = $indexes{$old_name}->{TYPE}; - $type ||= 'INDEX'; - my $fields = join(',', @{$indexes{$old_name}->{FIELDS}}); - # $old_name needs to be escaped, sometimes, because it was - # a reserved word. - $old_name = '`' . $old_name . '`'; - $sql .= " ADD $type $name ($fields), DROP INDEX $old_name,"; - } - # Remove the last comma. - chop($sql); - return ($sql); + my ($self, $table, %indexes) = @_; + my @keys = keys %indexes or return (); + + my $sql = "ALTER TABLE $table "; + + foreach my $old_name (@keys) { + my $name = $indexes{$old_name}->{NAME}; + my $type = $indexes{$old_name}->{TYPE}; + $type ||= 'INDEX'; + my $fields = join(',', @{$indexes{$old_name}->{FIELDS}}); + + # $old_name needs to be escaped, sometimes, because it was + # a reserved word. + $old_name = '`' . $old_name . '`'; + $sql .= " ADD $type $name ($fields), DROP INDEX $old_name,"; + } + + # Remove the last comma. + chop($sql); + return ($sql); } sub get_set_serial_sql { - my ($self, $table, $column, $value) = @_; - return ("ALTER TABLE $table AUTO_INCREMENT = $value"); + my ($self, $table, $column, $value) = @_; + return ("ALTER TABLE $table AUTO_INCREMENT = $value"); } # Converts a DBI column_info output to an abstract column definition. @@ -265,145 +287,158 @@ sub get_set_serial_sql { # although there's a chance that it will also work properly if called # elsewhere. sub column_info_to_column { - my ($self, $column_info) = @_; - - # Unfortunately, we have to break Schema's normal "no database" - # barrier a few times in this function. - my $dbh = Bugzilla->dbh; - - my $table = $column_info->{TABLE_NAME}; - my $col_name = $column_info->{COLUMN_NAME}; - - my $column = {}; - - ($column->{NOTNULL} = 1) if $column_info->{NULLABLE} == 0; - - if ($column_info->{mysql_is_pri_key}) { - # In MySQL, if a table has no PK, but it has a UNIQUE index, - # that index will show up as the PK. So we have to eliminate - # that possibility. - # Unfortunately, the only way to definitely solve this is - # to break Schema's standard of not touching the live database - # and check if the index called PRIMARY is on that field. - my $pri_index = $dbh->bz_index_info_real($table, 'PRIMARY'); - if ( $pri_index && grep($_ eq $col_name, @{$pri_index->{FIELDS}}) ) { - $column->{PRIMARYKEY} = 1; - } - } + my ($self, $column_info) = @_; - # MySQL frequently defines a default for a field even when we - # didn't explicitly set one. So we have to have some special - # hacks to determine whether or not we should actually put - # a default in the abstract schema for this field. - if (defined $column_info->{COLUMN_DEF}) { - # The defaults that MySQL inputs automatically are usually - # something that would be considered "false" by perl, either - # a 0 or an empty string. (Except for datetime and decimal - # fields, which have their own special auto-defaults.) - # - # Here's how we handle this: If it exists in the schema - # without a default, then we don't use the default. If it - # doesn't exist in the schema, then we're either going to - # be dropping it soon, or it's a custom end-user column, in which - # case having a bogus default won't harm anything. - my $schema_column = $self->get_column($table, $col_name); - unless ( (!$column_info->{COLUMN_DEF} - || $column_info->{COLUMN_DEF} eq '0000-00-00 00:00:00' - || $column_info->{COLUMN_DEF} eq '0.00') - && $schema_column - && !exists $schema_column->{DEFAULT}) { - - my $default = $column_info->{COLUMN_DEF}; - # Schema uses '0' for the defaults for decimal fields. - $default = 0 if $default =~ /^0\.0+$/; - # If we're not a number, we're a string and need to be - # quoted. - $default = $dbh->quote($default) if !($default =~ /^(-)?(\d+)(.\d+)?$/); - $column->{DEFAULT} = $default; - } - } + # Unfortunately, we have to break Schema's normal "no database" + # barrier a few times in this function. + my $dbh = Bugzilla->dbh; - my $type = $column_info->{TYPE_NAME}; + my $table = $column_info->{TABLE_NAME}; + my $col_name = $column_info->{COLUMN_NAME}; - # Certain types of columns need the size/precision appended. - if ($type =~ /CHAR$/ || $type eq 'DECIMAL') { - # This is nicely lowercase and has the size/precision appended. - $type = $column_info->{mysql_type_name}; - } + my $column = {}; - # If we're a tinyint, we could be either a BOOLEAN or an INT1. - # Only the BOOLEAN_MAP knows the difference. - elsif ($type eq 'TINYINT' && exists BOOLEAN_MAP->{$table} - && exists BOOLEAN_MAP->{$table}->{$col_name}) { - $type = 'BOOLEAN'; - if (exists $column->{DEFAULT}) { - $column->{DEFAULT} = $column->{DEFAULT} ? 'TRUE' : 'FALSE'; - } - } + ($column->{NOTNULL} = 1) if $column_info->{NULLABLE} == 0; - # We also need to check if we're an auto_increment field. - elsif ($type =~ /INT/) { - # Unfortunately, the only way to do this in DBI is to query the - # database, so we have to break the rule here that Schema normally - # doesn't touch the live DB. - my $ref_sth = $dbh->prepare( - "SELECT $col_name FROM $table LIMIT 1"); - $ref_sth->execute; - if ($ref_sth->{mysql_is_auto_increment}->[0]) { - if ($type eq 'MEDIUMINT') { - $type = 'MEDIUMSERIAL'; - } - elsif ($type eq 'SMALLINT') { - $type = 'SMALLSERIAL'; - } - else { - $type = 'INTSERIAL'; - } - } - $ref_sth->finish; + if ($column_info->{mysql_is_pri_key}) { + # In MySQL, if a table has no PK, but it has a UNIQUE index, + # that index will show up as the PK. So we have to eliminate + # that possibility. + # Unfortunately, the only way to definitely solve this is + # to break Schema's standard of not touching the live database + # and check if the index called PRIMARY is on that field. + my $pri_index = $dbh->bz_index_info_real($table, 'PRIMARY'); + if ($pri_index && grep($_ eq $col_name, @{$pri_index->{FIELDS}})) { + $column->{PRIMARYKEY} = 1; } + } + + # MySQL frequently defines a default for a field even when we + # didn't explicitly set one. So we have to have some special + # hacks to determine whether or not we should actually put + # a default in the abstract schema for this field. + if (defined $column_info->{COLUMN_DEF}) { + + # The defaults that MySQL inputs automatically are usually + # something that would be considered "false" by perl, either + # a 0 or an empty string. (Except for datetime and decimal + # fields, which have their own special auto-defaults.) + # + # Here's how we handle this: If it exists in the schema + # without a default, then we don't use the default. If it + # doesn't exist in the schema, then we're either going to + # be dropping it soon, or it's a custom end-user column, in which + # case having a bogus default won't harm anything. + my $schema_column = $self->get_column($table, $col_name); + unless ( + ( + !$column_info->{COLUMN_DEF} + || $column_info->{COLUMN_DEF} eq '0000-00-00 00:00:00' + || $column_info->{COLUMN_DEF} eq '0.00' + ) + && $schema_column + && !exists $schema_column->{DEFAULT} + ) + { - # For all other db-specific types, check if they exist in - # REVERSE_MAPPING and use the type found there. - if (exists REVERSE_MAPPING->{$type}) { - $type = REVERSE_MAPPING->{$type}; + my $default = $column_info->{COLUMN_DEF}; + + # Schema uses '0' for the defaults for decimal fields. + $default = 0 if $default =~ /^0\.0+$/; + + # If we're not a number, we're a string and need to be + # quoted. + $default = $dbh->quote($default) if !($default =~ /^(-)?(\d+)(.\d+)?$/); + $column->{DEFAULT} = $default; + } + } + + my $type = $column_info->{TYPE_NAME}; + + # Certain types of columns need the size/precision appended. + if ($type =~ /CHAR$/ || $type eq 'DECIMAL') { + + # This is nicely lowercase and has the size/precision appended. + $type = $column_info->{mysql_type_name}; + } + + # If we're a tinyint, we could be either a BOOLEAN or an INT1. + # Only the BOOLEAN_MAP knows the difference. + elsif ($type eq 'TINYINT' + && exists BOOLEAN_MAP->{$table} + && exists BOOLEAN_MAP->{$table}->{$col_name}) + { + $type = 'BOOLEAN'; + if (exists $column->{DEFAULT}) { + $column->{DEFAULT} = $column->{DEFAULT} ? 'TRUE' : 'FALSE'; + } + } + + # We also need to check if we're an auto_increment field. + elsif ($type =~ /INT/) { + + # Unfortunately, the only way to do this in DBI is to query the + # database, so we have to break the rule here that Schema normally + # doesn't touch the live DB. + my $ref_sth = $dbh->prepare("SELECT $col_name FROM $table LIMIT 1"); + $ref_sth->execute; + if ($ref_sth->{mysql_is_auto_increment}->[0]) { + if ($type eq 'MEDIUMINT') { + $type = 'MEDIUMSERIAL'; + } + elsif ($type eq 'SMALLINT') { + $type = 'SMALLSERIAL'; + } + else { + $type = 'INTSERIAL'; + } } + $ref_sth->finish; - $column->{TYPE} = $type; + } - #print "$table.$col_name: " . Data::Dumper->Dump([$column]) . "\n"; + # For all other db-specific types, check if they exist in + # REVERSE_MAPPING and use the type found there. + if (exists REVERSE_MAPPING->{$type}) { + $type = REVERSE_MAPPING->{$type}; + } - return $column; + $column->{TYPE} = $type; + + #print "$table.$col_name: " . Data::Dumper->Dump([$column]) . "\n"; + + return $column; } sub get_rename_column_ddl { - my ($self, $table, $old_name, $new_name) = @_; - my $def = $self->get_type_ddl($self->get_column($table, $old_name)); - # MySQL doesn't like having the PRIMARY KEY statement in a rename. - $def =~ s/PRIMARY KEY//i; - return ("ALTER TABLE $table CHANGE COLUMN $old_name $new_name $def"); + my ($self, $table, $old_name, $new_name) = @_; + my $def = $self->get_type_ddl($self->get_column($table, $old_name)); + + # MySQL doesn't like having the PRIMARY KEY statement in a rename. + $def =~ s/PRIMARY KEY//i; + return ("ALTER TABLE $table CHANGE COLUMN $old_name $new_name $def"); } sub get_type_ddl { - my $self = shift; - my $type_ddl = $self->SUPER::get_type_ddl(@_); - - # TIMESTAMPS as of 5.6.6 still default to - # 'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' - # unless explicitly setup in the table definition. This will change in future releases - # and can be disabled by using 'explicit_defaults_for_timestamp = 1' in my.cnf. - # So instead, we explicitly setup TIMESTAMP types to not be automatic. - if ($type_ddl =~ /^timestamp/i) { - if ($type_ddl !~ /NOT NULL/) { - $type_ddl .= ' NULL DEFAULT NULL'; - } - if ($type_ddl =~ /NOT NULL/ && $type_ddl !~ /DEFAULT/) { - $type_ddl .= ' DEFAULT CURRENT_TIMESTAMP'; - } + my $self = shift; + my $type_ddl = $self->SUPER::get_type_ddl(@_); + +# TIMESTAMPS as of 5.6.6 still default to +# 'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' +# unless explicitly setup in the table definition. This will change in future releases +# and can be disabled by using 'explicit_defaults_for_timestamp = 1' in my.cnf. +# So instead, we explicitly setup TIMESTAMP types to not be automatic. + if ($type_ddl =~ /^timestamp/i) { + if ($type_ddl !~ /NOT NULL/) { + $type_ddl .= ' NULL DEFAULT NULL'; + } + if ($type_ddl =~ /NOT NULL/ && $type_ddl !~ /DEFAULT/) { + $type_ddl .= ' DEFAULT CURRENT_TIMESTAMP'; } + } - return $type_ddl; + return $type_ddl; } 1; diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm index b67ddfd59..36f957820 100644 --- a/Bugzilla/DB/Schema/Oracle.pm +++ b/Bugzilla/DB/Schema/Oracle.pm @@ -21,8 +21,9 @@ use base qw(Bugzilla::DB::Schema); use Carp qw(confess); use Bugzilla::Util; -use constant ADD_COLUMN => 'ADD'; +use constant ADD_COLUMN => 'ADD'; use constant MULTIPLE_FKS_IN_ALTER => 0; + # Whether this is true or not, this is what it needs to be in order for # hash_identifier to maintain backwards compatibility with versions before # 3.2rc2. @@ -31,91 +32,95 @@ use constant MAX_IDENTIFIER_LEN => 27; #------------------------------------------------------------------------------ sub _initialize { - my $self = shift; + my $self = shift; + + $self = $self->SUPER::_initialize(@_); - $self = $self->SUPER::_initialize(@_); + $self->{db_specific} = { - $self->{db_specific} = { + BOOLEAN => 'integer', + FALSE => '0', + TRUE => '1', - BOOLEAN => 'integer', - FALSE => '0', - TRUE => '1', + INT1 => 'integer', + INT2 => 'integer', + INT3 => 'integer', + INT4 => 'integer', - INT1 => 'integer', - INT2 => 'integer', - INT3 => 'integer', - INT4 => 'integer', + SMALLSERIAL => 'integer', + MEDIUMSERIAL => 'integer', + INTSERIAL => 'integer', - SMALLSERIAL => 'integer', - MEDIUMSERIAL => 'integer', - INTSERIAL => 'integer', + TINYTEXT => 'varchar(255)', + MEDIUMTEXT => 'varchar(4000)', + LONGTEXT => 'clob', - TINYTEXT => 'varchar(255)', - MEDIUMTEXT => 'varchar(4000)', - LONGTEXT => 'clob', + LONGBLOB => 'blob', - LONGBLOB => 'blob', + DATETIME => 'date', + DATE => 'date', + }; - DATETIME => 'date', - DATE => 'date', - }; + $self->_adjust_schema; - $self->_adjust_schema; + return $self; - return $self; +} #eosub--_initialize -} #eosub--_initialize #-------------------------------------------------------------------- sub get_table_ddl { - my $self = shift; - my $table = shift; - unshift @_, $table; - my @ddl = $self->SUPER::get_table_ddl(@_); - - my @fields = @{ $self->{abstract_schema}{$table}{FIELDS} || [] }; - while (@fields) { - my $field_name = shift @fields; - my $field_info = shift @fields; - # Create triggers to deal with empty string. - if ( $field_info->{TYPE} =~ /varchar|TEXT/i - && $field_info->{NOTNULL} ) { - push (@ddl, _get_notnull_trigger_ddl($table, $field_name)); - } - # Create sequences and triggers to emulate SERIAL datatypes. - if ( $field_info->{TYPE} =~ /SERIAL/i ) { - push (@ddl, $self->_get_create_seq_ddl($table, $field_name)); - } + my $self = shift; + my $table = shift; + unshift @_, $table; + my @ddl = $self->SUPER::get_table_ddl(@_); + + my @fields = @{$self->{abstract_schema}{$table}{FIELDS} || []}; + while (@fields) { + my $field_name = shift @fields; + my $field_info = shift @fields; + + # Create triggers to deal with empty string. + if ($field_info->{TYPE} =~ /varchar|TEXT/i && $field_info->{NOTNULL}) { + push(@ddl, _get_notnull_trigger_ddl($table, $field_name)); } - return @ddl; -} #eosub--get_table_ddl + # Create sequences and triggers to emulate SERIAL datatypes. + if ($field_info->{TYPE} =~ /SERIAL/i) { + push(@ddl, $self->_get_create_seq_ddl($table, $field_name)); + } + } + return @ddl; + +} #eosub--get_table_ddl # Extend superclass method to create Oracle Text indexes if index type # is FULLTEXT from schema. Returns a "create index" SQL statement. sub _get_create_index_ddl { - my ($self, $table_name, $index_name, $index_fields, $index_type) = @_; - $index_name = "idx_" . $self->_hash_identifier($index_name); - if ($index_type eq 'FULLTEXT') { - my $sql = "CREATE INDEX $index_name ON $table_name (" - . join(',',@$index_fields) - . ") INDEXTYPE IS CTXSYS.CONTEXT " - . " PARAMETERS('LEXER BZ_LEX SYNC(ON COMMIT)')" ; - return $sql; - } - - return($self->SUPER::_get_create_index_ddl($table_name, $index_name, - $index_fields, $index_type)); + my ($self, $table_name, $index_name, $index_fields, $index_type) = @_; + $index_name = "idx_" . $self->_hash_identifier($index_name); + if ($index_type eq 'FULLTEXT') { + my $sql + = "CREATE INDEX $index_name ON $table_name (" + . join(',', @$index_fields) + . ") INDEXTYPE IS CTXSYS.CONTEXT " + . " PARAMETERS('LEXER BZ_LEX SYNC(ON COMMIT)')"; + return $sql; + } + + return ($self->SUPER::_get_create_index_ddl( + $table_name, $index_name, $index_fields, $index_type + )); } sub get_drop_index_ddl { - my $self = shift; - my ($table, $name) = @_; + my $self = shift; + my ($table, $name) = @_; - $name = 'idx_' . $self->_hash_identifier($name); - return $self->SUPER::get_drop_index_ddl($table, $name); + $name = 'idx_' . $self->_hash_identifier($name); + return $self->SUPER::get_drop_index_ddl($table, $name); } # Oracle supports the use of FOREIGN KEY integrity constraints @@ -124,30 +129,31 @@ sub get_drop_index_ddl { # - Delete CASCADE # - Delete SET NULL sub get_fk_ddl { - my $self = shift; - my $ddl = $self->SUPER::get_fk_ddl(@_); + my $self = shift; + my $ddl = $self->SUPER::get_fk_ddl(@_); - # iThe Bugzilla Oracle driver implements UPDATE via a trigger. - $ddl =~ s/ON UPDATE \S+//i; - # RESTRICT is the default for DELETE on Oracle and may not be specified. - $ddl =~ s/ON DELETE RESTRICT//i; + # iThe Bugzilla Oracle driver implements UPDATE via a trigger. + $ddl =~ s/ON UPDATE \S+//i; - return $ddl; + # RESTRICT is the default for DELETE on Oracle and may not be specified. + $ddl =~ s/ON DELETE RESTRICT//i; + + return $ddl; } sub get_add_fks_sql { - my $self = shift; - my ($table, $column_fks) = @_; - my @sql = $self->SUPER::get_add_fks_sql(@_); - - foreach my $column (keys %$column_fks) { - my $fk = $column_fks->{$column}; - next if $fk->{UPDATE} && uc($fk->{UPDATE}) ne 'CASCADE'; - my $fk_name = $self->_get_fk_name($table, $column, $fk); - my $to_column = $fk->{COLUMN}; - my $to_table = $fk->{TABLE}; - - my $trigger = <<END; + my $self = shift; + my ($table, $column_fks) = @_; + my @sql = $self->SUPER::get_add_fks_sql(@_); + + foreach my $column (keys %$column_fks) { + my $fk = $column_fks->{$column}; + next if $fk->{UPDATE} && uc($fk->{UPDATE}) ne 'CASCADE'; + my $fk_name = $self->_get_fk_name($table, $column, $fk); + my $to_column = $fk->{COLUMN}; + my $to_table = $fk->{TABLE}; + + my $trigger = <<END; CREATE OR REPLACE TRIGGER ${fk_name}_UC AFTER UPDATE OF $to_column ON $to_table REFERENCING NEW AS NEW OLD AS OLD @@ -158,350 +164,370 @@ CREATE OR REPLACE TRIGGER ${fk_name}_UC WHERE $column = :OLD.$to_column; END ${fk_name}_UC; END - push(@sql, $trigger); - } + push(@sql, $trigger); + } - return @sql; + return @sql; } sub get_drop_fk_sql { - my $self = shift; - my ($table, $column, $references) = @_; - my $fk_name = $self->_get_fk_name(@_); - my @sql; - if (!$references->{UPDATE} || $references->{UPDATE} =~ /CASCADE/i) { - push(@sql, "DROP TRIGGER ${fk_name}_uc"); - } - push(@sql, $self->SUPER::get_drop_fk_sql(@_)); - return @sql; + my $self = shift; + my ($table, $column, $references) = @_; + my $fk_name = $self->_get_fk_name(@_); + my @sql; + if (!$references->{UPDATE} || $references->{UPDATE} =~ /CASCADE/i) { + push(@sql, "DROP TRIGGER ${fk_name}_uc"); + } + push(@sql, $self->SUPER::get_drop_fk_sql(@_)); + return @sql; } sub _get_fk_name { - my ($self, $table, $column, $references) = @_; - my $to_table = $references->{TABLE}; - my $to_column = $references->{COLUMN}; - my $fk_name = "${table}_${column}_${to_table}_${to_column}"; - $fk_name = "fk_" . $self->_hash_identifier($fk_name); + my ($self, $table, $column, $references) = @_; + my $to_table = $references->{TABLE}; + my $to_column = $references->{COLUMN}; + my $fk_name = "${table}_${column}_${to_table}_${to_column}"; + $fk_name = "fk_" . $self->_hash_identifier($fk_name); - return $fk_name; + return $fk_name; } sub get_add_column_ddl { - my $self = shift; - my ($table, $column, $definition, $init_value) = @_; - my @sql; - - # Create sequences and triggers to emulate SERIAL datatypes. - if ($definition->{TYPE} =~ /SERIAL/i) { - # Clone the definition to not alter the original one. - my %def = %$definition; - # Oracle requires to define the column is several steps. - my $pk = delete $def{PRIMARYKEY}; - my $notnull = delete $def{NOTNULL}; - @sql = $self->SUPER::get_add_column_ddl($table, $column, \%def, $init_value); - push(@sql, $self->_get_create_seq_ddl($table, $column)); - push(@sql, "UPDATE $table SET $column = ${table}_${column}_SEQ.NEXTVAL"); - push(@sql, "ALTER TABLE $table MODIFY $column NOT NULL") if $notnull; - push(@sql, "ALTER TABLE $table ADD PRIMARY KEY ($column)") if $pk; - } - else { - @sql = $self->SUPER::get_add_column_ddl(@_); - # Create triggers to deal with empty string. - if ($definition->{TYPE} =~ /varchar|TEXT/i && $definition->{NOTNULL}) { - push(@sql, _get_notnull_trigger_ddl($table, $column)); - } + my $self = shift; + my ($table, $column, $definition, $init_value) = @_; + my @sql; + + # Create sequences and triggers to emulate SERIAL datatypes. + if ($definition->{TYPE} =~ /SERIAL/i) { + + # Clone the definition to not alter the original one. + my %def = %$definition; + + # Oracle requires to define the column is several steps. + my $pk = delete $def{PRIMARYKEY}; + my $notnull = delete $def{NOTNULL}; + @sql = $self->SUPER::get_add_column_ddl($table, $column, \%def, $init_value); + push(@sql, $self->_get_create_seq_ddl($table, $column)); + push(@sql, "UPDATE $table SET $column = ${table}_${column}_SEQ.NEXTVAL"); + push(@sql, "ALTER TABLE $table MODIFY $column NOT NULL") if $notnull; + push(@sql, "ALTER TABLE $table ADD PRIMARY KEY ($column)") if $pk; + } + else { + @sql = $self->SUPER::get_add_column_ddl(@_); + + # Create triggers to deal with empty string. + if ($definition->{TYPE} =~ /varchar|TEXT/i && $definition->{NOTNULL}) { + push(@sql, _get_notnull_trigger_ddl($table, $column)); } + } - return @sql; + return @sql; } sub get_alter_column_ddl { - my ($self, $table, $column, $new_def, $set_nulls_to) = @_; - - my @statements; - my $old_def = $self->get_column_abstract($table, $column); - my $specific = $self->{db_specific}; - - # If the types have changed, we have to deal with that. - if (uc(trim($old_def->{TYPE})) ne uc(trim($new_def->{TYPE}))) { - push(@statements, $self->_get_alter_type_sql($table, $column, - $new_def, $old_def)); - } - - my $default = $new_def->{DEFAULT}; - my $default_old = $old_def->{DEFAULT}; - - if (defined $default) { - $default = $specific->{$default} if exists $specific->{$default}; - } - # This first condition prevents "uninitialized value" errors. - if (!defined $default && !defined $default_old) { - # Do Nothing - } - # If we went from having a default to not having one - elsif (!defined $default && defined $default_old) { - push(@statements, "ALTER TABLE $table MODIFY $column" - . " DEFAULT NULL"); - } - # If we went from no default to a default, or we changed the default. - elsif ( (defined $default && !defined $default_old) || - ($default ne $default_old) ) - { - push(@statements, "ALTER TABLE $table MODIFY $column " - . " DEFAULT $default"); - } - - # 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, "ALTER TABLE $table MODIFY $column" - . " NOT NULL"); - push (@statements, _get_notnull_trigger_ddl($table, $column)) - if $old_def->{TYPE} =~ /varchar|text/i - && $new_def->{TYPE} =~ /varchar|text/i; - } - # If we went from NOT NULL to NULL - elsif ($old_def->{NOTNULL} && !$new_def->{NOTNULL}) { - push(@statements, "ALTER TABLE $table MODIFY $column" - . " NULL"); - push(@statements, "DROP TRIGGER ${table}_${column}") - if $new_def->{TYPE} =~ /varchar|text/i - && $old_def->{TYPE} =~ /varchar|text/i; - } - - # If we went from not being a PRIMARY KEY to being a PRIMARY KEY. - if (!$old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) { - push(@statements, "ALTER TABLE $table ADD PRIMARY KEY ($column)"); - } - # If we went from being a PK to not being a PK - elsif ( $old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY} ) { - push(@statements, "ALTER TABLE $table DROP PRIMARY KEY"); + my ($self, $table, $column, $new_def, $set_nulls_to) = @_; + + my @statements; + my $old_def = $self->get_column_abstract($table, $column); + my $specific = $self->{db_specific}; + + # If the types have changed, we have to deal with that. + if (uc(trim($old_def->{TYPE})) ne uc(trim($new_def->{TYPE}))) { + push(@statements, + $self->_get_alter_type_sql($table, $column, $new_def, $old_def)); + } + + my $default = $new_def->{DEFAULT}; + my $default_old = $old_def->{DEFAULT}; + + if (defined $default) { + $default = $specific->{$default} if exists $specific->{$default}; + } + + # This first condition prevents "uninitialized value" errors. + if (!defined $default && !defined $default_old) { + + # Do Nothing + } + + # If we went from having a default to not having one + elsif (!defined $default && defined $default_old) { + push(@statements, "ALTER TABLE $table MODIFY $column" . " DEFAULT NULL"); + } + + # If we went from no default to a default, or we changed the default. + elsif ((defined $default && !defined $default_old) + || ($default ne $default_old)) + { + push(@statements, "ALTER TABLE $table MODIFY $column " . " DEFAULT $default"); + } + + # 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"); } - - return @statements; + push(@statements, "ALTER TABLE $table MODIFY $column" . " NOT NULL"); + push(@statements, _get_notnull_trigger_ddl($table, $column)) + if $old_def->{TYPE} =~ /varchar|text/i && $new_def->{TYPE} =~ /varchar|text/i; + } + + # If we went from NOT NULL to NULL + elsif ($old_def->{NOTNULL} && !$new_def->{NOTNULL}) { + push(@statements, "ALTER TABLE $table MODIFY $column" . " NULL"); + push(@statements, "DROP TRIGGER ${table}_${column}") + if $new_def->{TYPE} =~ /varchar|text/i && $old_def->{TYPE} =~ /varchar|text/i; + } + + # If we went from not being a PRIMARY KEY to being a PRIMARY KEY. + if (!$old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) { + push(@statements, "ALTER TABLE $table ADD PRIMARY KEY ($column)"); + } + + # If we went from being a PK to not being a PK + elsif ($old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY}) { + push(@statements, "ALTER TABLE $table DROP PRIMARY KEY"); + } + + return @statements; } sub _get_alter_type_sql { - my ($self, $table, $column, $new_def, $old_def) = @_; - my @statements; - - my $type = $new_def->{TYPE}; - $type = $self->{db_specific}->{$type} - if exists $self->{db_specific}->{$type}; - - if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { - die("You cannot specify a DEFAULT on a SERIAL-type column.") - if $new_def->{DEFAULT}; - } - - if ( ($old_def->{TYPE} =~ /LONGTEXT/i && $new_def->{TYPE} !~ /LONGTEXT/i) - || ($old_def->{TYPE} !~ /LONGTEXT/i && $new_def->{TYPE} =~ /LONGTEXT/i) - ) { - # LONG to VARCHAR or VARCHAR to LONG is not allowed in Oracle, - # just a way to work around. - # Determine whether column_temp is already exist. - my $dbh=Bugzilla->dbh; - my $column_exist = $dbh->selectcol_arrayref( - "SELECT CNAME FROM COL WHERE TNAME = UPPER(?) AND - CNAME = UPPER(?)", undef,$table,$column . "_temp"); - if(!@$column_exist) { - push(@statements, - "ALTER TABLE $table ADD ${column}_temp $type"); - } - push(@statements, "UPDATE $table SET ${column}_temp = $column"); - push(@statements, "COMMIT"); - push(@statements, "ALTER TABLE $table DROP COLUMN $column"); - push(@statements, - "ALTER TABLE $table RENAME COLUMN ${column}_temp TO $column"); - } else { - push(@statements, "ALTER TABLE $table MODIFY $column $type"); - } - - if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { - push(@statements, _get_create_seq_ddl($table, $column)); + my ($self, $table, $column, $new_def, $old_def) = @_; + my @statements; + + my $type = $new_def->{TYPE}; + $type = $self->{db_specific}->{$type} if exists $self->{db_specific}->{$type}; + + if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { + die("You cannot specify a DEFAULT on a SERIAL-type column.") + if $new_def->{DEFAULT}; + } + + if ( ($old_def->{TYPE} =~ /LONGTEXT/i && $new_def->{TYPE} !~ /LONGTEXT/i) + || ($old_def->{TYPE} !~ /LONGTEXT/i && $new_def->{TYPE} =~ /LONGTEXT/i)) + { + # LONG to VARCHAR or VARCHAR to LONG is not allowed in Oracle, + # just a way to work around. + # Determine whether column_temp is already exist. + my $dbh = Bugzilla->dbh; + my $column_exist = $dbh->selectcol_arrayref( + "SELECT CNAME FROM COL WHERE TNAME = UPPER(?) AND + CNAME = UPPER(?)", undef, $table, $column . "_temp" + ); + if (!@$column_exist) { + push(@statements, "ALTER TABLE $table ADD ${column}_temp $type"); } - - # If this column is no longer SERIAL, we need to drop the sequence - # that went along with it. - if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) { - push(@statements, "DROP SEQUENCE ${table}_${column}_SEQ"); - push(@statements, "DROP TRIGGER ${table}_${column}_TR"); - } - - # If this column is changed to type TEXT/VARCHAR, we need to deal with - # empty string. - if ( $old_def->{TYPE} !~ /varchar|text/i - && $new_def->{TYPE} =~ /varchar|text/i - && $new_def->{NOTNULL} ) - { - push (@statements, _get_notnull_trigger_ddl($table, $column)); - } - # If this column is no longer TEXT/VARCHAR, we need to drop the trigger - # that went along with it. - if ( $old_def->{TYPE} =~ /varchar|text/i - && $old_def->{NOTNULL} - && $new_def->{TYPE} !~ /varchar|text/i ) - { - push(@statements, "DROP TRIGGER ${table}_${column}"); - } - return @statements; + push(@statements, "UPDATE $table SET ${column}_temp = $column"); + push(@statements, "COMMIT"); + push(@statements, "ALTER TABLE $table DROP COLUMN $column"); + push(@statements, "ALTER TABLE $table RENAME COLUMN ${column}_temp TO $column"); + } + else { + push(@statements, "ALTER TABLE $table MODIFY $column $type"); + } + + if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { + push(@statements, _get_create_seq_ddl($table, $column)); + } + + # If this column is no longer SERIAL, we need to drop the sequence + # that went along with it. + if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) { + push(@statements, "DROP SEQUENCE ${table}_${column}_SEQ"); + push(@statements, "DROP TRIGGER ${table}_${column}_TR"); + } + + # If this column is changed to type TEXT/VARCHAR, we need to deal with + # empty string. + if ( $old_def->{TYPE} !~ /varchar|text/i + && $new_def->{TYPE} =~ /varchar|text/i + && $new_def->{NOTNULL}) + { + push(@statements, _get_notnull_trigger_ddl($table, $column)); + } + + # If this column is no longer TEXT/VARCHAR, we need to drop the trigger + # that went along with it. + if ( $old_def->{TYPE} =~ /varchar|text/i + && $old_def->{NOTNULL} + && $new_def->{TYPE} !~ /varchar|text/i) + { + push(@statements, "DROP TRIGGER ${table}_${column}"); + } + return @statements; } sub get_rename_column_ddl { - my ($self, $table, $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 $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. - 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} ) { - push(@sql, _get_notnull_trigger_ddl($table,$new_name)); - push(@sql, "DROP TRIGGER ${table}_${old_name}"); - } - return @sql; + my ($self, $table, $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 $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. + 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}) { + push(@sql, _get_notnull_trigger_ddl($table, $new_name)); + push(@sql, "DROP TRIGGER ${table}_${old_name}"); + } + return @sql; } sub get_drop_column_ddl { - my $self = shift; - my ($table, $column) = @_; - my @sql; - push(@sql, $self->SUPER::get_drop_column_ddl(@_)); - my $dbh=Bugzilla->dbh; - my $trigger_name = uc($table . "_" . $column); - my $exist_trigger = $dbh->selectcol_arrayref( - "SELECT OBJECT_NAME FROM USER_OBJECTS - WHERE OBJECT_NAME = ?", undef, $trigger_name); - if(@$exist_trigger) { - push(@sql, "DROP TRIGGER $trigger_name"); - } - # If this column is of type SERIAL, we need to drop the sequence - # and trigger that went along with it. - my $def = $self->get_column_abstract($table, $column); - if ($def->{TYPE} =~ /SERIAL/i) { - push(@sql, "DROP SEQUENCE ${table}_${column}_SEQ"); - push(@sql, "DROP TRIGGER ${table}_${column}_TR"); - } - return @sql; + my $self = shift; + my ($table, $column) = @_; + my @sql; + push(@sql, $self->SUPER::get_drop_column_ddl(@_)); + my $dbh = Bugzilla->dbh; + my $trigger_name = uc($table . "_" . $column); + my $exist_trigger = $dbh->selectcol_arrayref( + "SELECT OBJECT_NAME FROM USER_OBJECTS + WHERE OBJECT_NAME = ?", undef, $trigger_name + ); + if (@$exist_trigger) { + push(@sql, "DROP TRIGGER $trigger_name"); + } + + # If this column is of type SERIAL, we need to drop the sequence + # and trigger that went along with it. + my $def = $self->get_column_abstract($table, $column); + if ($def->{TYPE} =~ /SERIAL/i) { + push(@sql, "DROP SEQUENCE ${table}_${column}_SEQ"); + push(@sql, "DROP TRIGGER ${table}_${column}_TR"); + } + 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 ($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}"); - } + 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; + 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"); - } + 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"); + } + push(@sql, "DROP TABLE $name CASCADE CONSTRAINTS PURGE"); - return @sql; + return @sql; } sub _get_notnull_trigger_ddl { - my ($table, $column) = @_; - - my $notnull_sql = "CREATE OR REPLACE TRIGGER " - . " ${table}_${column}" - . " BEFORE INSERT OR UPDATE ON ". $table - . " FOR EACH ROW" - . " BEGIN " - . " IF :NEW.". $column ." IS NULL THEN " - . " SELECT '" . Bugzilla::DB::Oracle->EMPTY_STRING - . "' INTO :NEW.". $column ." FROM DUAL; " - . " END IF; " - . " END ".$table.";"; - return $notnull_sql; + my ($table, $column) = @_; + + my $notnull_sql + = "CREATE OR REPLACE TRIGGER " + . " ${table}_${column}" + . " BEFORE INSERT OR UPDATE ON " + . $table + . " FOR EACH ROW" + . " BEGIN " + . " IF :NEW." + . $column + . " IS NULL THEN " + . " SELECT '" + . Bugzilla::DB::Oracle->EMPTY_STRING + . "' INTO :NEW." + . $column + . " FROM DUAL; " + . " END IF; " . " END " + . $table . ";"; + return $notnull_sql; } sub _get_create_seq_ddl { - my ($self, $table, $column, $start_with) = @_; - $start_with ||= 1; - my @ddl; - my $seq_name = "${table}_${column}_SEQ"; - my $seq_sql = "CREATE SEQUENCE $seq_name " - . " INCREMENT BY 1 " - . " START WITH $start_with " - . " NOMAXVALUE " - . " NOCYCLE " - . " NOCACHE"; - push (@ddl, $seq_sql); - push(@ddl, $self->_get_create_trigger_ddl($table, $column, $seq_name)); - - return @ddl; + my ($self, $table, $column, $start_with) = @_; + $start_with ||= 1; + my @ddl; + my $seq_name = "${table}_${column}_SEQ"; + my $seq_sql + = "CREATE SEQUENCE $seq_name " + . " INCREMENT BY 1 " + . " START WITH $start_with " + . " NOMAXVALUE " + . " NOCYCLE " + . " NOCACHE"; + push(@ddl, $seq_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; + 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; - my $seq_name = "${table}_${column}_SEQ"; - push(@sql, "DROP SEQUENCE ${seq_name}"); - push(@sql, $self->_get_create_seq_ddl($table, $column, $value)); - return @sql; + my ($self, $table, $column, $value) = @_; + my @sql; + my $seq_name = "${table}_${column}_SEQ"; + push(@sql, "DROP SEQUENCE ${seq_name}"); + push(@sql, $self->_get_create_seq_ddl($table, $column, $value)); + return @sql; } 1; diff --git a/Bugzilla/DB/Schema/Pg.pm b/Bugzilla/DB/Schema/Pg.pm index 7606faa3d..8af1af8c0 100644 --- a/Bugzilla/DB/Schema/Pg.pm +++ b/Bugzilla/DB/Schema/Pg.pm @@ -23,169 +23,191 @@ use Storable qw(dclone); #------------------------------------------------------------------------------ sub _initialize { - my $self = shift; - - $self = $self->SUPER::_initialize(@_); - - # Remove FULLTEXT index types from the schemas. - foreach my $table (keys %{ $self->{schema} }) { - if ($self->{schema}{$table}{INDEXES}) { - foreach my $index (@{ $self->{schema}{$table}{INDEXES} }) { - if (ref($index) eq 'HASH') { - 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 (exists $index->{TYPE} - && $index->{TYPE} eq 'FULLTEXT'); - } - } + my $self = shift; + + $self = $self->SUPER::_initialize(@_); + + # Remove FULLTEXT index types from the schemas. + foreach my $table (keys %{$self->{schema}}) { + if ($self->{schema}{$table}{INDEXES}) { + foreach my $index (@{$self->{schema}{$table}{INDEXES}}) { + if (ref($index) eq 'HASH') { + 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 (exists $index->{TYPE} && $index->{TYPE} eq 'FULLTEXT'); } + } } + } - $self->{db_specific} = { + $self->{db_specific} = { - BOOLEAN => 'smallint', - FALSE => '0', - TRUE => '1', + BOOLEAN => 'smallint', + FALSE => '0', + TRUE => '1', - INT1 => 'integer', - INT2 => 'integer', - INT3 => 'integer', - INT4 => 'integer', + INT1 => 'integer', + INT2 => 'integer', + INT3 => 'integer', + INT4 => 'integer', - SMALLSERIAL => 'serial unique', - MEDIUMSERIAL => 'serial unique', - INTSERIAL => 'serial unique', + SMALLSERIAL => 'serial unique', + MEDIUMSERIAL => 'serial unique', + INTSERIAL => 'serial unique', - TINYTEXT => 'varchar(255)', - MEDIUMTEXT => 'text', - LONGTEXT => 'text', + TINYTEXT => 'varchar(255)', + MEDIUMTEXT => 'text', + LONGTEXT => 'text', - LONGBLOB => 'bytea', + LONGBLOB => 'bytea', - DATETIME => 'timestamp(0) without time zone', - DATE => 'date', - }; + DATETIME => 'timestamp(0) without time zone', + DATE => 'date', + }; - $self->_adjust_schema; + $self->_adjust_schema; - return $self; + return $self; + +} #eosub--_initialize -} #eosub--_initialize #-------------------------------------------------------------------- sub get_create_database_sql { - my ($self, $name) = @_; - # We only create as utf8 if we have no params (meaning we're doing - # a new installation) or if the utf8 param is on. - my $create_utf8 = Bugzilla->params->{'utf8'} - || !defined Bugzilla->params->{'utf8'}; - my $charset = $create_utf8 ? "ENCODING 'UTF8' TEMPLATE template0" : ''; - return ("CREATE DATABASE $name $charset"); + my ($self, $name) = @_; + + # We only create as utf8 if we have no params (meaning we're doing + # a new installation) or if the utf8 param is on. + my $create_utf8 + = Bugzilla->params->{'utf8'} || !defined Bugzilla->params->{'utf8'}; + my $charset = $create_utf8 ? "ENCODING 'UTF8' TEMPLATE template0" : ''; + return ("CREATE DATABASE $name $charset"); } sub get_rename_column_ddl { - my ($self, $table, $old_name, $new_name) = @_; - if (lc($old_name) eq lc($new_name)) { - # if the only change is a case change, return an empty list, since Pg - # is case-insensitive and will return an error about a duplicate name - return (); - } - 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. - push(@sql, "ALTER SEQUENCE ${table}_${old_name}_seq - RENAME TO ${table}_${new_name}_seq"); - } - return @sql; + my ($self, $table, $old_name, $new_name) = @_; + if (lc($old_name) eq lc($new_name)) { + + # if the only change is a case change, return an empty list, since Pg + # is case-insensitive and will return an error about a duplicate name + return (); + } + 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. + push( + @sql, "ALTER SEQUENCE ${table}_${old_name}_seq + RENAME TO ${table}_${new_name}_seq" + ); + } + 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, since Pg - # is case-insensitive and will return an error about a duplicate name - return (); + 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, since Pg + # is case-insensitive and will return an error about a duplicate name + return (); + } + + 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')" + ); } - - 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"); - } + 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; + return @sql; } sub get_set_serial_sql { - my ($self, $table, $column, $value) = @_; - return ("SELECT setval('${table}_${column}_seq', $value, false) - FROM $table"); + my ($self, $table, $column, $value) = @_; + return ( + "SELECT setval('${table}_${column}_seq', $value, false) + FROM $table" + ); } sub _get_alter_type_sql { - my ($self, $table, $column, $new_def, $old_def) = @_; - my @statements; - - my $type = $new_def->{TYPE}; - $type = $self->{db_specific}->{$type} - if exists $self->{db_specific}->{$type}; - - if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { - die("You cannot specify a DEFAULT on a SERIAL-type column.") - if $new_def->{DEFAULT}; - } - - $type =~ s/\bserial\b/integer/i; - - # On Pg, you don't need UNIQUE if you're a PK--it creates - # two identical indexes otherwise. - $type =~ s/unique//i if $new_def->{PRIMARYKEY}; - - push(@statements, "ALTER TABLE $table ALTER COLUMN $column - TYPE $type"); - - if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { - push(@statements, "CREATE SEQUENCE ${table}_${column}_seq - OWNED BY $table.$column"); - push(@statements, "SELECT setval('${table}_${column}_seq', + my ($self, $table, $column, $new_def, $old_def) = @_; + my @statements; + + my $type = $new_def->{TYPE}; + $type = $self->{db_specific}->{$type} if exists $self->{db_specific}->{$type}; + + if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { + die("You cannot specify a DEFAULT on a SERIAL-type column.") + if $new_def->{DEFAULT}; + } + + $type =~ s/\bserial\b/integer/i; + + # On Pg, you don't need UNIQUE if you're a PK--it creates + # two identical indexes otherwise. + $type =~ s/unique//i if $new_def->{PRIMARYKEY}; + + push( + @statements, "ALTER TABLE $table ALTER COLUMN $column + TYPE $type" + ); + + if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) { + push( + @statements, "CREATE SEQUENCE ${table}_${column}_seq + OWNED BY $table.$column" + ); + push( + @statements, "SELECT setval('${table}_${column}_seq', MAX($table.$column)) - FROM $table"); - push(@statements, "ALTER TABLE $table ALTER COLUMN $column - SET DEFAULT nextval('${table}_${column}_seq')"); - } - - # If this column is no longer SERIAL, we need to drop the sequence - # that went along with it. - if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) { - push(@statements, "ALTER TABLE $table ALTER COLUMN $column - DROP DEFAULT"); - push(@statements, "ALTER SEQUENCE ${table}_${column}_seq - OWNED BY NONE"); - push(@statements, "DROP SEQUENCE ${table}_${column}_seq"); - } - - return @statements; + FROM $table" + ); + push( + @statements, "ALTER TABLE $table ALTER COLUMN $column + SET DEFAULT nextval('${table}_${column}_seq')" + ); + } + + # If this column is no longer SERIAL, we need to drop the sequence + # that went along with it. + if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) { + push( + @statements, "ALTER TABLE $table ALTER COLUMN $column + DROP DEFAULT" + ); + push( + @statements, "ALTER SEQUENCE ${table}_${column}_seq + OWNED BY NONE" + ); + push(@statements, "DROP SEQUENCE ${table}_${column}_seq"); + } + + return @statements; } 1; diff --git a/Bugzilla/DB/Schema/Sqlite.pm b/Bugzilla/DB/Schema/Sqlite.pm index 6d524db59..1bd53515a 100644 --- a/Bugzilla/DB/Schema/Sqlite.pm +++ b/Bugzilla/DB/Schema/Sqlite.pm @@ -22,37 +22,37 @@ use constant FK_ON_CREATE => 1; sub _initialize { - my $self = shift; + my $self = shift; - $self = $self->SUPER::_initialize(@_); + $self = $self->SUPER::_initialize(@_); - $self->{db_specific} = { - BOOLEAN => 'integer', - FALSE => '0', - TRUE => '1', + $self->{db_specific} = { + BOOLEAN => 'integer', + FALSE => '0', + TRUE => '1', - INT1 => 'integer', - INT2 => 'integer', - INT3 => 'integer', - INT4 => 'integer', + INT1 => 'integer', + INT2 => 'integer', + INT3 => 'integer', + INT4 => 'integer', - SMALLSERIAL => 'SERIAL', - MEDIUMSERIAL => 'SERIAL', - INTSERIAL => 'SERIAL', + SMALLSERIAL => 'SERIAL', + MEDIUMSERIAL => 'SERIAL', + INTSERIAL => 'SERIAL', - TINYTEXT => 'text', - MEDIUMTEXT => 'text', - LONGTEXT => 'text', + TINYTEXT => 'text', + MEDIUMTEXT => 'text', + LONGTEXT => 'text', - LONGBLOB => 'blob', + LONGBLOB => 'blob', - DATETIME => 'DATETIME', - DATE => 'DATETIME', - }; + DATETIME => 'DATETIME', + DATE => 'DATETIME', + }; - $self->_adjust_schema; + $self->_adjust_schema; - return $self; + return $self; } @@ -61,83 +61,86 @@ sub _initialize { ################################# 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); + 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); + 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 ($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_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', - ); + } + + 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; + my ($column) = @_; + + # 1 = Comma at start + # 2 = Column name + Space + # 3 = Definition + # 4 = Ending comma + return qr/(^|,)(\s\Q$column\E\s+)(.*?)(,|$)/m; } ############################# @@ -145,133 +148,137 @@ sub _sqlite_column_regex { ############################# 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"; + + # 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; + 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; + 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 }); + 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 }); + 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(@_); + 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; + 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 }); + 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 }); + 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}); } ################ @@ -279,24 +286,23 @@ sub get_rename_column_ddl { ################ 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); + 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 ($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; + 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); + return $self->_sqlite_alter_schema($table, \@clauses); } |