SQL::Sp...tatement(3)

📦
version 1.00023 · 6 versions
, in opensuse-leap 16.0

💖 SQL::SplitStatement — the SQL::Sp...tatement(3) manual, bestie ✨

okay so this is the User Contributed Perl Documentation page for SQL::SplitStatement(3), and it is literally about splitting SQL into atomic statements. main character energy fr fr.

🌸 NAME 🌸

SQL::SplitStatement — Split any SQL code into atomic statements.

🦋 SYNOPSIS 🦋

bestie, here’s the whole vibe in code. Do not touch the semicolons, they are doing important work 💅

# Multiple SQL statements in a single string
my $sql_code = <<'SQL';
CREATE TABLE parent(a, b, c   , d    );
CREATE TABLE child (x, y, "w;", "z;z");
/* C-style comment; */
CREATE TRIGGER "check;delete;parent;" BEFORE DELETE ON parent WHEN
    EXISTS (SELECT 1 FROM child WHERE old.a = x AND old.b = y)
BEGIN
    SELECT RAISE(ABORT, 'constraint failed;'); -- Inline SQL comment
END;
-- Standalone SQL; comment; with semicolons;
INSERT INTO parent (a, b, c, d) VALUES ('pippo;', 'pluto;', NULL, NULL);
SQL

use SQL::SplitStatement;

my $sql_splitter = SQL::SplitStatement->new;
my @statements = $sql_splitter->split($sql_code);

# @statements now is:
#
# (
#     'CREATE TABLE parent(a, b, c   , d    )',
#     'CREATE TABLE child (x, y, "w;", "z;z")',
#     'CREATE TRIGGER "check;delete;parent;" BEFORE DELETE ON parent WHEN
#     EXISTS (SELECT 1 FROM child WHERE old.a = x AND old.b = y)
# BEGIN
#     SELECT RAISE(ABORT, \'constraint failed;\');
# END',
#     'INSERT INTO parent (a, b, c, d) VALUES (\'pippo;\', \'pluto;\', NULL, NULL)'
# )

🌈 DESCRIPTION 🌈

okay so SQL::SplitStatement is this simple module that tries to split any SQL code — even the non-standard-extension girlies (details are over in the SUPPORTED DBMSs section below) — into the atomic statements it’s composed of. periodt.

The logic is way more sophisticated than doing a raw split on the ; (semicolon) character. First, it recognizes various different statement terminator tokens (the list is below, iykyk), and then this module can correctly handle those tokens showing up inside identifiers, values, comments, BEGIN ... END blocks (even nested ones), dollar-quoted strings, MySQL custom DELIMITERs, procedural code, etc. — as partially exemplified in the SYNOPSIS above. 💅

But bestie, do not mistake this for a validating parser. Technically speaking, it’s just a context-sensitive tokenizer. Think of it as an in-progress heuristic approach that will gradually improve as test cases get reported. That also means, except for the LIMITATIONS detailed below, there is no known SQL code (to the author) that the most current release of this module can’t correctly split. no cap.

The test suite bundled with the distribution — which now includes the popular Sakila and Pagila sample db schemata, as detailed in the SHOWCASE section below — should give you an idea of what this module is capable of. slay. ✨

If your atomic statements are going to be fed to a DBMS, you are encouraged to use DBIx::MultiStatementDo instead. It uses this module and also (optionally) offers automatic transactions support, so that you get the all-or-nothing behavior you would probably want. 🫶

✨ METHODS ✨

🌸 new

  • SQL::SplitStatement->new( %options )
  • SQL::SplitStatement->new( \%options )

This one creates a brand-new SQL::SplitStatement object and hands it right back to you, bestie. It's chill like that — pass your options either as a plain hash or as a hashref, whichever vibe you're on. 🫶

new accepts the following Boolean options, and for documentation purposes we're grouping them into two little cliques: Formatting Options and DBMSs Specific Options.

🎀 Formatting Options

  • keep_terminators — a Boolean option that, when it's set to a false value (and false is literally the default, no cap), makes the trailing terminator token get yeeted from the returned atomic statements. Set it to a true value and the terminators stay. Periodt.

The possible terminators (which get treated as terminators depending on context) are:

  • ; (the semicolon character);
  • any string defined by the MySQL DELIMITER command;
  • a ; followed by a / (forward-slash character) on its own line;
  • a ; followed by a . (dot character) on its own line, followed by a / on its own line;
  • a / on its own line regardless of the preceding characters (only if the slash_terminates option, explained below, is set).

Those multi-line terminators up there? They're always treated as a single token, so they get discarded (or returned) as a whole — no splitting them up, and this holds regardless of the slash_terminates option value. 💅

If your statements are headed to a DBMS, you are advised to keep this option at its default (false) value, since some drivers/DBMSs really don't want the terminator sitting at the end of the (single) statement. Rude of them but okay.

