NAME
SQL::DB - Perl interface to SQL Databases
VERSION
0.04. Development release.
SYNOPSIS
use SQL::DB;
my $db = SQL::DB->new();
$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->define([
table => 'addresses',
class => 'Address',
column => [name => 'id', type => 'INTEGER', primary => 1],
column => [name => 'kind', type => 'INTEGER'],
column => [name => 'city', type => 'INTEGER'],
]);
$db->connect('dbi:SQLite:/tmp/sqldbtest.db', 'user', 'pass', {});
$db->deploy;
my $person = Person::Abstract->new;
$db->do(
insert => [$person->id, $person->name, $person->age],
values => [1, 'Homer', 43],
);
my $address = Address::Abstract->new;
$db->do(
insert => [$address->id, $address->kind, $address->city],
values => [2, 'residential', 'Springfield'],
);
$db->do(
update => $person,
set => [$person->address->set(2)],
where => $person->name == 'Homer',
);
my $p = Person::Abstract->new;
my $add = Address::Abstract->new;
my @items = $db->fetch(
select => [$p->name, $p->age, $add->city],
from => $p,
left_join => $add,
on => $add->id == $p->address,
where => $add->city == 'Springfield' & $p->age > 40,
order_by => $p->age->desc,
limit => 10,
);
foreach my $item (@items) {
print $item->name, '(',$item->age,') lives in ', $item->city, "\n";
}
# "Homer(38) lives in Springfield"DESCRIPTION
SQL::DB provides a low-level interface to SQL databases using Perl objects and logic operators. It is not quite an Object Mapping Layer (such as Class::DBI) and is also not quite an an abstraction (like SQL::Abstract). It falls somewhere inbetween.
For a more complete introduction see SQL::DB::Tutorial.
METHODS
new(@def)
Create a new SQL::DB object. @def is an optional schema definition according to SQL::DB::Schema.
define(@def)
Add to the schema definition. The mandatory @def must be a list of ARRAY refs as required by SQL::DB::Schema.
schema($schema)
Returns the current schema object. The optional $schema will set the current value. Will croak if $schema is not an SQL::DB::Schema object.
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 statements necessary to create the $schema in the database. Will warn on any tables that already exist. Will croak if the schema has not yet been defined.
do(@query)
Constructs an SQL::DB::Query object using @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 using @query, and runs that query against the connected database. Croaks if an error occurs. This method can be used for any SELECT-type statement that retrieves rows.
If the query used a simple "select" then returns a list of simple Class::Accessor-based objects whose method names correspond to the columns or functions in the query.
If the query used a "selecto" then returns a list of SQL::DB::Object -based objects.
insert($sqlobject)
This is a shortcut for $db->do($sqlobject->q_insert). See SQL::DB::Object for what the q_insert() method does.
update($sqlobject)
This is a shortcut for $db->do($sqlobject->q_update). See SQL::DB::Object for what the q_update() method does.
delete($sqlobject)
This is a shortcut for $db->do($sqlobject->q_delete). See SQL::DB::Object for what the q_delete() method does.
qcount
Returns the number of successful queries that have been run.
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, SQL::Builder, Class::DBI, Tangram
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.