SQL::S...ructure(3pm)

✨ NAME ✨

okay bestie, welcome to SQL::Statement::Structure — a User Contributed Perl Documentation moment 💖 — and the whole assignment is parse and examine structure of SQL queries. (The header also whispers SQL::S...ructure(3pm), but we're folding that name in, not copying the line, periodt.)

---

🌸 SYNOPSIS 🌸

here's the whole flex in code form, no cap 💅✨

use SQL::Statement;
my $sql    = "SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5,2";
my $parser = SQL::Parser->new();
$parser->{RaiseError}=1;
$parser->{PrintError}=0;
$parser->parse("LOAD 'MyLib::MySyntax' ");
my $stmt = SQL::Statement->new($sql,$parser);
printf "Command             %s\n",$stmt->command;
printf "Num of Placeholders %s\n",scalar $stmt->params;
printf "Columns             %s\n",join( ',', map {$_->name} $stmt->column_defs() );
printf "Tables              %s\n",join( ',', map {$_->name} $stmt->tables() );
printf "Where operator      %s\n",join( ',', $stmt->where->op() );
printf "Limit               %s\n",$stmt->limit();
printf "Offset              %s\n",$stmt->offset();

# these will work not before $stmt->execute()
printf "Order Columns       %s\n",join(',', map {$_->column} $stmt->order() );

---

💖 DESCRIPTION 💖

so here's the tea 🍓: SQL::Statement can be used by itself, without DBI and without a subclass, to parse SQL statements and to let you examine the structure of the statement (table names, column names, where clause predicates, etc.). It will also execute statements using in-memory tables. That means you can create and populate some tables, then query them and fetch the results of the queries, and also examine the differences between statement metadata during different phases of prepare, execute, fetch. The rest of this doc explains how to create and modify a parser object and how to use it to parse and examine SQL statements. If you want other uses of the module, go see SQL::Statement.

🧚 Creating a parser object 🧚

okay so the parser object only needs to be created once per script 🎀. After that? It can be reused to parse any number of SQL statements. The basic creation of a parser is this:

my $parser = SQL::Parser->new();

You can set the error-reporting for the parser the same way you do in DBI:

$parser->{RaiseError}=1;   # turn on die-on-error behaviour
$parser->{PrinteError}=1;  # turn on warnings-on-error behaviour

Just like DBI, RaiseError defaults to 0 (off) and PrintError defaults to 1 (on).

For many purposes, the built-in SQL syntax should be sufficient. But if you need to, you can change the behaviour of the parser by extending the supported SQL syntax either by loading a file containing definitions; or by issuing SQL commands that modify the way the parser treats types, keywords, functions, and operators.

$parser->parse("LOAD MyLib::MySyntax");
$parser->parse("CREATE TYPE myDataType");

For all the deets on the supported SQL syntax and the methods of extending the syntax, see SQL::Statement::Syntax.

🦋 Parsing SQL statements 🦋

While you only need to define a new SQL::Parser object once per script, you need to define a new SQL::Statment object once for each statement you want to parse.

my $stmt = SQL::Statement->new($sql, $parser);

The call to new() takes two arguments — the SQL string you want to parse, and the SQL::Parser object you previously created. The call to new is the equivalent of a DBI call to prepare() — it parses the SQL into a structure but does not attempt to execute the SQL unless you explicitly call execute().

🔍 EXAMINING THE STRUCTURE OF SQL STATEMENTS 🔍

okay bestie so once you've got a query parsed, you're gonna wanna look inside it, right? these methods are literally your little x-ray goggles for a statement 👓✨

---

command

hands you back the SQL command. for the full list of commands that are supported, go peek at SQL::Statement::Syntax 📜

my $command = $stmt->command();

---

column definitions

my $numColumns = $stmt->column_defs();  # Scalar context
my @columnList = $stmt->column_defs();  # Array context
my($col1, $col2) = ($stmt->column_defs(0), $stmt->column_defs(1));

this one's for pulling out column lists 💅 and what it means depends entirely on which command you ran:

SELECT $col1, $col2, ... $colN FROM $table WHERE ...
UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
    $colN = $valN WHERE ...
INSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)

