NAME

SQL::Format::Spec - The SQL::Format cheat sheet

sqlf() CHEAT SHEET

This cheat sheet rules are:

# Comment
Input  : $format
Input  : \@arguments
Expects: $stmt
Expects: \@bind

For example:

# basic select query
SELECT %c FROM %t WHERE %w
[qw/foo bar/], 'hoge', { fuga => 'piyo' }
SELECT `foo`, `bar` FROM `hoge` WHERE (`fuga` = ?)
[qw/piyo/]

# maybe your code are
my ($stmt, @bind) = sqlf 'SELECT %c FROM %t WHERE %w' => (
    [qw/foo bar/],
    'hoge',
    { fuga => 'piyo' },
);

columns

# scalar
SELECT %c FROM table
'foo'
SELECT `foo` FROM table
[]

# array
SELECT %c FROM table
[qw/foo bar/]
SELECT `foo`, `bar` FROM table
[]

# empty array
SELECT %c FROM table
[]
SELECT * FROM table
[]

# undef
SELECT %c FROM table
undef
SELECT * FROM table
[]

# '*'
SELECT %c FROM table
'*'
SELECT * FROM table
[]

# scalar ref
SELECT %c FROM table
\'foo bar'
SELECT foo bar FROM table
[]

# scalar ref in array
SELECT %c FROM table
[\'foo bar', 'baz']
SELECT foo bar, `baz` FROM table
[]

# hash in array
SELECT %c FROM table
[ { foo => 'bar' } ]
SELECT `foo` `bar` FROM table
[]

# array in array
SELECT %c FROM table
[ [foo => 'bar'] ]
SELECT `foo` `bar` FROM table
[]

# scalar ref into array in array
SELECT %c FROM table
[ [\'UNIX_TIMESTAMP()' => 'bar'] ]
SELECT UNIX_TIMESTAMP() `bar` FROM table
[]

# ref array in array (will deprecate)
SELECT %c FROM table
[ \[\'UNIX_TIMESTAMP(?)' => 'bar', '2012-12-12 12:12:12'] ]
SELECT UNIX_TIMESTAMP(?) `bar` FROM table
['2012-12-12 12:12:12']

# array in array in array
SELECT %c FROM table
[ [ [\'UNIX_TIMESTAMP(?)', '2012-12-12 12:12:12'], 'bar' ] ]
SELECT UNIX_TIMESTAMP(?) `bar` FROM table
['2012-12-12 12:12:12']

# ref array in array in array
SELECT %c FROM table
[ [ \[\'UNIX_TIMESTAMP(?)', '2012-12-12 12:12:12'], 'bar' ] ]
SELECT UNIX_TIMESTAMP(?) `bar` FROM table
['2012-12-12 12:12:12']

table

# scalar
SELECT foo FROM %t
'table'
SELECT foo FROM `table`
[]

# hash
SELECT foo FROM %t
{ table => 't' }
SELECT foo FROM `table` `t`
[]

# hash multi value
SELECT foo FROM %t
{ tableA => 'a', tableB => 'b' }
SELECT foo FROM `tableA` `a`, `tableB` `b`
[]

# array
SELECT foo FROM %t
[qw/t1 t2/]
SELECT foo FROM `t1`, `t2`
[]

# array mixied
SELECT foo FROM %t
[ 't1', { t2 => 'foo' }, { t3 => 'bar', t4 => 'baz' } ]
SELECT foo FROM `t1`, `t2` `foo`, `t3` `bar`, `t4` `baz`
[]

# add index hint
SELECT foo FROM %t
{ table => { index => { type => 'force', keys => [qw/key1 key2/] } } }
SELECT foo FROM `table` FORCE INDEX (`key1`, `key2`)
[]

# add index hint (default type is USE INDEX)
SELECT foo FROM %t
{ table => { index => { keys => [qw/key1 key2/] } } }
SELECT foo FROM `table` USE INDEX (`key1`, `key2`)
[]

# add index hint (keys is scalar)
SELECT foo FROM %t
{ table => { index => { keys => 'key1' } } }
SELECT foo FROM `table` USE INDEX (`key1`)
[]

# add index hint with alias
SELECT foo FROM %t
{ table => { alias => 't1', index => { type => 'force', keys => [qw/key1 key2/] } } }
SELECT foo FROM `table` `t1` FORCE INDEX (`key1`, `key2`)
[]

