-
-
16 Jun 2015 03:55:50 UTC
- Distribution: Fey
- Module version: 0.43
- Source (raw)
- Browse (raw)
- Changes
- Homepage
- How to Contribute
- Repository
- Issues (6)
- Testers (895 / 4 / 0)
- Kwalitee
Bus factor: 1- 97.59% Coverage
- License: artistic_2
- Activity
24 month- Tools
- Download (88.18KB)
- MetaCPAN Explorer
- Permissions
- Subscribe to distribution
- Permalinks
- This version
- Latest version
and 5 contributors-
Aristotle Pagaltzis
-
hdp@glaive.weftsoar.net
-
hdp@localhost
-
hdp@rook.opensourcery.com
-
Oliver Charles
- Dependencies
- Carp
- Exception::Class
- Exporter
- List::AllUtils
- Moose
- Moose::Role
- Moose::Util::TypeConstraints
- MooseX::Params::Validate
- MooseX::Role::Parameterized
- MooseX::SemiAffordanceAccessor
- MooseX::StrictConstructor
- MooseX::Types
- MooseX::Types::Combine
- MooseX::Types::Moose
- Scalar::Util
- Task::Weaken
- Tie::IxHash
- base
- namespace::autoclean
- overload
- strict
- warnings
- Reverse dependencies
- CPAN Testers List
- Dependency graph
Take me over?
The maintainer of this distribution is looking for someone to take over! If you're interested then please contact them via email.NAME
Fey::SQL - Documentation on SQL generation with Fey and SQL object factory
VERSION
version 0.43
SYNOPSIS
my $sql = Fey::SQL->new_select(); $sql->select( @columns );
DESCRIPTION
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.METHODS
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->new_select()
Returns a new
Fey::SQL::Select
object.Fey::SQL->new_insert()
Returns a new
Fey::SQL::Insert
object.Fey::SQL->new_update()
Returns a new
Fey::SQL::Update
object.Fey::SQL->new_delete()
Returns a new
Fey::SQL::Delete
object.Fey::SQL->new_where()
Returns a new
Fey::SQL::Where
object.Fey::SQL->new_union()
Returns a new
Fey::SQL::Union
object.Fey::SQL->new_intersect()
Returns a new
Fey::SQL::Intersect
object.Fey::SQL->new_except()
Returns a new
Fey::SQL::Except
object.CREATING SQL
This documentation covers the clauses in SQL queries which are shared across different types of queries, including
WHERE
,ORDER BY
, andLIMIT
. For SQL clauses that are specific to one type of query, see the appropriate subclass. For example, forSELECT
clauses, see the Fey::SQL::Select class documentation.WHERE Clauses
Many types of queries allow
WHERE
clauses via the awhere()
method. The method accepts several different types of parameters:Comparisons
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 towhere()
.# 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
.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 isundef
, then the generated query will useIS NULL
orIS NOT NULL
, as appropriate:# 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.You can also use
and()
instead of where if you like the look ...$sql->where( $size, '=', $value ) ->and ( $quantity, '>', 10 );
The
and()
method is just sugar, since by default, multiple calls towhere()
end up concatenated with anAND
in the resulting SQL.Boolean AND/OR
You can pass the strings "and" and "or" to the
where()
method in order to create complex boolean conditions. When you callwhere()
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 );
What Comparison Operators Are Valid?
Basically, any operator should work, and there is no check that a particular operator is valid.
Some operators are special-cased, specifically
BETWEEN
,IN
, andNOT IN
. If you useBETWEEN
as the operator, you are expected to pass two items after it. If you useIN
orNOT IN
, you can pass as many items as you need to on the right hand side.What Can Be Compared?
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 trueThis includes objects which do the Fey::Role::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::Comparable role: Fey::SQL::Select, Fey::SQL::Union, Fey::SQL::Intersect, 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 toFey::Literal->new_from_scalar()
and converted into an appropriate Fey::Literal object.An object which returns true for
overload::Overloaded($object)
This will be stringified (
$object .= q{}
) and passed toFey::Literal->new_from_scalar()
.
NULL In Comparisons
Fey does the right thing for NULLs used in equality comparisons, generating
IS NULL
andIS NOT NULL
as appropriate.Subgroups
You can pass the strings "(" and ")" to the
where()
method in order to create subgroups.# WHERE Part.size > 10 # AND ( Part.name = 'Widget' # OR # Part.name = 'Grommit' ) $sql->where( $size, '>', 10 ); $sql->where( '(' ); $sql->where( $name, '=', 'Widget' ); $sql->where( 'or' ); $sql->where( $name, '=', 'Grommit' ); $sql->where( ')' );
ORDER BY Clauses
Many types of queries allow
ORDER BY
clauses via theorder_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 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 theORDER 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' );
LIMIT Clauses
Many types of queries allow
LIMIT
clauses via thelimit()
method. This method accepts two parameters, with the second being optional.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 );
Bind Parameters
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
andHAVING
clauses, as well as theVALUES
clause of anINSERT
, and theSET
clause of anUPDATE
.You can retrieve the bind parameters by calling
$sql->bind_params()
. These will be returned in the proper order for passing toDBI
'sexecute()
method.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: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.
Cloning
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.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 );
Overloaded Objects as Parameters
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 anINSERT
, andSET
clauses in anUPDATE
.BUGS
See Fey for details on how to report bugs.
AUTHOR
Dave Rolsky <autarch@urth.org>
COPYRIGHT AND LICENSE
This software is Copyright (c) 2011 - 2015 by Dave Rolsky.
This is free software, licensed under:
The Artistic License 2.0 (GPL Compatible)
Module Install Instructions
To install Fey, copy and paste the appropriate command in to your terminal.
cpanm Fey
perl -MCPAN -e shell install Fey
For more information on module installation, please visit the detailed CPAN module installation guide.