call it with no args and you get the whole list back — $col1, $col2, ..., $colN — or you can be picky and pass a column number instead 🔢

heads up bestie: the column list can totally be empty, like in

INSERT INTO $table VALUES (...)

and in CREATE or DROP statements too. no columns, no problem 🫶

but hold on — what does "returning a column" even mean?? 😭 it means you get back an SQL::Statement::Util::Column instance, which is a class implementing the methods table and name, both of which hand you back the matching scalar. say what now? okay, look at these:

INSERT INTO foo (bar) VALUES (1)
SELECT bar FROM foo WHERE ...
SELECT foo.bar FROM foo WHERE ...

in all three of those cases you'd get exactly one column instance back, and

$col->name() eq 'bar'
$col->table() eq 'foo'

iconic. consistent. we love to see it ✨

---

tables

my $tableNum = $stmt->tables();  # Scalar context
my @tables = $stmt->tables();    # Array context
my($table1, $table2) = ($stmt->tables(0), $stmt->tables(1));

basically the same energy as "columns" but it gives you SQL::Statement::Table instances instead 🎀

for UPDATE, DELETE, INSERT, CREATE and DROP you'll always get exactly one table back. SELECT statements though? those can return more than one table when there's a join involved — she's versatile like that 💁‍♀️

table objects offer a single method, name, which returns the table name. that's it, that's the whole API.

---

params

my $paramNum = $stmt->params();  # Scalar context
my @params = $stmt->params();    # Array context
my($p1, $p2) = ($stmt->params(0), $stmt->params(1));

params gives you the tea on the input parameters a statement uses. for example, consider:

INSERT INTO foo VALUES (?, ?)

that'd return two instances of SQL::Statement::Param 🫧 and param objects implement a single method, $param->num(), which retrieves the parameter number (0 and 1, in the example above).

and ngl, as of now it's not very useful ... :-) but hey, it's there 💀

---

row_values

my $rowValueNum = $stmt->row_values(); # Scalar context
my @rowValues = $stmt->row_values(0);  # Array context
my($rval1, $rval2) = ($stmt->row_values(0,0),
                      $stmt->row_values(0,1));

this one's for statements like

UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
    $colN = $valN WHERE ...
INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),
                                ($val1, $val2, ..., $valN)

so you can actually read those $val1, $val2, ... $valN values 👀 it returns (lists of) scalar values or SQL::Statement::Param instances.

---

order

my $orderNum = $stmt->order();   # Scalar context
my @order = $stmt->order();      # Array context
my($o1, $o2) = ($stmt->order(0), $stmt->order(1));

in SELECT statements you can use this to peek at the ORDER clause 🔎 example:

SELECT * FROM FOO ORDER BY id DESC, name

in this case, order could return 2 instances of SQL::Statement::Order. and you've got the methods $o->table(), $o->column(), $o->direction() and $o->desc() to examine that order object with 🧚

---

limit

my $limit = $stmt->limit();

in a SELECT statement you can drop a LIMIT clause to do cursoring:

SELECT * FROM FOO LIMIT 5
SELECT * FROM FOO LIMIT 5, 5
SELECT * FROM FOO LIMIT 10, 5

these three would retrieve rows 0..4, 5..9, and 10..14 of the table FOO, respectively 📊

if there's no LIMIT clause at all, then $stmt->limit returns undef. otherwise it returns the limit number — the maximum number of rows — from the statement (5 or 10 for the statements above).

---

offset

my $offset = $stmt->offset();

no LIMIT clause? then $stmt->limit returns undef. otherwise it returns the offset number — the index of the first row to be included in the limit clause 🎯

---

where_hash

my $where_hash = $stmt->where_hash();

okay so if you wanna evaluate the WHERE clause by hand, you grab the topmost where clause node with the where_hash method 🫡 then you evaluate the left-hand side and the right-hand side of the operation — probably recursively, bestie — and once that's done, you apply the operator and finally negate the result, if required. easy?? 😅

the where clause nodes have (up to) 4 attributes:

