1SQL::Statement::StructuUrsee(r3)Contributed Perl DocumenStQaLt:i:oSntatement::Structure(3)
2
3
4

NAME

6       SQL::Statement::Structure - parse and examine structure of SQL queries
7

SYNOPSIS

9           use SQL::Statement;
10           my $sql    = "SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5,2";
11           my $parser = SQL::Parser->new();
12           $parser->{RaiseError}=1;
13           $parser->{PrintError}=0;
14           $parser->parse("LOAD 'MyLib::MySyntax' ");
15           my $stmt = SQL::Statement->new($sql,$parser);
16           printf "Command             %s\n",$stmt->command;
17           printf "Num of Placeholders %s\n",scalar $stmt->params;
18           printf "Columns             %s\n",join( ',', map {$_->name} $stmt->column_defs() );
19           printf "Tables              %s\n",join( ',', map {$_->name} $stmt->tables() );
20           printf "Where operator      %s\n",join( ',', $stmt->where->op() );
21           printf "Limit               %s\n",$stmt->limit();
22           printf "Offset              %s\n",$stmt->offset();
23
24           # these will work not before $stmt->execute()
25           printf "Order Columns       %s\n",join(',', map {$_->column} $stmt->order() );
26

DESCRIPTION

28       The SQL::Statement module can be used by itself, without DBI and
29       without a subclass to parse SQL statements and to allow you to examine
30       the structure of the statement (table names, column names, where clause
31       predicates, etc.).  It will also execute statements using in-memory
32       tables.  That means that you can create and populate some tables, then
33       query them and fetch the results of the queries as well as examine the
34       differences between statement metadata during different phases of
35       prepare, execute, fetch. See the remainder of this document for a
36       description of how to create and modify a parser object and how to use
37       it to parse and examine SQL statements.  See SQL::Statement for other
38       uses of the module.
39

Creating a parser object

41       The parser object only needs to be created once per script. It can then
42       be reused to parse any number of SQL statements. The basic creation of
43       a parser is this:
44
45           my $parser = SQL::Parser->new();
46
47       You can set the error-reporting for the parser the same way you do in
48       DBI:
49
50           $parser->{RaiseError}=1;   # turn on die-on-error behaviour
51           $parser->{PrinteError}=1;  # turn on warnings-on-error behaviour
52
53       As with DBI, RaiseError defaults to 0 (off) and PrintError defaults to
54       1 (on).
55
56       For many purposes, the built-in SQL syntax should be sufficient.
57       However, if you need to, you can change the behaviour of the parser by
58       extending the supported SQL syntax either by loading a file containing
59       definitions; or by issuing SQL commands that modify the way the parser
60       treats types, keywords, functions, and operators.
61
62           $parser->parse("LOAD MyLib::MySyntax");
63           $parser->parse("CREATE TYPE myDataType");
64
65       See SQL::Statement::Syntax for details of the supported SQL syntax and
66       for methods of extending the syntax.
67

Parsing SQL statements

69       While you only need to define a new SQL::Parser object once per script,
70       you need to define a new SQL::Statment object once for each statement
71       you want to parse.
72
73           my $stmt = SQL::Statement->new($sql, $parser);
74
75       The call to new() takes two arguments - the SQL string you want to
76       parse, and the SQL::Parser object you previously created.  The call to
77       new is the equivalent of a DBI call to prepare() - it parses the SQL
78       into a structure but does not attempt to execute the SQL unless you
79       explicitly call execute().
80

Examining the structure of SQL statements

