/*
* 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.
*<p>
* This test starts 1 Writer and 2 Readers and sets up connection pool for readers
* by calling the method {@link SQLiteDatabase#enableWriteAheadLogging()}.
* <p>
* Writer does the following in a tight loop
* <pre>
* begin transaction
* insert into table_1
* insert into table_2
* commit
* </pre>
* <p>
* As long a the writer is alive, Readers do the following in a tight loop at the same time
* <pre>
* Reader_K does "select count(*) from table_K" where K = 1 or 2
* </pre>
* <p>
* The test is run for TIME_TO_RUN_WAL_TEST_FOR sec.
* <p>
* The test is repeated for different connection-pool-sizes (1..3)
* <p>
* And at the end of of each test, the following statistics are printed
* <ul>
* <li>connection-pool-size</li>
* <li>number-of-transactions by writer</li>
* <li>number of reads by reader_K while the writer is IN or NOT-IN xaction</li>
* </ul>
*/
@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<Integer> stmtObjs = new ArrayList<Integer>();
ArrayList<String> sqlStrings = new ArrayList<String>();
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<Integer> 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<Integer> 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<Integer> 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<Pair<String, String>> dbs = mDatabase.getAttachedDbs();
for (Pair<String, String> 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) {}
}
}