reduce locking when using SQLiteStatement

Do compiling of sql, binding of args and execution of the SQL
statement within one single database lock period.
This reduces the number of times the database lock
needs to be acquired during the course of compilation, binding
and execution of a SQLiteStatement.

Change-Id: I22b090ec9e10fc0aa2532a93bafe610af2546b58

2 files changed, 69 insertions, 126 deletions

diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java
index ca8dac1..91ef0b7 100644
--- a/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java
+++ b/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java
@@ -279,11 +279,24 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         }
     }
 
+    private static class ClassToTestSqlCompilationAndCaching extends SQLiteProgram {
+        private ClassToTestSqlCompilationAndCaching(SQLiteDatabase db, String sql) {
+            super(db, sql);
+        }
+        private static ClassToTestSqlCompilationAndCaching create(SQLiteDatabase db, String sql) {
+            db.lock();
+            try {
+                return new ClassToTestSqlCompilationAndCaching(db, sql);
+            } finally {
+                db.unlock();
+            }
+        }
+    }
+
     @SmallTest
     public void testLruCachingOfSqliteCompiledSqlObjs() {
         mDatabase.disableWriteAheadLogging();
         mDatabase.execSQL("CREATE TABLE test (i int, j int);");
-        mDatabase.execSQL("insert into test values(1,1);");
         // set cache size
         int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
         mDatabase.setMaxSqlCacheSize(N);
@@ -292,22 +305,24 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // insertion of (N+1)th entry, make sure 0th entry is closed
         ArrayList<Integer> stmtObjs = new ArrayList<Integer>();
         ArrayList<String> sqlStrings = new ArrayList<String>();
-        SQLiteStatement stmt0 = null;
+        int stmt0 = 0;
         for (int i = 0; i < N+1; i++) {
-            String s = "select * from test where i = " + i + " and j = ?";
+            String s = "insert into test values(" + i + ",?);";
             sqlStrings.add(s);
-            SQLiteStatement c = mDatabase.compileStatement(s);
-            c.bindLong(1, 1);
-            stmtObjs.add(i, c.getSqlStatementId());
+            ClassToTestSqlCompilationAndCaching c =
+                    ClassToTestSqlCompilationAndCaching.create(mDatabase, s);
+            int n = c.getSqlStatementId();
+            stmtObjs.add(i, n);
             if (i == 0) {
-                // save thie SQLiteStatement obj. we want to make sure it is thrown out of
-                // the cache and its handle is 0'ed.
-                stmt0 = c;
+                // save the statementId of this obj. we want to make sure it is thrown out of
+                // the cache at the end of this test.
+                stmt0 = n;
             }
             c.close();
         }
-        // is 0'th entry out of the cache?
-        assertEquals(0, stmt0.getSqlStatementId());
+        // is 0'th entry out of the cache? it should be in the list of statementIds
+        // corresponding to the pre-compiled sql statements to be finalized.
+        assertTrue(mDatabase.getQueuedUpStmtList().contains(stmt0));
         for (int i = 1; i < N+1; i++) {
             SQLiteCompiledSql compSql = mDatabase.getCompiledStatementForSql(sqlStrings.get(i));
             assertNotNull(compSql);
@@ -321,11 +336,7 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
                 "num1 INTEGER, num2 INTEGER, image BLOB);");
         final String statement = "DELETE FROM test WHERE _id=?;";
         SQLiteStatement statementDoNotClose = mDatabase.compileStatement(statement);
-        // SQl statement is compiled only at find bind or execute call
-        assertTrue(statementDoNotClose.getSqlStatementId() == 0);
         statementDoNotClose.bindLong(1, 1);
-        assertTrue(statementDoNotClose.getSqlStatementId() > 0);
-        int nStatement = statementDoNotClose.getSqlStatementId();
         /* do not close statementDoNotClose object.
          * That should leave it in SQLiteDatabase.mPrograms.
          * mDatabase.close() in tearDown() should release it.
@@ -340,24 +351,25 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
     public void testStatementClose() {
         mDatabase.execSQL("CREATE TABLE test (i int, j int);");
         // fill up statement cache in mDatabase\
-        int N = 26;
+        int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
         mDatabase.setMaxSqlCacheSize(N);
         SQLiteStatement stmt;
         int stmt0Id = 0;
         for (int i = 0; i < N; i ++) {
-            stmt = mDatabase.compileStatement("insert into test values(" + i + ", ?);");
-            stmt.bindLong(1, 1);
+            ClassToTestSqlCompilationAndCaching c =
+                    ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                            "insert into test values(" + i + ", ?);");
             // keep track of 0th entry
             if (i == 0) {
-                stmt0Id = stmt.getSqlStatementId();
+                stmt0Id = c.getSqlStatementId();
             }
-            stmt.executeInsert();
-            stmt.close();
+            c.close();
         }
 
         // add one more to the cache - and the above 'stmt0Id' should fall out of cache
-        SQLiteStatement stmt1 = mDatabase.compileStatement("insert into test values(100, ?);");
-        stmt1.bindLong(1, 1);
+        ClassToTestSqlCompilationAndCaching stmt1 =
+                ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                        "insert into test values(100, ?);");
         stmt1.close();
 
         // the above close() should have queuedUp the statement for finalization
@@ -381,18 +393,18 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // fill up statement cache in mDatabase in a thread
         Thread t1 = new Thread() {
             @Override public void run() {
-                int N = 26;
+                int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
                 mDatabase.setMaxSqlCacheSize(N);
                 SQLiteStatement stmt;
                 for (int i = 0; i < N; i ++) {
-                    stmt = mDatabase.compileStatement("insert into test values(" + i + ", ?);");
-                    stmt.bindLong(1,1);
+                    ClassToTestSqlCompilationAndCaching c =
+                        ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                                "insert into test values(" + i + ", ?);");
                     // keep track of 0th entry
                     if (i == 0) {
-                        setStmt0Id(stmt.getSqlStatementId());
+                        stmt0Id = c.getSqlStatementId();
                     }
-                    stmt.executeInsert();
-                    stmt.close();
+                    c.close();
                 }
             }
         };
@@ -404,9 +416,9 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // just for the heck of it, do it in a separate thread
         Thread t2 = new Thread() {
             @Override public void run() {
-                SQLiteStatement stmt1 = mDatabase.compileStatement(
-                        "insert into test values(100, ?);");
-                stmt1.bindLong(1, 1);
+                ClassToTestSqlCompilationAndCaching stmt1 =
+                        ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                                "insert into test values(100, ?);");
                 stmt1.close();
             }
         };
@@ -452,18 +464,18 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // fill up statement cache in mDatabase in a thread
         Thread t1 = new Thread() {
             @Override public void run() {
-                int N = 26;
+                int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
                 mDatabase.setMaxSqlCacheSize(N);
                 SQLiteStatement stmt;
                 for (int i = 0; i < N; i ++) {
-                    stmt = mDatabase.compileStatement("insert into test values(" + i + ", ?);");
-                    stmt.bindLong(1, 1);
+                    ClassToTestSqlCompilationAndCaching c =
+                            ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                                    "insert into test values(" + i + ", ?);");
                     // keep track of 0th entry
                     if (i == 0) {
-                        setStmt0Id(stmt.getSqlStatementId());
+                        stmt0Id = c.getSqlStatementId();
                     }
-                    stmt.executeInsert();
-                    stmt.close();
+                    c.close();
                 }
             }
         };
@@ -475,8 +487,9 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // just for the heck of it, do it in a separate thread
         Thread t2 = new Thread() {
             @Override public void run() {
-                SQLiteStatement stmt1 = mDatabase.compileStatement(
-                        "insert into test values(100, ?);");
+                ClassToTestSqlCompilationAndCaching stmt1 =
+                        ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                                "insert into test values(100, ?);");
                 stmt1.bindLong(1, 1);
                 stmt1.close();
             }
diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java
index eb27551..217545f 100644
--- a/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java
+++ b/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java
@@ -158,14 +158,16 @@ public class SQLiteStatementTest extends AndroidTestCase {
         assertEquals(0, stmt.getSqlStatementId());
         int colValue = new Random().nextInt();
         stmt.bindLong(1, colValue);
-        // verify that the sql statement is now compiled
-        int n = stmt.nStatement;
-        assertTrue(n > 0);
-        assertEquals(n, stmt.getSqlStatementId());
+        // verify that the sql statement is still not compiled
+        assertEquals(0, stmt.getSqlStatementId());
         // should still be using the mDatabase connection - verify
         assertEquals(mDatabase.mNativeHandle, stmt.nHandle);
+        assertEquals(mDatabase, stmt.mDatabase);
         stmt.bindString(2, "blah" + colValue);
-        assertEquals(n, stmt.nStatement);
+        // verify that the sql statement is still not compiled
+        assertEquals(0, stmt.getSqlStatementId());
+        assertEquals(mDatabase.mNativeHandle, stmt.nHandle);
+        assertEquals(mDatabase, stmt.mDatabase);
         stmt.executeInsert();
         // now that the statement is executed, pre-compiled statement should be released
         assertEquals(0, stmt.nStatement);
@@ -187,97 +189,25 @@ public class SQLiteStatementTest extends AndroidTestCase {
         assertEquals(mDatabase.mNativeHandle, stmt.nHandle);
         assertEquals(mDatabase, stmt.mDatabase);
         stmt.bindString(1, "blah" + colValue);
-        // verify that the sql statement is now compiled
-        n = stmt.nStatement;
-        assertTrue(n > 0);
-        assertEquals(n, stmt.getSqlStatementId());
-        SQLiteDatabase dbUsed = mDatabase;
-        if (wal) {
-            // if wal is set, should be using a pooled connection handle
-            dbUsed = mDatabase.mConnectionPool.getConnectionList().get(0);
-            assertTrue(mDatabase.mNativeHandle != dbUsed.mNativeHandle);
-        }
-        assertEquals(dbUsed.mNativeHandle, stmt.nHandle);
-        assertEquals(dbUsed, stmt.mDatabase);
+        // verify that the sql statement is still not compiled
+        assertEquals(0, stmt.nStatement);
+        assertEquals(0, stmt.getSqlStatementId());
+        assertEquals(mDatabase.mNativeHandle, stmt.nHandle);
+        assertEquals(mDatabase, stmt.mDatabase);
         // execute the statement
         Long l = stmt.simpleQueryForLong();
         assertEquals(colValue, l.intValue());
         // now that the statement is executed, pre-compiled statement should be released
         assertEquals(0, stmt.nStatement);
         assertEquals(0, stmt.getSqlStatementId());
-        // but the database handle should still remain attached to the statement
-        assertEquals(dbUsed.mNativeHandle, stmt.nHandle);
-        assertEquals(dbUsed, stmt.mDatabase);
+        assertEquals(mDatabase.mNativeHandle, stmt.nHandle);
+        assertEquals(mDatabase, stmt.mDatabase);
         stmt.close();
         // pre-compiled SQL statement should still remain released from this object
         assertEquals(0, stmt.nStatement);
         assertEquals(0, stmt.getSqlStatementId());
         // but the database handle should still remain attached to the statement
-        assertEquals(dbUsed, stmt.mDatabase);
-    }
-
-    /**
-     * test to make sure SqliteStatement.nStatement is populated only during bind and execute calls.
-     */
-    public void testGetSqlStatementId() {
-        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, text1 TEXT, text2 TEXT, " +
-                "num1 INTEGER, num2 INTEGER, image BLOB);");
-        final String statement = "DELETE FROM test WHERE _id=?;";
-        SQLiteStatement statementOne = mDatabase.compileStatement(statement);
-        // sql statement is NOT compiled until the bind or execute call.
-        statementOne.bindLong(1, 1);
-        SQLiteStatement statementTwo = mDatabase.compileStatement(statement);
-        statementTwo.bindLong(1, 1);
-        // since the same compiled statement is being accessed at the same time by 2 different
-        // objects, they each get their own statement id
-        assertTrue(statementOne.getSqlStatementId() != statementTwo.getSqlStatementId());
-        statementOne.close();
-        statementTwo.close();
-
-        // two SQLiteStatements referring to the same SQL statement should refer to the same
-        // pre-compiled SQl statement id if the SQLiteStatement objects are NOT in use at the same
-        // time
-        statementOne = mDatabase.compileStatement(statement);
-        statementOne.bindLong(1, 1);
-        // now that the SQL statement is compiled, get its pre-compiled SQL statement id
-        int n = statementOne.getSqlStatementId();
-        statementOne.close();
-        statementTwo = mDatabase.compileStatement(statement);
-        statementTwo.bindLong(1, 2); // use different value for bindarg, just for the heck of it
-        assertEquals(n, statementTwo.getSqlStatementId());
-        statementTwo.close();
-
-        // now try to compile 2 different statements and they should have different uniquerIds.
-        SQLiteStatement statement1 = mDatabase.compileStatement("DELETE FROM test WHERE _id > ?;");
-        statement1.bindLong(1, 1);
-        SQLiteStatement statement2 = mDatabase.compileStatement("DELETE FROM test WHERE _id < ?;");
-        statement2.bindLong(1, 11);
-        assertTrue(statement1.getSqlStatementId() != statement2.getSqlStatementId());
-        statement1.close();
-        statement2.close();
-    }
-
-    public void testOnAllReferencesReleased() {
-        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, text1 TEXT, text2 TEXT, " +
-                "num1 INTEGER, num2 INTEGER, image BLOB);");
-        final String statement = "DELETE FROM test WHERE _id=?;";
-        SQLiteStatement statementOne = mDatabase.compileStatement(statement);
-        statementOne.bindLong(1, 1);
-        assertTrue(statementOne.getSqlStatementId() > 0);
-        statementOne.releaseReference();
-        assertEquals(0, statementOne.getSqlStatementId());
-        statementOne.close();
-    }
-
-    public void testOnAllReferencesReleasedFromContainer() {
-        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, text1 TEXT, text2 TEXT, " +
-                "num1 INTEGER, num2 INTEGER, image BLOB);");
-        final String statement = "DELETE FROM test WHERE _id=?;";
-        SQLiteStatement statementOne = mDatabase.compileStatement(statement);
-        statementOne.bindLong(1, 1);
-        assertTrue(statementOne.getSqlStatementId() > 0);
-        statementOne.releaseReferenceFromContainer();
-        assertEquals(0, statementOne.getSqlStatementId());
-        statementOne.close();
+        assertEquals(mDatabase.mNativeHandle, stmt.nHandle);
+        assertEquals(mDatabase, stmt.mDatabase);
     }
 }