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
|
#!/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 => 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 CONSTRAINT FK5302D47D93FE702E_diff 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_args => {
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 added (
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_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 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
|
