use Test::More;
+use lib qw(t/lib);
+use DBIC::SqlMakerTest;
BEGIN {
eval "use DBD::SQLite";
plan $@
? ( skip_all => 'needs DBD::SQLite for testing' )
- : ( tests => 8 );
+ : ( tests => 12 );
}
-use lib qw(t/lib);
-
use_ok('DBICTest');
-DBICTest->init_schema();
+my $schema = DBICTest->init_schema();
-my $sql_maker = DBICTest->schema->storage->sql_maker;
+my $sql_maker = $schema->storage->sql_maker;
$sql_maker->quote_char('`');
$sql_maker->name_sep('.');
-my ($sql,) = $sql_maker->select(
+my ($sql, @bind) = $sql_maker->select(
[
{
'me' => 'cd'
undef
);
-is($sql,
- q/SELECT COUNT( * ) FROM `cd` `me` JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` ) WHERE ( `artist`.`name` = ? AND `me`.`year` = ? )/,
- 'got correct SQL for count query with quoting');
+is_same_sql_bind(
+ $sql, \@bind,
+ q/SELECT COUNT( * ) 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 quoting'
+);
+
+
+($sql, @bind) = $sql_maker->select(
+ [
+ {
+ 'me' => 'cd'
+ }
+ ],
+ [
+ 'me.cdid',
+ 'me.artist',
+ 'me.title',
+ 'me.year'
+ ],
+ undef,
+ '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,) = $sql_maker->select(
+
+($sql, @bind) = $sql_maker->select(
[
{
'me' => 'cd'
],
undef,
[
- 'year DESC'
+ 'year DESC',
+ 'title ASC'
],
undef,
undef
);
-TODO: {
- local $TODO = "order_by with quoting needs fixing (ash/castaway)";
+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,
+ { -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,
+ [
+ { -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)'
+ );
- is($sql,
- q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/,
- 'quoted ORDER BY with DESC okay');
}
-TODO: {
- local $TODO = "select attr with star needs fixing (mst/nate)";
- ($sql,) = $sql_maker->select(
+($sql, @bind) = $sql_maker->select(
[
{
'me' => 'cd'
}
],
[
- 'me.*'
+ 'me.cdid',
+ 'me.artist',
+ 'me.title',
+ 'me.year'
],
undef,
- [],
+ \'year DESC',
undef,
- 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)'
+);
- is($sql, q/SELECT `me`.* FROM `cd` `me`/, 'select attr with me.* is right');
-}
-($sql,) = $sql_maker->select(
+($sql, @bind) = $sql_maker->select(
[
{
'me' => 'cd'
],
undef,
[
- \'year DESC'
+ \'year DESC',
+ \'title ASC'
],
undef,
undef
);
-is($sql,
- q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/,
- 'did not quote ORDER BY with scalarref');
-
-my %data = (
- name => 'Bill',
- order => 12
+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)'
);
-my @binds;
-($sql,@binds) = $sql_maker->update(
+($sql, @bind) = $sql_maker->update(
'group',
{
'order' => '12',
}
);
-is($sql,
- q/UPDATE `group` SET `name` = ?, `order` = ?/,
- 'quoted table names for UPDATE');
+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
+ );
+
+ is_same_sql_bind(
+ $sql, \@bind,
+ q/SELECT `me`.* FROM `cd` `me`/, [],
+ 'select attr with me.* is right'
+ );
+}
+
$sql_maker->quote_char([qw/[ ]/]);
-($sql,) = $sql_maker->select(
+($sql, @bind) = $sql_maker->select(
[
{
'me' => 'cd'
undef
);
-is($sql,
- q/SELECT COUNT( * ) FROM [cd] [me] JOIN [artist] [artist] ON ( [artist].[artistid] = [me].[artist] ) WHERE ( [artist].[name] = ? AND [me].[year] = ? )/,
- 'got correct SQL for count query with bracket quoting');
+is_same_sql_bind(
+ $sql, \@bind,
+ q/SELECT COUNT( * ) 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,@binds) = $sql_maker->update(
+($sql, @bind) = $sql_maker->update(
'group',
{
'order' => '12',
}
);
-is($sql,
- q/UPDATE [group] SET [name] = ?, [order] = ?/,
- 'bracket quoted table names for UPDATE');
+is_same_sql_bind(
+ $sql, \@bind,
+ q/UPDATE [group] SET [name] = ?, [order] = ?/, [ ['name' => 'Bill'], ['order' => '12'] ],
+ 'bracket quoted table names for UPDATE'
+);