8 eval "use SQL::Translator";
9 plan skip_all => 'SQL::Translator required' if $@;
11 # do not taunt happy dave ball
13 my $schema = DBICTest->init_schema;
17 my $translator = SQL::Translator->new(
19 'DBIx::Schema' => $schema,
24 $translator->parser('SQL::Translator::Parser::DBIx::Class');
25 $translator->producer('SQLite');
27 my $output = $translator->translate();
29 my @fk_constraints = (
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'],
39 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
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 => ''},
53 {'display' => 'cd_to_producer->cd',
54 'selftable' => 'cd_to_producer', 'foreigntable' => 'cd',
55 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
56 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
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 => ''},
63 {'display' => 'self_ref_alias -> self_ref for self_ref',
64 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
65 'selfcols' => ['self_ref'], 'foreigncols' => ['id'],
66 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
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 => ''},
73 {'display' => 'cd -> artist',
74 'selftable' => 'cd', 'foreigntable' => 'artist',
75 'selfcols' => ['artist'], 'foreigncols' => ['artistid'],
76 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
78 # Artist_undirected_map
79 {'display' => 'artist_undirected_map -> artist for id1',
80 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
81 'selfcols' => ['id1'], 'foreigncols' => ['artistid'],
82 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
83 {'display' => 'artist_undirected_map -> artist for id2',
84 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
85 'selfcols' => ['id2'], 'foreigncols' => ['artistid'],
86 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
89 {'display' => 'track->cd',
90 'selftable' => 'track', 'foreigntable' => 'cd',
91 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
92 'needed' => 2, on_delete => 'CASCADE', on_update => 'CASCADE'},
95 {'display' => 'treelike -> treelike for parent',
96 'selftable' => 'treelike', 'foreigntable' => 'treelike',
97 'selfcols' => ['parent'], 'foreigncols' => ['id'],
98 'needed' => 1, on_delete => '', on_update => ''},
100 # shouldn't this be generated?
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 => ''},
108 {'display' => 'tags -> cd',
109 'selftable' => 'tags', 'foreigntable' => 'cd',
110 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
111 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
114 {'display' => 'bookmark -> link',
115 'selftable' => 'bookmark', 'foreigntable' => 'link',
116 'selfcols' => ['link'], 'foreigncols' => ['id'],
117 'needed' => 1, on_delete => '', on_update => ''},
120 my @unique_constraints = (
121 {'display' => 'cd artist and title unique',
122 'table' => 'cd', 'cols' => ['artist', 'title'],
124 {'display' => 'producer name unique',
125 'table' => 'producer', 'cols' => ['name'],
127 {'display' => 'twokeytreelike name unique',
128 'table' => 'twokeytreelike', 'cols' => ['name'],
130 # {'display' => 'employee position and group_id unique',
131 # 'table' => 'employee', cols => ['position', 'group_id'],
135 my $tschema = $translator->schema();
136 for my $table ($tschema->get_tables) {
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 ".
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");
152 # Make sure all the foreign keys are done.
154 for ($i = 0; $i <= $#fk_constraints; ++$i) {
155 ok(!$fk_constraints[$i]->{'needed'},
156 "Constraint $fk_constraints[$i]->{display}");
158 # Make sure all the uniques are done.
159 for ($i = 0; $i <= $#unique_constraints; ++$i) {
160 ok(!$unique_constraints[$i]->{'needed'},
161 "Constraint $unique_constraints[$i]->{display}");
165 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
167 $ondel = '' if (!defined($ondel));
168 $onupd = '' if (!defined($onupd));
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}) {
179 for (my $j = 0; $j <= $#$selfcol; ++$j) {
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]) {
192 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
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]) {
206 --$fk_constraints[$i]->{needed};
214 my( $ondel, $onupd );
217 my ($selftable, $selfcol) = @_;
219 $ondel = '' if (!defined($ondel));
220 $onupd = '' if (!defined($onupd));
223 for ($i = 0; $i <= $#unique_constraints; ++$i) {
224 if ($selftable eq $unique_constraints[$i]->{'table'}) {
227 for (my $j = 0; $j <= $#$selfcol; ++$j) {
229 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
230 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
239 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
241 for (my $k = 0; $k <= $#$selfcol; ++$k) {
242 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
252 --$unique_constraints[$i]->{needed};