[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / generic_subq.t

use strict;
use warnings;

use Test::More;

local $TODO = 'Temporarily todo-ed for dq2eb';

use lib qw(t/lib);
use List::Util 'min';
use DBICTest;
use DBIC::SqlMakerTest;
use DBIx::Class::SQLMaker::LimitDialects;
my ($ROWS, $TOTAL, $OFFSET) = (
   DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
   DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
   DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
);


my $schema = DBICTest->init_schema;

$schema->storage->_sql_maker->limit_dialect ('GenericSubQ');

my $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
  '+columns' => [{ owner_name => 'owner.name' }],
  join => 'owner',
  rows => 2,
  order_by => 'me.title',
});

is_same_sql_bind(
  $rs->as_query,
  '(
    SELECT  me.id, me.source, me.owner, me.title, me.price,
            owner_name
      FROM (
        SELECT  me.id, me.source, me.owner, me.title, me.price,
                owner.name AS owner_name
          FROM books me
          JOIN owners owner ON owner.id = me.owner
        WHERE ( source = ? )
      ) me
    WHERE
      (
        SELECT COUNT(*)
          FROM books rownum__emulation
        WHERE rownum__emulation.title < me.title
      ) < ?
    ORDER BY me.title ASC
  )',
  [
    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
    [ $ROWS => 2 ],
  ],
);

is_deeply (
  [ $rs->get_column ('title')->all ],
  ['Best Recipe Cookbook', 'Dynamical Systems'],
  'Correct columns selected with rows',
);

$schema->storage->_sql_maker->quote_char ('"');
$schema->storage->_sql_maker->name_sep ('.');

$rs = $schema->resultset ('BooksInLibrary')->search ({}, {
  order_by => { -desc => 'title' },
  '+select' => ['owner.name'],
  '+as' => ['owner.name'],
  join => 'owner',
  rows => 3,
  offset => 1,
});

is_same_sql_bind(
  $rs->as_query,
  '(
    SELECT  "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
            "owner__name"
      FROM (
        SELECT  "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
                "owner"."name" AS "owner__name"
          FROM "books" "me"
          JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
        WHERE ( "source" = ? )
      ) "me"
    WHERE
      (
        SELECT COUNT(*)
          FROM "books" "rownum__emulation"
        WHERE "rownum__emulation"."title" > "me"."title"
      ) BETWEEN ? AND ?
    ORDER BY "me"."title" DESC
  )',
  [
    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
    [ $OFFSET => 1 ],
    [ $TOTAL => 3 ],
  ],
);

is_deeply (
  [ $rs->get_column ('title')->all ],
  [ 'Dynamical Systems', 'Best Recipe Cookbook' ],
  'Correct columns selected with rows',
);

$rs = $schema->resultset ('BooksInLibrary')->search ({}, {
  order_by => 'title',
  'select' => ['owner.name'],
  'as' => ['owner_name'],
  join => 'owner',
  offset => 1,
});

is_same_sql_bind(
  $rs->as_query,
  '(
    SELECT "owner_name"
      FROM (
        SELECT "owner"."name" AS "owner_name", "title" AS "ORDER__BY__001"
          FROM "books" "me"
          JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
        WHERE ( "source" = ? )
      ) "me"
    WHERE
      (
        SELECT COUNT(*)
          FROM "books" "rownum__emulation"
        WHERE "rownum__emulation"."title" < "ORDER__BY__001"
      ) BETWEEN ? AND ?
    ORDER BY "ORDER__BY__001" ASC
  )',
  [
    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
    [ $OFFSET => 1 ],
    [ $TOTAL => 2147483647 ],
  ],
);

is_deeply (
  [ $rs->get_column ('owner_name')->all ],
  [ ('Newton') x 2 ],
  'Correct columns selected with rows',
);

$rs = $schema->resultset('CD')->search({}, {
  columns => [qw( me.cdid me.title me.genreid me.year tracks.position tracks.title )],
  join => 'tracks',
  collapse => 1,
  order_by => [ { -asc => 'me.genreid' }, { -desc => 'year' }, 'me.title', \ 'single_track DESC', { -desc => [qw( me.cdid tracks.position )] } ],
});

my @full_res = @{$rs->all_hri};

is (@full_res, 5, 'Expected amount of CDs');

