=head1 NAME
SQL::DB - SQL database interface for Perl (DEPRECATED)
=head1 VERSION
0.971.2. Development release.
=head1 SYNOPSIS
use SQL::DB ':all';
my $db = SQL::DB->connect( 'dbi:...', 'username', 'password' );
# The very simple stuff
my $success = $db->insert(
into => 'actors',
values => { id => 1, name => 'John Smith' },
);
my $count = $db->update(
'actors',
set => { name => 'Jack Smith' },
where => { id => 1, name => 'John Smith' },
);
my $count = $db->delete(
from => 'actors',
where => { actor_id => 1, last_name => 'Jones' },
);
my $row = $db->select(
[ 'id', 'name' ],
from => 'actors',
where => { id => 1 },
);
print $row->id . ':' . $row->name . "\n";
# The do-any-SQL-you-want stuff
my $people = $db->irow('people');
$db->do(
insert_into => $people->( 'id', 'name' );
sql_values( 1, 'A Name' ),
);
# Anyone celebrating a birthday today gets a raise, with the
# calculation done inside the database.
my $people = $db->urow('people');
$db->do(
update => $people,
set => $people->salary( $people->salary * 2 ),
where => $people->dob == $today,
);
my $purchases = $db->srow('purchases');
my $row = $db->fetch1(
select => [ $people->name, $ps->pid ],
from => $people,
left_join => $purchases,
on => $purchases->id == $people->id,
where => $people->id->in( 1, 2, 3 )
. AND
. $people->name->like('%paul%'),
limit => 1,
offset => 1,
);
# then do stuff with $row->pid, $row->name etc
my @rows = $db->fetch(
select => [ sql_coalesce( $p->pid, $p->cid )->as('pcid') ],
from => $p,
where => $p->cid->is_not_null,
);
# coalesce column is available as $row->pcid
my $iter = $db->iter( @query ... );
while ( my $row = $iter->next ) {
print $row->column(), $row->some_other_column;
}
# If you want the data your own way you can still use the query
# syntax:
my $sth = $db->sth(@query);
map { print join( ',', @$_ ) . "\n" } $sth->fetchall_arrayref;
# Transactions provided by DBIx::Connector
$db->txn(
sub {
# multiple statements
}
);
=head1 DESCRIPTION
NOTE: This module is UNSUPPORTED, UNMAINTAINED, and will be REMOVED
from CPAN at the end of 2015. If you found the SQL::DB interface suited
your style of DBI/SQL interaction you may find the L<DBIx::ThinSQL>
module a suitable replacement.
B<SQL::DB> is an extension to the Perl Database Interface (L<DBI>). It
is designed for complicated queries and efficient access to results.
With an API that lets you easily write almost-raw SQL, SQL::DB gives
you unfettered access to the power and flexibility of your underlying
database. It aims to be a tool for programmers who want their databases
to work just as hard as their Perl scripts.
SQL::DB gives you access to aggregate expressions, joins, nested
selects, unions and database-side operator invocations. Transactional
support is provided via L<DBIx::Connector>. Security conscious coders
will be pleased to know that all user-supplied values are bound
properly using L<DBI> "bind_param()". Binding binary data is handled
transparently across different database types.
SQL::DB offers a couple of very simple Create, Retrieve, Update and
Delete (CRUD) action methods. These are designed to get you up and
running quickly when your query data is already inside a hash. The
methods are abstractions of the real API, but should still read as much
as possible like SQL.
Although rows can be retrieved from the database as simple objects,
SQL::DB does not attempt to be an Object-Relational-Mapper (ORM). There
are no auto-inflating columns or automatic joins and the code size and
speed reflect the lack of complexity.
SQL::DB uses the light-weight L<Log::Any> for logging.
=head1 QUERY FORMAT
An SQL::DB query is composed from a list of scalars, schema objects,
expressions, and arrays as follows.
=over
=item Scalars
Scalars are assumed to be SQL keywords and are accepted more or less
unchanged (uppercased with underscores converted to spaces).
Perl SQL
---- ---
'select' SELECT
'outer_join' OUTER JOIN
=item Schema Objects
and DELETE. A urow object maps to a table in the database. It has a
method for each of the columns in the table which return
column-objects. These column-objects in turn have further methods which
map to various SQL statements. The table and column objects are used in
positions as appropriate for their types.
Perl SQL
---- ---
$table table (for UPDATE statements)
$table table0 (for SELECT statements)
$table->col > 1 table0.col > ?
$table->col->is_not_null table0.col IS NULL
$table->col->asc table0.col ASC
$table->col->between(1,5) table0.col BETWEEN(?,?)
=item Expressions
Expressions are built using schema objects (described below) that hold
extra information such as table/column names and types, your
application's input values, and their relationships.
Perl SQL
---- ---
$table->col == 1 table0.col = ?
($table->col == 1) .AND.
($table.col2 = ?
$table->col > 1 table0.col > ?
$table->col == table.col =
$table->col2 - $table->col3 table.col2 - table.col3
=item Arrays
Arrays (array-refs) can contain scalars and expressions which will
appear in the final SQL joined together by commas (',').
Perl SQL
---- ---
select => [1,2,3] SELECT ?,?,?
set => [$t->c1 == 1, $t->c2 == 2 ] SET t->c1 = ?, t.c2 = ?
=item Functions
Arrays (array-refs) can contain scalars and expressions which will
appear in the final SQL joined together by commas (',').
Perl SQL
---- ---
sql_count($table->col) COUNT(table0.col)
sql_values('a', 'b', 'c') VALUES(?,?,?)
=back
=head1 CONSTRUCTORS
=over
=item new( %args )
Connect to a database and return a new SQL::DB handle. Arguments are as
follows:
=over
=item dsn
The data source name ('dbi:...'). Required.
=item username
The database authentication username.
=item password
The database authentication password.
=item attr
A hash-reference of L<DBI> handle attributes. RaiseError and
AutoCommit are always set to 1 and cannot be overridden. PrintError
defaults to 0. ChopBlanks defaults to 1. pg_enable_utf8,
sqlite_unicode, and mysql_enable_utf8 default to 1.
=item cache_sth
Whether to use the L<DBI> prepare_cached() method. See ATTRIBUTES
below.
=item table_info
A reference to a hash containing the table information for the
database. This is typically the value returned from an earlier call to
the C<deployed_table_info> method of L<SQL::DBx::Deploy>.
Use of this parameter is not necessary for L<SQL::DB> applications, but
could be an important optimisation to minimize database calls in
applications with more than a trivial number of tables. This is more
interesting for remote database engines such as PostgreSQL with a
higher query latency than for local database engines such as SQLite.
The standard usage scenario is as follows: After any change to the
table/column structure of the database you can the save table
information to a file:
use SQL::DB;
use SQL::DBx::Deploy;
use YAML::Tiny;
my $db = SQL::DB->connect(...);
DumpFile('table_info.yaml', $db->deployed_table_info);
Then in your application code you set the C<table_info> parameter when
you instantiate your SQL::DB object:
package MyApp;
use SQL::DB;
use YAML::Tiny;
my $db = SQL::DB->new(
dsn => 'dbi:Pg:dbname=myapp',
dbuser => 'username',
dbpass => '******',
table_info => LoadFile('table_info.yaml'),
);
Now any calls to "urow" or "srow" on the $db object will not need to
query the database to know the table structure.
=back
=item connect( $dsn, [ $username, $password, $attr ] )
This is a convenience wrapper around new() for those who prefer
L<DBI>-style construction.
=back
=head1 ATTRIBUTES
=over 4
=item conn -> L<DBIx::Connector>
The handle connecting us to the database. Read-only.
=item dbd -> Str
The database driver name (Pg, SQLite, etc). Read-only.
=item schema -> L<SQL::DB::Schema>
The schema definition associated with this connection. Read-only.
=item cache_sth <-> Bool
If true then the L<DBI> prepare_cached() method will be used for
creating statement handles. Otherwise prepare() will be used.
=back
=head1 METHODS
=over 4
=item irows( @tables ) -> @coderef
Returns coderefs for generating table/column strings. Calling the
coderefs with column name arguments returns a string of the form
'table(col1,col2)'.
These refs can be used with the do() method for INSERT queries.
=item irow( $table ) => &coderef
A singular/scalar version of C<irows>.
=item urows( @tables ) -> @URow
Returns schema objects representing database tables/rows for
update-style SQL.
These objects have a method for each table column. Each column object
has the following methods: is_null(), is_not_null(), in(), not_in(),
between(), not_between(), as(), like(), asc(), and desc() (see
L<SQL::DB::Expr> for details).
These objects can be used with the do() method for UPDATE and DELETE
queries.
=item urow( $table ) => $SQL::DB::Expr
A singular/scalar version of C<urows>.
=item srows( @tables ) -> @SRow
Returns schema objects representing database tables/rows for
select-style SQL.
These objects have a method for each table column. Each column object
has the following methods: is_null(), is_not_null(), in(), not_in(),
between(), not_between(), as(), like(), asc(), and desc() (see
L<SQL::DB::Expr> for details).
These objects can be used with the do(), sth(), fetch(), fetch1(), and
iter() methods for SELECT queries.
=item srow( $table ) => $SQL::DB::Expr
A singular/scalar version of C<srows>.
=item prepare( @query ) -> ($sth, $query)
Prepares @query using L<DBI> prepare() and returns a L<DBI::st>
statement handle and the L<SQL::DB::Expr> object representing the
query. Croaks on error.
You need to call execute() on the statement handle yourself afterwards.
=item prepare_cached( @query ) -> ($sth, $query)
Prepares @query using L<DBI> prepare_cached() and returns a L<DBI::st>
statement handle and the L<SQL::DB::Expr> object representing the
query. Croaks on error.
You need to call execute() on the statement handle yourself afterwards.
=item do(@query) -> $count
Prepares (a INSERT, UPDATE or DELETE) @query using either prepare() or
prepare_cached() (depending on the "cache_sth" attribute) and
execute()'s the associated L<DBI::st> statement handle. Croaks on
error.
Returns the value of the execute() call. The result is typically the
number of rows affected (but this is driver dependent).
=item sth( @query ) -> DBI::st
Prepares (a SELECT) @query using either prepare() or prepare_cached()
(depending on the "cache_sth" attribute) then execute()s and returns
the associated L<DBI::st> statement handle. Croaks on error.
This method is useful if you want to obtain the data with one of the
L<DBI> fetchrow_hashref(), fetchall_arrayref() etc methods.
=item object(@query) -> $object
Prepares (a SELECT) @query using either prepare() or prepare_cached()
(depending on the "cache_sth" attribute) and execute()'s it. Croaks on
error.
Returns a single array-based object and closes the underlying statement
handle. The object has accessor methods for each column selected.
=item objects(@query) -> @objects
Prepares (a SELECT) @query using either prepare() or prepare_cached()
(depending on the "cache_sth" attribute) and execute()'s it. Croaks on
error.
Returns a list of array-based objects and closes the underlying
statement handle. Each object has accessor methods for each column
selected.
Be aware that calling this method can consume large amounts of memory
if there are lots of rows to be retrieved. Consider using iter()
instead.
=item fetch1
An alias for object().
=item fetch
An alias for objects().
=item hash
Same as for object() but returns a hashref instead.
=item hashes
Same as for objects() but returns hashrefs instead.
=item array
Same as for object() but returns an arrayref instead.
=item arrays
Same as for objects() but returns arrayrefs instead.
=item iter( @query ) -> L<SQL::DB::Iter>
Prepares (a SELECT) @query using either prepare() or prepare_cached()
(depending on the "cache_sth" attribute) and execute()'s it. Croaks on
error.
Returns a cursor/iteration object with next() and all() methods for
retrieving array-based objects. Each object has accessor methods for
each column selected.
=item txn(&coderef)
Runs the Perl code in &coderef as an SQL transaction. If &coderef does
not raise any exceptions then the transaction is commited, otherwise it
is rolled back. See L<DBIx::Connector> for details.
=item current_timestamp -> Str
Returns the current UTC timestamp as a string in the form 'yyyy-mm-dd
hh:mm:ssZ'. The same value is always returned during a transaction.
=item query_as_string($sql, @bind_values) -> Str
Pretty print an SQL query by inserting the bind values into the SQL
itself.
=item insert(into => $table, values => \%val) -> Int
Insert a row into the database and return the number of rows affected.
=item update($table, set => \%values, where => \%expr) -> Int
Update rows in the database and return the number of rows affected.
This method is retricted to the wholesale replacement of column values
(no database-side calculations etc). Multiple WHERE key/values are
only 'AND'd together. An 'undef' value maps to SQL's NULL value.
=item delete(from => $table, where => \%expr) -> Int
Delete rows from the database and return the number of rows affected.
=item select(\@columns, from => $table, where => \%expr) -> @Obj
Retrieve rows from the database as a list of objects in array context,
or a single object in scalar context. These objects (blessed into a
dynamically created class) have an accessor method for each column.
The first argument to the select() method must be either an array
reference of column names, or a single '*'. If the array reference is
given only the columns specified will be retrieved from the database.
=back
=head1 CLASS FUNCTIONS
The following functions can be exported individually or all at once
using the ':all' tag. They all return an object which can be combined
with or used inside other functions.
=over 4
=item bv( $value, [ $bind_type ] ) -> L<SQL::DB::BindValue>
This function returns an object which tells SQL::DB to bind $value
using a placeholder. The optional $bind_type is a database type
(integer, varchar, timestamp, bytea, etc) which will be converted to
the appropriate bind constant during a prepare() or prepare_cached()
call.
=item query( @query ) -> L<SQL::DB::Expr>
Build an SQL query using a list of scalars, schema objects,
expressions, other functions, and arrays. This function is the basis
for the prepare(), prepare_cached(), do(), sth(), iter(), fetch() and
fetch1() methods.
=item sql_and( @args ) -> L<SQL::DB::Expr>
Maps to "$arg1 AND $arg2 AND ...".
=item sql_case( @stmts ) -> L<SQL::DB::Expr>
Wraps @stmts inside a CASE/END pair while converting arguments to
expressions where needed.
sql_case(
when => $actors->name->is_null,
then => 'No Name',
else => $actors->name,
)->as('name')
# CASE WHEN actors0.name IS NULL
# THEN ? ELSE actors0.name END AS name
=item sql_coalesce(@args) -> L<SQL::DB::Expr>
Maps to "COALESCE($arg1, $arg2, ...)".
=item sql_cast($arg1, as => $arg2) -> L<SQL::DB::Expr>
Maps to "CAST( $arg1 AS $arg2 )".
=item sql_concat(@args) -> L<SQL::DB::Expr>
Maps to "$arg1 || $arg2 || ...".
=item sql_count(@args) -> L<SQL::DB::Expr>
Maps to "COUNT($arg1, $arg2, ...)".
=item sql_exists(@args) -> L<SQL::DB::Expr>
Maps to "EXISTS(@args)".
=item sql_func('myfunc', @args) -> L<SQL::DB::Expr>
Maps to "MYFUNC($arg1, $arg2, ...)".
=item sql_hex(@args) -> L<SQL::DB::Expr>
Maps to "HEX($arg1, $arg2, ...)".
=item sql_length(@args) -> L<SQL::DB::Expr>
Maps to "LENGTH(@args)".
=item sql_lower(@args) -> L<SQL::DB::Expr>
Maps to "LOWER(@args)".
=item sql_ltrim(@args) -> L<SQL::DB::Expr>
Maps to "LTRIM(@args)".
=item sql_max(@args) -> L<SQL::DB::Expr>
Maps to "MAX(@args)".
=item sql_min(@args) -> L<SQL::DB::Expr>
Maps to "MIN(@args)".
=item sql_rtrim(@args) -> L<SQL::DB::Expr>
Maps to "RTRIM(@args)".
=item sql_sum(@args) -> L<SQL::DB::Expr>
Maps to "MIN(@args)".
=item sql_or(@args) -> L<SQL::DB::Expr>
Maps to "$arg1 OR $arg2 OR ...".
=item sql_replace(@args) -> L<SQL::DB::Expr>
Maps to "REPLACE($arg1,$arg2 [,$arg3])".
=item sql_substr(@args) -> L<SQL::DB::Expr>
Maps to "SUBSTR($arg1, $arg2, ...)".
=item sql_table($name, @columns) -> L<SQL::DB::Expr>
Maps to "name(col1,col2,...)".
=item sql_upper(@args) -> L<SQL::DB::Expr>
Maps to "UPPER(@args)".
=item sql_values(@args) -> L<SQL::DB::Expr>
Maps to "VALUES($arg1, $arg2, ...)".
=back
=head1 COMPATABILITY
All SQL::DB releases have so far been DEVELOPMENT!
SQL::DB jumped from version 0.18 to 0.971.2 due to a complete rewrite
based on Moo. Lots of things were simplified, modules deleted,
dependencies removed, etc. The API changed enough to almost give this
distribution a new name, except I don't know of anyone using this apart
from myself. 0.971.2 will be the last release marked as development,
0.99 will be a release candidate, and 1.00 will be the first stable
release.
=head1 SEE ALSO
L<DBIx::Connector>, L<Log::Any>, L<DBIx::ThinSQL>
=head1 SUPPORT
SQL::DB is managed via Github:
https://github.com/mlawren/sql-db/tree/devel
SQL::DB follows a semantic versioning scheme:
http://semver.org
=head1 AUTHOR
Mark Lawrence E<lt>nomad@null.netE<gt>
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2007-2011 Mark Lawrence <nomad@null.net>
This program is free software; you can redistribute it and/or modify it
under the terms of the GNU General Public License as published by the
Free Software Foundation; either version 3 of the License, or (at your
option) any later version.