consolidate multiple ALTER TABLE statements for efficiency

diff --git a/Changes b/Changes
index 54aa28e..83f6abd 100644 (file)
--- a/Changes
+++ b/Changes
@@ -1,5 +1,8 @@
 Revision history for Perl extension DBIx::DBSchema.
 
+0.41_01 unreleased
+        - consolidate multiple ALTER TABLE statements for efficiency
+
 0.40 Sat Dec 17 17:03:51 PST 2011
         - doc: sql_update_schema link to sql_add_column misspelled
         - Pg: fix mapping for DOUBLE PRECISION type to avoid needless alter cols

diff --git a/DBSchema/Column.pm b/DBSchema/Column.pm
index f10d43a..27c4c22 100644 (file)
--- a/DBSchema/Column.pm
+++ b/DBSchema/Column.pm
@@ -325,10 +325,16 @@ sub quoted_default {
 
 }
 
-=item sql_add_column [ DBH ] 
+=item sql_add_column [ DBH ]
 
-Returns a list of SQL statements to add this column to an existing table.  (To
-create a new table, see L<DBIx::DBSchema::Table/sql_create_table> instead.)
+Returns SQL to add this column to an existing table.  (To create a new table,
+see L<DBIx::DBSchema::Table/sql_create_table> instead.)
+
+NOTE: This interface has changed in 0.41
+
+Returns two listrefs.  The first is a list of column alteration SQL fragments
+for an ALTER TABLE statement.  The second is a list of full SQL statements that
+should be run after the ALTER TABLE statement.
 
 The data source can be specified by passing an open DBI database handle, or by
 passing the DBI data source name, username and password.  
