Fey::SQL - Documentation on SQL generation with Fey and SQL object factory
version 0.39
my $sql = Fey::SQL->new_select(); $sql->select( @columns );
This module mostly exists to provide documentation and a factory interface for making SQL statement objects.
For convenience, loading this module loads all of the Fey::SQL::* classes, such as Fey::SQL::Select, Fey::SQL::Delete, etc.
Fey::SQL::*
This class acts as a factory for the various SQL statement classes, such as Fey::SQL::Select or Fey::SQL::Update. This is simply sugar which makes it easy to replace Fey::SQL with a subclass, either for your application or for a specific DBMS.
Fey::SQL
Returns a new Fey::SQL::Select object.
Fey::SQL::Select
Returns a new Fey::SQL::Insert object.
Fey::SQL::Insert
Returns a new Fey::SQL::Update object.
Fey::SQL::Update
Returns a new Fey::SQL::Delete object.
Fey::SQL::Delete
Returns a new Fey::SQL::Where object.
Fey::SQL::Where
Returns a new Fey::SQL::Union object.
Fey::SQL::Union
Returns a new Fey::SQL::Intersect object.
Fey::SQL::Intersect
Returns a new Fey::SQL::Except object.
Fey::SQL::Except
This documentation covers the clauses in SQL queries which are shared across different types of queries, including WHERE, ORDER BY, and LIMIT. For SQL clauses that are specific to one type of query, see the appropriate subclass. For example, for SELECT clauses, see the Fey::SQL::Select class documentation.
WHERE
ORDER BY
LIMIT
SELECT
Many types of queries allow WHERE clauses via the a where() method. The method accepts several different types of parameters:
where()
Comparing a column to a given value ...
# WHERE Part.size = $value} $sql->where( $size, '=', $value ); # WHERE Part.size = AVG(Part.size); $sql->where( $size, '=', $avg_size_function ); # WHERE Part.size = ? $sql->where( $size, '=', $placeholder ); # WHERE User.user_id = Message.user_id $sql->where( $user_id, '=', $other_user_id );
The left-hand side of a conditional does not need to be a column object, it could be a function or anything that produces valid SQL.
my $length = Fey::Literal::Function->new( 'LENGTH', $name ); # WHERE LENGTH(Part.name) = 10 $sql->where( $length, '=', 10 );
The second parameter in a conditional can be any comparison operator that produces valid SQL:
# WHERE Message.body LIKE 'hello%' $sql->where( $body, 'LIKE', 'hello%' ); # WHERE Part.quantity > 10 $sql->where( $quantity, '>', 10 );
If you use a comparison operator like BETWEEN or (NOT) IN, you can pass more than three parameters to where().
BETWEEN
(NOT) IN
# WHERE Part.size BETWEEN 4 AND 10 $sql->where( $size, 'BETWEEN', 4, 10 ); # WHERE User.user_id IN (1, 2, 7, 9) $sql->where( $user_id, 'IN', 1, 2, 7, 9 );
You can also pass a subselect when using IN.
IN
my $select = $sql->select(...); # WHERE User.user_id IN ( SELECT user_id FROM ... ) $sql->where( $user_id, 'IN', $select );
If you use =, !=, or <> as the comparison and the right-hand side is undef, then the generated query will use IS NULL or IS NOT NULL, as appropriate:
=
!=
<>
undef
IS NULL
IS NOT NULL
# WHERE Part.name IS NULL $sql->where( $name, '=', undef ); # WHERE Part.name IS NOT NULL $sql->where( $name, '!=', undef );
Note that if you use a placeholder object in this case, then the query will not be transformed into an IS (NOT) NULL expression, since the value of the placeholder is not known when the SQL is being generated.
IS (NOT) NULL
You can also use and() instead of where if you like the look ...
and()
$sql->where( $size, '=', $value ) ->and ( $quantity, '>', 10 );
The and() method is just sugar, since by default, multiple calls to where() end up concatenated with an AND in the resulting SQL.
AND
You can pass the strings "and" and "or" to the where() method in order to create complex boolean conditions. When you call where() with multiple comparisons in a row, an implicit "and" is added between each one.
# WHERE Part.size > 10 OR Part.size = 5 $sql->where( $size, '>', 10 ); $sql->where( 'or' ); $sql->where( $size, '=', 5 ); # WHERE Part.size > 10 AND Part.size < 20 $sql->where( $size, '>', 10 ); # there is an implicit $sql->where( 'and' ) here ... $sql->where( $size, '<', 10 );
Basically, any operator should work, and there is no check that a particular operator is valid.
Some operators are special-cased, specifically BETWEEN, IN, and NOT IN. If you use BETWEEN as the operator, you are expected to pass two items after it. If you use IN or NOT IN, you can pass as many items as you need to on the right hand side.
NOT IN
When you call where() to do a comparison, you can pass any of the following types of things:
An object which has an is_comparable() method that returns true
is_comparable()
This includes objects which do the Fey::ColumnLike role: Fey::Column and Fey::Column::Alias. A column only returns true for is_comparable() when it is actually attached to a table.
Objects which do the Fey::Role::Comaprable role: Fey::SQL::Select, Fey::SQL::Union, Fey::SQL::Intersection, and Fey::SQL::Except always return true for is_comparable().
If you try to compare something to something that returns a data set, you must be using an equality comparison operator (=, !=, etc), IN, or, NOT IN.
Also, all Fey::Literal subclasses return true for is_comparable(): Fey::Literal::Function, Fey::Literal::Null, Fey::Literal::Number, Fey::Literal::String, and Fey::Literal::Term.
Finally, you can pass a Fey::Placeholder object.
An unblessed non-reference scalar
This can be undef, a string, or a number. This scalar will be passed to Fey::Literal->new_from_scalar() and converted into an appropriate Fey::Literal object.
Fey::Literal->new_from_scalar()
An object which returns true for overload::Overloaded($object)
overload::Overloaded($object)
This will be stringified ($object .= q{}) and passed to Fey::Literal->new_from_scalar().
$object .= q{}
Fey does the right thing for NULLs used in equality comparisons, generating IS NULL and IS NOT NULL as appropriate.
You can pass the strings "(" and ")" to the where() method in order to create subgroups.
# WHERE Part.size > 10 # AND ( User.name = 'Widget' # OR # User.name = 'Grommit' ) $sql->where( $size, '>', 10 ); $sql->where( '(' ); $sql->where( $name, '=', 'Widget' ); $sql->where( 'or' ); $sql->where( $name, '=', 'Grommit' ); $sql->where( ')' );
Many types of queries allow ORDER BY clauses via the order_by() method. This method accepts a list of items. The items in the list may be columns, functions, terms, or sort directions ("ASC" or "DESC"). The sort direction can also specify "NULLS FIRST" or "NULLS LAST".
order_by()
# ORDER BY Part.size $sql->order_by( $size ); # ORDER BY Part.size DESC $sql->order_by( $size, 'DESC' ); # ORDER BY Part.size DESC, Part.name ASC $sql->order_by( $size, 'DESC', $name, 'ASC' ); # ORDER BY Part.size ASC NULLS FIRST $sql->order_by( $size, 'ASC NULLS FIRST' ); my $length = Fey::Literal::Function->new( 'LENGTH', $name ); # ORDER BY LENGTH( Part.name ) ASC $sql->order_by( $length, 'ASC' );
If you pass a function literal to the order_by() method and the literal was used previously in the select clause, then an alias is used in the ORDER BY clause.
my $length = Fey::Literal::Function->new( 'LENGTH', $name ); $sql->select($length); # SELECT LENGTH(Part.name) AS FUNCTION0 ... # ORDER BY FUNCTION0 ASC $sql->order_by( $length, 'ASC' );
Many types of queries allow LIMIT clauses via the limit() method. This method accepts two parameters, with the second being optional.
limit()
The first parameter is the number of items. The second, optional parameter, is the offset for the limit clause.
# LIMIT 10 $sql->limit( 10 ); # LIMIT 10 OFFSET 20 $sql->limit( 10, 20 ); # OFFSET 20 $sql->limit( undef, 20 );
By default, whenever you pass a non-object value where a placeholder could go, the SQL class replaces this with a placeholder and stores the value as a bind parameter. This applies to things like WHERE and HAVING clauses, as well as the VALUES clause of an INSERT, and the SET clause of an UPDATE.
HAVING
VALUES
INSERT
SET
UPDATE
You can retrieve the bind parameters by calling $sql->bind_params(). These will be returned in the proper order for passing to DBI's execute() method.
$sql->bind_params()
DBI
execute()
If you do not want values automatically converted to placeholders, you can turn this behavior off by setting auto_placeholders to a false value when creating the object:
auto_placeholders
my $select = Fey::SQL->new_select( auto_placeholders => 0 );
In this case, values will be quoted as needed and inserted directly into the generated SQL.
Every SQL object has a clone() method. This is useful if you want to have an object that you use as the base for multiple queries.
clone()
my $user_select = Fey::SQL->new_select( $user_table ) ->from( $user_table); my $select_new = $user_select->clone() ->where( $creation_column, '>=', $six_months_ago ); my $select_old $user_select->clone() ->where( $creation_column, '<', $six_months_ago );
Any method which accepts a plain scalar can also take an overloaded object that overloads stringification or numification. This includes WHERE clause comparisons, VALUES in an INSERT, and SET clauses in an UPDATE.
See Fey for details on how to report bugs.
Dave Rolsky <autarch@urth.org>
This software is Copyright (c) 2011 by Dave Rolsky.
This is free software, licensed under:
The Artistic License 2.0 (GPL Compatible)
To install Fey, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Fey
CPAN shell
perl -MCPAN -e shell install Fey
For more information on module installation, please visit the detailed CPAN module installation guide.