1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
2 use DBIx::Class::Optional::Dependencies -skip_all_without => qw( cdbicompat Class::DBI::Plugin::DeepAbstractSearch>=0 );
11 my $DB = DBICTest->_sqlite_dbname(sqlite_use_file => 1);;
13 # not usre why this test needs an AutoCommit => 0 and a commit further
15 $ENV{DBIC_UNSAFE_AUTOCOMMIT_OK} = 1;
17 my @DSN = ("dbi:SQLite:dbname=$DB", '', '', { AutoCommit => 0 });
20 use base qw(DBIx::Class::CDBICompat);
21 use Class::DBI::Plugin::DeepAbstractSearch;
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
28 ( eval <<'EOS' or die $@ );
30 sub setup_schema_instance {
31 my $s = shift->next::method(@_);
32 $s->schema_sanity_checker('');
40 __PACKAGE__->connection(@DSN);
42 my $sql = <<'SQL_END';
44 ---------------------------------------
46 ---------------------------------------
47 CREATE TABLE artists (
48 id INTEGER NOT NULL PRIMARY KEY,
52 INSERT INTO artists VALUES (1, "Willie Nelson");
53 INSERT INTO artists VALUES (2, "Patsy Cline");
55 ---------------------------------------
57 ---------------------------------------
59 id INTEGER NOT NULL PRIMARY KEY,
63 INSERT INTO labels VALUES (1, "Columbia");
64 INSERT INTO labels VALUES (2, "Sony");
65 INSERT INTO labels VALUES (3, "Supraphon");
67 ---------------------------------------
69 ---------------------------------------
71 id INTEGER NOT NULL PRIMARY KEY,
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);
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);
86 ---------------------------------------
88 ---------------------------------------
90 id INTEGER NOT NULL PRIMARY KEY,
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");
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");
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");
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");
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");
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");
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");
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 "$@ $!";
137 Music::DBI->dbi_commit;
139 package Music::Artist;
140 use base 'Music::DBI';
141 Music::Artist->table('artists');
142 Music::Artist->columns(All => qw/id name/);
145 package Music::Label;
146 use base 'Music::DBI';
147 Music::Label->table('labels');
148 Music::Label->columns(All => qw/id name/);
151 use base 'Music::DBI';
152 Music::CD->table('cds');
153 Music::CD->columns(All => qw/id label artist title year/);
156 package Music::Track;
157 use base 'Music::DBI';
158 Music::Track->table('tracks');
159 Music::Track->columns(All => qw/id cd position title/);
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');
173 my @artists = Music::Artist->deep_search_where($where, $attr);
174 is_deeply [ sort @artists ], [ 1, 2 ], "all without order";
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";
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";
192 my $where = { name => { -like => 'Patsy Cline' }, };
194 my @artists = Music::Artist->deep_search_where($where, $attr);
195 is_deeply \@artists, [ 2 ], "simple search";
199 my $where = { 'artist.name' => 'Patsy Cline' };
201 my @cds = Music::CD->deep_search_where($where, $attr);
202 is_deeply [ sort @cds ], [ 5, 6, 7 ], "Patsy's CDs";
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";
211 my $count = Music::CD->count_deep_search_where($where);
212 is_deeply $count, 3, "count Patsy's CDs by title";
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'";
224 'cd.artist.name' => { -like => 'W%' },
225 'cd.year' => { '>' => 2000 },
226 'position' => { '<' => 3 }
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 ";
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";
238 'cd.artist.name' => { -like => 'W%' },
239 'cd.year' => { '>' => 2000 },
240 'position' => { '<' => 3 }
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 ";
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";
251 my $where = { 'cd.title' => [ -and => { -like => '%o%' }, { -like => '%W%' } ] };
252 my $attr = { order_by => [ 'cd.id' ] } ;
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'";
259 my $where = { 'cd.year' => [ 1995, 1999 ] };
260 my $attr = { order_by => [ 'cd.id' ] } ;
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";
268 my $where = { 'cd.year' => { -in => [ 1995, 1999 ] } };
269 my $attr = { order_by => [ 'cd.id' ] } ;
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";
277 my $where = { -and => [ 'cd.year' => [ 1995, 1999 ], position => { '<=', 2 } ] };
278 my $attr = { order_by => [ 'cd.id' ] } ;
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";
286 my $where = { -and => [ 'cd.year' => { -in => [ 1995, 1999 ] }, position => { '<=', 2 } ] };
287 my $attr = { order_by => [ 'cd.id' ] } ;
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";
295 my $where = { 'label.name' => { -in => [ 'Sony', 'Supraphon', 'Bogus' ] } };
296 my $attr = { order_by => [ 'id' ] } ;
298 my @cds = Music::CD->deep_search_where($where, $attr);
299 is_deeply [ @cds ], [ 2, 4, 6, 7 ],
300 "CDs from Sony or Supraphon";
304 my $where = { 'label.name' => [ 'Sony', 'Supraphon', 'Bogus' ] };
305 my $attr = { order_by => [ 'id' ] } ;
307 my @cds = Music::CD->deep_search_where($where, $attr);
308 is_deeply [ @cds ], [ 2, 4, 6, 7 ],
309 "CDs from Sony or Supraphon";