From: Ash Berlin Date: Thu, 4 Dec 2008 16:45:46 +0000 (+0000) Subject: PgSQL diff patch from wries X-Git-Tag: v0.11008~269 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=3406fd5b1839f1e6d165ae39b86e08effc80ac2c PgSQL diff patch from wries (also fixed line lengths in Changes) --- diff --git a/Changes b/Changes index e4ff8ba..987e69f 100644 --- a/Changes +++ b/Changes @@ -1,37 +1,49 @@ # ---------------------------------------------------------- # # ---------------------------------------------------------- -* parsing MySQL CURRENT_TIMESTAMP as scalar ref so it can be produced without quotes (jgoulah) +* parsing MySQL CURRENT_TIMESTAMP as scalar ref so it can be produced without + quotes (jgoulah) * Add ignore_opts parser arg (to ignore table options) in Parser::MySQL (jgoulah) * Skip tests for buggy Spreadsheet::ParseExcel versions (rbo) * Add support for skip tables parser arg in Parser::DBI::MySQL (jgoulah) -* Changed behaviour of ::Producer::Oracle when returning an array of statements to make it compatible to DBI->do() +* Changed behaviour of ::Producer::Oracle when returning an array of statements + to make it compatible to DBI->do() * Fixed a few bugs in ::Producer::Oracle * Applied patch from jgoulah to support mysql's MERGE option * Applied patch from rbo to add support of multiple database events on a trigger -* Applied patch from lukes to allow drop if exists in sqlite producer, with version >= 3.3 -* Applied patch from rjbs with minor changes, now we support scalar refs in default values! +* Applied patch from lukes to allow drop if exists in sqlite producer, with + version >= 3.3 +* Applied patch from rjbs with minor changes, now we support scalar refs in + default values! * Fixed SQLite producer to end index statements in newlines, in scalar context * Decreed that all list context statements shall not end in ; or ;\n * Fixed SQLite, Diff and MySQL producers to agree with Decree. * Added support for CREATE VIEW + tests in the Pg producer (wreis) * Added support for CREATE VIEW + tests in the sqlite producer (groditi) -* Added proper argument parsing and documentation to MySQL Parser and Producer (ribasushi) -* Using DROP VIEW instead of OR REPLACE clause in the Pg producer, as replace only allows replacement with same number of columns (wreis) -* Added support for DROP VIEW and fixed CREATE VIEW statement in the sqlite producer (wreis) -* Removed source_db and target_db accessors from Diff (throwback to old version, only output_db is used) +* Added proper argument parsing and documentation to MySQL Parser and + Producer (ribasushi) +* Using DROP VIEW instead of OR REPLACE clause in the Pg producer, as replace + only allows replacement with identical set of columns (wreis) +* Added support for DROP VIEW and fixed CREATE VIEW statement in the sqlite + producer (wreis) +* Removed source_db and target_db accessors from Diff (throwback to old version, + only output_db is used) -* Support for longer varchar fields in My +* Support for longer varchar fields in MySQL # ---------------------------------------------------------- # 0.09001 2008-08-19 # ---------------------------------------------------------- * Added support for CREATE VIEW + tests in the mysql producer (groditi) * Added support for SET fields in the mysql producer + test (groditi) -* Added support for proper booleans in the mysql producer, when a mysql version of at least 4.x is supplied -* Added support for proper enums under pg (as of 8.3), with pg version check, and deferrable constraints -* Added support to truncate long constraint and index names in the mysql producer, because of a change to DBIx::Class to produce such long names in some cases. +* Added support for proper booleans in the mysql producer, when a mysql version + of at least 4.x is supplied +* Added support for proper enums under pg (as of 8.3), with pg version check, + and deferrable constraints +* Added support to truncate long constraint and index names in the mysql + producer, because of a change to DBIx::Class to produce such long names in + some cases. # ---------------------------------------------------------- # 0.09000 2008-02-25 @@ -48,9 +60,13 @@ * Major refactoring of SQL::Translator::Diff again: * Diff is no longer one huge monolithic function. * Added more tests for diff -* When producing diffs for MySQL you will (by default) get single alter statements per table -* SQLite can also do remove columns (by creating a temp table as shown in http://sqlite.org/faq.html#q11 -* Columns can be renamed if the new schema is from a form that can have metadata (which is pretty much anything but an SQL file.) It does this by looking at renamed_from in the $field->extra +* When producing diffs for MySQL you will (by default) get single alter + statements per table +* SQLite can also do remove columns (by creating a temp table as shown in + http://sqlite.org/faq.html#q11 +* Columns can be renamed if the new schema is from a form that can have metadata + (which is pretty much anything but an SQL file.) It does this by looking at + renamed_from in the $field->extra * Updated Oracle and Postgres producers * More tests! @@ -72,7 +88,8 @@ # 0.08 2006-12-07 # ----------------------------------------------------------- -* Patched 18ttschema-producer.t and 33tt-table-producter.t to skip on TT 2.15, thanks Ash! +* Patched 18ttschema-producer.t and 33tt-table-producter.t to skip on TT 2.15, + thanks Ash! # ----------------------------------------------------------- # 0.08_04 2006-11-10 @@ -94,7 +111,8 @@ # 0.08_02 2006-11-03 # ----------------------------------------------------------- -* Added patch from Ash to separate DROP statements in mysql producer in list-context +* Added patch from Ash to separate DROP statements in mysql producer in + list-context * Fixed up SQLites usage of no-comments # ----------------------------------------------------------- @@ -102,8 +120,10 @@ # ----------------------------------------------------------- * Made Trigger check that a give table exists in on_table - castaway -* Split some producers (DB2, MySQL, SQLite, PostgreSQL) into sub methods (others to follow) - castaway -* Add alter_* methods to some Producers and docs to Producer.pm (for use by Diff later) - castaway +* Split some producers (DB2, MySQL, SQLite, PostgreSQL) into sub methods (others + to follow) - castaway +* Add alter_* methods to some Producers and docs to Producer.pm (for use by Diff + later) - castaway * Made changes to allow producers to return a list of statements - castaway * Split sqlt-diff into script and module - castaway * Added quote_table_names and quote_field_names patch (omega, zamolxes) - castaway diff --git a/lib/SQL/Translator/Diff.pm b/lib/SQL/Translator/Diff.pm index 9066181..99de6b7 100644 --- a/lib/SQL/Translator/Diff.pm +++ b/lib/SQL/Translator/Diff.pm @@ -254,10 +254,11 @@ sub produce_diff_sql { } if ( @diffs ) { - if ( $self->output_db !~ /^(?:MySQL|SQLite)$/ ) { + if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) { unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!"); } - return join '', map { $_ ? "$_;\n\n" : "\n" } ("-- Convert schema '$src_name' to '$tar_name':", @diffs); + return join '', map { $_ ? ( $_ =~ /;$/xms ? $_ : "$_;\n\n" ) : "\n" } + ("-- Convert schema '$src_name' to '$tar_name':", @diffs); } return undef; diff --git a/lib/SQL/Translator/Parser/PostgreSQL.pm b/lib/SQL/Translator/Parser/PostgreSQL.pm index f0d6ad8..525582c 100644 --- a/lib/SQL/Translator/Parser/PostgreSQL.pm +++ b/lib/SQL/Translator/Parser/PostgreSQL.pm @@ -1036,7 +1036,7 @@ sub parse { name => $idata->{'name'}, type => uc $idata->{'type'}, fields => $idata->{'fields'}, - ) or die $table->error; + ) or die $table->error . ' ' . $table->name; } for my $cdata ( @{ $tdata->{'constraints'} || [] } ) { diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm index 495f529..17924f3 100644 --- a/lib/SQL/Translator/Producer/PostgreSQL.pm +++ b/lib/SQL/Translator/Producer/PostgreSQL.pm @@ -217,10 +217,10 @@ sub produce { }); } - $output = join("\n\n", @table_defs); + $output = join(";\n\n", @table_defs) . ";\n\n"; if ( @fks ) { $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments; - $output .= join( "\n\n", @fks ) . "\n"; + $output .= join( ";\n\n", @fks ) . ";\n"; } if ( $WARN ) { @@ -327,9 +327,12 @@ sub create_table 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 ); - my $table_name_ur = $qt ? $table_name : unreserve($table_name); + my $table_name = $table->name or next; + $table_name = mk_name( $table_name, '', undef, 1 ); + my ( $fql_tbl_name ) = ( $table_name =~ s/\W(.*)$// ) ? $1 : q{}; + my $table_name_ur = $qt ? $table_name + : $fql_tbl_name ? join('.', $table_name, unreserve($fql_tbl_name)) + : unreserve($table_name); $table->name($table_name_ur); # print STDERR "$table_name table_name\n"; @@ -404,11 +407,12 @@ sub create_table if $postgres_version >= 8.3; $create_statement .= qq[CREATE TABLE $qt$table_name_ur$qt (\n]. join( ",\n", map { " $_" } @field_defs, @constraint_defs ). - "\n);" + "\n)" ; + $create_statement .= @index_defs ? ';' : q{}; + $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} ) + . join(";\n", @index_defs); - $create_statement .= "\n" . join("\n", @index_defs) . "\n"; - return $create_statement, \@fks; } @@ -444,7 +448,6 @@ sub create_view { $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION'; } - $create .= ";\n\n"; return $create; } @@ -489,8 +492,8 @@ sub create_view { 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;"; + 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); } @@ -543,7 +546,7 @@ sub create_view { $index->fields; next unless @fields; - my $def_start = qq[Constraint "$name" ]; + my $def_start = qq[CONSTRAINT "$name" ]; if ( $type eq PRIMARY_KEY ) { push @constraint_defs, "${def_start}PRIMARY KEY ". '(' .$qf . join( $qf. ', '.$qf, @fields ) . $qf . ')'; @@ -556,7 +559,7 @@ sub create_view { $index_def = "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (". join( ', ', map { qq[$qf$_$qf] } @fields ). - ');' + ')' ; } else { @@ -592,7 +595,7 @@ sub create_view { $c->reference_fields; next if !@fields && $c->type ne CHECK_C; - my $def_start = $name ? qq[Constraint "$name" ] : ''; + my $def_start = $name ? qq[CONSTRAINT "$name" ] : ''; if ( $c->type eq PRIMARY_KEY ) { push @constraint_defs, "${def_start}PRIMARY KEY ". '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')'; @@ -632,7 +635,7 @@ sub create_view { $def .= ' DEFERRABLE'; } - push @fks, "$def;"; + push @fks, "$def"; } return \@constraint_defs, \@fks; @@ -650,7 +653,7 @@ sub convert_datatype # $len = ($len < length($_)) ? length($_) : $len for (@$list); # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' ); # push @$constraint_defs, -# qq[Constraint "$chk_name" CHECK ($qf$field_name$qf ]. +# qq[CONSTRAINT "$chk_name" CHECK ($qf$field_name$qf ]. # qq[IN ($commalist))]; $data_type = 'character varying'; } @@ -723,39 +726,42 @@ sub alter_field if($from_field->table->name ne $to_field->table->name); my @out; - push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL;', + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL', $to_field->table->name, $to_field->name) if(!$to_field->is_nullable and $from_field->is_nullable); my $from_dt = convert_datatype($from_field); my $to_dt = convert_datatype($to_field); - push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s;', + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s', $to_field->table->name, $to_field->name, $to_dt) if($to_dt ne $from_dt); - push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s;', + push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s', $to_field->table->name, $from_field->name, $to_field->name) if($from_field->name ne $to_field->name); - push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;', + my $old_default = $from_field->default_value; + my $new_default = $to_field->default_value; + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s', $to_field->table->name, $to_field->name, - $to_field->default_value) - if(defined $to_field->default_value && - $from_field->default_value ne $to_field->default_value); + $to_field->default_value) + if ( defined $new_default && + (!defined $old_default || $old_default ne $new_default) ); return wantarray ? @out : join("\n", @out); - } +sub rename_field { alter_field(@_) } + sub add_field { my ($new_field) = @_; - my $out = sprintf('ALTER TABLE %s ADD COLUMN %s;', + my $out = sprintf('ALTER TABLE %s ADD COLUMN %s', $new_field->table->name, create_field($new_field)); return $out; @@ -766,13 +772,76 @@ sub drop_field { my ($old_field) = @_; - my $out = sprintf('ALTER TABLE %s DROP COLUMN %s;', + my $out = sprintf('ALTER TABLE %s DROP COLUMN %s', $old_field->table->name, $old_field->name); return $out; } +sub alter_table { + my ($to_table, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $out = sprintf('ALTER TABLE %s %s', + $qt . $to_table->name . $qt, + $options->{alter_table_action}); + return $out; +} + +sub rename_table { + my ($old_table, $new_table, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + $options->{alter_table_action} = "RENAME TO $qt$new_table$qt"; + return alter_table($old_table, $options); +} + +sub alter_create_index { + my ($index, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qf = $options->{quote_field_names} || ''; + my ($idef, $constraints) = create_index($index, { + quote_field_names => $qf, + quote_table_names => $qt, + table_name => $index->table->name, + }); + return $index->type eq NORMAL ? $idef + : sprintf('ALTER TABLE %s ADD %s', + $qt . $index->table->name . $qt, + join(q{}, @$constraints) + ); +} + +sub alter_drop_index { + my ($index, $options) = @_; + my $index_name = $index->name; + return "DROP INDEX $index_name"; +} + +sub alter_drop_constraint { + my ($c, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qc = $options->{quote_field_names} || ''; + my $out = sprintf('ALTER TABLE %s DROP CONSTRAINT %s', + $qt . $c->table->name . $qt, + $qc . $c->name . $qc ); + return $out; +} + +sub alter_create_constraint { + my ($index, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + return $index->type eq FOREIGN_KEY ? join(q{}, @{create_constraint(@_)}) + : join( ' ', 'ALTER TABLE', $qt.$index->table->name.$qt, + 'ADD', join(q{}, map { @{$_} } create_constraint(@_)) + ); +} + +sub drop_table { + my ($table, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + return "DROP TABLE $qt$table$qt CASCADE"; +} + 1; # ------------------------------------------------------------------- diff --git a/t/30sqlt-new-diff-pgsql.t b/t/30sqlt-new-diff-pgsql.t new file mode 100644 index 0000000..0926514 --- /dev/null +++ b/t/30sqlt-new-diff-pgsql.t @@ -0,0 +1,158 @@ +#!/usr/bin/perl +# vim: set ft=perl: + +use strict; +use warnings; +use SQL::Translator; + +use File::Spec::Functions qw(catfile updir tmpdir); +use FindBin qw($Bin); +use Test::More; +use Test::Differences; +use Test::SQL::Translator qw(maybe_plan); +use SQL::Translator::Schema::Constants; +use Storable 'dclone'; + +plan tests => 4; + +use_ok('SQL::Translator::Diff') or die "Cannot continue\n"; + +my $tr = SQL::Translator->new; + +my ( $source_schema, $target_schema, $parsed_sql_schema ) = map { + my $t = SQL::Translator->new; + $t->parser( 'YAML' ) + or die $tr->error; + my $out = $t->translate( catfile($Bin, qw/data diff pgsql/, $_ ) ) + or die $tr->error; + + my $schema = $t->schema; + unless ( $schema->name ) { + $schema->name( $_ ); + } + ($schema); +} (qw( create1.yml create2.yml )); + +# Test for differences +my $out = SQL::Translator::Diff::schema_diff( + $source_schema, + 'PostgreSQL', + $target_schema, + 'PostgreSQL', + { + producer_options => { + quote_table_names => 0, + } + } +); +eq_or_diff($out, <<'## END OF DIFF', "Diff as expected"); +-- Convert schema 'create1.yml' to 'create2.yml':; + +BEGIN; + +CREATE TABLE added ( + "id" bigint +); + +ALTER TABLE old_name RENAME TO new_name; + +ALTER TABLE employee DROP CONSTRAINT FK5302D47D93FE702E; + +ALTER TABLE person DROP CONSTRAINT UC_age_name; + +DROP INDEX u_name; + +ALTER TABLE employee DROP COLUMN job_title; + +ALTER TABLE new_name ADD COLUMN new_field integer; + +ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT '1'; + +ALTER TABLE person ALTER COLUMN person_id TYPE serial; + +ALTER TABLE person ALTER COLUMN name SET NOT NULL; + +ALTER TABLE person ALTER COLUMN age SET DEFAULT 18; + +ALTER TABLE person ALTER COLUMN iq TYPE bigint; + +ALTER TABLE person RENAME COLUMN description TO physical_description; + +ALTER TABLE person ADD CONSTRAINT "unique_name" UNIQUE (name); + +ALTER TABLE employee ADD FOREIGN KEY (employee_id) + REFERENCES person (person_id) DEFERRABLE; + +ALTER TABLE person ADD CONSTRAINT "UC_person_id" UNIQUE (person_id); + +ALTER TABLE person ADD CONSTRAINT "UC_age_name" UNIQUE (age, name); + +DROP TABLE deleted CASCADE; + + +COMMIT; + +## END OF DIFF + +$out = SQL::Translator::Diff::schema_diff( + $source_schema, 'PostgreSQL', $target_schema, 'PostgreSQL', + { ignore_index_names => 1, + ignore_constraint_names => 1, + producer_options => { + quote_table_names => 0, + quote_field_names => 0, + } + }); + +eq_or_diff($out, <<'## END OF DIFF', "Diff as expected"); +-- Convert schema 'create1.yml' to 'create2.yml':; + +BEGIN; + +CREATE TABLE added02 ( + id bigint +); + +ALTER TABLE old_name RENAME TO new_name; + +ALTER TABLE person DROP CONSTRAINT UC_age_name; + +ALTER TABLE employee DROP COLUMN job_title; + +ALTER TABLE new_name ADD COLUMN new_field02 integer; + +ALTER TABLE person ADD COLUMN is_rock_star02 smallint DEFAULT '1'; + +ALTER TABLE person ALTER COLUMN person_id TYPE serial; + +ALTER TABLE person ALTER COLUMN name SET NOT NULL; + +ALTER TABLE person ALTER COLUMN age SET DEFAULT 18; + +ALTER TABLE person ALTER COLUMN iq TYPE bigint; + +ALTER TABLE person RENAME COLUMN description TO physical_description; + +ALTER TABLE person ADD CONSTRAINT "UC_person_id3" UNIQUE (person_id); + +ALTER TABLE person ADD CONSTRAINT "UC_age_name3" UNIQUE (age, name); + +DROP TABLE deleted CASCADE; + + +COMMIT; + +## END OF DIFF + + +# Test for sameness +$out = SQL::Translator::Diff::schema_diff( + $source_schema, 'PostgreSQL', $source_schema, 'PostgreSQL' +); + +eq_or_diff($out, <<'## END OF DIFF', "No differences found"); +-- Convert schema 'create1.yml' to 'create1.yml':; + +-- No differences found; + +## END OF DIFF diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t index 4747e16..04b2f5c 100644 --- a/t/46xml-to-pg.t +++ b/t/46xml-to-pg.t @@ -46,19 +46,16 @@ CREATE TABLE "Basic" ( "another_id" integer DEFAULT '2', "timest" timestamp(0), PRIMARY KEY ("id"), - Constraint "emailuniqueindex" UNIQUE ("email") + CONSTRAINT "emailuniqueindex" UNIQUE ("email") ); CREATE INDEX "titleindex" on "Basic" ("title"); - DROP TABLE "Another" CASCADE; CREATE TABLE "Another" ( "id" serial NOT NULL, PRIMARY KEY ("id") ); - - DROP VIEW "email_list"; CREATE VIEW "email_list" ( "email" ) AS ( SELECT email FROM Basic WHERE email IS NOT NULL diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t index 8203602..9a5d2e9 100644 --- a/t/47postgres-producer.t +++ b/t/47postgres-producer.t @@ -52,17 +52,17 @@ my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1, $field2); -is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL; -ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25);], +is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL +ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)], 'Alter field works'); $field1->name('field3'); my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1); -is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10);', 'Add field works'); +is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works'); my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2); -is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield;', 'Drop field works'); +is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works'); my $field3 = SQL::Translator::Schema::Field->new( name => 'time_field', table => $table, @@ -184,7 +184,7 @@ my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $cre my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS ( SELECT id, name FROM thing - );\n\n"; + )"; is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL'); my $view2 = SQL::Translator::Schema::View->new( @@ -200,5 +200,5 @@ my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $cre my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS ( SELECT id, name FROM thing - ) WITH CASCADED CHECK OPTION;\n\n"; + ) WITH CASCADED CHECK OPTION"; is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2'); diff --git a/t/49xml-to-pg-samefield.t b/t/49xml-to-pg-samefield.t index 4ec2644..2b4a8f8 100644 --- a/t/49xml-to-pg-samefield.t +++ b/t/49xml-to-pg-samefield.t @@ -38,8 +38,6 @@ CREATE TABLE "one" ( "same" character varying(100) DEFAULT 'hello' NOT NULL ); - - DROP TABLE "two" CASCADE; CREATE TABLE "two" ( "same" character varying(100) DEFAULT 'hello' NOT NULL diff --git a/t/data/diff/pgsql/create1.yml b/t/data/diff/pgsql/create1.yml new file mode 100644 index 0000000..3fd443a --- /dev/null +++ b/t/data/diff/pgsql/create1.yml @@ -0,0 +1,251 @@ +--- +schema: + procedures: {} + tables: + deleted: + constraints: + - fields: id + name: 'fk_fake' + reference_fields: + - fk_id + reference_table: fake + type: FOREIGN KEY + - fields: + - id + name: pk_id + type: UNIQUE + fields: + id: + data_type: int + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: id + order: 10 + size: + - 11 + indices: [] + name: deleted + options: [] + order: 3 + employee: + constraints: + - deferrable: 1 + expression: '' + fields: + - employee_id + match_type: '' + name: FK5302D47D93FE702E + on_delete: '' + on_update: '' + options: [] + reference_fields: + - person_id + reference_table: person + type: FOREIGN KEY + - deferrable: 1 + expression: '' + fields: + - position + - employee_id + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + fields: + employee_id: + data_type: int + default_value: ~ + extra: {} + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: employee_id + order: 8 + size: + - 11 + job_title: + data_type: varchar + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: job_title + order: 9 + size: + - 255 + position: + data_type: varchar + default_value: ~ + extra: {} + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: position + order: 7 + size: + - 50 + indices: [] + name: employee + order: 2 + old_name: + name: old_name + fields: + pk: + data_type: int + default_value: ~ + extra: {} + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 1 + name: pk + order: 1 + order: 4 + person: + constraints: + - deferrable: 1 + expression: '' + fields: + - person_id + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + - deferrable: 1 + expression: '' + fields: + - age + match_type: '' + name: UC_age_name + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: UNIQUE + fields: + age: + data_type: int + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 1 + name: age + order: 3 + size: + - 11 + description: + data_type: text + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: description + order: 6 + size: + - 65535 + iq: + data_type: smallint + default_value: 0 + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: iq + order: 5 + size: + - 4 + name: + data_type: varchar + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: name + order: 2 + size: + - 20 + person_id: + data_type: int + default_value: ~ + extra: {} + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: person_id + order: 1 + size: + - 11 + weight: + data_type: numeric + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: weight + order: 4 + size: + - 11 + - 2 + indices: + - fields: + - name + name: u_name + options: [] + type: UNIQUE + name: person + order: 1 + triggers: {} + views: {} +translator: + add_drop_table: 0 + filename: t/data/pgsql/create.sql + no_comments: 0 + parser_args: + db_password: ~ + db_user: ~ + dsn: ~ + field_separator: ~ + mysql_parser_version: ~ + record_separator: ~ + scan_fields: 1 + trim_fields: 1 + parser_type: SQL::Translator::Parser::PostgreSQL + producer_args: + add_prefix: ~ + add_truncate: ~ + db_password: ~ + db_user: ~ + dsn: ~ + imap_file: ~ + imap_url: ~ + indent: ~ + newlines: ~ + package_name: ~ + prefix: ~ + pretty: ~ + skip: ~ + skiplike: ~ + title: ~ + tt_conf: {} + tt_vars: {} + ttfile: ~ + producer_type: SQL::Translator::Producer::YAML + show_warnings: 0 + trace: 0 + version: 0.0899_01 diff --git a/t/data/diff/pgsql/create2.yml b/t/data/diff/pgsql/create2.yml new file mode 100644 index 0000000..42f9916 --- /dev/null +++ b/t/data/diff/pgsql/create2.yml @@ -0,0 +1,262 @@ +--- +schema: + procedures: {} + tables: + added: + constraints: [] + fields: + id: + data_type: int + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: id + order: 10 + size: + - 11 + indices: [] + name: added + options: [] + order: 3 + employee: + constraints: + - deferrable: 1 + expression: '' + fields: + - employee_id + match_type: '' + name: FK5302D47D93FE702E_diff + on_delete: '' + on_update: '' + options: [] + reference_fields: + - person_id + reference_table: person + type: FOREIGN KEY + - deferrable: 1 + expression: '' + fields: + - employee_id + - position + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + fields: + employee_id: + data_type: int + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 1 + is_unique: 0 + name: employee_id + order: 9 + size: + - 11 + position: + data_type: varchar + default_value: ~ + extra: {} + is_nullable: 0 + is_primary_key: 1 + is_unique: 0 + name: position + order: 8 + size: + - 50 + indices: [] + name: employee + order: 2 + new_name: + name: new_name + extra: + renamed_from: old_name + fields: + pk: + data_type: int + default_value: ~ + extra: {} + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 1 + name: pk + order: 1 + other: + data_type: int + name: new_field + order: 2 + order: 4 + person: + constraints: + - deferrable: 1 + expression: '' + fields: + - person_id + match_type: '' + name: '' + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: PRIMARY KEY + - deferrable: 1 + expression: '' + fields: + - person_id + match_type: '' + name: UC_person_id + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: UNIQUE + - deferrable: 1 + expression: '' + fields: + - age + - name + match_type: '' + name: UC_age_name + on_delete: '' + on_update: '' + options: [] + reference_fields: [] + reference_table: '' + type: UNIQUE + fields: + age: + data_type: int + default_value: 18 + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 1 + name: age + order: 3 + size: + - 11 + iq: + data_type: int + default_value: 0 + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: iq + order: 5 + size: + - 11 + is_rock_star: + data_type: smallint + default_value: 1 + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: is_rock_star + order: 6 + size: + - 4 + name: + data_type: varchar + default_value: ~ + extra: {} + is_nullable: 0 + is_primary_key: 0 + is_unique: 1 + name: name + order: 2 + size: + - 20 + person_id: + data_type: int + default_value: ~ + extra: {} + is_auto_increment: 1 + is_nullable: 0 + is_primary_key: 1 + is_unique: 1 + name: person_id + order: 1 + size: + - 11 + physical_description: + data_type: text + default_value: ~ + extra: + renamed_from: 'description' + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: physical_description + order: 7 + size: + - 65535 + weight: + data_type: numeric + default_value: ~ + extra: {} + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: weight + order: 4 + size: + - 11 + - 2 + indices: + - fields: + - name + name: unique_name + options: [] + type: UNIQUE + name: person + order: 1 + triggers: {} + views: {} +translator: + add_drop_table: 0 + filename: t/data/pgsql/create2.sql + no_comments: 0 + parser_args: + db_password: ~ + db_user: ~ + dsn: ~ + field_separator: ~ + mysql_parser_version: ~ + record_separator: ~ + scan_fields: 1 + trim_fields: 1 + parser_type: SQL::Translator::Parser::PostgreSQL + producer_args: + add_prefix: ~ + add_truncate: ~ + db_password: ~ + db_user: ~ + dsn: ~ + imap_file: ~ + imap_url: ~ + indent: ~ + newlines: ~ + package_name: ~ + prefix: ~ + pretty: ~ + skip: ~ + skiplike: ~ + title: ~ + tt_conf: {} + tt_vars: {} + ttfile: ~ + producer_type: SQL::Translator::Producer::YAML + show_warnings: 0 + trace: 0 + version: 0.0899_01