oracular (3) SQL::Abstract::Plugin::ExtraClauses.3pm.gz
NAME
SQL::Abstract::ExtraClauses - new/experimental additions to SQL::Abstract
SYNOPSIS
my $sqla = SQL::Abstract->new;
SQL::Abstract::ExtraClauses->apply_to($sqla);
WARNING
This module is basically a nursery for things that seem like a good idea to live in until we figure out
if we were right about that.
METHODS
apply_to
Applies the plugin to an SQL::Abstract object.
register_extensions
Registers the extensions described below
cb
For plugin authors, creates a callback to call a method on the plugin.
register
For plugin authors, registers callbacks more easily.
sqla
Available only during plugin callback executions, contains the currently active SQL::Abstract object.
NODE TYPES
alias
Represents a table alias. Expands name and column names with ident as default.
# expr
{ -alias => [ 't', 'x', 'y', 'z' ] }
# aqt
{ -alias => [
{ -ident => [ 't' ] }, { -ident => [ 'x' ] },
{ -ident => [ 'y' ] }, { -ident => [ 'z' ] },
] }
# query
t(x, y, z)
[]
as
Represents an sql AS. LHS is expanded with ident as default, RHS is treated as a list of arguments for
the alias node.
# expr
{ foo => { -as => 'bar' } }
# aqt
{ -as => [ { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
# query
foo AS bar
[]
# expr
{ -as => [ { -select => { _ => 'blah' } }, 't', 'blah' ] }
# aqt
{ -as => [
{ -select =>
{ select => { -op => [ ',', { -ident => [ 'blah' ] } ] } }
},
{ -alias => [ { -ident => [ 't' ] }, { -ident => [ 'blah' ] } ] },
] }
# query
(SELECT blah) AS t(blah)
[]
cast
# expr
{ -cast => [ { -ident => 'birthday' }, 'date' ] }
# aqt
{ -func => [
'cast', {
-as => [ { -ident => [ 'birthday' ] }, { -ident => [ 'date' ] } ]
},
] }
# query
CAST(birthday AS date)
[]
join
If given an arrayref, pretends it was given a hashref with the first element of the arrayref as the value
for 'to' and the remaining pairs copied.
Given a hashref, the 'as' key is if presented expanded to wrap the 'to'.
If present the 'using' key is expanded as a list of idents.
Known keys are: 'from' (the left hand side), 'type' ('left', 'right', or nothing), 'to' (the right hand
side), 'on' and 'using'.
# expr
{ -join => {
from => 'lft',
on => { 'lft.bloo' => { '>' => 'rgt.blee' } },
to => 'rgt',
type => 'left',
} }
# aqt
{ -join => {
from => { -ident => [ 'lft' ] },
on => { -op => [
'>', { -ident => [ 'lft', 'bloo' ] },
{ -ident => [ 'rgt', 'blee' ] },
] },
to => { -ident => [ 'rgt' ] },
type => 'left',
} }
# query
lft LEFT JOIN rgt ON lft.bloo > rgt.blee
[]
from_list
List of components of the FROM clause; -foo type elements indicate a pair with the next element; this is
easiest if I show you:
# expr
{ -from_list => [
't1', -as => 'table_one', -join =>
[ 't2', 'on', { 'table_one.x' => 't2.x' } ],
] }
# aqt
{ -join => {
from =>
{
-as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
},
on => { -op => [
'=', { -ident => [ 'table_one', 'x' ] },
{ -ident => [ 't2', 'x' ] },
] },
to => { -ident => [ 't2' ] },
type => undef,
} }
# query
t1 AS table_one JOIN t2 ON table_one.x = t2.x
[]
Or with using:
# expr
{ -from_list =>
[ 't1', -as => 'table_one', -join => [ 't2', 'using', [ 'x' ] ] ]
}
# aqt
{ -join => {
from =>
{
-as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
},
to => { -ident => [ 't2' ] },
type => undef,
using =>
{ -op => [ 'or', { -op => [ 'or', { -ident => [ 'x' ] } ] } ] },
} }
# query
t1 AS table_one JOIN t2 USING ( x )
[]
With oddities:
# expr
{ -from_list => [
'x', -join =>
[ [ 'y', -join => [ 'z', 'type', 'left' ] ], 'type', 'left' ],
] }
# aqt
{ -join => {
from => { -ident => [ 'x' ] },
to => { -join => {
from => { -ident => [ 'y' ] },
to => { -ident => [ 'z' ] },
type => 'left',
} },
type => 'left',
} }
# query
x LEFT JOIN ( y LEFT JOIN z )
[]
setops
Expanders are provided for union, union_all, intersect, intersect_all, except and except_all, and each
takes an arrayref of queries:
# expr
{ -union => [
{ -select => { _ => { -value => 1 } } },
{ -select => { _ => { -value => 2 } } },
] }
# aqt
{ -union => { queries => [
{ -select =>
{ select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
},
{ -select =>
{ select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
},
] } }
# query
(SELECT ?) UNION (SELECT ?)
[ 1, 2 ]
# expr
{ -union_all => [
{ -select => { _ => { -value => 1 } } },
{ -select => { _ => { -value => 2 } } },
{ -select => { _ => { -value => 1 } } },
] }
# aqt
{ -union => {
queries => [
{ -select =>
{ select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
},
{ -select =>
{ select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
},
{ -select =>
{ select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
},
],
type => 'all',
} }
# query
(SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?)
[ 1, 2, 1 ]
STATEMENT EXTENSIONS
group by clause for select
Expanded as a list with an ident default:
# expr
{ -select => { group_by => [ 'foo', 'bar' ] } }
# aqt
{ -select => { group_by =>
{
-op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ]
}
} }
# query
GROUP BY foo, bar
[]
having clause for select
Basic expr, just like where, given having is pretty much post-group-by where clause:
# expr
{ -select =>
{ having => { '>' => [ { -count => { -ident => 'foo' } }, 3 ] } }
}
# aqt
{ -select => { having => { -op => [
'>', { -func => [ 'count', { -ident => [ 'foo' ] } ] },
{ -bind => [ undef, 3 ] },
] } } }
# query
HAVING COUNT(foo) > ?
[ 3 ]
setop clauses
If a select query contains a clause matching any of the setop node types, clauses that appear before the
setop would in the resulting query are gathered together and moved into an inner select node:
# expr
{ -select => {
_ => '*',
from => 'foo',
order_by => 'baz',
union =>
{
-select => { _ => '*', from => 'bar', where => { thing => 1 } }
},
where => { thing => 1 },
} }
# aqt
{ -select => {
order_by => { -op => [ ',', { -ident => [ 'baz' ] } ] },
setop => { -union => { queries => [
{ -select => {
from => { -ident => [ 'foo' ] },
select => { -op => [ ',', { -ident => [ '*' ] } ] },
where => { -op => [
'=', { -ident => [ 'thing' ] },
{ -bind => [ 'thing', 1 ] },
] },
} }, ] },
{ -select => {
from => { -ident => [ 'bar' ] },
select => { -op => [ ',', { -ident => [ '*' ] } ] },
where => { -op => [
'=', { -ident => [ 'thing' ] },
{ -bind => [ 'thing', 1 ] },
} },
] } },
} }
# query
(SELECT * FROM foo WHERE thing = ?) UNION (
SELECT * FROM bar WHERE thing = ?
)
ORDER BY baz
[ 1, 1 ]
update from clause
Some databases allow an additional FROM clause to reference other tables for the data to update; this
clause is expanded as a normal from list, check your database for what is and isn't allowed in practice.
# expr
{ -update => {
_ => 'employees',
from => 'accounts',
set => { sales_count => { sales_count => { '+' => \1 } } },
where => {
'accounts.name' => { '=' => \"'Acme Corporation'" },
'employees.id' => { -ident => 'accounts.sales_person' },
},
} }
# aqt
{ -update => {
from => { -ident => [ 'accounts' ] },
set => { -op => [
',', { -op => [
'=', { -ident => [ 'sales_count' ] }, { -op => [
'+', { -ident => [ 'sales_count' ] },
{ -literal => [ 1 ] },
] },
] },
] },
target => { -ident => [ 'employees' ] },
where => { -op => [
'and', { -op => [
'=', { -ident => [ 'accounts', 'name' ] },
{ -literal => [ "'Acme Corporation'" ] },
] }, { -op => [
'=', { -ident => [ 'employees', 'id' ] },
{ -ident => [ 'accounts', 'sales_person' ] },
] },
] },
} }
# query
UPDATE employees SET sales_count = sales_count + 1 FROM accounts
WHERE (
accounts.name = 'Acme Corporation'
AND employees.id = accounts.sales_person
)
[]
delete using clause
Some databases allow an additional USING clause to reference other tables for the data to update; this
clause is expanded as a normal from list, check your database for what is and isn't allowed in practice.
# expr
{ -delete => {
from => 'x',
using => 'y',
where => { 'x.id' => { -ident => 'y.x_id' } },
} }
# aqt
{ -delete => {
target => { -op => [ ',', { -ident => [ 'x' ] } ] },
using => { -ident => [ 'y' ] },
where => { -op => [
'=', { -ident => [ 'x', 'id' ] },
{ -ident => [ 'y', 'x_id' ] },
] },
} }
# query
DELETE FROM x USING y WHERE x.id = y.x_id
[]
insert rowvalues and select clauses
rowvalues and select are shorthand for
{ from => { -select ... } }
and
{ from => { -values ... } }
respectively:
# expr
{ -insert =>
{ into => 'numbers', rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] }
}
# aqt
{ -insert => {
from => { -values => [
{ -row =>
[ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ]
},
{ -row =>
[ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ]
},
{ -row =>
[ { -bind => [ undef, 5 ] }, { -bind => [ undef, 6 ] } ]
},
] },
target => { -ident => [ 'numbers' ] },
} }
# query
INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?)
[ 1, 2, 3, 4, 5, 6 ]
# expr
{ -insert =>
{ into => 'numbers', select => { _ => '*', from => 'old_numbers' } }
}
# aqt
{ -insert => {
from => { -select => {
from => { -ident => [ 'old_numbers' ] },
select => { -op => [ ',', { -ident => [ '*' ] } ] },
} },
target => { -ident => [ 'numbers' ] },
} }
# query
INSERT INTO numbers SELECT * FROM old_numbers
[]
with and with_recursive clauses
These clauses are available on select/insert/update/delete queries; check your database for applicability
(e.g. mysql supports all four but mariadb only select).
The value should be an arrayref of name/query pairs:
# expr
{ -select => {
from => 'foo',
select => '*',
with => [ 'foo', { -select => { select => \1 } } ],
} }
# aqt
{ -select => {
from => { -ident => [ 'foo' ] },
select => { -op => [ ',', { -ident => [ '*' ] } ] },
with => { queries => [ [
{ -ident => [ 'foo' ] }, { -select =>
{ select => { -op => [ ',', { -literal => [ 1 ] } ] } }
},
] ] },
} }
# query
WITH foo AS (SELECT 1) SELECT * FROM foo
[]
A more complete example (designed for mariadb, (ab)using the fact that mysqloids materialise subselects
in FROM into an unindexed temp table to circumvent the restriction that you can't select from the table
you're currently updating:
# expr
{ -update => {
_ => [
'tree_table', -join => {
as => 'tree',
on => { 'tree.id' => 'tree_with_path.id' },
to => { -select => {
from => 'tree_with_path',
select => '*',
with_recursive => [
[ 'tree_with_path', 'id', 'parent_id', 'path' ],
{ -select => {
_ => [
'id', 'parent_id', { -as => [
{ -cast => { -as => [ 'id', 'char', 255 ] } },
'path',
] } ],
from => 'tree_table',
union_all => { -select => {
_ => [
't.id', 't.parent_id', { -as => [
{ -concat => [ 'r.path', \"'/'", 't.id' ] },
'path',
] },
],
from => [
'tree_table', -as => 't', -join => {
as => 'r',
on => { 't.parent_id' => 'r.id' },
to => 'tree_with_path',
},
],
} },
where => { parent_id => undef },
} },
],
} },
},
],
set => { path => { -ident => [ 'tree', 'path' ] } },
} }
# query
UPDATE
tree_table JOIN
(
WITH RECURSIVE
tree_with_path(id, parent_id, path) AS (
(
SELECT id, parent_id, CAST(id AS char(255)) AS path
FROM tree_table WHERE parent_id IS NULL
) UNION ALL (
SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path
FROM
tree_table AS t JOIN tree_with_path AS r ON
t.parent_id = r.id
)
)
SELECT * FROM tree_with_path
) AS tree
ON tree.id = tree_with_path.id
SET path = tree.path
[]