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
7b90bb13 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},
89);
90
637ca936 91my $tschema = $translator->schema();
92for my $table ($tschema->get_tables) {
93 my $table_name = $table->name;
94 for my $c ( $table->get_constraints ) {
7b90bb13 95 if ($c->type eq 'FOREIGN KEY') {
96 ok(check_fk($table_name, scalar $c->fields,
97 $c->reference_table, scalar $c->reference_fields,
98 $c->on_delete, $c->on_update), "Foreign key constraint on $table_name matches an expected constraint");
99 }
100 elsif ($c->type eq 'UNIQUE') {
101 ok(check_unique($table_name, scalar $c->fields),
102 "Unique constraint on $table_name matches an expected constraint");
103 }
637ca936 104 }
105}
106
7b90bb13 107# Make sure all the foreign keys are done.
637ca936 108my $i;
7b90bb13 109for ($i = 0; $i <= $#fk_constraints; ++$i) {
110 ok(!$fk_constraints[$i]->{'needed'}, "Constraint $fk_constraints[$i]->{display}");
111}
112# Make sure all the uniques are done.
113for ($i = 0; $i <= $#unique_constraints; ++$i) {
114 ok(!$unique_constraints[$i]->{'needed'}, "Constraint $unique_constraints[$i]->{display}");
637ca936 115}
116
7b90bb13 117sub check_fk {
637ca936 118 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
119
120 $ondel = '' if (!defined($ondel));
121 $onupd = '' if (!defined($onupd));
122
123 my $i;
7b90bb13 124 for ($i = 0; $i <= $#fk_constraints; ++$i) {
125 if ($selftable eq $fk_constraints[$i]->{'selftable'} &&
126 $foreigntable eq $fk_constraints[$i]->{'foreigntable'} &&
127 ($ondel eq $fk_constraints[$i]->{on_delete}) &&
128 ($onupd eq $fk_constraints[$i]->{on_update})) {
637ca936 129 # check columns
130
131 my $found = 0;
132 for (my $j = 0; $j <= $#$selfcol; ++$j) {
133 $found = 0;
7b90bb13 134 for (my $k = 0; $k <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$k) {
135 if ($selfcol->[$j] eq $fk_constraints[$i]->{'selfcols'}->[$k] &&
136 $foreigncol->[$j] eq $fk_constraints[$i]->{'foreigncols'}->[$k]) {
137 $found = 1;
138 last;
139 }
140 }
141 last unless $found;
142 }
143
144 if ($found) {
145 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
146 $found = 0;
147 for (my $k = 0; $k <= $#$selfcol; ++$k) {
148 if ($selfcol->[$k] eq $fk_constraints[$i]->{'selfcols'}->[$j] &&
149 $foreigncol->[$k] eq $fk_constraints[$i]->{'foreigncols'}->[$j]) {
150 $found = 1;
151 last;
152 }
153 }
154 last unless $found;
155 }
156 }
157
158 if ($found) {
159 --$fk_constraints[$i]->{needed};
160 return 1;
161 }
162 }
163 }
164 return 0;
165}
166
167sub check_unique {
168 my ($selftable, $selfcol) = @_;
169
170 $ondel = '' if (!defined($ondel));
171 $onupd = '' if (!defined($onupd));
172
173 my $i;
174 for ($i = 0; $i <= $#unique_constraints; ++$i) {
175 if ($selftable eq $unique_constraints[$i]->{'table'}) {
176
177 my $found = 0;
178 for (my $j = 0; $j <= $#$selfcol; ++$j) {
179 $found = 0;
180 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
181 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
637ca936 182 $found = 1;
183 last;
184 }
185 }
186 last unless $found;
187 }
188
189 if ($found) {
7b90bb13 190 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
637ca936 191 $found = 0;
192 for (my $k = 0; $k <= $#$selfcol; ++$k) {
7b90bb13 193 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
637ca936 194 $found = 1;
195 last;
196 }
197 }
198 last unless $found;
199 }
200 }
201
202 if ($found) {
7b90bb13 203 --$unique_constraints[$i]->{needed};
637ca936 204 return 1;
205 }
206 }
207 }
208 return 0;
209}