NAME

SQL::DB - Perl/SQL database interface

VERSION

0.19_7. Development release.

SYNOPSIS

use SQL::DB;
my $db = SQL::DB->connect( $dsn, $dbuser, $dbpass );

### Basic Operations - thin wrappers around the main API

$db->insert_into('purchases',
    values => {id => 1, count => 1},
);

$db->update('purchases',
    set   => {count => 2},
    where => {id => 1},
);

my $obj = $db->select( ['id','who','count'],
    from => 'customers',
    where => {id => 1},
);
# print $obj->id, $obj->who etc.

$db->delete_from('purchases',
    where => {id => 1},
);

### Advanced Operation - make the database do some real work

# It is faster if we don't have to retrieve
# this information from the DB
table 'customers' => qw/ cid name surname age /;
table 'products'  => qw/ pid label category /;
table 'purchases' => qw/ pid cid /;

# Works for both Pg and SQLite
$db->create_sequence('purchases');
my $id = $db->nextval('purchases');

$db->txn( sub {

    my $purchases = srow('purchases');
    my $purchases2 = srow('purchases');


    $db->do(
        insert_into => sql_purchases('id','name'),
        select   => [ $id, $purchases->name ],
        from     => $purchases,
        where    => $purchases->id->in(
            select   => [ $purchases2->id ],
            from     => $purchases2,
            where    => ($purchases2->id != 1) .OR. (1),
        ),
        order_by => $purchases->name->desc,
        offset => 20,
        limit  => 5,
    );

    # Give everyone a birthday - calculated in the DB
    my $people = urow('people');
    $db->do(
        update => $people,
        set    => $people->age( $people->age + 1 ),
        where  => $people->dob == $today,
    );

    $db->do(
        delete_from => $people,
        where       => ($people->id > 1) .AND. ($people->name != 'Markb'),
    );

    $people = srow('people');

    my @people = $db->fetch1(
        select => [
            $people->id,
            $people->name,
        ],
        from => $people,
        where => $people->name->like('Mark%'),
    )

    # If you just want plain rows you can obtain the DBI
    # statement handle:
    my $sth = $db->sth( @query);
    map { print join(',',@$_) ."\n" } $sth->fetchall_arrayref;


}, catch => sub {
    die "WTF: $_";
});

DESCRIPTION

SQL::DB is a Perl-to-SQL interface. By providing an interface that is very close to real SQL, SQL::DB give you unfettered access to the power and flexibility of the underlying database. It aims to be a tool for programmers who want their databases to work just as hard as their Perl scripts.

SQL::DB is capable of generating just about any kind of query, including aggregate expressions, joins, nested selects, unions, database-side operator invocations, and transactions. It has some minor cross database support, keeping the amount of database-specific code you have to write to a minimum.

Although rows can be retrieved from the database as simple objects, SQL::DB does not attempt to be an Object-Relational-Mapper al-la DBIx::Class.

there is This is nothing like a full-blown Object-Relational-Mapper (ORM) such as DBIx::Classin the The effort needed to use SQL::DB is primarily related to learning the query syntax, which is quite similar to SQL.

Abstract Rows and Expressions

SQL::DB queries use abstract representations of table rows - objects that can be thought of as matching any row in a table. Abstract rows are obtained using the Schema->arow() method. The objects returned have methods that match the columns of a table, plus some extra SQL syntax/sugar methods.

my ( $cds, $tracks ) = $db->arow(qw/ cds tracks /);

The real power of SQL::DB lies in the way that SQL expressions can be constructed using these abstract columns. The abstract column methods do not really act on the abstract row, but intead return an expression object (SQL::DB::Expr). Using Perl's overload feature these objects can be combined and nested in Perl in a way that maps very closely to they way the SQL would be written by hand.

Perl Expression                     SQL Result
----------------------------------  ---------------------------
$cds                                cds

$cds->title                         cds.title

$tracks->cd                         tracks.cd

$tracks->id->func('count')          count(tracks.cd)

$tracks->set_length(                SET tracks.length =
    $tracks->length + 10 )              tracks.length + 10

( $cds->id == 1 ) .OR.              cds.id = 1 OR
  $cds->title->like( '%Magic%' ) )      cds.title LIKE '%Magic%'

( $cds->id->is_not_null .AND.       cds.id IS NOT NULL AND
  ! $tracks->id->in( 1, 2, 5 ) )        NOT tracks.id IN (1,2,5)

Here is a summary of the default available expression operators and methods. New expression subroutines can be generated in your own code - see SQL::DB::Expr for details.

Perl            SQL             Applies to
---------       -------         ------------
.AND.           AND             All Expressions
.OR.            OR              All Expressions
!               NOT             All Expressions
.CONCAT.        || or CONCAT    All Expressions
==              =               All Expressions
!=              !=              All Expressions

like            LIKE            Column only
in              IN              Column only
not_in          NOT IN          Column only
is_null         IS NULL         Column only
is_not_null     IS NOT NULL     Column only
asc             ASC             Column only
desc            DESC            Column only
count           COUNT           Column only
min             MIN             Column only
max             MAX             Column only
func('x',...)   X(col,...)      Column only

Query Syntax

Here is a better example with multiple functions and multiple tables. For each CD, show me the number of tracks, the length of the longest track, and the total length of the CD in one query:

track = $db->arow('tracks');
@objs = $db->fetch(
    select   => [
        $track->cd->title,
        $track->id->func('count'),
        $track->length->func('max'),
        $track->length->func('sum')
    ],
    group_by  => [
        $track->cd->title,
    ],
);