Note that the last, possibly empty, statement of a given SQL text never has a trailing terminator. See below for an example. 👀
  • keep_terminator — an alias for the keep_terminators option explained above. Heads up though: if keep_terminators and keep_terminator are both passed to new, an exception is thrown. Not me picking favorites, it's just the rules. 💀
  • keep_extra_spaces — a Boolean option that, when false (default, obviously), trims the spaces (\s) around the statements. Set it true and those spaces get to stay.

When keep_terminators is also false, the terminator is discarded first (regardless of the spaces around it) and then the trailing spaces get trimmed. This guarantees that when keep_extra_spaces is false, your returned statements will never have trailing (nor leading) spaces — no matter what keep_terminators is doing. Iconic behavior honestly.

  • keep_comments — a Boolean option that, when false (default, as always), discards the comments in the returned statements. When true, the comments stay with their statements. ✨

Both SQL and multi-line C-style comments are recognized.

When kept, each comment comes back in the same string as the atomic statement it belongs to. A comment belongs to a statement if, in the original SQL code, it appears before the end of that statement and after the terminator of the previous statement (if one exists) — like this pseudo-SQL snippet:

/* This comment
will be returned
together with statement1 */

<statement1>; -- This will go with statement2
              -- (note the semicolon which closes statement1)

<statement2>
-- This with statement2 as well
  • keep_empty_statements — a Boolean option that, when false (default, we been knew), discards empty statements. Set it true and the empty statements come back instead. 🦋

A statement counts as empty when it contains no characters other than the terminator and space characters (\s).

A statement made up solely of comments is not recognized as empty, so it may still be returned even when keep_empty_statements is false. To dodge that, just leave keep_comments false too — problem solved, bestie.

But note: an empty statement is recognized as empty regardless of the value of the keep_terminators and keep_extra_spaces options. It's giving consistent.

These options are basically meant to be kept at their default (false) values — especially if the atomic statements are getting handed to a DBMS.

They're mainly for cosmetic reasons, or if you want to count how many atomic statements (empty ones included) your original SQL code was composed of. Main character energy for stat nerds. 💜

Another situation where they're useful (and in the general case, necessary, really) is when you want to retain the ability to verbatim rebuild the original SQL string from the returned statements:

my $verbatim_splitter = SQL::SplitStatement->new(
    keep_terminators      => 1,
    keep_extra_spaces     => 1,
    keep_comments         => 1,
    keep_empty_statements => 1
);

my @verbatim_statements = $verbatim_splitter->split($sql_string);

$sql_string eq join '', @verbatim_statements; # Always true, given the constructor above.

Other than this, again — you are recommended to stick with the defaults. Slay responsibly. 🎀

🌈 DBMSs Specific Options

The same syntactic structure can have different semantics across different SQL dialects, so sometimes you gotta help the parser make the right call. That's literally what these options are for. 🧚

  • slash_terminates — a Boolean option that, when true (and true is the default, yes bestie), allows a / (forward-slash) on its own line — even with no semicolon before it — to be admitted as a (possible) terminator.

Set it false and a forward-slash on its own line is treated as a statement terminator only if it's preceded by a semicolon or by a dot and a semicolon.

If you're dealing with Oracle's SQL, you should let this option be set, since a slash (alone, without a preceding semicolon) is sometimes used as a terminator — SQL*Plus permits it (on non-block statements).

With SQL dialects other than Oracle, there's the (theoretical) possibility that a slash on its own line can pass the additional checks and get considered a terminator (when it shouldn't be). That chance should be really tiny — it has never been observed in real world code, fr fr. Though negligible, setting this option to false rules that risk out anyway. 🔥

🌟 split

  • $sql_splitter->split( $sql_string )

This is the method that actually does the splitting — the one that breaks the SQL code into its atomic components. 👑

It returns a list containing the atomic statements, in the same order they appear in the original SQL code, returned according to the options explained above.

Note that, as mentioned above, an SQL string that terminates with a terminator token (like a semicolon, for example) contains a trailing empty statement — this is correct and it's treated accordingly (if keep_empty_statements is set to a true value):

my $sql_splitter = SQL::SplitStatement->new(
    keep_empty_statements => 1
);

my @statements = $sql_splitter->split( 'SELECT 1;' );

print 'The SQL code contains ' . scalar(@statements) . ' statements.';
# The SQL code contains 2 statements.

💖 split_with_placeholders

  • $sql_splitter->split_with_placeholders( $sql_string )

Works exactly like the split method above, except it also returns a list of integers — each one being the number of placeholders in the corresponding atomic statement. Obsessed. 🍓

More precisely, its return value is a list of two elements: the first is a reference to the list of atomic statements exactly as returned by split, and the second is a reference to the list of placeholder counts as explained above.

Here's an example:

# 4 statements (valid SQLite SQL)
my $sql_code = <<'SQL';
CREATE TABLE state (id, name);
INSERT INTO  state (id, name) VALUES (?, ?);
CREATE TABLE city  (id, name, state_id);
INSERT INTO  city  (id, name, state_id) VALUES (?, ?, ?)
SQL

my $splitter = SQL::SplitStatement->new;

my ( $statements, $placeholders )
    = $splitter->split_with_placeholders( $sql_code );

# $placeholders now is: [0, 2, 0, 3]

