From: Ivan Kohler Date: Mon, 4 Nov 2013 22:54:51 +0000 (-0800) Subject: Create/alter tables before foreign key changes X-Git-Url: http://git.freeside.biz/gitweb/?p=DBIx-DBSchema.git;a=commitdiff_plain;h=404241bce9880be2a101bda624e05bf6e944c21a Create/alter tables before foreign key changes --- diff --git a/Changes b/Changes index 57589a1..f3f9259 100644 --- 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 , 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 --- 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 --- 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 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) + ); }