7 use DBIC::SqlMakerTest;
10 eval "use DBD::SQLite";
12 ? ( skip_all => 'needs DBD::SQLite for testing' )
18 my $schema = DBICTest->init_schema();
20 my $sql_maker = $schema->storage->sql_maker;
22 $sql_maker->quote_char('`');
23 $sql_maker->name_sep('.');
25 my ($sql, @bind) = $sql_maker->select(
36 'artist.artistid' => 'me.artist'
42 '-join_type' => 'left'
45 'tracks.cd' => 'me.cdid'
51 { count => 'tracks.cd' },
52 { min => 'me.year', -as => 'me.minyear' },
55 'artist.name' => 'Caterwauler McCrae',
66 SELECT `me`.`cdid`, COUNT( `tracks`.`cd` ), MIN( `me`.`year` ) AS `me`.`minyear`
68 JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` )
69 LEFT JOIN `tracks` `tracks` ON ( `tracks`.`cd` = `me`.`cdid` )
70 WHERE ( `artist`.`name` = ? AND `me`.`year` = ? )
72 [ ['artist.name' => 'Caterwauler McCrae'], ['me.year' => 2001] ],
73 'got correct SQL and bind parameters for complex select query with quoting'
77 ($sql, @bind) = $sql_maker->select(
97 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`/, [],
98 'scalar ORDER BY okay (single value)'
102 ($sql, @bind) = $sql_maker->select(
125 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year DESC`, `title ASC`/, [],
126 'scalar ORDER BY okay (multiple values)'
130 ($sql, @bind) = $sql_maker->select(
150 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, [],
151 'hashref ORDER BY okay (single value)'
155 ($sql, @bind) = $sql_maker->select(
178 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC, `title` ASC/, [],
179 'hashref ORDER BY okay (multiple values)'
185 ($sql, @bind) = $sql_maker->select(
205 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, [],
206 'did not quote ORDER BY with scalarref (single value)'
210 ($sql, @bind) = $sql_maker->select(
233 q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC, title ASC/, [],
234 'did not quote ORDER BY with scalarref (multiple values)'
238 ($sql, @bind) = $sql_maker->update(
248 q/UPDATE `group` SET `name` = ?, `order` = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
249 'quoted table names for UPDATE'
253 ($sql, @bind) = $sql_maker->select(
270 q/SELECT `me`.* FROM `cd` `me`/, [],
271 'select attr with me.* is right'
276 $sql_maker->quote_char([qw/[ ]/]);
278 ($sql, @bind) = $sql_maker->select(
285 'artist' => 'artist',
289 'artist.artistid' => 'me.artist'
305 'artist.name' => 'Caterwauler McCrae',
315 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] ],
316 'got correct SQL and bind parameters for count query with bracket quoting'
320 ($sql, @bind) = $sql_maker->update(
330 q/UPDATE [group] SET [name] = ?, [order] = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
331 'bracket quoted table names for UPDATE'