SQL::Ab...Classic(3)

💅 SQL::Abstract::Classic(3) 💅

okay bestie, this is the manual page for SQL::Abstract::Classic — from the User Contributed Perl Documentation section, and yes, the manual name is literally SQL::Abstract::Classic(3). it is here to generate SQL from Perl data structures, and we are gonna get through it together. no cap. 🫶

---

🎀 NAME 🎀

SQL::Abstract::Classic — generate SQL from Perl data structures. that's the whole vibe, fr fr. 💖

---

✨ SYNOPSIS ✨

use SQL::Abstract::Classic;

my $sql = SQL::Abstract::Classic->new;

my($stmt, @bind) = $sql->select($source, \@fields, \%where, $order);

my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);

my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);

my($stmt, @bind) = $sql->delete($table, \%where);

# Then, use these in your DBI statements
my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);

# Just generate the WHERE clause
my($stmt, @bind) = $sql->where(\%where, $order);

# Return values in the same order, for hashed queries
# See PERFORMANCE section for more details
my @bind = $sql->values(\%fieldvals);

---

🌱 Low-impact fork of SQL::Abstract v1.81 ( 2014-10-25 ) 🌱

okay so this module is nearly identical to SQL::Abstract v1.81 at https://metacpan.org/release/RIBASUSHI/SQL-Abstract-1.81. literally almost the same, bestie.

the whole reason this fork exists? apparently there was a recent flurry of activity on the original SQL::Abstract namespace, and that risks leaving downstream users without a way to opt out of impending developments. so this module exists to preserve the ability of users to opt into the new way of doing things according to their own schedules. iconic move, honestly. 🦋

---

🌸 DESCRIPTION 🌸

this module was inspired by the excellent DBIx::Abstract. but when using that module, the author found that what they really wanted to do was generate SQL while still retaining complete control over their statement handles and using the DBI interface. so they set out to create an abstract SQL generation module. main character energy. ✨

while it's based on the concepts used by DBIx::Abstract, there are several important differences, especially when it comes to WHERE clauses. the concepts were modified to make the SQL easier to generate from Perl data structures and, IMO, more intuitive. the underlying idea is for this module to do what you mean, based on the data structures you provide it. the big advantage is that you don't have to modify your code every time your data changes, because this module figures it out. slay. 💅

to begin with, an SQL INSERT is as easy as just specifying a hash of "key=value" pairs:

my %data = (
    name => 'Jimbo Bobson',
    phone => '123-456-7890',
    address => '42 Sister Lane',
    city => 'St. Louis',
    state => 'Louisiana',
);

the SQL can then be generated with this:

my($stmt, @bind) = $sql->insert('people', \%data);

which would give you something like this:

$stmt = "INSERT INTO people
                (address, city, name, phone, state)
                VALUES (?, ?, ?, ?, ?)";
@bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
         '123-456-7890', 'Louisiana');

these are then used directly in your DBI code:

my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);

---

🪐 Inserting and Updating Arrays 🪐

if your database has array types (like for example Postgres), activate the special option array_datatypes => 1 when creating the SQL::Abstract::Classic object. then you may use an arrayref to insert and update database array types:

my $sql = SQL::Abstract::Classic->new(array_datatypes => 1);
my %data = (
    planets => [qw/Mercury Venus Earth Mars/]
);

my($stmt, @bind) = $sql->insert('solar_system', \%data);

this results in:

$stmt = "INSERT INTO solar_system (planets) VALUES (?)"

@bind = (['Mercury', 'Venus', 'Earth', 'Mars']);

---

🔥 Inserting and Updating SQL 🔥

in order to apply SQL functions to elements of your %data you may specify a reference to an arrayref for the given hash value. for example, if you need to execute the Oracle to_date function on a value, you can say something like this:

my %data = (
    name => 'Bill',
    date_entered => \[ "to_date(?,'MM/DD/YYYY')", "03/02/2003" ],
);

the first value in the array is the actual SQL. any other values are optional and would be included in the bind values array. this gives you:

my($stmt, @bind) = $sql->insert('people', \%data);

$stmt = "INSERT INTO people (name, date_entered)
            VALUES (?, to_date(?,'MM/DD/YYYY'))";
@bind = ('Bill', '03/02/2003');

an UPDATE is just as easy, all you change is the name of the function:

my($stmt, @bind) = $sql->update('people', \%data);

notice that your %data isn't touched; the module will generate the appropriately quirky SQL for you automatically. usually you'll want to specify a WHERE clause for your UPDATE, though, which is where handling %where hashes comes in handy... 👀

---

💜 Complex where statements 💜

this module can generate pretty complicated WHERE statements easily. for example, simple "key=value" pairs are taken to mean equality, and if you want to see if a field is within a set of values, you can use an arrayref. let's say we wanted to SELECT some data based on this criteria:

my %where = (
   requestor => 'inna',
   worker => ['nwiger', 'rcwe', 'sfz'],
   status => { '!=', 'completed' }
);

my($stmt, @bind) = $sql->select('tickets', '*', \%where);

