Commit | Line | Data |
70350518 |
1 | use strict; |
2 | use warnings; |
3 | |
637ca936 |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
637ca936 |
7 | |
8 | eval "use SQL::Translator"; |
9 | plan skip_all => 'SQL::Translator required' if $@; |
10 | |
661fc8eb |
11 | my $schema = DBICTest->init_schema; |
637ca936 |
12 | |
0da8b7da |
13 | plan tests => 160; |
637ca936 |
14 | |
661fc8eb |
15 | my $translator = SQL::Translator->new( |
16 | parser_args => { |
17 | 'DBIx::Schema' => $schema, |
18 | }, |
19 | producer_args => {}, |
637ca936 |
20 | ); |
21 | |
22 | $translator->parser('SQL::Translator::Parser::DBIx::Class'); |
23 | $translator->producer('SQLite'); |
24 | |
25 | my $output = $translator->translate(); |
26 | |
256e87b0 |
27 | |
28 | ok($output, "SQLT produced someoutput") |
29 | or diag($translator->error); |
30 | |
b1edf9f9 |
31 | # Note that the constraints listed here are the only ones that are tested -- if |
32 | # more exist in the Schema than are listed here and all listed constraints are |
c75b18e9 |
33 | # correct, the test will still pass. If you add a class with UNIQUE or FOREIGN |
34 | # KEY constraints to DBICTest::Schema, add tests here if you think the existing |
35 | # test coverage is not sufficient |
b1edf9f9 |
36 | |
37 | my %fk_constraints = ( |
661fc8eb |
38 | |
39 | # TwoKeys |
b1edf9f9 |
40 | twokeys => [ |
41 | { |
42 | 'display' => 'twokeys->cd', |
0da8b7da |
43 | 'name' => 'twokeys_fk_cd', 'index_name' => 'cd', |
b1edf9f9 |
44 | 'selftable' => 'twokeys', 'foreigntable' => 'cd', |
45 | 'selfcols' => ['cd'], 'foreigncols' => ['cdid'], |
13de943d |
46 | on_delete => '', on_update => '', deferrable => 0, |
b1edf9f9 |
47 | }, |
48 | { |
49 | 'display' => 'twokeys->artist', |
0da8b7da |
50 | 'name' => 'twokeys_fk_artist', 'index_name' => 'artist', |
b1edf9f9 |
51 | 'selftable' => 'twokeys', 'foreigntable' => 'artist', |
52 | 'selfcols' => ['artist'], 'foreigncols' => ['artistid'], |
e394339b |
53 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
54 | }, |
55 | ], |
661fc8eb |
56 | |
57 | # FourKeys_to_TwoKeys |
b1edf9f9 |
58 | fourkeys_to_twokeys => [ |
59 | { |
60 | 'display' => 'fourkeys_to_twokeys->twokeys', |
0da8b7da |
61 | 'name' => 'fourkeys_to_twokeys_fk_t_cd_t_artist', 'index_name' => 't_cd_t_artist', |
b1edf9f9 |
62 | 'selftable' => 'fourkeys_to_twokeys', 'foreigntable' => 'twokeys', |
63 | 'selfcols' => ['t_artist', 't_cd'], 'foreigncols' => ['artist', 'cd'], |
e394339b |
64 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
65 | }, |
66 | { |
0da8b7da |
67 | 'display' => 'fourkeys_to_twokeys->fourkeys', 'index_name' => 'f_foo_f_goodbye_f_hello_f_bar', |
68 | 'name' => 'fourkeys_to_twokeys_fk_f_foo_f_goodbye_f_hello_f_bar', |
b1edf9f9 |
69 | 'selftable' => 'fourkeys_to_twokeys', 'foreigntable' => 'fourkeys', |
70 | 'selfcols' => [qw(f_foo f_bar f_hello f_goodbye)], |
71 | 'foreigncols' => [qw(foo bar hello goodbye)], |
e394339b |
72 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
73 | }, |
74 | ], |
661fc8eb |
75 | |
76 | # CD_to_Producer |
b1edf9f9 |
77 | cd_to_producer => [ |
78 | { |
79 | 'display' => 'cd_to_producer->cd', |
0da8b7da |
80 | 'name' => 'cd_to_producer_fk_cd', 'index_name' => 'cd', |
b1edf9f9 |
81 | 'selftable' => 'cd_to_producer', 'foreigntable' => 'cd', |
82 | 'selfcols' => ['cd'], 'foreigncols' => ['cdid'], |
e394339b |
83 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
84 | }, |
85 | { |
86 | 'display' => 'cd_to_producer->producer', |
0da8b7da |
87 | 'name' => 'cd_to_producer_fk_producer', 'index_name' => 'producer', |
b1edf9f9 |
88 | 'selftable' => 'cd_to_producer', 'foreigntable' => 'producer', |
89 | 'selfcols' => ['producer'], 'foreigncols' => ['producerid'], |
e394339b |
90 | on_delete => '', on_update => '', deferrable => 1, |
b1edf9f9 |
91 | }, |
92 | ], |
661fc8eb |
93 | |
94 | # Self_ref_alias |
b1edf9f9 |
95 | self_ref_alias => [ |
96 | { |
97 | 'display' => 'self_ref_alias->self_ref for self_ref', |
0da8b7da |
98 | 'name' => 'self_ref_alias_fk_self_ref', 'index_name' => 'self_ref', |
b1edf9f9 |
99 | 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref', |
100 | 'selfcols' => ['self_ref'], 'foreigncols' => ['id'], |
e394339b |
101 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
102 | }, |
103 | { |
104 | 'display' => 'self_ref_alias->self_ref for alias', |
0da8b7da |
105 | 'name' => 'self_ref_alias_fk_alias', 'index_name' => 'alias', |
b1edf9f9 |
106 | 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref', |
107 | 'selfcols' => ['alias'], 'foreigncols' => ['id'], |
e394339b |
108 | on_delete => '', on_update => '', deferrable => 1, |
b1edf9f9 |
109 | }, |
110 | ], |
661fc8eb |
111 | |
112 | # CD |
b1edf9f9 |
113 | cd => [ |
114 | { |
115 | 'display' => 'cd->artist', |
0da8b7da |
116 | 'name' => 'cd_fk_artist', 'index_name' => 'artist', |
b1edf9f9 |
117 | 'selftable' => 'cd', 'foreigntable' => 'artist', |
118 | 'selfcols' => ['artist'], 'foreigncols' => ['artistid'], |
13de943d |
119 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
120 | }, |
121 | ], |
661fc8eb |
122 | |
123 | # Artist_undirected_map |
b1edf9f9 |
124 | artist_undirected_map => [ |
125 | { |
126 | 'display' => 'artist_undirected_map->artist for id1', |
0da8b7da |
127 | 'name' => 'artist_undirected_map_fk_id1', 'index_name' => 'id1', |
b1edf9f9 |
128 | 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist', |
129 | 'selfcols' => ['id1'], 'foreigncols' => ['artistid'], |
e394339b |
130 | on_delete => 'CASCADE', on_update => '', deferrable => 1, |
b1edf9f9 |
131 | }, |
132 | { |
133 | 'display' => 'artist_undirected_map->artist for id2', |
0da8b7da |
134 | 'name' => 'artist_undirected_map_fk_id2', 'index_name' => 'id2', |
b1edf9f9 |
135 | 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist', |
136 | 'selfcols' => ['id2'], 'foreigncols' => ['artistid'], |
e394339b |
137 | on_delete => 'CASCADE', on_update => '', deferrable => 1, |
b1edf9f9 |
138 | }, |
139 | ], |
661fc8eb |
140 | |
141 | # Track |
b1edf9f9 |
142 | track => [ |
143 | { |
144 | 'display' => 'track->cd', |
0da8b7da |
145 | 'name' => 'track_fk_cd', 'index_name' => 'cd', |
b1edf9f9 |
146 | 'selftable' => 'track', 'foreigntable' => 'cd', |
147 | 'selfcols' => ['cd'], 'foreigncols' => ['cdid'], |
e394339b |
148 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
149 | }, |
150 | ], |
661fc8eb |
151 | |
152 | # TreeLike |
b1edf9f9 |
153 | treelike => [ |
154 | { |
155 | 'display' => 'treelike->treelike for parent', |
0da8b7da |
156 | 'name' => 'treelike_fk_parent', 'index_name' => 'parent', |
b1edf9f9 |
157 | 'selftable' => 'treelike', 'foreigntable' => 'treelike', |
158 | 'selfcols' => ['parent'], 'foreigncols' => ['id'], |
e394339b |
159 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
160 | }, |
161 | ], |
162 | |
163 | # TwoKeyTreeLike |
164 | twokeytreelike => [ |
165 | { |
166 | 'display' => 'twokeytreelike->twokeytreelike for parent1,parent2', |
0da8b7da |
167 | 'name' => 'twokeytreelike_fk_parent1_parent2', 'index_name' => 'parent1_parent2', |
b1edf9f9 |
168 | 'selftable' => 'twokeytreelike', 'foreigntable' => 'twokeytreelike', |
169 | 'selfcols' => ['parent1', 'parent2'], 'foreigncols' => ['id1','id2'], |
e394339b |
170 | on_delete => '', on_update => '', deferrable => 1, |
b1edf9f9 |
171 | }, |
172 | ], |
ae515736 |
173 | |
661fc8eb |
174 | # Tags |
b1edf9f9 |
175 | tags => [ |
176 | { |
177 | 'display' => 'tags->cd', |
0da8b7da |
178 | 'name' => 'tags_fk_cd', 'index_name' => 'cd', |
b1edf9f9 |
179 | 'selftable' => 'tags', 'foreigntable' => 'cd', |
180 | 'selfcols' => ['cd'], 'foreigncols' => ['cdid'], |
e394339b |
181 | on_delete => 'CASCADE', on_update => 'CASCADE', deferrable => 1, |
b1edf9f9 |
182 | }, |
183 | ], |
661fc8eb |
184 | |
185 | # Bookmark |
b1edf9f9 |
186 | bookmark => [ |
187 | { |
188 | 'display' => 'bookmark->link', |
0da8b7da |
189 | 'name' => 'bookmark_fk_link', 'index_name' => 'link', |
b1edf9f9 |
190 | 'selftable' => 'bookmark', 'foreigntable' => 'link', |
191 | 'selfcols' => ['link'], 'foreigncols' => ['id'], |
e394339b |
192 | on_delete => '', on_update => '', deferrable => 1, |
b1edf9f9 |
193 | }, |
194 | ], |
a0024650 |
195 | # ForceForeign |
196 | forceforeign => [ |
197 | { |
198 | 'display' => 'forceforeign->artist', |
0da8b7da |
199 | 'name' => 'forceforeign_fk_artist', 'index_name' => 'artist', |
a0024650 |
200 | 'selftable' => 'forceforeign', 'foreigntable' => 'artist', |
201 | 'selfcols' => ['artist'], 'foreigncols' => ['artist_id'], |
e394339b |
202 | on_delete => '', on_update => '', deferrable => 1, |
a0024650 |
203 | }, |
204 | ], |
205 | |
0da8b7da |
206 | # LongColumns |
207 | long_columns => [ |
208 | { |
209 | 'display' => 'long_columns->owner', |
210 | 'name' => 'long_columns_fk_64_character_column_aaaaaaaaaaaaaaaaaaa_1ca973e2', |
211 | 'index_name' => '64_character_column_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', |
212 | 'selftable' => 'long_columns', 'foreigntable' => 'long_columns', |
213 | 'selfcols' => ['64_character_column_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'], |
214 | 'foreigncols' => ['lcid'], |
215 | on_delete => '', on_update => '', deferrable => 1, |
216 | }, |
217 | { |
218 | 'display' => 'long_columns->owner2', |
219 | 'name' => 'long_columns_fk_32_character_column_aaaaaaaaaaaa_32_cha_6060a8f3', |
220 | 'index_name' => '32_character_column_aaaaaaaaaaaa_32_character_column_bb_30f7a7fe', |
221 | 'selftable' => 'long_columns', 'foreigntable' => 'long_columns', |
222 | 'selfcols' => ['32_character_column_bbbbbbbbbbbb', '32_character_column_aaaaaaaaaaaa'], |
223 | 'foreigncols' => ['32_character_column_aaaaaaaaaaaa', '32_character_column_bbbbbbbbbbbb'], |
224 | on_delete => '', on_update => '', deferrable => 1, |
225 | }, |
226 | { |
227 | 'display' => 'long_columns->owner3', |
228 | 'name' => 'long_columns_fk_16_character_col', |
229 | 'index_name' => '16_character_col', |
230 | 'selftable' => 'long_columns', 'foreigntable' => 'long_columns', |
231 | 'selfcols' => ['16_character_col'], 'foreigncols' => ['8_char_c'], |
232 | on_delete => '', on_update => '', deferrable => 1, |
233 | }, |
234 | ], |
b1edf9f9 |
235 | ); |
236 | |
237 | my %unique_constraints = ( |
238 | # CD |
239 | cd => [ |
240 | { |
241 | 'display' => 'cd artist and title unique', |
0da8b7da |
242 | 'name' => 'cd_artist_title', |
b1edf9f9 |
243 | 'table' => 'cd', 'cols' => ['artist', 'title'], |
244 | }, |
245 | ], |
246 | |
247 | # Producer |
248 | producer => [ |
249 | { |
250 | 'display' => 'producer name unique', |
0da8b7da |
251 | 'name' => 'prod_name', # explicit name |
b1edf9f9 |
252 | 'table' => 'producer', 'cols' => ['name'], |
253 | }, |
254 | ], |
255 | |
0da8b7da |
256 | long_columns => [ |
257 | { |
258 | 'display' => 'long but not quite truncated unique', |
259 | 'name' => 'long_columns_16_character_col_32_character_column_aaaaaaaaaaaa', |
260 | 'table' => 'long_columns', 'cols' => [qw( 32_character_column_aaaaaaaaaaaa 16_character_col )], |
261 | }, |
262 | { |
263 | 'display' => 'multi column truncated unique', |
264 | 'name' => 'long_columns_8_char_c_16_character_col_32_character_col_ee4a438c', |
265 | 'table' => 'long_columns', 'cols' => [qw( 32_character_column_aaaaaaaaaaaa 16_character_col 8_char_c )], |
266 | }, |
267 | { |
268 | 'display' => 'different multi column truncated unique with same base', |
269 | 'name' => 'long_columns_8_char_c_16_character_col_32_character_col_c5dbc7a7', |
270 | 'table' => 'long_columns', 'cols' => [qw( 32_character_column_bbbbbbbbbbbb 16_character_col 8_char_c )], |
271 | }, |
272 | { |
273 | 'display' => 'single column truncated unique', |
274 | 'name' => 'long_columns_64_character_column_aaaaaaaaaaaaaaaaaaaaaa_095dc664', |
275 | 'table' => 'long_columns', 'cols' => ['64_character_column_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'], |
276 | }, |
277 | ], |
278 | |
b1edf9f9 |
279 | # TwoKeyTreeLike |
280 | twokeytreelike => [ |
281 | { |
282 | 'display' => 'twokeytreelike name unique', |
0da8b7da |
283 | 'name' => 'tktlnameunique', # explicit name |
b1edf9f9 |
284 | 'table' => 'twokeytreelike', 'cols' => ['name'], |
285 | }, |
286 | ], |
287 | |
288 | # Employee |
289 | # Constraint is commented out in DBICTest/Schema/Employee.pm |
290 | # employee => [ |
291 | # { |
292 | # 'display' => 'employee position and group_id unique', |
0da8b7da |
293 | # 'name' => 'position_group', |
b1edf9f9 |
294 | # 'table' => 'employee', cols => ['position', 'group_id'], |
295 | # }, |
296 | # ], |
7b90bb13 |
297 | ); |
298 | |
17cab2f0 |
299 | my %indexes = ( |
c385ecea |
300 | artist => [ |
301 | { |
302 | 'fields' => ['name'] |
303 | }, |
304 | ] |
305 | ); |
306 | |
637ca936 |
307 | my $tschema = $translator->schema(); |
d6c79cb3 |
308 | # Test that the $schema->sqlt_deploy_hook was called okay and that it removed |
309 | # the 'link' table |
310 | ok( !defined($tschema->get_table('link')), "Link table was removed by hook"); |
311 | |
b1edf9f9 |
312 | # Test that nonexistent constraints are not found |
313 | my $constraint = get_constraint('FOREIGN KEY', 'cd', ['title'], 'cd', ['year']); |
314 | ok( !defined($constraint), 'nonexistent FOREIGN KEY constraint not found' ); |
315 | $constraint = get_constraint('UNIQUE', 'cd', ['artist']); |
316 | ok( !defined($constraint), 'nonexistent UNIQUE constraint not found' ); |
a0024650 |
317 | $constraint = get_constraint('FOREIGN KEY', 'forceforeign', ['cd'], 'cd', ['cdid']); |
318 | ok( !defined($constraint), 'forced nonexistent FOREIGN KEY constraint not found' ); |
b1edf9f9 |
319 | |
320 | for my $expected_constraints (keys %fk_constraints) { |
321 | for my $expected_constraint (@{ $fk_constraints{$expected_constraints} }) { |
322 | my $desc = $expected_constraint->{display}; |
323 | my $constraint = get_constraint( |
324 | 'FOREIGN KEY', |
325 | $expected_constraint->{selftable}, $expected_constraint->{selfcols}, |
326 | $expected_constraint->{foreigntable}, $expected_constraint->{foreigncols}, |
327 | ); |
328 | ok( defined($constraint), "FOREIGN KEY constraint matching `$desc' found" ); |
329 | test_fk($expected_constraint, $constraint); |
661fc8eb |
330 | } |
637ca936 |
331 | } |
332 | |
b1edf9f9 |
333 | for my $expected_constraints (keys %unique_constraints) { |
334 | for my $expected_constraint (@{ $unique_constraints{$expected_constraints} }) { |
335 | my $desc = $expected_constraint->{display}; |
336 | my $constraint = get_constraint( |
337 | 'UNIQUE', $expected_constraint->{table}, $expected_constraint->{cols}, |
338 | ); |
339 | ok( defined($constraint), "UNIQUE constraint matching `$desc' found" ); |
0da8b7da |
340 | test_unique($expected_constraint, $constraint); |
b1edf9f9 |
341 | } |
637ca936 |
342 | } |
343 | |
17cab2f0 |
344 | for my $table_index (keys %indexes) { |
345 | for my $expected_index ( @{ $indexes{$table_index} } ) { |
c385ecea |
346 | |
347 | ok ( get_index($table_index, $expected_index), "Got a matching index on $table_index table"); |
348 | } |
349 | } |
350 | |
b1edf9f9 |
351 | # Returns the Constraint object for the specified constraint type, table and |
352 | # columns from the SQL::Translator schema, or undef if no matching constraint |
353 | # is found. |
354 | # |
355 | # NB: $type is either 'FOREIGN KEY' or 'UNIQUE'. In UNIQUE constraints the last |
356 | # two parameters are not used. |
357 | sub get_constraint { |
358 | my ($type, $table_name, $cols, $f_table, $f_cols) = @_; |
359 | $f_table ||= ''; # For UNIQUE constraints, reference_table is '' |
360 | $f_cols ||= []; |
361 | |
362 | my $table = $tschema->get_table($table_name); |
363 | |
364 | my %fields = map { $_ => 1 } @$cols; |
365 | my %f_fields = map { $_ => 1 } @$f_cols; |
366 | |
367 | CONSTRAINT: |
368 | for my $constraint ( $table->get_constraints ) { |
369 | next unless $constraint->type eq $type; |
370 | next unless $constraint->reference_table eq $f_table; |
371 | |
372 | my %rev_fields = map { $_ => 1 } $constraint->fields; |
373 | my %rev_f_fields = map { $_ => 1 } $constraint->reference_fields; |
374 | |
375 | # Check that the given fields are a subset of the constraint's fields |
376 | for my $field ($constraint->fields) { |
377 | next CONSTRAINT unless $fields{$field}; |
378 | } |
379 | if ($type eq 'FOREIGN KEY') { |
380 | for my $f_field ($constraint->reference_fields) { |
381 | next CONSTRAINT unless $f_fields{$f_field}; |
661fc8eb |
382 | } |
b1edf9f9 |
383 | } |
661fc8eb |
384 | |
b1edf9f9 |
385 | # Check that the constraint's fields are a subset of the given fields |
386 | for my $field (@$cols) { |
387 | next CONSTRAINT unless $rev_fields{$field}; |
388 | } |
389 | if ($type eq 'FOREIGN KEY') { |
390 | for my $f_field (@$f_cols) { |
391 | next CONSTRAINT unless $rev_f_fields{$f_field}; |
661fc8eb |
392 | } |
393 | } |
b1edf9f9 |
394 | |
395 | return $constraint; # everything passes, found the constraint |
661fc8eb |
396 | } |
b1edf9f9 |
397 | return undef; # didn't find a matching constraint |
7b90bb13 |
398 | } |
399 | |
c385ecea |
400 | sub get_index { |
401 | my ($table_name, $index) = @_; |
402 | |
403 | my $table = $tschema->get_table($table_name); |
404 | |
405 | CAND_INDEX: |
406 | for my $cand_index ( $table->get_indices ) { |
407 | |
408 | next CAND_INDEX if $index->{name} && $cand_index->name ne $index->{name} |
409 | || $index->{type} && $cand_index->type ne $index->{type}; |
410 | |
411 | my %idx_fields = map { $_ => 1 } $cand_index->fields; |
412 | |
413 | for my $field ( @{ $index->{fields} } ) { |
414 | next CAND_INDEX unless $idx_fields{$field}; |
415 | } |
416 | |
417 | %idx_fields = map { $_ => 1 } @{$index->{fields}}; |
418 | for my $field ( $cand_index->fields) { |
419 | next CAND_INDEX unless $idx_fields{$field}; |
420 | } |
421 | |
422 | return $cand_index; |
423 | } |
424 | |
425 | return undef; # No matching idx |
426 | } |
427 | |
b1edf9f9 |
428 | # Test parameters in a FOREIGN KEY constraint other than columns |
429 | sub test_fk { |
430 | my ($expected, $got) = @_; |
431 | my $desc = $expected->{display}; |
0da8b7da |
432 | is( $got->name, $expected->{name}, |
433 | "name parameter correct for `$desc'" ); |
b1edf9f9 |
434 | is( $got->on_delete, $expected->{on_delete}, |
435 | "on_delete parameter correct for `$desc'" ); |
436 | is( $got->on_update, $expected->{on_update}, |
437 | "on_update parameter correct for `$desc'" ); |
13de943d |
438 | is( $got->deferrable, $expected->{deferrable}, |
439 | "is_deferrable parameter correct for `$desc'" ); |
0da8b7da |
440 | |
441 | my $index = get_index( $got->table, { fields => $expected->{selfcols} } ); |
442 | ok( defined $index, "index exists for `$desc'" ); |
443 | is( $index->name, $expected->{index_name}, "index has correct name for `$desc'" ); |
444 | } |
445 | |
446 | sub test_unique { |
447 | my ($expected, $got) = @_; |
448 | my $desc = $expected->{display}; |
449 | is( $got->name, $expected->{name}, |
450 | "name parameter correct for `$desc'" ); |
637ca936 |
451 | } |