NAME
SQL::DB - Perl interface to SQL Databases
VERSION
0.06. Development release.
SYNOPSIS
use SQL::DB qw(max min coalesce count nextval currval setval);
my $db = SQL::DB->new();
$db->define([
table => 'addresses',
class => 'Address',
column => [name => 'id', type => 'INTEGER', primary => 1],
column => [name => 'kind', type => 'INTEGER'],
column => [name => 'city', type => 'INTEGER'],
# seq => [name => 'name', start => 1, increment => 2],
]);
$db->define([
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',
]);
$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,
);
print 'Head count: '. $ans->count_name .' Max age:'.$ans->max_age."\n";
# "Head count: 1 Max age:43"
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,
);
foreach my $item (@items) {
print $item->name, '(',$item->age,') lives in ', $item->city, "\n";
}
# "Homer(43) lives in Springfield"
return @items # this line for automatic testDESCRIPTION
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.
The typical workflow is as follows. After creating an SQL::DB object you can:
* define() the desired or existing schema (tables and columns)
* connect() to the database
* deploy() the schema (CREATE TABLEs etc)
* Create one or more "abstract row" objects using arow().
* do() insert, update or delete queries defined using the abstract row objects.
* fetch() (select) data with queries defined using the abstract row objects.
* 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::Intro.
METHODS
new
Create a new SQL::DB object.
define(@def)
Define the structure of the tables and indexes in the database. @def is a list of ARRAY references as required by SQL::DB::Table.
connect($dbi, $user, $pass, $attrs)
Connect to a database. The parameters are passed directly to DBI->connect.
connect_cached($dbi, $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.
dbh
Returns the DBI database handle we are connected with.
deploy
Runs the CREATE TABLE and CREATE INDEX statements necessary to create the schema in the database. Will warn on any tables that already exist.
query(@query)
Return an SQL::DB::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.
do(@query)
Constructs a SQL::DB::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.
fetch(@query)
Constructs an SQL::DB::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.
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.
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.
qcount
Returns the number of successful queries that have been run.
The following methods are part of the _very thin_ object layer that is part of SQL::DB. The objects returned by fetch() and fetch1() can typically be used here. See SQL::DB::Row for more details.
insert($sqlobject)
A shortcut for $db->do($sqlobject->q_insert).
update($sqlobject)
A shortcut for $db->do($sqlobject->q_update).
delete($sqlobject)
A shortcut for $db->do($sqlobject->q_delete).
DEBUGGING
If $SQL::DB::DEBUG is set to a true value then SQL queries and other important actions are 'warn'ed to STDERR
SEE ALSO
SQL::Abstract, DBIx::Class, Class::DBI, Tangram
You can see SQL::DB in action in the MySpam application (disclaimer: I am also the author)
AUTHOR
Mark Lawrence <nomad@null.net>
COPYRIGHT AND LICENSE
Copyright (C) 2007 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.