.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "SQL::Tiny 3pm" .TH SQL::Tiny 3pm "2022-11-21" "perl v5.36.0" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" SQL::Tiny \- A very simple SQL\-building library .SH "VERSION" .IX Header "VERSION" Version 0.04 .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 1 \& my ($sql,$binds) = sql_select( \*(Aqusers\*(Aq, [ \*(Aqname\*(Aq, \*(Aqstatus\*(Aq ], { status => [ \*(AqDeleted\*(Aq, \*(AqInactive\*(Aq ] }, { order_by => \*(Aqname\*(Aq } ); \& \& my ($sql,$binds) = sql_select( \*(Aqusers\*(Aq, [ \*(AqCOUNT(*)\*(Aq ], { status => [ \*(AqDeleted\*(Aq, \*(AqInactive\*(Aq ] }, { group_by => \*(Aqstatus\*(Aq } ); \& \& my ($sql,$binds) = sql_insert( \*(Aqusers\*(Aq, { name => \*(AqDave\*(Aq, status => \*(AqActive\*(Aq } ); \& \& my ($sql,$binds) = sql_update( \*(Aqusers\*(Aq, { status => \*(AqInactive\*(Aq }, { password => undef } ); \& \& my ($sql,$binds) = sql_delete( \*(Aqusers\*(Aq, { status => \*(AqInactive\*(Aq } ); .Ve .SH "DOCUMENTATION" .IX Header "DOCUMENTATION" A very simple SQL-building library. It's not for all your \s-1SQL\s0 needs, only the very simple ones. .PP It doesn't handle JOINs. It doesn't handle subselects. It's only for simple \s-1SQL.\s0 .PP In my test suites, I have a lot of ad hoc \s-1SQL\s0 queries, and it drives me nuts to have so much \s-1SQL\s0 code lying around. SQL::Tiny is for generating \&\s-1SQL\s0 code for simple cases. .PP I'd far rather have: .PP .Vb 10 \& my ($sql,$binds) = sql_insert( \& \*(Aqusers\*(Aq, \& { \& name => \*(AqDave\*(Aq, \& salary => 50000, \& status => \*(AqActive\*(Aq, \& dateadded => \e\*(AqSYSDATE()\*(Aq, \& qty => \e[ \*(AqROUND(?)\*(Aq, 14.5 ], \& } \& ); .Ve .PP than hand-coding: .PP .Vb 8 \& my $sql = \*(AqINSERT INTO users (name,salary,status,dateadded,qty) VALUES (:name,:status,:salary,SYSDATE(),ROUND(:qty))\*(Aq; \& my $binds = { \& \*(Aq:name\*(Aq => \*(AqDave\*(Aq, \& \*(Aq:salary\*(Aq => 50000, \& \*(Aq:status\*(Aq => \*(AqActive\*(Aq, \& \*(Aq:dateadded\*(Aq => \e\*(AqSYSDATE()\*(Aq, \& \*(Aq:qty\*(Aq => 14.5, \& }; .Ve .PP or even the positional: .PP .Vb 2 \& my $sql = \*(AqINSERT INTO users (name,salary,status,dateadded,qty) VALUES (?,?,?,SYSDATE(),ROUND(?))\*(Aq; \& my $binds = [ \*(AqDave\*(Aq, 50000, \*(AqActive\*(Aq, 14.5 ]; .Ve .PP The trade-off for that brevity of code is that SQL::Tiny has to make new \&\s-1SQL\s0 and binds from the input every time. You can't cache the \s-1SQL\s0 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. .PP The other trade-off is that SQL::Tiny handles only very simple code. It won't handle JOINs of any kind. .PP SQL::Tiny isn't meant for all of your \s-1SQL\s0 needs, only the simple ones that you do over and over. .SH "EXPORT" .IX Header "EXPORT" All subs can be exported, but none are by default. \f(CW\*(C`:all\*(C'\fR exports all subs. .SH "SUBROUTINES/METHODS" .IX Header "SUBROUTINES/METHODS" .ie n .SS "sql_select( $table, \e@columns, \e%where [, \e%other ] )" .el .SS "sql_select( \f(CW$table\fP, \e@columns, \e%where [, \e%other ] )" .IX Subsection "sql_select( $table, @columns, %where [, %other ] )" Creates simple SELECTs and binds. .PP The \f(CW%other\fR can contain \f(CW\*(C`group_by\*(C'\fR and \f(CW\*(C`order_by\*(C'\fR. .PP Calling: .PP .Vb 6 \& my ($sql,$binds) = sql_select( \& \*(Aqusers\*(Aq, \& [qw( userid name )], \& { status => \*(AqX\*(Aq ], \& { order_by => \*(Aqname\*(Aq }, \& ); .Ve .PP returns: .PP .Vb 2 \& $sql = \*(AqSELECT userid,name FROM users WHERE status=? ORDER BY name\*(Aq; \& $binds = [ \*(AqX\*(Aq ]; .Ve .ie n .SS "sql_insert( $table, \e%values )" .el .SS "sql_insert( \f(CW$table\fP, \e%values )" .IX Subsection "sql_insert( $table, %values )" Creates simple INSERTs and binds. .PP Calling: .PP .Vb 10 \& my ($sql,$binds) = sql_insert( \& \*(Aqusers\*(Aq, \& { \& serialno => \*(Aq12345\*(Aq, \& name => \*(AqDave\*(Aq, \& rank => \*(AqSergeant\*(Aq, \& height => undef, \& date_added => \e\*(AqSYSDATE()\*(Aq, \& } \& ); .Ve .PP returns: .PP .Vb 2 \& $sql = \*(AqINSERT INTO users (date_added,height,name,rank,serialno) VALUES (SYSDATE(),NULL,?,?,?)\*(Aq; \& $binds = [ \*(AqDave\*(Aq, \*(AqSergeant\*(Aq, 12345 ] .Ve .ie n .SS "sql_update( $table, \e%values, \e%where )" .el .SS "sql_update( \f(CW$table\fP, \e%values, \e%where )" .IX Subsection "sql_update( $table, %values, %where )" Creates simple \s-1UPDATE\s0 calls and binds. .PP Calling: .PP .Vb 10 \& my ($sql,$binds) = sql_update( \& \*(Aqusers\*(Aq, \& { \& status => \*(AqX\*(Aq, \& lockdate => undef, \& }, \& { \& orderdate => \e\*(AqSYSDATE()\*(Aq, \& }, \& ); .Ve .PP returns: .PP .Vb 2 \& $sql = \*(AqUPDATE users SET lockdate=NULL, status=? WHERE orderdate=SYSDATE()\*(Aq \& $binds = [ \*(AqX\*(Aq ] .Ve .ie n .SS "sql_delete( $table, \e%where )" .el .SS "sql_delete( \f(CW$table\fP, \e%where )" .IX Subsection "sql_delete( $table, %where )" Creates simple \s-1DELETE\s0 calls and binds. .PP Calling: .PP .Vb 9 \& my ($sql,$binds) = sql_delete( \& \*(Aqusers\*(Aq, \& { \& serialno => 12345, \& height => undef, \& date_added => \e\*(AqSYSDATE()\*(Aq, \& status => [qw( X Y Z )], \& }, \& ); .Ve .PP returns: .PP .Vb 2 \& $sql = \*(AqDELETE FROM users WHERE date_added = SYSDATE() AND height IS NULL AND serialno = ? AND status IN (?,?,?)\*(Aq \& $binds = [ 12345, \*(AqX\*(Aq, \*(AqY\*(Aq, \*(AqZ\*(Aq ] .Ve .SH "AUTHOR" .IX Header "AUTHOR" Andy Lester, \f(CW\*(C`\*(C'\fR .SH "BUGS" .IX Header "BUGS" Please report any bugs or feature requests to , or email me directly. .SH "SUPPORT" .IX Header "SUPPORT" You can find documentation for this module with the perldoc command. .PP .Vb 1 \& perldoc SQL::Tiny .Ve .PP You can also look for information at: .IP "\(bu" 4 MetaCPAN .Sp .IP "\(bu" 4 GitHub issue tracker .Sp .SH "ACKNOWLEDGEMENTS" .IX Header "ACKNOWLEDGEMENTS" Thanks to the following folks for their contributions: Mohammad S Anwar, Tim Heaney. .SH "LICENSE AND COPYRIGHT" .IX Header "LICENSE AND COPYRIGHT" Copyright 2019 Andy Lester. .PP 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: .PP