DBIO-SQLite
view release on metacpan or search on metacpan
# at this point there should be no active statements
# (finish() was called everywhere, either explicitly via
# reset() or on DESTROY)
for (keys %{$schema->storage->dbh->{CachedKids}}) {
fail("Unreachable cached statement still active: $_")
if $schema->storage->dbh->{CachedKids}{$_}->FETCH('Active');
}
my $tag = $schema->resultset('Tag')->search(
[ { 'me.tag' => 'Blue' } ],
{ columns => 'tagid' }
)->next;
ok($tag->has_column_loaded('tagid'), 'Has tagid loaded');
ok(!$tag->has_column_loaded('tag'), 'Has not tag loaded');
ok($schema->storage(), 'Storage available');
{
my $rs = $schema->resultset("Artist")->search({
-and => [
artistid => { '>=', 1 },
artistid => { '<', 3 }
]
t/80unique.t view on Meta::CPAN
[ qw/primary prod_name/ ],
'Producer source has a named unique constraint'
);
is_deeply(
[ sort $schema->source('Track')->unique_constraint_names ],
[ qw/primary track_cd_position track_cd_title/ ],
'Track source has three unique constraints'
);
is_deeply(
[ sort $schema->source('Tag')->unique_constraint_names ],
[ qw/primary tagid_cd tagid_cd_tag tags_tagid_tag tags_tagid_tag_cd/ ],
'Tag source has five unique constraints (from add_unique_constraings)'
);
my $artistid = 1;
my $title = 'UNIQUE Constraint';
my $cd1 = $schema->resultset('CD')->find_or_create({
artist => $artistid,
title => $title,
year => 2005,
t/83cache.t view on Meta::CPAN
# SELECT count for nested has_many prefetch
$schema->is_executed_querycount( sub {
$artist = ($rs->all)[0];
}, 1, 'only one SQL statement executed');
$schema->is_executed_querycount( sub {
my @objs;
my $cds = $artist->cds;
my $tags = $cds->next->tags;
while( my $tag = $tags->next ) {
push @objs, $tag->tagid; #warn "tag:", $tag->ID, " => ", $tag->tag;
}
is_deeply( \@objs, [ 3 ], 'first cd has correct tags' );
$tags = $cds->next->tags;
@objs = ();
while( my $tag = $tags->next ) {
push @objs, $tag->id; #warn "tag: ", $tag->ID;
}
t/99dbic_sqlt_parser.t view on Meta::CPAN
is_deeply (
[sort map { $_->name } @tables],
[qw/cd cd_to_producer producer tags track/],
'partial dbic schema parsing ok',
);
# the primary key is currently unnamed in sqlt - adding below
my %constraints_for_table = (
producer => [qw/prod_name /],
tags => [qw/tagid_cd tagid_cd_tag tags_fk_cd tags_tagid_tag tags_tagid_tag_cd /],
track => [qw/track_cd_position track_cd_title track_fk_cd /],
cd => [qw/cd_artist_title cd_fk_single_track /],
cd_to_producer => [qw/cd_to_producer_fk_cd cd_to_producer_fk_producer /],
);
for my $table (@tables) {
my $tablename = $table->name;
my @constraints = $table->get_constraints;
is_deeply (
[ sort map { $_->name } @constraints ],
t/count/distinct.t view on Meta::CPAN
$rs = $schema->resultset('Tag')->search({ tag => \" IN ('Blue', 'Cheesy')" }, { group_by => 'tag' });
is($get_count->($rs), 2, 'Count with literal SQL and single group_by');
$rs = $schema->resultset('Tag')->search({ tag => \" IN ('Blue', 'Cheesy')" }, { group_by => 'cd' });
is($get_count->($rs), 5, 'Count with literal SQL and another single group_by');
$rs = $schema->resultset('Tag')->search({ tag => \" IN ('Blue', 'Cheesy')" }, { group_by => [ qw/tag cd/ ] });
is($get_count->($rs), 7, 'Count with literal SQL and multiple group_by');
$rs = $schema->resultset('Tag')->search({ tag => 'Blue' }, { '+select' => { max => 'tagid' }, distinct => 1 });
is($get_count->($rs), 4, 'Count with +select aggreggate');
$rs = $schema->resultset('Tag')->search({}, { select => [\'length(me.tag)'], distinct => 1 });
is($get_count->($rs), 3, 'Count by distinct function result as select literal');
}
throws_ok(
sub { my $row = $schema->resultset('Tag')->search({}, { select => { distinct => [qw/tag cd/] } })->first },
qr/\Qselect => { distinct => ... } syntax is not supported for multiple columns/,
'throw on unsupported syntax'
t/count/distinct.t view on Meta::CPAN
dbic_colname => 'cds.title',
sqlt_size => 100,
} => 'fooooo' ],
],
);
is ($rs->next->get_column ('num_cds'), 3, 'Function aliased correctly');
}
# These two rely on the database to throw an exception. This might not be the case one day. Please revise.
dies_ok(sub { my $count = $schema->resultset('Tag')->search({}, { '+select' => \'tagid AS tag_id', distinct => 1 })->count }, 'expecting to die');
done_testing;
t/lib/sqlite.sql view on Meta::CPAN
"lyric_id" integer NOT NULL,
"text" varchar(100) NOT NULL,
FOREIGN KEY ("lyric_id") REFERENCES "lyrics"("lyric_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "lyric_versions_idx_lyric_id" ON "lyric_versions" ("lyric_id");
CREATE UNIQUE INDEX "lyric_versions_lyric_id_text" ON "lyric_versions" ("lyric_id", "text");
CREATE TABLE "tags" (
"tagid" INTEGER PRIMARY KEY NOT NULL,
"cd" integer NOT NULL,
"tag" varchar(100) NOT NULL,
FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "tags_idx_cd" ON "tags" ("cd");
CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd");
CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag");
CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag");
CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd");
CREATE TABLE "cd_to_producer" (
"cd" integer NOT NULL,
"producer" integer NOT NULL,
"attribute" integer,
PRIMARY KEY ("cd", "producer"),
FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("producer") REFERENCES "producer"("producerid")
);
t/prefetch/grouped.t view on Meta::CPAN
my $rs = $schema->resultset("CD")->search({}, {
prefetch => 'tags',
order_by => 'cdid',
distinct => 1,
});
is_same_sql_bind (
$rs->as_query,
'(
SELECT "me"."cdid", "me"."artist", "me"."title", "me"."year", "me"."genreid", "me"."single_track",
"tags"."tagid", "tags"."cd", "tags"."tag"
FROM (
SELECT "me"."cdid", "me"."artist", "me"."title", "me"."year", "me"."genreid", "me"."single_track"
FROM cd "me"
GROUP BY "me"."cdid", "me"."artist", "me"."title", "me"."year", "me"."genreid", "me"."single_track"
) "me"
LEFT JOIN "tags" "tags" ON "tags"."cd" = "me"."cdid"
ORDER BY "cdid"
)',
[],
'Prefetch + distinct resulted in correct group_by',
t/prefetch/grouped.t view on Meta::CPAN
prefetch => ['tags'],
distinct => 1,
order_by => {'-desc' => 'tags.tag'},
offset => 1,
rows => 3,
});
is_same_sql_bind($rs->as_query,
'(
SELECT "me"."cdid", "me"."title", "me"."test_count",
"tags"."tagid", "tags"."cd", "tags"."tag"
FROM (
SELECT "me"."cdid", "me"."title",
COUNT( "tags"."tag" ) AS "test_count"
FROM cd "me"
LEFT JOIN "tags" "tags"
ON "tags"."cd" = "me"."cdid"
GROUP BY "me"."cdid", "me"."title"
ORDER BY MAX( "tags"."tag" ) DESC
LIMIT ?
OFFSET ?
t/prefetch/grouped.t view on Meta::CPAN
LEFT JOIN "tags" "tags"
ON "tags"."cd" = "me"."cdid"
ORDER BY "tags"."tag" DESC
)',
[ [$ROWS => 3], [$OFFSET => 1] ],
'Expected limited prefetch with distinct SQL',
);
my $expected_hri = [
{ cdid => 4, test_count => 2, title => "Generic Manufactured Singles", tags => [
{ cd => 4, tag => "Shiny", tagid => 9 },
{ cd => 4, tag => "Cheesy", tagid => 6 },
]},
{
cdid => 5, test_count => 2, title => "Come Be Depressed With Us", tags => [
{ cd => 5, tag => "Cheesy", tagid => 7 },
{ cd => 5, tag => "Blue", tagid => 4 },
]},
{
cdid => 1, test_count => 1, title => "Spoonful of bees", tags => [
{ cd => 1, tag => "Blue", tagid => 1 },
]},
];
is_deeply (
$rs->all_hri,
$expected_hri,
'HRI dump of limited prefetch with distinct as expected'
);
# pre-multiplied main source also should work
t/prefetch/grouped.t view on Meta::CPAN
prefetch => ['tags'],
distinct => 1,
order_by => {'-desc' => 'tags.tag'},
offset => 1,
rows => 3,
});
is_same_sql_bind($rs->as_query,
'(
SELECT "cds"."cdid", "cds"."title", "cds"."test_count",
"tags"."tagid", "tags"."cd", "tags"."tag"
FROM cd "me"
JOIN "artist" "artist"
ON "artist"."artistid" = "me"."artist"
JOIN (
SELECT "cds"."cdid", "cds"."title",
COUNT( "tags"."tag" ) AS "test_count",
"cds"."artist"
FROM cd "me"
JOIN "artist" "artist"
ON "artist"."artistid" = "me"."artist"
t/prefetch/standard.t view on Meta::CPAN
join => { 'artist' => {} }
}
);
is( $cd->artist->name, 'Caterwauler McCrae', 'single related column prefetched');
}, 1, 'manual prefetch ran only 1 select statement');
# start test for nested prefetch SELECT count
my $tag;
$schema->is_executed_querycount( sub {
$rs = $schema->resultset('Tag')->search(
{ 'me.tagid' => 1 },
{
prefetch => { cd => 'artist' }
}
);
$tag = $rs->first;
is( $tag->cd->title, 'Spoonful of bees', 'step 1 ok for nested prefetch' );
is( $tag->cd->artist->name, 'Caterwauler McCrae', 'step 2 ok for nested prefetch');
t/prefetch/via_search_related.t view on Meta::CPAN
scalar ($no_prefetch->all),
"Amount of returned rows is right"
);
}, 'search_related prefetch with order_by works');
lives_ok ( sub {
my $no_prefetch = $schema->resultset('Track')->search_related(cd =>
{
'cd.year' => "2000",
'tagid' => 1,
},
{
join => 'tags',
rows => 1,
}
);
my $use_prefetch = $no_prefetch->search(
undef,
{
t/search/related_strip_prefetch.t view on Meta::CPAN
my $rs = $schema->resultset('CD')->search (
{ 'tracks.trackid' => { '!=', 666 }},
{ join => 'artist', prefetch => 'tracks', rows => 2 }
);
my $rel_rs = $rs->search_related ('tags', { 'tags.tag' => { '!=', undef }}, { distinct => 1});
is_same_sql_bind (
$rel_rs->as_query,
'(
SELECT "tags"."tagid", "tags"."cd", "tags"."tag"
FROM (
SELECT "me"."cdid", "me"."artist", "me"."title", "me"."year", "me"."genreid", "me"."single_track"
FROM cd "me"
JOIN "artist" "artist" ON "artist"."artistid" = "me"."artist"
LEFT JOIN "track" "tracks" ON "tracks"."cd" = "me"."cdid"
WHERE ( "tracks"."trackid" != ? )
LIMIT ?
) "me"
JOIN "artist" "artist" ON "artist"."artistid" = "me"."artist"
JOIN "tags" "tags" ON "tags"."cd" = "me"."cdid"
WHERE ( "tags"."tag" IS NOT NULL )
GROUP BY "tags"."tagid", "tags"."cd", "tags"."tag"
)',
[
[ { sqlt_datatype => 'integer', dbic_colname => 'tracks.trackid' } => 666 ],
[ $ROWS => 2 ]
],
'Prefetch spec successfully stripped on search_related'
);
done_testing;
( run in 3.017 seconds using v1.01-cache-2.11-cpan-acf6aa7dc9e )