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 object 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_datasource_handle'.

METHODS

There are very few methods in this class, but each one can do quite a bit.

db_select( \%params )

Executes a SELECT. Return value depends on what you ask for. Many of the parameters are optional unless you pass in SQL to execute.

Parameters:

sql ($) (optional)

Full statement to execute, although you may put '?' in the where clause and pass values for substitution. (No quoting hassles...)

select (\@) (optional unless 'sql' defined)

Fields to select

select_modifier ($) (optional)

Clause to insert between 'SELECT' and fields (e.g., DISTINCT)

from (\@ or $) (optional unless 'sql' defined)

List of tables to select from. (You can pass a single tablename as a scalar if you wish.)

order ($) (optional)

Clause to order results by; if not given, the order depends entirely on the database.

group ($) (optional)

Clause to group results by (in a 'GROUP BY' clause). This is normally only done with 'COUNT(*)' and such features. See your favorite SQL reference for more info.

where ($) (optional unless 'sql' defined)

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 ($) (optional)

What the method should return. Potential values are:

DEBUG ($) (optional)

Positive values trigger debugging with larger values triggering more debugging.

'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.
'sth': Returns a DBI statement handle that has been prepared and executed with the proper values. Use this if you are executing a query that may return a lot of rows but you only want to retrieve values for some of the rows.

value (\@) (optional unless you use '?' placeholders)

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.

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.uid

Returns:

[ { 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( \%params )

Create and execute an INSERT statement given the parameters passed in. Return value is true is insert was successful -- the exact value is whatever is returned from the execute() statement handle call from your database. (See DBI and your driver docs.)

Parameters:

sql ($) (optional)

Full SQL statement to run; you can still pass in values to quote/bind if you use '?' in the statement.

table ($) (optional unless 'sql' defined)

Name of table to insert into

field (\@) (optional unless 'sql' defined)

List of fieldnames to insert

value (\@) (optional unless you use '?' placeholders)

List of values, matching up with order of field list.

no_quote (\%) (optional)

Fields that we should not quote

return_sth ($) (optional)

If true, return the statement handle rather than a status.

DEBUG ($) (optional)

Positive values trigger debugging with larger values triggering more debugging.

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( \%params )

Create and execute an UPDATE statement given the parameters passed in. Return value is true is update was successful -- the exact value is whatever is returned from the execute() statement handle call from your database, which many times is the number of rows affected by the update. (See DBI and your driver docs -- in particular, note that the return value from an UPDATE can vary depending on the database being used as well as the number of records actually updated versus those that matched the criteria but were not updated because they already matched the value(s). In particular, see the discussion in DBD::mysql under 'mysql_client_found_rows'.)

Parameters:

sql ($) (optional)

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.

field (\@) (optional unless 'sql' defined)

List of fieldnames we are updating

value (\@) (optional unless you use '?' placeholders)

List of values corresponding to the fields we are updating.

table ($) (optional unless 'sql' defined)

Name of table we are updating

where ($) (optional unless 'sql' defined)

Clause that specifies the rows we are updating

no_quote (\%) (optional)

Specify fields not to quote

DEBUG ($) (optional)

Positive values trigger debugging with larger values triggering more debugging.

Examples:

Perl statement:

$t->db_update( { field => [ qw/ first_name last_name / ],
                 value => [ 'Chris', "O'Donohue" ],
                 table => 'users',
                 where => 'user_id = 98172' } );

SQL statement (assuming "'" gets quoted as "''"):

UPDATE users
   SET first_name = 'Chris',
       last_name = 'O''Donohue',
 WHERE user_id = 98172

db_delete( \%params )

Removes the record indicated by \%params from the database. Return value is true is delete was successful -- the exact value is whatever is returned from the execute() statement handle call from your database. (See DBI)

Parameters:

sql ($) (optional)

Full SQL statement to execute directly, although you can use '?' for values and pass the actual values in via the 'value' parameter.

table ($) (optional unless 'sql' defined)

Name of table from which we are removing records.

where ($) (optional unless 'sql' defined)

Specify the records we are removing. Be careful: if you pass in the table but not the criteria, you will clear out your table! (Just like real SQL...)

value (\@) (optional unless you use '?' placeholders)

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.

DEBUG ($) (optional)

Positive values trigger debugging with larger values triggering more debugging.

Examples:

Perl statement:

$t->db_delete( { table => 'users', where => 'user_id = 98172' } );

SQL statement:

DELETE FROM users
 WHERE user_id = 98172

Perl 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 users

Oops, just cleared out the 'users' table. Be careful!

db_discover_types( $table, \%params )

Basically issue a dummy query to a particular table to get its schema. We save the DBI type information in the %TYPE_INFO package 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 refer to the same table in the database. It is not harmful if you use the same name twice in this manner, the module just has to do a little extra work.

Other parameters:

db (object) (optional)

DBI database handle. (Optional only if you have a global_datasource_handle() class method defined.

DEBUG ($) (optional)

Positive values trigger debugging with larger values triggering more debugging.

dbi_type_info (\%) (optional)

If your DBD driver cannot retrieve type information from the database, you need to give this module a hint as to what type of datatypes you will be working with.

The package lexical %FAKE_TYPES has a mapping of fake-to-DBI type information. In a nutshell:
```
int   -> SQL_INTEGER
num   -> SQL_NUMERIC
float -> SQL_FLOAT
char  -> SQL_VARCHAR
date  -> SQL_DATE
```
So your class can define these hints in the dbi_type_info key in your object config:
```
my $spops = {
  myobj => {
    class => 'My::Object',
    field => [ qw/ obj_id start_date full_count name / ],
    dbi_type_info => {
         obj_id     => 'int',  start_date => 'date',
         full_count => 'int',  name       => 'char'
    },
    ...
  },
};
```

Debugging Methods

Note: we may scrap these in the future and simply use the global DEBUG value exported from SPOPS.

Debugging levels go from 0 to 5, with 5 being the most verbose. The reasons for the different levels are unclear.

SET_DEBUG_SELECT( $level )

Sets the debugging value for selecting records.

DEBUG_SELECT

Returns the current debugging value for selecting objects.

SET_DEBUG_INSERT( $level )

Sets the debugging value for inserting records.

DEBUG_INSERT

Returns the current debugging value for inserting objects.

SET_DEBUG_UPDATE( $level )

Sets the debugging value for updating records.

DEBUG_UPDATE

Returns the current debugging value for updating objects.

SET_DEBUG_DELETE( $level )

Sets the debugging value for deleting records.

DEBUG_DELETE

Returns the current debugging value for deleting objects.

ERROR HANDLING

All errors encountered by this module throw a SPOPS::Exception object (in case of a lack of required information) or, in most cases, a SPOPS::Exception::DBI object.

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 -- particularly useful for INSERTs and UPDATEs. It would be nice to be able to do that.

Datasource Names

Be able to pass a name to 'global_datasource_handle' (and to pass in that name to the relevant 'db_*' calls).

BUGS

None known.

COPYRIGHT

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>

See the SPOPS module for the full author list.

To install SPOPS, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SPOPS

CPAN shell

perl -MCPAN -e shell
install SPOPS

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

DATABASE METHODS

METHODS

db_select( \%params )

db_insert( \%params )

db_update( \%params )

db_delete( \%params )

db_discover_types( $table, \%params )

Debugging Methods

ERROR HANDLING

TO DO

BUGS

SEE ALSO

COPYRIGHT

AUTHORS

Module Install Instructions