@@ -4,65 +4,9 @@
# =========================================================================
use 5.008_001;
-
use strict;
-use warnings;
-use utf8;
-
-use Module::Build;
-use File::Basename;
-use File::Spec;
-use CPAN::Meta;
-use CPAN::Meta::Prereqs;
-
-my %args = (
- license => 'perl',
- dynamic_config => 0,
-
- configure_requires => {
- 'Module::Build' => 0.38,
- },
-
- name => 'SQL-Maker',
- module_name => 'SQL::Maker',
- allow_pureperl => 0,
-
- script_files => [glob('script/*'), glob('bin/*')],
- test_files => ((-d '.git' || $ENV{RELEASE_TESTING}) && -d 'xt') ? 't/ xt/' : 't/',
- recursive_test_files => 1,
-);
-if (-d 'share') {
- $args{share_dir} = 'share';
-}
+use Module::Build::Tiny 0.035;
-my $builder = Module::Build->subclass(
- class => 'MyBuilder',
- code => q{
- sub ACTION_distmeta {
- die "Do not run distmeta. Install Minilla and `minil install` instead.\n";
- }
- sub ACTION_installdeps {
- die "Do not run installdeps. Run `cpanm --installdeps .` instead.\n";
- }
- }
-)->new(%args);
-$builder->create_build_script();
+Build_PL();
-my $mbmeta = CPAN::Meta->load_file('MYMETA.json');
-my $meta = CPAN::Meta->load_file('META.json');
-my $prereqs_hash = CPAN::Meta::Prereqs->new(
- $meta->prereqs
-)->with_merged_prereqs(
- CPAN::Meta::Prereqs->new($mbmeta->prereqs)
-)->as_string_hash;
-my $mymeta = CPAN::Meta->new(
- {
- %{$meta->as_struct},
- prereqs => $prereqs_hash
- }
-);
-print "Merging cpanfile prereqs to MYMETA.yml\n";
-$mymeta->save('MYMETA.yml', { version => 1.4 });
-print "Merging cpanfile prereqs to MYMETA.json\n";
-$mymeta->save('MYMETA.json', { version => 2 });
@@ -1,4 +1,46 @@
-Revision history for Perl extension SQL::Maker
+Revision history for Perl module SQL::Maker
+
+1.20 2014-07-31T20:59:30Z
+
+ - DateTime is a testing deps
+ (Gelu Lupas)
+
+1.19 2014-07-30T09:32:20Z
+
+ - Fix issue 38: update() and delete() cannot use SQL::QueryMaker
+ (Kazuho Oku)
+
+1.18 2014-07-25T23:52:14Z
+
+ Handle `limit 0` correctly.
+
+1.17 2014-06-16T05:24:23Z
+
+ [BUG FIX]
+ - Treat objects that do not implement the `as_sql` method as bind parameters
+ This issue was introduced at 1.16 release.
+ (Kazuho Oku)
+
+1.16 2014-06-02T06:41:19Z
+
+ - accept objects generated by SQL::QueryMaker, introduce strict mode that disallows the use of unblessed refs
+ (Kazuho Oku)
+
+1.15 2014-04-02T13:12:56Z
+
+ - add index_hint option to select method
+ (soh335)
+
+1.14 2014-03-04T23:39:17Z
+
+ - `{x => []}` generates `1=0`
+ (karupanerura)
+ - Add options for the DELETE ... USING statement.
+ (Gelu Lupas)
+
+1.13 2014-02-10T04:29:18Z
+
+ - Documentation update(Gelu Lupas)
1.12 2013-06-25T04:25:24Z
@@ -7,9 +49,9 @@ Revision history for Perl extension SQL::Maker
- join condition accepts HashRef. eg. {'user.id' => 'user_item.user_id'}
(songmu)
- switch to Minilla
- (songmu)
+ (tokuhirom)
- add document about \%opt for insert() method
- (songmu)
+ (Toshio Ito)
- cares skip quoting asterisk after table. eg. 'foo.*' => '"foo".*'
(songmu)
@@ -18,130 +60,146 @@ Revision history for Perl extension SQL::Maker
- Added SQL::Maker#where
(tokuhirom)
-1.10
+1.10 2012-12-23
- - [IMPOTANT] SQL::Maker::Condition::compose_or now returns the SQL bracketed.
+ [IMPORTANT]
+ - SQL::Maker::Condition::compose_or now returns the SQL bracketed.
(chiba)
-1.09
+1.09 2012-12-11
- added SQL::Maker::Plugin::InsertOnDuplicate
(tokuhirom)
-1.08
+1.08 2012-12-09
- - added 'make_set_clause' method to core, and SQL::Maker::Plugin::InsertMulti use it.
+ - added 'make_set_clause' method to core,
+ and SQL::Maker::Plugin::InsertMulti use it.
(tokuhirom)
-1.07 Mon Sep 24 03:51:15 UTC 2012
+1.07 2012-09-24 03:51:15 UTC
- fix behavior for empty condition compose
-1.06 Sun Sep 16 04:30:50 UTC 2012
+1.06 2012-09-16 04:30:50 UTC
- In SQL::Maker::Select
- Add "add_where_raw" method. (issm)
- In SQL::Maker::Condition
- Add "add_raw" method. (issm)
-1.05
+1.05 not released
- support group_by at SQL::Maker->select
-1.04
+1.04 not released
- - support $where as SQL::Maker::Condition at select, update and delete(nihen)
+ - support $where as SQL::Maker::Condition at select,
+ update and delete(nihen)
- support joins at select
-1.03
+1.03 2011-10-31
- support INSERT statement without parameters on SQLite3.
https://github.com/tokuhirom/SQL-Maker/issues/11
(requested by forwardever++)
-1.02
+1.02 2011-09-22
- doc fix(reported by xaicron++)
-1.01
+1.01 2011-09-04
- - support this form: ['created_on' => { '>', \'DATE_SUB(NOW(), INTERVAL 1 DAY)' }]
+ - support this form:
+ ['created_on' => { '>', \'DATE_SUB(NOW(), INTERVAL 1 DAY)' }]
-1.00
+1.00 2011-06-29
- remove alpha tag.
-0.14
+0.14 2011-04-23
- Type check for clear error messages(gfx)
- fixed testing issue on t/10_subquery.t(reported by gfx++)
-0.13
-
- - In SQL::Maker
- - Allow 'INSERT IGNORE' by $opts->{prefix} params at insert() method (zigorou)
- - Accept key-value as array at insert() method (zigorou)
- - Accept value using subquery at insert(), update() method (zigorou)
- - Allow table alias at select() method (zigorou)
- - In SQL::Maker::Plugin::InsertMulti
- - Accept values as arrayref at insert_multi() (zigorou)
- - Allow 'INSERT IGNORE' by $opts->{prefix} params at insert_multi() (zigorou)
- - Allow literal and subquery in values at insert_multi() (zigorou)
- - Supports 'ON DUPLICATE KEY UPDATE' syntax on mysql driver at insert_multi() (zigorou)
- - In SQL::Maker::Select
- - Allow no conditional join (zigorou)
+0.13 2011-04-23
+
+ [In SQL::Maker]
+ - Allow 'INSERT IGNORE' by $opts->{prefix} params at insert() method
+ (zigorou)
+ - Accept key-value as array at insert() method (zigorou)
+ - Accept value using subquery at insert(), update() method (zigorou)
+ - Allow table alias at select() method (zigorou)
+
+ [In SQL::Maker::Plugin::InsertMulti]
+ - Accept values as arrayref at insert_multi() (zigorou)
+ - Allow 'INSERT IGNORE' by $opts->{prefix} params at insert_multi()
+ (zigorou)
+ - Allow literal and subquery in values at insert_multi() (zigorou)
+ - Supports 'ON DUPLICATE KEY UPDATE' syntax on mysql driver
+ at insert_multi() (zigorou)
-0.12
+ [In SQL::Maker::Select]
+ - Allow no conditional join (zigorou)
- SQL::Maker::Plugin::InsertMulti before 0.12 have a critical issue.
- I highly recomment version up to 0.12+, if you are using SQL::Maker::Plugin::InsertMulti.
+0.12 2011-02-17
+
+ - SQL::Maker::Plugin::InsertMulti before 0.12 have a critical issue.
+ I highly recommend version up to 0.12+,
+ if you are using SQL::Maker::Plugin::InsertMulti.
- FIX: SQL::Maker::Plugin::InsertMulti doesn't works correctly.
- doc fix on SQL::Maker::Plugin::InsertMulti
-0.11
+0.11 2011-02-15
- added SQL::Maker#new_condition method(nekokak)
-0.10
+0.10 2011-01-14
- support union, intersect, except(makamaka)
-0.09
+0.09 2011-01-10
- allow SELECT object and alias to add_join(makamaka)
-0.08
+0.08 2011-01-09
- doc enhancements(tokuhirom)
- support $builder->select(order_by => {foo => 'DESC'}) form for skinny
-0.07
+0.07 2011-01-09
- allow subquery in from clause(makamaka)
-0.06
+0.06 2011-01-07
- allow hashref in new_select() method
-0.05
+0.05 2011-01-06
- doc fix(lestrrat)
-0.04
+0.04 2011-01-03
- add space each comma on using IN statement(zigorou)
-0.03
+0.03 2010-12-28
+
+ - 60ded52 (HEAD, zigorou/master, origin/master, master)
+ remove last new_line on as_sql(), add tests for different new args
+ (zigorou++)
+ - 1d54a09 add new_line opts to SQL::Maker, SQL::Maker::Select
+ (zigorou++)
+ - 0b84cd7 change the quote_char detection logic.
+ see http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
+
+0.02 2010-12-12
- - 60ded52 (HEAD, zigorou/master, origin/master, master) remove last new_line on as_sql(), add tests for different new args(zigorou++)
- - 1d54a09 add new_line opts to SQL::Maker, SQL::Maker::Select(zigorou++)
- - 0b84cd7 change the quote_char detection logic. see http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
+ - added new method 'new_select'.
+ - pod fix
-0.02
+0.01 2010-12-08
- - added new method 'new_select'.
- - pod fix
+ - original version
-0.01 Sat Nov 27 11:37:12 2010
- - original version
@@ -15,6 +15,7 @@ lib/SQL/Maker/Select.pm
lib/SQL/Maker/Select/Oracle.pm
lib/SQL/Maker/SelectSet.pm
lib/SQL/Maker/Util.pm
+minil.toml
t/00_compile.t
t/01_delete.t
t/01_insert.t
@@ -27,10 +28,12 @@ t/09_new_line.t
t/10_subquery.t
t/11_set.t
t/12_insert_empty.t
+t/13_strict.t
t/condition/01_where.t
t/condition/02_make_term.t
t/condition/03_add_raw.t
t/condition/04_compose_empty.t
+t/condition/05_empty_values.t
t/plugins/insert_multi/01_insert_multi.t
t/plugins/insert_on_duplicate/01_insert_on_duplicate.t
t/select/01_statement.t
@@ -4,8 +4,10 @@
"Tokuhiro Matsuno <tokuhirom AAJKLFJEF@ GMAIL COM>"
],
"dynamic_config" : 0,
- "generated_by" : "Minilla/v0.5.3",
- "license" : "perl_5",
+ "generated_by" : "Minilla/v2.1.1",
+ "license" : [
+ "perl_5"
+ ],
"meta-spec" : {
"url" : "http://search.cpan.org/perldoc?CPAN::Meta::Spec",
"version" : "2"
@@ -19,21 +21,21 @@
"share",
"eg",
"examples",
- "author"
+ "author",
+ "builder"
]
},
"prereqs" : {
"configure" : {
"requires" : {
- "CPAN::Meta" : "0",
- "CPAN::Meta::Prereqs" : "0",
- "Module::Build" : "0.38"
+ "Module::Build::Tiny" : "0.035"
}
},
"develop" : {
"requires" : {
"Test::CPAN::Meta" : "0",
- "Test::MinimumVersion" : "0.10108",
+ "Test::MinimumVersion::Fast" : "0.04",
+ "Test::PAUSE::Permissions" : "0.04",
"Test::Pod" : "1.41",
"Test::Spellunker" : "v0.2.7"
}
@@ -43,6 +45,7 @@
"Class::Accessor::Lite" : "0.05",
"DBI" : "0",
"Module::Load" : "0",
+ "SQL::QueryMaker" : "0",
"Scalar::Util" : "0",
"parent" : "0",
"perl" : "5.008001"
@@ -53,13 +56,16 @@
"Test::More" : "0.98",
"Test::Requires" : "0",
"Tie::IxHash" : "0"
+ },
+ "suggests" : {
+ "DateTime" : "0"
}
}
},
"provides" : {
"SQL::Maker" : {
"file" : "lib/SQL/Maker.pm",
- "version" : "1.12"
+ "version" : "1.20"
},
"SQL::Maker::Condition" : {
"file" : "lib/SQL/Maker/Condition.pm"
@@ -93,11 +99,12 @@
},
"homepage" : "https://github.com/tokuhirom/SQL-Maker",
"repository" : {
- "url" : "git://github.com/tokuhirom/SQL-Maker.git",
+ "url" : "git://github.com/tokuhirom/SQL-Maker",
"web" : "https://github.com/tokuhirom/SQL-Maker"
}
},
- "version" : "1.12",
+ "version" : "1.20",
+ "x_authority" : "cpan:TOKUHIROM",
"x_contributors" : [
"lestrrat <lestrrat+github@gmail.com>",
"makamaka <makamaka.donzoko@gmail.com>",
@@ -109,6 +116,12 @@
"Masahiro Chiba <chiba@everqueue.com>",
"Toshio Ito <debug.ito@gmail.com>",
"Masayuki Matsuki <y.songmu@gmail.com>",
- "tokuhirom <tokuhirom@gmail.com>"
+ "Neil Bowers <neil@bowers.com>",
+ "Gelu Lupas <gvl@cpan.org>",
+ "karupanerura <karupa@cpan.org>",
+ "soh335 <sugarbabe335@gmail.com>",
+ "pokutuna <popopopopokutuna@gmail.com>",
+ "Kazuho Oku <kazuhooku@gmail.com>",
+ "Tokuhiro Matsuno <tokuhirom@gmail.com>"
]
}
@@ -3,19 +3,17 @@ abstract: 'Yet another SQL builder'
author:
- 'Tokuhiro Matsuno <tokuhirom AAJKLFJEF@ GMAIL COM>'
build_requires:
- Test::More: 0.98
- Test::Requires: 0
- Tie::IxHash: 0
+ Test::More: '0.98'
+ Test::Requires: '0'
+ Tie::IxHash: '0'
configure_requires:
- CPAN::Meta: 0
- CPAN::Meta::Prereqs: 0
- Module::Build: 0.38
+ Module::Build::Tiny: '0.035'
dynamic_config: 0
-generated_by: 'Minilla/v0.5.3, CPAN::Meta::Converter version 2.130880'
+generated_by: 'Minilla/v2.1.1, CPAN::Meta::Converter version 2.141520'
license: perl
meta-spec:
url: http://module-build.sourceforge.net/META-spec-v1.4.html
- version: 1.4
+ version: '1.4'
name: SQL-Maker
no_index:
directory:
@@ -26,10 +24,11 @@ no_index:
- eg
- examples
- author
+ - builder
provides:
SQL::Maker:
file: lib/SQL/Maker.pm
- version: 1.12
+ version: '1.20'
SQL::Maker::Condition:
file: lib/SQL/Maker/Condition.pm
SQL::Maker::Plugin::InsertMulti:
@@ -47,17 +46,19 @@ provides:
SQL::Maker::Util:
file: lib/SQL/Maker/Util.pm
requires:
- Class::Accessor::Lite: 0.05
- DBI: 0
- Module::Load: 0
- Scalar::Util: 0
- parent: 0
- perl: 5.008001
+ Class::Accessor::Lite: '0.05'
+ DBI: '0'
+ Module::Load: '0'
+ SQL::QueryMaker: '0'
+ Scalar::Util: '0'
+ parent: '0'
+ perl: '5.008001'
resources:
bugtracker: https://github.com/tokuhirom/SQL-Maker/issues
homepage: https://github.com/tokuhirom/SQL-Maker
- repository: git://github.com/tokuhirom/SQL-Maker.git
-version: 1.12
+ repository: git://github.com/tokuhirom/SQL-Maker
+version: '1.20'
+x_authority: cpan:TOKUHIROM
x_contributors:
- 'lestrrat <lestrrat+github@gmail.com>'
- 'makamaka <makamaka.donzoko@gmail.com>'
@@ -69,4 +70,10 @@ x_contributors:
- 'Masahiro Chiba <chiba@everqueue.com>'
- 'Toshio Ito <debug.ito@gmail.com>'
- 'Masayuki Matsuki <y.songmu@gmail.com>'
- - 'tokuhirom <tokuhirom@gmail.com>'
+ - 'Neil Bowers <neil@bowers.com>'
+ - 'Gelu Lupas <gvl@cpan.org>'
+ - 'karupanerura <karupa@cpan.org>'
+ - 'soh335 <sugarbabe335@gmail.com>'
+ - 'pokutuna <popopopopokutuna@gmail.com>'
+ - 'Kazuho Oku <kazuhooku@gmail.com>'
+ - 'Tokuhiro Matsuno <tokuhirom@gmail.com>'
@@ -17,7 +17,7 @@ SQL::Maker - Yet another SQL builder
($sql, @binds) = $builder->insert($table, \%values, \%opt);
# DELETE
- ($sql, @binds) = $builder->delete($table, \%where);
+ ($sql, @binds) = $builder->delete($table, \%where, \%opt);
# UPDATE
($sql, @binds) = $builder->update($table, \%set, \%where);
@@ -25,15 +25,15 @@ SQL::Maker - Yet another SQL builder
# DESCRIPTION
-SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](http://search.cpan.org/perldoc?DBIx::Skinny)'s SQL generator.
+SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](https://metacpan.org/pod/DBIx::Skinny)'s SQL generator.
# METHODS
-- my $builder = SQL::Maker->new(%args);
+- `my $builder = SQL::Maker->new(%args);`
Create new instance of SQL::Maker.
- Attributes are following:
+ Attributes are the following:
- driver: Str
@@ -57,145 +57,184 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](http:
Default: '\\n'
-- my $select = $builder->new\_select(%args|\\%args);
+ - strict: Bool
- Create new instance of [SQL::Maker::Select](http://search.cpan.org/perldoc?SQL::Maker::Select) from the settings from __$builder__.
+ Whether or not the use of unblessed references are prohibited for defining the SQL expressions.
- This method returns instance of [SQL::Maker::Select](http://search.cpan.org/perldoc?SQL::Maker::Select).
+ In strict mode, all the expressions must be declared by using blessed references that export `as_sql` and `bind` methods like [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker).
-- my ($sql, @binds) = $builder->select($table|\\@tables, \\@fields, \\%where|\\@where|$where, \\%opt);
+ Default: undef
+
+- `my $select = $builder->new_select(%args|\%args);`
+
+ Create new instance of [SQL::Maker::Select](https://metacpan.org/pod/SQL::Maker::Select) using the settings from **$builder**.
+
+ This method returns an instance of [SQL::Maker::Select](https://metacpan.org/pod/SQL::Maker::Select).
+
+- `my ($sql, @binds) = $builder->select($table|\@tables, \@fields, \%where|\@where|$where, \%opt);`
my ($sql, @binds) = $builder->select('user', ['*'], {name => 'john'}, {order_by => 'user_id DESC'});
# =>
# SELECT * FROM `user` WHERE (`name` = ?) ORDER BY user_id DESC
# ['john']
- This method returns SQL string and bind variables for SELECT statement.
+ This method returns the SQL string and bind variables for a SELECT statement.
- - $table
- - \\@tables
+ - `$table`
+ - `\@tables`
+
+ Table name for the **FROM** clause as scalar or arrayref. You can specify the instance of **SQL::Maker::Select** for a sub-query.
- Table name for __FROM__ clause in scalar or arrayref. You can specify the instance of __SQL::Maker::Select__ for sub-query.
+ If you are using `$opt->{joins}` this should be _undef_ since it's passed via the first join.
- - \\@fields
+ - `\@fields`
This is a list for retrieving fields from database.
- Each element of the `@field` is a scalar or a scalar ref of the column name normally.
- If you want to specify alias of the field, you can use ArrayRef containing the pair of column
- and alias name (e.g. `['foo.id' => 'foo_id']`).
+ Each element of the `@fields` is normally a scalar or a scalar ref containing the column name.
+ If you want to specify an alias of the field, you can use an arrayref containing a pair
+ of column and alias names (e.g. `['foo.id' => 'foo_id']`).
- - \\%where
- - \\@where
- - $where
+ - `\%where`
+ - `\@where`
+ - `$where`
- where clause from hashref or arrayref via [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition), or [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition) object.
+ where clause from hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
- - \\%opt
+ - `\%opt`
- This is a options for SELECT statement
+ These are the options for the SELECT statement
- - $opt->{prefix}
+ - `$opt->{prefix}`
- This is a prefix for SELECT statement.
+ This is a prefix for the SELECT statement.
For example, you can provide the 'SELECT SQL\_CALC\_FOUND\_ROWS '. It's useful for MySQL.
Default Value: 'SELECT '
- - $opt->{limit}
+ - `$opt->{limit}`
+
+ This option adds a 'LIMIT $n' clause.
+
+ - `$opt->{offset}`
+
+ This option adds an 'OFFSET $n' clause.
+
+ - `$opt->{order_by}`
- This option makes 'LIMIT $n' clause.
+ This option adds an **ORDER BY** clause
- - $opt->{offset}
+ You can write it in any of the following forms:
- This option makes 'OFFSET $n' clause.
+ $builder->select(..., {order_by => 'foo DESC, bar ASC'});
+ $builder->select(..., {order_by => ['foo DESC', 'bar ASC']});
+ $builder->select(..., {order_by => {foo => 'DESC'}});
+ $builder->select(..., {order_by => [{foo => 'DESC'}, {bar => 'ASC'}]});
- - $opt->{order\_by}
+ - `$opt->{group_by}`
- This option makes __ORDER BY__ clause
+ This option adds a **GROUP BY** clause
- You can write it as following forms:
+ You can write it in any of the following forms:
- $builder->select(..., order_by => 'foo DESC, bar ASC');
- $builder->select(..., order_by => ['foo DESC', 'bar ASC']);
- $builder->select(..., order_by => {foo => 'DESC'});
- $builder->select(..., order_by => [{foo => 'DESC'}, {bar => 'ASC'}]);
+ $builder->select(..., {group_by => 'foo DESC, bar ASC'});
+ $builder->select(..., {group_by => ['foo DESC', 'bar ASC']});
+ $builder->select(..., {group_by => {foo => 'DESC'}});
+ $builder->select(..., {group_by => [{foo => 'DESC'}, {bar => 'ASC'}]});
- - $opt->{group\_by}
+ - `$opt->{having}`
- This option makes __GROUP BY__ clause
+ This option adds a HAVING clause
- You can write it as following forms:
+ - `$opt->{for_update}`
- $builder->select(..., group_by => 'foo DESC, bar ASC');
- $builder->select(..., group_by => ['foo DESC', 'bar ASC']);
- $builder->select(..., group_by => {foo => 'DESC'});
- $builder->select(..., group_by => [{foo => 'DESC'}, {bar => 'ASC'}]);
+ This option adds a 'FOR UPDATE" clause.
- - $opt->{having}
+ - `$opt->{joins}`
- This option makes HAVING clause
+ This option adds a 'JOIN' via [SQL::Maker::Select](https://metacpan.org/pod/SQL::Maker::Select).
- - $opt->{for\_update}
+ You can write it as follows:
- This option makes 'FOR UPDATE" clause.
+ $builder->select(undef, ..., {joins => [[user => {table => 'group', condition => 'user.gid = group.gid'}], ...]});
- - $opt->{joins}
+ - `$opt->{index_hint}`
- This option makes 'JOIN' via [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition).
+ This option adds an INDEX HINT like as 'USE INDEX' clause for MySQL via [SQL::Maker::Select](https://metacpan.org/pod/SQL::Maker::Select).
-- my ($sql, @binds) = $builder->insert($table, \\%values|\\@values, \\%opt);
+ You can write it as follows:
+
+ $builder->select(..., { index_hint => 'foo' });
+ $builder->select(..., { index_hint => ['foo', 'bar'] });
+ $builder->select(..., { index_hint => { list => 'foo' });
+ $builder->select(..., { index_hint => { type => 'FORCE', list => ['foo', 'bar'] });
+
+- `my ($sql, @binds) = $builder->insert($table, \%values|\@values, \%opt);`
my ($sql, @binds) = $builder->insert(user => {name => 'john'});
# =>
# INSERT INTO `user` (`name`) VALUES (?)
# ['john']
- Generate INSERT query.
+ Generate an INSERT query.
- - $table
+ - `$table`
Table name in scalar.
- - \\%values
+ - `\%values`
- This is a values for INSERT statement.
+ These are the values for the INSERT statement.
- - \\%opt
+ - `\%opt`
- This is a options for INSERT statement
+ These are the options for the INSERT statement
- - $opt->{prefix}
+ - `$opt->{prefix}`
- This is a prefix for INSERT statement.
+ This is a prefix for the INSERT statement.
For example, you can provide 'INSERT IGNORE INTO' for MySQL.
Default Value: 'INSERT INTO'
-- my ($sql, @binds) = $builder->delete($table, \\%where|\\@where|$where);
+- `my ($sql, @binds) = $builder->delete($table, \%where|\@where|$where, \%opt);`
my ($sql, @binds) = $builder->delete($table, \%where);
# =>
# DELETE FROM `user` WHERE (`name` = ?)
# ['john']
- Generate DELETE query.
+ Generate a DELETE query.
- - $table
+ - `$table`
Table name in scalar.
- - \\%where
- - \\@where
- - $where
+ - `\%where`
+ - `\@where`
+ - `$where`
+
+ where clause from hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
+
+ - `\%opt`
+
+ These are the options for the DELETE statement
+
+ - `$opt->{using}`
+
+ This option adds a USING clause. It takes a scalar or an arrayref of table names as argument:
- where clause from hashref or arrayref via [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition), or [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition) object.
+ my ($sql, $binds) = $bulder->delete($table, \%where, { using => 'group' });
+ # =>
+ # DELETE FROM `user` USING `group` WHERE (`group`.`name` = ?)
+ # ['doe']
+ $bulder->delete(..., { using => ['bar', 'qux'] });
-- my ($sql, @binds) = $builder->update($table, \\%set|@set, \\%where|\\@where|$where);
+- `my ($sql, @binds) = $builder->update($table, \%set|@set, \%where|\@where|$where);`
- Generate UPDATE query.
+ Generate a UPDATE query.
my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3});
# =>
@@ -214,21 +253,21 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](http:
- \\@where
- $where
- where clause from hashref or arrayref via [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition), or [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition) object.
+ where clause from a hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
-- $builder->new\_condition()
+- `$builder->new_condition()`
- Create new [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition) object from ` $builder ` settings.
+ Create new [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object from ` $builder ` settings.
-- my ($sql, @binds) = $builder->where(\\%where)
-- my ($sql, @binds) = $builder->where(\\@where)
-- my ($sql, @binds) = $builder->where(\\@where)
+- `my ($sql, @binds) = $builder->where(\%where)`
+- `my ($sql, @binds) = $builder->where(\@where)`
+- `my ($sql, @binds) = $builder->where(\@where)`
- Where clause from hashref or arrayref via [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition), or [SQL::Maker::Condition](http://search.cpan.org/perldoc?SQL::Maker::Condition) object.
+ Where clause from a hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
# PLUGINS
-SQL::Maker supports plugin system. Write the code like following.
+SQL::Maker features a plugin system. Write the code as follows:
package My::SQL::Maker;
use parent qw/SQL::Maker/;
@@ -236,11 +275,11 @@ SQL::Maker supports plugin system. Write the code like following.
# FAQ
-- Why don't you use SQL::Abstract?
+- Why don't you use SQL::Abstract?
- I need more extensible one.
+ I need a more extensible one.
- So, this module contains [SQL::Maker::Select](http://search.cpan.org/perldoc?SQL::Maker::Select), the extensible __SELECT__ clause object.
+ So, this module contains [SQL::Maker::Select](https://metacpan.org/pod/SQL::Maker::Select), the extensible **SELECT** clause object.
# AUTHOR
@@ -248,9 +287,10 @@ Tokuhiro Matsuno <tokuhirom AAJKLFJEF@ GMAIL COM>
# SEE ALSO
-[SQL::Abstract](http://search.cpan.org/perldoc?SQL::Abstract)
+[SQL::Abstract](https://metacpan.org/pod/SQL::Abstract)
+[SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker)
-Whole code was taken from [DBIx::Skinny](http://search.cpan.org/perldoc?DBIx::Skinny) by nekokak++.
+The whole code was taken from [DBIx::Skinny](https://metacpan.org/pod/DBIx::Skinny) by nekokak++.
# LICENSE
@@ -5,9 +5,11 @@ requires 'DBI';
requires 'Module::Load';
requires 'parent';
requires 'Scalar::Util';
+requires 'SQL::QueryMaker';
on test => sub {
requires 'Test::More', '0.98';
requires 'Test::Requires';
+ suggests 'DateTime';
requires 'Tie::IxHash';
};
@@ -2,6 +2,7 @@ package SQL::Maker::Condition;
use strict;
use warnings;
use utf8;
+use Scalar::Util ();
use SQL::Maker::Util;
use overload
'&' => sub { $_[0]->compose_and($_[1]) },
@@ -18,12 +19,23 @@ sub _quote {
sub new {
my $class = shift;
my %args = @_==1 ? %{$_[0]} : @_;
- bless {sql => [], bind => [], %args}, $class;
+ bless {sql => [], bind => [], strict => 0, %args}, $class;
}
sub _make_term {
my ($self, $col, $val) = @_;
+ if (Scalar::Util::blessed($val)) {
+ if ($val->can('as_sql')) {
+ return ($val->as_sql($col, sub { $self->_quote(@_) }), [ $val->bind() ]);
+ } else {
+ return ($self->_quote($col) . " = ?", [ $val ]);
+ }
+ }
+
+ Carp::croak("cannot pass in an unblessed ref as an argument in strict mode")
+ if ref($val) && $self->{strict};
+
if ( ref($val) eq 'ARRAY' ) {
# make_term(foo => {-and => [1,2,3]}) => (foo = 1) AND (foo = 2) AND (foo = 3)
if ( ref $val->[0] or ( ( $val->[0] || '' ) eq '-and' ) ) {
@@ -46,27 +58,14 @@ sub _make_term {
}
else {
# make_term(foo => [1,2,3]) => foo IN (1,2,3)
- my $term = $self->_quote($col) . " IN (" . substr('?, ' x scalar(@$val), 0, -2) . ')';
- return ($term, $val);
+ return $self->_make_term_by_arrayref($col, 'IN', $val);
}
}
elsif ( ref($val) eq 'HASH' ) {
my ( $op, $v ) = ( %{$val} );
$op = uc($op);
if ( ( $op eq 'IN' || $op eq 'NOT IN' ) && ref($v) eq 'ARRAY' ) {
- if (@$v == 0) {
- if ($op eq 'IN') {
- # make_term(foo => +{'IN' => []}) => 0=1
- return ('0=1', []);
- } else {
- # make_term(foo => +{'NOT IN' => []}) => 1=1
- return ('1=1', []);
- }
- } else {
- # make_term(foo => +{ 'IN', [1,2,3] }) => foo IN (1,2,3)
- my $term = $self->_quote($col) . " $op (" . join( ', ', ('?') x scalar @$v ) . ')';
- return ($term, $v);
- }
+ return $self->_make_term_by_arrayref($col, $op, $v);
}
elsif ( ( $op eq 'IN' || $op eq 'NOT IN' ) && ref($v) eq 'REF' ) {
# make_term(foo => +{ 'IN', \['SELECT foo FROM bar'] }) => foo IN (SELECT foo FROM bar)
@@ -108,6 +107,23 @@ sub _make_term {
}
}
+sub _make_term_by_arrayref {
+ my ($self, $col, $op, $v) = @_;
+ if (@$v == 0) {
+ if ($op eq 'IN') {
+ # make_term(foo => +{'IN' => []}) => 0=1
+ return ('0=1', []);
+ } else {
+ # make_term(foo => +{'NOT IN' => []}) => 1=1
+ return ('1=1', []);
+ }
+ } else {
+ # make_term(foo => +{ 'IN', [1,2,3] }) => foo IN (1,2,3)
+ my $term = $self->_quote($col) . " $op (" . join( ', ', ('?') x scalar @$v ) . ')';
+ return ($term, $v);
+ }
+}
+
sub add {
my ( $self, $col, $val ) = @_;
@@ -310,6 +326,10 @@ Here is a cheat sheet for conditions.
OUT QUERY: '1=1'
OUT BIND: ()
+ IN: ['foo_id', [123,sql_type(\3, SQL_INTEGER)]]
+ OUT QUERY: '`foo_id` IN (?, ?)'
+ OUT BIND: (123, sql_type(\3, SQL_INTEGER))
+
IN: ['foo_id', sql_type(\3, SQL_INTEGER)]
OUT QUERY: '`foo_id` = ?'
OUT BIND: sql_type(\3, SQL_INTEGER)
@@ -318,6 +338,80 @@ Here is a cheat sheet for conditions.
OUT QUERY: '`created_on` > DATE_SUB(NOW(), INTERVAL 1 DAY)'
OUT BIND:
+It is also possible to use the functions exported by C<SQL::QueryMaker> to define the conditions.
+
+ IN: ['foo' => sql_in(['bar','baz'])]
+ OUT QUERY: '`foo` IN (?,?)'
+ OUT BIND: ('bar','baz')
+
+ IN: ['foo' => sql_lt(3)]
+ OUT QUERY: '`foo` < ?'
+ OUT BIND: (3)
+
+ IN: ['foo' => sql_not_in(['bar','baz'])]
+ OUT QUERY: '`foo` NOT IN (?,?)'
+ OUT BIND: ('bar','baz')
+
+ IN: ['foo' => sql_ne('bar')]
+ OUT QUERY: '`foo` != ?'
+ OUT BIND: ('bar')
+
+ IN: ['foo' => sql_is_not_null()]
+ OUT QUERY: '`foo` IS NOT NULL'
+ OUT BIND: ()
+
+ IN: ['foo' => sql_between('1','2')]
+ OUT QUERY: '`foo` BETWEEN ? AND ?'
+ OUT BIND: ('1','2')
+
+ IN: ['foo' => sql_like('xaic%')]
+ OUT QUERY: '`foo` LIKE ?'
+ OUT BIND: ('xaic%')
+
+ IN: ['foo' => sql_or([sql_gt('bar'), sql_lt('baz')])]
+ OUT QUERY: '(`foo` > ?) OR (`foo` < ?)'
+ OUT BIND: ('bar','baz')
+
+ IN: ['foo' => sql_and([sql_gt('bar'), sql_lt('baz')])]
+ OUT QUERY: '(`foo` > ?) AND (`foo` < ?)'
+ OUT BIND: ('bar','baz')
+
+ IN: ['foo_id' => sql_op('IN (SELECT foo_id FROM bar WHERE t=?)',[44])]
+ OUT QUERY: '`foo_id` IN (SELECT foo_id FROM bar WHERE t=?)'
+ OUT BIND: ('44')
+
+ IN: ['foo_id' => sql_in([sql_raw('SELECT foo_id FROM bar WHERE t=?',44)])]
+ OUT QUERY: '`foo_id` IN ((SELECT foo_id FROM bar WHERE t=?))'
+ OUT BIND: ('44')
+
+ IN: ['foo_id', => sql_op('MATCH (@) AGAINST (?)',['apples'])]
+ OUT QUERY: 'MATCH (`foo_id`) AGAINST (?)'
+ OUT BIND: ('apples')
+
+ IN: ['foo_id',undef]
+ OUT QUERY: '`foo_id` IS NULL'
+ OUT BIND: ()
+
+ IN: ['foo_id',sql_in([])]
+ OUT QUERY: '0=1'
+ OUT BIND: ()
+
+ IN: ['foo_id',sql_not_in([])]
+ OUT QUERY: '1=1'
+ OUT BIND: ()
+
+ IN: ['foo_id', sql_type(\3, SQL_INTEGER)]
+ OUT QUERY: '`foo_id` = ?'
+ OUT BIND: sql_type(\3, SQL_INTEGER)
+
+ IN: ['foo_id', sql_in([sql_type(\3, SQL_INTEGER)])]
+ OUT QUERY: '`foo_id` IN (?)'
+ OUT BIND: sql_type(\3, SQL_INTEGER)
+
+ IN: ['created_on', sql_gt(sql_raw('DATE_SUB(NOW(), INTERVAL 1 DAY)')) ]
+ OUT QUERY: '`created_on` > DATE_SUB(NOW(), INTERVAL 1 DAY)'
+ OUT BIND:
+
=head1 SEE ALSO
L<SQL::Maker>
@@ -2,6 +2,7 @@ package SQL::Maker::Plugin::InsertMulti;
use strict;
use warnings;
use utf8;
+use Scalar::Util ();
our @EXPORT = qw/insert_multi/;
@@ -37,20 +38,32 @@ sub insert_multi {
for my $value ( @values ) {
my @value_stmt;
for my $val (@$value) {
- if (ref $val eq 'SCALAR') {
- # $val = \'NOW()'
- push @value_stmt, $$val;
- }
- elsif (ref $val eq 'REF' && ref $$val eq 'ARRAY') {
- # $val = \['UNIX_TIMESTAMP(?)', '2011-04-20 00:30:00']
- my ( $stmt, @sub_bind ) = @{$$val};
- push @value_stmt, $stmt;
- push @bind, @sub_bind;
- }
- else {
- # normal values
- push @value_stmt, '?';
- push @bind, $val;
+ if (Scalar::Util::blessed($val)) {
+ if ($val->can('as_sql')) {
+ push @value_stmt, $val->as_sql(undef, sub { $self->_quote($_[0]) });
+ push @bind, $val->bind();
+ } else {
+ push @value_stmt, '?';
+ push @bind, $val;
+ }
+ } else {
+ Carp::croak("cannot pass in an unblessed ref as an argument in strict mode")
+ if ref($val) && $self->strict;
+ if (! $self->strict && ref $val eq 'SCALAR') {
+ # $val = \'NOW()'
+ push @value_stmt, $$val;
+ }
+ elsif (! $self->strict && ref $val eq 'REF' && ref $$val eq 'ARRAY') {
+ # $val = \['UNIX_TIMESTAMP(?)', '2011-04-20 00:30:00']
+ my ( $stmt, @sub_bind ) = @{$$val};
+ push @value_stmt, $stmt;
+ push @bind, @sub_bind;
+ }
+ else {
+ # normal values
+ push @value_stmt, '?';
+ push @bind, $val;
+ }
}
}
$sql .= '(' . join(', ', @value_stmt) . '),' . $self->new_line;
@@ -20,6 +20,22 @@ sub new {
sub value_ref { $_[0]->{value_ref} }
sub type { $_[0]->{type} }
+sub as_sql {
+ my ($self, $supplied_colname, $quote_cb) = @_;
+ my $stmt;
+ if (defined $supplied_colname) {
+ $stmt = $quote_cb->($supplied_colname) . ' = ?';
+ } else {
+ $stmt = '?';
+ }
+ return $stmt;
+}
+
+sub bind {
+ my $self = shift;
+ return $self;
+}
+
1;
__END__
@@ -8,7 +8,7 @@ use Class::Accessor::Lite (
new => 0,
wo => [qw/distinct for_update/],
rw => [qw/prefix/],
- ro => [qw/quote_char name_sep new_line/],
+ ro => [qw/quote_char name_sep new_line strict/],
);
use Scalar::Util ();
@@ -45,6 +45,7 @@ sub new {
order_by => +[],
prefix => 'SELECT ',
new_line => "\n",
+ strict => 0,
%args
}, $class;
@@ -57,6 +58,7 @@ sub new_condition {
SQL::Maker::Condition->new(
quote_char => $self->{quote_char},
name_sep => $self->{name_sep},
+ strict => $self->{strict},
);
}
@@ -110,10 +112,23 @@ sub add_join {
sub add_index_hint {
my ($self, $table, $hint) = @_;
+ my ($type, $list);
+
+ if (ref $hint eq 'HASH') {
+ # { type => '...', list => ['foo'] }
+ $type = $hint->{type} || 'USE';
+ $list = ref($hint->{list}) eq 'ARRAY' ? $hint->{list} : [ $hint->{list} ];
+ } else {
+ # ['foo, 'bar'] or just 'foo'
+ $type = 'USE';
+ $list = ref($hint) eq 'ARRAY' ? $hint : [ $hint ];
+ }
+
$self->{index_hint}->{$table} = {
- type => $hint->{type} || 'USE',
- list => ref($hint->{list}) eq 'ARRAY' ? $hint->{list} : [ $hint->{list} ],
+ type => $type,
+ list => $list,
};
+
return $self;
}
@@ -153,7 +168,8 @@ sub as_sql {
my ($table, $join) = map { $j->{$_} } qw( table joins );
$table = $self->_add_index_hint(@$table); ## index hint handling
$sql .= $table unless $initial_table_written++;
- $sql .= ' ' . uc($join->{type}) . ' JOIN ' . $self->_quote($join->{table});
+ $sql .= ' ' . uc($join->{type}) if $join->{type};
+ $sql .= ' JOIN ' . $self->_quote($join->{table});
$sql .= ' ' . $self->_quote($join->{alias}) if $join->{alias};
if ( defined $join->{condition} ) {
@@ -188,7 +204,7 @@ sub as_sql {
$sql .= $self->as_sql_having if $self->{having};
$sql .= $self->as_sql_order_by if $self->{order_by};
- $sql .= $self->as_sql_limit if $self->{limit};
+ $sql .= $self->as_sql_limit if defined $self->{limit};
$sql .= $self->as_sql_for_update;
$sql =~ s/${new_line}+$//;
@@ -198,8 +214,10 @@ sub as_sql {
sub as_sql_limit {
my $self = shift;
- my $n = $self->{limit} or
- return '';
+
+ my $n = $self->{limit};
+ return '' unless defined $n;
+
die "Non-numerics in limit clause ($n)" if $n =~ /\D/;
return sprintf "LIMIT %d%s" . $self->new_line, $n,
($self->{offset} ? " OFFSET " . int($self->{offset}) : "");
@@ -345,7 +363,7 @@ Render the SQL string.
=item C<< my @bind = $stmt->bind(); >>
-Get bind variables.
+Get the bind variables.
=item C<< $stmt->add_select('*') >>
@@ -353,11 +371,11 @@ Get bind variables.
=item C<< $stmt->add_select(\'COUNT(*)' => 'cnt') >>
-Add new select term. It's quote automatically.
+Add a new select term. It's automatically quoted.
=item C<< $stmt->add_from($table :Str | $select :SQL::Maker::Select) : SQL::Maker::Select >>
-Add new from clause. You can specify the table name or instance of L<SQL::Maker::Select> for sub-query.
+Add a new FROM clause. You can specify the table name or an instance of L<SQL::Maker::Select> for a sub-query.
I<Return:> $stmt itself.
@@ -367,7 +385,8 @@ I<Return:> $stmt itself.
=item C<< $stmt->add_join(user => {type => 'inner', table => 'config', condition => ['user_id']}); >>
-Add new JOIN clause. If you pass arrayref for 'condition' then it uses 'USING'.
+Add a new JOIN clause. If you pass an arrayref for 'condition' then it uses 'USING'. If 'type' is omitted
+it falls back to plain JOIN.
my $stmt = SQL::Maker::Select->new();
$stmt->add_join(
@@ -421,6 +440,10 @@ Add new JOIN clause. If you pass arrayref for 'condition' then it uses 'USING'.
=item C<< $stmt->add_index_hint(foo => {type => 'USE', list => ['index_hint']}); >>
+=item C<< $stmt->add_index_hint(foo => 'index_hint'); >>
+
+=item C<< $stmt->add_index_hint(foo => ['index_hint']); >>
+
my $stmt = SQL::Maker::Select->new();
$stmt->add_select('name');
$stmt->add_from('user');
@@ -430,7 +453,7 @@ Add new JOIN clause. If you pass arrayref for 'condition' then it uses 'USING'.
=item C<< $stmt->add_where('foo_id' => 'bar'); >>
-Add new where clause.
+Add a new WHERE clause.
my $stmt = SQL::Maker::Select->new()
->add_select('c')
@@ -442,7 +465,7 @@ Add new where clause.
=item C<< $stmt->add_where_raw('id = ?', [1]) >>
-Add new where clause from raw placeholder string and bind variables.
+Add a new WHERE clause from raw placeholder string and bind variables.
my $stmt = SQL::Maker::Select->new()
->add_select('c')
@@ -455,7 +478,7 @@ Add new where clause from raw placeholder string and bind variables.
=item C<< $stmt->set_where($condition) >>
-Set the where clause.
+Set the WHERE clause.
$condition should be instance of L<SQL::Maker::Condition>.
@@ -474,7 +497,7 @@ $condition should be instance of L<SQL::Maker::Condition>.
=item C<< $stmt->add_order_by({'foo' => 'DESC'}); >>
-Add new order by clause.
+Add a new ORDER BY clause.
my $stmt = SQL::Maker::Select->new()
->add_select('c')
@@ -486,7 +509,7 @@ Add new order by clause.
=item C<< $stmt->add_group_by('foo'); >>
-Add new GROUP BY clause.
+Add a new GROUP BY clause.
my $stmt = SQL::Maker::Select->new()
->add_select('c')
@@ -502,9 +525,23 @@ Add new GROUP BY clause.
->as_sql();
# => "SELECT c FROM foo GROUP BY id DESC"
+=item C<< $stmt->limit(30) >>
+
+=item C<< $stmt->offset(5) >>
+
+Add LIMIT and OFFSET.
+
+ my $stmt = SQL::Maker::Select->new()
+ ->add_select('c')
+ ->add_from('foo')
+ ->limit(30)
+ ->offset(5)
+ ->as_sql();
+ # => "SELECT c FROM foo LIMIT 30 OFFSET 5"
+
=item C<< $stmt->add_having(cnt => 2) >>
-Add having clause
+Add a HAVING clause.
my $stmt = SQL::Maker::Select->new()
->add_from('foo')
@@ -2,9 +2,9 @@ package SQL::Maker;
use strict;
use warnings;
use 5.008001;
-our $VERSION = '1.12';
+our $VERSION = '1.20';
use Class::Accessor::Lite 0.05 (
- ro => [qw/quote_char name_sep new_line driver select_class/],
+ ro => [qw/quote_char name_sep new_line strict driver select_class/],
);
use Carp ();
@@ -47,6 +47,7 @@ sub new {
return bless {
name_sep => '.',
new_line => "\n",
+ strict => 0,
%args
}, $class;
}
@@ -57,6 +58,7 @@ sub new_condition {
SQL::Maker::Condition->new(
quote_char => $self->{quote_char},
name_sep => $self->{name_sep},
+ strict => $self->{strict},
);
}
@@ -68,6 +70,7 @@ sub new_select {
name_sep => $self->name_sep,
quote_char => $self->quote_char,
new_line => $self->new_line,
+ strict => $self->strict,
%args,
);
}
@@ -84,20 +87,32 @@ sub insert {
@values = ref $values eq 'HASH' ? %$values : @$values;
while (my ($col, $val) = splice(@values, 0, 2)) {
push @quoted_columns, $self->_quote($col);
- if (ref($val) eq 'SCALAR') {
- # $builder->insert(foo => { created_on => \"NOW()" });
- push @columns, $$val;
- }
- elsif (ref($val) eq 'REF' && ref($$val) eq 'ARRAY') {
- # $builder->insert( foo => \[ 'UNIX_TIMESTAMP(?)', '2011-04-12 00:34:12' ] );
- my ( $stmt, @sub_bind ) = @{$$val};
- push @columns, $stmt;
- push @bind_columns, @sub_bind;
- }
- else {
- # normal values
- push @columns, '?';
- push @bind_columns, $val;
+ if (Scalar::Util::blessed($val)) {
+ if ($val->can('as_sql')) {
+ push @columns, $val->as_sql(undef, sub { $self->_quote($_[0]) });
+ push @bind_columns, $val->bind();
+ } else {
+ push @columns, '?';
+ push @bind_columns, $val;
+ }
+ } else {
+ Carp::croak("cannot pass in an unblessed ref as an argument in strict mode")
+ if ref($val) && $self->strict;
+ if (ref($val) eq 'SCALAR') {
+ # $builder->insert(foo => { created_on => \"NOW()" });
+ push @columns, $$val;
+ }
+ elsif (ref($val) eq 'REF' && ref($$val) eq 'ARRAY') {
+ # $builder->insert( foo => \[ 'UNIX_TIMESTAMP(?)', '2011-04-12 00:34:12' ] );
+ my ( $stmt, @sub_bind ) = @{$$val};
+ push @columns, $stmt;
+ push @bind_columns, @sub_bind;
+ }
+ else {
+ # normal values
+ push @columns, '?';
+ push @bind_columns, $val;
+ }
}
}
@@ -122,11 +137,19 @@ sub _quote {
}
sub delete {
- my ($self, $table, $where) = @_;
+ my ($self, $table, $where, $opt) = @_;
my $w = $self->_make_where_clause($where);
my $quoted_table = $self->_quote($table);
- my $sql = "DELETE FROM $quoted_table" . $w->[0];
+ my $sql = "DELETE FROM $quoted_table";
+ if ($opt->{using}) {
+ # $bulder->delete('foo', \%where, { using => 'bar' });
+ # $bulder->delete('foo', \%where, { using => ['bar', 'qux'] });
+ my $tables = ref($opt->{using}) eq 'ARRAY' ? $opt->{using} : [$opt->{using}];
+ my $using = join(', ', map { $self->_quote($_) } @$tables);
+ $sql .= " USING " . $using;
+ }
+ $sql .= $w->[0];
return ($sql, @{$w->[1]});
}
@@ -151,20 +174,32 @@ sub make_set_clause {
my @args = ref $args eq 'HASH' ? %$args : @$args;
while (my ($col, $val) = splice @args, 0, 2) {
my $quoted_col = $self->_quote($col);
- if (ref $val eq 'SCALAR') {
- # $builder->update(foo => { created_on => \"NOW()" });
- push @columns, "$quoted_col = " . $$val;
- }
- elsif ( ref $val eq 'REF' && ref $$val eq 'ARRAY' ) {
- # $builder->update( foo => \[ 'VALUES(foo) + ?', 10 ] );
- my ( $stmt, @sub_bind ) = @{$$val};
- push @columns, "$quoted_col = " . $stmt;
- push @bind_columns, @sub_bind;
- }
- else {
- # normal values
- push @columns, "$quoted_col = ?";
- push @bind_columns, $val;
+ if (Scalar::Util::blessed($val)) {
+ if ($val->can('as_sql')) {
+ push @columns, "$quoted_col = " . $val->as_sql(undef, sub { $self->_quote($_[0]) });
+ push @bind_columns, $val->bind();
+ } else {
+ push @columns, "$quoted_col = ?";
+ push @bind_columns, $val;
+ }
+ } else {
+ Carp::croak("cannot pass in an unblessed ref as an argument in strict mode")
+ if ref($val) && $self->strict;
+ if (ref $val eq 'SCALAR') {
+ # $builder->update(foo => { created_on => \"NOW()" });
+ push @columns, "$quoted_col = " . $$val;
+ }
+ elsif (ref $val eq 'REF' && ref $$val eq 'ARRAY' ) {
+ # $builder->update( foo => \[ 'VALUES(foo) + ?', 10 ] );
+ my ( $stmt, @sub_bind ) = @{$$val};
+ push @columns, "$quoted_col = " . $stmt;
+ push @bind_columns, @sub_bind;
+ }
+ else {
+ # normal values
+ push @columns, "$quoted_col = ?";
+ push @bind_columns, $val;
+ }
}
}
return (\@columns, \@bind_columns);
@@ -198,7 +233,7 @@ sub _make_where_clause {
return ['', []] unless $where;
my $w = $self->_make_where_condition($where);
- my $sql = $w->as_sql(1);
+ my $sql = $w->as_sql();
return [$sql ? " WHERE $sql" : '', [$w->bind]];
}
@@ -283,8 +318,11 @@ sub select_query {
$stmt->add_group_by(\$o);
}
}
+ if (my $o = $opt->{index_hint}) {
+ $stmt->add_index_hint($table, $o);
+ }
- $stmt->limit( $opt->{limit} ) if $opt->{limit};
+ $stmt->limit( $opt->{limit} ) if defined $opt->{limit};
$stmt->offset( $opt->{offset} ) if $opt->{offset};
if (my $terms = $opt->{having}) {
@@ -324,7 +362,7 @@ SQL::Maker - Yet another SQL builder
($sql, @binds) = $builder->insert($table, \%values, \%opt);
# DELETE
- ($sql, @binds) = $builder->delete($table, \%where);
+ ($sql, @binds) = $builder->delete($table, \%where, \%opt);
# UPDATE
($sql, @binds) = $builder->update($table, \%set, \%where);
@@ -338,11 +376,11 @@ SQL::Maker is yet another SQL builder class. It is based on L<DBIx::Skinny>'s SQ
=over 4
-=item my $builder = SQL::Maker->new(%args);
+=item C<< my $builder = SQL::Maker->new(%args); >>
Create new instance of SQL::Maker.
-Attributes are following:
+Attributes are the following:
=over 4
@@ -368,135 +406,160 @@ This is the character that separates a part of statements.
Default: '\n'
+=item strict: Bool
+
+Whether or not the use of unblessed references are prohibited for defining the SQL expressions.
+
+In strict mode, all the expressions must be declared by using blessed references that export C<as_sql> and C<bind> methods like L<SQL::QueryMaker>.
+
+Default: undef
+
=back
-=item my $select = $builder->new_select(%args|\%args);
+=item C<< my $select = $builder->new_select(%args|\%args); >>
-Create new instance of L<SQL::Maker::Select> from the settings from B<$builder>.
+Create new instance of L<SQL::Maker::Select> using the settings from B<$builder>.
-This method returns instance of L<SQL::Maker::Select>.
+This method returns an instance of L<SQL::Maker::Select>.
-=item my ($sql, @binds) = $builder->select($table|\@tables, \@fields, \%where|\@where|$where, \%opt);
+=item C<< my ($sql, @binds) = $builder->select($table|\@tables, \@fields, \%where|\@where|$where, \%opt); >>
my ($sql, @binds) = $builder->select('user', ['*'], {name => 'john'}, {order_by => 'user_id DESC'});
# =>
# SELECT * FROM `user` WHERE (`name` = ?) ORDER BY user_id DESC
# ['john']
-This method returns SQL string and bind variables for SELECT statement.
+This method returns the SQL string and bind variables for a SELECT statement.
=over 4
-=item $table
+=item C<< $table >>
+
+=item C<< \@tables >>
-=item \@tables
+Table name for the B<FROM> clause as scalar or arrayref. You can specify the instance of B<SQL::Maker::Select> for a sub-query.
-Table name for B<FROM> clause in scalar or arrayref. You can specify the instance of B<SQL::Maker::Select> for sub-query.
+If you are using C<< $opt->{joins} >> this should be I<< undef >> since it's passed via the first join.
-=item \@fields
+=item C<< \@fields >>
This is a list for retrieving fields from database.
-Each element of the C<@field> is a scalar or a scalar ref of the column name normally.
-If you want to specify alias of the field, you can use ArrayRef containing the pair of column
-and alias name (e.g. C<< ['foo.id' => 'foo_id'] >>).
+Each element of the C<@fields> is normally a scalar or a scalar ref containing the column name.
+If you want to specify an alias of the field, you can use an arrayref containing a pair
+of column and alias names (e.g. C<< ['foo.id' => 'foo_id'] >>).
-=item \%where
+=item C<< \%where >>
-=item \@where
+=item C<< \@where >>
-=item $where
+=item C<< $where >>
where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
-=item \%opt
+=item C<< \%opt >>
-This is a options for SELECT statement
+These are the options for the SELECT statement
=over 4
-=item $opt->{prefix}
+=item C<< $opt->{prefix} >>
-This is a prefix for SELECT statement.
+This is a prefix for the SELECT statement.
For example, you can provide the 'SELECT SQL_CALC_FOUND_ROWS '. It's useful for MySQL.
Default Value: 'SELECT '
-=item $opt->{limit}
+=item C<< $opt->{limit} >>
+
+This option adds a 'LIMIT $n' clause.
+
+=item C<< $opt->{offset} >>
+
+This option adds an 'OFFSET $n' clause.
+
+=item C<< $opt->{order_by} >>
-This option makes 'LIMIT $n' clause.
+This option adds an B<ORDER BY> clause
-=item $opt->{offset}
+You can write it in any of the following forms:
-This option makes 'OFFSET $n' clause.
+ $builder->select(..., {order_by => 'foo DESC, bar ASC'});
+ $builder->select(..., {order_by => ['foo DESC', 'bar ASC']});
+ $builder->select(..., {order_by => {foo => 'DESC'}});
+ $builder->select(..., {order_by => [{foo => 'DESC'}, {bar => 'ASC'}]});
-=item $opt->{order_by}
+=item C<< $opt->{group_by} >>
-This option makes B<ORDER BY> clause
+This option adds a B<GROUP BY> clause
-You can write it as following forms:
+You can write it in any of the following forms:
- $builder->select(..., order_by => 'foo DESC, bar ASC');
- $builder->select(..., order_by => ['foo DESC', 'bar ASC']);
- $builder->select(..., order_by => {foo => 'DESC'});
- $builder->select(..., order_by => [{foo => 'DESC'}, {bar => 'ASC'}]);
+ $builder->select(..., {group_by => 'foo DESC, bar ASC'});
+ $builder->select(..., {group_by => ['foo DESC', 'bar ASC']});
+ $builder->select(..., {group_by => {foo => 'DESC'}});
+ $builder->select(..., {group_by => [{foo => 'DESC'}, {bar => 'ASC'}]});
-=item $opt->{group_by}
+=item C<< $opt->{having} >>
-This option makes B<GROUP BY> clause
+This option adds a HAVING clause
-You can write it as following forms:
+=item C<< $opt->{for_update} >>
- $builder->select(..., group_by => 'foo DESC, bar ASC');
- $builder->select(..., group_by => ['foo DESC', 'bar ASC']);
- $builder->select(..., group_by => {foo => 'DESC'});
- $builder->select(..., group_by => [{foo => 'DESC'}, {bar => 'ASC'}]);
+This option adds a 'FOR UPDATE" clause.
-=item $opt->{having}
+=item C<< $opt->{joins} >>
-This option makes HAVING clause
+This option adds a 'JOIN' via L<SQL::Maker::Select>.
-=item $opt->{for_update}
+You can write it as follows:
-This option makes 'FOR UPDATE" clause.
+ $builder->select(undef, ..., {joins => [[user => {table => 'group', condition => 'user.gid = group.gid'}], ...]});
-=item $opt->{joins}
+=item C<< $opt->{index_hint} >>
-This option makes 'JOIN' via L<SQL::Maker::Condition>.
+This option adds an INDEX HINT like as 'USE INDEX' clause for MySQL via L<SQL::Maker::Select>.
+
+You can write it as follows:
+
+ $builder->select(..., { index_hint => 'foo' });
+ $builder->select(..., { index_hint => ['foo', 'bar'] });
+ $builder->select(..., { index_hint => { list => 'foo' });
+ $builder->select(..., { index_hint => { type => 'FORCE', list => ['foo', 'bar'] });
=back
=back
-=item my ($sql, @binds) = $builder->insert($table, \%values|\@values, \%opt);
+=item C<< my ($sql, @binds) = $builder->insert($table, \%values|\@values, \%opt); >>
my ($sql, @binds) = $builder->insert(user => {name => 'john'});
# =>
# INSERT INTO `user` (`name`) VALUES (?)
# ['john']
-Generate INSERT query.
+Generate an INSERT query.
=over 4
-=item $table
+=item C<< $table >>
Table name in scalar.
-=item \%values
+=item C<< \%values >>
-This is a values for INSERT statement.
+These are the values for the INSERT statement.
-=item \%opt
+=item C<< \%opt >>
-This is a options for INSERT statement
+These are the options for the INSERT statement
=over 4
-=item $opt->{prefix}
+=item C<< $opt->{prefix} >>
-This is a prefix for INSERT statement.
+This is a prefix for the INSERT statement.
For example, you can provide 'INSERT IGNORE INTO' for MySQL.
@@ -506,34 +569,52 @@ Default Value: 'INSERT INTO'
=back
-=item my ($sql, @binds) = $builder->delete($table, \%where|\@where|$where);
+=item C<< my ($sql, @binds) = $builder->delete($table, \%where|\@where|$where, \%opt); >>
my ($sql, @binds) = $builder->delete($table, \%where);
# =>
# DELETE FROM `user` WHERE (`name` = ?)
# ['john']
-Generate DELETE query.
+Generate a DELETE query.
=over 4
-=item $table
+=item C<< $table >>
Table name in scalar.
-=item \%where
+=item C<< \%where >>
-=item \@where
+=item C<< \@where >>
-=item $where
+=item C<< $where >>
where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+=item C<< \%opt >>
+
+These are the options for the DELETE statement
+
+=over 4
+
+=item C<< $opt->{using} >>
+
+This option adds a USING clause. It takes a scalar or an arrayref of table names as argument:
+
+ my ($sql, $binds) = $bulder->delete($table, \%where, { using => 'group' });
+ # =>
+ # DELETE FROM `user` USING `group` WHERE (`group`.`name` = ?)
+ # ['doe']
+ $bulder->delete(..., { using => ['bar', 'qux'] });
+
=back
-=item my ($sql, @binds) = $builder->update($table, \%set|@set, \%where|\@where|$where);
+=back
+
+=item C<< my ($sql, @binds) = $builder->update($table, \%set|@set, \%where|\@where|$where); >>
-Generate UPDATE query.
+Generate a UPDATE query.
my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3});
# =>
@@ -556,27 +637,27 @@ Setting values.
=item $where
-where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
=back
-=item $builder->new_condition()
+=item C<< $builder->new_condition() >>
Create new L<SQL::Maker::Condition> object from C< $builder > settings.
-=item my ($sql, @binds) = $builder->where(\%where)
+=item C<< my ($sql, @binds) = $builder->where(\%where) >>
-=item my ($sql, @binds) = $builder->where(\@where)
+=item C<< my ($sql, @binds) = $builder->where(\@where) >>
-=item my ($sql, @binds) = $builder->where(\@where)
+=item C<< my ($sql, @binds) = $builder->where(\@where) >>
-Where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+Where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
=back
=head1 PLUGINS
-SQL::Maker supports plugin system. Write the code like following.
+SQL::Maker features a plugin system. Write the code as follows:
package My::SQL::Maker;
use parent qw/SQL::Maker/;
@@ -586,9 +667,9 @@ SQL::Maker supports plugin system. Write the code like following.
=over 4
-=item Why don't you use SQL::Abstract?
+=item Why don't you use SQL::Abstract?
-I need more extensible one.
+I need a more extensible one.
So, this module contains L<SQL::Maker::Select>, the extensible B<SELECT> clause object.
@@ -601,8 +682,9 @@ Tokuhiro Matsuno E<lt>tokuhirom AAJKLFJEF@ GMAIL COME<gt>
=head1 SEE ALSO
L<SQL::Abstract>
+L<SQL::QueryMaker>
-Whole code was taken from L<DBIx::Skinny> by nekokak++.
+The whole code was taken from L<DBIx::Skinny> by nekokak++.
=head1 LICENSE
@@ -0,0 +1,2 @@
+authority="cpan:TOKUHIROM"
+module_maker="ModuleBuildTiny"
@@ -73,6 +73,20 @@ subtest 'driver mysql' => sub {
is $sql, qq{DELETE FROM `foo`};
is join(',', @binds), '';
};
+
+ subtest 'delete using where_as_hashref' => sub {
+ my $builder = SQL::Maker->new(driver => 'mysql');
+ my ($sql, @binds) = $builder->delete('foo', [bar => 'baz', john => 'man'], {using => 'bar'});
+ is $sql, qq{DELETE FROM `foo` USING `bar` WHERE (`bar` = ?) AND (`john` = ?)};
+ is join(',', @binds), 'baz,man';
+ };
+
+ subtest 'delete using array where_as_hashref' => sub {
+ my $builder = SQL::Maker->new(driver => 'mysql');
+ my ($sql, @binds) = $builder->delete('foo', [bar => 'baz', john => 'man'], {using => ['bar', 'qux']});
+ is $sql, qq{DELETE FROM `foo` USING `bar`, `qux` WHERE (`bar` = ?) AND (`john` = ?)};
+ is join(',', @binds), 'baz,man';
+ };
};
subtest 'driver mysql, quote_char: "", new_line: " "' => sub {
@@ -106,6 +120,20 @@ subtest 'driver mysql, quote_char: "", new_line: " "' => sub {
is $sql, qq{DELETE FROM foo};
is join(',', @binds), '';
};
+
+ subtest 'delete using where_as_hashref' => sub {
+ my $builder = SQL::Maker->new(driver => 'mysql', quote_char => '', new_line => ' ');
+ my ($sql, @binds) = $builder->delete('foo', [bar => 'baz', john => 'man'], {using => 'bar'});
+ is $sql, qq{DELETE FROM foo USING bar WHERE (bar = ?) AND (john = ?)};
+ is join(',', @binds), 'baz,man';
+ };
+
+ subtest 'delete using array where_as_hashref' => sub {
+ my $builder = SQL::Maker->new(driver => 'mysql', quote_char => '', new_line => ' ');
+ my ($sql, @binds) = $builder->delete('foo', [bar => 'baz', john => 'man'], {using => ['bar', 'qux']});
+ is $sql, qq{DELETE FROM foo USING bar, qux WHERE (bar = ?) AND (john = ?)};
+ is join(',', @binds), 'baz,man';
+ };
};
done_testing;
@@ -2,6 +2,8 @@ use strict;
use warnings;
use Test::More;
use SQL::Maker;
+use SQL::QueryMaker;
+use Test::Requires 'DateTime';
use Test::Requires 'Tie::IxHash';
sub ordered_hashref {
@@ -12,16 +14,16 @@ sub ordered_hashref {
subtest 'driver sqlite' => sub {
subtest 'hash column-value' => sub {
my $builder = SQL::Maker->new(driver => 'sqlite');
- my ($sql, @binds) = $builder->insert('foo' => ordered_hashref(bar => 'baz', john => 'man', created_on => \"datetime('now')", updated_on => \["datetime(?)", "now"]));
- is $sql, qq{INSERT INTO "foo"\n("bar", "john", "created_on", "updated_on")\nVALUES (?, ?, datetime('now'), datetime(?))};
- is join(',', @binds), 'baz,man,now';
+ my ($sql, @binds) = $builder->insert('foo' => ordered_hashref(bar => 'baz', john => 'man', created_on => \"datetime('now')", updated_on => \["datetime(?)", "now"], expires => DateTime->new(year => 2025)));
+ is $sql, qq{INSERT INTO "foo"\n("bar", "john", "created_on", "updated_on", "expires")\nVALUES (?, ?, datetime('now'), datetime(?), ?)};
+ is join(',', @binds), 'baz,man,now,2025-01-01T00:00:00';
};
subtest 'array column-value' => sub {
my $builder = SQL::Maker->new(driver => 'sqlite');
- my ($sql, @binds) = $builder->insert('foo' => [ bar => 'baz', john => 'man', created_on => \"datetime('now')", updated_on => \["datetime(?)", "now" ] ]);
- is $sql, qq{INSERT INTO "foo"\n("bar", "john", "created_on", "updated_on")\nVALUES (?, ?, datetime('now'), datetime(?))};
- is join(',', @binds), 'baz,man,now';
+ my ($sql, @binds) = $builder->insert('foo' => [ bar => 'baz', john => 'man', created_on => \"datetime('now')", updated_on => \["datetime(?)", "now" ], expires => DateTime->new(year => 2025) ]);
+ is $sql, qq{INSERT INTO "foo"\n("bar", "john", "created_on", "updated_on", "expires")\nVALUES (?, ?, datetime('now'), datetime(?), ?)};
+ is join(',', @binds), 'baz,man,now,2025-01-01T00:00:00';
};
subtest 'insert ignore, hash column-value' => sub {
@@ -37,21 +39,28 @@ subtest 'driver sqlite' => sub {
is $sql, qq{INSERT IGNORE "foo"\n("bar", "john", "created_on", "updated_on")\nVALUES (?, ?, datetime('now'), datetime(?))};
is join(',', @binds), 'baz,man,now';
};
+
+ subtest 'term' => sub {
+ my $builder = SQL::Maker->new(driver => 'sqlite');
+ my ($sql, @binds) = $builder->insert('foo' => ordered_hashref(bar => 'baz', john => 'man', created_on => sql_raw("datetime('now')"), updated_on => sql_raw("datetime(?)", "now")));
+ is $sql, qq{INSERT INTO "foo"\n("bar", "john", "created_on", "updated_on")\nVALUES (?, ?, datetime('now'), datetime(?))};
+ is join(',', @binds), 'baz,man,now';
+ };
};
subtest 'driver mysql' => sub {
subtest 'hash column-value' => sub {
my $builder = SQL::Maker->new(driver => 'mysql');
- my ($sql, @binds) = $builder->insert('foo' => ordered_hashref(bar => 'baz', john => 'man', created_on => \"NOW()", updated_on => \["FROM_UNIXTIME(?)", 1302536204 ] ));
- is $sql, qq{INSERT INTO `foo`\n(`bar`, `john`, `created_on`, `updated_on`)\nVALUES (?, ?, NOW(), FROM_UNIXTIME(?))};
- is join(',', @binds), 'baz,man,1302536204';
+ my ($sql, @binds) = $builder->insert('foo' => ordered_hashref(bar => 'baz', john => 'man', created_on => \"NOW()", updated_on => \["FROM_UNIXTIME(?)", 1302536204 ], expires => DateTime->new(year => 2025) ));
+ is $sql, qq{INSERT INTO `foo`\n(`bar`, `john`, `created_on`, `updated_on`, `expires`)\nVALUES (?, ?, NOW(), FROM_UNIXTIME(?), ?)};
+ is join(',', @binds), 'baz,man,1302536204,2025-01-01T00:00:00';
};
subtest 'array column-value' => sub {
my $builder = SQL::Maker->new(driver => 'mysql');
- my ($sql, @binds) = $builder->insert('foo' => [ bar => 'baz', john => 'man', created_on => \"NOW()", updated_on => \["FROM_UNIXTIME(?)", 1302536204 ] ]);
- is $sql, qq{INSERT INTO `foo`\n(`bar`, `john`, `created_on`, `updated_on`)\nVALUES (?, ?, NOW(), FROM_UNIXTIME(?))};
- is join(',', @binds), 'baz,man,1302536204';
+ my ($sql, @binds) = $builder->insert('foo' => [ bar => 'baz', john => 'man', created_on => \"NOW()", updated_on => \["FROM_UNIXTIME(?)", 1302536204 ], expires => DateTime->new(year => 2025) ]);
+ is $sql, qq{INSERT INTO `foo`\n(`bar`, `john`, `created_on`, `updated_on`, `expires`)\nVALUES (?, ?, NOW(), FROM_UNIXTIME(?), ?)};
+ is join(',', @binds), 'baz,man,1302536204,2025-01-01T00:00:00';
};
subtest 'insert ignore, hash column-value' => sub {
@@ -67,6 +76,13 @@ subtest 'driver mysql' => sub {
is $sql, qq{INSERT IGNORE `foo`\n(`bar`, `john`, `created_on`, `updated_on`)\nVALUES (?, ?, NOW(), FROM_UNIXTIME(?))};
is join(',', @binds), 'baz,man,1302536204';
};
+
+ subtest 'term' => sub {
+ my $builder = SQL::Maker->new(driver => 'mysql');
+ my ($sql, @binds) = $builder->insert('foo' => ordered_hashref(bar => 'baz', john => 'man', created_on => sql_raw("NOW()"), updated_on => sql_raw("FROM_UNIXTIME(?)", 1302536204)));
+ is $sql, qq{INSERT INTO `foo`\n(`bar`, `john`, `created_on`, `updated_on`)\nVALUES (?, ?, NOW(), FROM_UNIXTIME(?))};
+ is join(',', @binds), 'baz,man,1302536204';
+ };
};
done_testing;
@@ -84,6 +84,27 @@ subtest 'driver: sqlite' => sub {
is join(',', @binds), '';
};
+ subtest 'limit' => sub {
+ subtest 'with value' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {limit => 3});
+ is $sql ,qq{SELECT *\nFROM "foo"\nLIMIT 3};
+ is join(',', @binds), '';
+ };
+
+ subtest 'with zero' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {limit => 0});
+ is $sql ,qq{SELECT *\nFROM "foo"\nLIMIT 0};
+ is join(',', @binds), '';
+ };
+
+ subtest 'with non-numeric' => sub {
+ {
+ my ($sql, @binds) = eval { $builder->select('foo' => ['*'], +{}, {limit => ' 9t'}) };
+ like $@, qr/Non-numerics/;
+ }
+ }
+ };
+
subtest 'order_by' => sub {
subtest 'scalar' => sub {
my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {order_by => 'yo'});
@@ -238,6 +259,27 @@ subtest 'driver: mysql' => sub {
is join(',', @binds), '';
};
+ subtest 'limit' => sub {
+ subtest 'with value' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {limit => 3});
+ is $sql ,qq{SELECT *\nFROM `foo`\nLIMIT 3};
+ is join(',', @binds), '';
+ };
+
+ subtest 'with zero' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {limit => 0});
+ is $sql ,qq{SELECT *\nFROM `foo`\nLIMIT 0};
+ is join(',', @binds), '';
+ };
+
+ subtest 'with non-numeric' => sub {
+ {
+ my ($sql, @binds) = eval { $builder->select('foo' => ['*'], +{}, {limit => ' 9t'}) };
+ like $@, qr/Non-numerics/;
+ }
+ }
+ };
+
subtest 'order_by' => sub {
subtest 'scalar' => sub {
my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {order_by => 'yo'});
@@ -380,6 +422,27 @@ subtest 'driver: mysql, quote_char: "", new_line: " "' => sub {
is join(',', @binds), '';
};
+ subtest 'limit' => sub {
+ subtest 'with value' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {limit => 3});
+ is $sql ,qq{SELECT * FROM foo LIMIT 3};
+ is join(',', @binds), '';
+ };
+
+ subtest 'with zero' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {limit => 0});
+ is $sql ,qq{SELECT * FROM foo LIMIT 0};
+ is join(',', @binds), '';
+ };
+
+ subtest 'with non-numeric' => sub {
+ {
+ my ($sql, @binds) = eval { $builder->select('foo' => ['*'], +{}, {limit => ' 9t'}) };
+ like $@, qr/Non-numerics/;
+ }
+ }
+ };
+
subtest 'order_by' => sub {
subtest 'scalar' => sub {
my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {order_by => 'yo'});
@@ -432,6 +495,23 @@ subtest 'driver: mysql, quote_char: "", new_line: " "' => sub {
};
};
+ subtest 'index_hint' => sub {
+ subtest 'scalar' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {index_hint => 'bar'});
+ is $sql, qq{SELECT * FROM foo USE INDEX (bar)};
+ };
+
+ subtest 'array ref' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {index_hint => ['bar', 'baz']});
+ is $sql, qq{SELECT * FROM foo USE INDEX (bar,baz)};
+ };
+
+ subtest 'hash ref' => sub {
+ my ($sql, @binds) = $builder->select('foo' => ['*'], +{}, {index_hint => {type => 'FORCE', list => ['bar']}});
+ is $sql, qq{SELECT * FROM foo FORCE INDEX (bar)};
+ };
+ };
+
subtest 'from' => sub {
subtest 'multi from' => sub {
my ($sql, @binds) = $builder->select( [ qw/foo bar/ ], ['*'], +{}, );
@@ -2,6 +2,8 @@ use strict;
use warnings;
use Test::More;
use SQL::Maker;
+use SQL::QueryMaker;
+use Test::Requires 'DateTime';
use Test::Requires 'Tie::IxHash';
sub ordered_hashref {
@@ -12,15 +14,15 @@ sub ordered_hashref {
subtest 'driver: sqlite' => sub {
my $builder = SQL::Maker->new(driver => 'sqlite');
subtest 'arrayref, where cause(hashref)' => sub {
- my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3});
- is $sql, qq{UPDATE "user" SET "name" = ?, "email" = ? WHERE ("user_id" = ?)};
- is join(',', @binds), 'john,john@example.com,3';
+ my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com', expires => DateTime->new(year => 2025)], {user_id => 3});
+ is $sql, qq{UPDATE "user" SET "name" = ?, "email" = ?, "expires" = ? WHERE ("user_id" = ?)};
+ is join(',', @binds), 'john,john@example.com,2025-01-01T00:00:00,3';
};
subtest 'arrayref, where cause(arrayref)' => sub {
- my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], [user_id => 3]);
- is $sql, qq{UPDATE "user" SET "name" = ?, "email" = ? WHERE ("user_id" = ?)};
- is join(',', @binds), 'john,john@example.com,3';
+ my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com', expires => DateTime->new(year => 2025)], [user_id => 3]);
+ is $sql, qq{UPDATE "user" SET "name" = ?, "email" = ?, "expires" = ? WHERE ("user_id" = ?)};
+ is join(',', @binds), 'john,john@example.com,2025-01-01T00:00:00,3';
};
subtest 'arrayref, where cause(condition)' => sub {
@@ -63,20 +65,26 @@ subtest 'driver: sqlite' => sub {
is $sql, qq{UPDATE "foo" SET "user_id" = ?, "updated_on" = datetime(?), "counter" = counter + 1};
is join(',', @binds), '100,now';
};
+
+ subtest 'literal, sub query using term' => sub {
+ my ($sql, @binds) = $builder->update( 'foo', [ user_id => 100, updated_on => sql_raw('datetime(?)', 'now'), counter => sql_raw('counter + 1') ] );
+ is $sql, qq{UPDATE "foo" SET "user_id" = ?, "updated_on" = datetime(?), "counter" = counter + 1};
+ is join(',', @binds), '100,now';
+ };
};
subtest 'driver: mysql' => sub {
my $builder = SQL::Maker->new(driver => 'mysql');
subtest 'array ref, where cause(hashref)' => sub {
- my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3});
- is $sql, qq{UPDATE `user` SET `name` = ?, `email` = ? WHERE (`user_id` = ?)};
- is join(',', @binds), 'john,john@example.com,3';
+ my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com', expires => DateTime->new(year => 2025)], {user_id => 3});
+ is $sql, qq{UPDATE `user` SET `name` = ?, `email` = ?, `expires` = ? WHERE (`user_id` = ?)};
+ is join(',', @binds), 'john,john@example.com,2025-01-01T00:00:00,3';
};
subtest 'array ref, where cause(arrayref)' => sub {
- my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], [user_id => 3]);
- is $sql, qq{UPDATE `user` SET `name` = ?, `email` = ? WHERE (`user_id` = ?)};
- is join(',', @binds), 'john,john@example.com,3';
+ my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com', expires => DateTime->new(year => 2025)], [user_id => 3]);
+ is $sql, qq{UPDATE `user` SET `name` = ?, `email` = ?, `expires` = ? WHERE (`user_id` = ?)};
+ is join(',', @binds), 'john,john@example.com,2025-01-01T00:00:00,3';
};
subtest 'array ref, where cause(condition)' => sub {
@@ -119,20 +127,26 @@ subtest 'driver: mysql' => sub {
is $sql, qq{UPDATE `foo` SET `user_id` = ?, `updated_on` = FROM_UNIXTIME(?), `counter` = counter + 1};
is join(',', @binds), '100,1302241686';
};
+
+ subtest 'literal, sub query using term' => sub {
+ my ($sql, @binds) = $builder->update( 'foo', [ user_id => 100, updated_on => sql_raw('FROM_UNIXTIME(?)', 1302241686), counter => sql_raw('counter + 1') ] );
+ is $sql, qq{UPDATE `foo` SET `user_id` = ?, `updated_on` = FROM_UNIXTIME(?), `counter` = counter + 1};
+ is join(',', @binds), '100,1302241686';
+ };
};
subtest 'driver: mysql, quote_char: "", new_line: " "' => sub {
my $builder = SQL::Maker->new(driver => 'mysql', quote_char => '', new_line => ' ');
subtest 'array ref, where cause(hashref)' => sub {
- my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], {user_id => 3});
- is $sql, qq{UPDATE user SET name = ?, email = ? WHERE (user_id = ?)};
- is join(',', @binds), 'john,john@example.com,3';
+ my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com', expires => DateTime->new(year => 2025)], {user_id => 3});
+ is $sql, qq{UPDATE user SET name = ?, email = ?, expires = ? WHERE (user_id = ?)};
+ is join(',', @binds), 'john,john@example.com,2025-01-01T00:00:00,3';
};
subtest 'array ref, where cause(arrayref)' => sub {
- my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com'], [user_id => 3]);
- is $sql, qq{UPDATE user SET name = ?, email = ? WHERE (user_id = ?)};
- is join(',', @binds), 'john,john@example.com,3';
+ my ($sql, @binds) = $builder->update('user', ['name' => 'john', email => 'john@example.com', expires => DateTime->new(year => 2025)], [user_id => 3]);
+ is $sql, qq{UPDATE user SET name = ?, email = ?, expires = ? WHERE (user_id = ?)};
+ is join(',', @binds), 'john,john@example.com,2025-01-01T00:00:00,3';
};
subtest 'array ref, where cause(condition)' => sub {
@@ -175,6 +189,12 @@ subtest 'driver: mysql, quote_char: "", new_line: " "' => sub {
is $sql, qq{UPDATE foo SET user_id = ?, updated_on = FROM_UNIXTIME(?), counter = counter + 1};
is join(',', @binds), '100,1302241686';
};
+
+ subtest 'literal, sub query using term' => sub {
+ my ($sql, @binds) = $builder->update( 'foo', [ user_id => 100, updated_on => sql_raw('FROM_UNIXTIME(?)', 1302241686), counter => sql_raw('counter + 1') ] );
+ is $sql, qq{UPDATE foo SET user_id = ?, updated_on = FROM_UNIXTIME(?), counter = counter + 1};
+ is join(',', @binds), '100,1302241686';
+ };
};
done_testing;
@@ -3,6 +3,7 @@ use warnings;
use Test::More;
use SQL::Maker;
use Test::Requires 'Tie::IxHash';
+use Test::Requires 'DateTime';
sub ordered_hashref {
tie my %params, Tie::IxHash::, @_;
@@ -29,6 +30,18 @@ subtest 'driver: sqlite' => sub {
is $sql, qq{("x" = ?)};
is join(',', @binds), '3';
};
+
+ subtest 'hash-stringify' => sub {
+ my ($sql, @binds) = $builder->where({ x => DateTime->new(year => 2025) });
+ is $sql, qq{("x" = ?)};
+ is join(',', @binds), '2025-01-01T00:00:00';
+ };
+
+ subtest 'hash-stringify' => sub {
+ my ($sql, @binds) = $builder->where([x => DateTime->new(year => 2025)]);
+ is $sql, qq{("x" = ?)};
+ is join(',', @binds), '2025-01-01T00:00:00';
+ };
};
done_testing;
@@ -0,0 +1,83 @@
+use strict;
+use warnings;
+use utf8;
+use Test::More;
+use SQL::Maker;
+use SQL::QueryMaker;
+
+sub checkerr {
+ my $code = shift;
+ return sub {
+ local $@;
+ my $query = eval {
+ $code->();
+ };
+ ok ! defined $query, "does not return anything";
+ like $@, qr/cannot pass in an unblessed ref/, "error is thrown";
+ };
+}
+
+my $maker = SQL::Maker->new(
+ driver => 'SQLite',
+ strict => 1,
+);
+
+ok $maker->strict, "maker->strict";
+
+subtest "maker->select where" => sub {
+ my ($sql, @binds) = $maker->select("table", ["*"], sql_eq(id => 1));
+ like $sql, qr/WHERE\s+.*id.*\s*=/s;
+ is_deeply \@binds, [ 1 ];
+};
+
+subtest "maker->update where" => sub {
+ my ($sql, @binds) = $maker->delete("table", sql_eq(id => 1));
+ like $sql, qr/WHERE\s+.*id.*\s*=/s;
+ is_deeply \@binds, [ 1 ];
+};
+
+subtest "maker->delete where" => sub {
+ my ($sql, @binds) = $maker->delete("table", sql_eq(id => 1));
+ like $sql, qr/WHERE\s+.*id.*\s*=/s;
+ is_deeply \@binds, [ 1 ];
+};
+
+subtest "maker->new_condition (err)" => checkerr(sub {
+ $maker->new_condition->add(
+ foo => [1],
+ );
+});
+
+{
+ my $select = $maker->new_select;
+ ok $select->strict, "select->strict";
+ subtest "select->new_condition (err)" => checkerr(sub {
+ $select->new_condition->add(
+ foo => [1],
+ );
+ });
+}
+
+subtest "maker->select (err)" => checkerr(sub {
+ $maker->select("user", ['*'], { name => ["John", "Tom" ]});
+});
+
+subtest "maker->insert (err)" => checkerr(sub {
+ $maker->insert(
+ user => [ name => "John", created_on => \"datetime(now)" ]
+ );
+});
+
+subtest "maker->delete (err)" => checkerr(sub {
+ $maker->delete(user => [ name => ["John", "Tom"]]);
+});
+
+subtest "maker->update where (err)" => checkerr(sub {
+ $maker->update(user => [name => "John"], { user_id => [1, 2] });
+});
+
+subtest "maker->update set (err)" => checkerr(sub {
+ $maker->update(user => [name => \"select *"]);
+});
+
+done_testing;
@@ -3,7 +3,7 @@ use warnings;
use Test::More;
use SQL::Maker::SQLType qw/sql_type/;
use SQL::Maker::Condition;
-use Data::Dumper;
+use SQL::QueryMaker;
use DBI qw/:sql_types/;
open my $fh, '<', 'lib/SQL/Maker/Condition.pm' or die "cannot open file: $!";
@@ -13,7 +13,7 @@ while (<$fh>) {
}
my ($in, $query, @bind);
while (<$fh>) {
- $in = eval $1 if /IN:(.+)/;
+ $in = $1 if /IN:\s*(.+)\s*$/;
$query = eval $1 if /OUT QUERY:(.+)/;
if (/OUT BIND:(.+)/) {
@bind = eval $1;
@@ -24,11 +24,15 @@ done_testing;
exit(0);
sub test {
- my ($source, $expected_term, $expected_bind) = @_;
- local $Data::Dumper::Terse = 1;
- local $Data::Dumper::Indent = 0;
+ my ($in, $expected_term, $expected_bind) = @_;
- subtest Dumper($source) => sub {
+ subtest $in => sub {
+ my $source = do {
+ local $@;
+ my $source = eval $in;
+ die $@ if $@;
+ $source;
+ };
my $cond = SQL::Maker::Condition->new(
quote_char => q{`},
name_sep => q{.},
@@ -0,0 +1,28 @@
+use strict;
+use warnings;
+use Test::More;
+use SQL::Maker::Condition;
+
+subtest '[]' => sub {
+ my $w = SQL::Maker::Condition->new();
+ $w->add(x => []);
+ is $w->as_sql, '(0=1)';
+ is join(', ', $w->bind), '';
+};
+
+subtest 'in' => sub {
+ my $w = SQL::Maker::Condition->new();
+ $w->add(x => { 'IN' => [] });
+ is $w->as_sql, '(0=1)';
+ is join(', ', $w->bind), '';
+};
+
+subtest 'not in' => sub {
+ my $w2 = SQL::Maker::Condition->new();
+ $w2->add(x => { 'NOT IN' => [] });
+ is $w2->as_sql, '(1=1)';
+ is join(', ', $w2->bind), '';
+};
+
+done_testing;
+
@@ -2,6 +2,8 @@ use strict;
use warnings;
use Test::More;
use SQL::Maker;
+use SQL::QueryMaker;
+use Test::Requires 'DateTime';
use Test::Requires 'Tie::IxHash';
sub ordered_hashref {
@@ -58,32 +60,70 @@ subtest 'mysql' => sub {
bar => 'baz',
john => 'man',
created_on => \"UNIX_TIMESTAMP()",
- updated_on => \[ "UNIX_TIMESTAMP(?)", "2011-04-12" ]
+ updated_on => \[ "UNIX_TIMESTAMP(?)", "2011-04-12" ],
+ expires => DateTime->new(year => 2024),
),
ordered_hashref(
bar => 'bee',
john => 'row',
created_on => \"UNIX_TIMESTAMP()",
- updated_on => \[ "UNIX_TIMESTAMP(?)", "2011-04-13" ]
+ updated_on => \[ "UNIX_TIMESTAMP(?)", "2011-04-13" ],
+ expires => DateTime->new(year => 2025),
),
],
+{
update => ordered_hashref(
bar => \"VALUES(bar)",
john => "john",
- updated_on => \[ "UNIX_TIMESTAMP(?)", "2011-04-14" ]
+ updated_on => \[ "UNIX_TIMESTAMP(?)", "2011-04-14" ],
+ expires => DateTime->new(year => 2025),
)
},
);
is $sql, substr(<< 'SQL', 0, -1);
INSERT INTO `foo`
+(`bar`, `john`, `created_on`, `updated_on`, `expires`)
+VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(?), ?),
+(?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(?), ?)
+ON DUPLICATE KEY UPDATE `bar` = VALUES(bar), `john` = ?, `updated_on` = UNIX_TIMESTAMP(?), `expires` = ?
+SQL
+ is join( ',', @binds ), 'baz,man,2011-04-12,2024-01-01T00:00:00,bee,row,2011-04-13,2025-01-01T00:00:00,john,2011-04-14,2025-01-01T00:00:00';
+ };
+
+subtest 'on duplicate key update (term)' => sub {
+ my $builder = SQL::Maker->new( driver => 'mysql' );
+ my ( $sql, @binds ) = $builder->insert_multi(
+ 'foo' => [
+ ordered_hashref(
+ bar => 'baz',
+ john => 'man',
+ created_on => sql_raw("UNIX_TIMESTAMP()"),
+ updated_on => sql_raw("UNIX_TIMESTAMP(?)", "2011-04-12"),
+ ),
+ ordered_hashref(
+ bar => 'bee',
+ john => 'row',
+ created_on => sql_raw("UNIX_TIMESTAMP()"),
+ updated_on => sql_raw("UNIX_TIMESTAMP(?)", "2011-04-13"),
+ ),
+ ],
+ +{
+ update => ordered_hashref(
+ bar => \"VALUES(bar)",
+ john => "john",
+ updated_on => sql_raw("UNIX_TIMESTAMP(?)", "2011-04-14"),
+ )
+ },
+ );
+ is $sql, substr(<< 'SQL', 0, -1);
+INSERT INTO `foo`
(`bar`, `john`, `created_on`, `updated_on`)
VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(?)),
(?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(?))
ON DUPLICATE KEY UPDATE `bar` = VALUES(bar), `john` = ?, `updated_on` = UNIX_TIMESTAMP(?)
SQL
- is join( ',', @binds ), 'baz,man,2011-04-12,bee,row,2011-04-13,john,2011-04-14';
- };
+ is join( ',', @binds ), 'baz,man,2011-04-12,bee,row,2011-04-13,john,2011-04-14';
+};
};
subtest 'insert_multi( $table, \@cols, \@values, \%opts )' => sub {
@@ -443,6 +443,8 @@ subtest 'LIMIT OFFSET' => sub {
is($stmt->as_sql, "FROM `foo`\nLIMIT 5");
$stmt->offset(10);
is($stmt->as_sql, "FROM `foo`\nLIMIT 5 OFFSET 10");
+ $stmt->limit(0);
+ is($stmt->as_sql, "FROM `foo`\nLIMIT 0 OFFSET 10");
$stmt->limit(" 15g"); ## Non-numerics should cause an error
{
my $sql = eval { $stmt->as_sql };
@@ -457,6 +459,8 @@ subtest 'LIMIT OFFSET' => sub {
is($stmt->as_sql, "FROM foo LIMIT 5");
$stmt->offset(10);
is($stmt->as_sql, "FROM foo LIMIT 5 OFFSET 10");
+ $stmt->limit(0);
+ is($stmt->as_sql, "FROM foo LIMIT 0 OFFSET 10");
$stmt->limit(" 15g"); ## Non-numerics should cause an error
{
my $sql = eval { $stmt->as_sql };
@@ -632,6 +636,22 @@ subtest 'index hint' => sub {
$stmt->add_index_hint('baz' => { type => 'USE', list => ['index_hint']});
is($stmt->as_sql, "SELECT foo FROM baz USE INDEX (index_hint)", "we can turn on USE INDEX");
};
+
+ subtest 'hint as scalar' => sub {
+ my $stmt = ns( quote_char => q{}, name_sep => q{.}, new_line => q{ } );
+ $stmt->add_select(foo => 'foo');
+ $stmt->add_from( qw(baz) );
+ $stmt->add_index_hint('baz' => 'index_hint');
+ is($stmt->as_sql, "SELECT foo FROM baz USE INDEX (index_hint)", "we can turn on USE INDEX");
+ };
+
+ subtest 'hint as array ref' => sub {
+ my $stmt = ns( quote_char => q{}, name_sep => q{.}, new_line => q{ } );
+ $stmt->add_select(foo => 'foo');
+ $stmt->add_from( qw(baz) );
+ $stmt->add_index_hint('baz' => ['index_hint']);
+ is($stmt->as_sql, "SELECT foo FROM baz USE INDEX (index_hint)", "we can turn on USE INDEX");
+ };
};
subtest 'index hint with joins' => sub {