• op — holds the operator, one of "AND", "OR", "=", "<>", ">=", ">", "<=", "<", "LIKE", "CLIKE", "IS", "IN", "BETWEEN" or a user defined operator, if any.
• arg1 — holds the left-hand side of the operator. this can be a scalar value, a hash containing a column or function definition, a parameter definition (hash has attribute "type" defined) or another operation (hash has attribute "op" defined).
• arg2 — holds the right-hand side of the operator. this can be a scalar value, a hash containing a column or function definition, a parameter definition (hash has attribute "type" defined) or another operation (hash has attribute "op" defined).
• neg — holds a TRUE value, if the operation result must be negated after evaluation.

to illustrate all that, consider this WHERE clause:

WHERE NOT (id > 2 AND name = 'joe') OR name IS NULL

we can represent this clause with the following tree 🌳

              (id > 2)   (name = 'joe')
                     \   /
          NOT         AND
                         \      (name IS NULL)
                          \    /
                            OR

so the WHERE clause would return an SQL::Statement::Op instance with the op() field set to 'OR' ✨ the arg2() field would return another SQL::Statement::Op instance, with arg1() being the SQL::Statement::Column instance representing id, the arg2() field containing the value undef (NULL) and the op() field being 'IS'.

the arg1() field of the topmost Op instance would return an Op instance with op() eq 'AND' and neg() returning TRUE. and the arg1() and arg2() fields would be Op's representing "id > 2" and "name = 'joe'" 🧠

and of course — of course — there's a ready-for-use method for WHERE clause evaluation 😌

the WHERE clause evaluation depends on an object being used for fetching parameter and column values. usually this can be an SQL::Statement::RAM::Table object or an SQL::Eval object, but fr fr it can be any object that supplies the methods

$val = $eval->param($paramNum);
$val = $eval->column($table, $column);

once you have such an object, you can call eval_where;

$match = $stmt->eval_where($eval);

---

where

my $where = $stmt->where();

this method is for examining the syntax tree of the "WHERE" clause 🌲 it returns undef (if no "WHERE" clause was used) or an instance of SQL::Statement::Term.

the where clause gets evaluated automatically on the currently selected row of the table currently being worked on, whenever its "value()" method is invoked 👑

and "SQL::Statement" creates that object tree for where clause evaluation directly after successfully parsing a statement from the given "where_clause", if any. automatic. effortless. main character energy 💅

---

⚡ EXECUTING AND FETCHING DATA FROM SQL STATEMENTS ⚡

execute

when it's called from a DBD or another subclass of SQL::Statement, the execute() method runs against whatever data-source (persistent storage) that DBD or subclass supplies — think CSV files for DBD::CSV, or BerkeleyDB for DBD::DBM 🗄️

if you're using SQL::Statement directly rather than as a subclass, you can still call execute() and your statements will be executed using temporary in-memory tables ✨ when you use it directly like that, you need to create a cache hashref and pass it as the first argument to execute:

my $cache  = {};
my $parser = SQL::Parser->new();
my $stmt   = SQL::Statement->new('CREATE TABLE x (id INT)',$parser);
$stmt->execute( $cache );

and if your statement has placeholders, you can pass those to execute after the $cache:

$stmt      = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);
$stmt->execute( $cache, 7, 'foo' );

fetch

there's only one "fetch()" method, bestie — it returns a single row of data as an arrayref 📦 use a loop to fetch all the rows:

while (my $row = $stmt->fetch()) {
    # ...
}

an example of executing and fetching

#!/usr/bin/perl -w
use strict;
use SQL::Statement;

my $cache={};
my $parser = SQL::Parser->new();
for my $sql(split /\n/,
"  CREATE TABLE a (b INT)
   INSERT INTO a VALUES(1)
   INSERT INTO a VALUES(2)
   SELECT MAX(b) FROM a  "
)
{
   $stmt = SQL::Statement->new($sql,$parser);
   $stmt->execute($cache);
   next unless $stmt->command eq 'SELECT';
   while (my $row=$stmt->fetch)
   {
       print "@$row\n";
   }
}
__END__

---

👑 AUTHOR & COPYRIGHT 👑

Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved. Copyright (c) 2009-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.

This document may be freely modified and distributed under the same terms as Perl itself.

---

aaaand that's a wrap bestie 🎀 this was perl v5.30.3, dated 2020-10-23, from the SQL::S...ructure(3pm) page — go forth and interrogate your statements, you absolute icon 💖✨🦋