4 use DBICTest::HelperRels;
6 eval "use SQL::Translator";
7 plan skip_all => 'SQL::Translator required' if $@;
9 my $schema = DBICTest::Schema;
13 my $translator = SQL::Translator->new(
15 'DBIx::Schema' => $schema,
21 $translator->parser('SQL::Translator::Parser::DBIx::Class');
22 $translator->producer('SQLite');
24 my $output = $translator->translate();
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'],
35 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
36 {'display' => 'cd_to_producer->cd',
37 'selftable' => 'cd_to_producer', 'foreigntable' => 'cd',
38 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
39 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
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'],
47 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
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'],
55 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
56 {'display' => 'artist_undirected_map -> artist for id1',
57 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
58 'selfcols' => ['id1'], 'foreigncols' => ['artistid'],
59 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
60 {'display' => 'artist_undirected_map -> artist for id2',
61 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
62 'selfcols' => ['id2'], 'foreigncols' => ['artistid'],
63 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
64 {'display' => 'track->cd',
65 'selftable' => 'track', 'foreigntable' => 'cd',
66 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
67 'needed' => 2, on_delete => 'CASCADE', on_update => 'CASCADE'},
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'],
79 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
82 my @unique_constraints = (
83 {'display' => 'cd artist and title unique',
84 'table' => 'cd', 'cols' => ['artist', 'title'],
86 {'display' => 'twokeytreelike name unique',
87 'table' => 'twokeytreelike', 'cols' => ['name'],
91 my $tschema = $translator->schema();
92 for my $table ($tschema->get_tables) {
93 my $table_name = $table->name;
94 for my $c ( $table->get_constraints ) {
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");
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");
107 # Make sure all the foreign keys are done.
109 for ($i = 0; $i <= $#fk_constraints; ++$i) {
110 ok(!$fk_constraints[$i]->{'needed'}, "Constraint $fk_constraints[$i]->{display}");
112 # Make sure all the uniques are done.
113 for ($i = 0; $i <= $#unique_constraints; ++$i) {
114 ok(!$unique_constraints[$i]->{'needed'}, "Constraint $unique_constraints[$i]->{display}");
118 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
120 $ondel = '' if (!defined($ondel));
121 $onupd = '' if (!defined($onupd));
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})) {
132 for (my $j = 0; $j <= $#$selfcol; ++$j) {
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]) {
145 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
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]) {
159 --$fk_constraints[$i]->{needed};
168 my ($selftable, $selfcol) = @_;
170 $ondel = '' if (!defined($ondel));
171 $onupd = '' if (!defined($onupd));
174 for ($i = 0; $i <= $#unique_constraints; ++$i) {
175 if ($selftable eq $unique_constraints[$i]->{'table'}) {
178 for (my $j = 0; $j <= $#$selfcol; ++$j) {
180 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
181 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
190 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
192 for (my $k = 0; $k <= $#$selfcol; ++$k) {
193 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
203 --$unique_constraints[$i]->{needed};