The Perl Advent Calendar needs more articles for 2022. Submit your idea today!

NAME

SQL::DB - Perl/SQL database interface

VERSION

0.97_1. Development release.

SYNOPSIS

    use SQL::DB;

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

    ### Convenience Methods - 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},
    );

    ### The Main API

    table 'people' => qw/ id name dob salary /;
    table 'purchases' => qw/ pid id /;

    my $people = urow('people');

    # * 2 calculation takes place inside the database:
    $db->do(
        update => $people,
        set    => $people->salary( $people->salary * 2 ),
        where  => $people->dob == $today,
    );

    my $purchases = srow('purchases');

    my $row = $db->fetch1(
        select    => [ $people->name, $ps->pid ],
        from      => $people,
        left_join => $purchases,
        on        => $purchases->id == $people->id,
        where     => $people->id->in(1,2,3) .AND.
                       $people->name->like('%paul%'),
        limit  => 1,
        offset => 1,
    );
    # then do stuff with $row->pid, $row->name etc

    my @rows = $db->fetch(
        select => [ sql_coalesce($p->pid, $p->cid)->as('pcid') ],
        from   => $p,
        where  => $p->cid->is_not_null,
    );
    # coalesce column is available as $row->pcid

    my $cursor = $db->cursor( @query ...);
    while (my $row = $cursor->next) {
        print $row->column(), $row->some_other_column;
    }

    # Emulated sequences on SQLite
    $db->create_sequence('purchases');
    my $id = $db->nextval('purchases');

    # If you want the data your own way you can still use the query
    # syntax:
    my $sth = $db->sth( @query);
    map { print join(',',@$_) ."\n" } $sth->fetchall_arrayref;

    # Transactions provided by DBIx::Connector
    $db->txn( sub {
        # multiple statements
    }, catch => sub {
        die "WTF: $_";
    });

DESCRIPTION

SQL::DB is a Perl-to-SQL-database 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.

SQL::DB uses the Moo object system internally.

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

connect($dsn, [ $dbuser, $dbpass, $dbattrs ])

This method is a convenience wrapper around new() for those who prefer DBI-style construction.

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.

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( $app, $array_ref )

Deploy the [ $type, $data ] 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.

last_deploy_id( $app )

Returns the count of all deployments.

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!

SQL::DB jumped from version 0.18 to 0.98 due to a complete rewrite based on Moo. Lots of things were simplified, modules deleted, dependencies removed, etc. The API changed enough to almost give this distribution a new name, except I don't know of anyone using this apart from myself. 0.98 will be the last release marked as development, 0.99 will be a release candidate, and 1.00 will be the first stable release.

SEE ALSO

DBIx::Connector, SQL::DB::Expr, SQL::DB::Cursor

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

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