use Test::More;
BEGIN {
- eval "use DBD::SQLite";
- plan $@ ? (skip_all => 'needs DBD::SQLite for testing')
- : (tests => 19);
+ plan skip_all => 'needs DBD::SQLite for testing'
+ unless eval { require DBD::SQLite };
+
+ plan skip_all => 'needs Class::DBI::Plugin::DeepAbstractSearch'
+ unless eval { require Class::DBI::Plugin::DeepAbstractSearch };
+
+ plan tests => 19;
}
my $DB = "t/testdb";
my @DSN = ("dbi:SQLite:dbname=$DB", '', '', { AutoCommit => 0 });
package Music::DBI;
-use base qw(Class::DBI);
+use base qw(DBIx::Class::CDBICompat);
use Class::DBI::Plugin::DeepAbstractSearch;
-__PACKAGE__->set_db(Main => @DSN);
+__PACKAGE__->connection(@DSN);
my $sql = <<'SQL_END';
-- Artists
---------------------------------------
CREATE TABLE artists (
- id INTEGER NOT NULL PRIMARY KEY,
- name VARCHAR(32)
+ id INTEGER NOT NULL PRIMARY KEY,
+ name VARCHAR(32)
);
INSERT INTO artists VALUES (1, "Willie Nelson");
-- Labels
---------------------------------------
CREATE TABLE labels (
- id INTEGER NOT NULL PRIMARY KEY,
- name VARCHAR(32)
+ id INTEGER NOT NULL PRIMARY KEY,
+ name VARCHAR(32)
);
INSERT INTO labels VALUES (1, "Columbia");
-- CDs
---------------------------------------
CREATE TABLE cds (
- id INTEGER NOT NULL PRIMARY KEY,
- label INTEGER,
- artist INTEGER,
- title VARCHAR(32),
- year INTEGER
+ id INTEGER NOT NULL PRIMARY KEY,
+ label INTEGER,
+ artist INTEGER,
+ title VARCHAR(32),
+ year INTEGER
);
INSERT INTO cds VALUES (1, 1, 1, "Songs", 2005);
INSERT INTO cds VALUES (2, 2, 1, "Read Headed Stanger", 2000);
-- Tracks
---------------------------------------
CREATE TABLE tracks (
- id INTEGER NOT NULL PRIMARY KEY,
- cd INTEGER,
- position INTEGER,
- title VARCHAR(32)
+ id INTEGER NOT NULL PRIMARY KEY,
+ cd INTEGER,
+ position INTEGER,
+ title VARCHAR(32)
);
INSERT INTO tracks VALUES (1, 1, 1, "Songs: Track 1");
INSERT INTO tracks VALUES (2, 1, 2, "Songs: Track 2");
SQL_END
foreach my $statement (split /;/, $sql) {
- $statement =~ s/^\s*//gs;
- $statement =~ s/\s*$//gs;
- next unless $statement;
- Music::DBI->db_Main->do($statement) or die "$@ $!";
+ $statement =~ s/^\s*//gs;
+ $statement =~ s/\s*$//gs;
+ next unless $statement;
+ Music::DBI->db_Main->do($statement) or die "$@ $!";
}
Music::DBI->dbi_commit;
use base 'Music::DBI';
Music::Artist->table('artists');
Music::Artist->columns(All => qw/id name/);
-Music::Artist->has_many(cds => 'Music::CD');
+
package Music::Label;
use base 'Music::DBI';
Music::Label->table('labels');
Music::Label->columns(All => qw/id name/);
-Music::Label->has_many(cds => 'Music::CD');
package Music::CD;
use base 'Music::DBI';
Music::CD->table('cds');
Music::CD->columns(All => qw/id label artist title year/);
-Music::CD->has_many(tracks => 'Music::Track');
-Music::CD->has_a(artist => 'Music::Artist');
-Music::CD->has_a(label => 'Music::Label');
+
package Music::Track;
use base 'Music::DBI';
Music::Track->table('tracks');
Music::Track->columns(All => qw/id cd position title/);
+
+Music::Artist->has_many(cds => 'Music::CD');
+Music::Label->has_many(cds => 'Music::CD');
+Music::CD->has_many(tracks => 'Music::Track');
+Music::CD->has_a(artist => 'Music::Artist');
+Music::CD->has_a(label => 'Music::Label');
Music::Track->has_a(cd => 'Music::CD');
package main;
{
- my $where = { };
- my $attr;
- my @artists = Music::Artist->deep_search_where($where, $attr);
- is_deeply [ sort @artists ], [ 1, 2 ], "all without order";
+ my $where = { };
+ my $attr;
+ my @artists = Music::Artist->deep_search_where($where, $attr);
+ is_deeply [ sort @artists ], [ 1, 2 ], "all without order";
}
{
- my $where = { };
- my $attr = { order_by => 'name' };
- my @artists = Music::Artist->deep_search_where($where, $attr);
- is_deeply \@artists, [ 2, 1 ], "all with ORDER BY name";
+ my $where = { };
+ my $attr = { order_by => 'name' };
+ my @artists = Music::Artist->deep_search_where($where, $attr);
+ is_deeply \@artists, [ 2, 1 ], "all with ORDER BY name";
}
{
- my $where = { };
- my $attr = { order_by => 'name DESC' };
- my @artists = Music::Artist->deep_search_where($where, $attr);
- is_deeply \@artists, [ 1, 2 ], "all with ORDER BY name DESC";
+ my $where = { };
+ my $attr = { order_by => 'name DESC' };
+ my @artists = Music::Artist->deep_search_where($where, $attr);
+ is_deeply \@artists, [ 1, 2 ], "all with ORDER BY name DESC";
}
{
- my $where = { name => { -like => 'Patsy Cline' }, };
- my $attr;
- my @artists = Music::Artist->deep_search_where($where, $attr);
- is_deeply \@artists, [ 2 ], "simple search";
+ my $where = { name => { -like => 'Patsy Cline' }, };
+ my $attr;
+ my @artists = Music::Artist->deep_search_where($where, $attr);
+ is_deeply \@artists, [ 2 ], "simple search";
}
{
- my $where = { 'artist.name' => 'Patsy Cline' };
- my $attr = { } ;
- my @cds = Music::CD->deep_search_where($where, $attr);
- is_deeply [ sort @cds ], [ 5, 6, 7 ], "Patsy's CDs";
+ my $where = { 'artist.name' => 'Patsy Cline' };
+ my $attr = { } ;
+ my @cds = Music::CD->deep_search_where($where, $attr);
+ is_deeply [ sort @cds ], [ 5, 6, 7 ], "Patsy's CDs";
}
{
- my $where = { 'artist.name' => 'Patsy Cline' };
- my $attr = { order_by => "title" } ;
- my @cds = Music::CD->deep_search_where($where, $attr);
- is_deeply [ @cds ], [ 5, 6, 7 ], "Patsy's CDs by title";
+ my $where = { 'artist.name' => 'Patsy Cline' };
+ my $attr = { order_by => "title" } ;
+ my @cds = Music::CD->deep_search_where($where, $attr);
+ is_deeply [ @cds ], [ 5, 6, 7 ], "Patsy's CDs by title";
- my $count = Music::CD->count_deep_search_where($where);
- is_deeply $count, 3, "count Patsy's CDs by title";
+ my $count = Music::CD->count_deep_search_where($where);
+ is_deeply $count, 3, "count Patsy's CDs by title";
}
{
- my $where = { 'cd.title' => { -like => 'S%' }, };
- my $attr = { order_by => "cd.title, title" } ;
- my @cds = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @cds ], [1, 2, 3, 4, 21, 22, 23, 24 ], "Tracks from CDs whose name starts with 'S'";
+ my $where = { 'cd.title' => { -like => 'S%' }, };
+ my $attr = { order_by => "cd.title, title" } ;
+ my @cds = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @cds ], [1, 2, 3, 4, 21, 22, 23, 24 ], "Tracks from CDs whose name starts with 'S'";
}
{
- my $where = {
- 'cd.artist.name' => { -like => 'W%' },
- 'cd.year' => { '>' => 2000 },
- 'position' => { '<' => 3 }
- };
- my $attr = { order_by => "cd.title DESC, title" } ;
- my @cds = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @cds ], [ 9, 10, 1, 2 ], "First 2 tracks from W's albums after 2000 ";
-
- my $count = Music::Track->count_deep_search_where($where);
- is_deeply $count, 4, "Count First 2 tracks from W's albums after 2000";
+ my $where = {
+ 'cd.artist.name' => { -like => 'W%' },
+ 'cd.year' => { '>' => 2000 },
+ 'position' => { '<' => 3 }
+ };
+ my $attr = { order_by => "cd.title DESC, title" } ;
+ my @cds = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @cds ], [ 9, 10, 1, 2 ], "First 2 tracks from W's albums after 2000 ";
+
+ my $count = Music::Track->count_deep_search_where($where);
+ is_deeply $count, 4, "Count First 2 tracks from W's albums after 2000";
}
{
- my $where = {
- 'cd.artist.name' => { -like => 'W%' },
- 'cd.year' => { '>' => 2000 },
- 'position' => { '<' => 3 }
- };
- my $attr = { order_by => [ 'cd.title DESC' , 'title' ] } ;
- my @cds = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @cds ], [ 9, 10, 1, 2 ], "First 2 tracks from W's albums after 2000, array ref order ";
-
- my $count = Music::Track->count_deep_search_where($where);
- is_deeply $count, 4, "Count First 2 tracks from W's albums after 2000, array ref order";
+ my $where = {
+ 'cd.artist.name' => { -like => 'W%' },
+ 'cd.year' => { '>' => 2000 },
+ 'position' => { '<' => 3 }
+ };
+ my $attr = { order_by => [ 'cd.title DESC' , 'title' ] } ;
+ my @cds = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @cds ], [ 9, 10, 1, 2 ], "First 2 tracks from W's albums after 2000, array ref order ";
+
+ my $count = Music::Track->count_deep_search_where($where);
+ is_deeply $count, 4, "Count First 2 tracks from W's albums after 2000, array ref order";
}
{
- my $where = { 'cd.title' => [ -and => { -like => '%o%' }, { -like => '%W%' } ] };
- my $attr = { order_by => [ 'cd.id' ] } ;
+ my $where = { 'cd.title' => [ -and => { -like => '%o%' }, { -like => '%W%' } ] };
+ my $attr = { order_by => [ 'cd.id' ] } ;
- my @tracks = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @tracks ], [ 3, 3, 4, 4, 4, 4, 4, 4 ], "Tracks from CD titles containing 'o' AND 'W'";
+ my @tracks = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @tracks ], [ 3, 3, 4, 4, 4, 4, 4, 4 ], "Tracks from CD titles containing 'o' AND 'W'";
}
{
- my $where = { 'cd.year' => [ 1995, 1999 ] };
- my $attr = { order_by => [ 'cd.id' ] } ;
+ my $where = { 'cd.year' => [ 1995, 1999 ] };
+ my $attr = { order_by => [ 'cd.id' ] } ;
- my @tracks = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @tracks ], [ 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6 ],
- "Tracks from CDs from 1995, 1999";
+ my @tracks = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @tracks ], [ 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6 ],
+ "Tracks from CDs from 1995, 1999";
}
{
- my $where = { 'cd.year' => { -in => [ 1995, 1999 ] } };
- my $attr = { order_by => [ 'cd.id' ] } ;
+ my $where = { 'cd.year' => { -in => [ 1995, 1999 ] } };
+ my $attr = { order_by => [ 'cd.id' ] } ;
- my @tracks = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @tracks ], [ 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6 ],
- "Tracks from CDs in 1995, 1999";
+ my @tracks = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @tracks ], [ 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6 ],
+ "Tracks from CDs in 1995, 1999";
}
{
- my $where = { -and => [ 'cd.year' => [ 1995, 1999 ], position => { '<=', 2 } ] };
- my $attr = { order_by => [ 'cd.id' ] } ;
+ my $where = { -and => [ 'cd.year' => [ 1995, 1999 ], position => { '<=', 2 } ] };
+ my $attr = { order_by => [ 'cd.id' ] } ;
- my @tracks = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @tracks ], [ 4, 4, 5, 5, 6, 6 ],
- "First 2 tracks Tracks from CDs from 1995, 1999";
+ my @tracks = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @tracks ], [ 4, 4, 5, 5, 6, 6 ],
+ "First 2 tracks Tracks from CDs from 1995, 1999";
}
{
- my $where = { -and => [ 'cd.year' => { -in => [ 1995, 1999 ] }, position => { '<=', 2 } ] };
- my $attr = { order_by => [ 'cd.id' ] } ;
+ my $where = { -and => [ 'cd.year' => { -in => [ 1995, 1999 ] }, position => { '<=', 2 } ] };
+ my $attr = { order_by => [ 'cd.id' ] } ;
- my @tracks = Music::Track->deep_search_where($where, $attr);
- is_deeply [ @tracks ], [ 4, 4, 5, 5, 6, 6 ],
- "First 2 tracks Tracks from CDs in 1995, 1999";
+ my @tracks = Music::Track->deep_search_where($where, $attr);
+ is_deeply [ @tracks ], [ 4, 4, 5, 5, 6, 6 ],
+ "First 2 tracks Tracks from CDs in 1995, 1999";
}
{
- my $where = { 'label.name' => { -in => [ 'Sony', 'Supraphon', 'Bogus' ] } };
- my $attr = { order_by => [ 'id' ] } ;
+ my $where = { 'label.name' => { -in => [ 'Sony', 'Supraphon', 'Bogus' ] } };
+ my $attr = { order_by => [ 'id' ] } ;
- my @cds = Music::CD->deep_search_where($where, $attr);
- is_deeply [ @cds ], [ 2, 4, 6, 7 ],
- "CDs from Sony or Supraphon";
+ my @cds = Music::CD->deep_search_where($where, $attr);
+ is_deeply [ @cds ], [ 2, 4, 6, 7 ],
+ "CDs from Sony or Supraphon";
}
{
- my $where = { 'label.name' => [ 'Sony', 'Supraphon', 'Bogus' ] };
- my $attr = { order_by => [ 'id' ] } ;
+ my $where = { 'label.name' => [ 'Sony', 'Supraphon', 'Bogus' ] };
+ my $attr = { order_by => [ 'id' ] } ;
- my @cds = Music::CD->deep_search_where($where, $attr);
- is_deeply [ @cds ], [ 2, 4, 6, 7 ],
- "CDs from Sony or Supraphon";
+ my @cds = Music::CD->deep_search_where($where, $attr);
+ is_deeply [ @cds ], [ 2, 4, 6, 7 ],
+ "CDs from Sony or Supraphon";
}
END { unlink $DB if -e $DB }