82       The following methods can be used to obtain information about a query:
83
84   command
85       Returns the SQL command. See SQL::Statement::Syntax for supported
86       command. Example:
87
88           my $command = $stmt->command();
89
90   column definitions
91           my $numColumns = $stmt->column_defs();  # Scalar context
92           my @columnList = $stmt->column_defs();  # Array context
93           my($col1, $col2) = ($stmt->column_defs(0), $stmt->column_defs(1));
94
95       This method is used to retrieve column lists. The meaning depends on
96       the query command:
97
98           SELECT $col1, $col2, ... $colN FROM $table WHERE ...
99           UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
100               $colN = $valN WHERE ...
101           INSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)
102
103       When used without arguments, the method returns a list of the columns
104       $col1, $col2, ..., $colN, you may alternatively use a column number as
105       argument. Note that the column list may be empty as in
106
107           INSERT INTO $table VALUES (...)
108
109       and in CREATE or DROP statements.
110
111       But what does "returning a column" mean? It is returning an
112       "SQL::Statement::Util::Column" instance, a class that implements the
113       methods "table" and "name", both returning the respective scalar. For
114       example, consider the following statements:
115
116           INSERT INTO foo (bar) VALUES (1)
117           SELECT bar FROM foo WHERE ...
118           SELECT foo.bar FROM foo WHERE ...
119
120       In all these cases exactly one column instance would be returned with
121
122           $col->name() eq 'bar'
123           $col->table() eq 'foo'
124
125   tables
126           my $tableNum = $stmt->tables();  # Scalar context
127           my @tables = $stmt->tables();    # Array context
128           my($table1, $table2) = ($stmt->tables(0), $stmt->tables(1));
129
130       Similar to "columns", this method returns instances of
131       "SQL::Statement::Table". For UPDATE, DELETE, INSERT, CREATE and DROP, a
132       single table will always be returned.  SELECT statements can return
133       more than one table, in case of joins. Table objects offer a single
134       method, "name" which returns the table name.
135
136   params
137           my $paramNum = $stmt->params();  # Scalar context
138           my @params = $stmt->params();    # Array context
139           my($p1, $p2) = ($stmt->params(0), $stmt->params(1));
140
141       The "params" method returns information about the input parameters used
142       in a statement. For example, consider the following:
143
144           INSERT INTO foo VALUES (?, ?)
145
146       This would return two instances of "SQL::Statement::Param". Param
147       objects implement a single method, "$param-"num()>, which retrieves the
148       parameter number. (0 and 1, in the above example). As of now, not very
149       useful ... :-)
150
151   row_values
152           my $rowValueNum = $stmt->row_values(); # Scalar context
153           my @rowValues = $stmt->row_values(0);  # Array context
154           my($rval1, $rval2) = ($stmt->row_values(0,0),
155                                 $stmt->row_values(0,1));
156
157       This method is used for statements like
158
159           UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
160               $colN = $valN WHERE ...
161           INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),
162                                           ($val1, $val2, ..., $valN)
163
164       to read the values $val1, $val2, ... $valN. It returns (lists of)
165       scalar values or "SQL::Statement::Param" instances.
166
167   order
168           my $orderNum = $stmt->order();   # Scalar context
169           my @order = $stmt->order();      # Array context
170           my($o1, $o2) = ($stmt->order(0), $stmt->order(1));
171
172       In SELECT statements you can use this for looking at the ORDER clause.
173       Example:
174
175           SELECT * FROM FOO ORDER BY id DESC, name
176
177       In this case, "order" could return 2 instances of
178       "SQL::Statement::Order".  You can use the methods "$o->table()",
179       "$o->column()", "$o->direction()" and "$o->desc()" to examine the order
180       object.
181
182   limit
183           my $limit = $stmt->limit();
184
185       In a SELECT statement you can use a "LIMIT" clause to implement
186       cursoring:
187
188           SELECT * FROM FOO LIMIT 5
189           SELECT * FROM FOO LIMIT 5, 5
190           SELECT * FROM FOO LIMIT 10, 5
191
192       These three statements would retrieve the rows 0..4, 5..9, 10..14 of
193       the table FOO, respectively. If no "LIMIT" clause is used, then the
194       method "$stmt->limit" returns undef. Otherwise it returns the limit
195       number (the maximum number of rows) from the statement (5 or 10 for the
196       statements above).
197
198   offset
199           my $offset = $stmt->offset();
200
201       If no "LIMIT" clause is used, then the method "$stmt->limit" returns
202       undef. Otherwise it returns the offset number (the index of the first
203       row to be included in the limit clause).
204
205   where_hash
206           my $where_hash = $stmt->where_hash();
207
208       To manually evaluate the WHERE clause, fetch the topmost where clause
209       node with the "where_hash" method. Then evaluate the left-hand and
210       right-hand side of the operation, perhaps recursively. Once that is
211       done, apply the operator and finally negate the result, if required.
212
213       The where clause nodes have (up to) 4 attributes:
214
215       op          contains the operator, one of "AND", "OR", "=", "<>", ">=",
216                   ">", "<=", "<", "LIKE", "CLIKE", "IS", "IN", "BETWEEN" or a
217                   user defined operator, if any.
218
219       arg1        contains the left-hand side of the operator. This can be a
220                   scalar value, a hash containing column or function
221                   definition, a parameter definition (hash has attribute
222                   "type" defined) or another operation (hash has attribute
223                   "op" defined).
224
225       arg2        contains the right-hand side of the operator. This can be a
226                   scalar value, a hash containing column or function
227                   definition, a parameter definition (hash has attribute
228                   "type" defined) or another operation (hash has attribute
229                   "op" defined).
230
231       neg         contains a TRUE value, if the operation result must be
232                   negated after evaluation.
233
234       To illustrate the above, consider the following WHERE clause:
235
236           WHERE NOT (id > 2 AND name = 'joe') OR name IS NULL
237
238       We can represent this clause by the following tree:
239
240                     (id > 2)   (name = 'joe')
241                            \   /
242                 NOT         AND
243                                \      (name IS NULL)
244                                 \    /
245                                   OR
246
247       Thus the WHERE clause would return an SQL::Statement::Op instance with
248       the op() field set to 'OR'. The arg2() field would return another
249       SQL::Statement::Op instance with arg1() being the
250       SQL::Statement::Column instance representing id, the arg2() field
251       containing the value undef (NULL) and the op() field being 'IS'.
252
253       The arg1() field of the topmost Op instance would return an Op instance
254       with op() eq 'AND' and neg() returning TRUE. The arg1() and arg2()
255       fields would be Op's representing "id > 2" and "name = 'joe'".
256
257       Of course there's a ready-for-use method for WHERE clause evaluation:
258
259       The WHERE clause evaluation depends on an object being used for
260       fetching parameter and column values. Usually this can be an
261       SQL::Statement::RAM::Table object or SQL::Eval object, but in fact it
262       can be any object that supplies the methods
263
264           $val = $eval->param($paramNum);
265           $val = $eval->column($table, $column);
266
267       Once you have such an object, you can call eval_where;
268
269           $match = $stmt->eval_where($eval);
270
271   where
272           my $where = $stmt->where();
273
274       This method is used to examine the syntax tree of the "WHERE" clause.
275       It returns undef (if no "WHERE" clause was used) or an instance of
276       SQL::Statement::Term.
277
278       The where clause is evaluated automatically on the current selected row
279       of the table currently worked on when it's "value()" method is invoked.
280
281       "SQL::Statement" creates the object tree for where clause evaluation
282       directly after successfully parsing a statement from the given
283       "where_clause", if any.
284