where the returned $placeholders list(ref) is read like this: first statement has 0 placeholders, second has 2, third has 0, and the fourth has 3. Math is mathing. ✨

The recognized placeholders are:

  • question mark placeholders, represented by the ? character;
  • dollar sign numbered placeholders, represented by the $1, $2, ..., $n strings;
  • named parameters, such as :foo, :bar, :baz etc.

🌸 keep_terminators

  • $sql_splitter->keep_terminators
  • $sql_splitter->keep_terminators( $boolean )

Getter/setter method for the keep_terminators option explained above.

🎀 keep_terminator

An alias for the keep_terminators method explained above.

🦋 keep_extra_spaces

  • $sql_splitter->keep_extra_spaces
  • $sql_splitter->keep_extra_spaces( $boolean )

Getter/setter method for the keep_extra_spaces option explained above.

💜 keep_comments

  • $sql_splitter->keep_comments
  • $sql_splitter->keep_comments( $boolean )

Getter/setter method for the keep_comments option explained above.

⭐ keep_empty_statements

  • $sql_splitter->keep_empty_statements
  • $sql_splitter->keep_empty_statements( $boolean )

Getter/setter method for the keep_empty_statements option explained above.

🔥 slash_terminates

  • $sql_splitter->slash_terminates
  • $sql_splitter->slash_terminates( $boolean )

Getter/setter method for the slash_terminates option explained above.

💖 SUPPORTED DBMSs 💖

Okay bestie, here’s the tea: SQL::SplitStatement is out here aiming to cover the widest possible range of DBMSs, SQL dialects and extensions (even the proprietary ones 👀), in a (nearly) fully transparent way for you. No cap.

Currently it’s been tested mainly on SQLite, PostgreSQL, MySQL and Oracle. Slay.

🧚 Procedural Extensions 🧚

Procedural code is by far the most complex to handle. Like, not even close.

Currently any block of code which starts with FUNCTION, PROCEDURE, DECLARE, CREATE or CALL is correctly recognized, as well as anonymous BEGIN ... END blocks, dollar quoted blocks and blocks delimited by a DELIMITER-defined custom terminator, therefore a wide range of procedural extensions should be handled correctly. However — and this is important — only PL/SQL, PL/PgSQL and MySQL code has been tested so far.

If you need also other procedural languages to be recognized, please let me know (possibly with some test cases). I’m literally asking nicely 🫶

😭 LIMITATIONS 😭

Bound to be plenty, given the heuristic nature of this module (and its ambitious goals). However, no limitations are currently known. Iconic, honestly.

Please report any problematic test case. Fr fr.

🌸 Non-limitations 🌸

To be split correctly, the given input must, in general, be syntactically valid SQL. For example, an unbalanced BEGIN or a misspelled keyword could, under certain circumstances, confuse the parser and make it trip over the next statement terminator, thus returning non-split statements. This should not be seen as a limitation though, as the original (invalid) SQL code would have been unusable anyway (remember that this is NOT a validating parser!). So don’t come for her 💅

✨ SHOWCASE ✨

To test the capabilities of this module, you can run it (or rather run sql-split) on the files t/data/sakila-schema.sql and t/data/pagila-schema.sql included in the distribution, which contain two quite large and complex real world db schemata, for MySQL and PostgreSQL respectively. Real-world slay.

For more information:

🎀 DEPENDENCIES 🎀

SQL::SplitStatement depends on the following modules:

  • Carp
  • Class::Accessor::Fast
  • List::MoreUtils
  • Regexp::Common
  • SQL::Tokenizer 0.22 or newer

👑 AUTHOR 👑

Emanuele Zeppieri, "<emazep@cpan.org>"

💀 BUGS 💀

No known bugs. Periodt.

Please report any bugs or feature requests to "bug-sql-SplitStatement at rt.cpan.org", or through the web interface at <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-SplitStatement>. I will be notified, and then you’ll automatically be notified of progress on your bug as I make changes. We love a feedback loop 🫶

🌈 SUPPORT 🌈

You can find documentation for this module with the perldoc command:

perldoc SQL::SplitStatement

You can also look for information at:

  • AnnoCPAN: Annotated CPAN documentation

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

  • CPAN Ratings

<http://cpanratings.perl.org/d/SQL-SplitStatement>

  • On MetaCPAN

<https://metacpan.org/pod/SQL::SplitStatement/>

🦋 ACKNOWLEDGEMENTS 🦋

Igor Sutton for his excellent SQL::Tokenizer, which made writing this module a joke. Literally a joke. In the best way. Obsessed.

🔥 SEE ALSO 🔥

  • DBIx::MultiStatementDo
  • sql-split

Copyright 2010-2011 Emanuele Zeppieri.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation, or the Artistic License.

See <http://dev.perl.org/licenses/> for more information.

Okay that’s the whole thing bestie, we ate and left no crumbs 🍓 This was perl v5.42.0, dated 2021-06-06 — main character energy from the SQL::Sp...tatement(3) side of the group chat. Bye 💅✨

📦 where this page comes from