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'},
80 {'display' => 'bookmark -> link',
81 'selftable' => 'bookmark', 'foreigntable' => 'link',
82 'selfcols' => ['link'], 'foreigncols' => ['id'],
83 'needed' => 1, on_delete => '', on_update => ''},
86 my @unique_constraints = (
87 {'display' => 'cd artist and title unique',
88 'table' => 'cd', 'cols' => ['artist', 'title'],
90 {'display' => 'twokeytreelike name unique',
91 'table' => 'twokeytreelike', 'cols' => ['name'],
93 # {'display' => 'employee position and group_id unique',
94 # 'table' => 'employee', cols => ['position', 'group_id'],
98 my $tschema = $translator->schema();
99 for my $table ($tschema->get_tables) {
100 my $table_name = $table->name;
101 for my $c ( $table->get_constraints ) {
102 if ($c->type eq 'FOREIGN KEY') {
103 ok(check_fk($table_name, scalar $c->fields,
104 $c->reference_table, scalar $c->reference_fields,
105 $c->on_delete, $c->on_update), "Foreign key constraint on $table_name matches an expected constraint");
107 elsif ($c->type eq 'UNIQUE') {
108 ok(check_unique($table_name, scalar $c->fields),
109 "Unique constraint on $table_name matches an expected constraint");
114 # Make sure all the foreign keys are done.
116 for ($i = 0; $i <= $#fk_constraints; ++$i) {
117 ok(!$fk_constraints[$i]->{'needed'}, "Constraint $fk_constraints[$i]->{display}");
119 # Make sure all the uniques are done.
120 for ($i = 0; $i <= $#unique_constraints; ++$i) {
121 ok(!$unique_constraints[$i]->{'needed'}, "Constraint $unique_constraints[$i]->{display}");
125 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
127 $ondel = '' if (!defined($ondel));
128 $onupd = '' if (!defined($onupd));
131 for ($i = 0; $i <= $#fk_constraints; ++$i) {
132 if ($selftable eq $fk_constraints[$i]->{'selftable'} &&
133 $foreigntable eq $fk_constraints[$i]->{'foreigntable'} &&
134 ($ondel eq $fk_constraints[$i]->{on_delete}) &&
135 ($onupd eq $fk_constraints[$i]->{on_update})) {
139 for (my $j = 0; $j <= $#$selfcol; ++$j) {
141 for (my $k = 0; $k <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$k) {
142 if ($selfcol->[$j] eq $fk_constraints[$i]->{'selfcols'}->[$k] &&
143 $foreigncol->[$j] eq $fk_constraints[$i]->{'foreigncols'}->[$k]) {
152 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
154 for (my $k = 0; $k <= $#$selfcol; ++$k) {
155 if ($selfcol->[$k] eq $fk_constraints[$i]->{'selfcols'}->[$j] &&
156 $foreigncol->[$k] eq $fk_constraints[$i]->{'foreigncols'}->[$j]) {
166 --$fk_constraints[$i]->{needed};
175 my ($selftable, $selfcol) = @_;
177 $ondel = '' if (!defined($ondel));
178 $onupd = '' if (!defined($onupd));
181 for ($i = 0; $i <= $#unique_constraints; ++$i) {
182 if ($selftable eq $unique_constraints[$i]->{'table'}) {
185 for (my $j = 0; $j <= $#$selfcol; ++$j) {
187 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
188 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
197 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
199 for (my $k = 0; $k <= $#$selfcol; ++$k) {
200 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
210 --$unique_constraints[$i]->{needed};