Limit / Offset
Marcus Pearce <[email protected]> Fri, 28 Apr 2006 16:05:32 +0100 (BST)
| Newsgroups | gmane.lisp.clsql.devel |
|---|---|
| Message-ID | <[email protected]> |
As of Lispworks version 4.4.5, the implementation of SELECT in CommonSQL accepts a :LIMIT keyword argument (which presumably does the obvious thing although it doesn't appear to be documented in the reference manual).[1] CLSQL:SELECT actually already handles both :LIMIT and :OFFSET keyword arguments (if you're going to have the former, you might as well have the latter) although this fact is currently undocumented and untested. The attached patch against 3.5.6 documents these abilities and adds a couple of tests to the test suite. Using SBCL on linux/x86, the new tests pass with the following backends: Postgresql 8.0.7 [2] Mysql 4.1.12 [3] Sqlite3 3.2.2 [4] I don't know whether other databases support limit clauses in queries so the tests are currently skipped for other backends. Cheers, Marcus [1] ftp://ftp.lispworks.com/pub/software_tools/reference/lw445/readme-4-4-5.pdf Section 11.8.15. [2] http://www.postgresql.org/docs/8.0/static/queries-limit.html [3] http://dev.mysql.com/doc/refman/4.1/en/select.html [4] http://www.sqlite.org/lang_select.html _______________________________________________ CLSQL-Devel mailing list [email protected] http://lists.b9.com/mailman/listinfo/clsql-devel
limit.diff
(text/plain, 5 KB)
diff -ur ../clsql-3.5.6/doc/ref-fdml.xml ./doc/ref-fdml.xml
--- ../clsql-3.5.6/doc/ref-fdml.xml 2006-04-28 14:51:00.000000000 +0100
+++ ./doc/ref-fdml.xml 2006-04-28 15:34:58.000000000 +0100
@@ -1189,6 +1189,22 @@
</listitem>
</varlistentry>
<varlistentry>
+ <term><parameter>limit</parameter></term>
+ <listitem>
+ <para>
+ A non-negative integer.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><parameter>offset</parameter></term>
+ <listitem>
+ <para>
+ A non-negative integer.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
<term><parameter>order-by</parameter></term>
<listitem>
<para>
@@ -1341,8 +1357,8 @@
specified lisp type. The keyword arguments
<parameter>all</parameter>, <parameter>distinct</parameter>,
<parameter>from</parameter>, <parameter>group-by</parameter>,
- <parameter>having</parameter>,
- <parameter>order-by</parameter>,
+ <parameter>having</parameter>, <parameter>limit</parameter>,
+ <parameter>offset</parameter>, <parameter>order-by</parameter>,
<parameter>set-operation</parameter> and
<parameter>where</parameter> are used to specify, using the
symbolic SQL syntax, the corresponding components of the SQL
@@ -1423,7 +1439,7 @@
:result-types :auto)
=> (10)
-(clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
+(select [avg [height]] :from [employee] :flatp t :field-names nil)
=> (1.58999584d0)
(select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
@@ -1442,13 +1458,21 @@
:flatp t)
=> (1 2 3 4)
-(clsql:select [emplid] :from [employee]
- :where [in [emplid] '(1 2 3 4)]
- :flatp t
- :order-by [emplid]
- :field-names nil)
+(select [emplid] :from [employee]
+ :where [in [emplid] '(1 2 3 4)]
+ :flatp t
+ :order-by [emplid]
+ :field-names nil)
=> (1 2 3 4)
+(select [emplid] :from [employee]
+ :order-by [emplid]
+ :limit 5
+ :offset 3
+ :field-names nil
+ :flatp t)
+=> (4 5 6 7 8)
+
(select [first-name] [last-name] :from [employee]
:field-names nil
:order-by '(([first-name] :asc) ([last-name] :desc)))
diff -ur ../clsql-3.5.6/doc/TODO ./doc/TODO
--- ../clsql-3.5.6/doc/TODO 2006-04-28 14:51:00.000000000 +0100
+++ ./doc/TODO 2006-04-28 14:52:48.000000000 +0100
@@ -12,8 +12,7 @@
- SQL operators: group-by, limit, not-null, ==, is, having, the, uplike,
view-class, coalesce, except, exists, substring, concat
- - SELECT: additional keyword arguments accepted include :LIMIT, :OFFSET,
- :INNER-JOIN and :ON.
+ - SELECT: additional keyword arguments accepted include :INNER-JOIN and :ON.
4. Documenting lower level, non-CommonSQL functions (some of this is already
done).
diff -ur ../clsql-3.5.6/tests/test-fdml.lisp ./tests/test-fdml.lisp
--- ../clsql-3.5.6/tests/test-fdml.lisp 2006-04-28 14:51:00.000000000 +0100
+++ ./tests/test-fdml.lisp 2006-04-28 14:52:48.000000000 +0100
@@ -495,6 +495,23 @@
"foo\\bar\\baz" "foo\\bar\\baz" "foo\\bar\\baz" "foo\\bar\\baz"
"foo\\bar\\baz" "foo\\bar\\baz"))
+(deftest :fdml/select/37
+ (clsql:select [emplid] :from [employee]
+ :order-by [emplid]
+ :limit 5
+ :field-names nil
+ :flatp t)
+ (1 2 3 4 5))
+
+(deftest :fdml/select/38
+ (clsql:select [emplid] :from [employee]
+ :order-by [emplid]
+ :limit 5
+ :offset 3
+ :field-names nil
+ :flatp t)
+ (4 5 6 7 8))
+
(deftest :fdml/do-query/1
(let ((result '()))
(clsql:do-query ((name) [select [last-name] :from [employee]
diff -ur ../clsql-3.5.6/tests/test-init.lisp ./tests/test-init.lisp
--- ../clsql-3.5.6/tests/test-init.lisp 2006-04-28 14:51:00.000000000 +0100
+++ ./tests/test-init.lisp 2006-04-28 14:52:48.000000000 +0100
@@ -623,11 +623,14 @@
((and (eq *test-database-underlying-type* :mssql)
(clsql-sys:in test :fdml/select/9))
(push (cons test "mssql uses integer math for AVG") skip-tests))
+ ((and (not (member *test-database-underlying-type*
+ '(:postgresql :mysql :sqlite3)))
+ (clsql-sys:in test :fdml/select/37 :fdml/select/38))
+ (push (cons test "LIMIT keyword not supported in SELECT") skip-tests))
(t
(push test-form test-forms)))))
(values (nreverse test-forms) (nreverse skip-tests))))
-
(defun rapid-load (type &optional (position 0))
"Rapid load for interactive testing."
(when *default-database*