Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
83eef562 |
2 | use DBIx::Class::Optional::Dependencies -skip_all_without => qw( cdbicompat Class::DBI::Plugin::DeepAbstractSearch>=0 ); |
3 | |
902133a3 |
4 | use strict; |
4a233f30 |
5 | use warnings; |
902133a3 |
6 | |
83eef562 |
7 | use Test::More; |
d9bd5195 |
8 | |
83eef562 |
9 | use DBICTest; |
902133a3 |
10 | |
d9bd5195 |
11 | my $DB = DBICTest->_sqlite_dbname(sqlite_use_file => 1);; |
902133a3 |
12 | |
6c925c72 |
13 | # not usre why this test needs an AutoCommit => 0 and a commit further |
14 | # down - EDONOTCARE |
15 | $ENV{DBIC_UNSAFE_AUTOCOMMIT_OK} = 1; |
16 | |
902133a3 |
17 | my @DSN = ("dbi:SQLite:dbname=$DB", '', '', { AutoCommit => 0 }); |
18 | |
19 | package Music::DBI; |
20 | use base qw(DBIx::Class::CDBICompat); |
21 | use Class::DBI::Plugin::DeepAbstractSearch; |
12e7015a |
22 | |
23 | BEGIN { |
24 | # offset the warning from DBIx::Class::Schema on 5.8 |
25 | # keep the ::Schema default as-is otherwise |
26 | DBIx::Class::_ENV_::OLD_MRO |
27 | and |
28 | ( eval <<'EOS' or die $@ ); |
29 | |
30 | sub setup_schema_instance { |
31 | my $s = shift->next::method(@_); |
32 | $s->schema_sanity_checker(''); |
33 | $s; |
34 | } |
35 | |
36 | 1; |
37 | EOS |
38 | } |
39 | |
902133a3 |
40 | __PACKAGE__->connection(@DSN); |
41 | |
42 | my $sql = <<'SQL_END'; |
43 | |
44 | --------------------------------------- |
45 | -- Artists |
46 | --------------------------------------- |
47 | CREATE TABLE artists ( |
48 | id INTEGER NOT NULL PRIMARY KEY, |
49 | name VARCHAR(32) |
50 | ); |
51 | |
52 | INSERT INTO artists VALUES (1, "Willie Nelson"); |
53 | INSERT INTO artists VALUES (2, "Patsy Cline"); |
54 | |
55 | --------------------------------------- |
56 | -- Labels |
57 | --------------------------------------- |
58 | CREATE TABLE labels ( |
59 | id INTEGER NOT NULL PRIMARY KEY, |
60 | name VARCHAR(32) |
61 | ); |
62 | |
63 | INSERT INTO labels VALUES (1, "Columbia"); |
64 | INSERT INTO labels VALUES (2, "Sony"); |
65 | INSERT INTO labels VALUES (3, "Supraphon"); |
66 | |
67 | --------------------------------------- |
68 | -- CDs |
69 | --------------------------------------- |
70 | CREATE TABLE cds ( |
71 | id INTEGER NOT NULL PRIMARY KEY, |
72 | label INTEGER, |
73 | artist INTEGER, |
74 | title VARCHAR(32), |
75 | year INTEGER |
76 | ); |
77 | INSERT INTO cds VALUES (1, 1, 1, "Songs", 2005); |
78 | INSERT INTO cds VALUES (2, 2, 1, "Read Headed Stanger", 2000); |
79 | INSERT INTO cds VALUES (3, 1, 1, "Wanted! The Outlaws", 2004); |
80 | INSERT INTO cds VALUES (4, 2, 1, "The Very Best of Willie Nelson", 1999); |
81 | |
82 | INSERT INTO cds VALUES (5, 1, 2, "12 Greates Hits", 1999); |
83 | INSERT INTO cds VALUES (6, 2, 2, "Sweet Dreams", 1995); |
84 | INSERT INTO cds VALUES (7, 3, 2, "The Best of Patsy Cline", 1991); |
85 | |
86 | --------------------------------------- |
87 | -- Tracks |
88 | --------------------------------------- |
89 | CREATE TABLE tracks ( |
90 | id INTEGER NOT NULL PRIMARY KEY, |
91 | cd INTEGER, |
92 | position INTEGER, |
93 | title VARCHAR(32) |
94 | ); |
95 | INSERT INTO tracks VALUES (1, 1, 1, "Songs: Track 1"); |
96 | INSERT INTO tracks VALUES (2, 1, 2, "Songs: Track 2"); |
97 | INSERT INTO tracks VALUES (3, 1, 3, "Songs: Track 3"); |
98 | INSERT INTO tracks VALUES (4, 1, 4, "Songs: Track 4"); |
99 | |
100 | INSERT INTO tracks VALUES (5, 2, 1, "Read Headed Stanger: Track 1"); |
101 | INSERT INTO tracks VALUES (6, 2, 2, "Read Headed Stanger: Track 2"); |
102 | INSERT INTO tracks VALUES (7, 2, 3, "Read Headed Stanger: Track 3"); |
103 | INSERT INTO tracks VALUES (8, 2, 4, "Read Headed Stanger: Track 4"); |
104 | |
105 | INSERT INTO tracks VALUES (9, 3, 1, "Wanted! The Outlaws: Track 1"); |
106 | INSERT INTO tracks VALUES (10, 3, 2, "Wanted! The Outlaws: Track 2"); |
107 | |
108 | INSERT INTO tracks VALUES (11, 4, 1, "The Very Best of Willie Nelson: Track 1"); |
109 | INSERT INTO tracks VALUES (12, 4, 2, "The Very Best of Willie Nelson: Track 2"); |
110 | INSERT INTO tracks VALUES (13, 4, 3, "The Very Best of Willie Nelson: Track 3"); |
111 | INSERT INTO tracks VALUES (14, 4, 4, "The Very Best of Willie Nelson: Track 4"); |
112 | INSERT INTO tracks VALUES (15, 4, 5, "The Very Best of Willie Nelson: Track 5"); |
113 | INSERT INTO tracks VALUES (16, 4, 6, "The Very Best of Willie Nelson: Track 6"); |
114 | |
115 | INSERT INTO tracks VALUES (17, 5, 1, "12 Greates Hits: Track 1"); |
116 | INSERT INTO tracks VALUES (18, 5, 2, "12 Greates Hits: Track 2"); |
117 | INSERT INTO tracks VALUES (19, 5, 3, "12 Greates Hits: Track 3"); |
118 | INSERT INTO tracks VALUES (20, 5, 4, "12 Greates Hits: Track 4"); |
119 | |
120 | INSERT INTO tracks VALUES (21, 6, 1, "Sweet Dreams: Track 1"); |
121 | INSERT INTO tracks VALUES (22, 6, 2, "Sweet Dreams: Track 2"); |
122 | INSERT INTO tracks VALUES (23, 6, 3, "Sweet Dreams: Track 3"); |
123 | INSERT INTO tracks VALUES (24, 6, 4, "Sweet Dreams: Track 4"); |
124 | |
125 | INSERT INTO tracks VALUES (25, 7, 1, "The Best of Patsy Cline: Track 1"); |
126 | INSERT INTO tracks VALUES (26, 7, 2, "The Best of Patsy Cline: Track 2"); |
127 | |
128 | SQL_END |
129 | |
130 | foreach my $statement (split /;/, $sql) { |
131 | $statement =~ s/^\s*//gs; |
132 | $statement =~ s/\s*$//gs; |
133 | next unless $statement; |
134 | Music::DBI->db_Main->do($statement) or die "$@ $!"; |
135 | } |
136 | |
137 | Music::DBI->dbi_commit; |
138 | |
139 | package Music::Artist; |
140 | use base 'Music::DBI'; |
141 | Music::Artist->table('artists'); |
142 | Music::Artist->columns(All => qw/id name/); |
143 | |
144 | |
145 | package Music::Label; |
146 | use base 'Music::DBI'; |
147 | Music::Label->table('labels'); |
148 | Music::Label->columns(All => qw/id name/); |
149 | |
150 | package Music::CD; |
151 | use base 'Music::DBI'; |
152 | Music::CD->table('cds'); |
153 | Music::CD->columns(All => qw/id label artist title year/); |
154 | |
155 | |
156 | package Music::Track; |
157 | use base 'Music::DBI'; |
158 | Music::Track->table('tracks'); |
159 | Music::Track->columns(All => qw/id cd position title/); |
160 | |
161 | Music::Artist->has_many(cds => 'Music::CD'); |
162 | Music::Label->has_many(cds => 'Music::CD'); |
163 | Music::CD->has_many(tracks => 'Music::Track'); |
164 | Music::CD->has_a(artist => 'Music::Artist'); |
165 | Music::CD->has_a(label => 'Music::Label'); |
166 | Music::Track->has_a(cd => 'Music::CD'); |
167 | |
168 | package main; |
169 | |
170 | { |
171 | my $where = { }; |
172 | my $attr; |
173 | my @artists = Music::Artist->deep_search_where($where, $attr); |
174 | is_deeply [ sort @artists ], [ 1, 2 ], "all without order"; |
175 | } |
176 | |
177 | { |
178 | my $where = { }; |
179 | my $attr = { order_by => 'name' }; |
180 | my @artists = Music::Artist->deep_search_where($where, $attr); |
181 | is_deeply \@artists, [ 2, 1 ], "all with ORDER BY name"; |
182 | } |
183 | |
184 | { |
185 | my $where = { }; |
186 | my $attr = { order_by => 'name DESC' }; |
187 | my @artists = Music::Artist->deep_search_where($where, $attr); |
188 | is_deeply \@artists, [ 1, 2 ], "all with ORDER BY name DESC"; |
189 | } |
190 | |
191 | { |
192 | my $where = { name => { -like => 'Patsy Cline' }, }; |
193 | my $attr; |
194 | my @artists = Music::Artist->deep_search_where($where, $attr); |
195 | is_deeply \@artists, [ 2 ], "simple search"; |
196 | } |
197 | |
198 | { |
199 | my $where = { 'artist.name' => 'Patsy Cline' }; |
200 | my $attr = { } ; |
201 | my @cds = Music::CD->deep_search_where($where, $attr); |
202 | is_deeply [ sort @cds ], [ 5, 6, 7 ], "Patsy's CDs"; |
203 | } |
204 | |
205 | { |
206 | my $where = { 'artist.name' => 'Patsy Cline' }; |
207 | my $attr = { order_by => "title" } ; |
208 | my @cds = Music::CD->deep_search_where($where, $attr); |
209 | is_deeply [ @cds ], [ 5, 6, 7 ], "Patsy's CDs by title"; |
210 | |
211 | my $count = Music::CD->count_deep_search_where($where); |
212 | is_deeply $count, 3, "count Patsy's CDs by title"; |
213 | } |
214 | |
215 | { |
216 | my $where = { 'cd.title' => { -like => 'S%' }, }; |
217 | my $attr = { order_by => "cd.title, title" } ; |
218 | my @cds = Music::Track->deep_search_where($where, $attr); |
219 | is_deeply [ @cds ], [1, 2, 3, 4, 21, 22, 23, 24 ], "Tracks from CDs whose name starts with 'S'"; |
220 | } |
221 | |
222 | { |
223 | my $where = { |
224 | 'cd.artist.name' => { -like => 'W%' }, |
225 | 'cd.year' => { '>' => 2000 }, |
226 | 'position' => { '<' => 3 } |
227 | }; |
228 | my $attr = { order_by => "cd.title DESC, title" } ; |
229 | my @cds = Music::Track->deep_search_where($where, $attr); |
230 | is_deeply [ @cds ], [ 9, 10, 1, 2 ], "First 2 tracks from W's albums after 2000 "; |
231 | |
232 | my $count = Music::Track->count_deep_search_where($where); |
233 | is_deeply $count, 4, "Count First 2 tracks from W's albums after 2000"; |
234 | } |
235 | |
236 | { |
237 | my $where = { |
238 | 'cd.artist.name' => { -like => 'W%' }, |
239 | 'cd.year' => { '>' => 2000 }, |
240 | 'position' => { '<' => 3 } |
241 | }; |
242 | my $attr = { order_by => [ 'cd.title DESC' , 'title' ] } ; |
243 | my @cds = Music::Track->deep_search_where($where, $attr); |
244 | is_deeply [ @cds ], [ 9, 10, 1, 2 ], "First 2 tracks from W's albums after 2000, array ref order "; |
245 | |
246 | my $count = Music::Track->count_deep_search_where($where); |
247 | is_deeply $count, 4, "Count First 2 tracks from W's albums after 2000, array ref order"; |
248 | } |
249 | |
250 | { |
251 | my $where = { 'cd.title' => [ -and => { -like => '%o%' }, { -like => '%W%' } ] }; |
252 | my $attr = { order_by => [ 'cd.id' ] } ; |
253 | |
254 | my @tracks = Music::Track->deep_search_where($where, $attr); |
255 | is_deeply [ @tracks ], [ 3, 3, 4, 4, 4, 4, 4, 4 ], "Tracks from CD titles containing 'o' AND 'W'"; |
256 | } |
257 | |
258 | { |
259 | my $where = { 'cd.year' => [ 1995, 1999 ] }; |
260 | my $attr = { order_by => [ 'cd.id' ] } ; |
261 | |
262 | my @tracks = Music::Track->deep_search_where($where, $attr); |
263 | is_deeply [ @tracks ], [ 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6 ], |
264 | "Tracks from CDs from 1995, 1999"; |
265 | } |
266 | |
267 | { |
268 | my $where = { 'cd.year' => { -in => [ 1995, 1999 ] } }; |
269 | my $attr = { order_by => [ 'cd.id' ] } ; |
270 | |
271 | my @tracks = Music::Track->deep_search_where($where, $attr); |
272 | is_deeply [ @tracks ], [ 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6 ], |
273 | "Tracks from CDs in 1995, 1999"; |
274 | } |
275 | |
276 | { |
277 | my $where = { -and => [ 'cd.year' => [ 1995, 1999 ], position => { '<=', 2 } ] }; |
278 | my $attr = { order_by => [ 'cd.id' ] } ; |
279 | |
280 | my @tracks = Music::Track->deep_search_where($where, $attr); |
281 | is_deeply [ @tracks ], [ 4, 4, 5, 5, 6, 6 ], |
282 | "First 2 tracks Tracks from CDs from 1995, 1999"; |
283 | } |
284 | |
285 | { |
286 | my $where = { -and => [ 'cd.year' => { -in => [ 1995, 1999 ] }, position => { '<=', 2 } ] }; |
287 | my $attr = { order_by => [ 'cd.id' ] } ; |
288 | |
289 | my @tracks = Music::Track->deep_search_where($where, $attr); |
290 | is_deeply [ @tracks ], [ 4, 4, 5, 5, 6, 6 ], |
291 | "First 2 tracks Tracks from CDs in 1995, 1999"; |
292 | } |
293 | |
294 | { |
295 | my $where = { 'label.name' => { -in => [ 'Sony', 'Supraphon', 'Bogus' ] } }; |
296 | my $attr = { order_by => [ 'id' ] } ; |
297 | |
298 | my @cds = Music::CD->deep_search_where($where, $attr); |
299 | is_deeply [ @cds ], [ 2, 4, 6, 7 ], |
300 | "CDs from Sony or Supraphon"; |
301 | } |
302 | |
303 | { |
304 | my $where = { 'label.name' => [ 'Sony', 'Supraphon', 'Bogus' ] }; |
305 | my $attr = { order_by => [ 'id' ] } ; |
306 | |
307 | my @cds = Music::CD->deep_search_where($where, $attr); |
308 | is_deeply [ @cds ], [ 2, 4, 6, 7 ], |
309 | "CDs from Sony or Supraphon"; |
310 | } |
311 | |
d9bd5195 |
312 | done_testing; |