is_deeply (
  \@full_res,
  [
    { cdid => 2, genreid => undef, title => "Forkful of bees", year => 2001, tracks => [
      { position => 3, title => "Sticky Honey" },
      { position => 2, title => "Stripy" },
      { position => 1, title => "Stung with Success" },
    ] },
    { cdid => 4, genreid => undef, title => "Generic Manufactured Singles", year => 2001, tracks => [
      { position => 3, title => "No More Ideas" },
      { position => 2, title => "Boring Song" },
      { position => 1, title => "Boring Name" },
    ] },
    { cdid => 5, genreid => undef, title => "Come Be Depressed With Us", year => 1998, tracks => [
      { position => 3, title => "Suicidal" },
      { position => 2, title => "Under The Weather" },
      { position => 1, title => "Sad" },
    ] },
    { cdid => 3, genreid => undef, title => "Caterwaulin' Blues", year => 1997, tracks => [
      { position => 3, title => "Fowlin" },
      { position => 2, title => "Howlin" },
      { position => 1, title => "Yowlin" },
    ] },
    { cdid => 1, genreid => 1, title => "Spoonful of bees", year => 1999, tracks => [
      { position => 3, title => "Beehind You" },
      { position => 2, title => "Apiary" },
      { position => 1, title => "The Bees Knees" },
    ] },
  ],
  'Complex ordered gensubq limited cds and tracks in expected sqlite order'
);

