| Subject: | Tangram/Driver/SQLite.pm patch, adding limit_sql() |
| Date: | Fri, 12 Jan 2007 13:34:14 +0200 |
| To: | bug-Tangram [...] rt.cpan.org |
| From: | Assen Tchorbadjiev <pt [...] tchorbadjiev.com> |
Hello,
attached is a patch against the [git] HEAD branch of Tangram, adding a
new subroutine limit_sql(). It also applies against the stable 2.10 release.
I've added some extra comments inside, and I guess there should a
decision whether all of the 3x argument cases should/will be supported.
Example code:
# case 1, explicit offset
my @o = $st->select( $expr, filter => $filter, limit => [ 0, 10 ] );
# case 2, implicit offset, will be set to 0
my @o = $st->select( $expr, filter => $filter, limit => [ 10 ] );
# case 3, same as above, but w/o the array reference
my @o = $st->select( $expr, filter => $filter, limit => 10 );
The limit_sql() will build up always like:
Show quoted text
Show quoted text
> ... LIMIT \d+ OFFSET ( 0 || $arg )
which may, or may not be ok. Please let me know.
As per:
Show quoted textShow quoted text
> Note that if the OFFSET keyword is used in the LIMIT clause,
> then the limit is the first number and the offset is the second number.
> If a comma is used instead of the OFFSET keyword, then the offset is the
> first number and the limit is the second number.
> This seeming contradition is intentional -
> it maximizes compatibility with legacy SQL database systems.
there are two options - either use the OFFSET, or just the \d+,\d+ syntax.
Regards,
Assen
diff --git a/lib/Tangram/Driver/SQLite.pm b/lib/Tangram/Driver/SQLite.pm
index b72f948..0eb2469 100644
--- a/lib/Tangram/Driver/SQLite.pm
+++ b/lib/Tangram/Driver/SQLite.pm
@@ -79,4 +79,19 @@ sub sequence_sql {
return "SELECT nextval('$sequence_name')";
}
+sub limit_sql {
+ my ( $self, $spec ) = @_;
+
+ # default case - simple scalar - use it as the limit part only
+ my ( $offset, $limit ) = ( 0, $spec );
+ if ( ref $spec eq 'ARRAY' ) {
+ # array ref with either one, or two fields.
+ # this will also cover invocation syntax like limit => [ 2 ], where [ 2 ]
+ # will be taken as the limit part, and the offset will be set to 0
+ ( $offset, $limit ) = scalar @$spec > 1 ? ( @$spec ) : ( 0, $spec->[0] );
+ }
+ # always return limit syntax like: \d+ OFFSET \d+ || 0
+ return ( limit => sprintf( '%d OFFSET %d', $limit, $offset ) );
+}
+
1;