README

NAME
    SQL::DB - SQL database interface for Perl (DEPRECATED)

VERSION
    0.971.2. Development release.

SYNOPSIS
        use SQL::DB ':all';
        my $db = SQL::DB->connect( 'dbi:...', 'username', 'password' );


        # The very simple stuff

        my $success = $db->insert(
            into   => 'actors',
            values => { id => 1, name => 'John Smith' },
        );

        my $count = $db->update(
            'actors',
            set   => { name => 'Jack Smith' },
            where => { id   => 1, name => 'John Smith' },
        );

        my $count = $db->delete(
            from  => 'actors',
            where => { actor_id => 1, last_name => 'Jones' },
        );

        my $row = $db->select(
            [ 'id', 'name' ],
            from  => 'actors',
            where => { id => 1 },
        );

        print $row->id . ':' . $row->name . "\n";


        # The do-any-SQL-you-want stuff

        my $people = $db->irow('people');

        $db->do(
            insert_into => $people->( 'id', 'name' );
              sql_values( 1, 'A Name' ),
        );

        # Anyone celebrating a birthday today gets a raise, with the
        # calculation done inside the database.

        my $people = $db->urow('people');

        $db->do(
            update => $people,
            set    => $people->salary( $people->salary * 2 ),
            where  => $people->dob == $today,
        );

        my $purchases = $db->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 $iter = $db->iter( @query ... );
        while ( my $row = $iter->next ) {
            print $row->column(), $row->some_other_column;
        }

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

DESCRIPTION
    NOTE: This module is UNSUPPORTED, UNMAINTAINED, and will be REMOVED from
    CPAN at the end of 2015. If you found the SQL::DB interface suited your
    style of DBI/SQL interaction you may find the DBIx::ThinSQL module a
    suitable replacement.

    SQL::DB is an extension to the Perl Database Interface (DBI). It is
    designed for complicated queries and efficient access to results. With
    an API that lets you easily write almost-raw SQL, SQL::DB gives you
    unfettered access to the power and flexibility of your 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 gives you access to aggregate expressions, joins, nested
    selects, unions and database-side operator invocations. Transactional
    support is provided via DBIx::Connector. Security conscious coders will
    be pleased to know that all user-supplied values are bound properly
    using DBI "bind_param()". Binding binary data is handled transparently
    across different database types.

    SQL::DB offers a couple of very simple Create, Retrieve, Update and
    Delete (CRUD) action methods. These are designed to get you up and
    running quickly when your query data is already inside a hash. The
    methods are abstractions of the real API, but should still read as much
    as possible like SQL.

    Although rows can be retrieved from the database as simple objects,
    SQL::DB does not attempt to be an Object-Relational-Mapper (ORM). There
    are no auto-inflating columns or automatic joins and the code size and
    speed reflect the lack of complexity.

    SQL::DB uses the light-weight Log::Any for logging.

