fix diff for altering two things per column - add ; at the end
[dbsrgits/SQL-Translator.git] / t / 30sqlt-new-diff-pgsql.t
CommitLineData
3406fd5b 1#!/usr/bin/perl
2# vim: set ft=perl:
3
4use strict;
5use warnings;
6use SQL::Translator;
7
8use File::Spec::Functions qw(catfile updir tmpdir);
9use FindBin qw($Bin);
10use Test::More;
11use Test::Differences;
12use Test::SQL::Translator qw(maybe_plan);
13use SQL::Translator::Schema::Constants;
14use Storable 'dclone';
15
16plan tests => 4;
17
18use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
19
20my $tr = SQL::Translator->new;
21
22my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
23 my $t = SQL::Translator->new;
24 $t->parser( 'YAML' )
25 or die $tr->error;
26 my $out = $t->translate( catfile($Bin, qw/data diff pgsql/, $_ ) )
27 or die $tr->error;
aee4b66e 28
3406fd5b 29 my $schema = $t->schema;
30 unless ( $schema->name ) {
31 $schema->name( $_ );
32 }
33 ($schema);
34} (qw( create1.yml create2.yml ));
35
36# Test for differences
37my $out = SQL::Translator::Diff::schema_diff(
38 $source_schema,
39 'PostgreSQL',
40 $target_schema,
41 'PostgreSQL',
42 {
1f5b2625 43 producer_args => {
9683e26b 44 quote_identifiers => 0,
3406fd5b 45 }
46 }
47);
c50d1a0a 48
3406fd5b 49eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
50-- Convert schema 'create1.yml' to 'create2.yml':;
51
52BEGIN;
53
54CREATE TABLE added (
9683e26b 55 id bigint
3406fd5b 56);
57
58ALTER TABLE old_name RENAME TO new_name;
59
681dc480 60ALTER TABLE employee DROP CONSTRAINT FK5302D47D93FE702E;
3406fd5b 61
62ALTER TABLE person DROP CONSTRAINT UC_age_name;
63
64DROP INDEX u_name;
65
66ALTER TABLE employee DROP COLUMN job_title;
67
68ALTER TABLE new_name ADD COLUMN new_field integer;
69
06baeb21 70ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT 1;
3406fd5b 71
72ALTER TABLE person ALTER COLUMN person_id TYPE serial;
73
74ALTER TABLE person ALTER COLUMN name SET NOT NULL;
75
76ALTER TABLE person ALTER COLUMN age SET DEFAULT 18;
77
78ALTER TABLE person ALTER COLUMN iq TYPE bigint;
79
8953d9e7 80ALTER TABLE person ALTER COLUMN nickname SET NOT NULL;
81ALTER TABLE person ALTER COLUMN nickname TYPE character varying(24);
82
3406fd5b 83ALTER TABLE person RENAME COLUMN description TO physical_description;
84
5f31ed66 85ALTER TABLE person ADD CONSTRAINT unique_name UNIQUE (name);
3406fd5b 86
681dc480 87ALTER TABLE employee ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id)
3406fd5b 88 REFERENCES person (person_id) DEFERRABLE;
89
5f31ed66 90ALTER TABLE person ADD CONSTRAINT UC_person_id UNIQUE (person_id);
3406fd5b 91
5f31ed66 92ALTER TABLE person ADD CONSTRAINT UC_age_name UNIQUE (age, name);
3406fd5b 93
94DROP TABLE deleted CASCADE;
95
96
97COMMIT;
98
99## END OF DIFF
100
101$out = SQL::Translator::Diff::schema_diff(
102 $source_schema, 'PostgreSQL', $target_schema, 'PostgreSQL',
103 { ignore_index_names => 1,
104 ignore_constraint_names => 1,
1f5b2625 105 producer_args => {
3406fd5b 106 quote_table_names => 0,
107 quote_field_names => 0,
108 }
109 });
110
111eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
112-- Convert schema 'create1.yml' to 'create2.yml':;
113
114BEGIN;
115
963fd5fc 116CREATE TABLE added (
3406fd5b 117 id bigint
118);
119
120ALTER TABLE old_name RENAME TO new_name;
121
122ALTER TABLE person DROP CONSTRAINT UC_age_name;
123
124ALTER TABLE employee DROP COLUMN job_title;
125
963fd5fc 126ALTER TABLE new_name ADD COLUMN new_field integer;
3406fd5b 127
06baeb21 128ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT 1;
3406fd5b 129
130ALTER TABLE person ALTER COLUMN person_id TYPE serial;
131
132ALTER TABLE person ALTER COLUMN name SET NOT NULL;
133
134ALTER TABLE person ALTER COLUMN age SET DEFAULT 18;
135
136ALTER TABLE person ALTER COLUMN iq TYPE bigint;
137
8953d9e7 138ALTER TABLE person ALTER COLUMN nickname SET NOT NULL;
139ALTER TABLE person ALTER COLUMN nickname TYPE character varying(24);
140
3406fd5b 141ALTER TABLE person RENAME COLUMN description TO physical_description;
142
5f31ed66 143ALTER TABLE person ADD CONSTRAINT UC_person_id UNIQUE (person_id);
3406fd5b 144
5f31ed66 145ALTER TABLE person ADD CONSTRAINT UC_age_name UNIQUE (age, name);
3406fd5b 146
147DROP TABLE deleted CASCADE;
148
149
150COMMIT;
151
152## END OF DIFF
153
154
155# Test for sameness
156$out = SQL::Translator::Diff::schema_diff(
157 $source_schema, 'PostgreSQL', $source_schema, 'PostgreSQL'
158);
159
160eq_or_diff($out, <<'## END OF DIFF', "No differences found");
161-- Convert schema 'create1.yml' to 'create1.yml':;
162
163-- No differences found;
164
165## END OF DIFF