where array in

# array
WHERE %w
{ id => [qw/1 2 3/] }
WHERE (`id` IN (?, ?, ?))
[qw/1 2 3/]

# empry array
WHERE %w
{ id => [] }
WHERE (0=1)
[]

# subquery
WHERE %w
{ id => \['SELECT x_id FROM foo WHERE bar = ? AND baz = ?', qw/hoge fuga/] }
WHERE (`id` IN (SELECT x_id FROM foo WHERE bar = ? AND baz = ?))
[qw/hoge fuga/]

# subquery using sqlf
WHERE %w
{ id => \[sqlf('SELECT %c FROM %t WHERE %w', x_id => 'foo', { bar => 'hoge', baz => 'fuga'})] }
WHERE (`id` IN (SELECT `x_id` FROM `foo` WHERE (`bar` = ?) AND (`baz` = ?)))
[qw/hoge fuga/]

where array and

# scalars
WHERE %w
{ id => [ -and => qw/1 2 3/ ] }
WHERE ((`id` = ?) AND (`id` = ?) AND (`id` = ?))
[qw/1 2 3/]

# hashes
WHERE %w
{ id => [ -and => { '>' => 10 }, { '<' => 20 } ] }
WHERE ((`id` > ?) AND (`id` < ?))
[qw/10 20/]

# array(s)
WHERE %w
{ id => [ -and => [qw/1 2 3/], [qw/4 5 6/] ] }
WHERE ((`id` IN (?, ?, ?)) AND (`id` IN (?, ?, ?)))
[qw/1 2 3 4 5 6/]

# IN and scalar
WHERE %w
{ id => [ -and => { IN => [qw/1 2 3/] }, 4 ] }
WHERE ((`id` IN (?, ?, ?)) AND (`id` = ?))
[qw/1 2 3 4/]

where array or

# scalars
WHERE %w
{ id => [ -or => qw/1 2 3/ ] }
WHERE ((`id` = ?) OR (`id` = ?) OR (`id` = ?))
[qw/1 2 3/]

# hashes
WHERE %w
{ id => [ -or => { '>' => 10 }, { '<' => 20 } ] }
WHERE ((`id` > ?) OR (`id` < ?))
[qw/10 20/]

# array(s)
WHERE %w
{ id => [ -or => [qw/1 2 3/], [qw/4 5 6/] ] }
WHERE ((`id` IN (?, ?, ?)) OR (`id` IN (?, ?, ?)))
[qw/1 2 3 4 5 6/]

# IN and scalar
WHERE %w
{ id => [ -or => { IN => [qw/1 2 3/] }, 4 ] }
WHERE ((`id` IN (?, ?, ?)) OR (`id` = ?))
[qw/1 2 3 4/]

# no-op equals OR
WHERE %w
{ id => [ { '>' => 10 }, { '<' => 20 } ] }
WHERE ((`id` > ?) OR (`id` < ?))
[qw/10 20/]

where special key -or

# single
WHERE %w
+{ -or => { a => 1, b => 2 } }
WHERE ((`a` = ?) AND (`b` = ?))
[qw/1 2/]

# multi
WHERE %w
+{ -or => [ { a => 1, b => 2 }, { c => 3, d => 4 } ] }
WHERE (((`a` = ?) AND (`b` = ?)) OR ((`c` = ?) AND (`d` = ?)))
[qw/1 2 3 4/]

# multi complex
WHERE %w
+{ -or => [ { a => 1, b => 2 }, { c => 3, d => 4 } ], foo => 'bar' }
WHERE (((`a` = ?) AND (`b` = ?)) OR ((`c` = ?) AND (`d` = ?))) AND (`foo` = ?)
[qw/1 2 3 4 bar/]

where special key -and

# single
WHERE %w
+{ -and => { a => 1, b => 2 } }
WHERE ((`a` = ?) AND (`b` = ?))
[qw/1 2/]

# multi
WHERE %w
+{ -and => [ { a => 1, b => 2 }, { c => 3, d => 4 } ] }
WHERE (((`a` = ?) AND (`b` = ?)) AND ((`c` = ?) AND (`d` = ?)))
[qw/1 2 3 4/]