the above would give you something like this:

$stmt = "SELECT * FROM tickets WHERE
            ( requestor = ? ) AND ( status != ? )
            AND ( worker = ? OR worker = ? OR worker = ? )";
@bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');

which you could then use in DBI code like so:

my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);

easy, eh? 😌

---

🛠️ METHODS 🛠️

the methods are simple. there's one for every major SQL operation, and a constructor you use first. the arguments are specified in a similar order for each method (table, then fields, then a where clause) to try and simplify things. iykyk. 🫶

---

✨ new(option => 'value') ✨

the new() function takes a list of options and values, and returns a new SQL::Abstract::Classic object which can then be used to generate SQL through the methods below. the options accepted are:

• case — if set to 'lower', then SQL will be generated in all lowercase. by default SQL is generated in "textbook" case meaning something like:

SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'

any setting other than 'lower' is ignored.

• cmp — this determines what the default comparison operator is. by default it is "=", meaning that a hash like this:

%where = (name => 'nwiger', email => 'nate@wiger.org');

will generate SQL like this:

WHERE name = 'nwiger' AND email = 'nate@wiger.org'

however, you may want loose comparisons by default, so if you set "cmp" to "like" you would get SQL such as:

WHERE name like 'nwiger' AND email like 'nate@wiger.org'

you can also override the comparison on an individual basis - see the huge section on "WHERE CLAUSES" at the bottom.

• sqltrue, sqlfalse — expressions for inserting boolean values within SQL statements. by default these are "1=1" and "1=0". they are used by the special operators -in and -not_in for generating correct SQL even when the argument is an empty array (see below).
• logic — this determines the default logical operator for multiple WHERE statements in arrays or hashes. if absent, the default logic is "or" for arrays, and "and" for hashes. this means that a WHERE array of the form:

@where = (
    event_date => {'>=', '2/13/99'},
    event_date => {'<=', '4/24/03'},
);

will generate SQL like this:

WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'

this is probably not what you want given this query, though (look at the dates). to change the "OR" to an "AND", simply specify:

my $sql = SQL::Abstract::Classic->new(logic => 'and');

which will change the above "WHERE" to:

WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'

the logic can also be changed locally by inserting a modifier in front of an arrayref:

@where = (-and => [event_date => {'>=', '2/13/99'},
                   event_date => {'<=', '4/24/03'} ]);

see the "WHERE CLAUSES" section for explanations.

• convert — this will automatically convert comparisons using the specified SQL function for both column and value. this is mostly used with an argument of "upper" or "lower", so that the SQL will have the effect of case-insensitive "searches". for example, this:

$sql = SQL::Abstract::Classic->new(convert => 'upper');
%where = (keywords => 'MaKe iT CAse inSeNSItive');

will turn out the following SQL:

WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')

the conversion can be upper(), lower(), or any other SQL function that can be applied symmetrically to fields (actually SQL::Abstract::Classic does not validate this option; it will just pass through what you specify verbatim).

• bindtype — this is a kludge because many databases suck. for example, you can't just bind values using DBI's execute() for Oracle "CLOB" or "BLOB" fields. instead, you have to use bind_param():

$sth->bind_param(1, 'reg data');
$sth->bind_param(2, $lots, {ora_type => ORA_CLOB});

the problem is, SQL::Abstract::Classic will normally just return a @bind array, which loses track of which field each slot refers to. fear not. 🫶

if you specify "bindtype" in new, you can determine how @bind is returned. currently, you can specify either "normal" (default) or "columns". if you specify "columns", you will get an array that looks like this:

my $sql = SQL::Abstract::Classic->new(bindtype => 'columns');
my($stmt, @bind) = $sql->insert(...);

@bind = (
    [ 'column1', 'value1' ],
    [ 'column2', 'value2' ],
    [ 'column3', 'value3' ],
);

you can then iterate through this manually, using DBI's bind_param().

$sth->prepare($stmt);
my $i = 1;
for (@bind) {
    my($col, $data) = @$_;
    if ($col eq 'details' || $col eq 'comments') {
        $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
    } elsif ($col eq 'image') {
        $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
    } else {
        $sth->bind_param($i, $data);
    }
    $i++;
}
$sth->execute;      # execute without @bind now

okay so, why would you even still use SQL::Abstract::Classic if you have to do all this crap? 💅 honestly? the whole point is that you still don't have to care which fields are or aren't included in your query. like, you could totally wrap that for loop up there in a cute little sub called bind_fields() or whatever and then just reuse it forever. you're still getting a whole layer of abstraction over hand-writing SQL by hand, and that's the vibe. ✨

also heads up bestie: if you set bindtype to columns, then the \[ $sql, @bind ] construct (peep "Literal SQL with placeholders and bind values (subqueries)") is gonna expect the bind values in this format. iykyk 🫶

