8 use DBIC::SqlMakerTest;
10 my $schema = DBICTest->init_schema();
12 my $sql_maker = $schema->storage->sql_maker;
14 $sql_maker->quote_char('`');
15 $sql_maker->name_sep('.');
17 my ($sql, @bind) = $sql_maker->select(
28 'artist.artistid' => 'me.artist'
34 '-join_type' => 'left'
37 'tracks.cd' => 'me.cdid'
43 { count => 'tracks.cd' },
44 { min => 'me.year', -as => 'minyear' },
47 'artist.name' => 'Caterwauler McCrae',
58 SELECT `me`.`cdid`, COUNT( `tracks`.`cd` ), MIN( `me`.`year` ) AS `minyear`
60 JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` )
61 LEFT JOIN `tracks` `tracks` ON ( `tracks`.`cd` = `me`.`cdid` )
62 WHERE ( `artist`.`name` = ? AND `me`.`year` = ? )
64 [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ],
65 'got correct SQL and bind parameters for complex select query with quoting'
69 ($sql, @bind) = $sql_maker->select(
82 { order_by => 'year DESC' },
89 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`/, [],
90 'scalar ORDER BY okay (single value)'
94 ($sql, @bind) = $sql_maker->select(
117 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`, `title ASC`/, [],
118 'scalar ORDER BY okay (multiple values)'
122 ($sql, @bind) = $sql_maker->select(
135 { order_by => { -desc => 'year' } },
142 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, [],
143 'hashref ORDER BY okay (single value)'
147 ($sql, @bind) = $sql_maker->select(
170 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC, `title` ASC/, [],
171 'hashref ORDER BY okay (multiple values)'
177 ($sql, @bind) = $sql_maker->select(
190 { order_by => \'year DESC' },
197 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, [],
198 'did not quote ORDER BY with scalarref (single value)'
202 ($sql, @bind) = $sql_maker->select(
225 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC, title ASC/, [],
226 'did not quote ORDER BY with scalarref (multiple values)'
230 ($sql, @bind) = $sql_maker->select(
232 [qw/ me.cdid me.artist me.title /],
233 { cdid => \['rlike ?', [cdid => 'X'] ] },
234 { group_by => 'title', having => \['count(me.artist) > ?', [ cnt => 2] ] },
239 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike ? ) GROUP BY `title` HAVING count(me.artist) > ?/,
240 [ [ cdid => 'X'], ['cnt' => '2'] ],
241 'Quoting works with where/having arrayrefsrefs',
245 ($sql, @bind) = $sql_maker->select(
247 [qw/ me.cdid me.artist me.title /],
248 { cdid => \'rlike X' },
249 { group_by => 'title', having => \'count(me.artist) > 2' },
254 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title` FROM `cd` `me` WHERE ( `cdid` rlike X ) GROUP BY `title` HAVING count(me.artist) > 2/,
256 'Quoting works with where/having scalarrefs',
260 ($sql, @bind) = $sql_maker->update(
270 q/UPDATE `group` SET `name` = ?, `order` = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
271 'quoted table names for UPDATE'
275 ($sql, @bind) = $sql_maker->select(
292 q/SELECT `me`.* FROM `cd` `me`/, [],
293 'select attr with me.* is right'
298 $sql_maker->quote_char([qw/[ ]/]);
300 ($sql, @bind) = $sql_maker->select(
307 'artist' => 'artist',
311 'artist.artistid' => 'me.artist'
327 'artist.name' => 'Caterwauler McCrae',
337 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] ],
338 'got correct SQL and bind parameters for count query with bracket quoting'
342 ($sql, @bind) = $sql_maker->update(
352 q/UPDATE [group] SET [name] = ?, [order] = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
353 'bracket quoted table names for UPDATE'