From 0ac4d26f78657a3bb089711cac2fd5f76d077437 Mon Sep 17 00:00:00 2001 From: Max Kanat-Alexander Date: Tue, 1 Jun 2010 13:33:49 -0700 Subject: Bug 569312: Speed up the adding of many FKs to the same table for MySQL and PostgreSQL, by adding them all in one ALTER statement r=mkanat, a=mkanat (module owner) --- Bugzilla/DB/Schema.pm | 26 ++++++++++++++++++++++---- 1 file changed, 22 insertions(+), 4 deletions(-) (limited to 'Bugzilla/DB/Schema.pm') diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index 5da55cf26..97e59efbd 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -210,6 +210,9 @@ update this column in this table." use constant SCHEMA_VERSION => '2.00'; use constant ADD_COLUMN => 'ADD COLUMN'; +# Multiple FKs can be added using ALTER TABLE ADD CONSTRAINT in one +# SQL statement. This isn't true for all databases. +use constant MULTIPLE_FKS_IN_ALTER => 1; # This is a reasonable default that's true for both PostgreSQL and MySQL. use constant MAX_IDENTIFIER_LEN => 63; @@ -1817,11 +1820,26 @@ sub _hash_identifier { } -sub get_add_fk_sql { - my ($self, $table, $column, $def) = @_; +sub get_add_fks_sql { + my ($self, $table, $column_fks) = @_; - my $fk_string = $self->get_fk_ddl($table, $column, $def); - return ("ALTER TABLE $table ADD $fk_string"); + 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 @sql; + if ($self->MULTIPLE_FKS_IN_ALTER) { + my $alter = "ALTER TABLE $table ADD " . join(', ADD ', @add); + push(@sql, $alter); + } + else { + foreach my $fk_string (@add) { + push(@sql, "ALTER TABLE $table ADD $fk_string"); + } + } + return @sql; } sub get_drop_fk_sql { -- cgit v1.2.3-24-g4f1b