sql::parser(3)

NAME

SQL::Parser -- validate, parse, or build SQL strings

SYNOPSIS

use  SQL::Parser;                                       #
CREATE A PARSER OBJECT
my $parser = SQL::Parser->new( $dialect, attrs );
my  $success  =  $parser->parse( $sql_string );         #
PARSE A SQL STRING &
if  ($success)  {                                       #
DISPLAY RESULTING DATA
    use   Data::Dumper;                                 #
STRUCTURE
    print Dumper $parser->structure;
}
$parser->feature( $class, $name,  $value  );            #
SET OR FIND STATUS OF
my  $has_feature = $parser->feature( $class, $name ); # A
PARSER FEATURE
$parser->dialect(  $dialect_name  );                    #
SET OR FIND STATUS OF
my  $current_dialect = $parser->dialect;              # A
PARSER DIALECT
print  $parser->errstr;                                 #
DISPLAY CURRENT ERROR
                                                     #
STRING

DESCRIPTION

SQL::Parser is a parser, builder, and sytax validator for
a small but useful subset of SQL (Structured Query Lan
guage). It accepts SQL strings and returns either a detailed error
message if the syntax is invalid or a data structure containing
the results of the parse if the syntax is valid. It will
soon also work in reverse to build a SQL string from a supplied da
ta structure.
The module can be used in batch mode to validate a series
of statements, or as middle-ware for DBI drivers or other
related projects. When combined with SQL::Statement version 0.2
or greater, the module can be used to actually perform the
SQL commands on a variety of file formats using DBD::AnyData,
or DBD::CSV, or DBD::Excel.
The module makes use of a variety of configuration files located in the SQL/Dialects directory, each of which is essentially a simple text file listing things like sup
ported data types, reserved words, and other features specific
to a given dialect of SQL. These features can also be turned
on or off during program execution.

SUPPORTED SQL SYNTAX

This module is meant primarly as a base class for DBD
drivers and as such concentrates on a small but useful
subset of SQL 92. It does *not* in any way pretend to be
a complete SQL 92 parser. The module will continue to add
new supported syntax, currently, this is what is sup
ported:

CREATE TABLE: CREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table
(
$col_1 $col_type1 $col_constraints1,
...,
$col_N $col_typeN $col_constraintsN,

)
[ ON COMMIT {DELETE|PRESERVE} ROWS ]; * col_type must be a valid data type as defined in; the
"valid_data_types" section of the dialect file for

the
current dialect; * col_constriaints may be "PRIMARY KEY" or one or; both of
"UNIQUE" and/or "NOT NULL"; * IMPORTANT NOTE: temporary tables, data types and; column
constraints are checked for syntax violations but

are
currently otherwise *IGNORED* -- they are recog

nized by
the parser, but not by the execution engine; * The following valid ANSI SQL92 options are not cur; rently
supported: table constraints, named constraints,

check
constriants, reference constraints, constraint
attributes, collations, default clauses, domain

names as
data types; DROP TABLE

DROP TABLE $table [ RESTRICT | CASCADE ]

* IMPORTANT NOTE: drop behavior (cascade or restrict)

is
checked for valid syntax but is otherwise *IGNORED*

-- it
is recognized by the parser, but not by the execu

tion
engine

INSERT INTO

INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES (

$val1, ... $valN )

* default values are not currently supported
* inserting from a subquery is not currently support

ed

DELETE FROM

DELETE FROM $table [ WHERE search_condition ]

* see "search_condition" below

UPDATE

UPDATE $table SET $col1 = $val1, ... $colN = $valN [

WHERE search_condition ]

* default values are not currently supported
* see "search_condition" below

SELECT

SELECT select_clause
FROM from_clause

[ WHERE search_condition ]

[ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]

[ LIMIT [start,] length ]

* select clause ::=
[DISTINCT|ALL] *

| [DISTINCT|ALL] col1 [,col2, ... colN]
| set_function1 [,set_function2, ... set_func

tionN]

* set function ::=

COUNT ( [DISTINCT|ALL] * )

| COUNT | MIN | MAX | AVG | SUM ( [DIS

TINCT|ALL] col_name )

* from clause ::=

table1 [, table2, ... tableN]

