NAME
SQL::DB - Perl interface to SQL Databases
VERSION
0.18. Development release.
SYNOPSIS
use SQL::DB qw(define_tables count max);
define_tables(
[
table => 'addresses',
class => 'Address',
column => [name => 'id', type => 'INTEGER', primary => 1],
column => [name => 'kind', type => 'INTEGER'],
column => [name => 'city', type => 'INTEGER'],
],
[
table => 'persons',
class => 'Person',
column => [name => 'id', type => 'INTEGER', primary => 1],
column => [name => 'name', type => 'VARCHAR(255)'],
column => [name => 'age', type => 'INTEGER'],
column => [name => 'address', type => 'INTEGER',
ref => 'addresses(id)',
null => 1],
column => [name => 'parent', type => 'INTEGER',
ref => 'persons(id)',
null => 1],
index => 'name',
]
);
my $db = SQL::DB->new();
$db->connect('dbi:SQLite:/tmp/sqldbtest.db', 'user', 'pass', {});
$db->deploy;
my $persons = $db->arow('persons');
my $addresses = $db->arow('addresses');
$db->do(
insert => [$persons->id, $persons->name, $persons->age],
values => [1, 'Homer', 43],
);
$db->do(
insert => [$addresses->id, $addresses->kind, $addresses->city],
values => [2, 'residential', 'Springfield'], # Pg: [nextval('id')...
);
$db->do(
update => [$persons->set_address(2)],
where => $persons->name == 'Homer',
);
my $ans = $db->fetch1(
select => [count($persons->name)->as('count_name'),
max($persons->age)->as('max_age')],
from => $persons,
where => $persons->age > 40,
);
# The following prints "Head count: 1 Max age:43"
print 'Head count: '. $ans->count_name .
' Max age: '. $ans->max_age ."\n";
my @items = $db->fetch(
select => [$persons->name, $persons->age, $addresses->city],
from => $persons,
left_join => $addresses,
on => $addresses->id == $persons->address,
where => ($addresses->city == 'Springfield') & ($persons->age > 40),
order_by => $persons->age->desc,
limit => 10,
);
# Give me "Homer(43) lives in Springfield"
foreach my $item (@items) {
print $item->name, '(',$item->age,') lives in ', $item->city, "\n";
}DESCRIPTION
SQL::DB provides a low-level interface to SQL databases, using Perl objects and logic operators. It is NOT an Object Relational Mapper like Class::DBI and neither is it an abstraction such as SQL::Abstract. It falls somewhere inbetween.
After using define_tables() to specify your schema and creating an SQL::DB object, the typical workflow is as follows:
* connect() to the database
* deploy() the schema (CREATE TABLEs etc)
* Using one or more "abstract rows" obtained via arow() you can do() insert, update or delete queries.
* Using one or more "abstract rows" obtained via arow() you can fetch() (select) data to work with (and possibly modify).
* Repeat the above three steps as needed. Further queries (with a higher level of automation) are possible with the objects returned by fetch().
* disconnect() from the database.
SQL::DB is capable of generating just about any kind of query, including, but not limited to, JOINs, nested SELECTs, UNIONs, database-side operator invocations, function calls, aggregate expressions, etc. However this package is still quite new, and nowhere near complete. Feedback, testing, and (even better) patches are all welcome.
For a more complete introduction see SQL::DB::Tutorial.
CLASS SUBROUTINES
define_tables(@definitions)
Define the structure of tables, their columns, and associated indexes. @definition is list of ARRAY references as required by SQL::DB::Schema::Table. This class subroutine can be called multiple times. Will warn if you redefine a table.
METHODS
new(@names)
Create a new SQL::DB object. The optional @names lists the tables that this object is to know about. By default all tables defined by define_tables() are known.
set_debug
Set the debugging status (true/false). With debugging turned on debug statements are 'warn'ed.
debug
Get the debug status.
set_sqldebug
Set the SQL statement debugging status (true/false). With this turned on all SQL statements are 'warn'ed.
sqldebug
Get the SQL debug status.
connect($dsn, $user, $pass, $attrs)
Connect to a database. The parameters are passed directly to DBI->connect. This method also informs the internal table/column representations what type of database we are connected to, so they can set their database-specific features accordingly. Returns the dbh.
connect_cached($dsn, $user, $pass, $attrs)
Connect to a database, potentially reusing an existing connection. The parameters are passed directly to DBI->connect_cached. Useful when running under persistent environments. This method also informs the internal table/column representations what type of database we are connected to, so they can set their database-specific features accordingly. Returns the dbh.
dbd
Returns the DBD driver name ('SQLite', 'mysql', 'Pg' etc) for the type of database we are connected to. Returns undef if we are not connected.
dbh
Returns the DBI database handle we are connected with.
deploy_sql
Returns a string containing the CREATE TABLE and CREATE INDEX statements necessary to build the schema in the database. The statements are correctly ordered based on column reference information.
create_table($name)
Creates the table and indexes in the database as previously defined by define_tables for table $name. Will warn on any attempts to create tables that already exist.
drop_table($name)
Drops the table and indexes in the database as previously defined by define_tables for table $name. Will warn on any attempts to create tables that already exist.
deploy
Creates all defined tables and indexes in the database. Will warn on any attempts to create tables that already exist.
query(@query)
Return an SQL::DB::Schema::Query object as defined by @query. This method is useful when creating nested SELECTs, UNIONs, or you can print the returned object if you just want to see what the SQL looks like.
query_as_string($sql, @bind_values)
An internal function for pretty printing SQL queries by inserting the bind values into the SQL itself. Returns a string.
do(@query)
Constructs a SQL::DB::Schema::Query object as defined by @query and runs that query against the connected database. Croaks if an error occurs. This is the method to use for any statement that doesn't retrieve values (eg INSERT, UPDATE and DELETE). Returns whatever value the underlying DBI->do call returns. This method uses "prepare_cached" to prepare the call to the database.
do_nopc(@query)
Same as for do() but uses "prepare" instead of "prepare_cached" to prepare the call to the database. This is really only necessary if you tend to be making recursive queries that are exactly the same. See DBI for details.
fetch(@query)
Constructs an SQL::DB::Schema::Query object as defined by @query and runs that query against the connected database. Croaks if an error occurs. This method should be used for SELECT-type statements that retrieve rows. This method uses "prepare_cached" to prepare the call to the database.
When called in array context returns a list of objects based on SQL::DB::Row. The objects have accessors for each column in the query. Be aware that this can consume large amounts of memory if there are lots of rows retrieved.
When called in scalar context returns a query cursor (SQL::DB::Cursor) (with "next", "all" and "reset" methods) to retrieve dynamically constructed objects one at a time.
fetch_nopc(@query)
Same as for fetch() but uses "prepare" instead of "prepare_cached" to prepare the call to the database. This is really only necessary if you tend to be making recursive queries that are exactly the same. See DBI for details.
fetch1(@query)
Similar to fetch() but always returns only the first object from the result set. All other rows (if any) can not be retrieved. You should only use this method if you know/expect one result. This method uses "prepare_cached" to prepare the call to the database.
fetch1_nopc(@query)
Same as for fetch1() but uses "prepare" instead of "prepare_cached" to prepare the call to the database. This is really only necessary if you tend to be making recursive queries that are exactly the same. See DBI for details.
txn(&coderef)
Runs the code in &coderef as an SQL transaction. If &coderef does not raise any exceptions then the transaction is commited, otherwise it is rolled back.
Returns true/false on success/failure. The returned value can also be printed in the event of failure. See Return::Value for details.
This method can be called recursively, but any sub-transaction failure will always result in the outer-most transaction also being rolled back.
qcount
Returns the number of successful queries that have been run.
quickrows(@objs)
Returns a string containing the column values of @objs in a tabular format. Useful for having a quick look at what the database has returned:
my @objs = $db->fetch(....);
warn $db->quickrows(@objs);create_seq($name)
This (and the seq() method below) are the only attempt that SQL::DB makes at cross-database abstraction. create_seq() creates a sequence called $name. The sequence is actually just a row in the 'sqldb' table.
Warns if the sequence already exists, returns true if successful.
seq($name,$count)
Return the next value for the sequence $name. If $count is specified then a list/array of $count values are returned. The uniqueness of the returned value(s) is assured by locking the appropriate table (or rows in the table) as required.
Note that this is not intended as a replacment for auto-incrementing primary keys in MySQL/SQLite, or real sequences in PostgreSQL. It is simply an ease-of-use mechanism for applications wishing to use a common sequence api across multiple databases.
disconnect
Disconnect from the database. Effectively DBI->disconnect.
METHODS ON FETCHED OBJECTS
Although SQL::DB is not an ORM system it does comes with a very thin object layer. Objects returned by fetch() and fetch1() can be modified using their set_* methods, just like a regular ORM system. However, the difference here is that the objects fields may map across multiple database tables.
Since the objects keep track of which columns have changed, and they also know which columns belong to which tables and which columns are primary keys, they can also automatically generate the appropriate commands for UPDATE or DELETE statements in order to make matching changes in the database.
Of course, the appropriate statements only work if the primary keys have been included as part of the fetch(). See the q_update() and q_delete() methods in SQL::DB::Row for more details.
update($sqlobject)
Nearly the same as $db->do($sqlobject->q_update).
delete($sqlobject)
Nearly the same as $db->do($sqlobject->q_delete).
insert($sqlobject)
Nearly the same as $db->do($sqlobject->q_insert).
COMPATABILITY
Version 0.13 changed the return type of the txn() method. Instead of a 2 value list indicating success/failure and error message, a single Return::Value object is returned intead.
SEE ALSO
SQL::Abstract, DBIx::Class, Class::DBI, Tangram
You can see SQL::DB in action in the MySpam application, also by the same author.
AUTHOR
Mark Lawrence <nomad@null.net>
COPYRIGHT AND LICENSE
Copyright (C) 2007,2008 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 2 of the License, or (at your option) any later version.