Create/alter tables before foreign key changes

diff --git a/Changes b/Changes
index 57589a1..f3f9259 100644 (file)
--- a/Changes
+++ b/Changes
@@ -4,6 +4,7 @@ Revision history for Perl module DBIx::DBSchema
     - Incorporate CPAN::Changes::Spec-compliant Changes file from Neil Bowers
       <neilb@cpan.org>, closes: CPAN#90023, thanks!
     - Fix table creation broken by 0.42
+    - Create/alter tables before foreign key changes
 
 0.42 2013-11-03 01:09:27 PST
     - Basic foreign key support

diff --git a/DBSchema.pm b/DBSchema.pm
index f4c65db..cc47979 100644 (file)
--- a/DBSchema.pm
+++ b/DBSchema.pm
@@ -235,7 +235,10 @@ specified database, will attempt to use generic SQL syntax.
 
 sub sql {
   my($self, $dbh) = ( shift, _dbh(@_) );
-  map { $self->table($_)->sql_create_table($dbh); } $self->tables;
+  ( 
+    ( map { $self->table($_)->sql_create_table($dbh); } $self->tables ),
+    ( map { $self->table($_)->sql_add_constraints($dbh); } $self->tables ),
+  );
 }
 
 =item sql_update_schema [ OPTIONS_HASHREF, ] PROTOTYPE_SCHEMA [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
@@ -278,6 +281,7 @@ sub sql_update_schema {
   my($self, $opt, $new, $dbh) = ( shift, _parse_opt(\@_), shift, _dbh(@_) );
 
   my @r = ();
+  my @later = ();
 
   foreach my $table ( $new->tables ) {
   
@@ -285,17 +289,19 @@ sub sql_update_schema {
   
       warn "$table exists\n" if $DEBUG > 1;
 
-      push @r, $self->table($table)->sql_alter_table( $new->table($table),
-                                                      $dbh,
-                                                      $opt
-                                                    );
+      push @r,
+        $self->table($table)->sql_alter_table( $new->table($table),
+                                                 $dbh, $opt );
+      push @later,
+        $self->table($table)->sql_alter_constraints( $new->table($table),
+                                                       $dbh, $opt );
 
     } else {
   
       warn "table $table does not exist.\n" if $DEBUG;
 
-      push @r, 
-        $new->table($table)->sql_create_table( $dbh );
+      push @r,     $new->table($table)->sql_create_table(    $dbh );
+      push @later, $new->table($table)->sql_add_constraints( $dbh );
   
     }
   
@@ -316,6 +322,8 @@ sub sql_update_schema {
 
   }
 
+  push @r, @later;
+
   warn join("\n", @r). "\n"
     if $DEBUG > 1;
 

diff --git a/DBSchema/Table.pm b/DBSchema/Table.pm
index 0da0579..b150d30 100644 (file)
--- a/DBSchema/Table.pm
+++ b/DBSchema/Table.pm
@@ -435,9 +435,6 @@ sub unique_singles {
 
 Returns a list of SQL statments to create this table.
 
-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.  
-
 The data source can be specified by passing an open DBI database handle, or by
 passing the DBI data source name, username and password.  
 
@@ -475,7 +472,7 @@ sub sql_create_table {
   push @columns, "PRIMARY KEY (". $self->primary_key. ")"
     if $self->primary_key && ! grep /PRIMARY KEY/i, @columns;
 
-  push @columns, $self->foreign_keys_sql;
+#  push @columns, $self->foreign_keys_sql;
 
   my $indexnum = 1;
 
@@ -494,6 +491,32 @@ sub sql_create_table {
   @r;
 }
 
+=item sql_add_constraints [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
+
+Returns a list of SQL statments to add constraints (foreign keys) to this table.
+
+The data source can be specified by passing an open DBI database handle, or by
+passing the DBI data source name, username and password.  
+
+Although the username and password are optional, it is best to call this method
+with a database handle or data source including a valid username and password -
+a DBI connection will be opened and the quoting and type mapping will be more
+reliable.
+
+If passed a DBI data source (or handle) such as `DBI:mysql:database', will use
+MySQL- or PostgreSQL-specific syntax.  Non-standard syntax for other engines
+(if applicable) may also be supported in the future.
+
+=cut
+
+sub sql_add_constraints {
+  my $self = shift;
+  my @fks = $self->foreign_keys_sql or return ();
+  (
+    'ALTER TABLE '. $self->name. ' '. join(",\n  ", map "ADD $_", @fks) 
+  );
+}
+
 =item sql_alter_table PROTOTYPE_TABLE, [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
 
 Returns a list of SQL statements to alter this table so that it is identical
@@ -630,6 +653,56 @@ sub sql_alter_table {
   }
 
   ###
+  # return the statements
+  ###
+
+  unshift @r, "ALTER TABLE $table ". join(', ', @at) if @at;
+
+  push @r, @r_later;
+
+  warn join('', map "$_\n", @r)
+    if $DEBUG && @r;
+
+  @r;
+
+}
+
+=item sql_alter_constraints PROTOTYPE_TABLE, [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
+
+Returns a list of SQL statements to alter this table so that it is identical
+to the provided table, also a DBIx::DBSchema::Table object.
+
+The data source can be specified by passing an open DBI database handle, or by
+passing the DBI data source name, username and password.  
+
+Although the username and password are optional, it is best to call this method
+with a database handle or data source including a valid username and password -
+a DBI connection will be opened and used to check the database version as well
+as for more reliable quoting and type mapping.  Note that the database
+connection will be used passively, B<not> to actually run the CREATE
+statements.
+
+If passed a DBI data source (or handle) such as `DBI:mysql:database' or
+`DBI:Pg:dbname=database', will use syntax specific to that database engine.
+Currently supported databases are MySQL and PostgreSQL.
+
+If not passed a data source (or handle), or if there is no driver for the
+specified database, will attempt to use generic SQL syntax.
+
+=cut
+
+#gosh, false laziness w/DBSchema::sql_update_schema
+
+sub sql_alter_constraints {
+  my($self, $opt, $new, $dbh) = ( shift, _parse_opt(\@_), shift, _dbh(@_) );
+
+  my $driver = _load_driver($dbh);
+
+  my $table = $self->name;
+
+  my @at = ();
+
+  ###
   # foreign keys (add)
   ###
 
@@ -642,18 +715,10 @@ sub sql_alter_table {
 
   # XXX foreign keys modify / drop
   
-  ###
-  # return the statements
-  ###
-
-  unshift @r, "ALTER TABLE $table ". join(', ', @at) if @at;
-
-  push @r, @r_later;
-
-  warn join('', map "$_\n", @r)
-    if $DEBUG && @r;
-
-  @r;
+  return () unless @at;
+  (
+    'ALTER TABLE '. $self->name. ' '. join(",\n  ", @at) 
+  );
 
 }