| /* |
| * Copyright (C) 2009 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.cts; |
| |
| import java.io.File; |
| import java.io.IOException; |
| import java.util.ArrayList; |
| import java.util.Locale; |
| |
| import android.content.ContentValues; |
| import android.content.Context; |
| import android.database.Cursor; |
| import android.database.DatabaseUtils; |
| import android.database.SQLException; |
| import android.database.sqlite.SQLiteCursor; |
| import android.database.sqlite.SQLiteCursorDriver; |
| import android.database.sqlite.SQLiteDatabase; |
| import android.database.sqlite.SQLiteDatabase.CursorFactory; |
| import android.database.sqlite.SQLiteException; |
| import android.database.sqlite.SQLiteQuery; |
| import android.database.sqlite.SQLiteStatement; |
| import android.database.sqlite.SQLiteTransactionListener; |
| import android.test.AndroidTestCase; |
| import android.test.MoreAsserts; |
| import android.test.suitebuilder.annotation.LargeTest; |
| import android.test.suitebuilder.annotation.SmallTest; |
| |
| public class SQLiteDatabaseTest extends AndroidTestCase { |
| private SQLiteDatabase mDatabase; |
| private File mDatabaseFile; |
| private String mDatabaseFilePath; |
| private String mDatabaseDir; |
| |
| private boolean mTransactionListenerOnBeginCalled; |
| private boolean mTransactionListenerOnCommitCalled; |
| private boolean mTransactionListenerOnRollbackCalled; |
| |
| private static final String DATABASE_FILE_NAME = "database_test.db"; |
| private static final String TABLE_NAME = "test"; |
| private static final int COLUMN_ID_INDEX = 0; |
| private static final int COLUMN_NAME_INDEX = 1; |
| private static final int COLUMN_AGE_INDEX = 2; |
| private static final int COLUMN_ADDR_INDEX = 3; |
| private static final String[] TEST_PROJECTION = new String[] { |
| "_id", // 0 |
| "name", // 1 |
| "age", // 2 |
| "address" // 3 |
| }; |
| |
| @Override |
| protected void setUp() throws Exception { |
| super.setUp(); |
| |
| getContext().deleteDatabase(DATABASE_FILE_NAME); |
| mDatabaseFilePath = getContext().getDatabasePath(DATABASE_FILE_NAME).getPath(); |
| mDatabaseFile = getContext().getDatabasePath(DATABASE_FILE_NAME); |
| mDatabaseDir = mDatabaseFile.getParent(); |
| mDatabaseFile.getParentFile().mkdirs(); // directory may not exist |
| mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null); |
| assertNotNull(mDatabase); |
| |
| mTransactionListenerOnBeginCalled = false; |
| mTransactionListenerOnCommitCalled = false; |
| mTransactionListenerOnRollbackCalled = false; |
| } |
| |
| @Override |
| protected void tearDown() throws Exception { |
| mDatabase.close(); |
| mDatabaseFile.delete(); |
| super.tearDown(); |
| } |
| |
| public void testOpenDatabase() { |
| CursorFactory factory = new CursorFactory() { |
| public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, |
| String editTable, SQLiteQuery query) { |
| return new MockSQLiteCursor(db, masterQuery, editTable, query); |
| } |
| }; |
| |
| SQLiteDatabase db = SQLiteDatabase.openDatabase(mDatabaseFilePath, |
| factory, SQLiteDatabase.CREATE_IF_NECESSARY); |
| assertNotNull(db); |
| db.close(); |
| |
| File dbFile = new File(mDatabaseDir, "database_test12345678.db"); |
| dbFile.delete(); |
| assertFalse(dbFile.exists()); |
| db = SQLiteDatabase.openOrCreateDatabase(dbFile.getPath(), factory); |
| assertNotNull(db); |
| db.close(); |
| dbFile.delete(); |
| |
| dbFile = new File(mDatabaseDir, DATABASE_FILE_NAME); |
| db = SQLiteDatabase.openOrCreateDatabase(dbFile, factory); |
| assertNotNull(db); |
| db.close(); |
| dbFile.delete(); |
| |
| db = SQLiteDatabase.create(factory); |
| assertNotNull(db); |
| db.close(); |
| } |
| |
| public void testDeleteDatabase() throws IOException { |
| File dbFile = new File(mDatabaseDir, "database_test12345678.db"); |
| File journalFile = new File(dbFile.getPath() + "-journal"); |
| File shmFile = new File(dbFile.getPath() + "-shm"); |
| File walFile = new File(dbFile.getPath() + "-wal"); |
| File mjFile1 = new File(dbFile.getPath() + "-mj00000000"); |
| File mjFile2 = new File(dbFile.getPath() + "-mj00000001"); |
| File innocentFile = new File(dbFile.getPath() + "-innocent"); |
| |
| dbFile.createNewFile(); |
| journalFile.createNewFile(); |
| shmFile.createNewFile(); |
| walFile.createNewFile(); |
| mjFile1.createNewFile(); |
| mjFile2.createNewFile(); |
| innocentFile.createNewFile(); |
| |
| boolean deleted = SQLiteDatabase.deleteDatabase(dbFile); |
| assertTrue(deleted); |
| |
| assertFalse(dbFile.exists()); |
| assertFalse(journalFile.exists()); |
| assertFalse(shmFile.exists()); |
| assertFalse(walFile.exists()); |
| assertFalse(mjFile1.exists()); |
| assertFalse(mjFile2.exists()); |
| assertTrue(innocentFile.exists()); |
| |
| innocentFile.delete(); |
| |
| boolean deletedAgain = SQLiteDatabase.deleteDatabase(dbFile); |
| assertFalse(deletedAgain); |
| } |
| |
| private class MockSQLiteCursor extends SQLiteCursor { |
| public MockSQLiteCursor(SQLiteDatabase db, SQLiteCursorDriver driver, |
| String editTable, SQLiteQuery query) { |
| super(db, driver, editTable, query); |
| } |
| } |
| |
| public void testTransaction() { |
| mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); |
| mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); |
| |
| // test execSQL without any explicit transactions. |
| setNum(1); |
| assertNum(1); |
| |
| // Test a single-level transaction. |
| setNum(0); |
| assertFalse(mDatabase.inTransaction()); |
| mDatabase.beginTransaction(); |
| assertTrue(mDatabase.inTransaction()); |
| setNum(1); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| assertFalse(mDatabase.inTransaction()); |
| assertNum(1); |
| assertFalse(mDatabase.isDbLockedByCurrentThread()); |
| assertFalse(mDatabase.isDbLockedByOtherThreads()); |
| |
| // Test a rolled-back transaction. |
| setNum(0); |
| assertFalse(mDatabase.inTransaction()); |
| mDatabase.beginTransaction(); |
| setNum(1); |
| assertTrue(mDatabase.inTransaction()); |
| mDatabase.endTransaction(); |
| assertFalse(mDatabase.inTransaction()); |
| assertNum(0); |
| assertFalse(mDatabase.isDbLockedByCurrentThread()); |
| assertFalse(mDatabase.isDbLockedByOtherThreads()); |
| |
| // it should throw IllegalStateException if we end a non-existent transaction. |
| assertThrowsIllegalState(new Runnable() { |
| public void run() { |
| mDatabase.endTransaction(); |
| } |
| }); |
| |
| // it should throw IllegalStateException if a set a non-existent transaction as clean. |
| assertThrowsIllegalState(new Runnable() { |
| public void run() { |
| mDatabase.setTransactionSuccessful(); |
| } |
| }); |
| |
| mDatabase.beginTransaction(); |
| mDatabase.setTransactionSuccessful(); |
| // it should throw IllegalStateException if we mark a transaction as clean twice. |
| assertThrowsIllegalState(new Runnable() { |
| public void run() { |
| mDatabase.setTransactionSuccessful(); |
| } |
| }); |
| // it should throw IllegalStateException if we begin a transaction after marking the |
| // parent as clean. |
| assertThrowsIllegalState(new Runnable() { |
| public void run() { |
| mDatabase.beginTransaction(); |
| } |
| }); |
| mDatabase.endTransaction(); |
| assertFalse(mDatabase.isDbLockedByCurrentThread()); |
| assertFalse(mDatabase.isDbLockedByOtherThreads()); |
| |
| assertFalse(mDatabase.inTransaction()); |
| // Test a two-level transaction. |
| setNum(0); |
| mDatabase.beginTransaction(); |
| assertTrue(mDatabase.inTransaction()); |
| mDatabase.beginTransaction(); |
| assertTrue(mDatabase.inTransaction()); |
| setNum(1); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| assertTrue(mDatabase.inTransaction()); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| assertFalse(mDatabase.inTransaction()); |
| assertNum(1); |
| assertFalse(mDatabase.isDbLockedByCurrentThread()); |
| assertFalse(mDatabase.isDbLockedByOtherThreads()); |
| |
| // Test rolling back an inner transaction. |
| setNum(0); |
| mDatabase.beginTransaction(); |
| mDatabase.beginTransaction(); |
| setNum(1); |
| mDatabase.endTransaction(); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| assertNum(0); |
| assertFalse(mDatabase.isDbLockedByCurrentThread()); |
| assertFalse(mDatabase.isDbLockedByOtherThreads()); |
| |
| // Test rolling back an outer transaction. |
| setNum(0); |
| mDatabase.beginTransaction(); |
| mDatabase.beginTransaction(); |
| setNum(1); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| mDatabase.endTransaction(); |
| assertNum(0); |
| assertFalse(mDatabase.isDbLockedByCurrentThread()); |
| assertFalse(mDatabase.isDbLockedByOtherThreads()); |
| } |
| |
| private void setNum(int num) { |
| mDatabase.execSQL("UPDATE test SET num = " + num); |
| } |
| |
| private void assertNum(int num) { |
| assertEquals(num, DatabaseUtils.longForQuery(mDatabase, |
| "SELECT num FROM test", null)); |
| } |
| |
| private void assertThrowsIllegalState(Runnable r) { |
| try { |
| r.run(); |
| fail("did not throw expected IllegalStateException"); |
| } catch (IllegalStateException e) { |
| } |
| } |
| |
| public void testAccessMaximumSize() { |
| long curMaximumSize = mDatabase.getMaximumSize(); |
| |
| // the new maximum size is less than the current size. |
| mDatabase.setMaximumSize(curMaximumSize - 1); |
| assertEquals(curMaximumSize, mDatabase.getMaximumSize()); |
| |
| // the new maximum size is more than the current size. |
| mDatabase.setMaximumSize(curMaximumSize + 1); |
| assertEquals(curMaximumSize + mDatabase.getPageSize(), mDatabase.getMaximumSize()); |
| assertTrue(mDatabase.getMaximumSize() > curMaximumSize); |
| } |
| |
| public void testAccessPageSize() { |
| File databaseFile = new File(mDatabaseDir, "database.db"); |
| if (databaseFile.exists()) { |
| databaseFile.delete(); |
| } |
| SQLiteDatabase database = null; |
| try { |
| database = SQLiteDatabase.openOrCreateDatabase(databaseFile.getPath(), null); |
| |
| long initialValue = database.getPageSize(); |
| // check that this does not throw an exception |
| // setting a different page size may not be supported after the DB has been created |
| database.setPageSize(initialValue); |
| assertEquals(initialValue, database.getPageSize()); |
| |
| } finally { |
| if (database != null) { |
| database.close(); |
| databaseFile.delete(); |
| } |
| } |
| } |
| |
| public void testCompileStatement() { |
| mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " |
| + "name TEXT, age INTEGER, address TEXT);"); |
| |
| String name = "Mike"; |
| int age = 21; |
| String address = "LA"; |
| |
| // at the beginning, there is no record in the database. |
| Cursor cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(0, cursor.getCount()); |
| |
| String sql = "INSERT INTO test (name, age, address) VALUES (?, ?, ?);"; |
| SQLiteStatement insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, name); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 2, age); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 3, address); |
| insertStatement.execute(); |
| insertStatement.close(); |
| cursor.close(); |
| |
| cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(name, cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(age, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals(address, cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test"); |
| deleteStatement.execute(); |
| |
| cursor = mDatabase.query("test", null, null, null, null, null, null); |
| assertEquals(0, cursor.getCount()); |
| cursor.deactivate(); |
| deleteStatement.close(); |
| cursor.close(); |
| } |
| |
| public void testDelete() { |
| mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " |
| + "name TEXT, age INTEGER, address TEXT);"); |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');"); |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');"); |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jim', 35, 'Chicago');"); |
| |
| // delete one record. |
| int count = mDatabase.delete(TABLE_NAME, "name = 'Mike'", null); |
| assertEquals(1, count); |
| |
| Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, |
| null, null, null, null); |
| assertNotNull(cursor); |
| // there are 2 records here. |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| // delete another record. |
| count = mDatabase.delete(TABLE_NAME, "name = ?", new String[] { "Jack" }); |
| assertEquals(1, count); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, |
| null, null); |
| assertNotNull(cursor); |
| // there are 1 records here. |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');"); |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');"); |
| |
| // delete all records. |
| count = mDatabase.delete(TABLE_NAME, null, null); |
| assertEquals(3, count); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(0, cursor.getCount()); |
| cursor.close(); |
| } |
| |
| public void testExecSQL() { |
| mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " |
| + "name TEXT, age INTEGER, address TEXT);"); |
| |
| // add a new record. |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');"); |
| |
| Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, |
| null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| // add other new record. |
| mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');"); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| // delete a record. |
| mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Jack" }); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| // delete a non-exist record. |
| mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Wrong Name" }); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| try { |
| // execSQL can not use for query. |
| mDatabase.execSQL("SELECT * FROM test;"); |
| fail("should throw SQLException."); |
| } catch (SQLException e) { |
| } |
| |
| // make sure execSQL can't be used to execute more than 1 sql statement at a time |
| mDatabase.execSQL("UPDATE test SET age = 40 WHERE name = 'Mike';" + |
| "UPDATE test SET age = 50 WHERE name = 'Mike';"); |
| // age should be updated to 40 not to 50 |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| // make sure sql injection is NOT allowed or has no effect when using query() |
| String harmfulQuery = "name = 'Mike';UPDATE test SET age = 50 WHERE name = 'Mike'"; |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, harmfulQuery, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| // row's age column SHOULD NOT be 50 |
| assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close();; |
| } |
| |
| public void testFindEditTable() { |
| String tables = "table1 table2 table3"; |
| assertEquals("table1", SQLiteDatabase.findEditTable(tables)); |
| |
| tables = "table1,table2,table3"; |
| assertEquals("table1", SQLiteDatabase.findEditTable(tables)); |
| |
| tables = "table1"; |
| assertEquals("table1", SQLiteDatabase.findEditTable(tables)); |
| |
| try { |
| SQLiteDatabase.findEditTable(""); |
| fail("should throw IllegalStateException."); |
| } catch (IllegalStateException e) { |
| } |
| } |
| |
| public void testGetPath() { |
| assertEquals(mDatabaseFilePath, mDatabase.getPath()); |
| } |
| |
| public void testAccessVersion() { |
| mDatabase.setVersion(1); |
| assertEquals(1, mDatabase.getVersion()); |
| |
| mDatabase.setVersion(3); |
| assertEquals(3, mDatabase.getVersion()); |
| } |
| |
| public void testInsert() { |
| mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " |
| + "name TEXT, age INTEGER, address TEXT);"); |
| |
| ContentValues values = new ContentValues(); |
| values.put("name", "Jack"); |
| values.put("age", 20); |
| values.put("address", "LA"); |
| mDatabase.insert(TABLE_NAME, "name", values); |
| |
| Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, |
| null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| mDatabase.insert(TABLE_NAME, "name", null); |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, |
| null, null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.moveToNext(); |
| assertNull(cursor.getString(COLUMN_NAME_INDEX)); |
| cursor.close(); |
| |
| values = new ContentValues(); |
| values.put("Wrong Key", "Wrong value"); |
| mDatabase.insert(TABLE_NAME, "name", values); |
| // there are still 2 records. |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, |
| null, null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.close(); |
| |
| // delete all record. |
| mDatabase.execSQL("DELETE FROM test;"); |
| |
| values = new ContentValues(); |
| values.put("name", "Mike"); |
| values.put("age", 30); |
| values.put("address", "London"); |
| mDatabase.insertOrThrow(TABLE_NAME, "name", values); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, |
| null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| mDatabase.insertOrThrow(TABLE_NAME, "name", null); |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, |
| null, null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.moveToNext(); |
| assertNull(cursor.getString(COLUMN_NAME_INDEX)); |
| cursor.close(); |
| |
| values = new ContentValues(); |
| values.put("Wrong Key", "Wrong value"); |
| try { |
| mDatabase.insertOrThrow(TABLE_NAME, "name", values); |
| fail("should throw SQLException."); |
| } catch (SQLException e) { |
| } |
| } |
| |
| public void testIsOpen() { |
| assertTrue(mDatabase.isOpen()); |
| |
| mDatabase.close(); |
| assertFalse(mDatabase.isOpen()); |
| } |
| |
| public void testIsReadOnly() { |
| assertFalse(mDatabase.isReadOnly()); |
| |
| SQLiteDatabase database = null; |
| try { |
| database = SQLiteDatabase.openDatabase(mDatabaseFilePath, null, |
| SQLiteDatabase.OPEN_READONLY); |
| assertTrue(database.isReadOnly()); |
| } finally { |
| if (database != null) { |
| database.close(); |
| } |
| } |
| } |
| |
| public void testReleaseMemory() { |
| SQLiteDatabase.releaseMemory(); |
| } |
| |
| public void testSetLockingEnabled() { |
| mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); |
| mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); |
| |
| mDatabase.setLockingEnabled(false); |
| |
| mDatabase.beginTransaction(); |
| setNum(1); |
| assertNum(1); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| } |
| |
| @SuppressWarnings("deprecation") |
| public void testYieldIfContended() { |
| assertFalse(mDatabase.yieldIfContended()); |
| |
| mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); |
| mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); |
| |
| // Make sure that things work outside an explicit transaction. |
| setNum(1); |
| assertNum(1); |
| |
| setNum(0); |
| assertFalse(mDatabase.inTransaction()); |
| mDatabase.beginTransaction(); |
| assertTrue(mDatabase.inTransaction()); |
| assertFalse(mDatabase.yieldIfContended()); |
| setNum(1); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| |
| mDatabase.beginTransaction(); |
| assertTrue(mDatabase.inTransaction()); |
| assertFalse(mDatabase.yieldIfContendedSafely()); |
| setNum(1); |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| } |
| |
| public void testQuery() { |
| mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " + |
| "name TEXT, month INTEGER, salary INTEGER);"); |
| mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + |
| "VALUES ('Mike', '1', '1000');"); |
| mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + |
| "VALUES ('Mike', '2', '3000');"); |
| mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + |
| "VALUES ('jack', '1', '2000');"); |
| mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + |
| "VALUES ('jack', '3', '1500');"); |
| mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + |
| "VALUES ('Jim', '1', '1000');"); |
| mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + |
| "VALUES ('Jim', '3', '3500');"); |
| |
| Cursor cursor = mDatabase.query(true, "employee", new String[] { "name", "sum(salary)" }, |
| null, null, "name", "sum(salary)>1000", "name", null); |
| assertNotNull(cursor); |
| assertEquals(3, cursor.getCount()); |
| |
| final int COLUMN_NAME_INDEX = 0; |
| final int COLUMN_SALARY_INDEX = 1; |
| cursor.moveToFirst(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.close(); |
| |
| CursorFactory factory = new CursorFactory() { |
| public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, |
| String editTable, SQLiteQuery query) { |
| return new MockSQLiteCursor(db, masterQuery, editTable, query); |
| } |
| }; |
| cursor = mDatabase.queryWithFactory(factory, true, "employee", |
| new String[] { "name", "sum(salary)" }, |
| null, null, "name", "sum(salary) > 1000", "name", null); |
| assertNotNull(cursor); |
| assertTrue(cursor instanceof MockSQLiteCursor); |
| cursor.moveToFirst(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.close(); |
| |
| cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" }, |
| null, null, "name", "sum(salary) <= 4000", "name"); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.close(); |
| |
| cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" }, |
| null, null, "name", "sum(salary) > 1000", "name", "2"); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| |
| cursor.moveToFirst(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); |
| cursor.close(); |
| |
| String sql = "SELECT name, month FROM employee WHERE salary > ?;"; |
| cursor = mDatabase.rawQuery(sql, new String[] { "2000" }); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| |
| final int COLUMN_MONTH_INDEX = 1; |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX)); |
| cursor.close(); |
| |
| cursor = mDatabase.rawQueryWithFactory(factory, sql, new String[] { "2000" }, null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| assertTrue(cursor instanceof MockSQLiteCursor); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX)); |
| cursor.close(); |
| } |
| |
| public void testReplace() { |
| mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " |
| + "name TEXT, age INTEGER, address TEXT);"); |
| |
| ContentValues values = new ContentValues(); |
| values.put("name", "Jack"); |
| values.put("age", 20); |
| values.put("address", "LA"); |
| mDatabase.replace(TABLE_NAME, "name", values); |
| |
| Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, |
| null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| int id = cursor.getInt(COLUMN_ID_INDEX); |
| assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| values = new ContentValues(); |
| values.put("_id", id); |
| values.put("name", "Mike"); |
| values.put("age", 40); |
| values.put("address", "London"); |
| mDatabase.replace(TABLE_NAME, "name", values); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); // there is still ONLY 1 record. |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| values = new ContentValues(); |
| values.put("name", "Jack"); |
| values.put("age", 20); |
| values.put("address", "LA"); |
| mDatabase.replaceOrThrow(TABLE_NAME, "name", values); |
| |
| cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToFirst(); |
| assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.moveToNext(); |
| assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); |
| assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); |
| assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); |
| cursor.close(); |
| |
| values = new ContentValues(); |
| values.put("Wrong Key", "Wrong value"); |
| try { |
| mDatabase.replaceOrThrow(TABLE_NAME, "name", values); |
| fail("should throw SQLException."); |
| } catch (SQLException e) { |
| } |
| } |
| |
| public void testUpdate() { |
| mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); |
| |
| mDatabase.execSQL("INSERT INTO test (data) VALUES ('string1');"); |
| mDatabase.execSQL("INSERT INTO test (data) VALUES ('string2');"); |
| mDatabase.execSQL("INSERT INTO test (data) VALUES ('string3');"); |
| |
| String updatedString = "this is an updated test"; |
| ContentValues values = new ContentValues(1); |
| values.put("data", updatedString); |
| assertEquals(1, mDatabase.update("test", values, "_id=1", null)); |
| Cursor cursor = mDatabase.query("test", null, "_id=1", null, null, null, null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToFirst(); |
| String value = cursor.getString(cursor.getColumnIndexOrThrow("data")); |
| assertEquals(updatedString, value); |
| cursor.close(); |
| } |
| |
| public void testNeedUpgrade() { |
| mDatabase.setVersion(0); |
| assertTrue(mDatabase.needUpgrade(1)); |
| mDatabase.setVersion(1); |
| assertFalse(mDatabase.needUpgrade(1)); |
| } |
| |
| public void testSetLocale() { |
| final String[] STRINGS = { |
| "c\u00f4t\u00e9", |
| "cote", |
| "c\u00f4te", |
| "cot\u00e9", |
| "boy", |
| "dog", |
| "COTE", |
| }; |
| |
| mDatabase.execSQL("CREATE TABLE test (data TEXT COLLATE LOCALIZED);"); |
| for (String s : STRINGS) { |
| mDatabase.execSQL("INSERT INTO test VALUES('" + s + "');"); |
| } |
| |
| mDatabase.setLocale(new Locale("en", "US")); |
| |
| String sql = "SELECT data FROM test ORDER BY data COLLATE LOCALIZED ASC"; |
| Cursor cursor = mDatabase.rawQuery(sql, null); |
| assertNotNull(cursor); |
| ArrayList<String> items = new ArrayList<String>(); |
| while (cursor.moveToNext()) { |
| items.add(cursor.getString(0)); |
| } |
| String[] results = items.toArray(new String[items.size()]); |
| assertEquals(STRINGS.length, results.length); |
| cursor.close(); |
| |
| // The database code currently uses PRIMARY collation strength, |
| // meaning that all versions of a character compare equal (regardless |
| // of case or accents), leaving the "cote" flavors in database order. |
| MoreAsserts.assertEquals(results, new String[] { |
| STRINGS[4], // "boy" |
| STRINGS[0], // sundry forms of "cote" |
| STRINGS[1], |
| STRINGS[2], |
| STRINGS[3], |
| STRINGS[6], // "COTE" |
| STRINGS[5], // "dog" |
| }); |
| } |
| |
| public void testOnAllReferencesReleased() { |
| assertTrue(mDatabase.isOpen()); |
| mDatabase.releaseReference(); |
| assertFalse(mDatabase.isOpen()); |
| } |
| |
| public void testTransactionWithSQLiteTransactionListener() { |
| mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); |
| mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); |
| |
| assertEquals(mTransactionListenerOnBeginCalled, false); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener()); |
| |
| // Assert that the transcation has started |
| assertEquals(mTransactionListenerOnBeginCalled, true); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| |
| setNum(1); |
| |
| // State shouldn't have changed |
| assertEquals(mTransactionListenerOnBeginCalled, true); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| |
| // commit the transaction |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| |
| // the listener should have been told that commit was called |
| assertEquals(mTransactionListenerOnBeginCalled, true); |
| assertEquals(mTransactionListenerOnCommitCalled, true); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| } |
| |
| public void testRollbackTransactionWithSQLiteTransactionListener() { |
| mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); |
| mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); |
| |
| assertEquals(mTransactionListenerOnBeginCalled, false); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener()); |
| |
| // Assert that the transcation has started |
| assertEquals(mTransactionListenerOnBeginCalled, true); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| |
| setNum(1); |
| |
| // State shouldn't have changed |
| assertEquals(mTransactionListenerOnBeginCalled, true); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, false); |
| |
| // commit the transaction |
| mDatabase.endTransaction(); |
| |
| // the listener should have been told that commit was called |
| assertEquals(mTransactionListenerOnBeginCalled, true); |
| assertEquals(mTransactionListenerOnCommitCalled, false); |
| assertEquals(mTransactionListenerOnRollbackCalled, true); |
| } |
| |
| private class TestSQLiteTransactionListener implements SQLiteTransactionListener { |
| public void onBegin() { |
| mTransactionListenerOnBeginCalled = true; |
| } |
| |
| public void onCommit() { |
| mTransactionListenerOnCommitCalled = true; |
| } |
| |
| public void onRollback() { |
| mTransactionListenerOnRollbackCalled = true; |
| } |
| } |
| |
| public void testGroupConcat() { |
| mDatabase.execSQL("CREATE TABLE test (i INT, j TEXT);"); |
| |
| // insert 2 rows |
| String sql = "INSERT INTO test (i) VALUES (?);"; |
| SQLiteStatement insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); |
| insertStatement.execute(); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 2); |
| insertStatement.execute(); |
| insertStatement.close(); |
| |
| // make sure there are 2 rows in the table |
| Cursor cursor = mDatabase.rawQuery("SELECT count(*) FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(2, cursor.getInt(0)); |
| cursor.close(); |
| |
| // concatenate column j from all the rows. should return NULL |
| cursor = mDatabase.rawQuery("SELECT group_concat(j, ' ') FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertNull(cursor.getString(0)); |
| cursor.close(); |
| |
| // drop the table |
| mDatabase.execSQL("DROP TABLE test;"); |
| // should get no exceptions |
| } |
| |
| public void testSchemaChanges() { |
| mDatabase.execSQL("CREATE TABLE test (i INT, j INT);"); |
| |
| // at the beginning, there is no record in the database. |
| Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(0, cursor.getCount()); |
| cursor.close(); |
| |
| String sql = "INSERT INTO test VALUES (?, ?);"; |
| SQLiteStatement insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2); |
| insertStatement.execute(); |
| insertStatement.close(); |
| |
| // read the data from the table and make sure it is correct |
| cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(1, cursor.getInt(0)); |
| assertEquals(2, cursor.getInt(1)); |
| cursor.close(); |
| |
| // alter the table and execute another statement |
| mDatabase.execSQL("ALTER TABLE test ADD COLUMN k int;"); |
| sql = "INSERT INTO test VALUES (?, ?, ?);"; |
| insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5); |
| insertStatement.execute(); |
| insertStatement.close(); |
| |
| // read the data from the table and make sure it is correct |
| cursor = mDatabase.rawQuery("SELECT i,j,k FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(1, cursor.getInt(0)); |
| assertEquals(2, cursor.getInt(1)); |
| assertNull(cursor.getString(2)); |
| cursor.moveToNext(); |
| assertEquals(3, cursor.getInt(0)); |
| assertEquals(4, cursor.getInt(1)); |
| assertEquals(5, cursor.getInt(2)); |
| cursor.close(); |
| |
| // make sure the old statement - which should *try to reuse* cached query plan - |
| // still works |
| cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(2, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(1, cursor.getInt(0)); |
| assertEquals(2, cursor.getInt(1)); |
| cursor.moveToNext(); |
| assertEquals(3, cursor.getInt(0)); |
| assertEquals(4, cursor.getInt(1)); |
| cursor.close(); |
| |
| SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test"); |
| deleteStatement.execute(); |
| deleteStatement.close(); |
| } |
| |
| public void testSchemaChangesNewTable() { |
| mDatabase.execSQL("CREATE TABLE test (i INT, j INT);"); |
| |
| // at the beginning, there is no record in the database. |
| Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(0, cursor.getCount()); |
| cursor.close(); |
| |
| String sql = "INSERT INTO test VALUES (?, ?);"; |
| SQLiteStatement insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2); |
| insertStatement.execute(); |
| insertStatement.close(); |
| |
| // read the data from the table and make sure it is correct |
| cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(1, cursor.getInt(0)); |
| assertEquals(2, cursor.getInt(1)); |
| cursor.close(); |
| |
| // alter the table and execute another statement |
| mDatabase.execSQL("CREATE TABLE test_new (i INT, j INT, k INT);"); |
| sql = "INSERT INTO test_new VALUES (?, ?, ?);"; |
| insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5); |
| insertStatement.execute(); |
| insertStatement.close(); |
| |
| // read the data from the table and make sure it is correct |
| cursor = mDatabase.rawQuery("SELECT i,j,k FROM test_new", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(3, cursor.getInt(0)); |
| assertEquals(4, cursor.getInt(1)); |
| assertEquals(5, cursor.getInt(2)); |
| cursor.close(); |
| |
| // make sure the old statement - which should *try to reuse* cached query plan - |
| // still works |
| cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(1, cursor.getInt(0)); |
| assertEquals(2, cursor.getInt(1)); |
| cursor.close(); |
| |
| SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test"); |
| deleteStatement.execute(); |
| deleteStatement.close(); |
| |
| SQLiteStatement deleteStatement2 = mDatabase.compileStatement("DELETE FROM test_new"); |
| deleteStatement2.execute(); |
| deleteStatement2.close(); |
| } |
| |
| public void testSchemaChangesDropTable() { |
| mDatabase.execSQL("CREATE TABLE test (i INT, j INT);"); |
| |
| // at the beginning, there is no record in the database. |
| Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(0, cursor.getCount()); |
| cursor.close(); |
| |
| String sql = "INSERT INTO test VALUES (?, ?);"; |
| SQLiteStatement insertStatement = mDatabase.compileStatement(sql); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); |
| DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2); |
| insertStatement.execute(); |
| insertStatement.close(); |
| |
| // read the data from the table and make sure it is correct |
| cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); |
| assertNotNull(cursor); |
| assertEquals(1, cursor.getCount()); |
| cursor.moveToNext(); |
| assertEquals(1, cursor.getInt(0)); |
| assertEquals(2, cursor.getInt(1)); |
| } |
| |
| /** |
| * With sqlite's write-ahead-logging (WAL) enabled, readers get old version of data |
| * from the table that a writer is modifying at the same time. |
| * <p> |
| * This method does the following to test this sqlite3 feature |
| * <ol> |
| * <li>creates a table in the database and populates it with 5 rows of data</li> |
| * <li>do "select count(*) from this_table" and expect to receive 5</li> |
| * <li>start a writer thread who BEGINs a transaction, INSERTs a single row |
| * into this_table</li> |
| * <li>writer stops the transaction at this point, kicks off a reader thread - which will |
| * do the above SELECT query: "select count(*) from this_table"</li> |
| * <li>this query should return value 5 - because writer is still in transaction and |
| * sqlite returns OLD version of the data</li> |
| * <li>writer ends the transaction, thus making the extra row now visible to everyone</li> |
| * <li>reader is kicked off again to do the same query. this time query should |
| * return value = 6 which includes the newly inserted row into this_table.</li> |
| *</p> |
| * @throws InterruptedException |
| */ |
| @LargeTest |
| public void testReaderGetsOldVersionOfDataWhenWriterIsInXact() throws InterruptedException { |
| // redo setup to create WAL enabled database |
| mDatabase.close(); |
| new File(mDatabase.getPath()).delete(); |
| mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); |
| boolean rslt = mDatabase.enableWriteAheadLogging(); |
| assertTrue(rslt); |
| assertNotNull(mDatabase); |
| |
| // create a new table and insert 5 records into it. |
| mDatabase.execSQL("CREATE TABLE t1 (i int, j int);"); |
| mDatabase.beginTransaction(); |
| for (int i = 0; i < 5; i++) { |
| mDatabase.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""}); |
| } |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| |
| // make sure a reader can read the above data |
| ReaderQueryingData r1 = new ReaderQueryingData(5); |
| r1.start(); |
| Thread.yield(); |
| try {r1.join();} catch (Exception e) {} |
| |
| WriterDoingSingleTransaction w = new WriterDoingSingleTransaction(); |
| w.start(); |
| w.join(); |
| } |
| |
| private class WriterDoingSingleTransaction extends Thread { |
| @Override public void run() { |
| // start a transaction |
| mDatabase.beginTransactionNonExclusive(); |
| mDatabase.execSQL("insert into t1 values(?,?);", new String[] {"11", "11"}); |
| assertTrue(mDatabase.isOpen()); |
| |
| // while the writer is in a transaction, start a reader and make sure it can still |
| // read 5 rows of data (= old data prior to the current transaction) |
| ReaderQueryingData r1 = new ReaderQueryingData(5); |
| r1.start(); |
| try {r1.join();} catch (Exception e) {} |
| |
| // now, have the writer do the select count(*) |
| // it should execute on the same connection as this transaction |
| // and count(*) should reflect the newly inserted row |
| Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null); |
| assertEquals(6, l.intValue()); |
| |
| // end transaction |
| mDatabase.setTransactionSuccessful(); |
| mDatabase.endTransaction(); |
| |
| // reader should now be able to read 6 rows = new data AFTER this transaction |
| r1 = new ReaderQueryingData(6); |
| r1.start(); |
| try {r1.join();} catch (Exception e) {} |
| } |
| } |
| |
| private class ReaderQueryingData extends Thread { |
| private int count; |
| /** |
| * constructor with a param to indicate the number of rows expected to be read |
| */ |
| public ReaderQueryingData(int count) { |
| this.count = count; |
| } |
| @Override public void run() { |
| Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null); |
| assertEquals(count, l.intValue()); |
| } |
| } |
| |
| public void testExceptionsFromEnableWriteAheadLogging() { |
| // attach a database |
| // redo setup to create WAL enabled database |
| mDatabase.close(); |
| new File(mDatabase.getPath()).delete(); |
| mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); |
| |
| // attach a database and call enableWriteAheadLogging - should not be allowed |
| mDatabase.execSQL("attach database ':memory:' as memoryDb"); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| assertFalse(mDatabase.enableWriteAheadLogging()); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| |
| // enableWriteAheadLogging on memory database is not allowed |
| SQLiteDatabase db = SQLiteDatabase.create(null); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| assertFalse(db.enableWriteAheadLogging()); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| db.close(); |
| } |
| |
| public void testEnableThenDisableWriteAheadLogging() { |
| // Enable WAL. |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(mDatabase.enableWriteAheadLogging()); |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) |
| .equalsIgnoreCase("WAL")); |
| |
| // Enabling when already enabled should have no observable effect. |
| assertTrue(mDatabase.enableWriteAheadLogging()); |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) |
| .equalsIgnoreCase("WAL")); |
| |
| // Disabling when there are no connections should work. |
| mDatabase.disableWriteAheadLogging(); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| } |
| |
| public void testEnableThenDisableWriteAheadLoggingUsingOpenFlag() { |
| new File(mDatabase.getPath()).delete(); |
| mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile.getPath(), null, |
| SQLiteDatabase.CREATE_IF_NECESSARY | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING, |
| null); |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) |
| .equalsIgnoreCase("WAL")); |
| |
| // Enabling when already enabled should have no observable effect. |
| assertTrue(mDatabase.enableWriteAheadLogging()); |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) |
| .equalsIgnoreCase("WAL")); |
| |
| // Disabling when there are no connections should work. |
| mDatabase.disableWriteAheadLogging(); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| } |
| |
| public void testEnableWriteAheadLoggingFromContextUsingModeFlag() { |
| // Without the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens without WAL. |
| getContext().deleteDatabase(DATABASE_FILE_NAME); |
| mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME, |
| Context.MODE_PRIVATE, null); |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| mDatabase.close(); |
| |
| // With the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens with WAL. |
| getContext().deleteDatabase(DATABASE_FILE_NAME); |
| mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME, |
| Context.MODE_PRIVATE | Context.MODE_ENABLE_WRITE_AHEAD_LOGGING, null); |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| mDatabase.close(); |
| } |
| |
| public void testEnableWriteAheadLoggingShouldThrowIfTransactionInProgress() { |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| String oldJournalMode = DatabaseUtils.stringForQuery( |
| mDatabase, "PRAGMA journal_mode", null); |
| |
| // Begin transaction. |
| mDatabase.beginTransaction(); |
| |
| try { |
| // Attempt to enable WAL should fail. |
| mDatabase.enableWriteAheadLogging(); |
| fail("Expected IllegalStateException"); |
| } catch (IllegalStateException ex) { |
| // expected |
| } |
| |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) |
| .equalsIgnoreCase(oldJournalMode)); |
| } |
| |
| public void testDisableWriteAheadLoggingShouldThrowIfTransactionInProgress() { |
| // Enable WAL. |
| assertFalse(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(mDatabase.enableWriteAheadLogging()); |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| |
| // Begin transaction. |
| mDatabase.beginTransaction(); |
| |
| try { |
| // Attempt to disable WAL should fail. |
| mDatabase.disableWriteAheadLogging(); |
| fail("Expected IllegalStateException"); |
| } catch (IllegalStateException ex) { |
| // expected |
| } |
| |
| assertTrue(mDatabase.isWriteAheadLoggingEnabled()); |
| assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) |
| .equalsIgnoreCase("WAL")); |
| } |
| } |
