[dbsrgits/DBIx-Class.git] / t / sqlmaker / core_quoted.t

BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }

use strict;
use warnings;

use Test::More;
use Test::Warn;

use DBICTest ':DiffSQL';

my $schema = DBICTest->init_schema();

my $sql_maker = $schema->storage->sql_maker;

$sql_maker->quote_char('`');
$sql_maker->name_sep('.');

my ($sql, @bind) = $sql_maker->select(
          [
            {
              'me' => 'cd'
            },
            [
              {
                'artist' => 'artist',
                '-join_type' => ''
              },
              {
                'artist.artistid' => { -ident => 'me.artist' },
              }
            ],
            [
              {
                'tracks' => 'tracks',
                '-join_type' => 'left'
              },
              {
                'tracks.cd' => { -ident => 'me.cdid' },
              }
            ],
          ],
          [
            'me.cdid',
            { count => 'tracks.cd' },
            { min => 'me.year', -as => 'minyear' },
          ],
          {
            'artist.name' => 'Caterwauler McCrae',
            'me.year' => 2001
          },
          {},
          undef,
          undef
);

is_same_sql_bind(
  $sql, \@bind,
  q/
    SELECT `me`.`cdid`, COUNT( `tracks`.`cd` ), MIN( `me`.`year` ) AS `minyear`
      FROM `cd` `me`
      JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` )
      LEFT JOIN `tracks` `tracks` ON ( `tracks`.`cd` = `me`.`cdid` )
    WHERE ( `artist`.`name` = ? AND `me`.`year` = ? )
  /,
  [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ],
  'got correct SQL and bind parameters for complex select query with quoting'
);


($sql, @bind) = $sql_maker->select(
          [
            {
              'me' => 'cd'
            }
          ],
          [
            'me.cdid',
            'me.artist',
            'me.title',
            'me.year'
          ],
          undef,
          { order_by => 'year DESC' },
          undef,
          undef
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`/, [],
  'scalar ORDER BY okay (single value)'
);