| table1 NATURAL [join_type] JOIN table2
| table1 [join_type] table2 USING (col1,col2,

... colN)
| table1 [join_type] JOIN table2 ON (table1.co

lA = table2.colB)

* join type ::=

INNER

| [OUTER] LEFT | RIGHT | FULL

* if join_type is not specified, INNER is the de

fault
* if DISTINCT or ALL is not specified, ALL is the

default
* if start position is omitted from LIMIT clause,

position 0 is

the default

* ON clauses may only contain equal comparisons and

AND combiners
* self-joins are not currently supported
* if implicit joins are used, the WHERE clause must

contain

and equijoin condition for each table
SEARCH CONDITION: [NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN; $valN ]
OPERATORS: $op = | <> | < | > | <= | >=
| IS NULL | IS NOT NULL | LIKE | CLIKE | BE

TWEEN | IN
The "CLIKE" operator works exactly the same as the
"LIKE" operator, but is case insensitive. For example:: WHERE foo LIKE 'bar%' # succeeds if foo is "bar; baz"
# fails if foo is "BARBAZ"

or "Barbaz"
WHERE foo CLIKE 'bar%' # succeeds for "barbaz",
"Barbaz", and "BARBAZ"
STRING FUNCTIONS & MATH EXPRESSIONS: String functions and math expressions are supported in; WHERE
clauses, in the VALUES part of an INSERT and UPDATE
statements. They are not currently supported in the SE; LECT
statement. For example:

SELECT * FROM foo WHERE UPPER(bar) = 'baz' # SUP

PORTED

SELECT UPPER(foo) FROM bar # NOT

SUPPORTED; TRIM ( [ [LEADING|TRAILING|BOTH] ['trim_char'] FROM ]
string )
Removes all occurrences of <trim_char> from the front,
back, or both sides of a string.

BOTH is the default if neither LEADING nor TRAILING

is specified.

Space is the default if no trim_char is specified.

Examples:

TRIM( string )
trims leading and trailing spaces from string

TRIM( LEADING FROM str )

trims leading spaces from string

TRIM( 'x' FROM str )

trims leading and trailing x's from string

SUBSTRING( string FROM start_pos [FOR length] )

Returns the substring starting at start_pos and
extending for "length" character or until the end of
the string, if no "length" is supplied. Examples:

SUBSTRING( 'foobar' FROM 4 ) # returns "bar"

SUBSTRING( 'foobar' FROM 4 FOR 2) # returns "ba"

UPPER(string) and LOWER(string)

These return the upper-case and lower-case variants of
the string:

UPPER('foo') # returns "FOO"
LOWER('FOO') # returns "foo"

Identifiers (table & column names)

Regular identifiers (table and column names *without*
quotes around them) are case INSENSITIVE so column foo,
fOo, FOO all refer to the same column.

Delimited identifiers (table and column names *with*
quotes around them) are case SENSITIVE so column "foo",
"fOo", "FOO" each refer to different columns.

A delimited identifier is *never* equal to a regular iden
tifer (so "foo" and foo are two different columns). But
don't do that :-).

Remember thought that, in DBD::CSV if table names are used
directly as file names, the case sensitivity depends on
the OS e.g. on Windows files named foo, FOO, and fOo are
the same as each other while on Unix they are different.

METHODS

new()

