1Data::ObjectDriver::SQLU(s3eprm)Contributed Perl DocumenDtaattai:o:nObjectDriver::SQL(3pm)
2
3
4

NAME

6       Data::ObjectDriver::SQL - an SQL statement
7

SYNOPSIS

9           my $sql = Data::ObjectDriver::SQL->new();
10           $sql->select([ 'id', 'name', 'bucket_id', 'note_id' ]);
11           $sql->from([ 'foo' ]);
12           $sql->add_where('name',      'fred');
13           $sql->add_where('bucket_id', { op => '!=', value => 47 });
14           $sql->add_where('note_id',   \'IS NULL');
15           $sql->limit(1);
16
17           my $sth = $dbh->prepare($sql->as_sql);
18           $sth->execute(@{ $sql->{bind} });
19           my @values = $sth->selectrow_array();
20
21           my $obj = SomeObject->new();
22           $obj->set_columns(...);
23

DESCRIPTION

25       Data::ObjectDriver::SQL represents an SQL statement. SQL statements are
26       used internally to "Data::ObjectDriver::Driver::DBI" object drivers to
27       convert database operations ("search()", "update()", etc) into database
28       operations, but sometimes you just gotta use SQL.
29

ATTRIBUTES

31       Data::ObjectDriver::SQL sports several data attributes that represent
32       the parts of the modeled SQL statement.  These attributes all have
33       accessor and mutator methods. Note that some attributes have more
34       convenient methods of modification (for example, "add_where()" for the
35       "where" attribute).
36
37   "select" (arrayref)
38       The database columns to select in a "SELECT" query.
39
40   "distinct" (boolean)
41       Whether the "SELECT" query should return DISTINCT rows only.
42
43   "select_map" (hashref)
44       The map of database column names to object fields in a "SELECT" query.
45       Use this mapping to convert members of the "select" list to column
46       names.
47
48   "select_map_reverse" (hashref)
49       The map of object fields to database column names in a "SELECT" query.
50       Use this map to reverse the "select_map" mapping where needed.
51
52   "from" (arrayref)
53       The list of tables from which to query results in a "SELECT" query.
54
55       Note if you perform a "SELECT" query with multiple tables, the rows
56       will be selected as Cartesian products that you'll need to reduce with
57       "WHERE" clauses. Your query might be better served with real joins
58       specified through the "joins" attribute of your statement.
59
60   "joins" (arrayref of hashrefs containing scalars and hashrefs)
61       The list of "JOIN" clauses to use in the table list of the statement.
62       Each clause is a hashref containing these members:
63
64       •   "table"
65
66           The name of the table in "from" being joined.
67
68       •   "joins" (arrayref)
69
70           The list of joins to perform on the table named in "table". Each
71           member of "joins" is a hashref containing:
72
73           •   "type"
74
75               The type of join to use. That is, the SQL string to use before
76               the word "JOIN" in the join expression; for example, "INNER" or
77               "NATURAL RIGHT OUTER"). This member is optional. When not
78               specified, the default plain "JOIN" join is specified.
79
80           •   "table"
81
82               The name of the table to which to join.
83
84           •   "condition"
85
86               The SQL expression across which to perform the join, as a
87               string.
88
89   "where" (arrayref)
90       The list of "WHERE" clauses that apply to the SQL statement. Individual
91       members of the list are strings of SQL. All members of this attribute
92       must be true for a record to be included as a result; that is, the list
93       members are "AND"ed together to form the full "WHERE" clause.
94
95   "where_values" (hashref of variant structures)
96       The set of data structures used to generate the "WHERE" clause SQL
97       found in the "where" attributes, keyed on the associated column names.
98
99   "bind" (arrayref)
100       The list of values to bind to the query when performed. That is, the
101       list of values to be replaced for the "?"es in the SQL.
102
103   "limit" (scalar)
104       The maximum number of results on which to perform the query.
105
106   "offset" (scalar)
107       The number of records to skip before performing the query. Combined
108       with a "limit" and application logic to increase the offset in
109       subsequent queries, you can paginate a set of records with a moving
110       window containing "limit" records.
111
112   "group" (hashref, or an arrayref of hashrefs)
113       The fields on which to group the results. Grouping fields are hashrefs
114       containing these members:
115
116       •   "column"
117
118           Name of the column on which to group.
119
120       Note you can set a single grouping field, or use an arrayref containing
121       multiple grouping fields.
122
123   "having" (arrayref)
124       The list of clauses to specify in the "HAVING" portion of a "GROUP ...
125       HAVING" clause. Individual clauses are simple strings containing the
126       conditional expression, as in "where".
127
128   "order" (hashref, or an arrayref of hashrefs)
129       Returns or sets the fields by which to order the results. Ordering
130       fields are hashrefs containing these members:
131
132       •   "column"
133
134           Name of the column by which to order.
135
136       •   "desc"
137
138           The SQL keyword to use to specify the ordering. For example, use
139           "DESC" to specify a descending order. This member is optional.
140
141       Note you can set a single ordering field, or use an arrayref containing
142       multiple ordering fields.
143
144   "$sql->comment([ $comment ])"
145       Returns or sets a simple comment to the SQL statement
146

USAGE