# multi complex
WHERE %w
+{ -and => [ { a => 1, b => 2 }, { c => 3, d => 4 } ], foo => 'bar' }
WHERE (((`a` = ?) AND (`b` = ?)) AND ((`c` = ?) AND (`d` = ?))) AND (`foo` = ?)
[qw/1 2 3 4 bar/]

# complex
WHERE %w
+{ -and => [ -or => [ { a => 1 }, { b => 2 } ], -or => [ { c => 3 }, { d => 4 } ], { foo => 'bar' } ], hoge => 'fuga' }
WHERE ((((`a` = ?)) OR ((`b` = ?))) AND (((`c` = ?)) OR ((`d` = ?))) AND ((`foo` = ?))) AND (`hoge` = ?)
[qw/1 2 3 4 bar fuga/]

where multiple

# basic
WHERE %w
[ { a => 1, b => 2 }, { c => 3, d => 4 }]
WHERE ((`a` = ?) AND (`b` = ?)) OR ((`c` = ?) AND (`d` = ?))
[qw/1 2 3 4/]

where hash in

# IN (array)
WHERE %w
{ id => { IN => [qw/1 2 3/] } }
WHERE (`id` IN (?, ?, ?))
[qw/1 2 3/]

# iN (ignore case)
WHERE %w
{ id => { iN => [qw/1 2 3/] } }
WHERE (`id` IN (?, ?, ?))
[qw/1 2 3/]

# -in
WHERE %w
{ id => { -in => [qw/1 2 3/] } }
WHERE (`id` IN (?, ?, ?))
[qw/1 2 3/]

# IN (empry array)
WHERE %w
{ id => { IN => [] } }
WHERE (0=1)
[]

# IN (scalar)
WHERE %w
{ id => { IN => 'foo' } }
WHERE (`id` = ?)
[qw/foo/]

# IN (ref)
WHERE %w
{ id => { IN => \['SELECT foo FROM bar WHERE hoge = ?', 'fuga'] } }
WHERE (`id` IN (SELECT foo FROM bar WHERE hoge = ?))
[qw/fuga/]

# IN (scalar ref)
WHERE %w
{ id => { 'IN' => \'SELECT foo FROM bar' } }
WHERE (`id` IN (SELECT foo FROM bar))
[]

# IN (undef)
WHERE %w
{ id => { IN => undef } }
WHERE (`id` IS NULL)
[]

where hash not in

# NOT IN (array)
WHERE %w
{ id => { 'NOT IN' => [qw/1 2 3/] } }
WHERE (`id` NOT IN (?, ?, ?))
[qw/1 2 3/]

# Not iN (ignore case)
WHERE %w
{ id => { 'Not iN' => [qw/1 2 3/] } }
WHERE (`id` NOT IN (?, ?, ?))
[qw/1 2 3/]

# -not_in
WHERE %w
{ id => { -not_in => [qw/1 2 3/] } }
WHERE (`id` NOT IN (?, ?, ?))
[qw/1 2 3/]

# NOT IN (empry array)
WHERE %w
{ id => { 'NOT IN' => [] } }
WHERE (1=1)
[]

# NOT IN (scalar)
WHERE %w
{ id => { 'NOT IN' => 'foo' } }
WHERE (`id` <> ?)
[qw/foo/]

# NOT IN (ref)
WHERE %w
{ id => { 'NOT IN' => \['SELECT foo FROM bar WHERE hoge = ?', 'fuga'] } }
WHERE (`id` NOT IN (SELECT foo FROM bar WHERE hoge = ?))
[qw/fuga/]

# NOT IN (scalar ref)
WHERE %w
{ id => { 'NOT IN' => \'SELECT foo FROM bar' } }
WHERE (`id` NOT IN (SELECT foo FROM bar))
[]

# NOT IN (undef)
WHERE %w
{ id => { 'NOT IN' => undef } }
WHERE (`id` IS NOT NULL)
[]

where hash like

# scalar
WHERE %w
{ id => { LIKE => 'foo%' } }
WHERE (`id` LIKE ?)
[qw/foo%/]

# -like
WHERE %w
{ id => { -like => 'foo%' } }
WHERE (`id` LIKE ?)
[qw/foo%/]

