NAME

Fey::SQL - Documentation on SQL generation with Fey and SQL object factory

VERSION

version 0.40

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, 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 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().

  # 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 );

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, 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.

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 true

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.
An object which returns true for overload::Overloaded($object)

This will be stringified ($object .= q{}) and passed to Fey::Literal->new_from_scalar().

NULL In Comparisons

Fey does the right thing for NULLs used in equality comparisons, generating IS NULL and IS 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 ( 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"). 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 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 );

  # 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 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.

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 an INSERT, and SET clauses in an UPDATE.

BUGS

See Fey for details on how to report bugs.

AUTHOR

Dave Rolsky <autarch@urth.org>

COPYRIGHT AND LICENSE

This is free software, licensed under:

  The Artistic License 2.0 (GPL Compatible)