1SQL::SplitStatement(3)User Contributed Perl DocumentationSQL::SplitStatement(3)
2
3
4

NAME

6       SQL::SplitStatement - Split any SQL code into atomic statements
7

VERSION

9       version 1.00020
10

SYNOPSIS

12           # Multiple SQL statements in a single string
13       my $sql_code = <<'SQL';
14       CREATE TABLE parent(a, b, c   , d    );
15       CREATE TABLE child (x, y, "w;", "z;z");
16       /* C-style comment; */
17       CREATE TRIGGER "check;delete;parent;" BEFORE DELETE ON parent WHEN
18           EXISTS (SELECT 1 FROM child WHERE old.a = x AND old.b = y)
19       BEGIN
20           SELECT RAISE(ABORT, 'constraint failed;'); -- Inline SQL comment
21       END;
22       -- Standalone SQL; comment; with semicolons;
23       INSERT INTO parent (a, b, c, d) VALUES ('pippo;', 'pluto;', NULL, NULL);
24       SQL
25
26       use SQL::SplitStatement;
27
28       my $sql_splitter = SQL::SplitStatement->new; my @statements =
29       $sql_splitter->split($sql_code);
30
31       # @statements now is: # # ( #     'CREATE TABLE parent(a, b, c   , d
32       )', #     'CREATE TABLE child (x, y, "w;", "z;z")', #     'CREATE
33       TRIGGER "check;delete;parent;" BEFORE DELETE ON parent WHEN #
34       EXISTS (SELECT 1 FROM child WHERE old.a = x AND old.b = y) # BEGIN #
35       SELECT RAISE(ABORT, \'constraint failed;\'); # END', #     'INSERT INTO
36       parent (a, b, c, d) VALUES (\'pippo;\', \'pluto;\', NULL, NULL)' # )
37

DESCRIPTION

39       This is a simple module which tries to split any SQL code, even
40       including non-standard extensions (for the details see the "SUPPORTED
41       DBMSs" section below), into the atomic statements it is composed of.
42
43       The logic used to split the SQL code is more sophisticated than a raw
44       "split" on the ";" (semicolon) character: first, various different
45       statement terminator tokens are recognized (see below for the list),
46       then this module is able to correctly handle the presence of said
47       tokens inside identifiers, values, comments, "BEGIN ... END" blocks
48       (even nested), dollar-quoted strings, MySQL custom "DELIMITER"s,
49       procedural code etc., as (partially) exemplified in the "SYNOPSIS"
50       above.
51
52       Consider however that this is by no means a validating parser
53       (technically speaking, it's just a context-sensitive tokenizer). It
54       should rather be seen as an in-progress heuristic approach, which will
55       gradually improve as test cases will be reported. This also means that,
56       except for the "LIMITATIONS" detailed below, there is no known (to the
57       author) SQL code the most current release of this module can't
58       correctly split.
59
60       The test suite bundled with the distribution (which now includes the
61       popular Sakila and Pagila sample db schemata, as detailed in the
62       "SHOWCASE" section below) should give you an idea of the capabilities
63       of this module
64
65       If your atomic statements are to be fed to a DBMS, you are encouraged
66       to use DBIx::MultiStatementDo instead, which uses this module and also
67       (optionally) offers automatic transactions support, so that you'll have
68       the all-or-nothing behavior you would probably want.
69

METHODS

71   "new"
72       ·   "SQL::SplitStatement->new( %options )"
73
74       ·   "SQL::SplitStatement->new( \%options )"
75
76       It creates and returns a new SQL::SplitStatement object. It accepts its
77       options either as a hash or a hashref.
78
79       "new" takes the following Boolean options, which for documentation
80       purposes can be grouped in two sets: "Formatting Options" and "DBMSs
81       Specific Options".
82
83       Formatting Options
84
85       ·   "keep_terminators"
86
87           A Boolean option which causes, when set to a false value (which is
88           the default), the trailing terminator token to be discarded in the
89           returned atomic statements.  When set to a true value, the
90           terminators are kept instead.
91
92           The possible terminators (which are treated as such depending on
93           the context) are:
94
95           ·   ";" (the semicolon character);
96
97           ·   any string defined by the MySQL "DELIMITER" command;
98
99           ·   an ";" followed by an "/" (forward-slash character) on its own
100               line;
101
102           ·   an ";" followed by an "." (dot character) on its own line,
103               followed by an "/" on its own line;
104
105           ·   an "/" on its own line regardless of the preceding characters
106               (only if the "slash_terminates" option, explained below, is
107               set).
108
109           The multi-line terminators above are always treated as a single
110           token, that is they are discarded (or returned) as a whole
111           (regardless of the "slash_terminates" option value).
112
113           If your statements are to be fed to a DBMS, you are advised to keep
114           this option to its default (false) value, since some drivers/DBMSs
115           don't want the terminator to be present at the end of the (single)
116           statement.
117
118           (Note that the last, possibly empty, statement of a given SQL text,
119           never has a trailing terminator. See below for an example.)
120
121       ·   "keep_terminator"
122
123           An alias for the the "keep_terminators" option explained above.
124           Note that if "keep_terminators" and "keep_terminator" are both
125           passed to "new", an exception is thrown.
126
127       ·   "keep_extra_spaces"
128
129           A Boolean option which causes, when set to a false value (which is
130           the default), the spaces ("\s") around the statements to be
131           trimmed.  When set to a true value, these spaces are kept instead.
132
133           When "keep_terminators" is set to false as well, the terminator is
134           discarded first (regardless of the spaces around it) and the
135           trailing spaces are trimmed then. This ensures that if
136           "keep_extra_spaces" is set to false, the returned statements will
137           never have trailing (nor leading) spaces, regardless of the
138           "keep_terminators" value.
139
140       ·   "keep_comments"
141
142           A Boolean option which causes, when set to a false value (which is
143           the default), the comments to be discarded in the returned
144           statements. When set to a true value, they are kept with the
145           statements instead.
146
147           Both SQL and multi-line C-style comments are recognized.
148
149           When kept, each comment is returned in the same string with the
150           atomic statement it belongs to. A comment belongs to a statement if
151           it appears, in the original SQL code, before the end of that
152           statement and after the terminator of the previous statement (if it
153           exists), as shown in this pseudo-SQL snippet:
154
155               /* This comment
156               will be returned
157               together with statement1 */
158
159               <statement1>; -- This will go with statement2
160                             -- (note the semicolon which closes statement1)
161
162               <statement2>
163               -- This with statement2 as well
164
165       ·   "keep_empty_statements"
166
167           A Boolean option which causes, when set to a false value (which is
168           the default), the empty statements to be discarded. When set to a
169           true value, the empty statements are returned instead.
170
171           A statement is considered empty when it contains no characters
172           other than the terminator and space characters ("\s").
173
174           A statement composed solely of comments is not recognized as empty
175           and may therefore be returned even when "keep_empty_statements" is
176           false. To avoid this, it is sufficient to leave "keep_comments" to
177           false as well.
178
179           Note instead that an empty statement is recognized as such
180           regardless of the value of the options "keep_terminators" and
181           "keep_extra_spaces".
182
183       These options are basically to be kept to their default (false) values,
184       especially if the atomic statements are to be given to a DBMS.
185
186       They are intended mainly for cosmetic reasons, or if you want to count
187       by how many atomic statements, including the empty ones, your original
188       SQL code was composed of.
189
190       Another situation where they are useful (in the general case necessary,
191       really), is when you want to retain the ability to verbatim rebuild the
192       original SQL string from the returned statements:
193
194           my $verbatim_splitter = SQL::SplitStatement->new(
195               keep_terminators      => 1,
196               keep_extra_spaces     => 1,
197               keep_comments         => 1,
198               keep_empty_statements => 1
199           );
200
201           my @verbatim_statements = $verbatim_splitter->split($sql_string);
202
203           $sql_string eq join '', @verbatim_statements; # Always true, given the constructor above.
204
205       Other than this, again, you are recommended to stick with the defaults.
206
207       DBMSs Specific Options
208
209       The same syntactic structure can have different semantics across
210       different SQL dialects, so sometimes it is necessary to help the parser
211       to make the right decision. This is the function of these options.
212
213       ·   "slash_terminates"
214
215           A Boolean option which causes, when set to a true value (which is
216           the default), a "/" (forward-slash) on its own line, even without a
217           preceding semicolon, to be admitted as a (possible) terminator.
218
219           If set to false, a forward-slash on its own line is treated as a
220           statement terminator only if preceded by a semicolon or by a dot
221           and a semicolon.
222
223           If you are dealing with Oracle's SQL, you should let this option
224           set, since a slash (alone, without a preceding semicolon) is
225           sometimes used as a terminator, as it is permitted by SQL*Plus (on
226           non-block statements).
227
228           With SQL dialects other than Oracle, there is the (theoretical)
229           possibility that a slash on its own line can pass the additional
230           checks and be considered a terminator (while it shouldn't). This
231           chance should be really tiny (it has never been observed in real
232           world code indeed). Though negligible, by setting this option to
233           false that risk can anyway be ruled out.
234
235   "split"
236       ·   "$sql_splitter->split( $sql_string )"
237
238       This is the method which actually splits the SQL code into its atomic
239       components.
240
241       It returns a list containing the atomic statements, in the same order
242       they appear in the original SQL code. The atomic statements are
243       returned according to the options explained above.
244
245       Note that, as mentioned above, an SQL string which terminates with a
246       terminator token (for example a semicolon), contains a trailing empty
247       statement: this is correct and it is treated accordingly (if
248       "keep_empty_statements" is set to a true value):
249
250           my $sql_splitter = SQL::SplitStatement->new(
251               keep_empty_statements => 1
252           );
253
254           my @statements = $sql_splitter->split( 'SELECT 1;' );
255
256           print 'The SQL code contains ' . scalar(@statements) . ' statements.';
257           # The SQL code contains 2 statements.
258
259   "split_with_placeholders"
260       ·   "$sql_splitter->split_with_placeholders( $sql_string )"
261
262       It works exactly as the "split" method explained above, except that it
263       returns also a list of integers, each of which is the number of the
264       placeholders contained in the corresponding atomic statement.
265
266       More precisely, its return value is a list of two elements, the first
267       of which is a reference to the list of the atomic statements exactly as
268       returned by the "split" method, while the second is a reference to the
269       list of the number of placeholders as explained above.
270
271       Here is an example:
272
273           # 4 statements (valid SQLite SQL)
274       my $sql_code = <<'SQL';
275       CREATE TABLE state (id, name);
276       INSERT INTO  state (id, name) VALUES (?, ?);
277       CREATE TABLE city  (id, name, state_id);
278       INSERT INTO  city  (id, name, state_id) VALUES (?, ?, ?)
279       SQL
280
281       my $splitter = SQL::SplitStatement->new;
282
283       my ( $statements, $placeholders )
284           = $splitter->split_with_placeholders( $sql_code );
285
286       # $placeholders now is: [0, 2, 0, 3]
287
288       where the returned $placeholders list(ref) is to be read as follows:
289       the first statement contains 0 placeholders, the second 2, the third 0
290       and the fourth 3.
291
292       The recognized placeholders are:
293
294       ·   question mark placeholders, represented by the "?" character;
295
296       ·   dollar sign numbered placeholders, represented by the "$1, $2, ...,
297           $n" strings;
298
299       ·   named parameters, such as ":foo", ":bar", ":baz" etc.
300
301   "keep_terminators"
302       ·   "$sql_splitter->keep_terminators"
303
304       ·   "$sql_splitter->keep_terminators( $boolean )"
305
306           Getter/setter method for the "keep_terminators" option explained
307           above.
308
309   "keep_terminator"
310       An alias for the "keep_terminators" method explained above.
311
312   "keep_extra_spaces"
313       ·   "$sql_splitter->keep_extra_spaces"
314
315       ·   "$sql_splitter->keep_extra_spaces( $boolean )"
316
317           Getter/setter method for the "keep_extra_spaces" option explained
318           above.
319
320   "keep_comments"
321       ·   "$sql_splitter->keep_comments"
322
323       ·   "$sql_splitter->keep_comments( $boolean )"
324
325           Getter/setter method for the "keep_comments" option explained
326           above.
327
328   "keep_empty_statements"
329       ·   "$sql_splitter->keep_empty_statements"
330
331       ·   "$sql_splitter->keep_empty_statements( $boolean )"
332
333           Getter/setter method for the "keep_empty_statements" option
334           explained above.
335
336   "slash_terminates"
337       ·   "$sql_splitter->slash_terminates"
338
339       ·   "$sql_splitter->slash_terminates( $boolean )"
340
341           Getter/setter method for the "slash_terminates" option explained
342           above.
343

SUPPORTED DBMSs

345       SQL::SplitStatement aims to cover the widest possible range of DBMSs,
346       SQL dialects and extensions (even proprietary), in a (nearly) fully
347       transparent way for the user.
348
349       Currently it has been tested mainly on SQLite, PostgreSQL, MySQL and
350       Oracle.
351
352   Procedural Extensions
353       Procedural code is by far the most complex to handle.
354
355       Currently any block of code which start with "FUNCTION", "PROCEDURE",
356       "DECLARE", "CREATE" or "CALL" is correctly recognized, as well as
357       anonymous "BEGIN ... END" blocks, dollar quoted blocks and blocks
358       delimited by a "DELIMITER"-defined custom terminator, therefore a wide
359       range of procedural extensions should be handled correctly. However,
360       only PL/SQL, PL/PgSQL and MySQL code has been tested so far.
361
362       If you need also other procedural languages to be recognized, please
363       let me know (possibly with some test cases).
364

LIMITATIONS

366       Bound to be plenty, given the heuristic nature of this module (and its
367       ambitious goals). However, no limitations are currently known.
368
369       Please report any problematic test case.
370
371   Non-limitations
372       To be split correctly, the given input must, in general, be
373       syntactically valid SQL. For example, an unbalanced "BEGIN" or a
374       misspelled keyword could, under certain circumstances, confuse the
375       parser and make it trip over the next statement terminator, thus
376       returning non-split statements.  This should not be seen as a
377       limitation though, as the original (invalid) SQL code would have been
378       unusable anyway (remember that this is NOT a validating parser!)
379

SHOWCASE

381       To test the capabilities of this module, you can run it (or rather run
382       sql-split) on the files t/data/sakila-schema.sql and
383       t/data/pagila-schema.sql included in the distribution, which contain
384       two quite large and complex real world db schemata, for MySQL and
385       PostgreSQL respectively.
386
387       For more information:
388
389       ·   Sakila db: <http://dev.mysql.com/doc/sakila/en/sakila.html>
390
391       ·   Pagila db: <http://pgfoundry.org/projects/dbsamples>
392

DEPENDENCIES

394       SQL::SplitStatement depends on the following modules:
395
396       ·   Carp
397
398       ·   Class::Accessor::Fast
399
400       ·   List::MoreUtils
401
402       ·   Regexp::Common
403
404       ·   SQL::Tokenizer 0.22 or newer
405

AUTHOR

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

BUGS

410       No known bugs.
411
412       Please report any bugs or feature requests to "bug-sql-SplitStatement
413       at rt.cpan.org", or through the web interface at
414       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-SplitStatement>.  I
415       will be notified, and then you'll automatically be notified of progress
416       on your bug as I make changes.
417

SUPPORT

419       You can find documentation for this module with the perldoc command:
420
421           perldoc SQL::SplitStatement
422
423       You can also look for information at:
424
425       ·   RT: CPAN's request tracker
426
427           <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-SplitStatement>
428
429       ·   AnnoCPAN: Annotated CPAN documentation
430
431           <http://annocpan.org/dist/SQL-SplitStatement>
432
433       ·   CPAN Ratings
434
435           <http://cpanratings.perl.org/d/SQL-SplitStatement>
436
437       ·   Search CPAN
438
439           <http://search.cpan.org/dist/SQL-SplitStatement/>
440

ACKNOWLEDGEMENTS

442       Igor Sutton for his excellent SQL::Tokenizer, which made writing this
443       module a joke.
444

SEE ALSO

446       ·   DBIx::MultiStatementDo
447
448       ·   sql-split
449

LICENSE AND COPYRIGHT

451       Copyright 2010-2011 Emanuele Zeppieri.
452
453       This program is free software; you can redistribute it and/or modify it
454       under the terms of either: the GNU General Public License as published
455       by the Free Software Foundation, or the Artistic License.
456
457       See http://dev.perl.org/licenses/ for more information.
458
459
460
461perl v5.30.0                      2019-07-26            SQL::SplitStatement(3)