The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-QueryBuilder-OO-0.2.6
River stage zero No dependents
++
/ SQL::QueryBuilder::OO

NAME

SQL::QueryBuilder::OO - Object oriented SQL query builder

SYNOPSIS

  use SQL::QueryBuilder::OO;

  # Uses an existing DBI database handle
  sqlQuery::setup(-dbh => $dbh);

  # Database handle is created when necessary via a sub-routine
  sqlQuery::setup(-connect => sub {
      DBI->connect(...);
  });

  # Full syntax
  $sql = sqlQueryBase::select(qw(id title description), {name => 'author'})
      ->from('article')
      ->innerJoin('users', 'userId')
      ->leftJoin({'comments' => 'c'}, sqlCondition::EQ('userId', 'c.from'))
      ->where(sqlCondition::AND(
              sqlCondition::EQ('category')->bind($cat),
              sqlCondition::NE('hidden')->bind(1)))
      ->limit(10,20)
      ->groupBy('title')
      ->orderBy({'timestamp' => 'DESC'});

  $sth = sqlQuery::q($sql)->execute();
  $row = $sth->fetchAssoc();
  $sth->freeResource();

  # Overloaded operators

  $cond = sqlCondition::EQ('a', 'b') & !sqlCondition::IN('c')->bind([1,2,3]);
  print "$cond";
  # -> (`a` = `b` AND NOT(`c` IN(1,2,3)))

DESCRIPTION

This module provides for an object oriented way to create complex SQL queries while maintaining code readability. It supports conditions construction and bound query parameters. While the module is named SQL::QueryBuilder::OO, this name is actually not used when constructing queries. The two main packages to build queries are sqlQueryBase and sqlCondition. The package to execute them is sqlQuery.

