File Coverage

blib/lib/Fey/SQL.pm
Criterion Covered Total %
statement 49 49 100.0
branch n/a
condition n/a
subroutine 19 19 100.0
pod 8 8 100.0
total 76 76 100.0


line stmt bran cond sub pod time code
1             package Fey::SQL;
2              
3 27     27   17326 use strict;
  27         49  
  27         981  
4 27     27   117 use warnings;
  27         40  
  27         1045  
5              
6             our $VERSION = '0.43';
7              
8 27     27   9318 use Fey::SQL::Delete;
  27         86  
  27         1162  
9 27     27   14813 use Fey::SQL::Insert;
  27         103  
  27         1284  
10 27     27   17142 use Fey::SQL::Select;
  27         106  
  27         1288  
11 27     27   16135 use Fey::SQL::Update;
  27         118  
  27         1306  
12 27     27   15959 use Fey::SQL::Where;
  27         107  
  27         1232  
13 27     27   15295 use Fey::SQL::Union;
  27         128  
  27         1798  
14 27     27   16059 use Fey::SQL::Intersect;
  27         111  
  27         1837  
15 27     27   26350 use Fey::SQL::Except;
  27         106  
  27         1796  
16 27     27   241 use Fey::Types;
  27         43  
  27         247  
17              
18             sub new_delete {
19 6     6 1 1028 shift;
20 6         172 return Fey::SQL::Delete->new(@_);
21             }
22              
23             sub new_insert {
24 27     27 1 196 shift;
25 27         673 return Fey::SQL::Insert->new(@_);
26             }
27              
28             sub new_select {
29 199     199 1 138335 shift;
30 199         5630 return Fey::SQL::Select->new(@_);
31             }
32              
33             sub new_update {
34 26     26 1 1274 shift;
35 26         742 return Fey::SQL::Update->new(@_);
36             }
37              
38             sub new_where {
39 5     5 1 180 shift;
40 5         185 return Fey::SQL::Where->new(@_);
41             }
42              
43             sub new_union {
44 9     9 1 9471 shift;
45 9         73 return Fey::SQL::Union->new(@_);
46             }
47              
48             sub new_intersect {
49 8     8 1 9051 shift;
50 8         48 return Fey::SQL::Intersect->new(@_);
51             }
52              
53             sub new_except {
54 8     8 1 9164 shift;
55 8         51 return Fey::SQL::Except->new(@_);
56             }
57              
58             1;
59              
60             # ABSTRACT: Documentation on SQL generation with Fey and SQL object factory
61              
62             __END__
63              
64             =pod
65              
66             =head1 NAME
67              
68             Fey::SQL - Documentation on SQL generation with Fey and SQL object factory
69              
70             =head1 VERSION
71              
72             version 0.43
73              
74             =head1 SYNOPSIS
75              
76             my $sql = Fey::SQL->new_select();
77              
78             $sql->select( @columns );
79              
80             =head1 DESCRIPTION
81              
82             This module mostly exists to provide documentation and a factory
83             interface for making SQL statement objects.
84              
85             For convenience, loading this module loads all of the C<Fey::SQL::*>
86             classes, such as L<Fey::SQL::Select>, L<Fey::SQL::Delete>, etc.
87              
88             =head1 METHODS
89              
90             This class acts as a factory for the various SQL statement classes,
91             such as L<Fey::SQL::Select> or L<Fey::SQL::Update>. This is simply
92             sugar which makes it easy to replace C<Fey::SQL> with a subclass,
93             either for your application or for a specific DBMS.
94              
95             =head2 Fey::SQL->new_select()
96              
97             Returns a new C<Fey::SQL::Select> object.
98              
99             =head2 Fey::SQL->new_insert()
100              
101             Returns a new C<Fey::SQL::Insert> object.
102              
103             =head2 Fey::SQL->new_update()
104              
105             Returns a new C<Fey::SQL::Update> object.
106              
107             =head2 Fey::SQL->new_delete()
108              
109             Returns a new C<Fey::SQL::Delete> object.
110              
111             =head2 Fey::SQL->new_where()
112              
113             Returns a new C<Fey::SQL::Where> object.
114              
115             =head2 Fey::SQL->new_union()
116              
117             Returns a new C<Fey::SQL::Union> object.
118              
119             =head2 Fey::SQL->new_intersect()
120              
121             Returns a new C<Fey::SQL::Intersect> object.
122              
123             =head2 Fey::SQL->new_except()
124              
125             Returns a new C<Fey::SQL::Except> object.
126              
127             =head1 CREATING SQL
128              
129             This documentation covers the clauses in SQL queries which are shared
130             across different types of queries, including C<WHERE>, C<ORDER BY>,
131             and C<LIMIT>. For SQL clauses that are specific to one type of query,
132             see the appropriate subclass. For example, for C<SELECT> clauses, see
133             the L<Fey::SQL::Select> class documentation.
134              
135             =head2 WHERE Clauses
136              
137             Many types of queries allow C<WHERE> clauses via the a C<where()>
138             method. The method accepts several different types of parameters:
139              
140             =head3 Comparisons
141              
142             Comparing a column to a given value ...
143              
144             # WHERE Part.size = $value}
145             $sql->where( $size, '=', $value );
146              
147             # WHERE Part.size = AVG(Part.size);
148             $sql->where( $size, '=', $avg_size_function );
149              
150             # WHERE Part.size = ?
151             $sql->where( $size, '=', $placeholder );
152              
153             # WHERE User.user_id = Message.user_id
154             $sql->where( $user_id, '=', $other_user_id );
155              
156             The left-hand side of a conditional does not need to be a column
157             object, it could be a function or anything that produces valid SQL.
158              
159             my $length = Fey::Literal::Function->new( 'LENGTH', $name );
160             # WHERE LENGTH(Part.name) = 10
161             $sql->where( $length, '=', 10 );
162              
163             The second parameter in a conditional can be any comparison operator that
164             produces valid SQL:
165              
166             # WHERE Message.body LIKE 'hello%'
167             $sql->where( $body, 'LIKE', 'hello%' );
168              
169             # WHERE Part.quantity > 10
170             $sql->where( $quantity, '>', 10 );
171              
172             If you use a comparison operator like C<BETWEEN> or C<(NOT) IN>, you
173             can pass more than three parameters to C<where()>.
174              
175             # WHERE Part.size BETWEEN 4 AND 10
176             $sql->where( $size, 'BETWEEN', 4, 10 );
177              
178             # WHERE User.user_id IN (1, 2, 7, 9)
179             $sql->where( $user_id, 'IN', 1, 2, 7, 9 );
180              
181             You can also pass a subselect when using C<IN>.
182              
183             my $select = $sql->select(...);
184              
185             # WHERE User.user_id IN ( SELECT user_id FROM ... )
186             $sql->where( $user_id, 'IN', $select );
187              
188             If you use C<=>, C<!=>, or C<< <> >> as the comparison and the
189             right-hand side is C<undef>, then the generated query will use C<IS
190             NULL> or C<IS NOT NULL>, as appropriate:
191              
192             # WHERE Part.name IS NULL
193             $sql->where( $name, '=', undef );
194              
195             # WHERE Part.name IS NOT NULL
196             $sql->where( $name, '!=', undef );
197              
198             Note that if you use a placeholder object in this case, then the query
199             will not be transformed into an C<IS (NOT) NULL> expression, since the
200             value of the placeholder is not known when the SQL is being generated.
201              
202             You can also use C<and()> instead of where if you like the look ...
203              
204             $sql->where( $size, '=', $value )
205             ->and ( $quantity, '>', 10 );
206              
207             The C<and()> method is just sugar, since by default, multiple calls to
208             C<where()> end up concatenated with an C<AND> in the resulting SQL.
209              
210             =head3 Boolean AND/OR
211              
212             You can pass the strings "and" and "or" to the C<where()> method in
213             order to create complex boolean conditions. When you call C<where()>
214             with multiple comparisons in a row, an implicit "and" is added between
215             each one.
216              
217             # WHERE Part.size > 10 OR Part.size = 5
218             $sql->where( $size, '>', 10 );
219             $sql->where( 'or' );
220             $sql->where( $size, '=', 5 );
221              
222             # WHERE Part.size > 10 AND Part.size < 20
223             $sql->where( $size, '>', 10 );
224             # there is an implicit $sql->where( 'and' ) here ...
225             $sql->where( $size, '<', 10 );
226              
227             =head3 What Comparison Operators Are Valid?
228              
229             Basically, any operator should work, and there is no check that a particular operator is valid.
230              
231             Some operators are special-cased, specifically C<BETWEEN>, C<IN>, and C<NOT
232             IN>. If you use C<BETWEEN> as the operator, you are expected to pass I<two>
233             items after it. If you use C<IN> or C<NOT IN>, you can pass as many items as
234             you need to on the right hand side.
235              
236             =head3 What Can Be Compared?
237              
238             When you call C<where()> to do a comparison, you can pass any of the following
239             types of things:
240              
241             =over 4
242              
243             =item * An object which has an C<is_comparable()> method that returns true
244              
245             This includes objects which do the L<Fey::Role::ColumnLike> role:
246             L<Fey::Column> and L<Fey::Column::Alias>. A column only returns true for
247             C<is_comparable()> when it is actually attached to a table.
248              
249             Objects which do the L<Fey::Role::Comparable> role: L<Fey::SQL::Select>,
250             L<Fey::SQL::Union>, L<Fey::SQL::Intersect>, and L<Fey::SQL::Except> always
251             return true for C<is_comparable()>.
252              
253             If you try to compare something to something that returns a data set, you must
254             be using an equality comparison operator (C<=>, C<!=>, etc), C<IN>, or, C<NOT
255             IN>.
256              
257             Also, all L<Fey::Literal> subclasses return true for C<is_comparable()>:
258             L<Fey::Literal::Function>, L<Fey::Literal::Null>, L<Fey::Literal::Number>,
259             L<Fey::Literal::String>, and L<Fey::Literal::Term>.
260              
261             Finally, you can pass a L<Fey::Placeholder> object.
262              
263             =item * An unblessed non-reference scalar
264              
265             This can be C<undef>, a string, or a number. This scalar will be passed to C<<
266             Fey::Literal->new_from_scalar() >> and converted into an appropriate
267             L<Fey::Literal> object.
268              
269             =item * An object which returns true for C<overload::Overloaded($object)>
270              
271             This will be stringified (C<$object .= q{}>) and passed to C<<
272             Fey::Literal->new_from_scalar() >>.
273              
274             =back
275              
276             =head3 NULL In Comparisons
277              
278             Fey does the right thing for NULLs used in equality comparisons, generating
279             C<IS NULL> and C<IS NOT NULL> as appropriate.
280              
281             =head2 Subgroups
282              
283             You can pass the strings "(" and ")" to the C<where()> method in order
284             to create subgroups.
285              
286             # WHERE Part.size > 10
287             # AND ( Part.name = 'Widget'
288             # OR
289             # Part.name = 'Grommit' )
290             $sql->where( $size, '>', 10 );
291             $sql->where( '(' );
292             $sql->where( $name, '=', 'Widget' );
293             $sql->where( 'or' );
294             $sql->where( $name, '=', 'Grommit' );
295             $sql->where( ')' );
296              
297             =head2 ORDER BY Clauses
298              
299             Many types of queries allow C<ORDER BY> clauses via the C<order_by()>
300             method. This method accepts a list of items. The items in the list may
301             be columns, functions, terms, or sort directions ("ASC" or
302             "DESC"). The sort direction can also specify "NULLS FIRST" or "NULLS
303             LAST".
304              
305             # ORDER BY Part.size
306             $sql->order_by( $size );
307              
308             # ORDER BY Part.size DESC
309             $sql->order_by( $size, 'DESC' );
310              
311             # ORDER BY Part.size DESC, Part.name ASC
312             $sql->order_by( $size, 'DESC', $name, 'ASC' );
313              
314             # ORDER BY Part.size ASC NULLS FIRST
315             $sql->order_by( $size, 'ASC NULLS FIRST' );
316              
317             my $length = Fey::Literal::Function->new( 'LENGTH', $name );
318             # ORDER BY LENGTH( Part.name ) ASC
319             $sql->order_by( $length, 'ASC' );
320              
321             If you pass a function literal to the C<order_by()> method and the
322             literal was used previously in the select clause, then an alias is
323             used in the C<ORDER BY> clause.
324              
325             my $length = Fey::Literal::Function->new( 'LENGTH', $name );
326             $sql->select($length);
327              
328             # SELECT LENGTH(Part.name) AS FUNCTION0 ...
329             # ORDER BY FUNCTION0 ASC
330             $sql->order_by( $length, 'ASC' );
331              
332             =head2 LIMIT Clauses
333              
334             Many types of queries allow C<LIMIT> clauses via the C<limit()>
335             method. This method accepts two parameters, with the second being
336             optional.
337              
338             The first parameter is the number of items. The second, optional
339             parameter, is the offset for the limit clause.
340              
341             # LIMIT 10
342             $sql->limit( 10 );
343              
344             # LIMIT 10 OFFSET 20
345             $sql->limit( 10, 20 );
346              
347             # OFFSET 20
348             $sql->limit( undef, 20 );
349              
350             =head2 Bind Parameters
351              
352             By default, whenever you pass a non-object value where a placeholder
353             could go, the SQL class replaces this with a placeholder and stores
354             the value as a bind parameter. This applies to things like C<WHERE>
355             and C<HAVING> clauses, as well as the C<VALUES> clause of an
356             C<INSERT>, and the C<SET> clause of an C<UPDATE>.
357              
358             You can retrieve the bind parameters by calling C<<
359             $sql->bind_params() >>. These will be returned in the proper order for
360             passing to C<DBI>'s C<execute()> method.
361              
362             If you do not want values automatically converted to placeholders, you
363             can turn this behavior off by setting C<auto_placeholders> to a false
364             value when creating the object:
365              
366             my $select = Fey::SQL->new_select( auto_placeholders => 0 );
367              
368             In this case, values will be quoted as needed and inserted directly
369             into the generated SQL.
370              
371             =head2 Cloning
372              
373             Every SQL object has a C<clone()> method. This is useful if you want
374             to have an object that you use as the base for multiple queries.
375              
376             my $user_select = Fey::SQL->new_select( $user_table )
377             ->from( $user_table);
378              
379             my $select_new =
380             $user_select->clone()
381             ->where( $creation_column, '>=', $six_months_ago );
382              
383             my $select_old
384             $user_select->clone()
385             ->where( $creation_column, '<', $six_months_ago );
386              
387             =head2 Overloaded Objects as Parameters
388              
389             Any method which accepts a plain scalar can also take an overloaded
390             object that overloads stringification or numification. This includes
391             C<WHERE> clause comparisons, C<VALUES> in an C<INSERT>, and C<SET>
392             clauses in an C<UPDATE>.
393              
394             =head1 BUGS
395              
396             See L<Fey> for details on how to report bugs.
397              
398             =head1 AUTHOR
399              
400             Dave Rolsky <autarch@urth.org>
401              
402             =head1 COPYRIGHT AND LICENSE
403              
404             This software is Copyright (c) 2011 - 2015 by Dave Rolsky.
405              
406             This is free software, licensed under:
407              
408             The Artistic License 2.0 (GPL Compatible)
409              
410             =cut