Executing and fetching data from SQL statements

286   execute
287       When called from a DBD or other subclass of SQL::Statement, the
288       execute() method will be executed against whatever data-source
289       (persistent storage) is supplied by the DBD or the subclass (e.g. CSV
290       files for DBD::CSV, or BerkeleyDB for DBD::DBM). If you are using
291       SQL::Statement directly rather than as a subclass, you can call the
292       execute() method and the statements will be executed() using temporary
293       in-memory tables. When used directly, like that, you need to create a
294       cache hashref and pass it as the first argument to execute:
295
296         my $cache  = {};
297         my $parser = SQL::Parser->new();
298         my $stmt   = SQL::Statement->new('CREATE TABLE x (id INT)',$parser);
299         $stmt->execute( $cache );
300
301       If you are using a statement with placeholders, those can be passed to
302       execute after the $cache:
303
304         $stmt      = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);
305         $stmt->execute( $cache, 7, 'foo' );
306
307   fetch
308       Only a single "fetch()" method is provided - it returns a single row of
309       data as an arrayref. Use a loop to fetch all rows:
310
311        while (my $row = $stmt->fetch()) {
312            # ...
313        }
314
315   an example of executing and fetching
316        #!/usr/bin/perl -w
317        use strict;
318        use SQL::Statement;
319
320        my $cache={};
321        my $parser = SQL::Parser->new();
322        for my $sql(split /\n/,
323        "  CREATE TABLE a (b INT)
324           INSERT INTO a VALUES(1)
325           INSERT INTO a VALUES(2)
326           SELECT MAX(b) FROM a  "
327        )
328        {
329           $stmt = SQL::Statement->new($sql,$parser);
330           $stmt->execute($cache);
331           next unless $stmt->command eq 'SELECT';
332           while (my $row=$stmt->fetch)
333           {
334               print "@$row\n";
335           }
336        }
337        __END__
338
340       Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
341       reserved.  Copyright (c) 2009-2020, Jens Rehsack <rehsackATcpan.org>,
342       all rights reserved.
343
344       This document may be freely modified and distributed under the same
345       terms as Perl itself.
346
347
348
349perl v5.32.1                      2021-01-27      SQL::Statement::Structure(3)
Impressum