From e5360fbf3efe85427f7e7f59afe7bbeddb4949ac Mon Sep 17 00:00:00 2001 From: Jeff Brown Date: Mon, 31 Oct 2011 17:48:13 -0700 Subject: Rewrite SQLite database wrappers. The main theme of this change is encapsulation. This change preserves all existing functionality but the implementation is now much cleaner. Instead of a "database lock", access to the database is treated as a resource acquisition problem. If a thread's owns a database connection, then it can access the database; otherwise, it must acquire a database connection first, and potentially wait for other threads to give up theirs. The SQLiteConnectionPool encapsulates the details of how connections are created, configured, acquired, released and disposed. One new feature is that SQLiteConnectionPool can make scheduling decisions about which thread should next acquire a database connection when there is contention among threads. The factors considered include wait queue ordering (fairness among peers), whether the connection is needed for an interactive operation (unfairness on behalf of the UI), and whether the primary connection is needed or if any old connection will do. Thus one goal of the new SQLiteConnectionPool is to improve the utilization of database connections. To emulate some quirks of the old "database lock," we introduce the concept of the primary database connection. The primary database connection is the one that is typically used to perform write operations to the database. When a thread holds the primary database connection, it effectively prevents other threads from modifying the database (although they can still read). What's more, those threads will block when they try to acquire the primary connection, which provides the same kind of mutual exclusion features that the old "database lock" had. (In truth, we probably don't need to be requiring use of the primary database connection in as many places as we do now, but we can seek to refine that behavior in future patches.) Another significant change is that native sqlite3_stmt objects (prepared statements) are fully encapsulated by the SQLiteConnection object that owns them. This ensures that the connection can finalize (destroy) all extant statements that belong to a database connection when the connection is closed. (In the original code, this was very complicated because the sqlite3_stmt objects were managed by SQLiteCompiledSql objects which had different lifetime from the original SQLiteDatabase that created them. Worse, the SQLiteCompiledSql finalizer method couldn't actually destroy the sqlite3_stmt objects because it ran on the finalizer thread and therefore could not guarantee that it could acquire the database lock in order to do the work. This resulted in some rather tortured logic involving a list of pending finalizable statements and a high change of deadlocks or leaks.) Because sqlite3_stmt objects never escape the confines of the SQLiteConnection that owns them, we can also greatly simplify the design of the SQLiteProgram, SQLiteQuery and SQLiteStatement objects. They no longer have to wrangle a native sqlite3_stmt object pointer and manage its lifecycle. So now all they do is hold bind arguments and provide a fancy API. All of the JNI glue related to managing database connections and performing transactions is now bound to SQLiteConnection (rather than being scattered everywhere). This makes sense because SQLiteConnection owns the native sqlite3 object, so it is the only class in the system that can interact with the native SQLite database directly. Encapsulation for the win. One particularly tricky part of this change is managing the ownership of SQLiteConnection objects. At any given time, a SQLiteConnection is either owned by a SQLiteConnectionPool or by a SQLiteSession. SQLiteConnections should never be leaked, but we handle that case too (and yell about it with CloseGuard). A SQLiteSession object is responsible for acquiring and releasing a SQLiteConnection object on behalf of a single thread as needed. For example, the session acquires a connection when a transaction begins and releases it when finished. If the session cannot acquire a connection immediately, then the requested operation blocks until a connection becomes available. SQLiteSessions are thread-local. A SQLiteDatabase assigns a distinct session to each thread that performs database operations. This is very very important. First, it prevents two threads from trying to use the same SQLiteConnection at the same time (because two threads can't share the same session). Second, it prevents a single thread from trying to acquire two SQLiteConnections simultaneously from the same database (because a single thread can't have two sessions for the same database which, in addition to being greedy, could result in a deadlock). There is strict layering between the various database objects, objects at lower layers are not aware of objects at higher layers. Moreover, objects at higher layers generally own objects at lower layers and are responsible for ensuring they are properly disposed when no longer needed (good for the environment). API layer: SQLiteDatabase, SQLiteProgram, SQLiteQuery, SQLiteStatement. Session layer: SQLiteSession. Connection layer: SQLiteConnectionPool, SQLiteConnection. Native layer: JNI glue. By avoiding cyclic dependencies between layers, we make the architecture much more intelligible, maintainable and robust. Finally, this change adds a great deal of new debugging information. It is now possible to view a list of the most recent database operations including how long they took to run using "adb shell dumpsys dbinfo". (Because most of the interesting work happens in SQLiteConnection, it is easy to add debugging instrumentation to track all database operations in one place.) Change-Id: Iffb4ce72d8bcf20b4e087d911da6aa84d2f15297 --- .../sqlite/DatabaseConnectionPoolTest.java | 368 -------- .../android/database/sqlite/SQLiteCursorTest.java | 48 +- .../database/sqlite/SQLiteDatabaseTest.java | 971 --------------------- .../database/sqlite/SQLiteStatementTest.java | 213 ----- .../sqlite/SQLiteUnfinalizedExceptionTest.java | 81 -- 5 files changed, 1 insertion(+), 1680 deletions(-) delete mode 100644 core/tests/coretests/src/android/database/sqlite/DatabaseConnectionPoolTest.java delete mode 100644 core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java delete mode 100644 core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java delete mode 100644 core/tests/coretests/src/android/database/sqlite/SQLiteUnfinalizedExceptionTest.java (limited to 'core/tests') diff --git a/core/tests/coretests/src/android/database/sqlite/DatabaseConnectionPoolTest.java b/core/tests/coretests/src/android/database/sqlite/DatabaseConnectionPoolTest.java deleted file mode 100644 index 525dd2d..0000000 --- a/core/tests/coretests/src/android/database/sqlite/DatabaseConnectionPoolTest.java +++ /dev/null @@ -1,368 +0,0 @@ -/* - * Copyright (C) 2006 The Android Open Source Project - * - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package android.database.sqlite; - -import android.content.Context; -import android.database.sqlite.SQLiteDatabaseTest.ClassToTestSqlCompilationAndCaching; -import android.test.AndroidTestCase; -import android.test.suitebuilder.annotation.SmallTest; -import android.util.Log; - -import java.io.File; -import java.util.ArrayList; -import java.util.HashMap; - -public class DatabaseConnectionPoolTest extends AndroidTestCase { - private static final String TAG = "DatabaseConnectionPoolTest"; - - private static final int MAX_CONN = 5; - private static final String TEST_SQL = "select * from test where i = ? AND j = 1"; - private static final String[] TEST_SQLS = new String[] { - TEST_SQL, TEST_SQL + 1, TEST_SQL + 2, TEST_SQL + 3, TEST_SQL + 4 - }; - - private SQLiteDatabase mDatabase; - private File mDatabaseFile; - private DatabaseConnectionPool mTestPool; - - @Override - protected void setUp() throws Exception { - super.setUp(); - - File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); - mDatabaseFile = new File(dbDir, "database_test.db"); - if (mDatabaseFile.exists()) { - mDatabaseFile.delete(); - } - mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); - assertNotNull(mDatabase); - mDatabase.execSQL("create table test (i int, j int);"); - mTestPool = new DatabaseConnectionPool(mDatabase); - assertNotNull(mTestPool); - } - - @Override - protected void tearDown() throws Exception { - mTestPool.close(); - mDatabase.close(); - mDatabaseFile.delete(); - super.tearDown(); - } - - @SmallTest - public void testGetAndRelease() { - mTestPool.setMaxPoolSize(MAX_CONN); - // connections should be lazily created. - assertEquals(0, mTestPool.getSize()); - // MAX pool size should be set to MAX_CONN - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // get a connection - SQLiteDatabase db = mTestPool.get(TEST_SQL); - // pool size should be one - since only one should be allocated for the above get() - assertEquals(1, mTestPool.getSize()); - assertEquals(mDatabase, db.mParentConnObj); - // no free connections should be available - assertEquals(0, mTestPool.getFreePoolSize()); - assertFalse(mTestPool.isDatabaseObjFree(db)); - // release the connection - mTestPool.release(db); - assertEquals(1, mTestPool.getFreePoolSize()); - assertEquals(1, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - assertTrue(mTestPool.isDatabaseObjFree(db)); - // release the same object again and expect IllegalStateException - try { - mTestPool.release(db); - fail("illegalStateException expected"); - } catch (IllegalStateException e ) { - // expected. - } - } - - /** - * get all connections from the pool and ask for one more. - * should get one of the connections already got so far. - */ - @SmallTest - public void testGetAllConnAndOneMore() { - mTestPool.setMaxPoolSize(MAX_CONN); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - ArrayList dbObjs = new ArrayList(); - for (int i = 0; i < MAX_CONN; i++) { - SQLiteDatabase db = mTestPool.get(TEST_SQL); - assertFalse(dbObjs.contains(db)); - dbObjs.add(db); - assertEquals(mDatabase, db.mParentConnObj); - } - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // pool is maxed out and no free connections. ask for one more connection - SQLiteDatabase db1 = mTestPool.get(TEST_SQL); - // make sure db1 is one of the existing ones - assertTrue(dbObjs.contains(db1)); - // pool size should remain at MAX_CONN - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // release db1 but since it is allocated 2 times, it should still remain 'busy' - mTestPool.release(db1); - assertFalse(mTestPool.isDatabaseObjFree(db1)); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // release all connections - for (int i = 0; i < MAX_CONN; i++) { - mTestPool.release(dbObjs.get(i)); - } - // all objects in the pool should be freed now - assertEquals(MAX_CONN, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - } - - /** - * same as above except that each connection has different SQL statement associated with it. - */ - @SmallTest - public void testConnRetrievalForPreviouslySeenSql() { - mTestPool.setMaxPoolSize(MAX_CONN); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - HashMap dbObjs = new HashMap(); - for (int i = 0; i < MAX_CONN; i++) { - SQLiteDatabase db = mTestPool.get(TEST_SQLS[i]); - executeSqlOnDatabaseConn(db, TEST_SQLS[i]); - assertFalse(dbObjs.values().contains(db)); - dbObjs.put(TEST_SQLS[i], db); - } - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // pool is maxed out and no free connections. ask for one more connection - // use a previously seen SQL statement - String testSql = TEST_SQLS[MAX_CONN - 1]; - SQLiteDatabase db1 = mTestPool.get(testSql); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // make sure db1 is one of the existing ones - assertTrue(dbObjs.values().contains(db1)); - assertEquals(db1, dbObjs.get(testSql)); - // do the same again - SQLiteDatabase db2 = mTestPool.get(testSql); - // make sure db1 is one of the existing ones - assertEquals(db2, dbObjs.get(testSql)); - - // pool size should remain at MAX_CONN - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - // release db1 but since the same connection is allocated 3 times, - // it should still remain 'busy' - mTestPool.release(db1); - assertFalse(mTestPool.isDatabaseObjFree(dbObjs.get(testSql))); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - // release db2 but since the same connection is allocated 2 times, - // it should still remain 'busy' - mTestPool.release(db2); - assertFalse(mTestPool.isDatabaseObjFree(dbObjs.get(testSql))); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - // release all connections - for (int i = 0; i < MAX_CONN; i++) { - mTestPool.release(dbObjs.get(TEST_SQLS[i])); - } - // all objects in the pool should be freed now - assertEquals(MAX_CONN, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - } - - private void executeSqlOnDatabaseConn(SQLiteDatabase db, String sql) { - // get the given sql be compiled on the given database connection. - // this will help DatabaseConenctionPool figure out if a given SQL statement - // is already cached by a database connection. - ClassToTestSqlCompilationAndCaching c = - ClassToTestSqlCompilationAndCaching.create(db, sql); - c.close(); - } - - /** - * get a connection for a SQL statement 'blah'. (connection_s) - * make sure the pool has at least one free connection even after this get(). - * and get a connection for the same SQL again. - * this connection should be different from connection_s. - * even though there is a connection with the given SQL pre-compiled, since is it not free - * AND since the pool has free connections available, should get a new connection. - */ - @SmallTest - public void testGetConnForTheSameSql() { - mTestPool.setMaxPoolSize(MAX_CONN); - - SQLiteDatabase db = mTestPool.get(TEST_SQL); - executeSqlOnDatabaseConn(db, TEST_SQL); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(1, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - assertFalse(mTestPool.isDatabaseObjFree(db)); - - SQLiteDatabase db1 = mTestPool.get(TEST_SQL); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(2, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - assertFalse(mTestPool.isDatabaseObjFree(db1)); - assertFalse(db1.equals(db)); - - mTestPool.release(db); - assertEquals(1, mTestPool.getFreePoolSize()); - assertEquals(2, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - mTestPool.release(db1); - assertEquals(2, mTestPool.getFreePoolSize()); - assertEquals(2, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - } - - /** - * get the same connection N times and release it N times. - * this tests DatabaseConnectionPool.PoolObj.mNumHolders - */ - @SmallTest - public void testGetSameConnNtimesAndReleaseItNtimes() { - mTestPool.setMaxPoolSize(MAX_CONN); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - HashMap dbObjs = new HashMap(); - for (int i = 0; i < MAX_CONN; i++) { - SQLiteDatabase db = mTestPool.get(TEST_SQLS[i]); - executeSqlOnDatabaseConn(db, TEST_SQLS[i]); - assertFalse(dbObjs.values().contains(db)); - dbObjs.put(TEST_SQLS[i], db); - } - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // every connection in the pool should have numHolders = 1 - for (int i = 0; i < MAX_CONN; i ++) { - assertEquals(1, mTestPool.getPool().get(i).getNumHolders()); - } - // pool is maxed out and no free connections. ask for one more connection - // use a previously seen SQL statement - String testSql = TEST_SQLS[MAX_CONN - 1]; - SQLiteDatabase db1 = mTestPool.get(testSql); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // make sure db1 is one of the existing ones - assertTrue(dbObjs.values().contains(db1)); - assertEquals(db1, dbObjs.get(testSql)); - assertFalse(mTestPool.isDatabaseObjFree(db1)); - DatabaseConnectionPool.PoolObj poolObj = mTestPool.getPool().get(db1.mConnectionNum - 1); - int numHolders = poolObj.getNumHolders(); - assertEquals(2, numHolders); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // get the same connection N times more - int N = 100; - for (int i = 0; i < N; i++) { - SQLiteDatabase db2 = mTestPool.get(testSql); - assertEquals(db1, db2); - assertFalse(mTestPool.isDatabaseObjFree(db2)); - // numHolders for this object should be now up by 1 - int prev = numHolders; - numHolders = poolObj.getNumHolders(); - assertEquals(prev + 1, numHolders); - } - // release it N times - for (int i = 0; i < N; i++) { - mTestPool.release(db1); - int prev = numHolders; - numHolders = poolObj.getNumHolders(); - assertEquals(prev - 1, numHolders); - assertFalse(mTestPool.isDatabaseObjFree(db1)); - } - // the connection should still have 2 more holders - assertFalse(mTestPool.isDatabaseObjFree(db1)); - assertEquals(2, poolObj.getNumHolders()); - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // release 2 more times - mTestPool.release(db1); - mTestPool.release(db1); - assertEquals(0, poolObj.getNumHolders()); - assertEquals(1, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - assertTrue(mTestPool.isDatabaseObjFree(db1)); - } - - @SmallTest - public void testStressTest() { - mTestPool.setMaxPoolSize(MAX_CONN); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - - HashMap dbMap = new HashMap(); - for (int i = 0; i < MAX_CONN; i++) { - SQLiteDatabase db = mTestPool.get(TEST_SQLS[i]); - assertFalse(dbMap.containsKey(db)); - dbMap.put(db, 1); - executeSqlOnDatabaseConn(db, TEST_SQLS[i]); - } - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // ask for lot more connections but since the pool is maxed out, we should start receiving - // connections that we already got so far - for (int i = MAX_CONN; i < 1000; i++) { - SQLiteDatabase db = mTestPool.get(TEST_SQL + i); - assertTrue(dbMap.containsKey(db)); - int k = dbMap.get(db); - dbMap.put(db, ++k); - } - assertEquals(0, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - // print the distribution of the database connection handles received, should be uniform. - for (SQLiteDatabase d : dbMap.keySet()) { - Log.i(TAG, "connection # " + d.mConnectionNum + ", numHolders: " + dbMap.get(d)); - } - // print the pool info - Log.i(TAG, mTestPool.toString()); - // release all - for (SQLiteDatabase d : dbMap.keySet()) { - int num = dbMap.get(d); - for (int i = 0; i < num; i++) { - mTestPool.release(d); - } - } - assertEquals(MAX_CONN, mTestPool.getFreePoolSize()); - assertEquals(MAX_CONN, mTestPool.getSize()); - assertEquals(MAX_CONN, mTestPool.getMaxPoolSize()); - } -} diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteCursorTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteCursorTest.java index f6b1d04..9ccc6e8 100644 --- a/core/tests/coretests/src/android/database/sqlite/SQLiteCursorTest.java +++ b/core/tests/coretests/src/android/database/sqlite/SQLiteCursorTest.java @@ -21,8 +21,6 @@ import android.content.Context; import android.database.Cursor; import android.test.AndroidTestCase; import android.test.suitebuilder.annotation.LargeTest; -import android.test.suitebuilder.annotation.SmallTest; -import android.util.Log; import java.io.File; import java.util.HashSet; @@ -54,52 +52,8 @@ public class SQLiteCursorTest extends AndroidTestCase { super.tearDown(); } - @SmallTest - public void testQueryObjReassignment() { - mDatabase.enableWriteAheadLogging(); - // have a few connections in the database connection pool - DatabaseConnectionPool pool = mDatabase.mConnectionPool; - pool.setMaxPoolSize(5); - SQLiteCursor cursor = - (SQLiteCursor) mDatabase.rawQuery("select * from " + TABLE_NAME, null); - assertNotNull(cursor); - // it should use a pooled database connection - SQLiteDatabase db = cursor.getDatabase(); - assertTrue(db.mConnectionNum > 0); - assertFalse(mDatabase.equals(db)); - assertEquals(mDatabase, db.mParentConnObj); - assertTrue(pool.getConnectionList().contains(db)); - assertTrue(db.isOpen()); - // do a requery. cursor should continue to use the above pooled connection - cursor.requery(); - SQLiteDatabase dbAgain = cursor.getDatabase(); - assertEquals(db, dbAgain); - // disable WAL so that the pooled connection held by the above cursor is closed - mDatabase.disableWriteAheadLogging(); - assertFalse(db.isOpen()); - assertNull(mDatabase.mConnectionPool); - // requery - which should make the cursor use mDatabase connection since the pooled - // connection is no longer available - cursor.requery(); - SQLiteDatabase db1 = cursor.getDatabase(); - assertTrue(db1.mConnectionNum == 0); - assertEquals(mDatabase, db1); - assertNull(mDatabase.mConnectionPool); - assertTrue(db1.isOpen()); - assertFalse(mDatabase.equals(db)); - // enable WAL and requery - this time a pooled connection should be used - mDatabase.enableWriteAheadLogging(); - cursor.requery(); - db = cursor.getDatabase(); - assertTrue(db.mConnectionNum > 0); - assertFalse(mDatabase.equals(db)); - assertEquals(mDatabase, db.mParentConnObj); - assertTrue(mDatabase.mConnectionPool.getConnectionList().contains(db)); - assertTrue(db.isOpen()); - } - /** - * this test could take a while to execute. so, designate it as LargetTest + * this test could take a while to execute. so, designate it as LargeTest */ @LargeTest public void testFillWindow() { diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java deleted file mode 100644 index 5ef8d11..0000000 --- a/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java +++ /dev/null @@ -1,971 +0,0 @@ -/* - * Copyright (C) 2006 The Android Open Source Project - * - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package android.database.sqlite; - -import android.content.ContentValues; -import android.content.Context; -import android.database.Cursor; -import android.database.DatabaseErrorHandler; -import android.database.DatabaseUtils; -import android.database.DefaultDatabaseErrorHandler; -import android.database.sqlite.SQLiteDatabase; -import android.database.sqlite.SQLiteDatabase.CursorFactory; -import android.database.sqlite.SQLiteStatement; -import android.test.AndroidTestCase; -import android.test.suitebuilder.annotation.LargeTest; -import android.test.suitebuilder.annotation.MediumTest; -import android.test.suitebuilder.annotation.SmallTest; -import android.test.suitebuilder.annotation.Suppress; -import android.util.Log; -import android.util.Pair; - -import java.io.File; -import java.util.ArrayList; -import java.util.List; - -public class SQLiteDatabaseTest extends AndroidTestCase { - private static final String TAG = "DatabaseGeneralTest"; - private static final String TEST_TABLE = "test"; - private static final int CURRENT_DATABASE_VERSION = 42; - private SQLiteDatabase mDatabase; - private File mDatabaseFile; - private static final int INSERT = 1; - private static final int UPDATE = 2; - private static final int DELETE = 3; - private static final String DB_NAME = "database_test.db"; - - @Override - protected void setUp() throws Exception { - super.setUp(); - dbSetUp(); - } - - @Override - protected void tearDown() throws Exception { - dbTeardown(); - super.tearDown(); - } - - private void dbTeardown() throws Exception { - mDatabase.close(); - mDatabaseFile.delete(); - } - - private void dbSetUp() throws Exception { - File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); - mDatabaseFile = new File(dbDir, DB_NAME); - if (mDatabaseFile.exists()) { - mDatabaseFile.delete(); - } - mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); - assertNotNull(mDatabase); - mDatabase.setVersion(CURRENT_DATABASE_VERSION); - } - - @SmallTest - public void testEnableWriteAheadLogging() { - mDatabase.disableWriteAheadLogging(); - assertNull(mDatabase.mConnectionPool); - mDatabase.enableWriteAheadLogging(); - DatabaseConnectionPool pool = mDatabase.mConnectionPool; - assertNotNull(pool); - // make the same call again and make sure the pool already setup is not re-created - mDatabase.enableWriteAheadLogging(); - assertEquals(pool, mDatabase.mConnectionPool); - } - - @SmallTest - public void testDisableWriteAheadLogging() { - mDatabase.execSQL("create table test (i int);"); - mDatabase.enableWriteAheadLogging(); - assertNotNull(mDatabase.mConnectionPool); - // get a pooled database connection - SQLiteDatabase db = mDatabase.getDbConnection("select * from test"); - assertNotNull(db); - assertFalse(mDatabase.equals(db)); - assertTrue(db.isOpen()); - // disable WAL - which should close connection pool and all pooled connections - mDatabase.disableWriteAheadLogging(); - assertNull(mDatabase.mConnectionPool); - assertFalse(db.isOpen()); - } - - @SmallTest - public void testCursorsWithClosedDbConnAfterDisableWriteAheadLogging() { - mDatabase.disableWriteAheadLogging(); - mDatabase.beginTransactionNonExclusive(); - mDatabase.execSQL("create table test (i int);"); - mDatabase.execSQL("insert into test values(1);"); - mDatabase.setTransactionSuccessful(); - mDatabase.endTransaction(); - mDatabase.enableWriteAheadLogging(); - assertNotNull(mDatabase.mConnectionPool); - assertEquals(0, mDatabase.mConnectionPool.getSize()); - assertEquals(0, mDatabase.mConnectionPool.getFreePoolSize()); - // get a cursor which should use pooled database connection - Cursor c = mDatabase.rawQuery("select * from test", null); - assertEquals(1, c.getCount()); - assertEquals(1, mDatabase.mConnectionPool.getSize()); - assertEquals(1, mDatabase.mConnectionPool.getFreePoolSize()); - SQLiteDatabase db = mDatabase.mConnectionPool.getConnectionList().get(0); - assertTrue(mDatabase.mConnectionPool.isDatabaseObjFree(db)); - // disable WAL - which should close connection pool and all pooled connections - mDatabase.disableWriteAheadLogging(); - assertNull(mDatabase.mConnectionPool); - assertFalse(db.isOpen()); - // cursor data should still be accessible because it is fetching data from CursorWindow - c.moveToNext(); - assertEquals(1, c.getInt(0)); - c.requery(); - assertEquals(1, c.getCount()); - c.moveToNext(); - assertEquals(1, c.getInt(0)); - c.close(); - } - - /** - * a transaction should be started before a standalone-update/insert/delete statement - */ - @SmallTest - public void testStartXactBeforeUpdateSql() throws InterruptedException { - runTestForStartXactBeforeUpdateSql(INSERT); - runTestForStartXactBeforeUpdateSql(UPDATE); - runTestForStartXactBeforeUpdateSql(DELETE); - } - private void runTestForStartXactBeforeUpdateSql(int stmtType) throws InterruptedException { - createTableAndClearCache(); - - ContentValues values = new ContentValues(); - // make some changes to data in TEST_TABLE - for (int i = 0; i < 5; i++) { - values.put("i", i); - values.put("j", "i" + System.currentTimeMillis()); - mDatabase.insert(TEST_TABLE, null, values); - switch (stmtType) { - case UPDATE: - values.put("j", "u" + System.currentTimeMillis()); - mDatabase.update(TEST_TABLE, values, "i = " + i, null); - break; - case DELETE: - mDatabase.delete(TEST_TABLE, "i = 1", null); - break; - } - } - // do a query. even though query uses a different database connection, - // it should still see the above changes to data because the above standalone - // insert/update/deletes are done in transactions automatically. - String sql = "select count(*) from " + TEST_TABLE; - SQLiteStatement stmt = mDatabase.compileStatement(sql); - final int expectedValue = (stmtType == DELETE) ? 4 : 5; - assertEquals(expectedValue, stmt.simpleQueryForLong()); - stmt.close(); - Cursor c = mDatabase.rawQuery(sql, null); - assertEquals(1, c.getCount()); - c.moveToFirst(); - assertEquals(expectedValue, c.getLong(0)); - c.close(); - - // do 5 more changes in a transaction but do a query before and after the commit - mDatabase.beginTransaction(); - for (int i = 10; i < 15; i++) { - values.put("i", i); - values.put("j", "i" + System.currentTimeMillis()); - mDatabase.insert(TEST_TABLE, null, values); - switch (stmtType) { - case UPDATE: - values.put("j", "u" + System.currentTimeMillis()); - mDatabase.update(TEST_TABLE, values, "i = " + i, null); - break; - case DELETE: - mDatabase.delete(TEST_TABLE, "i = 1", null); - break; - } - } - mDatabase.setTransactionSuccessful(); - // do a query before commit - should still have 5 rows - // this query should run in a different thread to force it to use a different database - // connection - Thread t = new Thread() { - @Override public void run() { - String sql = "select count(*) from " + TEST_TABLE; - SQLiteStatement stmt = getDb().compileStatement(sql); - assertEquals(expectedValue, stmt.simpleQueryForLong()); - stmt.close(); - Cursor c = getDb().rawQuery(sql, null); - assertEquals(1, c.getCount()); - c.moveToFirst(); - assertEquals(expectedValue, c.getLong(0)); - c.close(); - } - }; - t.start(); - // wait until the above thread is done - t.join(); - // commit and then query. should see changes from the transaction - mDatabase.endTransaction(); - stmt = mDatabase.compileStatement(sql); - final int expectedValue2 = (stmtType == DELETE) ? 9 : 10; - assertEquals(expectedValue2, stmt.simpleQueryForLong()); - stmt.close(); - c = mDatabase.rawQuery(sql, null); - assertEquals(1, c.getCount()); - c.moveToFirst(); - assertEquals(expectedValue2, c.getLong(0)); - c.close(); - } - private synchronized SQLiteDatabase getDb() { - return mDatabase; - } - - /** - * Test to ensure that readers are able to read the database data (old versions) - * EVEN WHEN the writer is in a transaction on the same database. - *

- * This test starts 1 Writer and 2 Readers and sets up connection pool for readers - * by calling the method {@link SQLiteDatabase#enableWriteAheadLogging()}. - *

- * Writer does the following in a tight loop - * 

-     *     begin transaction
-     *     insert into table_1
-     *     insert into table_2
-     *     commit
-     *
- *

- * As long a the writer is alive, Readers do the following in a tight loop at the same time - * 

-     *     Reader_K does "select count(*) from table_K"  where K = 1 or 2
-     *
- *

- * The test is run for TIME_TO_RUN_WAL_TEST_FOR sec. - *

- * The test is repeated for different connection-pool-sizes (1..3) - *

- * And at the end of of each test, the following statistics are printed - *

    - *
  • connection-pool-size
    • - *
  • number-of-transactions by writer
    • - *
  • number of reads by reader_K while the writer is IN or NOT-IN xaction
    • - *
- */ - @LargeTest - @Suppress // run this test only if you need to collect the numbers from this test - public void testConcurrencyEffectsOfConnPool() throws Exception { - // run the test with sqlite WAL enable - runConnectionPoolTest(true); - - // run the same test WITHOUT sqlite WAL enabled - runConnectionPoolTest(false); - } - - private void runConnectionPoolTest(boolean useWal) throws Exception { - int M = 3; - StringBuilder[] buff = new StringBuilder[M]; - for (int i = 0; i < M; i++) { - if (useWal) { - // set up connection pool - mDatabase.enableWriteAheadLogging(); - mDatabase.mConnectionPool.setMaxPoolSize(i + 1); - } else { - mDatabase.disableWriteAheadLogging(); - } - mDatabase.execSQL("CREATE TABLE t1 (i int, j int);"); - mDatabase.execSQL("CREATE TABLE t2 (i int, j int);"); - mDatabase.beginTransaction(); - for (int k = 0; k < 5; k++) { - mDatabase.execSQL("insert into t1 values(?,?);", new String[] {k+"", k+""}); - mDatabase.execSQL("insert into t2 values(?,?);", new String[] {k+"", k+""}); - } - mDatabase.setTransactionSuccessful(); - mDatabase.endTransaction(); - - // start a writer - Writer w = new Writer(mDatabase); - - // initialize an array of counters to be passed to the readers - Reader r1 = new Reader(mDatabase, "t1", w, 0); - Reader r2 = new Reader(mDatabase, "t2", w, 1); - w.start(); - r1.start(); - r2.start(); - - // wait for all threads to die - w.join(); - r1.join(); - r2.join(); - - // print the stats - int[][] counts = getCounts(); - buff[i] = new StringBuilder(); - buff[i].append("connpool-size = "); - buff[i].append(i + 1); - buff[i].append(", num xacts by writer = "); - buff[i].append(getNumXacts()); - buff[i].append(", num-reads-in-xact/NOT-in-xact by reader1 = "); - buff[i].append(counts[0][1] + "/" + counts[0][0]); - buff[i].append(", by reader2 = "); - buff[i].append(counts[1][1] + "/" + counts[1][0]); - - Log.i(TAG, "done testing for conn-pool-size of " + (i+1)); - - dbTeardown(); - dbSetUp(); - } - Log.i(TAG, "duration of test " + TIME_TO_RUN_WAL_TEST_FOR + " sec"); - for (int i = 0; i < M; i++) { - Log.i(TAG, buff[i].toString()); - } - } - - private boolean inXact = false; - private int numXacts; - private static final int TIME_TO_RUN_WAL_TEST_FOR = 15; // num sec this test should run - private int[][] counts = new int[2][2]; - - private synchronized boolean inXact() { - return inXact; - } - - private synchronized void setInXactFlag(boolean flag) { - inXact = flag; - } - - private synchronized void setCounts(int readerNum, int[] numReads) { - counts[readerNum][0] = numReads[0]; - counts[readerNum][1] = numReads[1]; - } - - private synchronized int[][] getCounts() { - return counts; - } - - private synchronized void setNumXacts(int num) { - numXacts = num; - } - - private synchronized int getNumXacts() { - return numXacts; - } - - private class Writer extends Thread { - private SQLiteDatabase db = null; - public Writer(SQLiteDatabase db) { - this.db = db; - } - @Override public void run() { - // in a loop, for N sec, do the following - // BEGIN transaction - // insert into table t1, t2 - // Commit - long now = System.currentTimeMillis(); - int k; - for (k = 0;(System.currentTimeMillis() - now) / 1000 < TIME_TO_RUN_WAL_TEST_FOR; k++) { - db.beginTransactionNonExclusive(); - setInXactFlag(true); - for (int i = 0; i < 10; i++) { - db.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""}); - db.execSQL("insert into t2 values(?,?);", new String[] {i+"", i+""}); - } - db.setTransactionSuccessful(); - setInXactFlag(false); - db.endTransaction(); - } - setNumXacts(k); - } - } - - private class Reader extends Thread { - private SQLiteDatabase db = null; - private String table = null; - private Writer w = null; - private int readerNum; - private int[] numReads = new int[2]; - public Reader(SQLiteDatabase db, String table, Writer w, int readerNum) { - this.db = db; - this.table = table; - this.w = w; - this.readerNum = readerNum; - } - @Override public void run() { - // while the write is alive, in a loop do the query on a table - while (w.isAlive()) { - for (int i = 0; i < 10; i++) { - DatabaseUtils.longForQuery(db, "select count(*) from " + this.table, null); - // update count of reads - numReads[inXact() ? 1 : 0] += 1; - } - } - setCounts(readerNum, numReads); - } - } - - public static class ClassToTestSqlCompilationAndCaching extends SQLiteProgram { - private ClassToTestSqlCompilationAndCaching(SQLiteDatabase db, String sql) { - super(db, sql); - } - public static ClassToTestSqlCompilationAndCaching create(SQLiteDatabase db, String sql) { - db.lock(); - try { - return new ClassToTestSqlCompilationAndCaching(db, sql); - } finally { - db.unlock(); - } - } - } - - @SmallTest - public void testLruCachingOfSqliteCompiledSqlObjs() { - createTableAndClearCache(); - // set cache size - int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; - mDatabase.setMaxSqlCacheSize(N); - - // do N+1 queries - and when the 0th entry is removed from LRU cache due to the - // insertion of (N+1)th entry, make sure 0th entry is closed - ArrayList stmtObjs = new ArrayList(); - ArrayList sqlStrings = new ArrayList(); - int stmt0 = 0; - for (int i = 0; i < N+1; i++) { - String s = "insert into test values(" + i + ",?);"; - sqlStrings.add(s); - ClassToTestSqlCompilationAndCaching c = - ClassToTestSqlCompilationAndCaching.create(mDatabase, s); - int n = c.getSqlStatementId(); - stmtObjs.add(i, n); - if (i == 0) { - // 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? 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); - assertTrue(stmtObjs.contains(compSql.nStatement)); - } - } - - @MediumTest - public void testDbCloseReleasingAllCachedSql() { - 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 statementDoNotClose = mDatabase.compileStatement(statement); - statementDoNotClose.bindLong(1, 1); - /* do not close statementDoNotClose object. - * That should leave it in SQLiteDatabase.mPrograms. - * mDatabase.close() in tearDown() should release it. - */ - } - - private void createTableAndClearCache() { - mDatabase.disableWriteAheadLogging(); - mDatabase.execSQL("DROP TABLE IF EXISTS " + TEST_TABLE); - mDatabase.execSQL("CREATE TABLE " + TEST_TABLE + " (i int, j int);"); - mDatabase.enableWriteAheadLogging(); - mDatabase.lock(); - // flush the above statement from cache and close all the pending statements to be released - mDatabase.deallocCachedSqlStatements(); - mDatabase.closePendingStatements(); - mDatabase.unlock(); - assertEquals(0, mDatabase.getQueuedUpStmtList().size()); - } - - /** - * test to make sure the statement finalizations are not done right away but - * piggy-backed onto the next sql statement execution on the same database. - */ - @SmallTest - public void testStatementClose() { - createTableAndClearCache(); - // fill up statement cache in mDatabase - int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; - mDatabase.setMaxSqlCacheSize(N); - SQLiteStatement stmt; - int stmt0Id = 0; - for (int i = 0; i < N; i ++) { - ClassToTestSqlCompilationAndCaching c = - ClassToTestSqlCompilationAndCaching.create(mDatabase, - "insert into test values(" + i + ", ?);"); - // keep track of 0th entry - if (i == 0) { - stmt0Id = c.getSqlStatementId(); - } - c.close(); - } - - // add one more to the cache - and the above 'stmt0Id' should fall out of cache - ClassToTestSqlCompilationAndCaching stmt1 = - ClassToTestSqlCompilationAndCaching.create(mDatabase, - "insert into test values(100, ?);"); - stmt1.close(); - - // the above close() should have queuedUp the statement for finalization - ArrayList statementIds = mDatabase.getQueuedUpStmtList(); - assertTrue(statementIds.contains(stmt0Id)); - - // execute something to see if this statement gets finalized - mDatabase.execSQL("delete from test where i = 10;"); - statementIds = mDatabase.getQueuedUpStmtList(); - assertFalse(statementIds.contains(stmt0Id)); - } - - /** - * same as above - except that the statement to be finalized is from Thread # 1. - * and it is eventually finalized in Thread # 2 when it executes a SQL statement. - * @throws InterruptedException - */ - @LargeTest - public void testStatementCloseDiffThread() throws InterruptedException { - createTableAndClearCache(); - final int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; - mDatabase.setMaxSqlCacheSize(N); - // fill up statement cache in mDatabase in a thread - Thread t1 = new Thread() { - @Override public void run() { - SQLiteStatement stmt; - for (int i = 0; i < N; i++) { - ClassToTestSqlCompilationAndCaching c = - ClassToTestSqlCompilationAndCaching.create(getDb(), - "insert into test values(" + i + ", ?);"); - // keep track of 0th entry - if (i == 0) { - stmt0Id = c.getSqlStatementId(); - } - c.close(); - } - } - }; - t1.start(); - // wait for the thread to finish - t1.join(); - // mDatabase shouldn't have any statements to be released - assertEquals(0, mDatabase.getQueuedUpStmtList().size()); - - // add one more to the cache - and the above 'stmt0Id' should fall out of cache - // just for the heck of it, do it in a separate thread - Thread t2 = new Thread() { - @Override public void run() { - ClassToTestSqlCompilationAndCaching stmt1 = - ClassToTestSqlCompilationAndCaching.create(getDb(), - "insert into test values(100, ?);"); - stmt1.bindLong(1, 1); - stmt1.close(); - } - }; - t2.start(); - t2.join(); - - // close() in the above thread should have queuedUp the stmt0Id for finalization - ArrayList statementIds = getDb().getQueuedUpStmtList(); - assertTrue(statementIds.contains(getStmt0Id())); - assertEquals(1, statementIds.size()); - - // execute something to see if this statement gets finalized - // again do it in a separate thread - Thread t3 = new Thread() { - @Override public void run() { - getDb().execSQL("delete from test where i = 10;"); - } - }; - t3.start(); - t3.join(); - - // is the statement finalized? - statementIds = getDb().getQueuedUpStmtList(); - assertFalse(statementIds.contains(getStmt0Id())); - } - - private volatile int stmt0Id = 0; - private synchronized int getStmt0Id() { - return this.stmt0Id; - } - - /** - * same as above - except that the queue of statements to be finalized are finalized - * by database close() operation. - */ - @LargeTest - public void testStatementCloseByDbClose() throws InterruptedException { - createTableAndClearCache(); - // fill up statement cache in mDatabase in a thread - Thread t1 = new Thread() { - @Override public void run() { - int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; - getDb().setMaxSqlCacheSize(N); - SQLiteStatement stmt; - for (int i = 0; i < N; i ++) { - ClassToTestSqlCompilationAndCaching c = - ClassToTestSqlCompilationAndCaching.create(getDb(), - "insert into test values(" + i + ", ?);"); - // keep track of 0th entry - if (i == 0) { - stmt0Id = c.getSqlStatementId(); - } - c.close(); - } - } - }; - t1.start(); - // wait for the thread to finish - t1.join(); - - // add one more to the cache - and the above 'stmt0Id' should fall out of cache - // just for the heck of it, do it in a separate thread - Thread t2 = new Thread() { - @Override public void run() { - ClassToTestSqlCompilationAndCaching stmt1 = - ClassToTestSqlCompilationAndCaching.create(getDb(), - "insert into test values(100, ?);"); - stmt1.bindLong(1, 1); - stmt1.close(); - } - }; - t2.start(); - t2.join(); - - // close() in the above thread should have queuedUp the statement for finalization - ArrayList statementIds = getDb().getQueuedUpStmtList(); - assertTrue(getStmt0Id() > 0); - assertTrue(statementIds.contains(stmt0Id)); - assertEquals(1, statementIds.size()); - - // close the database. everything from mClosedStatementIds in mDatabase - // should be finalized and cleared from the list - // again do it in a separate thread - Thread t3 = new Thread() { - @Override public void run() { - getDb().close(); - } - }; - t3.start(); - t3.join(); - - // check mClosedStatementIds in mDatabase. it should be empty - statementIds = getDb().getQueuedUpStmtList(); - assertEquals(0, statementIds.size()); - } - - /** - * This test tests usage execSQL() to begin transaction works in the following way - * Thread #1 does - * execSQL("begin transaction"); - * insert() - * Thread # 2 - * query() - * Thread#1 ("end transaction") - * Thread # 2 query will execute - because java layer will not have locked the SQLiteDatabase - * object and sqlite will consider this query to be part of the transaction. - * - * but if thread # 1 uses beginTransaction() instead of execSQL() to start transaction, - * then Thread # 2's query will have been blocked by java layer - * until Thread#1 ends transaction. - * - * @throws InterruptedException - */ - @SmallTest - public void testExecSqlToStartAndEndTransaction() throws InterruptedException { - runExecSqlToStartAndEndTransaction("END"); - // same as above, instead now do "COMMIT" or "ROLLBACK" instead of "END" transaction - runExecSqlToStartAndEndTransaction("COMMIT"); - runExecSqlToStartAndEndTransaction("ROLLBACK"); - } - private void runExecSqlToStartAndEndTransaction(String str) throws InterruptedException { - createTableAndClearCache(); - // disable WAL just so queries and updates use the same database connection - mDatabase.disableWriteAheadLogging(); - mDatabase.execSQL("BEGIN transaction"); - // even though mDatabase.beginTransaction() is not called to start transaction, - // mDatabase connection should now be in transaction as a result of - // mDatabase.execSQL("BEGIN transaction") - // but mDatabase.mLock should not be held by any thread - assertTrue(mDatabase.inTransaction()); - assertFalse(mDatabase.isDbLockedByCurrentThread()); - assertFalse(mDatabase.isDbLockedByOtherThreads()); - assertTrue(mDatabase.amIInTransaction()); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - assertTrue(mDatabase.inTransaction()); - assertFalse(mDatabase.isDbLockedByCurrentThread()); - assertFalse(mDatabase.isDbLockedByOtherThreads()); - assertTrue(mDatabase.amIInTransaction()); - Thread t = new Thread() { - @Override public void run() { - assertTrue(mDatabase.amIInTransaction()); - assertEquals(999, DatabaseUtils.longForQuery(getDb(), - "select j from " + TEST_TABLE + " WHERE i = 10", null)); - assertTrue(getDb().inTransaction()); - assertFalse(getDb().isDbLockedByCurrentThread()); - assertFalse(getDb().isDbLockedByOtherThreads()); - assertTrue(mDatabase.amIInTransaction()); - } - }; - t.start(); - t.join(); - assertTrue(mDatabase.amIInTransaction()); - assertTrue(mDatabase.inTransaction()); - assertFalse(mDatabase.isDbLockedByCurrentThread()); - assertFalse(mDatabase.isDbLockedByOtherThreads()); - mDatabase.execSQL(str); - assertFalse(mDatabase.amIInTransaction()); - assertFalse(mDatabase.inTransaction()); - assertFalse(mDatabase.isDbLockedByCurrentThread()); - assertFalse(mDatabase.isDbLockedByOtherThreads()); - } - - /** - * test the following - * http://b/issue?id=2871037 - * Cursor cursor = db.query(...); - * // with WAL enabled, the above uses a pooled database connection - * db.beginTransaction() - * try { - * db.insert(......); - * cursor.requery(); - * // since the cursor uses pooled database connection, the above requery - * // will not return the results that were inserted above since the insert is - * // done using main database connection AND the transaction is not committed yet. - * // fix is to make the above cursor use the main database connection - and NOT - * // the pooled database connection - * db.setTransactionSuccessful() - * } finally { - * db.endTransaction() - * } - * - * @throws InterruptedException - */ - @SmallTest - public void testTransactionAndWalInterplay1() throws InterruptedException { - createTableAndClearCache(); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - String sql = "select * from " + TEST_TABLE; - Cursor c = mDatabase.rawQuery(sql, null); - // should have 1 row in the table - assertEquals(1, c.getCount()); - mDatabase.beginTransactionNonExclusive(); - try { - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); - assertEquals(2, DatabaseUtils.longForQuery(mDatabase, - "select count(*) from " + TEST_TABLE, null)); - // requery on the previously opened cursor - // cursor should now use the main database connection and see 2 rows - c.requery(); - assertEquals(2, c.getCount()); - mDatabase.setTransactionSuccessful(); - } finally { - mDatabase.endTransaction(); - } - c.close(); - - // do the same test but now do the requery in a separate thread. - createTableAndClearCache(); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - final Cursor c1 = mDatabase.rawQuery("select count(*) from " + TEST_TABLE, null); - // should have 1 row in the table - assertEquals(1, c1.getCount()); - mDatabase.beginTransactionNonExclusive(); - try { - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); - assertEquals(2, DatabaseUtils.longForQuery(mDatabase, - "select count(*) from " + TEST_TABLE, null)); - // query in a different thread. that causes the cursor to use a pooled connection - // and since this thread hasn't committed its changes, the cursor should still see only - // 1 row - Thread t = new Thread() { - @Override public void run() { - c1.requery(); - assertEquals(1, c1.getCount()); - } - }; - t.start(); - t.join(); - // should be 2 rows now - including the the row inserted above - mDatabase.setTransactionSuccessful(); - } finally { - mDatabase.endTransaction(); - } - c1.close(); - } - - /** - * This test is same as {@link #testTransactionAndWalInterplay1()} except the following: - * instead of mDatabase.beginTransactionNonExclusive(), use execSQL("BEGIN transaction") - * and instead of mDatabase.endTransaction(), use execSQL("END"); - */ - @SmallTest - public void testTransactionAndWalInterplay2() throws InterruptedException { - createTableAndClearCache(); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - String sql = "select * from " + TEST_TABLE; - Cursor c = mDatabase.rawQuery(sql, null); - // should have 1 row in the table - assertEquals(1, c.getCount()); - mDatabase.execSQL("BEGIN transaction"); - try { - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); - assertEquals(2, DatabaseUtils.longForQuery(mDatabase, - "select count(*) from " + TEST_TABLE, null)); - // requery on the previously opened cursor - // cursor should now use the main database connection and see 2 rows - c.requery(); - assertEquals(2, c.getCount()); - } finally { - mDatabase.execSQL("commit;"); - } - c.close(); - - // do the same test but now do the requery in a separate thread. - createTableAndClearCache(); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - final Cursor c1 = mDatabase.rawQuery("select count(*) from " + TEST_TABLE, null); - // should have 1 row in the table - assertEquals(1, c1.getCount()); - mDatabase.execSQL("BEGIN transaction"); - try { - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); - assertEquals(2, DatabaseUtils.longForQuery(mDatabase, - "select count(*) from " + TEST_TABLE, null)); - // query in a different thread. but since the transaction is started using - // execSQ() instead of beginTransaction(), cursor's query is considered part of - // the same transaction - and hence it should see the above inserted row - Thread t = new Thread() { - @Override public void run() { - c1.requery(); - assertEquals(1, c1.getCount()); - } - }; - t.start(); - t.join(); - // should be 2 rows now - including the the row inserted above - } finally { - mDatabase.execSQL("commit"); - } - c1.close(); - } - - /** - * This test is same as {@link #testTransactionAndWalInterplay2()} except the following: - * instead of committing the data, do rollback and make sure the data seen by the query - * within the transaction is now gone. - */ - @SmallTest - public void testTransactionAndWalInterplay3() { - createTableAndClearCache(); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - String sql = "select * from " + TEST_TABLE; - Cursor c = mDatabase.rawQuery(sql, null); - // should have 1 row in the table - assertEquals(1, c.getCount()); - mDatabase.execSQL("BEGIN transaction"); - try { - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); - assertEquals(2, DatabaseUtils.longForQuery(mDatabase, - "select count(*) from " + TEST_TABLE, null)); - // requery on the previously opened cursor - // cursor should now use the main database connection and see 2 rows - c.requery(); - assertEquals(2, c.getCount()); - } finally { - // rollback the change - mDatabase.execSQL("rollback;"); - } - // since the change is rolled back, do the same query again and should now find only 1 row - c.requery(); - assertEquals(1, c.getCount()); - assertEquals(1, DatabaseUtils.longForQuery(mDatabase, - "select count(*) from " + TEST_TABLE, null)); - c.close(); - } - - @SmallTest - public void testAttachDb() { - String newDb = "/sdcard/mydata.db"; - File f = new File(newDb); - if (f.exists()) { - f.delete(); - } - assertFalse(f.exists()); - SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(newDb, null); - db.execSQL("create table test1 (i int);"); - db.execSQL("insert into test1 values(1);"); - db.execSQL("insert into test1 values(11);"); - Cursor c = null; - try { - c = db.rawQuery("select * from test1", null); - int count = c.getCount(); - Log.i(TAG, "count: " + count); - assertEquals(2, count); - } finally { - c.close(); - db.close(); - c = null; - } - - mDatabase.execSQL("attach database ? as newDb" , new String[]{newDb}); - Cursor c1 = null; - try { - c1 = mDatabase.rawQuery("select * from newDb.test1", null); - assertEquals(2, c1.getCount()); - } catch (Exception e) { - fail("unexpected exception: " + e.getMessage()); - } finally { - if (c1 != null) { - c1.close(); - } - } - List> dbs = mDatabase.getAttachedDbs(); - for (Pair p: dbs) { - Log.i(TAG, "attached dbs: " + p.first + " : " + p.second); - } - assertEquals(2, dbs.size()); - } - - /** - * http://b/issue?id=2943028 - * SQLiteOpenHelper maintains a Singleton even if it is in bad state. - */ - @SmallTest - public void testCloseAndReopen() { - mDatabase.close(); - TestOpenHelper helper = new TestOpenHelper(getContext(), DB_NAME, null, - CURRENT_DATABASE_VERSION, new DefaultDatabaseErrorHandler()); - mDatabase = helper.getWritableDatabase(); - createTableAndClearCache(); - mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); - Cursor c = mDatabase.query(TEST_TABLE, new String[]{"i", "j"}, null, null, null, null, null); - assertEquals(1, c.getCount()); - c.close(); - mDatabase.close(); - assertFalse(mDatabase.isOpen()); - mDatabase = helper.getReadableDatabase(); - assertTrue(mDatabase.isOpen()); - c = mDatabase.query(TEST_TABLE, new String[]{"i", "j"}, null, null, null, null, null); - assertEquals(1, c.getCount()); - c.close(); - } - private class TestOpenHelper extends SQLiteOpenHelper { - public TestOpenHelper(Context context, String name, CursorFactory factory, int version, - DatabaseErrorHandler errorHandler) { - super(context, name, factory, version, errorHandler); - } - @Override public void onCreate(SQLiteDatabase db) {} - @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {} - } -} diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java deleted file mode 100644 index 955336a..0000000 --- a/core/tests/coretests/src/android/database/sqlite/SQLiteStatementTest.java +++ /dev/null @@ -1,213 +0,0 @@ -/* - * Copyright (C) 2006 The Android Open Source Project - * - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package android.database.sqlite; - -import android.content.Context; -import android.test.AndroidTestCase; -import android.test.suitebuilder.annotation.LargeTest; -import android.test.suitebuilder.annotation.SmallTest; - -import java.io.File; -import java.util.Random; -import java.util.concurrent.locks.ReentrantLock; - -public class SQLiteStatementTest extends AndroidTestCase { - private SQLiteDatabase mDatabase; - private File mDatabaseFile; - - @Override - protected void setUp() throws Exception { - super.setUp(); - - File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); - mDatabaseFile = new File(dbDir, "database_test.db"); - if (mDatabaseFile.exists()) { - mDatabaseFile.delete(); - } - mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); - assertNotNull(mDatabase); - } - - @Override - protected void tearDown() throws Exception { - mDatabase.close(); - mDatabaseFile.delete(); - super.tearDown(); - } - - /** - * Start 2 threads to repeatedly execute the above SQL statement. - * Even though 2 threads are executing the same SQL, they each should get their own copy of - * prepared SQL statement id and there SHOULD NOT be an error from sqlite or android. - * @throws InterruptedException thrown if the test threads started by this test are interrupted - */ - @LargeTest - public void testUseOfSameSqlStatementBy2Threads() throws InterruptedException { - mDatabase.execSQL("CREATE TABLE test_pstmt (i INTEGER PRIMARY KEY, j text);"); - final String stmt = "SELECT * FROM test_pstmt WHERE i = ?"; - class RunStmtThread extends Thread { - @Override public void run() { - // do it enough times to make sure there are no corner cases going untested - for (int i = 0; i < 1000; i++) { - SQLiteStatement s1 = mDatabase.compileStatement(stmt); - s1.bindLong(1, i); - s1.execute(); - s1.close(); - } - } - } - RunStmtThread t1 = new RunStmtThread(); - t1.start(); - RunStmtThread t2 = new RunStmtThread(); - t2.start(); - while (t1.isAlive() || t2.isAlive()) { - Thread.sleep(10); - } - } - - /** - * A simple test: start 2 threads to repeatedly execute the same {@link SQLiteStatement}. - * The 2 threads take turns to use the {@link SQLiteStatement}; i.e., it is NOT in use - * by both the threads at the same time. - * - * @throws InterruptedException thrown if the test threads started by this test are interrupted - */ - @LargeTest - public void testUseOfSameSqliteStatementBy2Threads() throws InterruptedException { - mDatabase.execSQL("CREATE TABLE test_pstmt (i INTEGER PRIMARY KEY, j text);"); - final String stmt = "SELECT * FROM test_pstmt WHERE i = ?"; - final SQLiteStatement s1 = mDatabase.compileStatement(stmt); - class RunStmtThread extends Thread { - @Override public void run() { - // do it enough times to make sure there are no corner cases going untested - for (int i = 0; i < 1000; i++) { - lock(); - try { - s1.bindLong(1, i); - s1.execute(); - } finally { - unlock(); - } - Thread.yield(); - } - } - } - RunStmtThread t1 = new RunStmtThread(); - t1.start(); - RunStmtThread t2 = new RunStmtThread(); - t2.start(); - while (t1.isAlive() || t2.isAlive()) { - Thread.sleep(10); - } - } - /** Synchronize on this when accessing the SqliteStatemet in the above */ - private final ReentrantLock mLock = new ReentrantLock(true); - private void lock() { - mLock.lock(); - } - private void unlock() { - mLock.unlock(); - } - - /** - * Tests the following: a {@link SQLiteStatement} object should not refer to a - * pre-compiled SQL statement id except in during the period of binding the arguments - * and executing the SQL statement. - */ - @LargeTest - public void testReferenceToPrecompiledStatementId() { - mDatabase.execSQL("create table t (i int, j text);"); - verifyReferenceToPrecompiledStatementId(false); - verifyReferenceToPrecompiledStatementId(true); - - // a small stress test to make sure there are no side effects of - // the acquire & release of pre-compiled statement id by SQLiteStatement object. - for (int i = 0; i < 100; i++) { - verifyReferenceToPrecompiledStatementId(false); - verifyReferenceToPrecompiledStatementId(true); - } - } - - @SuppressWarnings("deprecation") - private void verifyReferenceToPrecompiledStatementId(boolean wal) { - if (wal) { - mDatabase.enableWriteAheadLogging(); - } else { - mDatabase.disableWriteAheadLogging(); - } - // test with INSERT statement - doesn't use connection pool, if WAL is set - SQLiteStatement stmt = mDatabase.compileStatement("insert into t values(?,?);"); - assertEquals(mDatabase.mNativeHandle, stmt.nHandle); - assertEquals(mDatabase, stmt.mDatabase); - // sql statement should not be compiled yet - assertEquals(0, stmt.nStatement); - assertEquals(0, stmt.getSqlStatementId()); - int colValue = new Random().nextInt(); - stmt.bindLong(1, colValue); - // 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); - // 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); - assertEquals(0, stmt.getSqlStatementId()); - 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 be the same - assertEquals(mDatabase, stmt.mDatabase); - - // test with a SELECT statement - uses connection pool if WAL is set - stmt = mDatabase.compileStatement("select i from t where j=?;"); - // sql statement should not be compiled yet - assertEquals(0, stmt.nStatement); - assertEquals(0, stmt.getSqlStatementId()); - assertEquals(mDatabase.mNativeHandle, stmt.nHandle); - assertEquals(mDatabase, stmt.mDatabase); - stmt.bindString(1, "blah" + colValue); - // 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()); - 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(mDatabase.mNativeHandle, stmt.nHandle); - assertEquals(mDatabase, stmt.mDatabase); - } -} diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteUnfinalizedExceptionTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteUnfinalizedExceptionTest.java deleted file mode 100644 index cd2005d..0000000 --- a/core/tests/coretests/src/android/database/sqlite/SQLiteUnfinalizedExceptionTest.java +++ /dev/null @@ -1,81 +0,0 @@ -/* - * Copyright (C) 2010 The Android Open Source Project - * - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package android.database.sqlite; - -import android.content.Context; -import android.database.sqlite.SQLiteDatabaseTest.ClassToTestSqlCompilationAndCaching; -import android.test.AndroidTestCase; -import android.test.suitebuilder.annotation.SmallTest; - -import java.io.File; - -public class SQLiteUnfinalizedExceptionTest extends AndroidTestCase { - private SQLiteDatabase mDatabase; - private File mDatabaseFile; - private static final String TABLE_NAME = "testCursor"; - @Override - protected void setUp() throws Exception { - super.setUp(); - - File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); - mDatabaseFile = new File(dbDir, "UnfinalizedExceptionTest.db"); - if (mDatabaseFile.exists()) { - mDatabaseFile.delete(); - } - mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); - assertNotNull(mDatabase); - } - - @Override - protected void tearDown() throws Exception { - mDatabase.close(); - mDatabaseFile.delete(); - super.tearDown(); - } - - @SmallTest - public void testUnfinalizedExceptionNotExcpected() { - mDatabase.execSQL("CREATE TABLE " + TABLE_NAME + " (i int, j int);"); - // the above statement should be in SQLiteDatabase.mPrograms - // and should automatically be finalized when database is closed - mDatabase.lock(); - try { - mDatabase.closeDatabase(); - } finally { - mDatabase.unlock(); - } - } - - @SmallTest - public void testUnfinalizedException() { - mDatabase.execSQL("CREATE TABLE " + TABLE_NAME + " (i int, j int);"); - mDatabase.lock(); - mDatabase.closePendingStatements(); // clears the above from finalizer queue in mdatabase - mDatabase.unlock(); - ClassToTestSqlCompilationAndCaching.create(mDatabase, "select * from " + TABLE_NAME); - // since the above is NOT closed, closing database should fail - mDatabase.lock(); - try { - mDatabase.closeDatabase(); - fail("exception expected"); - } catch (SQLiteUnfinalizedObjectsException e) { - // expected - } finally { - mDatabase.unlock(); - } - } -} -- cgit v1.1