NAME

DBIx::ThinSQL::SQLite - add various functions to SQLite

VERSION

0.0.2 Development release.

SYNOPSIS

    use DBI;
    use DBIx::ThinSQL::SQLite
        qw/sqlite_create_functions thinsql_create_methods/;

    # Add functions on connect

    my $db = DBI->connect(
        $dsn, undef, undef,
        {
            Callbacks => {
                connected => sub {
                    my $dbh = shift;
                    sqlite_create_functions( $dbh,
                        qw/debug nextval/ );
                  }
            },

        }
    );

    # Or manually at any time
    sqlite_create_functions( $db, qw/currval/ );

    # Then in your SQL you can use those functions

    $db->do(q{
        SELECT debug('logged via Log::Any->debug');
    });

    $db->do(q{
        SELECT create_sequence('name');
    });

    $db->do(q{
        SELECT nextval('name');
    });

    # If you are using DBIx::ThinSQL instead of DBI
    # you can also use the sequence functions as methods

    thinsql_create_methods(qw/create_sequence nextval/);

    $db->create_sequence('othername');
    $db->nextval('othername');

DESCRIPTION

DBIx::ThinSQL::SQLite adds various functions to the SQL syntax understood by SQLite, using the sqlite_create_function() and sqlite_create_aggregate_function() methods of DBD::SQLite. It also adds sequence methods to your database handles when you are using DBIx::ThinSQL.

Two functions are exported on request:

sqlite_create_functions( $dbh, @functions )

Add @functions to the SQL understood by SQLite for the database handle $dbh, which can be any combination of the following.

debug( @items )

This function called from SQL context results in a debug() call to a Log::Any instance. If the first item of @items begins with /^select/i then that statement will be run and the result included in the output as well.

create_sequence( $name )

Create a sequence in the database with name $name.

nextval( $name ) -> Int

Advance the sequence to its next value and return that value.

currval( $name ) -> Int

Return the current value of the sequence.

If Digest::SHA is installed then the following functions can also be created.

sha1( @args ) -> bytes

Calculate the SHA digest of all arguments concatenated together and return it in a 20-byte binary form. Unfortunately it seems that the underlying SQLite C sqlite_create_function() provides no way to identify the result as a blob, so you must always manually cast the result in SQL like so:

    CAST(sha1(SQLITE_EXPRESSION) AS blob)

sha1_hex( @args ) -> hexidecimal

Calculate the SQLite digest of all arguments concatenated together and return it in a 40-character hexidecimal form.

sha1_base64( @args ) -> base64

Calculate the SQLite digest of all arguments concatenated together and return it in a base64 encoded form.

agg_sha1( @args ) -> bytes

agg_sha1_hex( @args ) -> hexidecimal

agg_sha1_base64( @args ) -> base64

These aggregate functions are for use with statements using GROUP BY.

Note that user-defined SQLite functions are only valid for the current session. They must be created each time you connect to the database.

thinsql_create_methods( @methods )

Add @methods to the DBIx::ThinSQL::db class which can be any combination of the following.

create_sequence( $name )

Create a sequence in the database with name $name.

nextval( $name ) -> Int

Advance the sequence to its next value and return that value.

currval( $name ) -> Int

Return the current value of the sequence.

The methods are added to a Perl class and are therefore available to any DBIx::ThinSQL handle.

CAVEATS

An "autoincrement" integer primary key column in SQLite automatically creates a sequence for that table, which is incompatible with this module. Keep the two sequence types separate.

SEE ALSO

Log::Any

AUTHOR

Mark Lawrence <nomad@null.net>

COPYRIGHT AND LICENSE

Copyright (C) 2013 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.