# scalar ref
WHERE %w
{ id => { LIKE => \'"foo%"' } }
WHERE (`id` LIKE "foo%")
[]

# array
WHERE %w
{ id => { LIKE => ['%foo', \'"bar%"'] } }
WHERE (`id` LIKE ? OR `id` LIKE "bar%")
[qw/%foo/]

# with escape
WHERE %w
{ id => { LIKE => { '@' => '@_foo%' } } }
WHERE (`id` LIKE ? ESCAPE ?)
[qw/@_foo% @/]

# scalar ref with escape
WHERE %w
{ id => { LIKE => { '@' => \'"@_foo%"' } } }
WHERE (`id` LIKE "@_foo%" ESCAPE ?)
[qw/@/]

# array with escape
WHERE %w
{ id => { LIKE => { '@' => [\'"@_foo%"', '@_bar%'] } } }
WHERE (`id` LIKE "@_foo%" ESCAPE ? OR `id` LIKE ? ESCAPE ?)
[qw/@ @_bar% @/]

# -like_binaray
WHERE %w
{ id => { -LIKE_BINARY => '%foo' } }
WHERE (`id` LIKE BINARY ?)
[qw/%foo/]

# like binaray
WHERE %w
{ id => { 'LIKE BINARY' => '%foo' } }
WHERE (`id` LIKE BINARY ?)
[qw/%foo/]

where hash not like

# scalar
WHERE %w
{ id => { 'NOT LIKE' => 'foo%' } }
WHERE (`id` NOT LIKE ?)
[qw/foo%/]

# -not_like 
WHERE %w
{ id => { -not_like => 'foo%' } }
WHERE (`id` NOT LIKE ?)
[qw/foo%/]

# scalar ref
WHERE %w
{ id => { 'NOT LIKE' => \'"foo%"' } }
WHERE (`id` NOT LIKE "foo%")
[]

# array
WHERE %w
{ id => { 'NOT LIKE' => ['%foo', \'"bar%"'] } }
WHERE (`id` NOT LIKE ? OR `id` NOT LIKE "bar%")
[qw/%foo/]

# -not_like_binaray
WHERE %w
{ id => { -NOT_LIKE_BINARY => '%foo' } }
WHERE (`id` NOT LIKE BINARY ?)
[qw/%foo/]

# like binaray
WHERE %w
{ id => { 'NOT LIKE BINARY' => '%foo' } }
WHERE (`id` NOT LIKE BINARY ?)
[qw/%foo/]

where hash between

# array
WHERE %w
{ id => { BETWEEN => [qw/10 20/] } }
WHERE (`id` BETWEEN ? AND ?)
[qw/10 20/]

# -between
WHERE %w
{ id => { -between => [qw/10 20/] } }
WHERE (`id` BETWEEN ? AND ?)
[qw/10 20/]

# ref
WHERE %w
{ id => { BETWEEN => \['? AND ?', 10, 20] } }
WHERE (`id` BETWEEN ? AND ?)
[qw/10 20/]

# scalar
WHERE %w
{ id => { BETWEEN => \'lower(x) AND upper(y)' } }
WHERE (`id` BETWEEN lower(x) AND upper(y))
[]

# mixed
WHERE %w
{ id => { BETWEEN => [ \'lower(x)', \['upper(?)', 'stuff'] ] } }
WHERE (`id` BETWEEN lower(x) AND upper(?))
['stuff']

where hash not between

# array
WHERE %w
{ id => { 'NOT BETWEEN' => [qw/10 20/] } }
WHERE (`id` NOT BETWEEN ? AND ?)
[qw/10 20/]

# -between
WHERE %w
{ id => { -not_between => [qw/10 20/] } }
WHERE (`id` NOT BETWEEN ? AND ?)
[qw/10 20/]

# ref
WHERE %w
{ id => { 'NOT BETWEEN' => \['? AND ?', 10, 20] } }
WHERE (`id` NOT BETWEEN ? AND ?)
[qw/10 20/]

# scalar
WHERE %w
{ id => { 'NOT BETWEEN' => \'lower(x) AND upper(y)' } }
WHERE (`id` NOT BETWEEN lower(x) AND upper(y))
[]

# mixed
WHERE %w
{ id => { 'NOT BETWEEN' => [ \'lower(x)', \['upper(?)', 'stuff'] ] } }
WHERE (`id` NOT BETWEEN lower(x) AND upper(?))
['stuff']

