1SQL::Abstract::Limit(3)User Contributed Perl DocumentatioSnQL::Abstract::Limit(3)
2
3
4

NAME

6       SQL::Abstract::Limit - portable LIMIT emulation
7

SYNOPSIS

9           use SQL::Abstract::Limit;
10
11           my $sql = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );;
12
13           # or autodetect from a DBI $dbh:
14           my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
15
16           # or from a Class::DBI class:
17           my $sql = SQL::Abstract::Limit->new( limit_dialect => 'My::CDBI::App' );
18
19           # or object:
20           my $obj = My::CDBI::App->retrieve( $id );
21           my $sql = SQL::Abstract::Limit->new( limit_dialect => $obj );
22
23           # generate SQL:
24           my ( $stmt, @bind ) = $sql->select( $table, \@fields, \%where, \@order, $limit, $offset );
25
26           # Then, use these in your DBI statements
27           my $sth = $dbh->prepare( $stmt );
28           $sth->execute( @bind );
29
30           # Just generate the WHERE clause (only available for some syntaxes)
31           my ( $stmt, @bind )  = $sql->where( \%where, \@order, $limit, $offset );
32

DESCRIPTION

34       Portability layer for LIMIT emulation.
35
36       new( case => 'lower', cmp => 'like', logic => 'and', convert =>
37       'upper', limit_dialect => 'Top' )
38           All settings are optional.
39
40           limit_dialect
41                   Sets the default syntax model to use for emulating a "LIMIT
42                   $rows OFFSET $offset" clause. Default setting is
43                   "GenericSubQ". You can still pass other syntax settings in
44                   method calls, this just sets the default. Possible values
45                   are:
46
47                       LimitOffset     PostgreSQL, SQLite
48                       LimitXY         MySQL, MaxDB, anything that uses SQL::Statement
49                       LimitYX         SQLite (optional)
50                       RowsTo          InterBase/FireBird
51
52                       Top             SQL/Server, MS Access
53                       RowNum          Oracle
54                       FetchFirst      DB2
55                       Skip            Informix
56                       GenericSubQ     Sybase, plus any databases not recognised by this module
57
58                       $dbh            a DBI database handle
59
60                       CDBI subclass
61                       CDBI object
62
63                       other DBI-based thing
64
65                   The first group are implemented by appending a short clause
66                   to the end of the statement. The second group require more
67                   intricate wrapping of the original statement in subselects.
68
69                   You can pass a DBI database handle, and the module will
70                   figure out which dialect to use.
71
72                   You can pass a Class::DBI subclass or object, and the
73                   module will find the $dbh and use it to find the dialect.
74
75                   Anything else based on DBI can be easily added by locating
76                   the $dbh.  Patches or suggestions welcome.
77
78           Other options are described in SQL::Abstract.
79
80       select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [
81       $dialect ] ] ] )
82           Same as "SQL::Abstract::select", but accepts additional $rows,
83           $offset and $dialect parameters.
84
85           The $order parameter is required if $rows is specified.
86
87           The $fields parameter is required, but can be set to "undef", '' or
88           '*' (all these get set to '*').
89
90           The $where parameter is also required. It can be a hashref or an
91           arrayref, or "undef".
92
93       where( [ $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] ] )
94           Same as "SQL::Abstract::where", but accepts additional $rows,
95           $offset and $dialect parameters.
96
97           Some SQL dialects support syntaxes that can be applied as simple
98           phrases tacked on to the end of the WHERE clause. These are:
99
100               LimitOffset
101               LimitXY
102               LimitYX
103               RowsTo
104
105           This method returns a modified WHERE clause, if the limit syntax is
106           set to one of these options (either in the call to "where" or in
107           the constructor), and if $rows is passed in.
108
109           Dies via "croak" if you try to use it for other syntaxes.
110
111           $order is required if $rows is set.
112
113           $where is required if any other parameters are specified. It can be
114           a hashref or an arrayref, or "undef".
115
116           Returns a regular "WHERE" clause if no limits are set.
117
118       insert
119       update
120       delete
121       values
122       generate
123           See SQL::Abstract for these methods.
124
125           "update" and "delete" are not provided with any "LIMIT" emulation
126           in this release, and no support is planned at the moment. But
127           patches would be welcome.
128
129   Limit emulation
130       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
134           SELECT foo, bar FROM my_table WHERE some_conditions
135
136       $sql_after_select represents $sql with the leading "SELECT" keyword
137       removed.
138
139       "order_cols_up" represents the sort column(s) and direction(s)
140       specified in the "order" parameter.
141
142       "order_cols_down" represents the opposite sort.
143
144       "$last = $rows + $offset"
145
146       LimitOffset
147           Syntax
148                       $sql ORDER BY order_cols_up LIMIT $rows OFFSET $offset
149
150                   or
151
152                       $sql ORDER BY order_cols_up LIMIT $rows
153
154                   if "$offset == 0".
155
156           Databases
157                       PostgreSQL
158                       SQLite
159
160       LimitXY
161           Syntax
162                       $sql ORDER BY order_cols_up LIMIT $offset, $rows
163
164                   or
165
166                        $sql ORDER BY order_cols_up LIMIT $rows
167
168                   if "$offset == 0".
169
170           Databases
171                       MySQL
172
173       LimitYX
174           Syntax
175                       $sql ORDER BY order_cols_up LIMIT $rows, $offset
176
177                   or
178
179                       $sql ORDER BY order_cols_up LIMIT $rows
180
181                   if "$offset == 0".
182
183           Databases
184                       SQLite understands this syntax, or LimitOffset. If autodetecting the
185                              dialect, it will be set to LimitOffset.
186
187       RowsTo
188           Syntax
189                       $sql ORDER BY order_cols_up ROWS $offset TO $last
190
191           Databases
192                       InterBase
193                       FireBird
194
195       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
208           Databases
209                       SQL/Server
210                       MS Access
211
212       RowNum
213           Syntax  Oracle numbers rows from 1, not zero, so here $offset has
214                   been incremented by 1.
215
216                       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
226           Databases
227                       Oracle
228
229       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
242           Databases
243                   IBM DB2
244
245       GenericSubQ
246           When all else fails, this should work for many databases, but it is
247           probably fairly slow.
248
249           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
255           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
263                   $pk is the first column in "field_list".
264
265                   $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
270                   The "BETWEEN $offset AND $last" clause is replaced with "<
271                   $rows" if <$offset == 0>.
272
273           Databases
274                   Sybase Anything not otherwise known to this module.
275
276       Skip
277           Syntax
278                     select skip 5 limit 5 * from customer
279
280                   which will take rows 6 through 10 in the select.
281
282           Databases
283                   Informix
284

