t/sqlmaker/pg.t

   1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
   2
   3 use strict;
   4 use warnings;
   5
   6 use Test::More;
   7
   8 use DBICTest ':DiffSQL';
   9
  10 my $schema = DBICTest->init_schema(
  11   no_deploy => 1,
  12   quote_names => 1,
  13   storage_type => 'DBIx::Class::Storage::DBI::Pg'
  14 );
  15
  16 my $rs = $schema->resultset('Artist')->search_related('cds_unordered',
  17   { "me.rank" => 13 },
  18   {
  19     prefetch => 'tracks',
  20     join => 'genre',
  21     order_by => [ 'genre.name', { -desc => \ 'tracks.title' }, { -asc => "me.name" }, { -desc => [qw(year cds_unordered.title)] } ], # me. is the artist, *NOT* the cd
  22     rows => 1,
  23   },
  24 );
  25
  26 {
  27   # THIS IS AN OFFLINE TEST
  28   # We only need this so that the thing can be verified to work without PG_DSN
  29   # Executing it while "lying" this way won't work
  30   local $rs->result_source->related_source('tracks')->column_info('title')->{data_type} = 'bool';
  31   local $rs->result_source->related_source('genre')->column_info('name')->{data_type} = 'BOOLEAN';
  32
  33   is_same_sql_bind(
  34     $rs->as_query,
  35     q{(
  36       SELECT  "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track",
  37               "tracks"."trackid", "tracks"."cd", "tracks"."position", "tracks"."title", "tracks"."last_updated_on", "tracks"."last_updated_at"
  38         FROM "artist" "me"
  39         JOIN (
  40           SELECT "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track"
  41             FROM "artist" "me"
  42             JOIN cd "cds_unordered"
  43               ON "cds_unordered"."artist" = "me"."artistid"
  44             LEFT JOIN "genre" "genre"
  45               ON "genre"."genreid" = "cds_unordered"."genreid"
  46             LEFT JOIN "track" "tracks"
  47               ON "tracks"."cd" = "cds_unordered"."cdid"
  48           WHERE "me"."rank" = ?
  49           GROUP BY "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track", "me"."name"
  50           ORDER BY  BOOL_AND("genre"."name"),
  51                     BOOL_OR( tracks.title ) DESC,
  52                     "me"."name" ASC,
  53                     "year" DESC,
  54                     "cds_unordered"."title" DESC
  55           LIMIT ?
  56         ) "cds_unordered"
  57           ON "cds_unordered"."artist" = "me"."artistid"
  58         LEFT JOIN "genre" "genre"
  59           ON "genre"."genreid" = "cds_unordered"."genreid"
  60         LEFT JOIN "track" "tracks"
  61           ON "tracks"."cd" = "cds_unordered"."cdid"
  62       WHERE "me"."rank" = ?
  63       ORDER BY  "genre"."name",
  64                 tracks.title DESC,
  65                 "me"."name" ASC,
  66                 "year" DESC,
  67                 "cds_unordered"."title" DESC
  68     )},
  69     [
  70       [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ],
  71       [ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype => 1 ],
  72       [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ],
  73     ],
  74     'correct SQL with aggregate boolean order on Pg',
  75   );
  76 }
  77
  78 done_testing;