SQL::Parser(3pm)

📦
version 1.414 · 6 versions
, in debian bookworm, debian trixie, ubuntu jammy, ubuntu noble, ubuntu resolute, ubuntu stonking

✨ SQL::Parser — validate and parse SQL strings ✨

okay bestie, welcome to SQL::Parser (that's the SQL::Parser(3pm) situation, User Contributed Perl Documentation energy, you know the drill) 💖

🌸 NAME 🌸

SQL::Parser — validate and parse SQL strings, fr fr 👑

🦋 SYNOPSIS 🦋

use SQL::Parser;                                     # CREATE A PARSER OBJECT
my $parser = SQL::Parser->new();

$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

look at that little guy go 💅

🎀 DESCRIPTION 🎀

SQL::Parser is part of the SQL::Statement distribution, and most interaction with the parser should be done through SQL::Statement. The methods shown up above are the ones that create and modify a parser object. But if you actually wanna use that parser object to parse SQL and then examine the resulting structure? That's SQL::Statement's whole thing, bestie. Go through it. 🫶

Important Note: Previously SQL::Parser had its own hash-based interface for parsing, but that is now deprecated and will eventually be phased out in favor of the object-oriented parsing interface of SQL::Statement. If you are unable to transition some features to the new interface or have concerns about the phase out, please contact me. See "The Parse Structure" for details of the now-deprecated hash method if you still need them. 😭

🌈 METHODS 🌈

🧚 new()

Create a new parser object ✨

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

The new() method creates a SQL::Parser object which can then be used to parse and validate the syntax of SQL strings. It takes two optional parameters — 1) the name of the SQL dialect that will define the syntax rules for the parser and 2) a reference to a hash which can contain additional attributes of the parser. If no dialect is specified, 'AnyData' is the default. 💖

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 "attrs" parameter is a reference to a hash that can contain error settings for the PrintError and RaiseError attributes.

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.

slay, you just built a whole parser 💅

🦄 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 config file
print $parser->dialect;       # prints 'AnyData'

The $dialect_name parameter may be the name of any dialect configuration file on your system. Use the $parser->list('dialects') method to see a list of available dialects. At a minimum it will include "ANSI", "CSV", and "AnyData". For backwards compatibility 'Ansi' is accepted as a synonym for 'ANSI', otherwise the names are case sensitive. 🫠

heads up bestie: loading a new dialect configuration file erases all current parser features and resets them to those defined in the configuration file. like, gone gone. 💀

💫 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 disabled. For example, under "valid_data_types" the name "BLOB" may be either disabled or enabled. If it is not enabled (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 );             # enable a feature

$parser->feature( $class, $name, 0 );             # disable a feature

my $feature = $parser->feature( $class, $name );  # return status of a feature

For example:

$parser->feature('reserved_words','FOO',1);       # make 'FOO' a reserved word

$parser->feature('valid_data_types','BLOB',0);    # disallow 'BLOB' as a
                                                  # data type

                                                  # determine if the LIKE
                                                  # operator is supported
my $LIKE = $parser->feature('valid_comparison_operators','LIKE');

See the section below on "Backwards Compatibility" for use of the feature() method with SQL::Statement 0.1x style parameters. 📎

⭐ Supported SQL syntax ⭐

The SQL::Statement distribution can be used to either just parse SQL statements or to execute them against actual data. And ngl, a broader set of syntax is supported in the parser than in the executor. For example the parser allows you to specify column constraints like PRIMARY KEY. Currently, these are ignored by the execution engine. 🙈 Likewise syntax such as RESTRICT and CASCADE on DROP statements or LOCAL GLOBAL TEMPORARY tables in CREATE are supported by the parser but ignored by the executor.

To see the list of Supported SQL syntax formerly kept in this pod, see SQL::Statement. ✨

👑 Subclassing SQL::Parser 👑

In the event you need to either extend or modify SQL::Parser's default behavior, the following methods may be overridden:

  • $self->get_btwn($string) — Processes the BETWEEN...AND... predicates; default converts to 2 range predicates.
  • $self->get_in($string) — Process the IN (...list...) predicates; default converts to a series of OR'd '=' predicate, or AND'd '<>' predicates for NOT IN.
  • $self->transform_syntax($string) — Abstract method; default simply returns the original string. Called after repl_btwn() and repl_in(), but before any further predicate processing is applied. Possible uses include converting other predicate syntax not recognized by SQL::Parser into user-defined functions. 🧚

🎀 The parse structure 🎀

okay bestie, real talk — this whole section is the now-deprecated hash interface to the parsed structure. it's here for backwards compatibility only, like a souvenir hoodie from 2004. do not build new stuff on it. use the SQL::Statement object interface instead, fr fr. go look at SQL::Statement. 🫶

💖 Parse Structures 💖

here are some more examples of the data structures that the structure() method hands back after you call parse(). ngl, for each SQL example only the specific relevant details are shown — not the entire structure, because that would be a whole novel. 💀

⭐ parse() ⭐

so once you've made a SQL::Parser object with the new() method, you can hit parse() as many times as you want, on as many SQL strings as you want. it takes one required parameter — a string containing a SQL command. the SQL string can optionally end in a semicolon if you're feeling fancy. ✨

