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