DBIO

 view release on metacpan or  search on metacpan

lib/DBIO/Test.pm  view on Meta::CPAN

  ]);

  $schema->populate('LinerNotes', [
    [ qw/liner_id notes/ ],
    [ 2, "Buy Whiskey!" ],
    [ 4, "Buy Merch!" ],
    [ 5, "Kill Yourself!" ],
  ]);

  $schema->populate('Tag', [
    [ qw/tagid cd tag/ ],
    [ 1, 1, "Blue" ],
    [ 2, 2, "Blue" ],
    [ 3, 3, "Blue" ],
    [ 4, 5, "Blue" ],
    [ 5, 2, "Cheesy" ],
    [ 6, 4, "Cheesy" ],
    [ 7, 5, "Cheesy" ],
    [ 8, 2, "Shiny" ],
    [ 9, 4, "Shiny" ],
  ]);

lib/DBIO/Test/Schema/Tag.pm  view on Meta::CPAN

package DBIO::Test::Schema::Tag;
# ABSTRACT: Test result class for the tags table

use warnings;
use strict;

use base qw/DBIO::Test::BaseResult/;

__PACKAGE__->table('tags');
__PACKAGE__->add_columns(
  'tagid' => {
    data_type => 'integer',
    is_auto_increment => 1,
  },
  'cd' => {
    data_type => 'integer',
  },
  'tag' => {
    data_type => 'varchar',
    size      => 100,
  },
);
__PACKAGE__->set_primary_key('tagid');

__PACKAGE__->add_unique_constraints(  # do not remove, part of a test
  tagid_cd     => [qw/ tagid cd /],
  tagid_cd_tag => [qw/ tagid cd tag /],
);
__PACKAGE__->add_unique_constraints(  # do not remove, part of a test
  [qw/ tagid tag /],
  [qw/ tagid tag cd /],
);

__PACKAGE__->belongs_to( cd => 'DBIO::Test::Schema::CD', 'cd', {
  proxy => [ 'year', { cd_title => 'title' } ],
});

1;

__END__

lib/DBIO/Test/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/lib/MigrationsTest/Schema/Tag.pm  view on Meta::CPAN

package # hide from PAUSE
    MigrationsTest::Schema::Tag;

use warnings;
use strict;

use base qw/MigrationsTest::BaseResult/;

__PACKAGE__->table('tags');
__PACKAGE__->add_columns(
  'tagid' => {
    data_type => 'integer',
    is_auto_increment => 1,
  },
  'cd' => {
    data_type => 'integer',
  },
  'tag' => {
    data_type => 'varchar',
    size      => 100,
  },
);
__PACKAGE__->set_primary_key('tagid');

__PACKAGE__->add_unique_constraints(  # do not remove, part of a test
  tagid_cd     => [qw/ tagid cd /],
  tagid_cd_tag => [qw/ tagid cd tag /],
);
__PACKAGE__->add_unique_constraints(  # do not remove, part of a test
  [qw/ tagid tag /],
  [qw/ tagid tag cd /],
);

__PACKAGE__->belongs_to( cd => 'MigrationsTest::Schema::CD', 'cd', {
  proxy => [ 'year', { cd_title => 'title' } ],
});

1;

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

    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/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;