• quote_char — this is the character that a table or column name gets quoted with. default is an empty string, but you can set it to the character ` and get SQL like this:

SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'

or! you can hand it an array ref of two items — first one is the left-hand quote char, second one is the right-hand quote char. like, you could supply ['[',']'] for SQL Server 2000 compliant quotes, which generates SQL like this:

SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'

quoting is genuinely useful if your table or column names are reserved words in your database's SQL dialect. no cap. 🔥

• escape_char — this is the character that gets used to escape quote_chars that show up inside an identifier, before the identifier has been quoted.

the parameter default, in the case of a single quote_char character, is the quote character itself.

when opening-closing-style quoting is used (quote_char is an arrayref), this parameter defaults to the closing (right) quote_char. occurrences of the opening (left) quote_char within the identifier are currently left untouched. 🙈 and the default for opening-closing-style quotes may change in future versions, so you are strongly encouraged to specify the escape character explicitly. like, just say it out loud, bestie.

• name_sep — this is the character that separates a table and column name. it is necessary to specify this when the quote_char option is selected, so that tables and column names can be individually quoted like this:

SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1

• injection_guard — a regular expression qr/.../ that gets applied to any -function and unquoted column name specified in a query structure. it's a safety mechanism, bestie, to avoid injection attacks when you're mishandling user input. like e.g.:

my %condition_as_column_value_pairs = get_values_from_user();
$sqla->select( ... , \%condition_as_column_value_pairs );

if the expression matches, an exception gets thrown. 🚨 note that literal SQL supplied via \'...' or \['...'] is not checked in any way. so like, don't get too comfy.

defaults to checking for ; and the GO keyword (TransactSQL).

• array_datatypes — when this option is true, arrayrefs in INSERT or UPDATE get interpreted as array datatypes and are passed directly to the DBI layer. when this option is false, arrayrefs get interpreted as literal SQL, just like refs to arrayrefs — but like, that behavior is only there for backwards compatibility, so when you're writing new queries, use the "reference to arrayref" syntax for literal SQL. periodt. 👑
• special_ops — takes a reference to a list of "special operators" to extend the syntax understood by SQL::Abstract::Classic. see section "SPECIAL OPERATORS" for all the deets.
• unary_ops — takes a reference to a list of "unary operators" to extend the syntax understood by SQL::Abstract::Classic. see section "UNARY OPERATORS" for all the deets.

💖 insert($table, \@values || \%fieldvals, \%options)

this one's the simplest function, fr fr. you just give it a table name and either an arrayref of values or a hashref of field/value pairs. it returns an SQL INSERT statement and a list of bind values. see the sections on "Inserting and Updating Arrays" and "Inserting and Updating SQL" for info on how to insert with those data types. 🦋

the optional \%options hash reference may contain additional options to generate the insert SQL. currently supported options are:

• returning — takes either a scalar of raw SQL fields, or an array reference of field names, and adds on an SQL "RETURNING" statement at the end. this lets you return data generated by the insert statement (like row IDs) without performing another SELECT statement. but note, this is not part of the SQL standard and may not be supported by all database engines. 😭

🌸 update($table, \%fieldvals, \%where)

this takes a table, a hashref of field/value pairs, and an optional hashref WHERE clause. it returns an SQL UPDATE function and a list of bind values. see the sections on "Inserting and Updating Arrays" and "Inserting and Updating SQL" for info on how to insert with those data types.

🎀 select($source, $fields, $where, $order)

this returns a SQL SELECT statement and the associated list of bind values, as specified by the arguments:

• $source — specification of the 'FROM' part of the statement. the argument can be either a plain scalar (interpreted as a table name, will be quoted), or an arrayref (interpreted as a list of table names, joined by commas, quoted), or a scalarref (literal SQL, not quoted).
• $fields — specification of the list of fields to retrieve from the source. the argument can be either an arrayref (interpreted as a list of field names, will be joined by commas and quoted), or a plain scalar (literal SQL, not quoted). please observe 👀 that this API is not as flexible as that of the first argument $source, for backwards compatibility reasons. it's giving legacy, honestly.
• $where — optional argument to specify the WHERE part of the query. the argument is most often a hashref, but can also be an arrayref or plain scalar — see section WHERE clause for details.
• $order — optional argument to specify the ORDER BY part of the query. the argument can be a scalar, a hashref or an arrayref — see section ORDER BY clause for details.

🦋 delete($table, \%where)

this takes a table name and an optional hashref WHERE clause. it returns an SQL DELETE statement and list of bind values. iconic, honestly.

⭐ where(\%where, $order)

this one's used to generate just the WHERE clause. like, if you've got an arbitrary data structure and you already know what the rest of your SQL is gonna look like, but you want an easy way to produce a WHERE clause — use this. it returns an SQL WHERE clause and list of bind values. slay. 💅

🍓 values(\%data)

this just returns the values from the hash %data, in the same order that would be returned from any of the other above queries. using this lets you markedly speed up your queries if you're affecting lots of rows. see below under the "PERFORMANCE" section for the tea. ☕

🧚 generate($any, 'number', $of, \@data, $struct, \%types)

Warning: this is an experimental method and subject to change. ⚠️

this returns arbitrarily generated SQL. it's a really basic shortcut. it'll return two different things, depending on return context:

my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
my $stmt_and_val = $sql->generate('create table', \$table, \@fields);

these would return the following:

# First calling form
$stmt = "CREATE TABLE test (?, ?)";
@bind = (field1, field2);

# Second calling form
$stmt_and_val = "CREATE TABLE test (field1, field2)";

depending on what you're trying to do, it's up to you to choose the correct format. in this example, the second form is what you'd want. 👑

by the same token:

$sql->generate('alter session', { nls_date_format => 'MM/YY' });

might give you:

ALTER SESSION SET nls_date_format = 'MM/YY'

you get the idea, bestie. strings get their case twiddled, but everything else remains verbatim. no cap. 🔥

---

🌈 WHERE CLAUSES 🌈

💜 Introduction

this module uses a variation on the idea from DBIx::Abstract. it is NOT, repeat not 100% compatible. 🚨 the main logic of this module is that things in arrays are OR'ed, and things in hashes are AND'ed. that's the whole vibe check.

the easiest way to explain is to show lots of examples. after each %where hash shown, it's assumed you used:

my($stmt, @bind) = $sql->where(\%where);

however, note that the %where hash can be used directly in any of the other functions as well, as described above. 🫶

✨ Key-value pairs

so, let's get started. to begin, a simple hash:

my %where  = (
    user   => 'nwiger',
    status => 'completed'
);

is converted to SQL key = val statements:

$stmt = "WHERE user = ? AND status = ?";
@bind = ('nwiger', 'completed');

one common thing i end up doing is having a list of values that a field can be in. to do this, simply specify a list inside of an arrayref:

my %where  = (
    user   => 'nwiger',
    status => ['assigned', 'in-progress', 'pending'];
);

this simple code will create the following:

$stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
@bind = ('nwiger', 'assigned', 'in-progress', 'pending');

a field associated to an empty arrayref will be considered a logical false and will generate 0=1. 💀

🌸 Tests for NULL values

if the value part is undef then this gets converted to SQL <IS NULL>

my %where  = (
    user   => 'nwiger',
    status => undef,
);

becomes:

$stmt = "WHERE user = ? AND status IS NULL";
@bind = ('nwiger');

to test if a column IS NOT NULL:

my %where  = (
    user   => 'nwiger',
    status => { '!=', undef },
);

💅 Specific comparison operators

if you want to specify a different type of operator for your comparison, you can use a hashref for a given column:

my %where  = (
    user   => 'nwiger',
    status => { '!=', 'completed' }
);

which would generate:

$stmt = "WHERE user = ? AND status != ?";
@bind = ('nwiger', 'completed');

to test against multiple values, just enclose the values in an arrayref:

status => { '=', ['assigned', 'in-progress', 'pending'] };

which would give you:

"WHERE status = ? OR status = ? OR status = ?"

the hashref can also contain multiple pairs, in which case it gets expanded into an "AND" of its elements:

my %where  = (
    user   => 'nwiger',
    status => { '!=', 'completed', -not_like => 'pending%' }
);

# Or more dynamically, like from a form
$where{user} = 'nwiger';
$where{status}{'!='} = 'completed';
$where{status}{'-not_like'} = 'pending%';

           # Both generate this
           $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
           @bind = ('nwiger', 'completed', 'pending%');

okay so both of those give you the exact same thing, no cap. But if you're like “bestie I want an OR instead,” you can team it up with the arrayref idea:

           my %where => (
                user => 'nwiger',
                priority => [ { '=', 2 }, { '>', 5 } ]
           );

which would generate:

           $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
           @bind = ('2', '5', 'nwiger');

if you want to include literal SQL in there (with or without bind values), just use a scalar reference or a reference to an arrayref as the value:

           my %where  = (
               date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
               date_expires => { '<' => \"now()" }
           );

which would generate:

           $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
           @bind = ('11/26/2008');

💅 Logic and nesting operators 💅

okay so in the example above there’s a lowkey trap if you want to say something like this (notice the "AND"):

           WHERE priority != ? AND priority != ?

because in Perl you literally can’t do this:

           priority => { '!=' => 2, '!=' => 1 }

as the second "!=" key will obliterate the first one, periodt. The solution is to use the special -modifier form inside an arrayref:

           priority => [ -and => {'!=', 2},
                                 {'!=', 1} ]

normally these would be joined by "OR", but the modifier tells it to use "AND" instead. (Hint: you can use this in conjunction with the "logic" option to new() in order to change the way your queries work by default.) Important: note that the -modifier goes INSIDE the arrayref, as an extra first element. This will NOT do what you think it might:

           priority => -and => [{'!=', 2}, {'!=', 1}]   # WRONG!

here’s a quick list of equivalencies, since there is some overlap:

           # Same
           status => {'!=', 'completed', 'not like', 'pending%' }
           status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]

           # Same
           status => {'=', ['assigned', 'in-progress']}
           status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
           status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]

✨ Special operators: IN, BETWEEN, etc. ✨

you can also use the hashref format to compare a list of fields using the "IN" comparison operator, by specifying the list as an arrayref:

           my %where  = (
               status   => 'completed',
               reportid => { -in => [567, 2335, 2] }
           );

which would generate:

           $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
           @bind = ('completed', '567', '2335', '2');

the reverse operator -not_in generates SQL "NOT IN" and is used in the same way.

if the argument to -in is an empty array, 'sqlfalse' is generated (by default: "1=0"). Similarly, -not_in => [] generates 'sqltrue' (by default: "1=1").

in addition to the array you can supply a chunk of literal sql or literal sql with bind:

           my %where = {
             customer => { -in => \[
               'SELECT cust_id FROM cust WHERE balance > ?',
               2000,
             ],
             status => { -in => \'SELECT status_codes FROM states' },
           };

would generate:

           $stmt = "WHERE (
                 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
             AND status IN ( SELECT status_codes FROM states )
           )";
           @bind = ('2000');

finally, if the argument to -in is not a reference, it will be treated as a single-element array.

another pair of operators is -between and -not_between, used with an arrayref of two values:

           my %where  = (
               user   => 'nwiger',
               completion_date => {
                  -not_between => ['2002-10-01', '2003-02-06']
               }
           );

would give you:

           WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )

just like with -in, all plausible combinations of literal SQL are possible:

           my %where = {
             start0 => { -between => [ 1, 2 ] },
             start1 => { -between => \["? AND ?", 1, 2] },
             start2 => { -between => \"lower(x) AND upper(y)" },
             start3 => { -between => [
               \"lower(x)",
               \["upper(?)", 'stuff' ],
             ] },
           };

would give you:

           $stmt = "WHERE (
                 ( start0 BETWEEN ? AND ?                )
             AND ( start1 BETWEEN ? AND ?                )
             AND ( start2 BETWEEN lower(x) AND upper(y)  )
             AND ( start3 BETWEEN lower(x) AND upper(?)  )
           )";
           @bind = (1, 2, 1, 2, 'stuff');

these are the two builtin "special operators"; but the list can be expanded: see section "SPECIAL OPERATORS" below.

🦋 Unary operators: bool 🦋

if you wish to test against boolean columns or functions within your database you can use the -bool and -not_bool operators. For example to test the column is_user being true and the column is_enabled being false you would use:-

           my %where  = (
               -bool       => 'is_user',
               -not_bool   => 'is_enabled',
           );

would give you:

           WHERE is_user AND NOT is_enabled

if a more complex combination is required, testing more conditions, then you should use the and/or operators:-

           my %where  = (
               -and           => [
                   -bool      => 'one',
                   -not_bool  => { two=> { -rlike => 'bar' } },
                   -not_bool  => { three => [ { '=', 2 }, { '>', 5 } ] },
               ],
           );

would give you:

           WHERE
             one
               AND
             (NOT two RLIKE ?)
               AND
             (NOT ( three = ? OR three > ? ))

🌸 Nested conditions, -and/-or prefixes 🌸

so far, we’ve seen how multiple conditions are joined with a top-level "AND". We can change this by putting the different conditions we want in hashes and then putting those hashes in an array. For example:

           my @where = (
               {
                   user   => 'nwiger',
                   status => { -like => ['pending%', 'dispatched'] },
               },
               {
                   user   => 'robot',
                   status => 'unassigned',
               }
           );

this data structure would create the following:

           $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
                       OR ( user = ? AND status = ? ) )";
           @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');

clauses in hashrefs or arrayrefs can be prefixed with an -and or -or to change the logic inside:

           my @where = (
                -and => [
                   user => 'nwiger',
                   [
                       -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
                       -or => { workhrs => {'<', 50}, geo => 'EURO' },
                   ],
               ],
           );

that would yield:

           $stmt = "WHERE ( user = ?
                      AND ( ( workhrs > ? AND geo = ? )
                         OR ( workhrs < ? OR geo = ? ) ) )";
           @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');

🧚 Algebraic inconsistency, for historical reasons 🧚

"Important note": when connecting several conditions, the "-and-"|"-or" operator goes "outside" of the nested structure; whereas when connecting several constraints on one column, the "-and" operator goes "inside" the arrayref. Here is an example combining both features:

          my @where = (
            -and => [a => 1, b => 2],
            -or  => [c => 3, d => 4],
             e   => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
          )

yielding

         WHERE ( (    ( a = ? AND b = ? )
                   OR ( c = ? OR d = ? )
                   OR ( e LIKE ? AND e LIKE ? ) ) )

this difference in syntax is unfortunate but must be preserved for historical reasons. So be careful: the two examples below would seem algebraically equivalent, but they are not

         { col => [ -and =>
           { -like => 'foo%' },
           { -like => '%bar' },
         ] }
         # yields: WHERE ( ( col LIKE ? AND col LIKE ? ) )

         [ -and =>
           { col => { -like => 'foo%' } },
           { col => { -like => '%bar' } },
         ]
         # yields: WHERE ( ( col LIKE ? OR col LIKE ? ) )

🌈 Literal SQL and value type operators 🌈

the basic premise of SQL::Abstract::Classic is that in WHERE specifications the "left side" is a column name and the "right side" is a value (normally rendered as a placeholder). This holds true for both hashrefs and arrayref pairs as you see in the "WHERE CLAUSES" examples above. Sometimes it is necessary to alter this behavior. There are several ways of doing so.

🪪 -ident 🪪

this is a virtual operator that signals the string to its right side is an identifier (a column name) and not a value. For example to compare two columns you would write:

           my %where = (
               priority => { '<', 2 },
               requestor => { -ident => 'submitter' },
           );

which creates:

           $stmt = "WHERE priority < ? AND requestor = submitter";
           @bind = ('2');

if you are maintaining legacy code you may see a different construct as described in "Deprecated usage of Literal SQL", please use -ident in new code.

🧸 -value 🧸

this is a virtual operator that signals that the construct to its right side is a value to be passed to DBI. This is for example necessary when you want to write a where clause against an array (for RDBMS that support such datatypes). For example:

           my %where = (
               array => { -value => [1, 2, 3] }
           );

will result in:

           $stmt = 'WHERE array = ?';
           @bind = ([1, 2, 3]);

note that if you were to simply say:

           my %where = (
               array => [1, 2, 3]
           );

the result would probably not be what you wanted:

           $stmt = 'WHERE array = ? OR array = ? OR array = ?';
           @bind = (1, 2, 3);

📜 Literal SQL 📜

finally, sometimes only literal SQL will do. To include a random snippet of SQL verbatim, you specify it as a scalar reference. Consider this only as a last resort. Usually there is a better way. For example:

           my %where = (
               priority => { '<', 2 },
               requestor => { -in => \'(SELECT name FROM hitmen)' },
           );

would create:

           $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
           @bind = (2);

note that in this example, you only get one bind parameter back, since the verbatim SQL is passed as part of the statement.

🚨 CAVEAT 🚨

Never use untrusted input as a literal SQL argument - this is a massive security risk (there is no way to check literal snippets for SQL injections and other nastyness). If you need to deal with untrusted input use literal SQL with placeholders as described next.

🧵 Literal SQL with placeholders and bind values (subqueries) 🧵

okay so if the literal SQL you're inserting has placeholders and bind values, you use a reference to an arrayref — yes bestie, that's a double reference, not super common but perfectly legal Perl 💅. Like, to find a date in Postgres you can do something like this:

    my %where = (
       date_column => \[ "= date '2008-09-30' - ?::integer", 10 ]
    )

This would create:

    $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
    @bind = ('10');

Note that you must pass the bind values in the same format as they are returned by where. That means if you set bindtype to columns, you must provide the bind values in the [ column_meta => value ] format, where column_meta is an opaque scalar value; most commonly the column name, but you can use any scalar value (including references and blessed references), and SQL::Abstract::Classic will simply pass it through intact. So if bindtype is set to columns, the above example looks like this:

    my %where = (
       date_column => \[ "= date '2008-09-30' - ?::integer", [ {} => 10 ] ]
    )

Literal SQL is especially useful for nesting parenthesized clauses in the main SQL query. Here's a first example:

  my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
                               100, "foo%");
  my %where = (
    foo => 1234,
    bar => \["IN ($sub_stmt)" => @sub_bind],
  );

This yields:

  $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
                                             WHERE c2 < ? AND c3 LIKE ?))";
  @bind = (1234, 100, "foo%");

Other subquery operators, like for example > ALL or NOT IN, are expressed in the same way. Of course the $sub_stmt and its associated bind values can be generated through a former call to select():

  my ($sub_stmt, @sub_bind)
     = $sql->select("t1", "c1", {c2 => {"<" => 100},
                                 c3 => {-like => "foo%"}});
  my %where = (
    foo => 1234,
    bar => \["> ALL ($sub_stmt)" => @sub_bind],
  );

In the examples above, the subquery was used as an operator on a column; but the same principle also applies for a clause within the main %where hash, like an EXISTS subquery:

  my ($sub_stmt, @sub_bind)
     = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
  my %where = ( -and => [
    foo   => 1234,
    \["EXISTS ($sub_stmt)" => @sub_bind],
  ]);

which yields

  $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
                                        WHERE c1 = ? AND c2 > t0.c0))";
  @bind = (1234, 1);

Observe that the condition on c2 in the subquery refers to column t0.c0 of the main query: this is not a bind value, so we have to express it through a scalar ref. Writing c2 => {">" => "t0.c0"} would have generated c2 > ? with bind value t0.c0 ... not exactly what we wanted here. 😭

Finally, here's an example where a subquery is used for expressing unary negation:

  my ($sub_stmt, @sub_bind)
     = $sql->where({age => [{"<" => 10}, {">" => 20}]});
  $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
  my %where = (
        lname  => {like => '%son%'},
        \["NOT ($sub_stmt)" => @sub_bind],
    );

This yields

  $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
  @bind = ('%son%', 10, 20)

🕰️ Deprecated usage of Literal SQL 🕰️

Below are some examples of archaic use of literal SQL. It is shown only as reference for those who deal with legacy code. Each example has a much better, cleaner and safer alternative that users should opt for in new code. 🫶

• The old NULL-comparison flex — this used to be the move:

my %where = ( requestor => \'IS NOT NULL' )

and it gave you:

$stmt = "WHERE requestor IS NOT NULL"

This used to be the way of generating NULL comparisons, before the handling of undef got formalized. For new code please use the superior syntax as described in "Tests for NULL values". ✨

• The old column-comparison trick — legit ancient:

my %where = ( requestor => \'= submitter' )

which made:

$stmt = "WHERE requestor = submitter"

This used to be the only way to compare columns. Use the superior -ident method for all new code. For example an identifier declared in such a way will be properly quoted if quote_char is properly set, while the legacy form will remain as supplied.

• The empty-string boolean hack — truly a relic:

my %where = ( is_ready  => \"", completed => { '>', '2012-12-21' } )

and it generated:

$stmt = "WHERE completed > ? AND is_ready"
@bind = ('2012-12-21')

Using an empty string literal used to be the only way to express a boolean. For all new code please use the much more readable -bool operator. 💀

✨ Conclusion ✨

These pages could go on for a while, since the nesting of the data structures this module can handle are pretty much unlimited (the module implements the WHERE expansion as a recursive function internally). Your best bet is to "play around" with the module a little to see how the data structures behave, and choose the best format for your data based on that. 🦋

And of course, all the values above will probably be replaced with variables gotten from forms or the command line. After all, if you knew everything ahead of time, you wouldn't have to worry about dynamically-generating SQL and could just hardwire it into your script. no cap.

🌈 ORDER BY CLAUSES 🌈

Some functions take an order by clause. This can either be a scalar (just a column name), a hashref of { -desc => 'col' } or { -asc => 'col' }, a scalarref, an arrayref-ref, or an arrayref of any of the previous forms. Examples:

               Given              |         Will Generate
    ---------------------------------------------------------------
                                  |
    'colA'                        | ORDER BY colA
                                  |
    [qw/colA colB/]               | ORDER BY colA, colB
                                  |
    {-asc  => 'colA'}             | ORDER BY colA ASC
                                  |
    {-desc => 'colB'}             | ORDER BY colB DESC
                                  |
    ['colA', {-asc => 'colB'}]    | ORDER BY colA, colB ASC
                                  |
    { -asc => [qw/colA colB/] }   | ORDER BY colA ASC, colB ASC
                                  |
    \'colA DESC'                  | ORDER BY colA DESC
                                  |
    \[ 'FUNC(colA, ?)', $x ]      | ORDER BY FUNC(colA, ?)
                                  |   /* ...with $x bound to ? */
                                  |
    [                             | ORDER BY
      { -asc => 'colA' },         |     colA ASC,
      { -desc => [qw/colB/] },    |     colB DESC,
      { -asc => [qw/colC colD/] },|     colC ASC, colD ASC,
      \'colE DESC',               |     colE DESC,
      \[ 'FUNC(colF, ?)', $x ],   |     FUNC(colF, ?)
    ]                             |   /* ...with $x bound to ? */
    ===============================================================

🔥 SPECIAL OPERATORS 🔥

  my $sqlmaker = SQL::Abstract::Classic->new(special_ops => [
     {
      regex => qr/.../,
      handler => sub {
        my ($self, $field, $op, $arg) = @_;
        ...
      },
     },
     {
      regex => qr/.../,
      handler => 'method_name',
     },
   ]);

A "special operator" is a SQL syntactic clause that can be applied to a field, instead of a usual binary operator. For example:

   WHERE field IN (?, ?, ?)
   WHERE field BETWEEN ? AND ?
   WHERE MATCH(field) AGAINST (?, ?)

Special operators IN and BETWEEN are fairly standard and therefore are builtin within SQL::Abstract::Classic (as the overridable methods _where_field_IN and _where_field_BETWEEN). For other operators, like the MATCH .. AGAINST example above which is specific to MySQL, you can write your own operator handlers — supply a special_ops argument to the new method. That argument takes an arrayref of operator definitions; each operator definition is a hashref with two entries:

🔍 regex

the regular expression to match the operator

🛠️ handler

Either a coderef or a plain scalar method name. In both cases the expected return is ($sql, @bind).

When supplied with a method name, it is simply called on the SQL::Abstract::Classic object as:

 $self->$method_name ($field, $op, $arg)

Where:

• $field is the LHS of the operator
• $op is the part that matched the handler regex
• $arg is the RHS

When supplied with a coderef, it is called as:

 $coderef->($self, $field, $op, $arg)

For example, here's an implementation of the MATCH .. AGAINST syntax for MySQL:

  my $sqlmaker = SQL::Abstract::Classic->new(special_ops => [

    # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
    {regex => qr/^match$/i,
     handler => sub {
       my ($self, $field, $op, $arg) = @_;
       $arg = [$arg] if not ref $arg;
       my $label         = $self->_quote($field);
       my ($placeholder) = $self->_convert('?');
       my $placeholders  = join ", ", (($placeholder) x @$arg);
       my $sql           = $self->_sqlcase('match') . " ($label) "
                         . $self->_sqlcase('against') . " ($placeholders) ";
       my @bind = $self->_bindtype($field, @$arg);
       return ($sql, @bind);
       }
     },

  ]);

🦋 UNARY OPERATORS 🦋

  my $sqlmaker = SQL::Abstract::Classic->new(unary_ops => [
     {
      regex => qr/.../,
      handler => sub {
        my ($self, $op, $arg) = @_;
        ...
      },
     },
     {
      regex => qr/.../,
      handler => 'method_name',
     },
   ]);

A unary operator is a SQL syntactic clause that can be applied to a field — the operator goes before the field. 💅

You can write your own operator handlers — supply a unary_ops argument to the new method. That argument takes an arrayref of operator definitions; each operator definition is a hashref with two entries:

🔍 regex

the regular expression to match the operator

🛠️ handler

Either a coderef or a plain scalar method name. In both cases the expected return is $sql.

When supplied with a method name, it is simply called on the SQL::Abstract::Classic object as:

 $self->$method_name ($op, $arg)

Where:

• $op is the part that matched the handler regex
• $arg is the RHS or argument of the operator

When supplied with a coderef, it is called as:

 $coderef->($self, $op, $arg)

🔥 PERFORMANCE 🔥

Okay bestie, so Mark Stosberg did some benchmarking and it turns out this module is many orders of magnitude faster than using DBIx::Abstract. The author is fully honest that this was not an intentional design choice — it’s just a byproduct of the fact that you get to control your own DBI handles. 💅

To maximize performance, use a code snippet like this:

# prepare a statement handle using the first row
# and then reuse it for the rest of the rows
my($sth, $stmt);
for my $href (@array_of_hashrefs) {
    $stmt ||= $sql->insert('table', $href);
    $sth  ||= $dbh->prepare($stmt);
    $sth->execute($sql->values($href));
}

The reason this works is because the keys in your $href are sorted internally by SQL::Abstract::Classic. So as long as your data keeps the same structure, you only have to generate the SQL the first time around. On subsequent queries, just use the values function provided by this module to return your values in the correct order. ✨

But this depends on the values having the same type — if, for example, the values of a where clause may either have values (resulting in SQL of the form column = ? with a single bind value), or alternatively the values might be undef (resulting in SQL of the form column IS NULL with no bind value), then the caching technique suggested will not work. 😭

---

🎀 FORMBUILDER 🎀

If you use the author’s CGI::FormBuilder module at all, you’ll hopefully really like this part (the author does, at least 💖). Building up a complex query can be as simple as this:

#!/usr/bin/perl

use warnings;
use strict;

use CGI::FormBuilder;
use SQL::Abstract::Classic;

my $form = CGI::FormBuilder->new(...);
my $sql  = SQL::Abstract::Classic->new;

if ($form->submitted) {
    my $field = $form->field;
    my $id = delete $field->{id};
    my($stmt, @bind) = $sql->update('table', $field, {id => $id});
}

Of course, you would still have to connect using DBI to run the query, but the whole point is that if you make your form look like your table, the actual query script can be extremely simplistic. 🦋

If you’re REALLY lazy (same, honestly), check out HTML::QuickTable for a fast interface to returning and formatting data. The author frequently uses these three modules together to write complex database query apps in under 50 lines. Iconic behavior, no cap. 👑

---

🌸 ACKNOWLEDGEMENTS 🌸

There are a number of individuals that have really helped out with this module. Unfortunately, most of them submitted bugs via CPAN so the author has no idea who they are! 😭 But the people the author does know are:

• Ash Berlin (order_by hash term support)
• Matt Trout (DBIx::Class support)
• Mark Stosberg (benchmarking)
• Chas Owens (initial IN operator support)
• Philip Collins (per-field SQL functions)
• Eric Kolve (hashref AND support)
• Mike Fragassi (enhancements to BETWEEN and LIKE)
• Dan Kubb (support for quote_char and name_sep)
• Guillermo Roditi (patch to cleanup IN and BETWEEN, fix and tests for _order_by)
• Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
• Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
• Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
• Oliver Charles (support for RETURNING after INSERT)

Thanks! 🫶💖

---

🦋 SEE ALSO 🦋

• SQL::Abstract
• DBIx::Class
• DBIx::Abstract
• CGI::FormBuilder
• HTML::QuickTable

---

👑 AUTHOR 👑

Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.

---

💜 LICENSE 💜

This module is free software; you may copy this under the same terms as perl itself (either the GNU General Public License or the Artistic License).

---

Mwah 💋 — this is SQL::Ab...Classic(3) from perl v5.42.0, dated 2020-06-17. Stay hydrated and may your SQL always be prepared bestie 🌈⭐