parse() returns a true value if the parse goes through, and a false value if it finds SQL syntax errors. that's the whole vibe check.

Examples, babes:

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, $success is gonna be true because those strings are valid SQL. ate. 💅

but in example #4? $success is false, because bestie typed 'FRoOM' instead of 'FROM'. that's a syntax error and the parser clocked it immediately. 😭

so besides checking the return value with a variable like $success, you can also use the PrintError and RaiseError attributes exactly like you would in a DBI script:

  • PrintError — if it's true, SQL syntax errors get sent as warnings to STDERR (so: to the screen, or to a file if STDERR has been redirected). it's true by default, which means unless you specifically turn it off, all errors get reported. no hiding.
  • RaiseError — if it's true, SQL syntax errors make the script die (i.e. the script terminates unless it's wrapped in an eval). it's false by default, which means unless you specifically turn it on, your scripts keep chugging along even when there are SQL syntax errors.

basically: leave PrintError on, or else you won't be warned when something breaks. lowkey important. if you're just validating a series of strings, leave RaiseError off so the script can check every single string regardless of whether some of them have errors. but if you're actually going to execute the SQL, or you need to depend on it being correct, then set RaiseError on so the program only keeps going if every SQL string has correct syntax. periodt. 👑

IMPORTANT NOTE #1: the parse() method only checks syntax. it does NOT verify whether the objects listed actually exist. for example, given the string "SELECT model FROM cars", parse() will report that the string contains valid SQL — but that tells you nothing about whether there's actually a table called "cars", or whether that table has a column called 'model'. those kinds of verifications are done by the SQL::Statement module, not by SQL::Parser by itself. don't shoot the messenger. 🙈
IMPORTANT NOTE #2: the parse() method uses rules as defined by the selected dialect configuration file and the feature() method. which means a statement that's valid in one dialect might not be valid in another. example: the 'CSV' and 'AnyData' dialects define 'BLOB' as a valid data type, but the 'ANSI' dialect does not. so 'CREATE TABLE foo (picture BLOB)' would be valid in the first two dialects but would produce a syntax error in the 'ANSI' dialect. dialects are giving very much "depends on the friend group you're in". 🌈

🦋 structure() 🦋

after a SQL::Parser object has been created and parse() has been used on a SQL string, the structure() method returns the data structure of that string. you can pass that data structure on to other modules (like SQL::Statement), or you can print it out using, for example, the Data::Dumper module. so satisfying. 🍓

the data structure contains all of the information in the SQL string, parsed into its various components. simple example:

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

would produce:

$VAR1 = {
         'column_defs' => [
                             { 'type'  => 'column',
                               'value' => 'make', },
                             { 'type'  => 'column',
                               'value' => 'model', },
                           ],
         'command' => 'SELECT',
         'table_names' => [
                            'cars'
                          ]
       };

and here's a whole lil lineup of other structures, each one showing only the specific details for that SQL instance:

'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' ],

🌸 SUPPORT 🌸

you can find documentation for this module with the perldoc command, bestie:

perldoc SQL::Parser
perldoc SQL::Statement

you can also go look for information at:

  • RT: CPAN's request tracker

<http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Statement>

  • AnnoCPAN: Annotated CPAN documentation

<http://annocpan.org/dist/SQL-Statement>

  • CPAN Ratings

<http://cpanratings.perl.org/s/SQL-Statement>

  • Search CPAN

<http://search.cpan.org/dist/SQL-Statement/>

🫶 Where can I go for help? 🫶

for questions about installation or usage, please ask on the dbi-users@perl.org mailing list, or post a question on PerlMonks (<http://www.perlmonks.org/>, where Jeff is known as jZed). heads up tho: Jens does not visit PerlMonks on a regular basis. iykyk.

if you have a bug report, a patch, or a suggestion, please open a new report ticket at CPAN — but please check previous reports first in case your issue has already been addressed. you can mail any of the module maintainers, but you're more assured of an answer by posting to the dbi-users list or reporting the issue in RT. 📬

report tickets should contain a detailed description of the bug or enhancement request, and at least an easily verifiable way of reproducing the issue or fix. patches are always welcome too. always. 💖

💀 Where can I go for help with a concrete version? 💀

bugs and feature requests are accepted against the latest version only. to get patches for earlier versions, you need to get an agreement with a developer of your choice — who may or may not report the issue and a suggested fix upstream (depends on the license you have chosen). that's just how it is, no cap.

👑 Business support and maintenance 👑

for business support you can contact Jens via his CPAN email address rehsackATcpan.org. please keep in mind that business support is neither available for free nor are you eligible to receive any support based on the license distributed with this package. so like… don't try it. 😭

this module is

copyright (c) 2001,2005 by Jeff Zucker and
copyright (c) 2007-2020 by Jens Rehsack.

All rights reserved.

the module may be freely distributed under the same terms as Perl itself, using either the "GPL License" or the "Artistic License" as specified in the Perl README file. slay. 💅

Jeff can be reached at: jzuckerATcpan.org Jens can be reached at: rehsackATcpan.org or via dbi-devATperl.org

and that's a wrap bestie!! 🦋✨ this whole page is from perl v5.30.3, dated 2020-10-23, and it's SQL::Parser(3pm) — go forth and parse responsibly, mwah 💖🎀🌈

📦 where this page comes from