Commit | Line | Data |
70350518 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
cb3e35d2 |
7 | use Data::Dumper; |
70350518 |
8 | |
a47e1233 |
9 | my $schema = DBICTest->init_schema(); |
0567538f |
10 | |
4d91ad3f |
11 | my $orig_debug = $schema->storage->debug; |
12 | |
0567538f |
13 | use IO::File; |
14 | |
15 | BEGIN { |
16 | eval "use DBD::SQLite"; |
17 | plan $@ |
18 | ? ( skip_all => 'needs DBD::SQLite for testing' ) |
96e7f9ec |
19 | : ( tests => 16 ); |
0567538f |
20 | } |
21 | |
58ff4acf |
22 | # figure out if we've got a version of sqlite that is older than 3.2.6, in |
23 | # which case COUNT(DISTINCT()) doesn't work |
24 | my $is_broken_sqlite = 0; |
25 | my ($sqlite_major_ver,$sqlite_minor_ver,$sqlite_patch_ver) = |
26 | split /\./, $schema->storage->dbh->get_info(18); |
27 | if( $schema->storage->dbh->get_info(17) eq 'SQLite' && |
28 | ( ($sqlite_major_ver < 3) || |
29 | ($sqlite_major_ver == 3 && $sqlite_minor_ver < 2) || |
30 | ($sqlite_major_ver == 3 && $sqlite_minor_ver == 2 && $sqlite_patch_ver < 6) ) ) { |
31 | $is_broken_sqlite = 1; |
32 | } |
485f6e10 |
33 | |
0567538f |
34 | # test the abstract join => SQL generator |
35 | my $sa = new DBIC::SQL::Abstract; |
36 | |
37 | my @j = ( |
38 | { child => 'person' }, |
39 | [ { father => 'person' }, { 'father.person_id' => 'child.father_id' }, ], |
40 | [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], |
41 | ); |
42 | my $match = 'person child JOIN person father ON ( father.person_id = ' |
43 | . 'child.father_id ) JOIN person mother ON ( mother.person_id ' |
44 | . '= child.mother_id )' |
45 | ; |
46 | is( $sa->_recurse_from(@j), $match, 'join 1 ok' ); |
47 | |
48 | my @j2 = ( |
49 | { mother => 'person' }, |
50 | [ [ { child => 'person' }, |
51 | [ { father => 'person' }, |
52 | { 'father.person_id' => 'child.father_id' } |
53 | ] |
54 | ], |
55 | { 'mother.person_id' => 'child.mother_id' } |
56 | ], |
57 | ); |
58 | $match = 'person mother JOIN (person child JOIN person father ON (' |
59 | . ' father.person_id = child.father_id )) ON ( mother.person_id = ' |
60 | . 'child.mother_id )' |
61 | ; |
62 | is( $sa->_recurse_from(@j2), $match, 'join 2 ok' ); |
63 | |
64 | my @j3 = ( |
65 | { child => 'person' }, |
66 | [ { father => 'person', -join_type => 'inner' }, { 'father.person_id' => 'child.father_id' }, ], |
67 | [ { mother => 'person', -join_type => 'inner' }, { 'mother.person_id' => 'child.mother_id' } ], |
68 | ); |
69 | $match = 'person child INNER JOIN person father ON ( father.person_id = ' |
70 | . 'child.father_id ) INNER JOIN person mother ON ( mother.person_id ' |
71 | . '= child.mother_id )' |
72 | ; |
73 | |
74 | is( $sa->_recurse_from(@j3), $match, 'join 3 (inner join) ok'); |
75 | |
ca7b9fdf |
76 | my @j4 = ( |
77 | { mother => 'person' }, |
78 | [ [ { child => 'person', -join_type => 'left' }, |
79 | [ { father => 'person', -join_type => 'right' }, |
80 | { 'father.person_id' => 'child.father_id' } |
81 | ] |
82 | ], |
83 | { 'mother.person_id' => 'child.mother_id' } |
84 | ], |
85 | ); |
86 | $match = 'person mother LEFT JOIN (person child RIGHT JOIN person father ON (' |
87 | . ' father.person_id = child.father_id )) ON ( mother.person_id = ' |
88 | . 'child.mother_id )' |
89 | ; |
90 | is( $sa->_recurse_from(@j4), $match, 'join 4 (nested joins + join types) ok'); |
91 | |
635b9634 |
92 | my @j5 = ( |
93 | { child => 'person' }, |
94 | [ { father => 'person' }, { 'father.person_id' => \'!= child.father_id' }, ], |
95 | [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], |
96 | ); |
97 | $match = 'person child JOIN person father ON ( father.person_id != ' |
98 | . 'child.father_id ) JOIN person mother ON ( mother.person_id ' |
99 | . '= child.mother_id )' |
100 | ; |
101 | is( $sa->_recurse_from(@j5), $match, 'join 5 (SCALAR reference for ON statement) ok' ); |
102 | |
103 | my @j6 = ( |
104 | { child => 'person' }, |
105 | [ { father => 'person' }, { 'father.person_id' => { '!=', '42' } }, ], |
106 | [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], |
107 | ); |
11aada86 |
108 | $match = qr/^HASH reference arguments are not supported in JOINS - try using "\.\.\." instead/; |
635b9634 |
109 | eval { $sa->_recurse_from(@j6) }; |
110 | like( $@, $match, 'join 6 (HASH reference for ON statement dies) ok' ); |
111 | |
f9db5527 |
112 | my $rs = $schema->resultset("CD")->search( |
0567538f |
113 | { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' }, |
114 | { from => [ { 'me' => 'cd' }, |
115 | [ |
116 | { artist => 'artist' }, |
117 | { 'me.artist' => 'artist.artistid' } |
118 | ] ] } |
119 | ); |
120 | |
ebaefbc2 |
121 | cmp_ok( $rs + 0, '==', 1, "Single record in resultset"); |
0567538f |
122 | |
123 | is($rs->first->title, 'Forkful of bees', 'Correct record returned'); |
124 | |
f9db5527 |
125 | $rs = $schema->resultset("CD")->search( |
0567538f |
126 | { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' }, |
127 | { join => 'artist' }); |
128 | |
ebaefbc2 |
129 | cmp_ok( $rs + 0, '==', 1, "Single record in resultset"); |
0567538f |
130 | |
131 | is($rs->first->title, 'Forkful of bees', 'Correct record returned'); |
132 | |
f9db5527 |
133 | $rs = $schema->resultset("CD")->search( |
0567538f |
134 | { 'artist.name' => 'We Are Goth', |
135 | 'liner_notes.notes' => 'Kill Yourself!' }, |
136 | { join => [ qw/artist liner_notes/ ] }); |
137 | |
ebaefbc2 |
138 | cmp_ok( $rs + 0, '==', 1, "Single record in resultset"); |
0567538f |
139 | |
140 | is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned'); |
141 | |
8fe164b9 |
142 | # when using join attribute, make sure slice()ing all objects has same count as all() |
f9db5527 |
143 | $rs = $schema->resultset("CD")->search( |
8fe164b9 |
144 | { 'artist' => 1 }, |
145 | { join => [qw/artist/], order_by => 'artist.name' } |
146 | ); |
147 | cmp_ok( scalar $rs->all, '==', scalar $rs->slice(0, $rs->count - 1), 'slice() with join has same count as all()' ); |
148 | |
2bd9c7c0 |
149 | ok(!$rs->slice($rs->count+1000, $rs->count+1002)->count, |
150 | 'Slicing beyond end of rs returns a zero count'); |
151 | |
f9db5527 |
152 | $rs = $schema->resultset("Artist")->search( |
0567538f |
153 | { 'liner_notes.notes' => 'Kill Yourself!' }, |
154 | { join => { 'cds' => 'liner_notes' } }); |
155 | |
156 | cmp_ok( $rs->count, '==', 1, "Single record in resultset"); |
157 | |
158 | is($rs->first->name, 'We Are Goth', 'Correct record returned'); |
159 | |