($sql, @bind) = $sql_maker->select(
          [
            {
              'me' => 'cd'
            }
          ],
          [
            'me.cdid',
            'me.artist',
            'me.title',
            'me.year'
          ],
          undef,
          { order_by => [
            'year DESC',
            'title ASC'
          ]},
          undef,
          undef
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`, `title ASC`/, [],
  'scalar ORDER BY okay (multiple values)'
);

{
  ($sql, @bind) = $sql_maker->select(
            [
              {
                'me' => 'cd'
              }
            ],
            [
              'me.cdid',
              'me.artist',
              'me.title',
              'me.year'
            ],
            undef,
            { order_by => { -desc => 'year' } },
            undef,
            undef
  );

  is_same_sql_bind(
    $sql, \@bind,
    q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, [],
    'hashref ORDER BY okay (single value)'
  );


  ($sql, @bind) = $sql_maker->select(
            [
              {
                'me' => 'cd'
              }
            ],
            [
              'me.cdid',
              'me.artist',
              'me.title',
              'me.year'
            ],
            undef,
            { order_by => [
              { -desc => 'year' },
              { -asc => 'title' },
            ]},
            undef,
            undef
  );

  is_same_sql_bind(
    $sql, \@bind,
    q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC, `title` ASC/, [],
    'hashref ORDER BY okay (multiple values)'
  );

}


($sql, @bind) = $sql_maker->select(
          [
            {
              'me' => 'cd'
            }
          ],
          [
            'me.cdid',
            'me.artist',
            'me.title',
            'me.year'
          ],
          undef,
          { order_by => \'year DESC' },
          undef,
          undef
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, [],
  'did not quote ORDER BY with scalarref (single value)'
);


($sql, @bind) = $sql_maker->select(
          [
            {
              'me' => 'cd'
            }
          ],
          [
            'me.cdid',
            'me.artist',
            'me.title',
            'me.year'
          ],
          undef,
          { order_by => [
            \'year DESC',
            \'title ASC'
          ]},
          undef,
          undef
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC, title ASC/, [],
  'did not quote ORDER BY with scalarref (multiple values)'
);


($sql, @bind) = $sql_maker->select(
  [ { me => 'cd' }                  ],
  [qw/ me.cdid me.artist me.title  /],
  { cdid => \['rlike ?', [cdid => 'X'] ]       },
  { group_by => 'title', having => \['count(me.artist) > ?', [ cnt => 2] ] },
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike ? ) GROUP BY `title` HAVING count(me.artist) > ?/,
  [ [ cdid => 'X'], ['cnt' => '2'] ],
  'Quoting works with where/having arrayrefsrefs',
);


($sql, @bind) = $sql_maker->select(
  [ { me => 'cd' }                  ],
  [qw/ me.cdid me.artist me.title  /],
  { cdid => \'rlike X'              },
  { group_by => 'title', having => \'count(me.artist) > 2' },
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike X ) GROUP BY `title` HAVING count(me.artist) > 2/,
  [],
  'Quoting works with where/having scalarrefs',
);


($sql, @bind) = $sql_maker->update(
          'group',
          {
            'order' => '12',
            'name' => 'Bill'
          }
);

is_same_sql_bind(
  $sql, \@bind,
  q/UPDATE `group` SET `name` = ?, `order` = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
  'quoted table names for UPDATE'
);

{
  ($sql, @bind) = $sql_maker->select(
        [
          {
            'me' => 'cd'
          }
        ],
        [
          'me.*'
        ],
        undef,
        undef,
        undef,
        undef,
  );

  is_same_sql_bind(
    $sql, \@bind,
    q/SELECT `me`.* FROM `cd` `me`/, [],
    'select attr with me.* is right'
  );
}


$sql_maker->quote_char([qw/[ ]/]);

($sql, @bind) = $sql_maker->select(
          [
            {
              'me' => 'cd'
            },
            [
              {
                'artist' => 'artist',
                '-join_type' => ''
              },
              {
                'artist.artistid' => { -ident => 'me.artist' }
              }
            ]
          ],
          [
            {
              max => 'rank',
              -as => 'max_rank',
            },
            'rank',
            {
              'count' => '*',
              -as => 'cnt',
            }
          ],
          {
            'artist.name' => 'Caterwauler McCrae',
            'me.year' => 2001
          },
          undef,
          undef,
          undef,
);

is_same_sql_bind(
  $sql, \@bind,
  q/SELECT MAX ( [rank] ) AS [max_rank], [rank], COUNT( * ) AS [cnt] FROM [cd] [me]  JOIN [artist] [artist] ON ( [artist].[artistid] = [me].[artist] ) WHERE ( [artist].[name] = ? AND [me].[year] = ? )/, [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ],
  'got correct SQL and bind parameters for count query with bracket quoting'
);


($sql, @bind) = $sql_maker->update(
          'group',
          {
            'order' => '12',
            'name' => 'Bill'
          }
);

is_same_sql_bind(
  $sql, \@bind,
  q/UPDATE [group] SET [name] = ?, [order] = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
  'bracket quoted table names for UPDATE'
);


# Warning and sane behavior on ... select => [] ...
warnings_exist {
  local $TODO = "Some day we need to stop issuing implicit SELECT *";
  is_same_sql_bind(
    $schema->resultset("Artist")->search({}, { columns => [] })->as_query,
    '( SELECT 42 FROM [artist] [me] )',
    [],
  );
} qr/\QResultSets with an empty selection are deprecated (you almost certainly did not mean to do that): if this is indeed your intent you must explicitly supply/;

done_testing;
Commit	Line	Data
c0329273	1	BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ \|\| $! ) }
c0329273	2
3da841f1	3	use strict;
	4	use warnings;
	5
	6	use Test::More;
02562a20	7	use Test::Warn;
c0329273	8
a5a7bb73	9	use DBICTest ':DiffSQL';
3da841f1	10
c216324a	11	my $schema = DBICTest->init_schema();
3da841f1	12
c216324a	13	my $sql_maker = $schema->storage->sql_maker;
3da841f1	14
	15	$sql_maker->quote_char('`');
	16	$sql_maker->name_sep('.');
	17
9b459129	18	my ($sql, @bind) = $sql_maker->select(
3da841f1	19	[
	20	{
	21	'me' => 'cd'
	22	},
	23	[
	24	{
	25	'artist' => 'artist',
	26	'-join_type' => ''
	27	},
	28	{
1efc866d	29	'artist.artistid' => { -ident => 'me.artist' },
3da841f1	30	}
83e09b5b	31	],
	32	[
	33	{
	34	'tracks' => 'tracks',
	35	'-join_type' => 'left'
	36	},
	37	{
1efc866d	38	'tracks.cd' => { -ident => 'me.cdid' },
83e09b5b	39	}
83e09b5b	40	],
3da841f1	41	],
3da841f1	42	[
83e09b5b	43	'me.cdid',
83e09b5b	44	{ count => 'tracks.cd' },
c859e108	45	{ min => 'me.year', -as => 'minyear' },
3da841f1	46	],
	47	{
	48	'artist.name' => 'Caterwauler McCrae',
	49	'me.year' => 2001
	50	},
a6b68a60	51	{},
3da841f1	52	undef,
	53	undef
	54	);
	55
9b459129	56	is_same_sql_bind(
9b459129	57	$sql, \@bind,
83e09b5b	58	q/
c859e108	59	SELECT `me`.`cdid`, COUNT( `tracks`.`cd` ), MIN( `me`.`year` ) AS `minyear`
83e09b5b	60	FROM `cd` `me`
	61	JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` )
	62	LEFT JOIN `tracks` `tracks` ON ( `tracks`.`cd` = `me`.`cdid` )
	63	WHERE ( `artist`.`name` = ? AND `me`.`year` = ? )
	64	/,
	65	[ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ],
	66	'got correct SQL and bind parameters for complex select query with quoting'
9b459129	67	);
3da841f1	68
c80207cd	69
9b459129	70	($sql, @bind) = $sql_maker->select(
3da841f1	71	[
	72	{
	73	'me' => 'cd'
	74	}
	75	],
	76	[
	77	'me.cdid',
	78	'me.artist',
	79	'me.title',
	80	'me.year'
	81	],
	82	undef,
a6b68a60	83	{ order_by => 'year DESC' },
c80207cd	84	undef,
	85	undef
	86	);
	87
	88	is_same_sql_bind(
	89	$sql, \@bind,
	90	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`/, [],
	91	'scalar ORDER BY okay (single value)'
	92	);
	93
	94
	95	($sql, @bind) = $sql_maker->select(
3da841f1	96	[
c80207cd	97	{
	98	'me' => 'cd'
	99	}
	100	],
	101	[
	102	'me.cdid',
	103	'me.artist',
	104	'me.title',
	105	'me.year'
	106	],
	107	undef,
a6b68a60	108	{ order_by => [
c80207cd	109	'year DESC',
c80207cd	110	'title ASC'
a6b68a60	111	]},
3da841f1	112	undef,
	113	undef
	114	);
	115
8682bb07	116	is_same_sql_bind(
8682bb07	117	$sql, \@bind,
c80207cd	118	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`, `title ASC`/, [],
c80207cd	119	'scalar ORDER BY okay (multiple values)'
8682bb07	120	);
8682bb07	121
20ea616f	122	{
89479564	123	($sql, @bind) = $sql_maker->select(
	124	[
	125	{
	126	'me' => 'cd'
	127	}
	128	],
	129	[
	130	'me.cdid',
	131	'me.artist',
	132	'me.title',
	133	'me.year'
	134	],
	135	undef,
a6b68a60	136	{ order_by => { -desc => 'year' } },
89479564	137	undef,
	138	undef
	139	);
3da841f1	140
89479564	141	is_same_sql_bind(
	142	$sql, \@bind,
	143	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, [],
	144	'hashref ORDER BY okay (single value)'
	145	);
3da841f1	146
c80207cd	147
89479564	148	($sql, @bind) = $sql_maker->select(
	149	[
	150	{
	151	'me' => 'cd'
	152	}
	153	],
	154	[
	155	'me.cdid',
	156	'me.artist',
	157	'me.title',
	158	'me.year'
	159	],
	160	undef,
a6b68a60	161	{ order_by => [
89479564	162	{ -desc => 'year' },
a6b68a60	163	{ -asc => 'title' },
a6b68a60	164	]},
89479564	165	undef,
	166	undef
	167	);
	168
	169	is_same_sql_bind(
	170	$sql, \@bind,
	171	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC, `title` ASC/, [],
	172	'hashref ORDER BY okay (multiple values)'
	173	);
c80207cd	174
89479564	175	}
c80207cd	176
	177
	178	($sql, @bind) = $sql_maker->select(
	179	[
	180	{
	181	'me' => 'cd'
	182	}
	183	],
	184	[
	185	'me.cdid',
	186	'me.artist',
	187	'me.title',
	188	'me.year'
3da841f1	189	],
3da841f1	190	undef,
a6b68a60	191	{ order_by => \'year DESC' },
c80207cd	192	undef,
3da841f1	193	undef
	194	);
	195
9b459129	196	is_same_sql_bind(
	197	$sql, \@bind,
	198	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, [],
c80207cd	199	'did not quote ORDER BY with scalarref (single value)'
	200	);
	201
	202
	203	($sql, @bind) = $sql_maker->select(
	204	[
	205	{
	206	'me' => 'cd'
	207	}
	208	],
	209	[
	210	'me.cdid',
	211	'me.artist',
	212	'me.title',
	213	'me.year'
	214	],
	215	undef,
a6b68a60	216	{ order_by => [
c80207cd	217	\'year DESC',
c80207cd	218	\'title ASC'
a6b68a60	219	]},
c80207cd	220	undef,
	221	undef
	222	);
	223
	224	is_same_sql_bind(
	225	$sql, \@bind,
	226	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC, title ASC/, [],
	227	'did not quote ORDER BY with scalarref (multiple values)'
3da841f1	228	);
3da841f1	229
c80207cd	230
da562e65	231	($sql, @bind) = $sql_maker->select(
	232	[ { me => 'cd' } ],
	233	[qw/ me.cdid me.artist me.title /],
	234	{ cdid => \['rlike ?', [cdid => 'X'] ] },
	235	{ group_by => 'title', having => \['count(me.artist) > ?', [ cnt => 2] ] },
	236	);
	237
	238	is_same_sql_bind(
	239	$sql, \@bind,
	240	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike ? ) GROUP BY `title` HAVING count(me.artist) > ?/,
	241	[ [ cdid => 'X'], ['cnt' => '2'] ],
9611a147	242	'Quoting works with where/having arrayrefsrefs',
	243	);
	244
	245
	246	($sql, @bind) = $sql_maker->select(
	247	[ { me => 'cd' } ],
	248	[qw/ me.cdid me.artist me.title /],
	249	{ cdid => \'rlike X' },
	250	{ group_by => 'title', having => \'count(me.artist) > 2' },
	251	);
	252
	253	is_same_sql_bind(
	254	$sql, \@bind,
	255	q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike X ) GROUP BY `title` HAVING count(me.artist) > 2/,
	256	[],
da562e65	257	'Quoting works with where/having scalarrefs',
	258	);
	259
	260
9b459129	261	($sql, @bind) = $sql_maker->update(
3da841f1	262	'group',
	263	{
	264	'order' => '12',
	265	'name' => 'Bill'
	266	}
	267	);
	268
9b459129	269	is_same_sql_bind(
	270	$sql, \@bind,
	271	q/UPDATE `group` SET `name` = ?, `order` = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
	272	'quoted table names for UPDATE'
	273	);
3da841f1	274
20ea616f	275	{
949172b0	276	($sql, @bind) = $sql_maker->select(
	277	[
	278	{
	279	'me' => 'cd'
	280	}
	281	],
	282	[
	283	'me.*'
	284	],
	285	undef,
949172b0	286	undef,
a6b68a60	287	undef,
a6b68a60	288	undef,
949172b0	289	);
c80207cd	290
949172b0	291	is_same_sql_bind(
	292	$sql, \@bind,
	293	q/SELECT `me`.* FROM `cd` `me`/, [],
	294	'select attr with me.* is right'
	295	);
	296	}
c80207cd	297
c80207cd	298
3da841f1	299	$sql_maker->quote_char([qw/[ ]/]);
3da841f1	300
9b459129	301	($sql, @bind) = $sql_maker->select(
3da841f1	302	[
	303	{
	304	'me' => 'cd'
	305	},
	306	[
	307	{
	308	'artist' => 'artist',
	309	'-join_type' => ''
	310	},
	311	{
1efc866d	312	'artist.artistid' => { -ident => 'me.artist' }
3da841f1	313	}
	314	]
	315	],
	316	[
	317	{
50136dd9	318	max => 'rank',
	319	-as => 'max_rank',
	320	},
	321	'rank',
	322	{
	323	'count' => '*',
	324	-as => 'cnt',
3da841f1	325	}
	326	],
	327	{
	328	'artist.name' => 'Caterwauler McCrae',
	329	'me.year' => 2001
	330	},
3da841f1	331	undef,
a6b68a60	332	undef,
a6b68a60	333	undef,
3da841f1	334	);
3da841f1	335
9b459129	336	is_same_sql_bind(
9b459129	337	$sql, \@bind,
50136dd9	338	q/SELECT MAX ( [rank] ) AS [max_rank], [rank], COUNT( * ) AS [cnt] FROM [cd] [me] JOIN [artist] [artist] ON ( [artist].[artistid] = [me].[artist] ) WHERE ( [artist].[name] = ? AND [me].[year] = ? )/, [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ],
9b459129	339	'got correct SQL and bind parameters for count query with bracket quoting'
9b459129	340	);
3da841f1	341
3da841f1	342
9b459129	343	($sql, @bind) = $sql_maker->update(
3da841f1	344	'group',
	345	{
	346	'order' => '12',
	347	'name' => 'Bill'
	348	}
	349	);
	350
9b459129	351	is_same_sql_bind(
	352	$sql, \@bind,
	353	q/UPDATE [group] SET [name] = ?, [order] = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
	354	'bracket quoted table names for UPDATE'
	355	);
da562e65	356
02562a20	357
	358	# Warning and sane behavior on ... select => [] ...
	359	warnings_exist {
	360	local $TODO = "Some day we need to stop issuing implicit SELECT *";
	361	is_same_sql_bind(
	362	$schema->resultset("Artist")->search({}, { columns => [] })->as_query,
	363	'( SELECT 42 FROM [artist] [me] )',
	364	[],
	365	);
	366	} qr/\QResultSets with an empty selection are deprecated (you almost certainly did not mean to do that): if this is indeed your intent you must explicitly supply/;
	367
da562e65	368	done_testing;