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
     - 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
 
 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(@_) );
 
 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 ] ] ]
 }
 
 =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($self, $opt, $new, $dbh) = ( shift, _parse_opt(\@_), shift, _dbh(@_) );
 
   my @r = ();

+  my @later = ();

 
   foreach my $table ( $new->tables ) {
   
 
   foreach my $table ( $new->tables ) {
   


@@ -285,17 +289,19 @@ sub sql_update_schema {
   
       warn "$table exists\n" if $DEBUG > 1;
 
   
       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;
 
 
     } 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;
 
   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.
 
 
 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.  
 
 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, "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;
 
 
   my $indexnum = 1;
 


@@ -494,6 +491,32 @@ sub sql_create_table {
   @r;
 }
 
   @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
 =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)
   ###
 
   # foreign keys (add)
   ###
 


@@ -642,18 +715,10 @@ sub sql_alter_table {
 
   # XXX foreign keys modify / drop
   
 
   # 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) 
+  );

 
 }
 
 
 }