The new() method creates a SQL::Parser object which can then be used to parse, validate, or build SQL strings. It takes one required parameter -- the name of the SQL dialect that will define the rules for the parser. A sec ond optional parameter is a reference to a hash which can contain additional attributes of the parser.: use SQL::Parser;
my $parser = SQL::Parser->new( $dialect_name, attrs );
The dialect_name parameter is a string containing any valid dialect such as 'ANSI', 'AnyData', or 'CSV'. See the section on the dialect() method below for details.
The attribute parameter is a reference to a hash that can contain error settings for the PrintError and RaiseError attributes. See the section below on the parse() method for details.
An example:: use SQL::Parser;
my $parser = SQL::Parser->new('AnyData', {RaiseError=>1}; );; This creates a new parser that uses the grammar rules
contained in the .../SQL/Dialects/AnyData.pm file and; which
sets the RaiseError attribute to true.
For those needing backwards compatibility with SQL::State ment version 0.1x and lower, the attribute hash may also contain feature settings. See the section "FURTHER DETAILS - Backwards Compatibility" below for details.
parse()
Once a SQL::Parser object has been created with the new() method, the parse() method can be used to parse any number of SQL strings. It takes a single required parameter -- a string containing a SQL command. The SQL string may optionally be terminated by a semicolon. The parse() method returns a true value if the parse is successful and a false value if the parse finds SQL syntax errors.
Examples:: 1) my $success = $parser->parse('SELECT * FROM foo');; 2) my $sql = 'SELECT * FROM foo';
my $success = $parser->parse( $sql );; 3) my $success = $parser->parse(qq!
SELECT id,phrase
FROM foo

WHERE id < 7

AND phrase <> 'bar'

ORDER BY phrase;; !);
4) my $success = $parser->parse('SELECT * FRoOM foo ');
In examples #1,#2, and #3, the value of $success will be true because the strings passed to the parse() method are valid SQL strings.
In example #4, however, the value of $success will be false because the string contains a SQL syntax error ('FRoOM' instead of 'FROM').
In addition to checking the return value of parse() with a variable like $success, you may use the PrintError and RaiseError attributes as you would in a DBI script:: * If PrintError is true, then SQL syntax errors will be; sent as
warnings to STDERR (i.e. to the screen or to a file if

STDERR
has been redirected). This is set to true by default

which
means that unless you specifically turn it off, all er

rors
will be reported.; * If RaiseError is true, then SQL syntax errors will; cause the
script to die, (i.e. the script will terminate unless

wrapped
in an eval). This is set to false by default which

means
that unless you specifically turn it on, scripts will
continue to operate even if there are SQL syntax er

rors.; Basically, you should leave PrintError on or else you will
not be warned when an error occurs. If you are simply
validating a series of strings, you will want to leave
RaiseError off so that the script can check all strings
regardless of whether some of them contain SQL errors.
However, if you are going to try to execute the SQL or
need to depend that it is correct, you should set RaiseEr
ror on so that the program will only continue to operate
if all SQL strings use correct syntax.; IMPORTANT NOTE #1: The parse() method only checks syntax, it does NOT verify if the objects listed actually exist.
For example, given the string "SELECT model FROM cars",
the parse() method will report that the string contains
valid SQL but that will not tell you whether there actu
ally is a table called "cars" or whether that table con
tains a column called 'model'. Those kinds of verifica
tions can be performed by the SQL::Statement module, not
by SQL::Parser by itself.; IMPORTANT NOTE #2: The parse() method uses rules as
defined by the selected dialect configuration file and the
feature() method. This means that a statement that is valid in one dialect may not be valid in another. For
example the 'CSV' and 'AnyData' dialects define 'BLOB' as
a valid data type but the 'ANSI' dialect does not. There
fore the statement 'CREATE TABLE foo (picture BLOB)' would
be valid in the first two dialects but would produce a
syntax error in the 'ANSI' dialect.; structure(); After a SQL::Parser object has been created and the
parse() method used to parse a SQL string, the structure() method returns the data structure of that string. This
data structure may be passed on to other modules (e.g.
SQL::Statement) or it may be printed out using, for exam
ple, the Data::Dumper module.; The data structure contains all of the information in the
SQL string as parsed into its various components. To take
a simple example:

$parser->parse('SELECT make,model FROM cars');
use Data::Dumper;
print Dumper $parser->structure;; Would produce:

$VAR1 = {
'column_names' => [
'make',
'model'

],

'command' => 'SELECT',
'table_names' => [

'cars'

]; };
Please see the section "FURTHER DETAILS -- Parse struc tures" below for further examples.
build()
This method is in progress and should be available soon.
dialect(): $parser->dialect( $dialect_name ); # load a dialect; configuration file
my $dialect = $parser->dialect; # get the name of; the current dialect; For example:

$parser->dialect('AnyData'); # loads the AnyData con

