NAME
SPOPS::SQLInterface - Generic routines for DBI database interaction
SYNOPSIS
# Make this class a parent of my class
package My::DBIStuff;
use SPOPS::SQLInterface;
@My::DBIStuff::ISA = qw( SPOPS::SQLInterface );
# You should also be able to use it directly, but you
# need to pass in a database handler with every request
use SPOPS::SQLInterface;
my $dbc = 'SPOPS::SQLInterface';
my $db = DBI->connect( ... ) || die $DBI::errstr;
my $rows = $dbc->db_select( { select => [ qw/ uid first_name last_name / ],
from => [ 'users' ],
where => 'first_name = ? or last_name = ?',
value => [ 'fozzie', "th' bear" ],
db => $db } );
foreach my $row ( @{ $results } ) {
print "User ID $row->[0] is $row->[1] $row->[2]\n";
}DESCRIPTION
You are meant to inherit from this class, although you can use it as a standalone SQL abstraction tool as well, as long as you pass the database handle into every routine you call.
DATABASE METHODS
Relatively simple (!) methods to do the select, update, delete and insert statements, with the right values and table names being passed in.
All parameters are passed in via named values, such as:
$t->db_select( { select => [ 'this', 'that' ],
from => [ 'mytable' ] } );VERY IMPORTANT
The subclass that uses these methods must either pass in a DBI database handle via a named parameter (db) or make it available through a method of the class called 'global_db_handle'.
METHODS
There are very few methods in this class, but each one can do quite a bit.
db_select
Executes a SELECT. Return value depends on what you ask for.
Parameters:
select (\@)
Fields to select
select_modifier ($)
Clause to insert between 'SELECT' and fields (e.g., DISTINCT)
from (\@)
List of tables to select from
order ($)
Clause to order results by; if not given, the order depends entirely on the database.
where ($)
Clause to limit results. Note that you can use '?' for field values but they will get quoted as if they were a SQL_VARCHAR type of value.
return ($)
list: returns an arrayref of arrayrefs (default)
single: returns a single arrayref
hash: returns an arrayref of hashrefs
single-list: returns an arrayref with the first value of each record as the element.
value (\@)
List of values to bind, all as SQL_VARCHAR; they must match order of '?' in the where clause either passed in or within the SQL statement passed in.
sql ($)
Full statement to execute, although you may put '?' in the where clause and pass values for substitution. (No quoting hassles...)
Examples:
Perl statement:
$t->db_select( { select => [ qw/ first_name last_name /],
from => [ 'users' ],
where => 'last_name LIKE ?',
value => 'moo%' } );SQL statement:
SELECT first_name, last_name
FROM users
WHERE last_name LIKE 'moo%'Returns:
[ [ 'stephen', 'moore' ],
[ 'charles', 'mooron' ],
[ 'stacy', 'moonshine' ] ]Perl statement:
$t->db_select( { select => [ qw/ u.username l.login_date / ],
from => [ 'users u', 'logins l' ],
where => "l.login_date > '2000-04-18' and u.uid = l.uid"
return => 'hash' } );SQL statement:
SELECT u.username, l.login_date
FROM users u, logins l
WHERE l.login_date > '2000-04-18' and u.uid = l.uidReturns:
[ { username => 'smoore',
login_date => '2000-05-01' },
{ username => 'cmooron',
login_date => '2000-04-19' },
{ username => 'smoonshine',
login_date => '2000-05-02' } ]Perl statement:
$t->db_select( { select => [ qw/ login_name first_name last_name /],
from => [ 'users' ],
where => 'last_name LIKE ?',
value => 'moo%',
return => 'single-list' } );SQL statement:
SELECT login_name, first_name, last_name
FROM users
WHERE last_name LIKE 'moo%'Returns:
[ 'smoore',
'cmooron',
'smoonshine' ]db_insert
Create and execute an INSERT statement given the parameters passed in.
Parameters:
table ($)
Name of table to insert into
field (\@)
List of fieldnames to insert
value (\@)
List of values, matching up with order of field list.
no_quote (\%)
Fields that we should not quote
sql ($)
Full SQL statement to run; you can still pass in values to quote/bind if you use '?' in the statement.
return_sth ($)
If true, return the statement handle rather than a status.
Examples:
Perl statement:
$t->db_insert( { table => 'users',
field => [ qw/ username first_name last_name password / ],
value => [ 'cmw817', "Chris O'Winters" ] } );SQL statement:
INSERT INTO users
( username, first_name, last_name, password )
VALUES
( 'cmw817', 'Chris', 'O''Winters', NULL )Perl statement:
my $sql = qq/
INSERT INTO users ( username ) VALUES ( ? )
/;
foreach my $username ( qw/ chuck stinky jackson / ) {
$t->db_insert( { sql => $sql, value => [ $username ] } );
}SQL statements:
INSERT INTO users ( username ) VALUES ( 'chuck' )
INSERT INTO users ( username ) VALUES ( 'stinky' )
INSERT INTO users ( username ) VALUES ( 'jackson' )db_update
Create and execute an UPDATE statement given the parameters passed in.
Parameters:
field (\@)
List of fieldnames we are updating
value (\@)
List of values corresponding to the fields we are updating.
table ($)
Name of table we are updating
where ($)
Clause that specifies the rows we are updating
no_quote (\%)
Specify fields not to quote
sql ($)
Full SQL statement to run; note that you can use '?' for values and pass in the raw values via the 'value' parameter, and they will be quoted as necessary.
Examples:
Perl statement:
$t->db_update( { field => [ qw/ first_name last_name / ],
value => [ 'Chris', "O'Donohue" ],
table => 'users',
where => 'user_id = 98172' } );SQL statement:
UPDATE users
SET first_name = 'Chris',
last_name = 'O''Donohue',
WHERE user_id = 98172db_delete
Removes the record indicated by %params from the database.
Parameters:
table ($)
Name of table from which we are removing records.
where ($)
Specify the records we are removing
value (\@)
List of values to bind to '?' that may be found either in the where clause passed in or in the where clause found in the SQL statement.
sql ($)
Full SQL statement to execute directly, although you can use '?' for values and pass the actual values in via the 'value' parameter.
Be careful: if you pass in the table but not the criteria, you will clear out your table! (Just like real SQL...)
Examples:
Perl statement:
$t->db_delete( { table => 'users', where => 'user_id = 98172' } );SQL statement:
DELETE FROM users
WHERE user_id = 98172Perl statement:
$t->db_delete( { table => 'users', where => 'last_name LIKE ?',
value => [ 'moo%' ] } );SQL statement:
DELETE FROM users
WHERE last_name LIKE 'moo%'Perl statement:
$t->db_delete( { table => 'users' } );SQL statement:
DELETE FROM usersOops, just cleared out the 'users' table. Be careful!
db_discover_types
Basically issue a dummy query to a particular table to get its schema. We save the DBI type information in the %TYPE_INFO lexical that all routines here can access.
If a DBD driver does not support the {TYPE} attribute of the statement handle, you have to specify some simple types in your class configuration or provide them somehow. This is still slightly tied to SPOPS implementations in OpenInteract, but only slightly.
Return a hashref of fieldnames as keys and DBI types as values.
Parameters:
table ($)
The name of a particular table. Note that this routine is not smart enough to distinguish between: users and dbo.users even though they might be the same table in the database. It is not particularly harmful if you use the same name twice in this manner, the module just has to do a little extra work.
ERROR HANDLING
Like other classes in SPOPS, all errors encountered will result in the error information saved in SPOPS::Error and a die() being thrown. (More later.)
TO DO
DBI binding conventions
One of the things the DBI allows you to do is prepare a statement once and then execute it many times. It would be nice to allow that somehow.
BUGS
SEE ALSO
COPYRIGHT
Copyright (c) 2001 intes.net, inc.. All rights reserved.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
AUTHORS
Chris Winters <chris@cwinters.com>
Rusty Foster <rusty@kuro5hin.org> was influential in the early days of this library.