for my $slice (
  [0, 10],
  [3, 5 ],
  [4, 6 ],
  [0, 2 ],
  [1, 3 ],
) {

  my $rownum_cmp_op = $slice->[0]
    ? 'BETWEEN ? AND ?'
    : ' < ?'
  ;

  is_deeply(
    $rs->slice(@$slice)->all_hri,
    [ @full_res[ $slice->[0] .. min($#full_res, $slice->[1]) ] ],
    "Expected array slice on complex ordered limited gensubq ($slice->[0] : $slice->[1])",
  );

  is_same_sql_bind(
    $rs->slice(@$slice)->as_query,
    qq{(
      SELECT  "me"."cdid", "me"."title", "me"."genreid", "me"."year",
              "tracks"."position", "tracks"."title"
        FROM (
          SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
            FROM (
              SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
                FROM cd "me"
                LEFT JOIN "track" "tracks"
                  ON "tracks"."cd" = "me"."cdid"
              GROUP BY "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
             ) "me"
          WHERE (
            SELECT COUNT( * )
              FROM cd "rownum__emulation"
            WHERE (
              ( "me"."genreid" IS NOT NULL AND "rownum__emulation"."genreid" IS NULL )
                OR
              (
                "rownum__emulation"."genreid" < "me"."genreid"
                  AND
                "me"."genreid" IS NOT NULL
                  AND
                "rownum__emulation"."genreid" IS NOT NULL
              )
                OR
              (
                (
                  "me"."genreid" = "rownum__emulation"."genreid"
                    OR
                  ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
                )
                  AND
                "rownum__emulation"."year" > "me"."year"
              )
                OR
              (
                (
                  "me"."genreid" = "rownum__emulation"."genreid"
                    OR
                  ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
                )
                  AND
                "me"."year" = "rownum__emulation"."year"
                  AND
                "rownum__emulation"."title" < "me"."title"
              )
                OR
              (
                (
                  "me"."genreid" = "rownum__emulation"."genreid"
                    OR
                  ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
                )
                  AND
                "me"."year" = "rownum__emulation"."year"
                  AND
                "me"."title" = "rownum__emulation"."title"
                  AND
                (
                  ("me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NOT NULL )
                    OR
                  (
                    "rownum__emulation"."single_track" > "me"."single_track"
                      AND
                    "me"."single_track" IS NOT NULL
                      AND
                    "rownum__emulation"."single_track" IS NOT NULL
                  )
                )
              )
                OR
              (
                (
                  "me"."genreid" = "rownum__emulation"."genreid"
                    OR
                  ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
                )
                AND
                "me"."year" = "rownum__emulation"."year"
                  AND
                "me"."title" = "rownum__emulation"."title"
                  AND
                (
                  ( "me"."single_track" = "rownum__emulation"."single_track" )
                    OR
                  ( "me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NULL )
                )
                  AND
                "rownum__emulation"."cdid" > "me"."cdid"
              )
            )
          ) $rownum_cmp_op
          ORDER BY "me"."genreid" ASC, "me"."year" DESC, "me"."title" ASC, "me"."single_track" DESC, "me"."cdid" DESC
        ) "me"
        LEFT JOIN "track" "tracks"
          ON "tracks"."cd" = "me"."cdid"
      ORDER BY "me"."genreid" ASC, "year" DESC, "me"."title", single_track DESC, "me"."cdid" DESC, "tracks"."position" DESC
    )},
    [
      ( $slice->[0] ? [ $OFFSET => $slice->[0] ] : () ),
      [ $TOTAL => $slice->[1] + ($slice->[0] ? 0 : 1 ) ],
    ],
    "Expected sql on complex ordered limited gensubq ($slice->[0] : $slice->[1])",
  );
}

{
  $rs = $schema->resultset('Artist')->search({}, {
    columns => 'artistid',
    offset => 1,
    order_by => 'artistid',
  });
  local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";

  like (
    ${$rs->as_query}->[0],
    qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
    'Newlines/spaces preserved in final sql',
  );
}

done_testing;
Commit	Line	Data
75f025cf	1	use strict;
	2	use warnings;
	3
	4	use Test::More;
d7571c64	5
	6	local $TODO = 'Temporarily todo-ed for dq2eb';
	7
75f025cf	8	use lib qw(t/lib);
318e3d94	9	use List::Util 'min';
75f025cf	10	use DBICTest;
75f025cf	11	use DBIC::SqlMakerTest;
fcb7fcbb	12	use DBIx::Class::SQLMaker::LimitDialects;
	13	my ($ROWS, $TOTAL, $OFFSET) = (
	14	DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
	15	DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
	16	DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
	17	);
	18
75f025cf	19
	20	my $schema = DBICTest->init_schema;
	21
	22	$schema->storage->_sql_maker->limit_dialect ('GenericSubQ');
	23
	24	my $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
	25	'+columns' => [{ owner_name => 'owner.name' }],
	26	join => 'owner',
	27	rows => 2,
	28	order_by => 'me.title',
	29	});
	30
	31	is_same_sql_bind(
	32	$rs->as_query,
	33	'(
90ed89cb	34	SELECT me.id, me.source, me.owner, me.title, me.price,
75f025cf	35	owner_name
	36	FROM (
	37	SELECT me.id, me.source, me.owner, me.title, me.price,
	38	owner.name AS owner_name
	39	FROM books me
	40	JOIN owners owner ON owner.id = me.owner
	41	WHERE ( source = ? )
75f025cf	42	) me
	43	WHERE
	44	(
	45	SELECT COUNT(*)
	46	FROM books rownum__emulation
	47	WHERE rownum__emulation.title < me.title
fcb7fcbb	48	) < ?
318e3d94	49	ORDER BY me.title ASC
75f025cf	50	)',
fcb7fcbb	51	[
	52	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
	53	[ $ROWS => 2 ],
	54	],
75f025cf	55	);
	56
	57	is_deeply (
	58	[ $rs->get_column ('title')->all ],
	59	['Best Recipe Cookbook', 'Dynamical Systems'],
	60	'Correct columns selected with rows',
	61	);
	62
	63	$schema->storage->_sql_maker->quote_char ('"');
	64	$schema->storage->_sql_maker->name_sep ('.');
	65
	66	$rs = $schema->resultset ('BooksInLibrary')->search ({}, {
	67	order_by => { -desc => 'title' },
	68	'+select' => ['owner.name'],
	69	'+as' => ['owner.name'],
	70	join => 'owner',
	71	rows => 3,
	72	offset => 1,
	73	});
	74
	75	is_same_sql_bind(
	76	$rs->as_query,
	77	'(
90ed89cb	78	SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
75f025cf	79	"owner__name"
	80	FROM (
	81	SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
	82	"owner"."name" AS "owner__name"
	83	FROM "books" "me"
	84	JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
	85	WHERE ( "source" = ? )
75f025cf	86	) "me"
	87	WHERE
	88	(
	89	SELECT COUNT(*)
	90	FROM "books" "rownum__emulation"
	91	WHERE "rownum__emulation"."title" > "me"."title"
fcb7fcbb	92	) BETWEEN ? AND ?
318e3d94	93	ORDER BY "me"."title" DESC
75f025cf	94	)',
fcb7fcbb	95	[
	96	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
	97	[ $OFFSET => 1 ],
	98	[ $TOTAL => 3 ],
	99	],
75f025cf	100	);
	101
	102	is_deeply (
	103	[ $rs->get_column ('title')->all ],
	104	[ 'Dynamical Systems', 'Best Recipe Cookbook' ],
	105	'Correct columns selected with rows',
	106	);
	107
	108	$rs = $schema->resultset ('BooksInLibrary')->search ({}, {
	109	order_by => 'title',
	110	'select' => ['owner.name'],
	111	'as' => ['owner_name'],
	112	join => 'owner',
	113	offset => 1,
	114	});
	115
	116	is_same_sql_bind(
	117	$rs->as_query,
	118	'(
	119	SELECT "owner_name"
	120	FROM (
3e584f6f	121	SELECT "owner"."name" AS "owner_name", "title" AS "ORDER__BY__001"
75f025cf	122	FROM "books" "me"
	123	JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
	124	WHERE ( "source" = ? )
75f025cf	125	) "me"
	126	WHERE
	127	(
	128	SELECT COUNT(*)
	129	FROM "books" "rownum__emulation"
3e584f6f	130	WHERE "rownum__emulation"."title" < "ORDER__BY__001"
fcb7fcbb	131	) BETWEEN ? AND ?
3e584f6f	132	ORDER BY "ORDER__BY__001" ASC
75f025cf	133	)',
fcb7fcbb	134	[
	135	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
	136	[ $OFFSET => 1 ],
	137	[ $TOTAL => 2147483647 ],
	138	],
75f025cf	139	);
	140
	141	is_deeply (
	142	[ $rs->get_column ('owner_name')->all ],
	143	[ ('Newton') x 2 ],
	144	'Correct columns selected with rows',
	145	);
	146
