1 /* 2 * Copyright (C) 2006 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package android.database.sqlite; 18 19 import android.content.ContentValues; 20 import android.content.Context; 21 import android.database.Cursor; 22 import android.database.DatabaseErrorHandler; 23 import android.database.DatabaseUtils; 24 import android.database.DefaultDatabaseErrorHandler; 25 import android.database.sqlite.SQLiteDatabase; 26 import android.database.sqlite.SQLiteDatabase.CursorFactory; 27 import android.database.sqlite.SQLiteStatement; 28 import android.test.AndroidTestCase; 29 import android.test.suitebuilder.annotation.LargeTest; 30 import android.test.suitebuilder.annotation.MediumTest; 31 import android.test.suitebuilder.annotation.SmallTest; 32 import android.test.suitebuilder.annotation.Suppress; 33 import android.util.Log; 34 import android.util.Pair; 35 36 import java.io.File; 37 import java.util.ArrayList; 38 import java.util.List; 39 40 public class SQLiteDatabaseTest extends AndroidTestCase { 41 private static final String TAG = "DatabaseGeneralTest"; 42 private static final String TEST_TABLE = "test"; 43 private static final int CURRENT_DATABASE_VERSION = 42; 44 private SQLiteDatabase mDatabase; 45 private File mDatabaseFile; 46 private static final int INSERT = 1; 47 private static final int UPDATE = 2; 48 private static final int DELETE = 3; 49 private static final String DB_NAME = "database_test.db"; 50 51 @Override 52 protected void setUp() throws Exception { 53 super.setUp(); 54 dbSetUp(); 55 } 56 57 @Override 58 protected void tearDown() throws Exception { 59 dbTeardown(); 60 super.tearDown(); 61 } 62 63 private void dbTeardown() throws Exception { 64 mDatabase.close(); 65 mDatabaseFile.delete(); 66 } 67 68 private void dbSetUp() throws Exception { 69 File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); 70 mDatabaseFile = new File(dbDir, DB_NAME); 71 if (mDatabaseFile.exists()) { 72 mDatabaseFile.delete(); 73 } 74 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); 75 assertNotNull(mDatabase); 76 mDatabase.setVersion(CURRENT_DATABASE_VERSION); 77 } 78 79 @SmallTest 80 public void testEnableWriteAheadLogging() { 81 mDatabase.disableWriteAheadLogging(); 82 assertNull(mDatabase.mConnectionPool); 83 mDatabase.enableWriteAheadLogging(); 84 DatabaseConnectionPool pool = mDatabase.mConnectionPool; 85 assertNotNull(pool); 86 // make the same call again and make sure the pool already setup is not re-created 87 mDatabase.enableWriteAheadLogging(); 88 assertEquals(pool, mDatabase.mConnectionPool); 89 } 90 91 @SmallTest 92 public void testDisableWriteAheadLogging() { 93 mDatabase.execSQL("create table test (i int);"); 94 mDatabase.enableWriteAheadLogging(); 95 assertNotNull(mDatabase.mConnectionPool); 96 // get a pooled database connection 97 SQLiteDatabase db = mDatabase.getDbConnection("select * from test"); 98 assertNotNull(db); 99 assertFalse(mDatabase.equals(db)); 100 assertTrue(db.isOpen()); 101 // disable WAL - which should close connection pool and all pooled connections 102 mDatabase.disableWriteAheadLogging(); 103 assertNull(mDatabase.mConnectionPool); 104 assertFalse(db.isOpen()); 105 } 106 107 @SmallTest 108 public void testCursorsWithClosedDbConnAfterDisableWriteAheadLogging() { 109 mDatabase.disableWriteAheadLogging(); 110 mDatabase.beginTransactionNonExclusive(); 111 mDatabase.execSQL("create table test (i int);"); 112 mDatabase.execSQL("insert into test values(1);"); 113 mDatabase.setTransactionSuccessful(); 114 mDatabase.endTransaction(); 115 mDatabase.enableWriteAheadLogging(); 116 assertNotNull(mDatabase.mConnectionPool); 117 assertEquals(0, mDatabase.mConnectionPool.getSize()); 118 assertEquals(0, mDatabase.mConnectionPool.getFreePoolSize()); 119 // get a cursor which should use pooled database connection 120 Cursor c = mDatabase.rawQuery("select * from test", null); 121 assertEquals(1, c.getCount()); 122 assertEquals(1, mDatabase.mConnectionPool.getSize()); 123 assertEquals(1, mDatabase.mConnectionPool.getFreePoolSize()); 124 SQLiteDatabase db = mDatabase.mConnectionPool.getConnectionList().get(0); 125 assertTrue(mDatabase.mConnectionPool.isDatabaseObjFree(db)); 126 // disable WAL - which should close connection pool and all pooled connections 127 mDatabase.disableWriteAheadLogging(); 128 assertNull(mDatabase.mConnectionPool); 129 assertFalse(db.isOpen()); 130 // cursor data should still be accessible because it is fetching data from CursorWindow 131 c.moveToNext(); 132 assertEquals(1, c.getInt(0)); 133 c.requery(); 134 assertEquals(1, c.getCount()); 135 c.moveToNext(); 136 assertEquals(1, c.getInt(0)); 137 c.close(); 138 } 139 140 /** 141 * a transaction should be started before a standalone-update/insert/delete statement 142 */ 143 @SmallTest 144 public void testStartXactBeforeUpdateSql() throws InterruptedException { 145 runTestForStartXactBeforeUpdateSql(INSERT); 146 runTestForStartXactBeforeUpdateSql(UPDATE); 147 runTestForStartXactBeforeUpdateSql(DELETE); 148 } 149 private void runTestForStartXactBeforeUpdateSql(int stmtType) throws InterruptedException { 150 createTableAndClearCache(); 151 152 ContentValues values = new ContentValues(); 153 // make some changes to data in TEST_TABLE 154 for (int i = 0; i < 5; i++) { 155 values.put("i", i); 156 values.put("j", "i" + System.currentTimeMillis()); 157 mDatabase.insert(TEST_TABLE, null, values); 158 switch (stmtType) { 159 case UPDATE: 160 values.put("j", "u" + System.currentTimeMillis()); 161 mDatabase.update(TEST_TABLE, values, "i = " + i, null); 162 break; 163 case DELETE: 164 mDatabase.delete(TEST_TABLE, "i = 1", null); 165 break; 166 } 167 } 168 // do a query. even though query uses a different database connection, 169 // it should still see the above changes to data because the above standalone 170 // insert/update/deletes are done in transactions automatically. 171 String sql = "select count(*) from " + TEST_TABLE; 172 SQLiteStatement stmt = mDatabase.compileStatement(sql); 173 final int expectedValue = (stmtType == DELETE) ? 4 : 5; 174 assertEquals(expectedValue, stmt.simpleQueryForLong()); 175 stmt.close(); 176 Cursor c = mDatabase.rawQuery(sql, null); 177 assertEquals(1, c.getCount()); 178 c.moveToFirst(); 179 assertEquals(expectedValue, c.getLong(0)); 180 c.close(); 181 182 // do 5 more changes in a transaction but do a query before and after the commit 183 mDatabase.beginTransaction(); 184 for (int i = 10; i < 15; i++) { 185 values.put("i", i); 186 values.put("j", "i" + System.currentTimeMillis()); 187 mDatabase.insert(TEST_TABLE, null, values); 188 switch (stmtType) { 189 case UPDATE: 190 values.put("j", "u" + System.currentTimeMillis()); 191 mDatabase.update(TEST_TABLE, values, "i = " + i, null); 192 break; 193 case DELETE: 194 mDatabase.delete(TEST_TABLE, "i = 1", null); 195 break; 196 } 197 } 198 mDatabase.setTransactionSuccessful(); 199 // do a query before commit - should still have 5 rows 200 // this query should run in a different thread to force it to use a different database 201 // connection 202 Thread t = new Thread() { 203 @Override public void run() { 204 String sql = "select count(*) from " + TEST_TABLE; 205 SQLiteStatement stmt = getDb().compileStatement(sql); 206 assertEquals(expectedValue, stmt.simpleQueryForLong()); 207 stmt.close(); 208 Cursor c = getDb().rawQuery(sql, null); 209 assertEquals(1, c.getCount()); 210 c.moveToFirst(); 211 assertEquals(expectedValue, c.getLong(0)); 212 c.close(); 213 } 214 }; 215 t.start(); 216 // wait until the above thread is done 217 t.join(); 218 // commit and then query. should see changes from the transaction 219 mDatabase.endTransaction(); 220 stmt = mDatabase.compileStatement(sql); 221 final int expectedValue2 = (stmtType == DELETE) ? 9 : 10; 222 assertEquals(expectedValue2, stmt.simpleQueryForLong()); 223 stmt.close(); 224 c = mDatabase.rawQuery(sql, null); 225 assertEquals(1, c.getCount()); 226 c.moveToFirst(); 227 assertEquals(expectedValue2, c.getLong(0)); 228 c.close(); 229 } 230 private synchronized SQLiteDatabase getDb() { 231 return mDatabase; 232 } 233 234 /** 235 * Test to ensure that readers are able to read the database data (old versions) 236 * EVEN WHEN the writer is in a transaction on the same database. 237 *<p> 238 * This test starts 1 Writer and 2 Readers and sets up connection pool for readers 239 * by calling the method {@link SQLiteDatabase#enableWriteAheadLogging()}. 240 * <p> 241 * Writer does the following in a tight loop 242 * <pre> 243 * begin transaction 244 * insert into table_1 245 * insert into table_2 246 * commit 247 * </pre> 248 * <p> 249 * As long a the writer is alive, Readers do the following in a tight loop at the same time 250 * <pre> 251 * Reader_K does "select count(*) from table_K" where K = 1 or 2 252 * </pre> 253 * <p> 254 * The test is run for TIME_TO_RUN_WAL_TEST_FOR sec. 255 * <p> 256 * The test is repeated for different connection-pool-sizes (1..3) 257 * <p> 258 * And at the end of of each test, the following statistics are printed 259 * <ul> 260 * <li>connection-pool-size</li> 261 * <li>number-of-transactions by writer</li> 262 * <li>number of reads by reader_K while the writer is IN or NOT-IN xaction</li> 263 * </ul> 264 */ 265 @LargeTest 266 @Suppress // run this test only if you need to collect the numbers from this test 267 public void testConcurrencyEffectsOfConnPool() throws Exception { 268 // run the test with sqlite WAL enable 269 runConnectionPoolTest(true); 270 271 // run the same test WITHOUT sqlite WAL enabled 272 runConnectionPoolTest(false); 273 } 274 275 private void runConnectionPoolTest(boolean useWal) throws Exception { 276 int M = 3; 277 StringBuilder[] buff = new StringBuilder[M]; 278 for (int i = 0; i < M; i++) { 279 if (useWal) { 280 // set up connection pool 281 mDatabase.enableWriteAheadLogging(); 282 mDatabase.mConnectionPool.setMaxPoolSize(i + 1); 283 } else { 284 mDatabase.disableWriteAheadLogging(); 285 } 286 mDatabase.execSQL("CREATE TABLE t1 (i int, j int);"); 287 mDatabase.execSQL("CREATE TABLE t2 (i int, j int);"); 288 mDatabase.beginTransaction(); 289 for (int k = 0; k < 5; k++) { 290 mDatabase.execSQL("insert into t1 values(?,?);", new String[] {k+"", k+""}); 291 mDatabase.execSQL("insert into t2 values(?,?);", new String[] {k+"", k+""}); 292 } 293 mDatabase.setTransactionSuccessful(); 294 mDatabase.endTransaction(); 295 296 // start a writer 297 Writer w = new Writer(mDatabase); 298 299 // initialize an array of counters to be passed to the readers 300 Reader r1 = new Reader(mDatabase, "t1", w, 0); 301 Reader r2 = new Reader(mDatabase, "t2", w, 1); 302 w.start(); 303 r1.start(); 304 r2.start(); 305 306 // wait for all threads to die 307 w.join(); 308 r1.join(); 309 r2.join(); 310 311 // print the stats 312 int[][] counts = getCounts(); 313 buff[i] = new StringBuilder(); 314 buff[i].append("connpool-size = "); 315 buff[i].append(i + 1); 316 buff[i].append(", num xacts by writer = "); 317 buff[i].append(getNumXacts()); 318 buff[i].append(", num-reads-in-xact/NOT-in-xact by reader1 = "); 319 buff[i].append(counts[0][1] + "/" + counts[0][0]); 320 buff[i].append(", by reader2 = "); 321 buff[i].append(counts[1][1] + "/" + counts[1][0]); 322 323 Log.i(TAG, "done testing for conn-pool-size of " + (i+1)); 324 325 dbTeardown(); 326 dbSetUp(); 327 } 328 Log.i(TAG, "duration of test " + TIME_TO_RUN_WAL_TEST_FOR + " sec"); 329 for (int i = 0; i < M; i++) { 330 Log.i(TAG, buff[i].toString()); 331 } 332 } 333 334 private boolean inXact = false; 335 private int numXacts; 336 private static final int TIME_TO_RUN_WAL_TEST_FOR = 15; // num sec this test should run 337 private int[][] counts = new int[2][2]; 338 339 private synchronized boolean inXact() { 340 return inXact; 341 } 342 343 private synchronized void setInXactFlag(boolean flag) { 344 inXact = flag; 345 } 346 347 private synchronized void setCounts(int readerNum, int[] numReads) { 348 counts[readerNum][0] = numReads[0]; 349 counts[readerNum][1] = numReads[1]; 350 } 351 352 private synchronized int[][] getCounts() { 353 return counts; 354 } 355 356 private synchronized void setNumXacts(int num) { 357 numXacts = num; 358 } 359 360 private synchronized int getNumXacts() { 361 return numXacts; 362 } 363 364 private class Writer extends Thread { 365 private SQLiteDatabase db = null; 366 public Writer(SQLiteDatabase db) { 367 this.db = db; 368 } 369 @Override public void run() { 370 // in a loop, for N sec, do the following 371 // BEGIN transaction 372 // insert into table t1, t2 373 // Commit 374 long now = System.currentTimeMillis(); 375 int k; 376 for (k = 0;(System.currentTimeMillis() - now) / 1000 < TIME_TO_RUN_WAL_TEST_FOR; k++) { 377 db.beginTransactionNonExclusive(); 378 setInXactFlag(true); 379 for (int i = 0; i < 10; i++) { 380 db.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""}); 381 db.execSQL("insert into t2 values(?,?);", new String[] {i+"", i+""}); 382 } 383 db.setTransactionSuccessful(); 384 setInXactFlag(false); 385 db.endTransaction(); 386 } 387 setNumXacts(k); 388 } 389 } 390 391 private class Reader extends Thread { 392 private SQLiteDatabase db = null; 393 private String table = null; 394 private Writer w = null; 395 private int readerNum; 396 private int[] numReads = new int[2]; 397 public Reader(SQLiteDatabase db, String table, Writer w, int readerNum) { 398 this.db = db; 399 this.table = table; 400 this.w = w; 401 this.readerNum = readerNum; 402 } 403 @Override public void run() { 404 // while the write is alive, in a loop do the query on a table 405 while (w.isAlive()) { 406 for (int i = 0; i < 10; i++) { 407 DatabaseUtils.longForQuery(db, "select count(*) from " + this.table, null); 408 // update count of reads 409 numReads[inXact() ? 1 : 0] += 1; 410 } 411 } 412 setCounts(readerNum, numReads); 413 } 414 } 415 416 public static class ClassToTestSqlCompilationAndCaching extends SQLiteProgram { 417 private ClassToTestSqlCompilationAndCaching(SQLiteDatabase db, String sql) { 418 super(db, sql); 419 } 420 public static ClassToTestSqlCompilationAndCaching create(SQLiteDatabase db, String sql) { 421 db.lock(); 422 try { 423 return new ClassToTestSqlCompilationAndCaching(db, sql); 424 } finally { 425 db.unlock(); 426 } 427 } 428 } 429 430 @SmallTest 431 public void testLruCachingOfSqliteCompiledSqlObjs() { 432 createTableAndClearCache(); 433 // set cache size 434 int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; 435 mDatabase.setMaxSqlCacheSize(N); 436 437 // do N+1 queries - and when the 0th entry is removed from LRU cache due to the 438 // insertion of (N+1)th entry, make sure 0th entry is closed 439 ArrayList<Integer> stmtObjs = new ArrayList<Integer>(); 440 ArrayList<String> sqlStrings = new ArrayList<String>(); 441 int stmt0 = 0; 442 for (int i = 0; i < N+1; i++) { 443 String s = "insert into test values(" + i + ",?);"; 444 sqlStrings.add(s); 445 ClassToTestSqlCompilationAndCaching c = 446 ClassToTestSqlCompilationAndCaching.create(mDatabase, s); 447 int n = c.getSqlStatementId(); 448 stmtObjs.add(i, n); 449 if (i == 0) { 450 // save the statementId of this obj. we want to make sure it is thrown out of 451 // the cache at the end of this test. 452 stmt0 = n; 453 } 454 c.close(); 455 } 456 // is 0'th entry out of the cache? it should be in the list of statementIds 457 // corresponding to the pre-compiled sql statements to be finalized. 458 assertTrue(mDatabase.getQueuedUpStmtList().contains(stmt0)); 459 for (int i = 1; i < N+1; i++) { 460 SQLiteCompiledSql compSql = mDatabase.getCompiledStatementForSql(sqlStrings.get(i)); 461 assertNotNull(compSql); 462 assertTrue(stmtObjs.contains(compSql.nStatement)); 463 } 464 } 465 466 @MediumTest 467 public void testDbCloseReleasingAllCachedSql() { 468 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, text1 TEXT, text2 TEXT, " + 469 "num1 INTEGER, num2 INTEGER, image BLOB);"); 470 final String statement = "DELETE FROM test WHERE _id=?;"; 471 SQLiteStatement statementDoNotClose = mDatabase.compileStatement(statement); 472 statementDoNotClose.bindLong(1, 1); 473 /* do not close statementDoNotClose object. 474 * That should leave it in SQLiteDatabase.mPrograms. 475 * mDatabase.close() in tearDown() should release it. 476 */ 477 } 478 479 private void createTableAndClearCache() { 480 mDatabase.disableWriteAheadLogging(); 481 mDatabase.execSQL("DROP TABLE IF EXISTS " + TEST_TABLE); 482 mDatabase.execSQL("CREATE TABLE " + TEST_TABLE + " (i int, j int);"); 483 mDatabase.enableWriteAheadLogging(); 484 mDatabase.lock(); 485 // flush the above statement from cache and close all the pending statements to be released 486 mDatabase.deallocCachedSqlStatements(); 487 mDatabase.closePendingStatements(); 488 mDatabase.unlock(); 489 assertEquals(0, mDatabase.getQueuedUpStmtList().size()); 490 } 491 492 /** 493 * test to make sure the statement finalizations are not done right away but 494 * piggy-backed onto the next sql statement execution on the same database. 495 */ 496 @SmallTest 497 public void testStatementClose() { 498 createTableAndClearCache(); 499 // fill up statement cache in mDatabase 500 int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; 501 mDatabase.setMaxSqlCacheSize(N); 502 SQLiteStatement stmt; 503 int stmt0Id = 0; 504 for (int i = 0; i < N; i ++) { 505 ClassToTestSqlCompilationAndCaching c = 506 ClassToTestSqlCompilationAndCaching.create(mDatabase, 507 "insert into test values(" + i + ", ?);"); 508 // keep track of 0th entry 509 if (i == 0) { 510 stmt0Id = c.getSqlStatementId(); 511 } 512 c.close(); 513 } 514 515 // add one more to the cache - and the above 'stmt0Id' should fall out of cache 516 ClassToTestSqlCompilationAndCaching stmt1 = 517 ClassToTestSqlCompilationAndCaching.create(mDatabase, 518 "insert into test values(100, ?);"); 519 stmt1.close(); 520 521 // the above close() should have queuedUp the statement for finalization 522 ArrayList<Integer> statementIds = mDatabase.getQueuedUpStmtList(); 523 assertTrue(statementIds.contains(stmt0Id)); 524 525 // execute something to see if this statement gets finalized 526 mDatabase.execSQL("delete from test where i = 10;"); 527 statementIds = mDatabase.getQueuedUpStmtList(); 528 assertFalse(statementIds.contains(stmt0Id)); 529 } 530 531 /** 532 * same as above - except that the statement to be finalized is from Thread # 1. 533 * and it is eventually finalized in Thread # 2 when it executes a SQL statement. 534 * @throws InterruptedException 535 */ 536 @LargeTest 537 public void testStatementCloseDiffThread() throws InterruptedException { 538 createTableAndClearCache(); 539 final int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; 540 mDatabase.setMaxSqlCacheSize(N); 541 // fill up statement cache in mDatabase in a thread 542 Thread t1 = new Thread() { 543 @Override public void run() { 544 SQLiteStatement stmt; 545 for (int i = 0; i < N; i++) { 546 ClassToTestSqlCompilationAndCaching c = 547 ClassToTestSqlCompilationAndCaching.create(getDb(), 548 "insert into test values(" + i + ", ?);"); 549 // keep track of 0th entry 550 if (i == 0) { 551 stmt0Id = c.getSqlStatementId(); 552 } 553 c.close(); 554 } 555 } 556 }; 557 t1.start(); 558 // wait for the thread to finish 559 t1.join(); 560 // mDatabase shouldn't have any statements to be released 561 assertEquals(0, mDatabase.getQueuedUpStmtList().size()); 562 563 // add one more to the cache - and the above 'stmt0Id' should fall out of cache 564 // just for the heck of it, do it in a separate thread 565 Thread t2 = new Thread() { 566 @Override public void run() { 567 ClassToTestSqlCompilationAndCaching stmt1 = 568 ClassToTestSqlCompilationAndCaching.create(getDb(), 569 "insert into test values(100, ?);"); 570 stmt1.bindLong(1, 1); 571 stmt1.close(); 572 } 573 }; 574 t2.start(); 575 t2.join(); 576 577 // close() in the above thread should have queuedUp the stmt0Id for finalization 578 ArrayList<Integer> statementIds = getDb().getQueuedUpStmtList(); 579 assertTrue(statementIds.contains(getStmt0Id())); 580 assertEquals(1, statementIds.size()); 581 582 // execute something to see if this statement gets finalized 583 // again do it in a separate thread 584 Thread t3 = new Thread() { 585 @Override public void run() { 586 getDb().execSQL("delete from test where i = 10;"); 587 } 588 }; 589 t3.start(); 590 t3.join(); 591 592 // is the statement finalized? 593 statementIds = getDb().getQueuedUpStmtList(); 594 assertFalse(statementIds.contains(getStmt0Id())); 595 } 596 597 private volatile int stmt0Id = 0; 598 private synchronized int getStmt0Id() { 599 return this.stmt0Id; 600 } 601 602 /** 603 * same as above - except that the queue of statements to be finalized are finalized 604 * by database close() operation. 605 */ 606 @LargeTest 607 public void testStatementCloseByDbClose() throws InterruptedException { 608 createTableAndClearCache(); 609 // fill up statement cache in mDatabase in a thread 610 Thread t1 = new Thread() { 611 @Override public void run() { 612 int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; 613 getDb().setMaxSqlCacheSize(N); 614 SQLiteStatement stmt; 615 for (int i = 0; i < N; i ++) { 616 ClassToTestSqlCompilationAndCaching c = 617 ClassToTestSqlCompilationAndCaching.create(getDb(), 618 "insert into test values(" + i + ", ?);"); 619 // keep track of 0th entry 620 if (i == 0) { 621 stmt0Id = c.getSqlStatementId(); 622 } 623 c.close(); 624 } 625 } 626 }; 627 t1.start(); 628 // wait for the thread to finish 629 t1.join(); 630 631 // add one more to the cache - and the above 'stmt0Id' should fall out of cache 632 // just for the heck of it, do it in a separate thread 633 Thread t2 = new Thread() { 634 @Override public void run() { 635 ClassToTestSqlCompilationAndCaching stmt1 = 636 ClassToTestSqlCompilationAndCaching.create(getDb(), 637 "insert into test values(100, ?);"); 638 stmt1.bindLong(1, 1); 639 stmt1.close(); 640 } 641 }; 642 t2.start(); 643 t2.join(); 644 645 // close() in the above thread should have queuedUp the statement for finalization 646 ArrayList<Integer> statementIds = getDb().getQueuedUpStmtList(); 647 assertTrue(getStmt0Id() > 0); 648 assertTrue(statementIds.contains(stmt0Id)); 649 assertEquals(1, statementIds.size()); 650 651 // close the database. everything from mClosedStatementIds in mDatabase 652 // should be finalized and cleared from the list 653 // again do it in a separate thread 654 Thread t3 = new Thread() { 655 @Override public void run() { 656 getDb().close(); 657 } 658 }; 659 t3.start(); 660 t3.join(); 661 662 // check mClosedStatementIds in mDatabase. it should be empty 663 statementIds = getDb().getQueuedUpStmtList(); 664 assertEquals(0, statementIds.size()); 665 } 666 667 /** 668 * This test tests usage execSQL() to begin transaction works in the following way 669 * Thread #1 does 670 * execSQL("begin transaction"); 671 * insert() 672 * Thread # 2 673 * query() 674 * Thread#1 ("end transaction") 675 * Thread # 2 query will execute - because java layer will not have locked the SQLiteDatabase 676 * object and sqlite will consider this query to be part of the transaction. 677 * 678 * but if thread # 1 uses beginTransaction() instead of execSQL() to start transaction, 679 * then Thread # 2's query will have been blocked by java layer 680 * until Thread#1 ends transaction. 681 * 682 * @throws InterruptedException 683 */ 684 @SmallTest 685 public void testExecSqlToStartAndEndTransaction() throws InterruptedException { 686 runExecSqlToStartAndEndTransaction("END"); 687 // same as above, instead now do "COMMIT" or "ROLLBACK" instead of "END" transaction 688 runExecSqlToStartAndEndTransaction("COMMIT"); 689 runExecSqlToStartAndEndTransaction("ROLLBACK"); 690 } 691 private void runExecSqlToStartAndEndTransaction(String str) throws InterruptedException { 692 createTableAndClearCache(); 693 // disable WAL just so queries and updates use the same database connection 694 mDatabase.disableWriteAheadLogging(); 695 mDatabase.execSQL("BEGIN transaction"); 696 // even though mDatabase.beginTransaction() is not called to start transaction, 697 // mDatabase connection should now be in transaction as a result of 698 // mDatabase.execSQL("BEGIN transaction") 699 // but mDatabase.mLock should not be held by any thread 700 assertTrue(mDatabase.inTransaction()); 701 assertFalse(mDatabase.isDbLockedByCurrentThread()); 702 assertFalse(mDatabase.isDbLockedByOtherThreads()); 703 assertTrue(mDatabase.amIInTransaction()); 704 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 705 assertTrue(mDatabase.inTransaction()); 706 assertFalse(mDatabase.isDbLockedByCurrentThread()); 707 assertFalse(mDatabase.isDbLockedByOtherThreads()); 708 assertTrue(mDatabase.amIInTransaction()); 709 Thread t = new Thread() { 710 @Override public void run() { 711 assertTrue(mDatabase.amIInTransaction()); 712 assertEquals(999, DatabaseUtils.longForQuery(getDb(), 713 "select j from " + TEST_TABLE + " WHERE i = 10", null)); 714 assertTrue(getDb().inTransaction()); 715 assertFalse(getDb().isDbLockedByCurrentThread()); 716 assertFalse(getDb().isDbLockedByOtherThreads()); 717 assertTrue(mDatabase.amIInTransaction()); 718 } 719 }; 720 t.start(); 721 t.join(); 722 assertTrue(mDatabase.amIInTransaction()); 723 assertTrue(mDatabase.inTransaction()); 724 assertFalse(mDatabase.isDbLockedByCurrentThread()); 725 assertFalse(mDatabase.isDbLockedByOtherThreads()); 726 mDatabase.execSQL(str); 727 assertFalse(mDatabase.amIInTransaction()); 728 assertFalse(mDatabase.inTransaction()); 729 assertFalse(mDatabase.isDbLockedByCurrentThread()); 730 assertFalse(mDatabase.isDbLockedByOtherThreads()); 731 } 732 733 /** 734 * test the following 735 * http://b/issue?id=2871037 736 * Cursor cursor = db.query(...); 737 * // with WAL enabled, the above uses a pooled database connection 738 * db.beginTransaction() 739 * try { 740 * db.insert(......); 741 * cursor.requery(); 742 * // since the cursor uses pooled database connection, the above requery 743 * // will not return the results that were inserted above since the insert is 744 * // done using main database connection AND the transaction is not committed yet. 745 * // fix is to make the above cursor use the main database connection - and NOT 746 * // the pooled database connection 747 * db.setTransactionSuccessful() 748 * } finally { 749 * db.endTransaction() 750 * } 751 * 752 * @throws InterruptedException 753 */ 754 @SmallTest 755 public void testTransactionAndWalInterplay1() throws InterruptedException { 756 createTableAndClearCache(); 757 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 758 String sql = "select * from " + TEST_TABLE; 759 Cursor c = mDatabase.rawQuery(sql, null); 760 // should have 1 row in the table 761 assertEquals(1, c.getCount()); 762 mDatabase.beginTransactionNonExclusive(); 763 try { 764 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); 765 assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 766 "select count(*) from " + TEST_TABLE, null)); 767 // requery on the previously opened cursor 768 // cursor should now use the main database connection and see 2 rows 769 c.requery(); 770 assertEquals(2, c.getCount()); 771 mDatabase.setTransactionSuccessful(); 772 } finally { 773 mDatabase.endTransaction(); 774 } 775 c.close(); 776 777 // do the same test but now do the requery in a separate thread. 778 createTableAndClearCache(); 779 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 780 final Cursor c1 = mDatabase.rawQuery("select count(*) from " + TEST_TABLE, null); 781 // should have 1 row in the table 782 assertEquals(1, c1.getCount()); 783 mDatabase.beginTransactionNonExclusive(); 784 try { 785 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); 786 assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 787 "select count(*) from " + TEST_TABLE, null)); 788 // query in a different thread. that causes the cursor to use a pooled connection 789 // and since this thread hasn't committed its changes, the cursor should still see only 790 // 1 row 791 Thread t = new Thread() { 792 @Override public void run() { 793 c1.requery(); 794 assertEquals(1, c1.getCount()); 795 } 796 }; 797 t.start(); 798 t.join(); 799 // should be 2 rows now - including the the row inserted above 800 mDatabase.setTransactionSuccessful(); 801 } finally { 802 mDatabase.endTransaction(); 803 } 804 c1.close(); 805 } 806 807 /** 808 * This test is same as {@link #testTransactionAndWalInterplay1()} except the following: 809 * instead of mDatabase.beginTransactionNonExclusive(), use execSQL("BEGIN transaction") 810 * and instead of mDatabase.endTransaction(), use execSQL("END"); 811 */ 812 @SmallTest 813 public void testTransactionAndWalInterplay2() throws InterruptedException { 814 createTableAndClearCache(); 815 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 816 String sql = "select * from " + TEST_TABLE; 817 Cursor c = mDatabase.rawQuery(sql, null); 818 // should have 1 row in the table 819 assertEquals(1, c.getCount()); 820 mDatabase.execSQL("BEGIN transaction"); 821 try { 822 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); 823 assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 824 "select count(*) from " + TEST_TABLE, null)); 825 // requery on the previously opened cursor 826 // cursor should now use the main database connection and see 2 rows 827 c.requery(); 828 assertEquals(2, c.getCount()); 829 } finally { 830 mDatabase.execSQL("commit;"); 831 } 832 c.close(); 833 834 // do the same test but now do the requery in a separate thread. 835 createTableAndClearCache(); 836 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 837 final Cursor c1 = mDatabase.rawQuery("select count(*) from " + TEST_TABLE, null); 838 // should have 1 row in the table 839 assertEquals(1, c1.getCount()); 840 mDatabase.execSQL("BEGIN transaction"); 841 try { 842 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); 843 assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 844 "select count(*) from " + TEST_TABLE, null)); 845 // query in a different thread. but since the transaction is started using 846 // execSQ() instead of beginTransaction(), cursor's query is considered part of 847 // the same transaction - and hence it should see the above inserted row 848 Thread t = new Thread() { 849 @Override public void run() { 850 c1.requery(); 851 assertEquals(1, c1.getCount()); 852 } 853 }; 854 t.start(); 855 t.join(); 856 // should be 2 rows now - including the the row inserted above 857 } finally { 858 mDatabase.execSQL("commit"); 859 } 860 c1.close(); 861 } 862 863 /** 864 * This test is same as {@link #testTransactionAndWalInterplay2()} except the following: 865 * instead of committing the data, do rollback and make sure the data seen by the query 866 * within the transaction is now gone. 867 */ 868 @SmallTest 869 public void testTransactionAndWalInterplay3() { 870 createTableAndClearCache(); 871 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 872 String sql = "select * from " + TEST_TABLE; 873 Cursor c = mDatabase.rawQuery(sql, null); 874 // should have 1 row in the table 875 assertEquals(1, c.getCount()); 876 mDatabase.execSQL("BEGIN transaction"); 877 try { 878 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);"); 879 assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 880 "select count(*) from " + TEST_TABLE, null)); 881 // requery on the previously opened cursor 882 // cursor should now use the main database connection and see 2 rows 883 c.requery(); 884 assertEquals(2, c.getCount()); 885 } finally { 886 // rollback the change 887 mDatabase.execSQL("rollback;"); 888 } 889 // since the change is rolled back, do the same query again and should now find only 1 row 890 c.requery(); 891 assertEquals(1, c.getCount()); 892 assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 893 "select count(*) from " + TEST_TABLE, null)); 894 c.close(); 895 } 896 897 @SmallTest 898 public void testAttachDb() { 899 String newDb = "/sdcard/mydata.db"; 900 File f = new File(newDb); 901 if (f.exists()) { 902 f.delete(); 903 } 904 assertFalse(f.exists()); 905 SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(newDb, null); 906 db.execSQL("create table test1 (i int);"); 907 db.execSQL("insert into test1 values(1);"); 908 db.execSQL("insert into test1 values(11);"); 909 Cursor c = null; 910 try { 911 c = db.rawQuery("select * from test1", null); 912 int count = c.getCount(); 913 Log.i(TAG, "count: " + count); 914 assertEquals(2, count); 915 } finally { 916 c.close(); 917 db.close(); 918 c = null; 919 } 920 921 mDatabase.execSQL("attach database ? as newDb" , new String[]{newDb}); 922 Cursor c1 = null; 923 try { 924 c1 = mDatabase.rawQuery("select * from newDb.test1", null); 925 assertEquals(2, c1.getCount()); 926 } catch (Exception e) { 927 fail("unexpected exception: " + e.getMessage()); 928 } finally { 929 if (c1 != null) { 930 c1.close(); 931 } 932 } 933 List<Pair<String, String>> dbs = mDatabase.getAttachedDbs(); 934 for (Pair<String, String> p: dbs) { 935 Log.i(TAG, "attached dbs: " + p.first + " : " + p.second); 936 } 937 assertEquals(2, dbs.size()); 938 } 939 940 /** 941 * http://b/issue?id=2943028 942 * SQLiteOpenHelper maintains a Singleton even if it is in bad state. 943 */ 944 @SmallTest 945 public void testCloseAndReopen() { 946 mDatabase.close(); 947 TestOpenHelper helper = new TestOpenHelper(getContext(), DB_NAME, null, 948 CURRENT_DATABASE_VERSION, new DefaultDatabaseErrorHandler()); 949 mDatabase = helper.getWritableDatabase(); 950 createTableAndClearCache(); 951 mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);"); 952 Cursor c = mDatabase.query(TEST_TABLE, new String[]{"i", "j"}, null, null, null, null, null); 953 assertEquals(1, c.getCount()); 954 c.close(); 955 mDatabase.close(); 956 assertFalse(mDatabase.isOpen()); 957 mDatabase = helper.getReadableDatabase(); 958 assertTrue(mDatabase.isOpen()); 959 c = mDatabase.query(TEST_TABLE, new String[]{"i", "j"}, null, null, null, null, null); 960 assertEquals(1, c.getCount()); 961 c.close(); 962 } 963 private class TestOpenHelper extends SQLiteOpenHelper { 964 public TestOpenHelper(Context context, String name, CursorFactory factory, int version, 965 DatabaseErrorHandler errorHandler) { 966 super(context, name, factory, version, errorHandler); 967 } 968 @Override public void onCreate(SQLiteDatabase db) {} 969 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {} 970 } 971 } 972
