The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-Tiny-0.04
River stage zero No dependents
4 ++
/ SQL::Tiny

NAME

SQL::Tiny - A very simple SQL-building library

VERSION

Version 0.04

SYNOPSIS

    my ($sql,$binds) = sql_select( 'users', [ 'name', 'status' ], { status => [ 'Deleted', 'Inactive' ] }, { order_by => 'name' } );

    my ($sql,$binds) = sql_select( 'users', [ 'COUNT(*)' ], { status => [ 'Deleted', 'Inactive' ] }, { group_by => 'status' } );

    my ($sql,$binds) = sql_insert( 'users', { name => 'Dave', status => 'Active' } );

    my ($sql,$binds) = sql_update( 'users', { status => 'Inactive' }, { password => undef } );

    my ($sql,$binds) = sql_delete( 'users', { status => 'Inactive' } );

DOCUMENTATION

A very simple SQL-building library. It's not for all your SQL needs, only the very simple ones.

It doesn't handle JOINs. It doesn't handle subselects. It's only for simple SQL.

In my test suites, I have a lot of ad hoc SQL queries, and it drives me nuts to have so much SQL code lying around. SQL::Tiny is for generating SQL code for simple cases.

I'd far rather have:

    my ($sql,$binds) = sql_insert(
        'users',
        {
            name      => 'Dave',
            salary    => 50000,
            status    => 'Active',
            dateadded => \'SYSDATE()',
            qty       => \[ 'ROUND(?)', 14.5 ],
        }
    );

than hand-coding:

    my $sql   = 'INSERT INTO users (name,salary,status,dateadded,qty) VALUES (:name,:status,:salary,SYSDATE(),ROUND(:qty))';
    my $binds = {
        ':name'      => 'Dave',
        ':salary'    => 50000,
        ':status'    => 'Active',
        ':dateadded' => \'SYSDATE()',
        ':qty'       => 14.5,
    };

or even the positional:

    my $sql   = 'INSERT INTO users (name,salary,status,dateadded,qty) VALUES (?,?,?,SYSDATE(),ROUND(?))';
    my $binds = [ 'Dave', 50000, 'Active', 14.5 ];

The trade-off for that brevity of code is that SQL::Tiny has to make new SQL and binds from the input every time. You can't cache the SQL that comes back from SQL::Tiny because the placeholders could vary depending on what the input data is. Therefore, you don't want to use SQL::Tiny where speed is essential.

The other trade-off is that SQL::Tiny handles only very simple code. It won't handle JOINs of any kind.

SQL::Tiny isn't meant for all of your SQL needs, only the simple ones that you do over and over.

EXPORT

All subs can be exported, but none are by default. :all exports all subs.

SUBROUTINES/METHODS

sql_select( $table, \@columns, \%where [, \%other ] )

Creates simple SELECTs and binds.

The %other can contain group_by and order_by.

Calling:

    my ($sql,$binds) = sql_select(
        'users',
        [qw( userid name )],
        { status => 'X' ],
        { order_by => 'name' },
    );

returns:

    $sql   = 'SELECT userid,name FROM users WHERE status=? ORDER BY name';
    $binds = [ 'X' ];

sql_insert( $table, \%values )

Creates simple INSERTs and binds.

Calling:

    my ($sql,$binds) = sql_insert(
        'users',
        {
            serialno   => '12345',
            name       => 'Dave',
            rank       => 'Sergeant',
            height     => undef,
            date_added => \'SYSDATE()',
        }
    );

returns:

    $sql   = 'INSERT INTO users (date_added,height,name,rank,serialno) VALUES (SYSDATE(),NULL,?,?,?)';
    $binds = [ 'Dave', 'Sergeant', 12345 ]

sql_update( $table, \%values, \%where )

Creates simple UPDATE calls and binds.

Calling:

    my ($sql,$binds) = sql_update(
        'users',
        {
            status     => 'X',
            lockdate   => undef,
        },
        {
            orderdate => \'SYSDATE()',
        },
    );

returns:

    $sql   = 'UPDATE users SET lockdate=NULL, status=? WHERE orderdate=SYSDATE()'
    $binds = [ 'X' ]

sql_delete( $table, \%where )

Creates simple DELETE calls and binds.

Calling:

    my ($sql,$binds) = sql_delete(
        'users',
        {
            serialno   => 12345,
            height     => undef,
            date_added => \'SYSDATE()',
            status     => [qw( X Y Z )],
        },
    );

returns:

    $sql   = 'DELETE FROM users WHERE date_added = SYSDATE() AND height IS NULL AND serialno = ? AND status IN (?,?,?)'
    $binds = [ 12345, 'X', 'Y', 'Z' ]

AUTHOR

Andy Lester, <andy at petdance.com>

BUGS

Please report any bugs or feature requests to https://github.com/petdance/sql-tiny/issues, or email me directly.

SUPPORT

You can find documentation for this module with the perldoc command.

    perldoc SQL::Tiny

You can also look for information at:

ACKNOWLEDGEMENTS

Thanks to the following folks for their contributions: Mohammad S Anwar, Tim Heaney.

LICENSE AND COPYRIGHT

Copyright 2019 Andy Lester.

This program is free software; you can redistribute it and/or modify it under the terms of the the Artistic License (2.0). You may obtain a copy of the full license at:

http://www.perlfoundation.org/artistic_license_2_0