318e3d94	147	$rs = $schema->resultset('CD')->search({}, {
	148	columns => [qw( me.cdid me.title me.genreid me.year tracks.position tracks.title )],
	149	join => 'tracks',
	150	collapse => 1,
	151	order_by => [ { -asc => 'me.genreid' }, { -desc => 'year' }, 'me.title', \ 'single_track DESC', { -desc => [qw( me.cdid tracks.position )] } ],
	152	});
	153
	154	my @full_res = @{$rs->all_hri};
	155
	156	is (@full_res, 5, 'Expected amount of CDs');
	157
	158	is_deeply (
	159	\@full_res,
	160	[
	161	{ cdid => 2, genreid => undef, title => "Forkful of bees", year => 2001, tracks => [
	162	{ position => 3, title => "Sticky Honey" },
	163	{ position => 2, title => "Stripy" },
	164	{ position => 1, title => "Stung with Success" },
	165	] },
	166	{ cdid => 4, genreid => undef, title => "Generic Manufactured Singles", year => 2001, tracks => [
	167	{ position => 3, title => "No More Ideas" },
	168	{ position => 2, title => "Boring Song" },
	169	{ position => 1, title => "Boring Name" },
	170	] },
	171	{ cdid => 5, genreid => undef, title => "Come Be Depressed With Us", year => 1998, tracks => [
	172	{ position => 3, title => "Suicidal" },
	173	{ position => 2, title => "Under The Weather" },
	174	{ position => 1, title => "Sad" },
	175	] },
	176	{ cdid => 3, genreid => undef, title => "Caterwaulin' Blues", year => 1997, tracks => [
	177	{ position => 3, title => "Fowlin" },
	178	{ position => 2, title => "Howlin" },
	179	{ position => 1, title => "Yowlin" },
	180	] },
	181	{ cdid => 1, genreid => 1, title => "Spoonful of bees", year => 1999, tracks => [
	182	{ position => 3, title => "Beehind You" },
	183	{ position => 2, title => "Apiary" },
	184	{ position => 1, title => "The Bees Knees" },
	185	] },
	186	],
	187	'Complex ordered gensubq limited cds and tracks in expected sqlite order'
	188	);
	189
	190	for my $slice (
	191	[0, 10],
	192	[3, 5 ],
	193	[4, 6 ],
	194	[0, 2 ],
	195	[1, 3 ],
	196	) {
	197
	198	my $rownum_cmp_op = $slice->[0]
	199	? 'BETWEEN ? AND ?'
	200	: ' < ?'
	201	;
	202
	203	is_deeply(
	204	$rs->slice(@$slice)->all_hri,
	205	[ @full_res[ $slice->[0] .. min($#full_res, $slice->[1]) ] ],
	206	"Expected array slice on complex ordered limited gensubq ($slice->[0] : $slice->[1])",
	207	);
	208
	209	is_same_sql_bind(
	210	$rs->slice(@$slice)->as_query,
211	qq{(
212	SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year",
213	"tracks"."position", "tracks"."title"
214	FROM (
215	SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
216	FROM (
217	SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
218	FROM cd "me"
219	LEFT JOIN "track" "tracks"
220	ON "tracks"."cd" = "me"."cdid"
221	GROUP BY "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
222	) "me"
223	WHERE (
224	SELECT COUNT( * )
225	FROM cd "rownum__emulation"
226	WHERE (
227	( "me"."genreid" IS NOT NULL AND "rownum__emulation"."genreid" IS NULL )
228	OR
229	(
230	"rownum__emulation"."genreid" < "me"."genreid"
231	AND
232	"me"."genreid" IS NOT NULL
233	AND
234	"rownum__emulation"."genreid" IS NOT NULL
235	)
236	OR
237	(
238	(
239	"me"."genreid" = "rownum__emulation"."genreid"
240	OR
241	( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
242	)
243	AND
244	"rownum__emulation"."year" > "me"."year"
245	)
246	OR
247	(
248	(
249	"me"."genreid" = "rownum__emulation"."genreid"
250	OR
251	( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
252	)
253	AND
254	"me"."year" = "rownum__emulation"."year"
255	AND
256	"rownum__emulation"."title" < "me"."title"
257	)
258	OR
259	(
260	(
261	"me"."genreid" = "rownum__emulation"."genreid"
262	OR
263	( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
264	)
265	AND
266	"me"."year" = "rownum__emulation"."year"
267	AND
268	"me"."title" = "rownum__emulation"."title"
269	AND
270	(
271	("me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NOT NULL )
272	OR
273	(
274	"rownum__emulation"."single_track" > "me"."single_track"
275	AND
276	"me"."single_track" IS NOT NULL
277	AND
278	"rownum__emulation"."single_track" IS NOT NULL
279	)
280	)
281	)
282	OR
283	(
284	(
285	"me"."genreid" = "rownum__emulation"."genreid"
286	OR
287	( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
288	)
289	AND
290	"me"."year" = "rownum__emulation"."year"
291	AND
292	"me"."title" = "rownum__emulation"."title"
293	AND
294	(
295	( "me"."single_track" = "rownum__emulation"."single_track" )
296	OR
297	( "me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NULL )
298	)
299	AND
300	"rownum__emulation"."cdid" > "me"."cdid"
301	)
302	)
303	) $rownum_cmp_op
304	ORDER BY "me"."genreid" ASC, "me"."year" DESC, "me"."title" ASC, "me"."single_track" DESC, "me"."cdid" DESC
305	) "me"
306	LEFT JOIN "track" "tracks"
307	ON "tracks"."cd" = "me"."cdid"
308	ORDER BY "me"."genreid" ASC, "year" DESC, "me"."title", single_track DESC, "me"."cdid" DESC, "tracks"."position" DESC
309	)},
310	[
311	( $slice->[0] ? [ $OFFSET => $slice->[0] ] : () ),
312	[ $TOTAL => $slice->[1] + ($slice->[0] ? 0 : 1 ) ],
313	],
314	"Expected sql on complex ordered limited gensubq ($slice->[0] : $slice->[1])",
315	);
316	}
317
d7632687	318	{
d7632687	319	$rs = $schema->resultset('Artist')->search({}, {
528e717e	320	columns => 'artistid',
d7632687	321	offset => 1,
528e717e	322	order_by => 'artistid',
d7632687	323	});
	324	local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
	325
	326	like (
	327	${$rs->as_query}->[0],
	328	qr\| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table\|x,
	329	'Newlines/spaces preserved in final sql',
	330	);
	331	}
	332
75f025cf	333	done_testing;