QUERY FORMAT
    An SQL::DB query is composed from a list of scalars, schema objects,
    expressions, and arrays as follows.

    Scalars
        Scalars are assumed to be SQL keywords and are accepted more or less
        unchanged (uppercased with underscores converted to spaces).

            Perl                            SQL
            ----                            ---
            'select'                        SELECT
            'outer_join'                    OUTER JOIN

    Schema Objects
        and DELETE. A urow object maps to a table in the database. It has a
        method for each of the columns in the table which return
        column-objects. These column-objects in turn have further methods
        which map to various SQL statements. The table and column objects
        are used in positions as appropriate for their types.

            Perl                            SQL
            ----                            ---
            $table                          table  (for UPDATE statements)
            $table                          table0 (for SELECT statements)
            $table->col > 1                 table0.col > ?
            $table->col->is_not_null        table0.col IS NULL
            $table->col->asc                table0.col ASC
            $table->col->between(1,5)       table0.col BETWEEN(?,?)

    Expressions
        Expressions are built using schema objects (described below) that
        hold extra information such as table/column names and types, your
        application's input values, and their relationships.

            Perl                            SQL
            ----                            ---
            $table->col == 1                table0.col = ?

            ($table->col == 1) .AND.
              ($table.col2 = ?
            $table->col > 1                 table0.col > ?

            $table->col ==                  table.col =
              $table->col2 - $table->col3     table.col2 - table.col3

    Arrays
        Arrays (array-refs) can contain scalars and expressions which will
        appear in the final SQL joined together by commas (',').

            Perl                                    SQL
            ----                                    ---
            select => [1,2,3]                       SELECT ?,?,?
            set => [$t->c1 == 1, $t->c2 == 2 ]      SET t->c1 = ?, t.c2 = ?

    Functions
        Arrays (array-refs) can contain scalars and expressions which will
        appear in the final SQL joined together by commas (',').

            Perl                                SQL
            ----                                ---
            sql_count($table->col)              COUNT(table0.col)
            sql_values('a', 'b', 'c')           VALUES(?,?,?)

CONSTRUCTORS
    new( %args )
        Connect to a database and return a new SQL::DB handle. Arguments are
        as follows:

        dsn The data source name ('dbi:...'). Required.

        username
            The database authentication username.

        password
            The database authentication password.

        attr
            A hash-reference of DBI handle attributes. RaiseError and
            AutoCommit are always set to 1 and cannot be overridden.
            PrintError defaults to 0. ChopBlanks defaults to 1.
            pg_enable_utf8, sqlite_unicode, and mysql_enable_utf8 default to
            1.

        cache_sth
            Whether to use the DBI prepare_cached() method. See ATTRIBUTES
            below.

        table_info
            A reference to a hash containing the table information for the
            database. This is typically the value returned from an earlier
            call to the "deployed_table_info" method of SQL::DBx::Deploy.

            Use of this parameter is not necessary for SQL::DB applications,
            but could be an important optimisation to minimize database
            calls in applications with more than a trivial number of tables.
            This is more interesting for remote database engines such as
            PostgreSQL with a higher query latency than for local database
            engines such as SQLite.

            The standard usage scenario is as follows: After any change to
            the table/column structure of the database you can the save
            table information to a file:

                use SQL::DB;
                use SQL::DBx::Deploy;
                use YAML::Tiny;

                my $db = SQL::DB->connect(...);
                DumpFile('table_info.yaml', $db->deployed_table_info);

            Then in your application code you set the "table_info" parameter
            when you instantiate your SQL::DB object:

                package MyApp;
                use SQL::DB;
                use YAML::Tiny;

                my $db = SQL::DB->new(
                    dsn        => 'dbi:Pg:dbname=myapp',
                    dbuser     => 'username',
                    dbpass     => '******',
                    table_info => LoadFile('table_info.yaml'),
                );

            Now any calls to "urow" or "srow" on the $db object will not
            need to query the database to know the table structure.

    connect( $dsn, [ $username, $password, $attr ] )
        This is a convenience wrapper around new() for those who prefer
        DBI-style construction.

ATTRIBUTES
    conn -> DBIx::Connector
        The handle connecting us to the database. Read-only.

    dbd -> Str
        The database driver name (Pg, SQLite, etc). Read-only.

    schema -> SQL::DB::Schema
        The schema definition associated with this connection. Read-only.

    cache_sth <-> Bool
        If true then the DBI prepare_cached() method will be used for
        creating statement handles. Otherwise prepare() will be used.

METHODS
    irows( @tables ) -> @coderef
        Returns coderefs for generating table/column strings. Calling the
        coderefs with column name arguments returns a string of the form
        'table(col1,col2)'.

        These refs can be used with the do() method for INSERT queries.

    irow( $table ) => &coderef
        A singular/scalar version of "irows".

    urows( @tables ) -> @URow
        Returns schema objects representing database tables/rows for
        update-style SQL.

        These objects have a method for each table column. Each column
        object has the following methods: is_null(), is_not_null(), in(),
        not_in(), between(), not_between(), as(), like(), asc(), and desc()
        (see SQL::DB::Expr for details).

        These objects can be used with the do() method for UPDATE and DELETE
        queries.

    urow( $table ) => $SQL::DB::Expr
        A singular/scalar version of "urows".

    srows( @tables ) -> @SRow
        Returns schema objects representing database tables/rows for
        select-style SQL.

        These objects have a method for each table column. Each column
        object has the following methods: is_null(), is_not_null(), in(),
        not_in(), between(), not_between(), as(), like(), asc(), and desc()
        (see SQL::DB::Expr for details).

        These objects can be used with the do(), sth(), fetch(), fetch1(),
        and iter() methods for SELECT queries.

    srow( $table ) => $SQL::DB::Expr
        A singular/scalar version of "srows".

    prepare( @query ) -> ($sth, $query)
        Prepares @query using DBI prepare() and returns a DBI::st statement
        handle and the SQL::DB::Expr object representing the query. Croaks
        on error.

        You need to call execute() on the statement handle yourself
        afterwards.

    prepare_cached( @query ) -> ($sth, $query)
        Prepares @query using DBI prepare_cached() and returns a DBI::st
        statement handle and the SQL::DB::Expr object representing the
        query. Croaks on error.

        You need to call execute() on the statement handle yourself
        afterwards.

    do(@query) -> $count
        Prepares (a INSERT, UPDATE or DELETE) @query using either prepare()
        or prepare_cached() (depending on the "cache_sth" attribute) and
        execute()'s the associated DBI::st statement handle. Croaks on
        error.

        Returns the value of the execute() call. The result is typically the
        number of rows affected (but this is driver dependent).

    sth( @query ) -> DBI::st
        Prepares (a SELECT) @query using either prepare() or
        prepare_cached() (depending on the "cache_sth" attribute) then
        execute()s and returns the associated DBI::st statement handle.
        Croaks on error.

        This method is useful if you want to obtain the data with one of the
        DBI fetchrow_hashref(), fetchall_arrayref() etc methods.

    object(@query) -> $object
        Prepares (a SELECT) @query using either prepare() or
        prepare_cached() (depending on the "cache_sth" attribute) and
        execute()'s it. Croaks on error.

        Returns a single array-based object and closes the underlying
        statement handle. The object has accessor methods for each column
        selected.

    objects(@query) -> @objects
        Prepares (a SELECT) @query using either prepare() or
        prepare_cached() (depending on the "cache_sth" attribute) and
        execute()'s it. Croaks on error.

        Returns a list of array-based objects and closes the underlying
        statement handle. Each object has accessor methods for each column
        selected.

        Be aware that calling this method can consume large amounts of
        memory if there are lots of rows to be retrieved. Consider using
        iter() instead.

    fetch1
        An alias for object().

    fetch
        An alias for objects().

    hash
        Same as for object() but returns a hashref instead.

    hashes
        Same as for objects() but returns hashrefs instead.

    array
        Same as for object() but returns an arrayref instead.

    arrays
        Same as for objects() but returns arrayrefs instead.

    iter( @query ) -> SQL::DB::Iter
        Prepares (a SELECT) @query using either prepare() or
        prepare_cached() (depending on the "cache_sth" attribute) and
        execute()'s it. Croaks on error.

        Returns a cursor/iteration object with next() and all() methods for
        retrieving array-based objects. Each object has accessor methods for
        each column selected.

    txn(&coderef)
        Runs the Perl code in &coderef as an SQL transaction. If &coderef
        does not raise any exceptions then the transaction is commited,
        otherwise it is rolled back. See DBIx::Connector for details.

    current_timestamp -> Str
        Returns the current UTC timestamp as a string in the form
        'yyyy-mm-dd hh:mm:ssZ'. The same value is always returned during a
        transaction.

    query_as_string($sql, @bind_values) -> Str
        Pretty print an SQL query by inserting the bind values into the SQL
        itself.

    insert(into => $table, values => \%val) -> Int
        Insert a row into the database and return the number of rows
        affected.

    update($table, set => \%values, where => \%expr) -> Int
        Update rows in the database and return the number of rows affected.

        This method is retricted to the wholesale replacement of column
        values (no database-side calculations etc). Multiple WHERE
        key/values are only 'AND'd together. An 'undef' value maps to SQL's
        NULL value.

    delete(from => $table, where => \%expr) -> Int
        Delete rows from the database and return the number of rows
        affected.

    select(\@columns, from => $table, where => \%expr) -> @Obj
        Retrieve rows from the database as a list of objects in array
        context, or a single object in scalar context. These objects
        (blessed into a dynamically created class) have an accessor method
        for each column.

        The first argument to the select() method must be either an array
        reference of column names, or a single '*'. If the array reference
        is given only the columns specified will be retrieved from the
        database.

CLASS FUNCTIONS
    The following functions can be exported individually or all at once
    using the ':all' tag. They all return an object which can be combined
    with or used inside other functions.

    bv( $value, [ $bind_type ] ) -> SQL::DB::BindValue
        This function returns an object which tells SQL::DB to bind $value
        using a placeholder. The optional $bind_type is a database type
        (integer, varchar, timestamp, bytea, etc) which will be converted to
        the appropriate bind constant during a prepare() or prepare_cached()
        call.

    query( @query ) -> SQL::DB::Expr
        Build an SQL query using a list of scalars, schema objects,
        expressions, other functions, and arrays. This function is the basis
        for the prepare(), prepare_cached(), do(), sth(), iter(), fetch()
        and fetch1() methods.

    sql_and( @args ) -> SQL::DB::Expr
        Maps to "$arg1 AND $arg2 AND ...".

    sql_case( @stmts ) -> SQL::DB::Expr
        Wraps @stmts inside a CASE/END pair while converting arguments to
        expressions where needed.

            sql_case(
                when => $actors->name->is_null,
                then => 'No Name',
                else => $actors->name,
            )->as('name')

            # CASE WHEN actors0.name IS NULL
            # THEN ? ELSE actors0.name END AS name

    sql_coalesce(@args) -> SQL::DB::Expr
        Maps to "COALESCE($arg1, $arg2, ...)".

    sql_cast($arg1, as => $arg2) -> SQL::DB::Expr
        Maps to "CAST( $arg1 AS $arg2 )".

    sql_concat(@args) -> SQL::DB::Expr
        Maps to "$arg1 || $arg2 || ...".

    sql_count(@args) -> SQL::DB::Expr
        Maps to "COUNT($arg1, $arg2, ...)".

    sql_exists(@args) -> SQL::DB::Expr
        Maps to "EXISTS(@args)".

    sql_func('myfunc', @args) -> SQL::DB::Expr
        Maps to "MYFUNC($arg1, $arg2, ...)".

    sql_hex(@args) -> SQL::DB::Expr
        Maps to "HEX($arg1, $arg2, ...)".

    sql_length(@args) -> SQL::DB::Expr
        Maps to "LENGTH(@args)".

    sql_lower(@args) -> SQL::DB::Expr
        Maps to "LOWER(@args)".

    sql_ltrim(@args) -> SQL::DB::Expr
        Maps to "LTRIM(@args)".

    sql_max(@args) -> SQL::DB::Expr
        Maps to "MAX(@args)".

    sql_min(@args) -> SQL::DB::Expr
        Maps to "MIN(@args)".

    sql_rtrim(@args) -> SQL::DB::Expr
        Maps to "RTRIM(@args)".

    sql_sum(@args) -> SQL::DB::Expr
        Maps to "MIN(@args)".

    sql_or(@args) -> SQL::DB::Expr
        Maps to "$arg1 OR $arg2 OR ...".

    sql_replace(@args) -> SQL::DB::Expr
        Maps to "REPLACE($arg1,$arg2 [,$arg3])".

    sql_substr(@args) -> SQL::DB::Expr
        Maps to "SUBSTR($arg1, $arg2, ...)".

    sql_table($name, @columns) -> SQL::DB::Expr
        Maps to "name(col1,col2,...)".

    sql_upper(@args) -> SQL::DB::Expr
        Maps to "UPPER(@args)".

    sql_values(@args) -> SQL::DB::Expr
        Maps to "VALUES($arg1, $arg2, ...)".

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

    SQL::DB jumped from version 0.18 to 0.971.2 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.971.2 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, Log::Any

SUPPORT
    SQL::DB is managed via Github:

        https://github.com/mlawren/sql-db/tree/devel

    SQL::DB follows a semantic versioning scheme:

        http://semver.org

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.