Commit | Line | Data |
819fe9ef |
1 | #!/usr/bin/perl -w |
1ded8513 |
2 | # vim:filetype=perl |
3 | |
4 | # |
5 | # Note that the bulk of the testing for the mysql producer is in |
6 | # 08postgres-to-mysql.t. This test is for additional stuff that can't be tested |
7 | # using an Oracle schema as source e.g. extra attributes. |
8 | # |
9 | |
10 | use strict; |
11 | use Test::More; |
12 | use Test::Exception; |
13 | use Test::SQL::Translator qw(maybe_plan); |
14 | |
15 | use Data::Dumper; |
16 | use FindBin qw/$Bin/; |
17 | |
18 | # Testing 1,2,3,4... |
19 | #============================================================================= |
20 | |
21 | BEGIN { |
f6af58ae |
22 | maybe_plan(11, |
1ded8513 |
23 | 'YAML', |
24 | 'SQL::Translator::Producer::MySQL', |
25 | 'Test::Differences', |
26 | ) |
27 | } |
28 | use Test::Differences; |
29 | use SQL::Translator; |
30 | |
31 | # Main test. |
32 | { |
33 | my $yaml_in = <<EOSCHEMA; |
34 | --- |
35 | schema: |
36 | tables: |
37 | thing: |
38 | name: thing |
39 | extra: |
40 | mysql_charset: latin1 |
41 | mysql_collate: latin1_danish_ci |
42 | order: 1 |
43 | fields: |
8c4efd11 |
44 | id: |
45 | name: id |
46 | data_type: unsigned int |
47 | is_primary_key: 1 |
48 | is_auto_increment: 1 |
49 | order: 0 |
1ded8513 |
50 | name: |
51 | name: name |
52 | data_type: varchar |
53 | size: |
54 | - 32 |
55 | order: 1 |
56 | swedish_name: |
57 | name: swedish_name |
58 | data_type: varchar |
59 | size: 32 |
60 | extra: |
61 | mysql_charset: swe7 |
62 | order: 2 |
63 | description: |
64 | name: description |
65 | data_type: text |
66 | extra: |
67 | mysql_charset: utf8 |
68 | mysql_collate: utf8_general_ci |
69 | order: 3 |
fe0f47d0 |
70 | constraints: |
71 | - type: UNIQUE |
72 | fields: |
73 | - name |
74 | name: idx_unique_name |
8c4efd11 |
75 | thing2: |
76 | name: thing2 |
77 | extra: |
78 | order: 2 |
79 | fields: |
80 | id: |
81 | name: id |
82 | data_type: int |
83 | is_primary_key: 0 |
84 | order: 0 |
85 | is_foreign_key: 1 |
86 | foo: |
87 | name: foo |
88 | data_type: int |
89 | order: 1 |
90 | is_not_null: 1 |
fb149f81 |
91 | foo2: |
92 | name: foo2 |
93 | data_type: int |
94 | order: 2 |
95 | is_not_null: 1 |
8c4efd11 |
96 | constraints: |
97 | - type: PRIMARY_KEY |
98 | fields: |
99 | - id |
100 | - foo |
101 | - reference_table: thing |
102 | type: FOREIGN_KEY |
103 | fields: foo |
104 | name: fk_thing |
fb149f81 |
105 | - reference_table: thing |
106 | type: FOREIGN_KEY |
107 | fields: foo2 |
108 | name: fk_thing |
8c4efd11 |
109 | |
1ded8513 |
110 | EOSCHEMA |
111 | |
f6af58ae |
112 | my @stmts = ( |
113 | "SET foreign_key_checks=0;\n\n", |
1ded8513 |
114 | |
f6af58ae |
115 | "DROP TABLE IF EXISTS `thing`;\n", |
116 | "CREATE TABLE `thing` ( |
fe0f47d0 |
117 | `id` unsigned int auto_increment, |
118 | `name` varchar(32), |
119 | `swedish_name` varchar(32) CHARACTER SET swe7, |
120 | `description` text CHARACTER SET utf8 COLLATE utf8_general_ci, |
121 | INDEX (`id`), |
122 | INDEX (`name`), |
123 | PRIMARY KEY (`id`), |
124 | UNIQUE `idx_unique_name` (`name`) |
f6af58ae |
125 | ) Type=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;\n\n", |
1ded8513 |
126 | |
f6af58ae |
127 | "DROP TABLE IF EXISTS `thing2`;\n", |
128 | "CREATE TABLE `thing2` ( |
fe0f47d0 |
129 | `id` integer, |
130 | `foo` integer, |
fb149f81 |
131 | `foo2` integer, |
fe0f47d0 |
132 | INDEX (`id`), |
133 | INDEX (`foo`), |
fb149f81 |
134 | INDEX (`foo2`), |
fe0f47d0 |
135 | PRIMARY KEY (`id`, `foo`), |
fb149f81 |
136 | CONSTRAINT `thing2_fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`), |
137 | CONSTRAINT `thing2_fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`) |
f6af58ae |
138 | ) Type=InnoDB;\n\n", |
8c4efd11 |
139 | |
f6af58ae |
140 | "SET foreign_key_checks=1;\n\n" |
141 | |
142 | ); |
143 | |
144 | my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts; |
145 | |
146 | my $mysql_out = join("", @stmts_no_drop); |
8c4efd11 |
147 | |
1ded8513 |
148 | |
149 | my $sqlt; |
150 | $sqlt = SQL::Translator->new( |
151 | show_warnings => 1, |
152 | no_comments => 1, |
8c4efd11 |
153 | # debug => 1, |
1ded8513 |
154 | from => "YAML", |
155 | to => "MySQL", |
fe0f47d0 |
156 | quote_table_names => 1, |
157 | quote_field_names => 1 |
1ded8513 |
158 | ); |
159 | |
819fe9ef |
160 | my $out = $sqlt->translate(\$yaml_in) |
161 | or die "Translate error:".$sqlt->error; |
f6af58ae |
162 | ok $out ne "", "Produced something!"; |
163 | eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting"; |
164 | |
165 | my @out = $sqlt->translate(\$yaml_in) |
166 | or die "Translat eerror:".$sqlt->error; |
167 | is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting"; |
fe0f47d0 |
168 | |
fe0f47d0 |
169 | |
f6af58ae |
170 | @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0); |
fe0f47d0 |
171 | $out = $sqlt->translate(\$yaml_in) |
f6af58ae |
172 | or die "Translat eerror:".$sqlt->error; |
173 | |
174 | @out = $sqlt->translate(\$yaml_in) |
175 | or die "Translat eerror:".$sqlt->error; |
fe0f47d0 |
176 | $mysql_out =~ s/`//g; |
f6af58ae |
177 | my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop; |
178 | eq_or_diff $out, $mysql_out, "Output looks right without quoting"; |
179 | is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting"; |
180 | |
181 | @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1); |
182 | @out = $sqlt->translate(\$yaml_in) |
183 | or die "Translat eerror:".$sqlt->error; |
184 | $out = $sqlt->translate(\$yaml_in) |
185 | or die "Translat eerror:".$sqlt->error; |
186 | |
187 | eq_or_diff $out, join("", @stmts), "Output looks right with DROP TABLEs"; |
188 | is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs"; |
1ded8513 |
189 | } |
8db4bd9d |
190 | |
191 | ############################################################################### |
192 | # New alter/add subs |
193 | |
194 | my $table = SQL::Translator::Schema::Table->new( name => 'mytable'); |
195 | |
196 | my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', |
197 | table => $table, |
198 | data_type => 'VARCHAR', |
199 | size => 10, |
200 | default_value => undef, |
201 | is_auto_increment => 0, |
202 | is_nullable => 1, |
203 | is_foreign_key => 0, |
204 | is_unique => 0 ); |
205 | |
206 | my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1); |
207 | |
208 | is($field1_sql, 'myfield VARCHAR(10)', 'Create field works'); |
209 | |
210 | my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', |
211 | table => $table, |
212 | data_type => 'VARCHAR', |
213 | size => 25, |
214 | default_value => undef, |
215 | is_auto_increment => 0, |
216 | is_nullable => 0, |
217 | is_foreign_key => 0, |
218 | is_unique => 0 ); |
219 | |
220 | my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, |
221 | $field2); |
222 | is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works'); |
223 | |
224 | my $add_field = SQL::Translator::Producer::MySQL::add_field($field1); |
225 | |
226 | is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works'); |
227 | |
228 | my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2); |
229 | is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works'); |