1SQL::Statement::StructuUrsee(r3)Contributed Perl DocumenStQaLt:i:oSntatement::Structure(3)
2
6 SQL::Statement::Structure - parse & examine structure of SQL queries
7
9 use SQL::Statement;
10 my $sql = "
11 SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5,2
12 ";
13 my $parser = SQL::Parser->new();
14 $parser->{RaiseError}=1;
15 $parser->{PrintError}=0;
16 $parser->parse("LOAD 'MyLib::MySyntax' ");
17 my $stmt = SQL::Statement->new($sql,$parser);
18 printf "Command %s\n",$stmt->command;
19 printf "Num of Placeholders %s\n",scalar $stmt->params;
20 printf "Columns %s\n",join',',map{$_->name}$stmt->columns;
21 printf "Tables %s\n",join',',$stmt->tables;
22 printf "Where operator %s\n",join',',$stmt->where->op;
23 printf "Limit %s\n",$stmt->limit;
24 printf "Offset %s\n",$stmt->offset;
25 printf "Order Columns %s\n",join',',map{$_->column}$stmt->order;
26 __END__
27
29 The SQL::Statement module can be used by itself, without DBI and with‐
30 out a subclass to parse SQL statements and allow you to examine the
31 structure of the statement (table names, column names, where clause
32 predicates, etc.). It will also execute statements using in-memory
33 tables. That means that you can create and populate some tables, then
34 query them and fetch the results of the queries as well as examine the
35 differences between statement metadata during different phases of pre‐
36 pare, execute, fetch. See the remainder of this document for a
37 description of how to create and modify a parser object, how to use it
38 to parse and examine SQL statements. See SQL::Statement for other
39 usages of the module.
40
42 The parser object only needs to be created once per script. It can then
43 be reused to parse any number of SQL statements. The basic creation of
44 a parser is this:
45 my $parser = SQL::Parser->new();
47 You can set the error-reporting for the parser the same way you do in
49 DBI:
50 $parser->{RaiseError}=1; # turn on die-on-error behaviour
52 $parser->{PrinteError}=1; # turn on warnings-on-error behaviour
53 As with DBI, RaiseError defaults to 0 (off) and PrintError defaults to
55 1 (on).
56 For many purposes, the built-in SQL syntax should be sufficient. How‐
58 ever, if you need to, you can change the behaviour of the parser by
59 extending the supported SQL syntax either by loading a file containing
60 definitions; or by issuing SQL commands that modify the way the parser
61 treats types, keywords, functions, and operators.
62 $parser->parse("LOAD MyLib::MySyntax");
64 $parser->parse("CREATE TYPE myDataType");
65 See SQL::Statement::Syntax for details of the supported SQL syntax and
67 for methods of extending the syntax.
68
70 While you only need to define a new SQL::Parser object once per script,
71 you need to define a new SQL::Statment object once for each statement
72 you want to parse.
73 my $stmt = SQL::Statement->new($sql, $parser);
75 The call to new() takes two arguments - the SQL string you want to
77 parse, and the SQL::Parser object you previously created. The call to
78 new is the equivalent of a DBI call to prepare() - it parses the SQL
79 into a structure but doesn't attempt to execute the SQL unless you
80 explicitly call execute().
81
83 The following methods can be used to obtain information about a query:
84 command
86 Returns the SQL command. See SQL::Statement::Syntax for supported com‐
88 mand. Example:
89 my $command = $stmt->command();
91 columns
93 my $numColumns = $stmt->columns(); # Scalar context
95 my @columnList = $stmt->columns(); # Array context
96 my($col1, $col2) = ($stmt->columns(0), $stmt->columns(1));
97 This method is used to retrieve column lists. The meaning depends on
99 the query command:
100 SELECT $col1, $col2, ... $colN FROM $table WHERE ...
102 UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
103 $colN = $valN WHERE ...
104 INSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)
105 When used without arguments, the method returns a list of the columns
107 $col1, $col2, ..., $colN, you may alternatively use a column number as
108 argument. Note that the column list may be empty, like in
109 INSERT INTO $table VALUES (...)
111 and in CREATE or DROP statements.
113 But what does "returning a column" mean? It is returning an SQL::State‐
115 ment::Column instance, a class that implements the methods "table" and
116 "name", both returning the respective scalar. For example, consider the
117 following statements:
118 INSERT INTO foo (bar) VALUES (1)
120 SELECT bar FROM foo WHERE ...
121 SELECT foo.bar FROM foo WHERE ...
122 In all these cases exactly one column instance would be returned with
124 $col->name() eq 'bar'
126 $col->table() eq 'foo'
127 tables
129 my $tableNum = $stmt->tables(); # Scalar context
131 my @tables = $stmt->tables(); # Array context
132 my($table1, $table2) = ($stmt->tables(0), $stmt->tables(1));
133 Similar to "columns", this method returns instances of "SQL::State‐
135 ment::Table". For UPDATE, DELETE, INSERT, CREATE and DROP, a single
136 table will always be returned. SELECT statements can return more than
137 one table, in case of joins. Table objects offer a single method,
138 "name" which
139 returns the table name.
141 params
143 my $paramNum = $stmt->params(); # Scalar context
145 my @params = $stmt->params(); # Array context
146 my($p1, $p2) = ($stmt->params(0), $stmt->params(1));
147 The "params" method returns information about the input parameters used
149 in a statement. For example, consider the following:
150 INSERT INTO foo VALUES (?, ?)
152 This would return two instances of SQL::Statement::Param. Param objects
154 implement a single method, "$param-"num()>, which retrieves the parame‐
155 ter number. (0 and 1, in the above example). As of now, not very use‐
156 full ... :-)
157 row_values
159 my $rowValueNum = $stmt->row_values(); # Scalar context
161 my @rowValues = $stmt->row_values(); # Array context
162 my($rval1, $rval2) = ($stmt->row_values(0),
163 $stmt->row_values(1));
164 This method is used for statements like
166 UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
168 $colN = $valN WHERE ...
169 INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN)
170 to read the values $val1, $val2, ... $valN. It returns scalar values or
172 SQL::Statement::Param instances.
173 order
175 my $orderNum = $stmt->order(); # Scalar context
177 my @order = $stmt->order(); # Array context
178 my($o1, $o2) = ($stmt->order(0), $stmt->order(1));
179 In SELECT statements you can use this for looking at the ORDER clause.
181 Example:
182 SELECT * FROM FOO ORDER BY id DESC, name
184 In this case, "order" could return 2 instances of SQL::State‐
186 ment::Order. You can use the methods "$o->table()", "$o->column()" and
187 "$o->desc()" to examine the order object.
188 limit
190 my $limit = $stmt->limit();
192 In a SELECT statement you can use a "LIMIT" clause to implement cursor‐
194 ing:
195 SELECT * FROM FOO LIMIT 5
197 SELECT * FROM FOO LIMIT 5, 5
198 SELECT * FROM FOO LIMIT 10, 5
199 These three statements would retrieve the rows 0..4, 5..9, 10..14 of
201 the table FOO, respectively. If no "LIMIT" clause is used, then the
202 method "$stmt->limit" returns undef. Otherwise it returns the limit
203 number (the maximum number of rows) from the statement (5 or 10 for the
204 statements above).
205 offset
207 my $offset = $stmt->offset();
209 If no "LIMIT" clause is used, then the method "$stmt->limit" returns
211 undef. Otherwise it returns the offset number (the index of the first
212 row to be inlcuded in the limit clause).
213 where
215 my $where = $stmt->where();
217 This method is used to examine the syntax tree of the "WHERE" clause.
219 It returns undef (if no WHERE clause was used) or an instance of
220 SQL::Statement::Op. The Op instance offers 4 methods:
221 op returns the operator, one of "AND", "OR", "=", "<>", ">=",
223 ">", "<=", "<", "LIKE", "CLIKE" or "IS".
224 arg1
226 arg2 returns the left-hand and right-hand sides of the operator.
227 This can be a scalar value, an SQL::Statement::Param object
228 or yet another SQL::Statement::Op instance.
229 neg returns a TRUE value, if the operation result must be
231 negated after evalution.
232 To evaluate the WHERE clause, fetch the topmost Op instance with the
234 "where" method. Then evaluate the left-hand and right-hand side of the
235 operation, perhaps recursively. Once that is done, apply the operator
236 and finally negate the result, if required.
237 To illustrate the above, consider the following WHERE clause:
239 WHERE NOT (id > 2 AND name = 'joe') OR name IS NULL
241 We can represent this clause by the following tree:
243 (id > 2) (name = 'joe')
245 \ /
246 NOT AND
247 \ (name IS NULL)
248 \ /
249 OR
250 Thus the WHERE clause would return an SQL::Statement::Op instance with
252 the op() field set to 'OR'. The arg2() field would return another
253 SQL::Statement::Op instance with arg1() being the SQL::Statement::Col‐
254 umn instance representing id, the arg2() field containing the value
255 undef (NULL) and the op() field being 'IS'.
256 The arg1() field of the topmost Op instance would return an Op instance
258 with op() eq 'AND' and neg() returning TRUE. The arg1() and arg2()
259 fields would be Op's representing "id > 2" and "name = 'joe'".
260 Of course there's a ready-for-use method for WHERE clause evaluation:
262 The WHERE clause evaluation depends on an object being used for fetch‐
264 ing parameter and column values. Usually this can be an SQL::State‐
265 ment::RAM::Table object or SQL::Eval object, but in fact it can be any
266 object that supplies the methods
267 $val = $eval->param($paramNum);
269 $val = $eval->column($table, $column);
270 Once you have such an object, you can call eval_where;
272 $match = $stmt->eval_where($eval);
274
276 execute
277 When called from a DBD or other subclass of SQL::Statement, the exe‐
279 cute() method will be executed against whatever datasource (persistant
280 storage) is supplied by the DBD or the subclass (e.g. CSV files for
281 DBD::CSV, or BerkeleyDB for DBD::DBM). If you are using SQL::Statement
282 directly rather than as a subclass, you can call the execute() method
283 and the statements will be executed() using temporary in-memory tables.
284 When used directly, like that, you need to create a cache hashref and
285 pass it as the first argument to execute:
286 my $cache = {};
288 my $parser = SQL::Parser->new();
289 my $stmt = SQL::Statement->new('CREATE TABLE x (id INT)',$parser);
290 $stmt->execute( $cache );
291 If you are using a statement with placeholders, those can be passed to
293 execute after the $cache:
294 $stmt = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);
296 $stmt->execute( $cache, 7, 'foo' );
297 fetch
299 Only a single fetch() method is provided - it returns a single row of
301 data as an arrayref. Use a loop to fetch all rows:
302 while (my $row = $stmt->fetch) {
304 # ...
305 }
306 an example of executing and fetching
308 #!/usr/bin/perl -w
310 use strict;
311 use SQL::Statement;
312 my $cache={};
314 my $parser = SQL::Parser->new();
315 for my $sql(split /\n/,
316 " CREATE TABLE a (b INT)
317 INSERT INTO a VALUES(1)
318 INSERT INTO a VALUES(2)
319 SELECT MAX(b) FROM a "
320 ){
321 $stmt = SQL::Statement->new($sql,$parser);
322 $stmt->execute($cache);
323 next unless $stmt->command eq 'SELECT';
324 while (my $row=$stmt->fetch) {
325 print "@$row\n";
326 }
327 }
328 __END__
329
331 Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
332 reserved.
333 This document may be freely modified and distributed under the same
335 terms as Perl itself.
336perl v5.8.8 2005-04-18 SQL::Statement::Structure(3)