DESCRIPTION

The SQL::Statement module can be used by itself, without DBI and without a subclass to parse SQL statements and to allow you to 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 that you can create and populate some tables, then query them and fetch the results of the queries as well as examine the differences between statement metadata during different phases of prepare, execute, fetch. See the remainder of this document for a description of how to create and modify a parser object and how to use it to parse and examine SQL statements. See SQL::Statement for other uses of the module.

Creating a parser object

The parser object only needs to be created once per script. It can then 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:

As with DBI, RaiseError defaults to 0 (off) and PrintError defaults to 1 (on).

For many purposes, the built-in SQL syntax should be sufficient. However, 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.

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

The following methods can be used to obtain information about a query:

When used without arguments, the method returns a list of the columns $col1, $col2, ..., $colN, you may alternatively use a column number as argument. Note that the column list may be empty as in

INSERT INTO $table VALUES (...)

and in CREATE or DROP statements.

But what does "returning a column" mean? It is returning an SQL::Statement::Util::Column instance, a class that implements the methods table and name, both returning the respective scalar. For example, consider the following statements:

tables

Similar to columns, this method returns instances of SQL::Statement::Table. For UPDATE, DELETE, INSERT, CREATE and DROP, a single table will always be returned. SELECT statements can return more than one table, in case of joins. Table objects offer a single method, name which returns the table name.

params

The params method returns information about the input parameters used in a statement. For example, consider the following:

INSERT INTO foo VALUES (?, ?)

This would return two instances of SQL::Statement::Param. Param objects implement a single method, $param-num()>, which retrieves the parameter number. (0 and 1, in the above example). As of now, not very useful ... :-)

limit

These three statements would retrieve the rows 0..4, 5..9, 10..14 of the table FOO, respectively. If no LIMIT clause is used, then the method $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();

If no LIMIT clause is used, then the method $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();

To manually evaluate the WHERE clause, fetch the topmost where clause node with the where_hash method. Then evaluate the left-hand and right-hand side of the operation, perhaps recursively. Once that is done, apply the operator and finally negate the result, if required.

contains the left-hand side of the operator. This can be a scalar value, a hash containing column or function definition, a parameter definition (hash has attribute type defined) or another operation (hash has attribute op defined).

arg2

contains the right-hand side of the operator. This can be a scalar value, a hash containing column or function definition, a parameter definition (hash has attribute type defined) or another operation (hash has attribute op defined).

neg

contains a TRUE value, if the operation result must be negated after evalution.

To illustrate the above, consider the following WHERE clause:

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

We can represent this clause by the following tree:

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

Thus 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. The arg1() and arg2() fields would be Op's representing "id > 2" and "name = 'joe'".

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 SQL::Eval object, but in fact it can be any object that supplies the methods

where

This method is used to examine 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 is evaluated automatically on the current selected row of the table currently worked on when it's value() method is invoked.

SQL::Statement creates the object tree for where clause evaluation directly after successfully parsing a statement from the given where_clause, if any.

Executing and fetching data from SQL statements

execute

When called from a DBD or other subclass of SQL::Statement, the execute() method will be executed against whatever datasource (persistent storage) is supplied by the DBD or the subclass (e.g. CSV files for DBD::CSV, or BerkeleyDB for DBD::DBM). If you are using SQL::Statement directly rather than as a subclass, you can call the execute() method and the statements will be executed() using temporary in-memory tables. When used directly, like that, you need to create a cache hashref and pass it as the first argument to execute: