1SQL::Statement::StructuUrsee(r3)Contributed Perl DocumenStQaLt:i:oSntatement::Structure(3)
2
6 SQL::Statement::Structure - parse and examine structure of SQL queries
7
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 # these will work not before $stmt->execute()
25 printf "Order Columns %s\n",join(',', map {$_->column} $stmt->order() );
26
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
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 my $parser = SQL::Parser->new();
46 You can set the error-reporting for the parser the same way you do in
48 DBI:
49 $parser->{RaiseError}=1; # turn on die-on-error behaviour
51 $parser->{PrinteError}=1; # turn on warnings-on-error behaviour
52 As with DBI, RaiseError defaults to 0 (off) and PrintError defaults to
54 1 (on).
55 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 $parser->parse("LOAD MyLib::MySyntax");
63 $parser->parse("CREATE TYPE myDataType");
64 See SQL::Statement::Syntax for details of the supported SQL syntax and
66 for methods of extending the syntax.
67
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 my $stmt = SQL::Statement->new($sql, $parser);
74 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
82 The following methods can be used to obtain information about a query:
83 command
85 Returns the SQL command. See SQL::Statement::Syntax for supported
86 command. Example:
87 my $command = $stmt->command();
89 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 This method is used to retrieve column lists. The meaning depends on
96 the query command:
97 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 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 INSERT INTO $table VALUES (...)
108 and in CREATE or DROP statements.
110 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 INSERT INTO foo (bar) VALUES (1)
117 SELECT bar FROM foo WHERE ...
118 SELECT foo.bar FROM foo WHERE ...
119 In all these cases exactly one column instance would be returned with
121 $col->name() eq 'bar'
123 $col->table() eq 'foo'
124 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 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 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 The "params" method returns information about the input parameters used
142 in a statement. For example, consider the following:
143 INSERT INTO foo VALUES (?, ?)
145 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 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 This method is used for statements like
158 UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
160 $colN = $valN WHERE ...
161 INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),
162 ($val1, $val2, ..., $valN)
163 to read the values $val1, $val2, ... $valN. It returns (lists of)
165 scalar values or "SQL::Statement::Param" instances.
166 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 In SELECT statements you can use this for looking at the ORDER clause.
173 Example:
174 SELECT * FROM FOO ORDER BY id DESC, name
176 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 limit
183 my $limit = $stmt->limit();
184 In a SELECT statement you can use a "LIMIT" clause to implement
186 cursoring:
187 SELECT * FROM FOO LIMIT 5
189 SELECT * FROM FOO LIMIT 5, 5
190 SELECT * FROM FOO LIMIT 10, 5
191 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 offset
199 my $offset = $stmt->offset();
200 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 where_hash
206 my $where_hash = $stmt->where_hash();
207 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 The where clause nodes have (up to) 4 attributes:
214 op contains the operator, one of "AND", "OR", "=", "<>", ">=",
216 ">", "<=", "<", "LIKE", "CLIKE", "IS", "IN", "BETWEEN" or a
217 user defined operator, if any.
218 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 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 neg contains a TRUE value, if the operation result must be
232 negated after evaluation.
233 To illustrate the above, consider the following WHERE clause:
235 WHERE NOT (id > 2 AND name = 'joe') OR name IS NULL
237 We can represent this clause by the following tree:
239 (id > 2) (name = 'joe')
241 \ /
242 NOT AND
243 \ (name IS NULL)
244 \ /
245 OR
246 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 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 Of course there's a ready-for-use method for WHERE clause evaluation:
258 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 $val = $eval->param($paramNum);
265 $val = $eval->column($table, $column);
266 Once you have such an object, you can call eval_where;
268 $match = $stmt->eval_where($eval);
270 where
272 my $where = $stmt->where();
273 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 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 "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
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 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 If you are using a statement with placeholders, those can be passed to
302 execute after the $cache:
303 $stmt = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);
305 $stmt->execute( $cache, 7, 'foo' );
306 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 while (my $row = $stmt->fetch()) {
312 # ...
313 }
314 an example of executing and fetching
316 #!/usr/bin/perl -w
317 use strict;
318 use SQL::Statement;
319 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 This document may be freely modified and distributed under the same
345 terms as Perl itself.
346perl v5.32.1 2021-01-27 SQL::Statement::Structure(3)