@@ -352,6 +358,7 @@ sub sql_add_column {
 
   my $driver = $dbh ? _load_driver($dbh) : '';
 
+  my @alter_table = ();
   my @sql = ();
   my $table = $self->table_name;
 
@@ -370,7 +377,7 @@ sub sql_add_column {
     $self->null($hashref->{'effective_null'});
   }
 
-  push @sql, "ALTER TABLE $table ADD COLUMN ". $self->line($dbh);
+  push @alter_table, "ADD COLUMN ". $self->line($dbh);
 
   push @sql, @{ $hashref->{'sql_after'} } if $hashref->{'sql_after'};
 
@@ -381,14 +388,20 @@ sub sql_add_column {
   $self->type($real_type) if $real_type;
   $self->null($real_null) if defined $real_null;
 
-  @sql;
+  (\@alter_table, \@sql);
 
 }
 
 =item sql_alter_column PROTOTYPE_COLUMN  [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
 
-Returns a list of SQL statements to alter this column so that it is identical
-to the provided prototype column, also a DBIx::DBSchema::Column object.
+Returns SQL to alter this column so that it is identical to the provided
+prototype column, also a DBIx::DBSchema::Column object.
+
+NOTE: This interface has changed in 0.41
+
+Returns two listrefs.  The first is a list of column alteration SQL fragments
+for an ALTER TABLE statement.  The second is a list of full SQL statements that
+should be run after the ALTER TABLE statement.
 
 Optionally, the data source can be specified by passing an open DBI database
 handle, or by passing the DBI data source name, username and password.  
@@ -413,6 +426,7 @@ sub sql_alter_column {
 
   my $driver = $dbh ? _load_driver($dbh) : '';
 
+  my @alter_table = ();
   my @sql = ();
 
   my $dbd = "DBIx::DBSchema::DBD::$driver";
@@ -429,7 +443,7 @@ sub sql_alter_column {
 
     # change the type...
     if ( $hashref->{'sql_alter_type'} ) {
-      push @sql, $hashref->{'sql_alter_type'};
+      push @alter_table, $hashref->{'sql_alter_type'};
     }
 
     # change nullability...
@@ -443,7 +457,7 @@ sub sql_alter_column {
       # change nullability from NOT NULL to NULL
       if ( ! $self->null && $new->null ) {
     
-        push @sql, "ALTER TABLE $table ALTER COLUMN $name DROP NOT NULL";
+        push @alter_table, "ALTER COLUMN $name DROP NOT NULL";
     
       }
     
@@ -452,7 +466,7 @@ sub sql_alter_column {
       # the table first...
       if ( $self->null && ! $new->null ) {
     
-        push @sql, "ALTER TABLE $table ALTER COLUMN $name SET NOT NULL";
+        push @alter_table, "ALTER COLUMN $name SET NOT NULL";
     
       }
 
@@ -468,13 +482,13 @@ sub sql_alter_column {
 
       #warn "old default: $old_default / new default: $new_default\n";
 
-      my $alter = "ALTER TABLE $table ALTER COLUMN $name";
+      my $alter = "ALTER COLUMN $name";
 
       if ( $new_default ne '' ) {
         #warn "changing from $old_default to $new_default\n";
-        push @sql, "$alter SET DEFAULT $new_default";
+        push @alter_table, "$alter SET DEFAULT $new_default";
       } elsif ( $old_default !~ /^nextval/i ) { #Pg-specific :(
-        push @sql, "$alter DROP DEFAULT";
+        push @alter_table, "$alter DROP DEFAULT";
 
         push @sql, "UPDATE TABLE $table SET $name = NULL WHERE $name = ''"
           if $opt->{'nullify_default'} && $old_default eq "''" && $new->null;
@@ -486,13 +500,17 @@ sub sql_alter_column {
 
   }
 
-  @sql;
+  (\@alter_table, \@sql);
 
 }
 
 =item sql_drop_column [ DBH ] 
 
-Returns a list of SQL statements to drop this column from an existing table.
+Returns SQL to drop this column from an existing table.
+
+NOTE: This interface has changed in 0.41
+
+Returns a list of column alteration SQL fragments for an ALTER TABLE statement. 
 
 The optional database handle or DBI data source/username/password is not yet
 used.
@@ -505,7 +523,7 @@ sub sql_drop_column {
  my $table = $self->table_name;
  my $name = $self->name;
  
- ("ALTER TABLE $table DROP COLUMN $name"); # XXX what about indexes???
+ ("DROP COLUMN $name"); # XXX what about indexes???
 }
 
 =back
@@ -517,7 +535,7 @@ Ivan Kohler <ivan-dbix-dbschema@420.am>
 =head1 COPYRIGHT
 
 Copyright (c) 2000-2006 Ivan Kohler
-Copyright (c) 2007-2010 Freeside Internet Services, Inc.
+Copyright (c) 2007-2013 Freeside Internet Services, Inc.
 All rights reserved.
 This program is free software; you can redistribute it and/or modify it under
 the same terms as Perl itself.

diff --git a/DBSchema/DBD/Pg.pm b/DBSchema/DBD/Pg.pm
index b54f1d1..c441633 100644 (file)
--- a/DBSchema/DBD/Pg.pm
+++ b/DBSchema/DBD/Pg.pm
@@ -270,7 +270,7 @@ sub alter_column_callback {
     if ( $pg_server_version >= 80000 ) {
 
       $hashref->{'sql_alter_type'} =
-        "ALTER TABLE $table ALTER COLUMN ". $new_column->name.
+        "ALTER COLUMN ". $new_column->name.
         " TYPE ". $new_column->type.
         ( ( defined($new_column->length) && $new_column->length )
               ? '('.$new_column->length.')'

diff --git a/DBSchema/DBD/mysql.pm b/DBSchema/DBD/mysql.pm
index 798ea43..966996c 100644 (file)
--- a/DBSchema/DBD/mysql.pm
+++ b/DBSchema/DBD/mysql.pm
@@ -177,7 +177,7 @@ sub alter_column_callback {
   {
     my $old_def = $old_column->line($dbh);
     $hashref->{'sql_alter_type'} =
-      "ALTER TABLE $table CHANGE $old_name $new_def";
+      "CHANGE $old_name $new_def";
   }
 
   #change nullability

diff --git a/DBSchema/Table.pm b/DBSchema/Table.pm
index 8d047de..ab5f83f 100644 (file)
--- a/DBSchema/Table.pm
+++ b/DBSchema/Table.pm
@@ -634,17 +634,22 @@ sub sql_alter_table {
   foreach my $column ( $new->columns ) {
 
     if ( $self->column($column) )  {
-
       warn "  $table.$column exists\n" if $DEBUG > 1;
-      push @r, $self->column($column)->sql_alter_column( $new->column($column),
-                                                         $dbh,
-                                                         $opt,
-                                                       );
+
+      my ($alter_table, $sql) = 
+        $self->column($column)->sql_alter_column( $new->column($column),
+                                                  $dbh,
+                                                  $opt,
+                                                );
+      push @at, @$alter_table;
+      push @r, @$sql;
 
     } else {
-  
       warn "column $table.$column does not exist.\n" if $DEBUG > 1;
-      push @r, $new->column($column)->sql_add_column( $dbh );
+
+      my ($alter_table, $sql) = $new->column($column)->sql_add_column( $dbh );
+      push @at, @$alter_table;
+      push @r, @$sql;
   
     }
   
@@ -715,9 +720,11 @@ sub sql_alter_table {
 
     warn "column $table.$column should be dropped.\n" if $DEBUG;
 
-    push @r, $self->column($column)->sql_drop_column( $dbh );
+    push @at, $self->column($column)->sql_drop_column( $dbh );
 
   }
+
+  unshift @r, "ALTER TABLE $table ", join(', ', @at) if @at;
   
   ###
   # return the statements