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:

       •   MetaCPAN

           <https://metacpan.org/release/SQL-Tiny>

       •   GitHub issue tracker

           <https://github.com/petdance/sql-tiny/issues>

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>