8 eval "use SQL::Translator";
9 plan skip_all => 'SQL::Translator required' if $@;
11 # do not taunt happy dave ball
13 my $schema = DBICTest::Schema;
17 my $translator = SQL::Translator->new(
19 'DBIx::Schema' => $schema,
25 $translator->parser('SQL::Translator::Parser::DBIx::Class');
26 $translator->producer('SQLite');
28 my $output = $translator->translate();
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'},
40 {'display' => 'cd_to_producer->cd',
41 'selftable' => 'cd_to_producer', 'foreigntable' => 'cd',
42 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
43 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
44 {'display' => 'cd_to_producer->producer',
45 'selftable' => 'cd_to_producer', 'foreigntable' => 'producer',
46 'selfcols' => ['producer'], 'foreigncols' => ['producerid'],
47 'needed' => 1, on_delete => '', on_update => ''},
48 {'display' => 'self_ref_alias -> self_ref for self_ref',
49 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
50 'selfcols' => ['self_ref'], 'foreigncols' => ['id'],
51 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
52 {'display' => 'self_ref_alias -> self_ref for alias',
53 'selftable' => 'self_ref_alias', 'foreigntable' => 'self_ref',
54 'selfcols' => ['alias'], 'foreigncols' => ['id'],
55 'needed' => 1, on_delete => '', on_update => ''},
56 {'display' => 'cd -> artist',
57 'selftable' => 'cd', 'foreigntable' => 'artist',
58 'selfcols' => ['artist'], 'foreigncols' => ['artistid'],
59 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
60 {'display' => 'artist_undirected_map -> artist for id1',
61 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
62 'selfcols' => ['id1'], 'foreigncols' => ['artistid'],
63 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
64 {'display' => 'artist_undirected_map -> artist for id2',
65 'selftable' => 'artist_undirected_map', 'foreigntable' => 'artist',
66 'selfcols' => ['id2'], 'foreigncols' => ['artistid'],
67 'needed' => 1, on_delete => 'CASCADE', on_update => ''},
68 {'display' => 'track->cd',
69 'selftable' => 'track', 'foreigntable' => 'cd',
70 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
71 'needed' => 2, on_delete => 'CASCADE', on_update => 'CASCADE'},
72 {'display' => 'treelike -> treelike for parent',
73 'selftable' => 'treelike', 'foreigntable' => 'treelike',
74 'selfcols' => ['parent'], 'foreigncols' => ['id'],
75 'needed' => 1, on_delete => '', on_update => ''},
76 {'display' => 'twokeytreelike -> twokeytreelike for parent1,parent2',
77 'selftable' => 'twokeytreelike', 'foreigntable' => 'twokeytreelike',
78 'selfcols' => ['parent1', 'parent2'], 'foreigncols' => ['id1','id2'],
79 'needed' => 1, on_delete => '', on_update => ''},
80 {'display' => 'tags -> cd',
81 'selftable' => 'tags', 'foreigntable' => 'cd',
82 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
83 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
84 {'display' => 'bookmark -> link',
85 'selftable' => 'bookmark', 'foreigntable' => 'link',
86 'selfcols' => ['link'], 'foreigncols' => ['id'],
87 'needed' => 1, on_delete => '', on_update => ''},
90 my @unique_constraints = (
91 {'display' => 'cd artist and title unique',
92 'table' => 'cd', 'cols' => ['artist', 'title'],
94 {'display' => 'twokeytreelike name unique',
95 'table' => 'twokeytreelike', 'cols' => ['name'],
97 # {'display' => 'employee position and group_id unique',
98 # 'table' => 'employee', cols => ['position', 'group_id'],
102 my $tschema = $translator->schema();
103 for my $table ($tschema->get_tables) {
104 my $table_name = $table->name;
105 for my $c ( $table->get_constraints ) {
106 if ($c->type eq 'FOREIGN KEY') {
107 ok(check_fk($table_name, scalar $c->fields,
108 $c->reference_table, scalar $c->reference_fields,
109 $c->on_delete, $c->on_update), "Foreign key constraint on $table_name matches an expected constraint");
111 elsif ($c->type eq 'UNIQUE') {
112 ok(check_unique($table_name, scalar $c->fields),
113 "Unique constraint on $table_name matches an expected constraint");
118 # Make sure all the foreign keys are done.
120 for ($i = 0; $i <= $#fk_constraints; ++$i) {
121 ok(!$fk_constraints[$i]->{'needed'}, "Constraint $fk_constraints[$i]->{display}");
123 # Make sure all the uniques are done.
124 for ($i = 0; $i <= $#unique_constraints; ++$i) {
125 ok(!$unique_constraints[$i]->{'needed'}, "Constraint $unique_constraints[$i]->{display}");
129 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
131 $ondel = '' if (!defined($ondel));
132 $onupd = '' if (!defined($onupd));
135 for ($i = 0; $i <= $#fk_constraints; ++$i) {
136 if ($selftable eq $fk_constraints[$i]->{'selftable'} &&
137 $foreigntable eq $fk_constraints[$i]->{'foreigntable'} &&
138 ($ondel eq $fk_constraints[$i]->{on_delete}) &&
139 ($onupd eq $fk_constraints[$i]->{on_update})) {
143 for (my $j = 0; $j <= $#$selfcol; ++$j) {
145 for (my $k = 0; $k <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$k) {
146 if ($selfcol->[$j] eq $fk_constraints[$i]->{'selfcols'}->[$k] &&
147 $foreigncol->[$j] eq $fk_constraints[$i]->{'foreigncols'}->[$k]) {
156 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
158 for (my $k = 0; $k <= $#$selfcol; ++$k) {
159 if ($selfcol->[$k] eq $fk_constraints[$i]->{'selfcols'}->[$j] &&
160 $foreigncol->[$k] eq $fk_constraints[$i]->{'foreigncols'}->[$j]) {
170 --$fk_constraints[$i]->{needed};
179 my ($selftable, $selfcol) = @_;
181 $ondel = '' if (!defined($ondel));
182 $onupd = '' if (!defined($onupd));
185 for ($i = 0; $i <= $#unique_constraints; ++$i) {
186 if ($selftable eq $unique_constraints[$i]->{'table'}) {
189 for (my $j = 0; $j <= $#$selfcol; ++$j) {
191 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
192 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
201 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
203 for (my $k = 0; $k <= $#$selfcol; ++$k) {
204 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
214 --$unique_constraints[$i]->{needed};