From: Jess Robinson Date: Wed, 5 Mar 2008 10:14:22 +0000 (+0000) Subject: Added support for proper enums under pg (as of 8.3), with pg version check, and defer... X-Git-Tag: v0.11008~333 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=5342f5c1a47ee390b226bf3d9b57cdfb8c803b63 Added support for proper enums under pg (as of 8.3), with pg version check, and deferrable constraints Patch from Debolaz --- diff --git a/META.yml b/META.yml index 30ffda1..72c1613 100644 --- a/META.yml +++ b/META.yml @@ -1,6 +1,6 @@ --- name: SQL-Translator -version: 0.0900 +version: 0.09000 author: - 'Ken Y. Clark ' abstract: SQL DDL transformations and more @@ -12,6 +12,7 @@ requires: Class::Base: 0 Class::Data::Inheritable: 0.02 Class::MakeMethods: 0 + DBI: 0 IO::Dir: 0 Log::Log4perl: 0 Parse::RecDescent: 1.94 @@ -40,7 +41,7 @@ provides: file: lib/SQL/Translator/Parser/DB2/Grammar.pm SQL::Translator: file: lib/SQL/Translator.pm - version: 0.0900 + version: 0.09000 SQL::Translator::Diff: file: lib/SQL/Translator/Diff.pm SQL::Translator::Filter::DefaultExtra: @@ -222,6 +223,7 @@ provides: version: 1.08 SQL::Translator::Schema::Table: file: lib/SQL/Translator/Schema/Table.pm + version: 1.27 SQL::Translator::Schema::Trigger: file: lib/SQL/Translator/Schema/Trigger.pm version: 1.09 diff --git a/bin/sqlt b/bin/sqlt index 3df1db4..2608167 100755 --- a/bin/sqlt +++ b/bin/sqlt @@ -83,6 +83,10 @@ To translate a schema: --quote-field-names Qjuote all field names in statements --no-comments Don't include comments in SQL output + PostgreSQL Producer Options: + + --postgres-version PostgreSQL server version + Diagram Producer Options: --imap-file Filename to put image map data @@ -184,6 +188,7 @@ my $producer_db_password; # db_pass " my $producer_dsn; # db_user " my $add_truncate; my $mysql_parser_version; # MySQL parser arg for /*! comments +my $postgres_version; # PostgreSQL version GetOptions( 'add-drop-table' => \$add_drop_table, @@ -226,6 +231,7 @@ GetOptions( 'use-same-auth' => \$use_same_auth, 'version' => \$show_version, 'mysql-parser-version=i' => \$mysql_parser_version, + 'postgres-version=f' => \$postgres_version, ) or pod2usage(2); if ($use_same_auth) { @@ -289,6 +295,7 @@ my $translator = SQL::Translator->new( prefix => $prefix, indent => $indent, newlines => $newlines, + postgres_version => $postgres_version, package_name => $package_name, }, ); diff --git a/lib/SQL/Translator.pm b/lib/SQL/Translator.pm index 916cd59..645102c 100644 --- a/lib/SQL/Translator.pm +++ b/lib/SQL/Translator.pm @@ -1273,6 +1273,8 @@ The following people have contributed to the SQLFairy project: =item * Sam Angiuoli +=item * Anders Nor Berle + =item * Dave Cash =item * Darren Chamberlain diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm index 9647357..afea2b8 100644 --- a/lib/SQL/Translator/Producer/PostgreSQL.pm +++ b/lib/SQL/Translator/Producer/PostgreSQL.pm @@ -181,6 +181,9 @@ sub produce { my $no_comments = $translator->no_comments; my $add_drop_table = $translator->add_drop_table; my $schema = $translator->schema; + my $pargs = $translator->producer_args; + + my $postgres_version = $pargs->{postgres_version} || 0; my $qt = ''; $qt = '"' if ($translator->quote_table_names); @@ -198,6 +201,7 @@ sub produce { { quote_table_names => $qt, quote_field_names => $qf, no_comments => $no_comments, + postgres_version => $postgres_version, add_drop_table => $add_drop_table,}); push @table_defs, $table_def; push @fks, @$fks; @@ -315,6 +319,7 @@ sub create_table my $qf = $options->{quote_field_names} || ''; my $no_comments = $options->{no_comments} || 0; my $add_drop_table = $options->{add_drop_table} || 0; + my $postgres_version = $options->{postgres_version} || 0; my $table_name = $table->name or next; $table_name = mk_name( $table_name, '', undef, 1 ); @@ -322,7 +327,7 @@ sub create_table $table->name($table_name_ur); # print STDERR "$table_name table_name\n"; - my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks ); + my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @type_defs, @type_drops, @fks ); push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments; @@ -341,6 +346,9 @@ sub create_table push @field_defs, create_field($field, { quote_table_names => $qt, quote_field_names => $qf, table_name => $table_name_ur, + postgres_version => $postgres_version, + type_defs => \@type_defs, + type_drops => \@type_drops, constraint_defs => \@constraint_defs,}); } @@ -377,8 +385,17 @@ sub create_table my $create_statement; $create_statement = join("\n", @comments); - $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n] - if $add_drop_table; + if ($add_drop_table) { + if ($postgres_version >= 8.2) { + $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n]; + $create_statement .= join ("\n", @type_drops) . "\n" + if $postgres_version >= 8.3; + } else { + $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n]; + } + } + $create_statement .= join("\n", @type_defs) . "\n" + if $postgres_version >= 8.3; $create_statement .= qq[CREATE TABLE $qt$table_name_ur$qt (\n]. join( ",\n", map { " $_" } @field_defs, @constraint_defs ). "\n);" @@ -401,6 +418,9 @@ sub create_table my $qf = $options->{quote_field_names} || ''; my $table_name = $field->table->name; my $constraint_defs = $options->{constraint_defs} || []; + my $postgres_version = $options->{postgres_version} || 0; + my $type_defs = $options->{type_defs} || []; + my $type_drops = $options->{type_drops} || []; $field_name_scope{$table_name} ||= {}; my $field_name = mk_name( @@ -425,7 +445,14 @@ sub create_table my $commalist = join( ', ', map { qq['$_'] } @$list ); my $seq_name; - $field_def .= ' '. convert_datatype($field); + if ($postgres_version >= 8.3 && $field->data_type eq 'enum') { + my $type_name = $field->table->name . '_' . $field->name . '_type'; + $field_def .= ' '. $type_name; + push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist);"; + push @$type_drops, "DROP TYPE IF EXISTS $type_name;"; + } else { + $field_def .= ' '. convert_datatype($field); + } # # Default value -- disallow for timestamps @@ -568,6 +595,10 @@ sub create_table $def .= ' ON UPDATE '.join( ' ', $c->on_update ); } + if ( $c->deferrable ) { + $def .= ' DEFERRABLE'; + } + push @fks, "$def;"; } diff --git a/lib/SQL/Translator/Schema/Table.pm b/lib/SQL/Translator/Schema/Table.pm index 3aa2c4c..feb0014 100644 --- a/lib/SQL/Translator/Schema/Table.pm +++ b/lib/SQL/Translator/Schema/Table.pm @@ -51,6 +51,8 @@ use base 'SQL::Translator::Schema::Object'; use vars qw( $VERSION $FIELD_ORDER ); +$VERSION = sprintf "%d.%02d", q$Revision: 1.27 $ =~ /(\d+)\.(\d+)/; + # Stringify to our name, being careful not to pass any args through so we don't # accidentally set it to undef. We also have to tweak bool so the object is # still true when it doesn't have a name (which shouldn't happen!). diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t index 0fd269c..ced163f 100644 --- a/t/46xml-to-pg.t +++ b/t/46xml-to-pg.t @@ -58,5 +58,5 @@ CREATE TABLE "Another" ( ); ALTER TABLE "Basic" ADD FOREIGN KEY ("another_id") - REFERENCES "Another" ("id"); + REFERENCES "Another" ("id") DEFERRABLE; SQL diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t index 9c0590d..d52136f 100644 --- a/t/47postgres-producer.t +++ b/t/47postgres-producer.t @@ -14,7 +14,7 @@ use FindBin qw/$Bin/; #============================================================================= BEGIN { - maybe_plan(6, + maybe_plan(7, 'SQL::Translator::Producer::PostgreSQL', 'Test::Differences', ) @@ -90,3 +90,17 @@ my $field4 = SQL::Translator::Schema::Field->new( name => 'bytea_field', my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4); is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works'); + +my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field', + table => $table, + data_type => 'enum', + extra => { list => [ 'Foo', 'Bar' ] }, + is_auto_increment => 0, + is_nullable => 0, + is_foreign_key => 0, + is_unique => 0 ); + +my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 }); + +is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works'); +