where hash other operator

# scalar
WHERE %w
{ id => { '<' => 12345 } }
WHERE (`id` < ?)
[qw/12345/]

# scalar ref
WHERE %w
{ id => { '>' => \'UNIX_TIMESTAMP()' } }
WHERE (`id` > UNIX_TIMESTAMP())
[]

# ref array
WHERE %w
{ id => { '!=' => \['UNIX_TIMESTAMP(?)', '2012-12-12 00:00:00'] } }
WHERE (`id` != UNIX_TIMESTAMP(?))
['2012-12-12 00:00:00']

# array =
WHERE %w
{ id => { '=' => [qw/1 2 3/] } }
WHERE (`id` IN (?, ?, ?))
[qw/1 2 3/]

# empty array =
WHERE %w
{ id => { '=' => [] } }
WHERE (0=1)
[]

# array !=
WHERE %w
{ id => { '!=' => [qw/1 2 3/] } }
WHERE (`id` NOT IN (?, ?, ?))
[qw/1 2 3/]

# empty array !=
WHERE %w
{ id => { '!=' => [] } }
WHERE (1=1)
[]

# empty condition
WHERE %w
{}
WHERE (1=1)
[]

where hash mixed

# mixied scalar
WHERE %w
{ id => { '>' => '12345', '<' => '67890' } }
WHERE ((`id` < ?) AND (`id` > ?))
[qw/67890 12345/]

# mixied array
WHERE %w
{ id => { '!=' => [qw/a b c/], '=' => [qw/1 2 3/] } }
WHERE ((`id` NOT IN (?, ?, ?)) AND (`id` IN (?, ?, ?)))
[qw/a b c 1 2 3/]

where scalar

# scalar
WHERE %w
{ id => 12345 }
WHERE (`id` = ?)
[12345]

where scalar ref

# scalar ref
WHERE %w
{ id => \'> UNIX_TIMESTAMP()' }
WHERE (`id` > UNIX_TIMESTAMP())
[]

where undef

# is null
WHERE %w
{ id => undef }
WHERE (`id` IS NULL)
[]

group by

# scalar
%o
{ group_by => 'foo' }
GROUP BY `foo`
[]

# undef
%o
{ group_by => undef }
GROUP BY NULL
[]

# hashref
%o
{ group_by => { foo => 'DESC' } }
GROUP BY `foo` DESC
[]

# hashref -asc
%o
{ group_by => { -asc => 'foo' } }
GROUP BY `foo` ASC
[]

# hashref multi value
%o
{ group_by => { foo => 'DESC', -asc => 'bar' } }
GROUP BY `bar` ASC, `foo` DESC
[]

# array mixed
%o
{ group_by => ['hoge', { foo => 'DESC', -asc => 'bar' }] }
GROUP BY `hoge`, `bar` ASC, `foo` DESC
[]

having (same as where)

# simple
%o
{ having => { foo => 'bar' } }
HAVING (`foo` = ?)
[qw/bar/]

# with group by
%o
{ having => { 'SUM(foo)' => { '>=' => 10 } }, group_by => 'bar' }
GROUP BY `bar` HAVING (SUM(foo) >= ?)
[qw/10/]

order by

# scalar
%o
{ order_by => 'foo' }
ORDER BY `foo`
[]

# undef
%o
{ order_by => undef }
ORDER BY NULL
[]

# hashref
%o
{ order_by => { foo => 'DESC' } }
ORDER BY `foo` DESC
[]

# hashref -asc
%o
{ order_by => { -asc => 'foo' } }
ORDER BY `foo` ASC
[]

# hashref multi value
%o
{ order_by => { foo => 'DESC', -asc => 'bar' } }
ORDER BY `bar` ASC, `foo` DESC
[]

# array mixed
%o
{ order_by => ['hoge', { foo => 'DESC', -asc => 'bar' }] }
ORDER BY `hoge`, `bar` ASC, `foo` DESC
[]

limit offset

# limit only
%o
{ limit => 100 }
LIMIT 100
[]

# limit offset
%o
{ limit => 100, offset => 20 }
LIMIT 100 OFFSET 20
[]

set clause

