fixed up sqlt tests a bit and tried fixing 2 failing sqlt tests
[dbsrgits/DBIx-Class-Historic.git] / t / 86sqlt.t
CommitLineData
70350518 1use strict;
2use warnings;
3
637ca936 4use Test::More;
5use lib qw(t/lib);
6use DBICTest;
637ca936 7
8eval "use SQL::Translator";
9plan skip_all => 'SQL::Translator required' if $@;
10
8e6df6ae 11# do not taunt happy dave ball
12
661fc8eb 13my $schema = DBICTest->init_schema;
637ca936 14
368a5228 15plan tests => 33;
637ca936 16
661fc8eb 17my $translator = SQL::Translator->new(
18 parser_args => {
19 'DBIx::Schema' => $schema,
20 },
21 producer_args => {},
637ca936 22);
23
24$translator->parser('SQL::Translator::Parser::DBIx::Class');
25$translator->producer('SQLite');
26
27my $output = $translator->translate();
28
661fc8eb 29my @fk_constraints = (
30
31 # TwoKeys
637ca936 32 {'display' => 'twokeys->cd',
33 'selftable' => 'twokeys', 'foreigntable' => 'cd',
34 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
35 'needed' => 1, on_delete => '', on_update => ''},
36 {'display' => 'twokeys->artist',
37 'selftable' => 'twokeys', 'foreigntable' => 'artist',
38 'selfcols' => ['artist'], 'foreigncols' => ['artistid'],
de60a93d 39 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
661fc8eb 40
41 # FourKeys_to_TwoKeys
42 {'display' => 'fourkeys_to_twokeys->twokeys',
43 'selftable' => 'fourkeys_to_twokeys', 'foreigntable' => 'twokeys',
44 'selfcols' => ['t_artist', 't_cd'], 'foreigncols' => ['artist', 'cd'],
45 'needed' => 0, on_delete => '', on_update => ''},
46 {'display' => 'fourkeys_to_twokeys->fourkeys',
47 'selftable' => 'fourkeys_to_twokeys', 'foreigntable' => 'fourkeys',
48 'selfcols' => [qw(f_foo f_bar f_hello f_goodbye)],
49 'foreigncols' => [qw(foo bar hello goodbye)],
50 'needed' => 0, on_delete => '', on_update => ''},
51
52 # CD_to_Producer
637ca936 53 {'display' => 'cd_to_producer->cd',
54 'selftable' => 'cd_to_producer', 'foreigntable' => 'cd',
55 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
de60a93d 56 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 57 {'display' => 'cd_to_producer->producer',
58 'selftable' => 'cd_to_producer', 'foreigntable' => 'producer',
59 'selfcols' => ['producer'], 'foreigncols' => ['producerid'],
60 'needed' => 1, on_delete => '', on_update => ''},
661fc8eb 61
62 # Self_ref_alias
637ca936 63 {'display' => 'self_ref_alias -> self_ref for self_ref',
64 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
65 'selfcols' => ['self_ref'], 'foreigncols' => ['id'],
de60a93d 66 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 67 {'display' => 'self_ref_alias -> self_ref for alias',
68 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
69 'selfcols' => ['alias'], 'foreigncols' => ['id'],
70 'needed' => 1, on_delete => '', on_update => ''},
661fc8eb 71
72 # CD
637ca936 73 {'display' => 'cd -> artist',
74 'selftable' => 'cd', 'foreigntable' => 'artist',
75 'selfcols' => ['artist'], 'foreigncols' => ['artistid'],
de60a93d 76 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
661fc8eb 77
78 # Artist_undirected_map
637ca936 79 {'display' => 'artist_undirected_map -> artist for id1',
80 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
81 'selfcols' => ['id1'], 'foreigncols' => ['artistid'],
de60a93d 82 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
637ca936 83 {'display' => 'artist_undirected_map -> artist for id2',
84 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
85 'selfcols' => ['id2'], 'foreigncols' => ['artistid'],
de60a93d 86 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
661fc8eb 87
88 # Track
637ca936 89 {'display' => 'track->cd',
90 'selftable' => 'track', 'foreigntable' => 'cd',
91 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
de60a93d 92 'needed' => 2, on_delete => 'CASCADE', on_update => 'CASCADE'},
661fc8eb 93
94 # TreeLike
637ca936 95 {'display' => 'treelike -> treelike for parent',
96 'selftable' => 'treelike', 'foreigntable' => 'treelike',
97 'selfcols' => ['parent'], 'foreigncols' => ['id'],
98 'needed' => 1, on_delete => '', on_update => ''},
ae515736 99
100 # shouldn't this be generated?
101 #
102 #{'display' => 'twokeytreelike -> twokeytreelike for parent1,parent2',
103 # 'selftable' => 'twokeytreelike', 'foreigntable' => 'twokeytreelike',
104 # 'selfcols' => ['parent1', 'parent2'], 'foreigncols' => ['id1','id2'],
105 # 'needed' => 1, on_delete => '', on_update => ''},
106
661fc8eb 107 # Tags
637ca936 108 {'display' => 'tags -> cd',
109 'selftable' => 'tags', 'foreigntable' => 'cd',
110 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
de60a93d 111 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
661fc8eb 112
113 # Bookmark
81155549 114 {'display' => 'bookmark -> link',
115 'selftable' => 'bookmark', 'foreigntable' => 'link',
116 'selfcols' => ['link'], 'foreigncols' => ['id'],
117 'needed' => 1, on_delete => '', on_update => ''},
637ca936 118 );
119
7b90bb13 120my @unique_constraints = (
121 {'display' => 'cd artist and title unique',
122 'table' => 'cd', 'cols' => ['artist', 'title'],
123 'needed' => 1},
368a5228 124 {'display' => 'producer name unique',
125 'table' => 'producer', 'cols' => ['name'],
126 'needed' => 1},
7b90bb13 127 {'display' => 'twokeytreelike name unique',
128 'table' => 'twokeytreelike', 'cols' => ['name'],
129 'needed' => 1},
78145e31 130# {'display' => 'employee position and group_id unique',
131# 'table' => 'employee', cols => ['position', 'group_id'],
132# 'needed' => 1},
7b90bb13 133);
134
637ca936 135my $tschema = $translator->schema();
136for my $table ($tschema->get_tables) {
661fc8eb 137 my $table_name = $table->name;
138 for my $c ( $table->get_constraints ) {
139 if ($c->type eq 'FOREIGN KEY') {
140 ok( check_fk($table_name, scalar $c->fields,
141 $c->reference_table, scalar $c->reference_fields,
142 $c->on_delete, $c->on_update),
143 "Foreign key constraint on $table_name matches an expected ".
144 "constraint" );
145 } elsif ($c->type eq 'UNIQUE') {
146 ok(check_unique($table_name, scalar $c->fields),
147 "Unique constraint on $table_name matches an expected constraint");
637ca936 148 }
661fc8eb 149 }
637ca936 150}
151
7b90bb13 152# Make sure all the foreign keys are done.
637ca936 153my $i;
7b90bb13 154for ($i = 0; $i <= $#fk_constraints; ++$i) {
661fc8eb 155 ok(!$fk_constraints[$i]->{'needed'},
156 "Constraint $fk_constraints[$i]->{display}");
7b90bb13 157}
158# Make sure all the uniques are done.
159for ($i = 0; $i <= $#unique_constraints; ++$i) {
661fc8eb 160 ok(!$unique_constraints[$i]->{'needed'},
161 "Constraint $unique_constraints[$i]->{display}");
637ca936 162}
163
7b90bb13 164sub check_fk {
661fc8eb 165 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
166
167 $ondel = '' if (!defined($ondel));
168 $onupd = '' if (!defined($onupd));
169
170 my $i;
171 for ($i = 0; $i <= $#fk_constraints; ++$i) {
172 if ($selftable eq $fk_constraints[$i]->{'selftable'} &&
173 $foreigntable eq $fk_constraints[$i]->{'foreigntable'} &&
174 $ondel eq $fk_constraints[$i]->{on_delete} &&
175 $onupd eq $fk_constraints[$i]->{on_update}) {
176 # check columns
177
178 my $found = 0;
179 for (my $j = 0; $j <= $#$selfcol; ++$j) {
180 $found = 0;
181 for (my $k = 0; $k <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$k) {
182 if ($selfcol->[$j] eq $fk_constraints[$i]->{'selfcols'}->[$k] &&
183 $foreigncol->[$j] eq $fk_constraints[$i]->{'foreigncols'}->[$k]) {
184 $found = 1;
185 last;
186 }
187 }
188 last unless $found;
189 }
190
191 if ($found) {
192 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
193 $found = 0;
194 for (my $k = 0; $k <= $#$selfcol; ++$k) {
195 if ($selfcol->[$k] eq $fk_constraints[$i]->{'selfcols'}->[$j] &&
196 $foreigncol->[$k] eq $fk_constraints[$i]->{'foreigncols'}->[$j]) {
197 $found = 1;
198 last;
199 }
200 }
201 last unless $found;
202 }
203 }
204
205 if ($found) {
206 --$fk_constraints[$i]->{needed};
207 return 1;
208 }
209 }
210 }
211 return 0;
7b90bb13 212}
213
58d387fe 214my( $ondel, $onupd );
215
7b90bb13 216sub check_unique {
661fc8eb 217 my ($selftable, $selfcol) = @_;
218
219 $ondel = '' if (!defined($ondel));
220 $onupd = '' if (!defined($onupd));
221
222 my $i;
223 for ($i = 0; $i <= $#unique_constraints; ++$i) {
224 if ($selftable eq $unique_constraints[$i]->{'table'}) {
225
226 my $found = 0;
227 for (my $j = 0; $j <= $#$selfcol; ++$j) {
228 $found = 0;
229 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
230 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
231 $found = 1;
232 last;
233 }
234 }
235 last unless $found;
236 }
237
238 if ($found) {
239 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
240 $found = 0;
241 for (my $k = 0; $k <= $#$selfcol; ++$k) {
242 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
243 $found = 1;
244 last;
245 }
246 }
247 last unless $found;
248 }
249 }
250
251 if ($found) {
252 --$unique_constraints[$i]->{needed};
253 return 1;
254 }
255 }
256 }
257 return 0;
637ca936 258}