Merge 'trunk' into 'DBIx-Class-current'
[dbsrgits/DBIx-Class-Historic.git] / t / helperrels / 26sqlt.t
CommitLineData
637ca936 1use Test::More;
2use lib qw(t/lib);
3use DBICTest;
4use DBICTest::HelperRels;
5
6eval "use SQL::Translator";
7plan skip_all => 'SQL::Translator required' if $@;
8
9my $schema = DBICTest::Schema;
10
78145e31 11plan tests => 31;
637ca936 12
13my $translator = SQL::Translator->new(
14 parser_args => {
15 'DBIx::Schema' => $schema,
16 },
17 producer_args => {
18 },
19);
20
21$translator->parser('SQL::Translator::Parser::DBIx::Class');
22$translator->producer('SQLite');
23
24my $output = $translator->translate();
25
7b90bb13 26my @fk_constraints =
637ca936 27 (
28 {'display' => 'twokeys->cd',
29 'selftable' => 'twokeys', 'foreigntable' => 'cd',
30 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
31 'needed' => 1, on_delete => '', on_update => ''},
32 {'display' => 'twokeys->artist',
33 'selftable' => 'twokeys', 'foreigntable' => 'artist',
34 'selfcols' => ['artist'], 'foreigncols' => ['artistid'],
de60a93d 35 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 36 {'display' => 'cd_to_producer->cd',
37 'selftable' => 'cd_to_producer', 'foreigntable' => 'cd',
38 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
de60a93d 39 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 40 {'display' => 'cd_to_producer->producer',
41 'selftable' => 'cd_to_producer', 'foreigntable' => 'producer',
42 'selfcols' => ['producer'], 'foreigncols' => ['producerid'],
43 'needed' => 1, on_delete => '', on_update => ''},
44 {'display' => 'self_ref_alias -> self_ref for self_ref',
45 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
46 'selfcols' => ['self_ref'], 'foreigncols' => ['id'],
de60a93d 47 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 48 {'display' => 'self_ref_alias -> self_ref for alias',
49 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
50 'selfcols' => ['alias'], 'foreigncols' => ['id'],
51 'needed' => 1, on_delete => '', on_update => ''},
52 {'display' => 'cd -> artist',
53 'selftable' => 'cd', 'foreigntable' => 'artist',
54 'selfcols' => ['artist'], 'foreigncols' => ['artistid'],
de60a93d 55 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 56 {'display' => 'artist_undirected_map -> artist for id1',
57 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
58 'selfcols' => ['id1'], 'foreigncols' => ['artistid'],
de60a93d 59 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
637ca936 60 {'display' => 'artist_undirected_map -> artist for id2',
61 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
62 'selfcols' => ['id2'], 'foreigncols' => ['artistid'],
de60a93d 63 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
637ca936 64 {'display' => 'track->cd',
65 'selftable' => 'track', 'foreigntable' => 'cd',
66 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
de60a93d 67 'needed' => 2, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 68 {'display' => 'treelike -> treelike for parent',
69 'selftable' => 'treelike', 'foreigntable' => 'treelike',
70 'selfcols' => ['parent'], 'foreigncols' => ['id'],
71 'needed' => 1, on_delete => '', on_update => ''},
72 {'display' => 'twokeytreelike -> twokeytreelike for parent1,parent2',
73 'selftable' => 'twokeytreelike', 'foreigntable' => 'twokeytreelike',
74 'selfcols' => ['parent1', 'parent2'], 'foreigncols' => ['id1','id2'],
75 'needed' => 1, on_delete => '', on_update => ''},
76 {'display' => 'tags -> cd',
77 'selftable' => 'tags', 'foreigntable' => 'cd',
78 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
de60a93d 79 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
637ca936 80 );
81
7b90bb13 82my @unique_constraints = (
83 {'display' => 'cd artist and title unique',
84 'table' => 'cd', 'cols' => ['artist', 'title'],
85 'needed' => 1},
86 {'display' => 'twokeytreelike name unique',
87 'table' => 'twokeytreelike', 'cols' => ['name'],
88 'needed' => 1},
78145e31 89# {'display' => 'employee position and group_id unique',
90# 'table' => 'employee', cols => ['position', 'group_id'],
91# 'needed' => 1},
7b90bb13 92);
93
637ca936 94my $tschema = $translator->schema();
95for my $table ($tschema->get_tables) {
96 my $table_name = $table->name;
97 for my $c ( $table->get_constraints ) {
7b90bb13 98 if ($c->type eq 'FOREIGN KEY') {
99 ok(check_fk($table_name, scalar $c->fields,
100 $c->reference_table, scalar $c->reference_fields,
101 $c->on_delete, $c->on_update), "Foreign key constraint on $table_name matches an expected constraint");
102 }
103 elsif ($c->type eq 'UNIQUE') {
104 ok(check_unique($table_name, scalar $c->fields),
105 "Unique constraint on $table_name matches an expected constraint");
106 }
637ca936 107 }
108}
109
7b90bb13 110# Make sure all the foreign keys are done.
637ca936 111my $i;
7b90bb13 112for ($i = 0; $i <= $#fk_constraints; ++$i) {
113 ok(!$fk_constraints[$i]->{'needed'}, "Constraint $fk_constraints[$i]->{display}");
114}
115# Make sure all the uniques are done.
116for ($i = 0; $i <= $#unique_constraints; ++$i) {
117 ok(!$unique_constraints[$i]->{'needed'}, "Constraint $unique_constraints[$i]->{display}");
637ca936 118}
119
7b90bb13 120sub check_fk {
637ca936 121 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
122
123 $ondel = '' if (!defined($ondel));
124 $onupd = '' if (!defined($onupd));
125
126 my $i;
7b90bb13 127 for ($i = 0; $i <= $#fk_constraints; ++$i) {
128 if ($selftable eq $fk_constraints[$i]->{'selftable'} &&
129 $foreigntable eq $fk_constraints[$i]->{'foreigntable'} &&
130 ($ondel eq $fk_constraints[$i]->{on_delete}) &&
131 ($onupd eq $fk_constraints[$i]->{on_update})) {
637ca936 132 # check columns
133
134 my $found = 0;
135 for (my $j = 0; $j <= $#$selfcol; ++$j) {
136 $found = 0;
7b90bb13 137 for (my $k = 0; $k <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$k) {
138 if ($selfcol->[$j] eq $fk_constraints[$i]->{'selfcols'}->[$k] &&
139 $foreigncol->[$j] eq $fk_constraints[$i]->{'foreigncols'}->[$k]) {
140 $found = 1;
141 last;
142 }
143 }
144 last unless $found;
145 }
146
147 if ($found) {
148 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
149 $found = 0;
150 for (my $k = 0; $k <= $#$selfcol; ++$k) {
151 if ($selfcol->[$k] eq $fk_constraints[$i]->{'selfcols'}->[$j] &&
152 $foreigncol->[$k] eq $fk_constraints[$i]->{'foreigncols'}->[$j]) {
153 $found = 1;
154 last;
155 }
156 }
157 last unless $found;
158 }
159 }
160
161 if ($found) {
162 --$fk_constraints[$i]->{needed};
163 return 1;
164 }
165 }
166 }
167 return 0;
168}
169
170sub check_unique {
171 my ($selftable, $selfcol) = @_;
172
173 $ondel = '' if (!defined($ondel));
174 $onupd = '' if (!defined($onupd));
175
176 my $i;
177 for ($i = 0; $i <= $#unique_constraints; ++$i) {
178 if ($selftable eq $unique_constraints[$i]->{'table'}) {
179
180 my $found = 0;
181 for (my $j = 0; $j <= $#$selfcol; ++$j) {
182 $found = 0;
183 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
184 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
637ca936 185 $found = 1;
186 last;
187 }
188 }
189 last unless $found;
190 }
191
192 if ($found) {
7b90bb13 193 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
637ca936 194 $found = 0;
195 for (my $k = 0; $k <= $#$selfcol; ++$k) {
7b90bb13 196 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
637ca936 197 $found = 1;
198 last;
199 }
200 }
201 last unless $found;
202 }
203 }
204
205 if ($found) {
7b90bb13 206 --$unique_constraints[$i]->{needed};
637ca936 207 return 1;
208 }
209 }
210 }
211 return 0;
212}