1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
#!/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_args => {
quote_identifiers => 1,
}
}
);
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 "employee" DROP CONSTRAINT "FK5302D47D93FE702E";
ALTER TABLE "employee" DROP COLUMN "job_title";
ALTER TABLE "employee" ADD CONSTRAINT "FK5302D47D93FE702E_diff" FOREIGN KEY ("employee_id")
REFERENCES "person" ("person_id") DEFERRABLE;
ALTER TABLE "old_name" RENAME TO "new_name";
ALTER TABLE "new_name" ADD COLUMN "new_field" integer;
ALTER TABLE "person" DROP CONSTRAINT "UC_age_name";
DROP INDEX "u_name";
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" ALTER COLUMN "nickname" SET NOT NULL;
ALTER TABLE "person" ALTER COLUMN "nickname" TYPE character varying(24);
ALTER TABLE "person" RENAME COLUMN "description" TO "physical_description";
ALTER TABLE "person" ADD CONSTRAINT "unique_name" UNIQUE ("name");
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_args => {
quote_identifiers => 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 employee DROP COLUMN job_title;
ALTER TABLE old_name RENAME TO new_name;
ALTER TABLE new_name ADD COLUMN new_field integer;
ALTER TABLE person DROP CONSTRAINT UC_age_name;
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 ALTER COLUMN nickname SET NOT NULL;
ALTER TABLE person ALTER COLUMN nickname TYPE character varying(24);
ALTER TABLE person RENAME COLUMN description TO physical_description;
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
# 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
|
