From ab18d1f46a0501f9a54da1ef08ff4967f4b63b68 Mon Sep 17 00:00:00 2001
From: Owen Lin <owenlin@google.com>
Date: Wed, 6 May 2009 16:45:59 -0700
Subject: Fix SQLite limit issue.

SQLLite limit is not only used to limit the returned number of data.
It can be used to do an offset query.

For example, "SELECT * FROM table LIMIT 100, 10", will return the
data of index in the range of [100, 100 + 10).

This change set enable this kind of useage.

This is also more efficient than use "cursor.moveToPosition()".
In my experiment, I query 1000 items in batch mode, i.e.,
get 20 items out of 1000 each time.

    Time of using LIMIT clause: 626ms
    Time of useing "cursor.moveToPosition()": 2062ms
---
 core/java/android/database/sqlite/SQLiteQueryBuilder.java | 12 ++++++++----
 1 file changed, 8 insertions(+), 4 deletions(-)

(limited to 'core/java/android')

diff --git a/core/java/android/database/sqlite/SQLiteQueryBuilder.java b/core/java/android/database/sqlite/SQLiteQueryBuilder.java
index ab7c827..8a63919 100644
--- a/core/java/android/database/sqlite/SQLiteQueryBuilder.java
+++ b/core/java/android/database/sqlite/SQLiteQueryBuilder.java
@@ -18,16 +18,15 @@ package android.database.sqlite;
 
 import android.database.Cursor;
 import android.database.DatabaseUtils;
-import android.database.sqlite.SQLiteDatabase;
 import android.provider.BaseColumns;
 import android.text.TextUtils;
-import android.util.Config;
 import android.util.Log;
 
 import java.util.Iterator;
 import java.util.Map;
 import java.util.Set;
 import java.util.Map.Entry;
+import java.util.regex.Pattern;
 
 /**
  * This is a convience class that helps build SQL queries to be sent to
@@ -36,10 +35,12 @@ import java.util.Map.Entry;
 public class SQLiteQueryBuilder
 {
     private static final String TAG = "SQLiteQueryBuilder";
+    private static final Pattern sLimitPattern =
+            Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?");
 
     private Map<String, String> mProjectionMap = null;
     private String mTables = "";
-    private StringBuilder mWhereClause = new StringBuilder(64);
+    private final StringBuilder mWhereClause = new StringBuilder(64);
     private boolean mDistinct;
     private SQLiteDatabase.CursorFactory mFactory;
 
@@ -169,6 +170,9 @@ public class SQLiteQueryBuilder
             throw new IllegalArgumentException(
                     "HAVING clauses are only permitted when using a groupBy clause");
         }
+        if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
+            throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
+        }
 
         StringBuilder query = new StringBuilder(120);
 
@@ -187,7 +191,7 @@ public class SQLiteQueryBuilder
         appendClause(query, " GROUP BY ", groupBy);
         appendClause(query, " HAVING ", having);
         appendClause(query, " ORDER BY ", orderBy);
-        appendClauseEscapeClause(query, " LIMIT ", limit);
+        appendClause(query, " LIMIT ", limit);
 
         return query.toString();
     }
-- 
cgit v1.1