The project is actually a port of PHP classes to construct queries used in one of my proprietary projects (which may explain the excessive use of the scope resolution operator (::) in the module's syntax).

Setting the module up

Module set-up is not optional; you may not be executing any queries, yet, an existing (or ad-hoc created) database handle is required for purposes of safely quoting interpolated values.

If at any point you're getting an "sqlQuery is not setup, yet." error, you forgot to use any one of the following statements.

Using an existing database handle

To use an existing DBI database handle, put this in your program's prolog:

  sqlQuery::setup(-dbh => $dbh);

Creating a database handle when needed

To create a new database handle when it's needed (ad-hoc), supply a subroutine that will be called once:

  sqlQuery::setup(-connect => sub {
      DBI->connect(...);
  });

Building queries

The package to provide builder interfaces is called sqlQueryBase and has these methods:

SELECT queries

select(COLUMNS...[, OPTIONS])

Creates a SELECT query object. Columns to select default to * if none are given. They are otherwise to be specified as a list of expressions that can be literal column names or HASH references with column aliases.

Column names are quoted where appropriate:

  # Build SELECT * query
  $all = sqlQueryBase::select();

  # Build SELECT ... query
  $sql = sqlQueryBase::select(
       # literal column names
          qw(id title),
       # column alias
          {'u.username' => 'author', timestamp => 'authored'},
       # SELECT specific options
          [qw(SQL_CACHE SQL_CALC_FOUND_ROWS)]);

The references returned from the above statements are blessed into an internal package. Those internal packages will not be documented here, since they may be subject to change. Their methods, however, are those of a valid SQL SELECT statement. When constructing queries you'll have to maintain the order of SQL syntax. This means, that the following will be treated as an error by Perl itself:

  $sql = sqlQueryBase::select()
          ->from('table')
          ->limit(10)
          ->where(...);

  Can't locate object method "where" via package "sqlSelectAssemble" at ...

The correct order would have been:

  $sql = sqlQueryBase::select()
          ->from('table')
          ->where(...)
          ->limit(10);

The following methods are available to construct the query further:

from(TABLES...)

This obviously represents the "FROM" part of a select query. It accepts a list of string literals as table names or table aliases:

  $sql = sqlQueryBase::select()->from('posts', {'user' => 'u'});
leftJoin(TABLE, CONDITION)
innerJoin(TABLE, CONDITION)
rightJoin(TABLE, CONDITION)

These methods extend the "FROM" fragment with a left, inner or right table join. The table name can either be a string literal or a HASH reference for aliasing table names.

The condition should either be an sqlCondition object (see "Creating conditions"):

  # SELECT * FROM `table_a` LEFT JOIN `table_b` ON(`column_a` = `column_b`)
  $sql = sqlQueryBase::select()
          ->from('table_a')
          ->leftJoin('table_b', sqlCondition::EQ('column_a', 'column_b'));

...or a string literal of a common column name for the USING clause:

  # SELECT * FROM `table_a` LEFT JOIN `table_b` USING(`id`)
  $sql = sqlQueryBase::select()
          ->from('table_a')
          ->leftJoin('table_b', 'id');
where(CONDITION)

This represents the "WHERE" part of a SELECT query. It will accept one object of the sqlCondition package (see "Creating conditions").

groupBy(COLUMNS...)

This represents the "GROUP BY" statement of a SELECT query.

having(CONDITION)

This represents the "HAVING" part of a SELECT query. It will accept one object of the sqlCondition package (see "Creating conditions").

orderBy(COLUMNS...)

This represents the "ORDER BY" statement of a SELECT query. Columns are expected to be string literals or HASH references (one member only) with ordering directions:

  $sql = sqlQueryBase::select()
          ->from('table')
          ->orderBy('id', {timestamp => 'DESC'}, 'title');
limit(COUNT[, OFFSET])

This represents the "LIMIT" fragment of a SELECT query. It deviates from the standard SQL expression, as the limit count is always the first argument to this method, regardless of a given offset. The first or both parameters may be undef to skip the LIMIT clause.

Creating conditions

Conditions can be used as a parameter for leftJoin, having, innerJoin, rightJoin or where. They are constructed with the sqlCondition package, whose methods are not exported due to their generic names. Instead, the "namespace" has to be mentioned for each conditional:

  $cond = sqlCondition::AND(
          sqlCondition::EQ('id')->bind(1337),
          sqlCondition::BETWEEN('stamp', "2013-01-06", "2014-03-31"));

Those are all operators:

Booleans

To logically connect conditions, the following to methods are available:

AND(CONDITIONS...)

Connect one or more conditions with a boolean AND.

OR(CONDITIONS...)

Connect one or more conditions with a boolean OR.

NOT(CONDITION)

Negate a condition with an unary NOT.

Relational operators

All relational operators expect a mandatory column name as their first argument and a second optional ride-hand-side column name.

If the optional second parameter is left out, the conditional can be bound (see "Binding parameters").

EQ(COLUMN[, RHS-COLUMN])

Equal to operator (=).

NE(COLUMN[, RHS-COLUMN])

Not equal to operator (!=).

LT(COLUMN[, RHS-COLUMN])

Less than operator (<).

GT(COLUMN[, RHS-COLUMN])

Greater than operator (>).

LTE(COLUMN[, RHS-COLUMN])

Less than or equal to operator (<=).

GTE(COLUMN[, RHS-COLUMN])

Greater than or equal to operator (>=).

SQL specific operators

BETWEEN(COLUMN, START, END)

Creates an "x BETWEEN start AND end" conditional.

IN(COLUMN)

Creates an "x IN(...)" conditional.

Note that, if bound, this method will croak if it encounters an empty list. This behavior is subject to change in future versions: the statement will be reduced to a "falsy" statement and a warning will be issued.

ISNULL(COLUMN)

Creates an "x IS NULL" conditional.

ISNOTNULL(COLUMN)

Creates an "x IS NOT NULL" conditional.

LIKE(COLUMN, PATTERN)

Creates an "x LIKE pattern" conditional.

Note that the pattern is passed unmodified. Beware of the LIKE pitfalls concerning the characters % and _.

NOTIN(COLUMN)

Creates an "x NOT IN(...)" conditional.

Convenience for sqlCondition::NOT(sqlCondition::IN('x')-bind([1,2,3]))>. Please refer to IN for caveats.

Binding parameters

An SQL conditional can be bound against a parameter via its bind() method:

  $cond = sqlCondition::AND(
          sqlCondition::EQ('id')->bind(1337),
          sqlCondition::NOT(
             sqlCondition::IN('category')->bind([1,2,3,4])));

  print $cond;                        # "`id` = ? AND NOT(`category` IN(?))"
  @args = $cond->gatherBoundArgs();   # (sqlValueInt(1337),sqlValueList([1,2,3,4]))

A special case are conditionals bound against undef (which is the equivalent to SQL NULL):

  $cat = undef;
  $cond = sqlCondition::OR(
          sqlCondition::EQ('author')->bind(undef),
          sqlCondition::NE('category')->bind($cat));

  print $cond;                        # `author` IS NULL OR `category` IS NOT NULL
  @args = $cond->gatherBoundArgs();   # ()

Since `author` = NULL would never be "true", the condition is replaced with the correct `author` IS NULL statement. (Note that the first conditional could actually be written sqlCondition::ISNULL('author'). The substitution is thus useful when binding against variables of unknown content).

Parameter conversion

Bound parameters are internally converted to a sub-class of sqlParameter. Since most scalar values are already converted automatically, a user might never need to employ any of those packages listed below. If more complex queries are desired, however, they just have to be used.

sqlValueDate
sqlValueDateTime

To bind a value and use its date or date/time representation, use:

  $cond->bind(new sqlValueDate()); # use current time, return YYYY-MM-DD
  $cond->bind(new sqlValueDateTime()); # use current time, return YYYY-MM-DD HH:MM:SS

  $tm = mktime(...);
  $cond->bind(new sqlValueDate($tm)); # use UNIX timestamp; return YYYY-MM-DD
  $cond->bind(new sqlValueDateTime($tm)); # use UNIX timestamp; return YYYY-MM-DD HH:MM:SS

  $str = "Wed, 6 Jan 82 02:20:00 +0100";
  $cond->bind(new sqlValueDate($str)); # use textual representation; return YYYY-MM-DD
  $cond->bind(new sqlValueDateTime($str)); # use textual representation; return YYYY-MM-DD HH:MM:SS

The latter variants using textual representation use Date::Parse to convert a string into a UNIX timestamp. Refer to Date::Parse to learn about supported formats.

sqlValueFloat

To bind a value as a floating point number (with optional precision), use:

  $cond->bind(new sqlValueFloat($number, 4)); # Precision of four; eight is the default

Scalars looking like floating point numbers are automatically converted to this package when using bind().

sqlValueInt

To bind a value as an integer, use:

  $cond->bind(new sqlValueInt($number));

Scalars looking like (un)signed integers are automatically converted to this package when using bind().

sqlValueList

To create a safe list of values, use:

  sqlCondition::IN('column')->bind(new sqlValueList([1,2,3,4]));

Scalars that are ARRAYREFs are automatically converted to this package when using bind(). All elements of the list are subject to conversion as well.

sqlValueLiteral

To include a complex statement as-is, use:

  sqlCondition::EQ('a')->bind(new sqlValueLiteral('IF(`b` = `c`, 0, 1)'));
  # -> `a` = IF(`b` = `c`, 0, 1)

Please do not abuse this to interpolate values into the query: this would pose a security risk since these values aren't subject to "escaping".

sqlValueNull

To represent MySQL's NULL, use:

  $cond->bind(new sqlValueNull());

Scalars evaluating to undef are automatically converted to this package when using bind().

sqlValueString

To bind a value as a string, use:

  $cond->bind(new sqlValueString($value));

All scalars that aren't undef, integers, or floats are converted to this package when using bind(). The value is properly escaped before query interpolation.

Named or index-based parameters

The module supports both named or index-based parameters; just not both in a mix:

  # Index-based parameters
  $query = sqlQueryBase::select()
      ->from('table')
      ->where(sqlCondition::EQ('id')->bind(1337));
  print "$query"; # -> SELECT * FROM `table` WHERE `id` = ?

  # Named parameters
  $query = sqlQueryBase::select()
      ->from('table')
      ->where(sqlCondition::EQ('id', ':value'));
  print "$query"; # -> SELECT * FROM `table` WHERE `id` = :value

Index-based parameters can be bound to the corresponding sqlCondition when it's created and are later interpolated. Name based parameters make for cleaner query creation statements but require an additional step prior to executing the query:

  $query = sqlQueryBase::select()
      ->from('table')
      ->where(sqlCondition::EQ('id', ':value'));
  $res = sqlQuery->new($query)
      ->setParameters({value => 1337}) # assign name-value pairs here
      ->execute();

Conditions with overloaded operators

To regain a little readability, the binary operators & and | and the unary ! have been overloaded to substitute for sqlCondition::AND, sqlCondition::OR and sqlCondition::NOT respectively.

This:

  $cond = sqlCondition::AND(
          sqlCondition::EQ('a', 'b'),
          sqlCondition::OR(
              sqlCondition::NOT(sqlCondition::LIKE('d', "%PATTERN%")),
              sqlCondition::C('UNIX_TIMESTAMP(`column`) >= DATE_SUB(NOW(), INTERVAL 7 DAY)')));

is the same as this:

  $cond = sqlCondition::EQ('a', 'b')
        & (!sqlCondition::LIKE('d', "%PATTERN%")
        | 'UNIX_TIMESTAMP(`column`) >= DATE_SUB(NOW(), INTERVAL 7 DAY)');

Executing queries

The package to execute queries with is sqlQuery. Depending on its usage, it returns an sqlQueryResult package instance:

  $query = sqlQuery->new($sql);
  $result = $query->execute();
  $row = $result->fetchAssoc();
  $result->freeResource();

Fetching results

A query result of the sqlQueryResult package has these methods:

fetchAll()

Fetch all rows, return a list of HASHREFs.

fetchArray()

Fetch one row, return the values as a list.

fetchAssoc()

Fetch one row, return it as a HASHREF.

fetchColumn($name)

Fetch one row, return its named column $name or index-based column (from zero).

fetchRow() (alias)

Fetch one row, return it as a HASHREF.

Other methods

The following are other methods of sqlQueryResult unrelated to fetching data:

freeResource()

Finishes an executed statement, freeing its resources.

getNumRows()
numRows()

Return number of rows in a SELECT query.

EXAMPLES

Execute a single statement

Index-based parameters

  sqlQuery::exec('UPDATE `foo` SET `bar` = ?', 'splort'); # returns number of affected rows

Named parameters

  sqlQuery::exec('UPDATE `foo` SET `bar` = :bar', {
      bar => 'splort'
  }); # returns number of affected rows

TODO

  • Implement support for UPDATE, INSERT, REPLACE and DELETE statements.

  • Implement support for UNION.

DEPENDENCIES

AUTHOR

Oliver Schieche <schiecheo@cpan.org>

http://perfect-co.de/

$Id: OO.pm 55 2016-03-03 07:02:51Z schieche $

LICENSE AND COPYRIGHT

Copyright (C) 2013-2015 Oliver Schieche.

This software is a free library. You can modify and/or distribute it under the same terms as Perl itself.