NAME
Fey::SQL - Documentation on SQL generation with Fey and SQL object factory
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.
CREATING SQL
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 Clauses
Many types of queries allow WHERE clauses via the a where() 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 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().
# 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 is undef, then the generated query will use IS NULL or IS 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 to where() end up concatenated with an AND 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 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 );Subgroups
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( ')' );ORDER BY Clauses
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").
# 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' );
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' );LIMIT Clauses
Many types of queries allow LIMIT clauses via the limit() 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 );
# LIMIT 0 OFFSET 20
$sql->limit( 0, 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 and HAVING clauses, as well as the VALUES clause of an INSERT, and the SET clause of an 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.
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.
AUTHOR
Dave Rolsky, <autarch@urth.org>
BUGS
See Fey for details on how to report bugs.
COPYRIGHT & LICENSE
Copyright 2006-2008 Dave Rolsky, All Rights Reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.