# hash
UPDATE foo SET %s
{ bar => 'baz' }
UPDATE foo SET `bar` = ?
[qw/baz/]

# hash multi
UPDATE foo SET %s
{ bar => 'baz', hoge => 'fuga' }
UPDATE foo SET `bar` = ?, `hoge` = ?
[qw/baz fuga/]

# scalar ref into hash
UPDATE foo SET %s
{ bar => \'UNIX_TIMESTAMP()' }
UPDATE foo SET `bar` = UNIX_TIMESTAMP()
[]

# array ref into hash
UPDATE foo SET %s
{ bar => \['UNIX_TIMESTAMP(?)', '2012-12-12'] }
UPDATE foo SET `bar` = UNIX_TIMESTAMP(?)
['2012-12-12']

# array
UPDATE foo SET %s
[ bar => 'baz' ]
UPDATE foo SET `bar` = ?
[qw/baz/]

# array multi
UPDATE foo SET %s
[ hoge => 'fuga', bar => 'baz' ]
UPDATE foo SET `hoge` = ?, `bar` = ?
[qw/fuga baz/]

# scalar ref into array
UPDATE foo SET %s
[ bar => \'UNIX_TIMESTAMP()' ]
UPDATE foo SET `bar` = UNIX_TIMESTAMP()
[]

# array ref into array
UPDATE foo SET %s
[ bar => \['UNIX_TIMESTAMP(?)', '2012-12-12'] ]
UPDATE foo SET `bar` = UNIX_TIMESTAMP(?)
['2012-12-12']

join

# inner join on scalar
%j
{ table => 'bar', condition => 'foo.id = bar.id' }
INNER JOIN `bar` ON foo.id = bar.id
[]

# inner join on ref hash
%j
{ table => 'bar', condition => { 'foo.id' => 'bar.id' } }
INNER JOIN `bar` ON `foo`.`id` = `bar`.`id`
[]

# inner join on hash multi
%j
{ table => 'bar', condition => { 'foo.id' => 'bar.id', 'foo.created_at' => 'bar.created_at' } }
INNER JOIN `bar` ON (`foo`.`created_at` = `bar`.`created_at`) AND (`foo`.`id` = `bar`.`id`)
[]

# inner join on hash with op
%j
{ table => 'bar', condition => { 'foo.id' => { '>' => 'bar.id' } } }
INNER JOIN `bar` ON `foo`.`id` > `bar`.`id`
[]

# inner join on hash with ref array
%j
{ table => 'bar', condition => { 'foo.id' => \['UNIX_TIMESTAMP(?)', '2012-12-12'] } }
INNER JOIN `bar` ON `foo`.`id` = UNIX_TIMESTAMP(?)
['2012-12-12']

# inner join on hash with op multi
%j
{ table => 'bar', condition => { 'foo.id' => { '<' => 'bar.id', '>' => \['?', '12345'] } } }
INNER JOIN `bar` ON (`foo`.`id` < `bar`.`id`) AND (`foo`.`id` > ?)
[qw/12345/]

# inner join on where clause
%j
{ table => 'bar', condition => { -where => { foo => 12345 } } }
INNER JOIN `bar` ON (`foo` = ?)
[qw/12345/]

# inner join on array
%j
{ table => 'bar', condition => [qw/id created_at/] }
INNER JOIN `bar` USING (`id`, `created_at`)
[]

# with alias
%j
{ table => { bar => 'b' }, condition => { 'f.id' => 'b.id' } }
INNER JOIN `bar` `b` ON `f`.`id` = `b`.`id`
[]

# left join
%j
{ type => 'left', table => { bar => 'b' }, condition => { 'f.id' => 'b.id' } }
LEFT JOIN `bar` `b` ON `f`.`id` = `b`.`id`
[]

# array
%j
[ { type => 'left', table => { bar => 'b'}, condition => { 'f.id' => 'b.id' } }, { table => { hoge => 'h' }, condition => { 'h.id' => 'f.id' } } ]
LEFT JOIN `bar` `b` ON `f`.`id` = `b`.`id` INNER JOIN `hoge` `h` ON `h`.`id` = `f`.`id`
[]

AUTHOR

xaicron <xaicron {at} cpan.org>

COPYRIGHT

Copyright 2012 - xaicron

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

SEE ALSO

SQL::Format