foreach my $obj (@objs) {
    print 'Title: '            . $obj->title      ."\n";
    print 'Number of Tracks: ' . $obj->count_id   ."\n";
    print 'Longest Track: '    . $obj->max_length ."\n";
    print 'CD Length: '        . $obj->sum_length ."\n\n";
}

Transactions, Savepoints, and Sequences

Emulated on systems that don't support native sequences. Based roughly on the PostgreSQL api.

CONSTRUCTORS

new( dsn => $dsn, ...): The new() constructor requires at least a 'dsn' option, and most likely you also want the 'dbuser' and 'dbpass' options as well. Also accepted are 'dbattrs', 'debug', 'dry_run' and 'prepare_mode'. See ATTRIBUTES below for the definitions.
connect($dsn,$dbuser,$dbpass,$dbattrs): Similar to the DBI connect() constructor.

ATTRIBUTES

Unless specified all attributes can be read and set.

debug <-> Bool: General debugging state (true/false). Debug messages are 'warn'ed.
dry_run <-> Bool: When set to false doesn't send any commands to the database. I would check the code to make sure I covered everything before you actually rely on this.
dsn <-> Str: The DBI connection string.
dbd -> Str: The DBD driver name ('SQLite', 'mysql', 'Pg' etc) for the type of database we are connected to. Derived from the 'dsn' attribute.
dbuser <-> Str: The DBI connection username.
dbpass <-> Str: The DBI connection password.
dbattrs <-> HASHREF: The DBI connection attributes.
conn -> DBIx::Connector: The handle connecting us to the database.
prepare_mode -> Str: One of either 'prepare' or 'prepare_cached' (default). See DBI for details.

METHODS

BUILD

Documented here for completeness. This is used by the Moo object system at instantiation time.

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.

create_table($name)

Creates the table $name and associated indexes and sequences in the database. Will warn and skip on any attempts to create tables that already exist.

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. This method uses "prepare_cached" to prepare the call to the database.

fetch(@query) -> SQL::DB::Cursor | @SQL::DB::Row

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. This method uses "prepare_cached" to prepare the call to the database.

When called in array context returns a list of SQL::DB::Row based objects. 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) -> SQL::DB::Row

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.

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.

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.

current_timestamp

The current date and time (as a string) that remains fixed within a transaction.

cursor( @query )

Runs a query and returns a SQL::DB::Cursor object. You can call next() and all() methods on this object to obtain data.

sth( @query )

Runs a query and returns a DBI::st statement handle. You can call fetchrow_array() and other DBI method on this handle.

insert_into($row)

Insert $row into the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.

update($row)

Update $row in the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.

delete_from($row) -> Int

Delete $row from the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.

select(\@columns, from => $table, where => \%args) -> Obj

Delete $row from the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.

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.

This method can be called recursively, but any sub-transaction failure will always result in the outer-most transaction also being rolled back. For savepoint support see DBIx::Connector.

create_sequence( $name )

Creates a sequence in the database. Takes the same arguments as Sequences are emulated on SQLite, used directly for PostgreSQL, and are unsupported for everything else.

$db->create_sequence( 'myseq' );

nextval( $name )

Advance the sequence to its next value and return that value. If $count is specified then a array of $count values are returned and the sequence incremented appropriately. Sequences are emulated on SQLite, used directly for PostgreSQL, and are unsupported for everything else.

setval( $name, $value )

Unimplemented.

drop_sequence( $name )

Unimplemented.

deploy( $array_ref )

Deploy the [SQL,Perl] pairs contained in $array_ref to the database. Only $array_ref elements greater than the previous deployment count (stored in the _sqldb table) will be deployed.

CLASS FUNCTIONS

All of the following functions can be exported on demand, or all at once using the ':all' tag.

table( $name, @columns ): Define a table in the database by table name and columns.
srow( $name, [...] ) -> SQL::DB::SRow::$name, ...: Returns an object (or objects in array context) representing any row of table $name. This abstraction object is used for building SELECT statements with the 'sth', 'fetch', 'fetch1', 'cursor' etc methods. The returned object has a method for each column in the table.
urow( $name, [...] ) -> SQL::DB::URow::$name, ...: Returns an object (or objects in array context) representing any row of table $name. This abstraction object is used for building UPDATE or DELETE statements with the 'do' method. The returned object has a method for each column in the table.
query( @statements ): Create a new SQL::DB::Expr expression based on the given @statements. Scalars are uppercased with '_' converted to spaces, and ARRAYREF elements are joined together with ','.
sql_and
sql_case
sql_coalesce
sql_cast
sql_concat
sql_count
sql_exists
sql_func
sql_length
sql_lower
sql_max
sql_min
sql_sum
sql_or
sql_upper
sql_values

COMPATABILITY

All SQL::DB releases have so far been DEVELOPMENT!

Version 0.19 was a complete rewrite based on Moo. Lots of things were simplified, modules deleted, dependencies removed, etc. The API has changed completely.

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.

SUPPORT

This distribution is still under development. Feedback, testing, bug reports and patches are all welcome.

Bug Reporting

https://rt.cpan.org/Public/Bug/Report.html?Queue=SQL-DB

Source Code

git clone git://github.com/mlawren/sql-db.git

AUTHOR

Mark Lawrence <nomad@null.net>

COPYRIGHT AND LICENSE

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 3 of the License, or (at your option) any later version.

To install SQL::DB, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::DB

CPAN shell

perl -MCPAN -e shell
install SQL::DB

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)