SUBCLASSING

286       You can create your own syntax by making a subclass that provides an
287       "emulate_limit" method. This might be useful if you are using stored
288       procedures to provide more efficient paging.
289
290       emulate_limit( $self, $sql, $order, $rows, $offset )
291           $sql
292               This is the SQL statement built by SQL::Abstract, but without
293               the ORDER BY clause, e.g.
294
295                   SELECT foo, bar FROM my_table WHERE conditions
296
297               or just
298
299                   WHERE conditions
300
301               if calling "where" instead of "select".
302
303           $order
304               The "order" parameter passed to the "select" or "where" call.
305               You can get an "ORDER BY" clause from this by calling
306
307                   my $order_by = $self->_order_by( $order );
308
309               You can get a pair of "ORDER BY" clauses that sort in opposite
310               directions by saying
311
312                   my ( $up, $down ) = $self->_order_directions( $order );
313
314           The method should return a suitably modified SQL statement.
315

AUTO-DETECTING THE DIALECT

317       The $dialect parameter that can be passed to the constructor or to the
318       "select" and "where" methods can be a number of things. The module will
319       attempt to determine the appropriate syntax to use.
320
321       Supported $dialect things are:
322
323           dialect name (e.g. LimitOffset, RowsTo, Top etc.)
324           database moniker (e.g. Oracle, SQLite etc.)
325           DBI database handle
326           Class::DBI subclass or object
327

CAVEATS

329       Paging results sets is a complicated undertaking, with several
330       competing factors to take into account. This module does not magically
331       give you the optimum paging solution for your situation. It gives you a
332       solution that may be good enough in many situations. But if your tables
333       are large, the SQL generated here will often not be efficient. Or if
334       your queries involve joins or other complications, you will probably
335       need to look elsewhere.
336
337       But if your tables aren't too huge, and your queries straightforward,
338       you can just plug this module in and move on to your next task.
339

ACKNOWLEDGEMENTS

341       Thanks to Aaron Johnson for the Top syntax model (SQL/Server and MS
342       Access).
343
344       Thanks to Emanuele Zeppieri for the IBM DB2 syntax model.
345
346       Thanks to Paul Falbe for the Informix implementation.
347

TODO

349       Find more syntaxes to implement.
350
351       Test the syntaxes against real databases. I only have access to MySQL.
352       Reports of success or failure would be great.
353

DEPENDENCIES

355       SQL::Abstract, DBI::Const::GetInfoType, Carp.
356

SEE ALSO

358       DBIx::SQLEngine, DBIx::SearchBuilder, DBIx::RecordSet.
359

BUGS

361       Please report all bugs via the CPAN Request Tracker at
362       http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit
363       <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.
364
366       Copyright 2004 by David Baird.
367
368       This library is free software; you can redistribute it and/or modify it
369       under the same terms as Perl itself.
370

AUTHOR

372       David Baird, "cpan@riverside-cms.co.uk"
373

HOW IS IT DONE ELSEWHERE

375       A few CPAN modules do this for a few databases, but the most
376       comprehensive seem to be DBIx::SQLEngine, DBIx::SearchBuilder and
377       DBIx::RecordSet.
378
379       Have a look in the source code for my notes on how these modules tackle
380       similar problems.
381
382
383
384perl v5.12.0                      2008-12-22           SQL::Abstract::Limit(3)
Impressum