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 => ''},
77 # shouldn't this be generated?
79 #{'display' => 'twokeytreelike -> twokeytreelike for parent1,parent2',
80 # 'selftable' => 'twokeytreelike', 'foreigntable' => 'twokeytreelike',
81 # 'selfcols' => ['parent1', 'parent2'], 'foreigncols' => ['id1','id2'],
82 # 'needed' => 1, on_delete => '', on_update => ''},
84 {'display' => 'tags -> cd',
85 'selftable' => 'tags', 'foreigntable' => 'cd',
86 'selfcols' => ['cd'], 'foreigncols' => ['cdid'],
87 'needed' => 1, on_delete => 'CASCADE', on_update => 'CASCADE'},
88 {'display' => 'bookmark -> link',
89 'selftable' => 'bookmark', 'foreigntable' => 'link',
90 'selfcols' => ['link'], 'foreigncols' => ['id'],
91 'needed' => 1, on_delete => '', on_update => ''},
94 my @unique_constraints = (
95 {'display' => 'cd artist and title unique',
96 'table' => 'cd', 'cols' => ['artist', 'title'],
98 {'display' => 'twokeytreelike name unique',
99 'table' => 'twokeytreelike', 'cols' => ['name'],
101 # {'display' => 'employee position and group_id unique',
102 # 'table' => 'employee', cols => ['position', 'group_id'],
106 my $tschema = $translator->schema();
107 for my $table ($tschema->get_tables) {
108 my $table_name = $table->name;
109 for my $c ( $table->get_constraints ) {
110 if ($c->type eq 'FOREIGN KEY') {
111 ok(check_fk($table_name, scalar $c->fields,
112 $c->reference_table, scalar $c->reference_fields,
113 $c->on_delete, $c->on_update), "Foreign key constraint on $table_name matches an expected constraint");
115 elsif ($c->type eq 'UNIQUE') {
116 ok(check_unique($table_name, scalar $c->fields),
117 "Unique constraint on $table_name matches an expected constraint");
122 # Make sure all the foreign keys are done.
124 for ($i = 0; $i <= $#fk_constraints; ++$i) {
125 ok(!$fk_constraints[$i]->{'needed'}, "Constraint $fk_constraints[$i]->{display}");
127 # Make sure all the uniques are done.
128 for ($i = 0; $i <= $#unique_constraints; ++$i) {
129 ok(!$unique_constraints[$i]->{'needed'}, "Constraint $unique_constraints[$i]->{display}");
133 my ($selftable, $selfcol, $foreigntable, $foreigncol, $ondel, $onupd) = @_;
135 $ondel = '' if (!defined($ondel));
136 $onupd = '' if (!defined($onupd));
139 for ($i = 0; $i <= $#fk_constraints; ++$i) {
140 if ($selftable eq $fk_constraints[$i]->{'selftable'} &&
141 $foreigntable eq $fk_constraints[$i]->{'foreigntable'} &&
142 ($ondel eq $fk_constraints[$i]->{on_delete}) &&
143 ($onupd eq $fk_constraints[$i]->{on_update})) {
147 for (my $j = 0; $j <= $#$selfcol; ++$j) {
149 for (my $k = 0; $k <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$k) {
150 if ($selfcol->[$j] eq $fk_constraints[$i]->{'selfcols'}->[$k] &&
151 $foreigncol->[$j] eq $fk_constraints[$i]->{'foreigncols'}->[$k]) {
160 for (my $j = 0; $j <= $#{$fk_constraints[$i]->{'selfcols'}}; ++$j) {
162 for (my $k = 0; $k <= $#$selfcol; ++$k) {
163 if ($selfcol->[$k] eq $fk_constraints[$i]->{'selfcols'}->[$j] &&
164 $foreigncol->[$k] eq $fk_constraints[$i]->{'foreigncols'}->[$j]) {
174 --$fk_constraints[$i]->{needed};
182 my( $ondel, $onupd );
185 my ($selftable, $selfcol) = @_;
187 $ondel = '' if (!defined($ondel));
188 $onupd = '' if (!defined($onupd));
191 for ($i = 0; $i <= $#unique_constraints; ++$i) {
192 if ($selftable eq $unique_constraints[$i]->{'table'}) {
195 for (my $j = 0; $j <= $#$selfcol; ++$j) {
197 for (my $k = 0; $k <= $#{$unique_constraints[$i]->{'cols'}}; ++$k) {
198 if ($selfcol->[$j] eq $unique_constraints[$i]->{'cols'}->[$k]) {
207 for (my $j = 0; $j <= $#{$unique_constraints[$i]->{'cols'}}; ++$j) {
209 for (my $k = 0; $k <= $#$selfcol; ++$k) {
210 if ($selfcol->[$k] eq $unique_constraints[$i]->{'cols'}->[$j]) {
220 --$unique_constraints[$i]->{needed};