fig file
print $parser->dialect; # prints 'AnyData'; The $dialect_name parameter may be the name of any di; alect
configuration file on your system. Use the
$parser->list('dialects') method to see a list of avail; able
dialects. At a minimum it will include "ANSI", "CSV",; and
"AnyData". For backwards compatiblity 'Ansi' is accepted; as a
synonym for 'ANSI', otherwise the names are case sensi; tive.; Loading a new dialect configuration file erases all cur; rent
parser features and resets them to those defined in the
configuration file.; See the section above on "Dialects" for details of these
configuration files.
feature()
Features define the rules to be used by a specific parser instance. They are divided into the following classes:: * valid_commands
* valid_options
* valid_comparison_operators
* valid_data_types
* reserved_words
Within each class a feature name is either enabled or dis abled. For example, under "valid_data_types" the name "BLOB" may be either disabled or enabled. If it is not eneabled (either by being specifically disabled, or simply by not being specified at all) then any SQL string using "BLOB" as a data type will throw a syntax error "Invalid data type: 'BLOB'".
The feature() method allows you to enable, disable, or check the status of any feature.: $parser->feature( $class, $name, 1 ); # en; able a feature; $parser->feature( $class, $name, 0 ); # dis; able a feature; my $feature = $parser->feature( $class, $name ); # show; status of a feature; For example:; $parser->feature('reserved_words','FOO',1); # make; 'FOO' a reserved word; $parser->feature('valid_data_types','BLOB',0); # dis; allow 'BLOB' as a
# data

type

# de

termine if the LIKE
# oper

ator is supported; my $LIKE = $parser->feature('valid_operators','LIKE');
See the section below on "Backwards Compatibility" for use of the feature() method with SQL::Statement 0.1x style parameters.
list()
errstr()

FURTHER DETAILS

Dialect Configuration Files

These will change completely when Tim finalizes the DBI
get_info method.

Parse Structures

Here are some further examples of the data structures returned by the structure() method after a call to parse(). Only specific details are shown for each SQL instance, not the entire struture.: 'SELECT make,model, FROM cars'

command => 'SELECT',
table_names => [ 'cars' ],
column_names => [ 'make', 'model' ],; 'CREATE TABLE cars ( id INTEGER, model VARCHAR(40) )'

column_defs => {
id => { data_type => INTEGER },
model => { data_type => VARCHAR(40) },

},; 'SELECT DISTINCT make FROM cars'

set_quantifier => 'DISTINCT',; 'SELECT MAX (model) FROM cars'

set_function => {
name => 'MAX',
arg => 'models',

},; 'SELECT * FROM cars LIMIT 5,10'

limit_clause => {
offset => 5,
limit => 10,

},; 'SELECT * FROM vars ORDER BY make, model DESC'

sort_spec_list => [
{ make => 'ASC' },
{ model => 'DESC' },

],; "INSERT INTO cars VALUES ( 7, 'Chevy', 'Impala' )"

values => [ 7, 'Chevy', 'Impala' ],; Backwards Compatibility; This module can be used in conjunction with SQL::State
ment, version 0.2 and higher. Earlier versions of
SQL::Statement included a SQL::Parser as a submodule that
used slightly different syntax than the current version.
The current version supports all of this earlier syntax
although new users are encouraged to use the new syntax
listed above. If the syntax listed below is used, the
module should be able to be subclassed exactly as it was
with the older SQL::Statement versions and will therefore
not require any modules or scripts that used it to make
changes.; In the old style, features of the parser were accessed
with this syntax:

feature('create','type_blob',1); # allow BLOB as a data

type
feature('create','type_blob',0); # disallow BLOB as a da

ta type
feature('select','join',1); # allow multi-table

statements; The same settings could be acheieved in calls to new:

my $parser = SQL::Parser->new(
'Ansi',
{
create => {type_blob=>1},
select => {join=>1},

},

);; Both of these styles of setting features are supported in
the current SQL::Parser.

ACKNOWLEDGEMENTS

*Many* thanks to Ilya Sterin who wrote most of code for the: build() method and who assisted on the parentheses pars
ing code: and who proved a great deal of support, advice, and test
ing: throughout the development of the module.

AUTHOR & COPYRIGHT

The module may be freely distributed under the same terms
as Perl itself using either the "GPL License" or the "Artis
tic License" as specified in the Perl README file.
Jeff can be reached at: jeff@vpservices.com.

docs.sk

comprehensive documentation repository

Most Viewed