/*
* 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);
}
}