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 printf "Order Columns %s\n",join(',', map {$_->column} $stmt->order() );
24
26 The SQL::Statement module can be used by itself, without DBI and
27 without a subclass to parse SQL statements and to allow you to examine
28 the structure of the statement (table names, column names, where clause
29 predicates, etc.). It will also execute statements using in-memory
30 tables. That means that you can create and populate some tables, then
31 query them and fetch the results of the queries as well as examine the
32 differences between statement metadata during different phases of
33 prepare, execute, fetch. See the remainder of this document for a
34 description of how to create and modify a parser object and how to use
35 it to parse and examine SQL statements. See SQL::Statement for other
36 uses of the module.
37
39 The parser object only needs to be created once per script. It can then
40 be reused to parse any number of SQL statements. The basic creation of
41 a parser is this:
42 my $parser = SQL::Parser->new();
44 You can set the error-reporting for the parser the same way you do in
46 DBI:
47 $parser->{RaiseError}=1; # turn on die-on-error behaviour
49 $parser->{PrinteError}=1; # turn on warnings-on-error behaviour
50 As with DBI, RaiseError defaults to 0 (off) and PrintError defaults to
52 1 (on).
53 For many purposes, the built-in SQL syntax should be sufficient.
55 However, if you need to, you can change the behaviour of the parser by
56 extending the supported SQL syntax either by loading a file containing
57 definitions; or by issuing SQL commands that modify the way the parser
58 treats types, keywords, functions, and operators.
59 $parser->parse("LOAD MyLib::MySyntax");
61 $parser->parse("CREATE TYPE myDataType");
62 See SQL::Statement::Syntax for details of the supported SQL syntax and
64 for methods of extending the syntax.
65
67 While you only need to define a new SQL::Parser object once per script,
68 you need to define a new SQL::Statment object once for each statement
69 you want to parse.
70 my $stmt = SQL::Statement->new($sql, $parser);
72 The call to new() takes two arguments - the SQL string you want to
74 parse, and the SQL::Parser object you previously created. The call to
75 new is the equivalent of a DBI call to prepare() - it parses the SQL
76 into a structure but does not attempt to execute the SQL unless you
77 explicitly call execute().
78
80 The following methods can be used to obtain information about a query:
81 command
83 Returns the SQL command. See SQL::Statement::Syntax for supported
84 command. Example:
85 my $command = $stmt->command();
87 column definitions
89 my $numColumns = $stmt->column_defs(); # Scalar context
90 my @columnList = $stmt->column_defs(); # Array context
91 my($col1, $col2) = ($stmt->column_defs(0), $stmt->column_defs(1));
92 This method is used to retrieve column lists. The meaning depends on
94 the query command:
95 SELECT $col1, $col2, ... $colN FROM $table WHERE ...
97 UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
98 $colN = $valN WHERE ...
99 INSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)
100 When used without arguments, the method returns a list of the columns
102 $col1, $col2, ..., $colN, you may alternatively use a column number as
103 argument. Note that the column list may be empty as in
104 INSERT INTO $table VALUES (...)
106 and in CREATE or DROP statements.
108 But what does "returning a column" mean? It is returning an
110 "SQL::Statement::Util::Column" instance, a class that implements the
111 methods "table" and "name", both returning the respective scalar. For
112 example, consider the following statements:
113 INSERT INTO foo (bar) VALUES (1)
115 SELECT bar FROM foo WHERE ...
116 SELECT foo.bar FROM foo WHERE ...
117 In all these cases exactly one column instance would be returned with
119 $col->name() eq 'bar'
121 $col->table() eq 'foo'
122 tables
124 my $tableNum = $stmt->tables(); # Scalar context
125 my @tables = $stmt->tables(); # Array context
126 my($table1, $table2) = ($stmt->tables(0), $stmt->tables(1));
127 Similar to "columns", this method returns instances of
129 "SQL::Statement::Table". For UPDATE, DELETE, INSERT, CREATE and DROP, a
130 single table will always be returned. SELECT statements can return
131 more than one table, in case of joins. Table objects offer a single
132 method, "name" which returns the table name.
133 params
135 my $paramNum = $stmt->params(); # Scalar context
136 my @params = $stmt->params(); # Array context
137 my($p1, $p2) = ($stmt->params(0), $stmt->params(1));
138 The "params" method returns information about the input parameters used
140 in a statement. For example, consider the following:
141 INSERT INTO foo VALUES (?, ?)
143 This would return two instances of "SQL::Statement::Param". Param
145 objects implement a single method, "$param-"num()>, which retrieves the
146 parameter number. (0 and 1, in the above example). As of now, not very
147 useful ... :-)
148 row_values
150 my $rowValueNum = $stmt->row_values(); # Scalar context
151 my @rowValues = $stmt->row_values(0); # Array context
152 my($rval1, $rval2) = ($stmt->row_values(0,0),
153 $stmt->row_values(0,1));
154 This method is used for statements like
156 UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
158 $colN = $valN WHERE ...
159 INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),
160 ($val1, $val2, ..., $valN)
161 to read the values $val1, $val2, ... $valN. It returns (lists of)
163 scalar values or "SQL::Statement::Param" instances.
164 order
166 my $orderNum = $stmt->order(); # Scalar context
167 my @order = $stmt->order(); # Array context
168 my($o1, $o2) = ($stmt->order(0), $stmt->order(1));
169 In SELECT statements you can use this for looking at the ORDER clause.
171 Example:
172 SELECT * FROM FOO ORDER BY id DESC, name
174 In this case, "order" could return 2 instances of
176 "SQL::Statement::Order". You can use the methods "$o->table()",
177 "$o->column()" and "$o->desc()" to examine the order object.
178 limit
180 my $limit = $stmt->limit();
181 In a SELECT statement you can use a "LIMIT" clause to implement
183 cursoring:
184 SELECT * FROM FOO LIMIT 5
186 SELECT * FROM FOO LIMIT 5, 5
187 SELECT * FROM FOO LIMIT 10, 5
188 These three statements would retrieve the rows 0..4, 5..9, 10..14 of
190 the table FOO, respectively. If no "LIMIT" clause is used, then the
191 method "$stmt->limit" returns undef. Otherwise it returns the limit
192 number (the maximum number of rows) from the statement (5 or 10 for the
193 statements above).
194 offset
196 my $offset = $stmt->offset();
197 If no "LIMIT" clause is used, then the method "$stmt->limit" returns
199 undef. Otherwise it returns the offset number (the index of the first
200 row to be included in the limit clause).
201 where_hash
203 my $where_hash = $stmt->where_hash();
204 To manually evaluate the WHERE clause, fetch the topmost where clause
206 node with the "where_hash" method. Then evaluate the left-hand and
207 right-hand side of the operation, perhaps recursively. Once that is
208 done, apply the operator and finally negate the result, if required.
209 The where clause nodes have (up to) 4 attributes:
211 op contains the operator, one of "AND", "OR", "=", "<>", ">=",
213 ">", "<=", "<", "LIKE", "CLIKE", "IS", "IN", "BETWEEN" or a
214 user defined operator, if any.
215 arg1 contains the left-hand side of the operator. This can be a
217 scalar value, a hash containing column or function
218 definition, a parameter definition (hash has attribute
219 "type" defined) or another operation (hash has attribute
220 "op" defined).
221 arg2 contains the right-hand side of the operator. This can be a
223 scalar value, a hash containing column or function
224 definition, a parameter definition (hash has attribute
225 "type" defined) or another operation (hash has attribute
226 "op" defined).
227 neg contains a TRUE value, if the operation result must be
229 negated after evalution.
230 To illustrate the above, consider the following WHERE clause:
232 WHERE NOT (id > 2 AND name = 'joe') OR name IS NULL
234 We can represent this clause by the following tree:
236 (id > 2) (name = 'joe')
238 \ /
239 NOT AND
240 \ (name IS NULL)
241 \ /
242 OR
243 Thus the WHERE clause would return an SQL::Statement::Op instance with
245 the op() field set to 'OR'. The arg2() field would return another
246 SQL::Statement::Op instance with arg1() being the
247 SQL::Statement::Column instance representing id, the arg2() field
248 containing the value undef (NULL) and the op() field being 'IS'.
249 The arg1() field of the topmost Op instance would return an Op instance
251 with op() eq 'AND' and neg() returning TRUE. The arg1() and arg2()
252 fields would be Op's representing "id > 2" and "name = 'joe'".
253 Of course there's a ready-for-use method for WHERE clause evaluation:
255 The WHERE clause evaluation depends on an object being used for
257 fetching parameter and column values. Usually this can be an
258 SQL::Statement::RAM::Table object or SQL::Eval object, but in fact it
259 can be any object that supplies the methods
260 $val = $eval->param($paramNum);
262 $val = $eval->column($table, $column);
263 Once you have such an object, you can call eval_where;
265 $match = $stmt->eval_where($eval);
267 where
269 my $where = $stmt->where();
270 This method is used to examine the syntax tree of the "WHERE" clause.
272 It returns undef (if no "WHERE" clause was used) or an instance of
273 SQL::Statement::Term.
274 The where clause is evaluated automatically on the current selected row
276 of the table currently worked on when it's "value()" method is invoked.
277 "SQL::Statement" creates the object tree for where clause evaluation
279 directly after successfully parsing a statement from the given
280 "where_clause", if any.
281
283 execute
284 When called from a DBD or other subclass of SQL::Statement, the
285 execute() method will be executed against whatever datasource
286 (persistent storage) is supplied by the DBD or the subclass (e.g. CSV
287 files for DBD::CSV, or BerkeleyDB for DBD::DBM). If you are using
288 SQL::Statement directly rather than as a subclass, you can call the
289 execute() method and the statements will be executed() using temporary
290 in-memory tables. When used directly, like that, you need to create a
291 cache hashref and pass it as the first argument to execute:
292 my $cache = {};
294 my $parser = SQL::Parser->new();
295 my $stmt = SQL::Statement->new('CREATE TABLE x (id INT)',$parser);
296 $stmt->execute( $cache );
297 If you are using a statement with placeholders, those can be passed to
299 execute after the $cache:
300 $stmt = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);
302 $stmt->execute( $cache, 7, 'foo' );
303 fetch
305 Only a single "fetch()" method is provided - it returns a single row of
306 data as an arrayref. Use a loop to fetch all rows:
307 while (my $row = $stmt->fetch()) {
309 # ...
310 }
311 an example of executing and fetching
313 #!/usr/bin/perl -w
314 use strict;
315 use SQL::Statement;
316 my $cache={};
318 my $parser = SQL::Parser->new();
319 for my $sql(split /\n/,
320 " CREATE TABLE a (b INT)
321 INSERT INTO a VALUES(1)
322 INSERT INTO a VALUES(2)
323 SELECT MAX(b) FROM a "
324 )
325 {
326 $stmt = SQL::Statement->new($sql,$parser);
327 $stmt->execute($cache);
328 next unless $stmt->command eq 'SELECT';
329 while (my $row=$stmt->fetch)
330 {
331 print "@$row\n";
332 }
333 }
334 __END__
335
337 Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
338 reserved. Copyright (c) 2009, Jens Rehsack <rehsackATcpan.org>, all
339 rights reserved.
340 This document may be freely modified and distributed under the same
342 terms as Perl itself.
343perl v5.12.1 2010-08-01 SQL::Statement::Structure(3)