148   "Data::ObjectDriver::SQL->new()"
149       Creates a new, empty SQL statement.
150
151   "$sql->add_select($column [, $term ])"
152       Adds the database column $column to the list of fields to return in a
153       "SELECT" query. The requested object member will be indicated to be
154       $term in the statement's "select_map" and "select_map_reverse"
155       attributes.
156
157       $term is optional, and defaults to the same value as $column.
158
159   "$sql->add_join($table, \@joins)"
160       Adds the join statement indicated by $table and "\@joins" to the list
161       of "JOIN" table references for the statement. The structure for the set
162       of joins are as described for the "joins" attribute member above.
163
164   "$sql->add_index_hint($table, $index)"
165       Specifies a particular index to use for a particular table.
166
167   "$sql->add_where($column, $value)"
168       Adds a condition on the value of the database column $column to the
169       statement's "WHERE" clause. A record will be tested against the below
170       conditions according to what type of data structure $value is:
171
172       •   a scalar
173
174           The value of $column must equal $value.
175
176       •   a reference to a scalar
177
178           The value of $column must evaluate true against the SQL given in
179           $$value.  For example, if $$value were "IS NULL", $column must be
180           "NULL" for a record to pass.
181
182       •   a hashref
183
184           The value of $column must compare against the condition represented
185           by $value, which can contain the members:
186
187           •   "value"
188
189               The value with which to compare (required).
190
191           •   "op"
192
193               The SQL operator with which to compare "value" and the value of
194               $column (required).
195
196           •   "column"
197
198               The column name for the comparison. If this is present, it
199               overrides the column name $column, allowing you to build more
200               complex conditions like "((foo = 1 AND bar = 2) OR (baz = 3))".
201
202           For example, if "value" were "NULL" and "op" were "IS", a record's
203           $column column would have to be "NULL" to match.
204
205       •   an arrayref of scalars
206
207           The value of $column may equal any of the members of @$value. The
208           generated SQL performs the comparison with as an "IN" expression.
209
210       •   an arrayref of (mostly) references
211
212           The value of $column must compare against any of the expressions
213           represented in @$value. Each member of the list can be any of the
214           structures described here as possible forms of $value.
215
216           If the first member of the @$value array is the scalar string
217           "-and", all subsequent members of <@$value> must be met for the
218           record to match.  Note this is not very useful unless contained as
219           one option of a larger "OR" alternation.
220
221       All individual conditions specified with "add_where()" must be true for
222       a record to be a result of the query.
223
224       Beware that you can create a circular reference that will recursively
225       generate an infinite SQL statement (for example, by specifying a
226       arrayref $value that itself contains $value). As "add_where()"
227       evaluates your expressions before storing the conditions in the "where"
228       attribute as a generated SQL string, this will occur when calling
229       "add_where()", not "as_sql()". So don't do that.
230
231   "$sql->add_complex_where(\@list)"
232       This method accepts an array reference of clauses that are glued
233       together with logical operators. With it, you can express where clauses
234       that mix logical operators together to produce more complex queries.
235       For instance:
236
237           [ { foo => 1, bar => 2 }, -or => { baz => 3 } ]
238
239       The values given for the columns support all the variants documented
240       for the "add_where()" method above. Logical operators used inbetween
241       the hashref elements can be one of: '-or', '-and', '-or_not',
242       '-and_not'.
243
244   "$sql->has_where($column, [$value])"
245       Returns whether a where clause for the column $column was added to the
246       statement with the "add_where()" method.
247
248       The $value argument is currently ignored.
249
250   "$sql->add_having($column, $value)"
251       Adds an expression to the "HAVING" portion of the statement's "GROUP
252       ...  HAVING" clause. The expression compares $column using $value,
253       which can be any of the structures described above for the
254       "add_where()" method.
255
256   "$sql->add_index_hint($table, \@hints)"
257       Addes the index hint into a "SELECT" query. The structure for the set
258       of "\@hints" are arrayref of hashrefs containing these members:
259
260       •   "type" (scalar)
261
262           The name of the type. "USE", "IGNORE or "FORCE".
263
264       •   "list" (arrayref)
265
266           The list of name of indexes which to use.
267
268   "$sql->as_sql()"
269       Returns the SQL fully representing the SQL statement $sql.
270
271   "$sql->as_sql_having()"
272       Returns the SQL representing the "HAVING" portion of $sql's "GROUP ...
273       HAVING" clause.
274
275   "$sql->as_sql_where()"
276       Returns the SQL representing $sql's "WHERE" clause.
277
278   "$sql->as_limit()"
279       Returns the SQL for the "LIMIT ... OFFSET" clause of the statement.
280
281   "$sql->as_aggregate($set)"
282       Returns the SQL representing the aggregation clause of type $set for
283       the SQL statement $sql. Reasonable values of $set are "ORDER" and
284       "GROUP".
285

DIAGNOSTICS

287       •   "Invalid/unsafe column name column"
288
289           The column name you specified to "add_where()" contained characters
290           that are not allowed in database column names. Only word characters
291           and periods are allowed. Perhaps you didn't filter punctuation out
292           of a generated column name correctly.
293

BUGS AND LIMITATIONS

295       Data::ObjectDriver::SQL does not provide the functionality for turning
296       SQL statements into instances of object classes.
297

SEE ALSO

LICENSE

300       Data::ObjectDriver is free software; you may redistribute it and/or
301       modify it under the same terms as Perl itself.
302
304       Except where otherwise noted, Data::ObjectDriver is Copyright 2005-2006
305       Six Apart, cpan@sixapart.com. All rights reserved.
306
307
308
309perl v5.34.0                      2022-01-21      Data::ObjectDriver::SQL(3pm)
Impressum