1SQL::Abstract::Limit(3)User Contributed Perl DocumentatioSnQL::Abstract::Limit(3)
2
6 SQL::Abstract::Limit - portable LIMIT emulation
7
9 use SQL::Abstract::Limit;
10 my $sql = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );;
12 # or autodetect from a DBI $dbh:
14 my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
15 # or from a Class::DBI class:
17 my $sql = SQL::Abstract::Limit->new( limit_dialect => 'My::CDBI::App' );
18 # or object:
20 my $obj = My::CDBI::App->retrieve( $id );
21 my $sql = SQL::Abstract::Limit->new( limit_dialect => $obj );
22 # generate SQL:
24 my ( $stmt, @bind ) = $sql->select( $table, \@fields, \%where, \@order, $limit, $offset );
25 # Then, use these in your DBI statements
27 my $sth = $dbh->prepare( $stmt );
28 $sth->execute( @bind );
29 # Just generate the WHERE clause (only available for some syntaxes)
31 my ( $stmt, @bind ) = $sql->where( \%where, \@order, $limit, $offset );
32
34 Portability layer for LIMIT emulation.
35 new( case => 'lower', cmp => 'like', logic => 'and', convert =>
37 'upper', limit_dialect => 'Top' )
38 All settings are optional.
39 limit_dialect
41 Sets the default syntax model to use for emulating a "LIMIT
42 $rows OFFSET $offset" clause. Default setting is "Generic‐
43 SubQ". You can still pass other syntax settings in method
44 calls, this just sets the default. Possible values are:
45 LimitOffset PostgreSQL, SQLite
47 LimitXY MySQL, MaxDB, anything that uses SQL::Statement
48 LimitYX SQLite (optional)
49 RowsTo InterBase/FireBird
50 Top SQL/Server, MS Access
52 RowNum Oracle
53 FetchFirst DB2
54 First Informix # not implemented yet
55 GenericSubQ Sybase, plus any databases not recognised by this module
56 $dbh a DBI database handle
58 CDBI subclass
60 CDBI object
61 other DBI-based thing
63 The first group are implemented by appending a short clause
65 to the end of the statement. The second group require more
66 intricate wrapping of the original statement in subselects.
67 You can pass a DBI database handle, and the module will
69 figure out which dialect to use.
70 You can pass a Class::DBI subclass or object, and the mod‐
72 ule will find the $dbh and use it to find the dialect.
73 Anything else based on DBI can be easily added by locating
75 the $dbh. Patches or suggestions welcome.
76 Other options are described in SQL::Abstract.
78 select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [
80 $dialect ] ] ] )
81 Same as "SQL::Abstract::select", but accepts additional $rows,
82 $offset and $dialect parameters.
83 The $order parameter is required if $rows is specified.
85 The $fields parameter is required, but can be set to "undef", '' or
87 '*' (all these get set to '*').
88 The $where parameter is also required. It can be a hashref or an
90 arrayref, or "undef".
91 where( [ $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] ] )
93 Same as "SQL::Abstract::where", but accepts additional $rows, $off‐
94 set and $dialect parameters.
95 Some SQL dialects support syntaxes that can be applied as simple
97 phrases tacked on to the end of the WHERE clause. These are:
98 LimitOffset
100 LimitXY
101 LimitYX
102 RowsTo
103 This method returns a modified WHERE clause, if the limit syntax is
105 set to one of these options (either in the call to "where" or in
106 the constructor), and if $rows is passed in.
107 Dies via "croak" if you try to use it for other syntaxes.
109 $order is required if $rows is set.
111 $where is required if any other parameters are specified. It can be
113 a hashref or an arrayref, or "undef".
114 Returns a regular "WHERE" clause if no limits are set.
116 insert
118 update
119 delete
120 values
121 generate
122 See SQL::Abstract for these methods.
123 "update" and "delete" are not provided with any "LIMIT" emulation
125 in this release, and no support is planned at the moment. But
126 patches would be welcome.
127 Limit emulation
129 The following dialects are available for emulating the LIMIT clause. In
131 each case, $sql represents the SQL statement generated by
132 "SQL::Abstract::select", minus the ORDER BY clause, e.g.
133 SELECT foo, bar FROM my_table WHERE some_conditions
135 $sql_after_select represents $sql with the leading "SELECT" keyword
137 removed.
138 "order_cols_up" represents the sort column(s) and direction(s) speci‐
140 fied in the "order" parameter.
141 "order_cols_down" represents the opposite sort.
143 "$last = $rows + $offset"
145 LimitOffset
147 Syntax
148 $sql ORDER BY order_cols_up LIMIT $rows OFFSET $offset
149 or
151 $sql ORDER BY order_cols_up LIMIT $rows
153 if "$offset == 0".
155 Databases
157 PostgreSQL
158 SQLite
159 LimitXY
161 Syntax
162 $sql ORDER BY order_cols_up LIMIT $offset, $rows
163 or
165 $sql ORDER BY order_cols_up LIMIT $rows
167 if "$offset == 0".
169 Databases
171 MySQL
172 LimitYX
174 Syntax
175 $sql ORDER BY order_cols_up LIMIT $rows, $offset
176 or
178 $sql ORDER BY order_cols_up LIMIT $rows
180 if "$offset == 0".
182 Databases
184 SQLite understands this syntax, or LimitOffset. If autodetecting the
185 dialect, it will be set to LimitOffset.
186 RowsTo
188 Syntax
189 $sql ORDER BY order_cols_up ROWS $offset TO $last
190 Databases
192 InterBase
193 FireBird
194 Top
196 Syntax
197 SELECT * FROM
198 (
199 SELECT TOP $rows * FROM
200 (
201 SELECT TOP $last $sql_after_select
202 ORDER BY order_cols_up
203 ) AS foo
204 ORDER BY order_cols_down
205 ) AS bar
206 ORDER BY order_cols_up
207 Databases
209 SQL/Server
210 MS Access
211 RowNum
213 Syntax Oracle numbers rows from 1, not zero, so here $offset has
214 been incremented by 1.
215 SELECT * FROM
217 (
218 SELECT A.*, ROWNUM r FROM
219 (
220 $sql ORDER BY order_cols_up
221 ) A
222 WHERE ROWNUM <= $last
223 ) B
224 WHERE r >= $offset
225 Databases
227 Oracle
228 FetchFirst
230 Syntax
231 SELECT * FROM (
232 SELECT * FROM (
233 $sql
234 ORDER BY order_cols_up
235 FETCH FIRST $last ROWS ONLY
236 ) foo
237 ORDER BY order_cols_down
238 FETCH FIRST $rows ROWS ONLY
239 ) bar
240 ORDER BY order_cols_up
241 Databases
243 IBM DB2
244 GenericSubQ
246 When all else fails, this should work for many databases, but it is
247 probably fairly slow.
248 This method relies on having a column with unique values as the
250 first column in the "SELECT" clause (i.e. the first column in the
251 "\@fields" parameter). The results will be sorted by that unique
252 column, so any $order parameter is ignored, unless it matches the
253 unique column, in which case the direction of the sort is honoured.
254 Syntax
256 SELECT field_list FROM $table X WHERE where_clause AND
257 (
258 SELECT COUNT(*) FROM $table WHERE $pk > X.$pk
259 )
260 BETWEEN $offset AND $last
261 ORDER BY $pk $asc_desc
262 $pk is the first column in "field_list".
264 $asc_desc is the opposite direction to that specified in
266 the method call. So if you want the final results sorted
267 "ASC", say so, and it gets flipped internally, but the
268 results come out as you'd expect. I think.
269 The "BETWEEN $offset AND $last" clause is replaced with "<
271 $rows" if <$offset == 0>.
272 Databases
274 Sybase Anything not otherwise known to this module.
275 First
277 Syntax Looks to be identical to "Top", e.g. "SELECT FIRST 10 *
278 FROM table". Can probably be implemented in a very similar
279 way, but not done yet.
280 Databases
282 Informix
283
285 You can create your own syntax by making a subclass that provides an
286 "emulate_limit" method. This might be useful if you are using stored
287 procedures to provide more efficient paging.
288 emulate_limit( $self, $sql, $order, $rows, $offset )
290 $sql
291 This is the SQL statement built by SQL::Abstract, but without
292 the ORDER BY clause, e.g.
293 SELECT foo, bar FROM my_table WHERE conditions
295 or just
297 WHERE conditions
299 if calling "where" instead of "select".
301 $order
303 The "order" parameter passed to the "select" or "where" call.
304 You can get an "ORDER BY" clause from this by calling
305 my $order_by = $self->_order_by( $order );
307 You can get a pair of "ORDER BY" clauses that sort in opposite
309 directions by saying
310 my ( $up, $down ) = $self->_order_directions( $order );
312 The method should return a suitably modified SQL statement.
314
316 The $dialect parameter that can be passed to the constructor or to the
317 "select" and "where" methods can be a number of things. The module will
318 attempt to determine the appropriate syntax to use.
319 Supported $dialect things are:
321 dialect name (e.g. LimitOffset, RowsTo, Top etc.)
323 database moniker (e.g. Oracle, SQLite etc.)
324 DBI database handle
325 Class::DBI subclass or object
326
328 Paging results sets is a complicated undertaking, with several compet‐
329 ing factors to take into account. This module does not magically give
330 you the optimum paging solution for your situation. It gives you a
331 solution that may be good enough in many situations. But if your tables
332 are large, the SQL generated here will often not be efficient. Or if
333 your queries involve joins or other complications, you will probably
334 need to look elsewhere.
335 But if your tables aren't too huge, and your queries straightforward,
337 you can just plug this module in and move on to your next task.
338
340 Thanks to Aaron Johnson for the Top syntax model (SQL/Server and MS
341 Access).
342 Thanks to Emanuele Zeppieri for the IBM DB2 syntax model.
344
346 Find more syntaxes to implement.
347 Test the syntaxes against real databases. I only have access to MySQL.
349 Reports of success or failure would be great.
350
352 SQL::Abstract, DBI::Const::GetInfoType, Carp.
353
355 DBIx::SQLEngine, DBIx::SearchBuilder, DBIx::RecordSet.
356
358 Please report all bugs via the CPAN Request Tracker at
359 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.
360
362 Copyright 2004 by David Baird.
363 This library is free software; you can redistribute it and/or modify it
365 under the same terms as Perl itself.
366
368 David Baird, "cpan@riverside-cms.co.uk"
369
371 A few CPAN modules do this for a few databases, but the most comprehen‐
372 sive seem to be DBIx::SQLEngine, DBIx::SearchBuilder and DBIx::Record‐
373 Set.
374 Have a look in the source code for my notes on how these modules tackle
376 similar problems.
377perl v5.8.8 2005-12-19 SQL::Abstract::Limit(3)