1 /* 2 * Copyright (C) 2009 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.cts; 18 19 import static android.database.sqlite.cts.DatabaseTestUtils.getDbInfoOutput; 20 import static android.database.sqlite.cts.DatabaseTestUtils.waitForConnectionToClose; 21 22 import android.app.ActivityManager; 23 import android.content.ContentValues; 24 import android.content.Context; 25 import android.database.Cursor; 26 import android.database.DatabaseUtils; 27 import android.database.SQLException; 28 import android.database.sqlite.SQLiteCursor; 29 import android.database.sqlite.SQLiteCursorDriver; 30 import android.database.sqlite.SQLiteDatabase; 31 import android.database.sqlite.SQLiteDatabase.CursorFactory; 32 import android.database.sqlite.SQLiteDebug; 33 import android.database.sqlite.SQLiteGlobal; 34 import android.database.sqlite.SQLiteQuery; 35 import android.database.sqlite.SQLiteStatement; 36 import android.database.sqlite.SQLiteTransactionListener; 37 import android.test.AndroidTestCase; 38 import android.test.MoreAsserts; 39 import android.test.suitebuilder.annotation.LargeTest; 40 import android.util.Log; 41 42 import java.io.File; 43 import java.io.IOException; 44 import java.util.ArrayList; 45 import java.util.List; 46 import java.util.Locale; 47 import java.util.concurrent.Semaphore; 48 49 public class SQLiteDatabaseTest extends AndroidTestCase { 50 51 private static final String TAG = "SQLiteDatabaseTest"; 52 private static final String EXPECTED_MAJOR_MINOR_VERSION = "3.22"; 53 private static final int EXPECTED_MIN_PATCH_LEVEL = 0; 54 55 private SQLiteDatabase mDatabase; 56 private File mDatabaseFile; 57 private String mDatabaseFilePath; 58 private String mDatabaseDir; 59 60 private boolean mTransactionListenerOnBeginCalled; 61 private boolean mTransactionListenerOnCommitCalled; 62 private boolean mTransactionListenerOnRollbackCalled; 63 64 private static final String DATABASE_FILE_NAME = "database_test.db"; 65 private static final String TABLE_NAME = "test"; 66 67 private static final int COLUMN_ID_INDEX = 0; 68 private static final int COLUMN_NAME_INDEX = 1; 69 private static final int COLUMN_AGE_INDEX = 2; 70 private static final int COLUMN_ADDR_INDEX = 3; 71 private static final String[] TEST_PROJECTION = new String[] { 72 "_id", // 0 73 "name", // 1 74 "age", // 2 75 "address" // 3 76 }; 77 78 @Override 79 protected void setUp() throws Exception { 80 super.setUp(); 81 82 getContext().deleteDatabase(DATABASE_FILE_NAME); 83 mDatabaseFilePath = getContext().getDatabasePath(DATABASE_FILE_NAME).getPath(); 84 mDatabaseFile = getContext().getDatabasePath(DATABASE_FILE_NAME); 85 mDatabaseDir = mDatabaseFile.getParent(); 86 mDatabaseFile.getParentFile().mkdirs(); // directory may not exist 87 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null); 88 assertNotNull(mDatabase); 89 90 mTransactionListenerOnBeginCalled = false; 91 mTransactionListenerOnCommitCalled = false; 92 mTransactionListenerOnRollbackCalled = false; 93 } 94 95 @Override 96 protected void tearDown() throws Exception { 97 closeAndDeleteDatabase(); 98 super.tearDown(); 99 } 100 101 private void closeAndDeleteDatabase() { 102 mDatabase.close(); 103 SQLiteDatabase.deleteDatabase(mDatabaseFile); 104 } 105 106 public void testOpenDatabase() { 107 CursorFactory factory = MockSQLiteCursor::new; 108 SQLiteDatabase db = SQLiteDatabase.openDatabase(mDatabaseFilePath, 109 factory, SQLiteDatabase.CREATE_IF_NECESSARY); 110 assertNotNull(db); 111 db.close(); 112 113 File dbFile = new File(mDatabaseDir, "database_test12345678.db"); 114 dbFile.delete(); 115 assertFalse(dbFile.exists()); 116 db = SQLiteDatabase.openOrCreateDatabase(dbFile.getPath(), factory); 117 assertNotNull(db); 118 db.close(); 119 dbFile.delete(); 120 121 dbFile = new File(mDatabaseDir, DATABASE_FILE_NAME); 122 db = SQLiteDatabase.openOrCreateDatabase(dbFile, factory); 123 assertNotNull(db); 124 db.close(); 125 dbFile.delete(); 126 127 db = SQLiteDatabase.create(factory); 128 assertNotNull(db); 129 db.close(); 130 } 131 132 public void testDeleteDatabase() throws IOException { 133 File dbFile = new File(mDatabaseDir, "database_test12345678.db"); 134 File journalFile = new File(dbFile.getPath() + "-journal"); 135 File shmFile = new File(dbFile.getPath() + "-shm"); 136 File walFile = new File(dbFile.getPath() + "-wal"); 137 File mjFile1 = new File(dbFile.getPath() + "-mj00000000"); 138 File mjFile2 = new File(dbFile.getPath() + "-mj00000001"); 139 File innocentFile = new File(dbFile.getPath() + "-innocent"); 140 141 dbFile.createNewFile(); 142 journalFile.createNewFile(); 143 shmFile.createNewFile(); 144 walFile.createNewFile(); 145 mjFile1.createNewFile(); 146 mjFile2.createNewFile(); 147 innocentFile.createNewFile(); 148 149 boolean deleted = SQLiteDatabase.deleteDatabase(dbFile); 150 assertTrue(deleted); 151 152 assertFalse(dbFile.exists()); 153 assertFalse(journalFile.exists()); 154 assertFalse(shmFile.exists()); 155 assertFalse(walFile.exists()); 156 assertFalse(mjFile1.exists()); 157 assertFalse(mjFile2.exists()); 158 assertTrue(innocentFile.exists()); 159 160 innocentFile.delete(); 161 162 boolean deletedAgain = SQLiteDatabase.deleteDatabase(dbFile); 163 assertFalse(deletedAgain); 164 } 165 166 private class MockSQLiteCursor extends SQLiteCursor { 167 public MockSQLiteCursor(SQLiteDatabase db, SQLiteCursorDriver driver, 168 String editTable, SQLiteQuery query) { 169 super(db, driver, editTable, query); 170 } 171 } 172 173 public void testTransaction() { 174 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 175 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 176 177 // test execSQL without any explicit transactions. 178 setNum(1); 179 assertNum(1); 180 181 // Test a single-level transaction. 182 setNum(0); 183 assertFalse(mDatabase.inTransaction()); 184 mDatabase.beginTransaction(); 185 assertTrue(mDatabase.inTransaction()); 186 setNum(1); 187 mDatabase.setTransactionSuccessful(); 188 mDatabase.endTransaction(); 189 assertFalse(mDatabase.inTransaction()); 190 assertNum(1); 191 assertFalse(mDatabase.isDbLockedByCurrentThread()); 192 assertFalse(mDatabase.isDbLockedByOtherThreads()); 193 194 // Test a rolled-back transaction. 195 setNum(0); 196 assertFalse(mDatabase.inTransaction()); 197 mDatabase.beginTransaction(); 198 setNum(1); 199 assertTrue(mDatabase.inTransaction()); 200 mDatabase.endTransaction(); 201 assertFalse(mDatabase.inTransaction()); 202 assertNum(0); 203 assertFalse(mDatabase.isDbLockedByCurrentThread()); 204 assertFalse(mDatabase.isDbLockedByOtherThreads()); 205 206 // it should throw IllegalStateException if we end a non-existent transaction. 207 assertThrowsIllegalState(new Runnable() { 208 public void run() { 209 mDatabase.endTransaction(); 210 } 211 }); 212 213 // it should throw IllegalStateException if a set a non-existent transaction as clean. 214 assertThrowsIllegalState(new Runnable() { 215 public void run() { 216 mDatabase.setTransactionSuccessful(); 217 } 218 }); 219 220 mDatabase.beginTransaction(); 221 mDatabase.setTransactionSuccessful(); 222 // it should throw IllegalStateException if we mark a transaction as clean twice. 223 assertThrowsIllegalState(new Runnable() { 224 public void run() { 225 mDatabase.setTransactionSuccessful(); 226 } 227 }); 228 // it should throw IllegalStateException if we begin a transaction after marking the 229 // parent as clean. 230 assertThrowsIllegalState(new Runnable() { 231 public void run() { 232 mDatabase.beginTransaction(); 233 } 234 }); 235 mDatabase.endTransaction(); 236 assertFalse(mDatabase.isDbLockedByCurrentThread()); 237 assertFalse(mDatabase.isDbLockedByOtherThreads()); 238 239 assertFalse(mDatabase.inTransaction()); 240 // Test a two-level transaction. 241 setNum(0); 242 mDatabase.beginTransaction(); 243 assertTrue(mDatabase.inTransaction()); 244 mDatabase.beginTransaction(); 245 assertTrue(mDatabase.inTransaction()); 246 setNum(1); 247 mDatabase.setTransactionSuccessful(); 248 mDatabase.endTransaction(); 249 assertTrue(mDatabase.inTransaction()); 250 mDatabase.setTransactionSuccessful(); 251 mDatabase.endTransaction(); 252 assertFalse(mDatabase.inTransaction()); 253 assertNum(1); 254 assertFalse(mDatabase.isDbLockedByCurrentThread()); 255 assertFalse(mDatabase.isDbLockedByOtherThreads()); 256 257 // Test rolling back an inner transaction. 258 setNum(0); 259 mDatabase.beginTransaction(); 260 mDatabase.beginTransaction(); 261 setNum(1); 262 mDatabase.endTransaction(); 263 mDatabase.setTransactionSuccessful(); 264 mDatabase.endTransaction(); 265 assertNum(0); 266 assertFalse(mDatabase.isDbLockedByCurrentThread()); 267 assertFalse(mDatabase.isDbLockedByOtherThreads()); 268 269 // Test rolling back an outer transaction. 270 setNum(0); 271 mDatabase.beginTransaction(); 272 mDatabase.beginTransaction(); 273 setNum(1); 274 mDatabase.setTransactionSuccessful(); 275 mDatabase.endTransaction(); 276 mDatabase.endTransaction(); 277 assertNum(0); 278 assertFalse(mDatabase.isDbLockedByCurrentThread()); 279 assertFalse(mDatabase.isDbLockedByOtherThreads()); 280 } 281 282 private void setNum(int num) { 283 mDatabase.execSQL("UPDATE test SET num = " + num); 284 } 285 286 private void assertNum(int num) { 287 assertEquals(num, DatabaseUtils.longForQuery(mDatabase, 288 "SELECT num FROM test", null)); 289 } 290 291 private void assertThrowsIllegalState(Runnable r) { 292 try { 293 r.run(); 294 fail("did not throw expected IllegalStateException"); 295 } catch (IllegalStateException e) { 296 } 297 } 298 299 public void testAccessMaximumSize() { 300 long curMaximumSize = mDatabase.getMaximumSize(); 301 302 // the new maximum size is less than the current size. 303 mDatabase.setMaximumSize(curMaximumSize - 1); 304 assertEquals(curMaximumSize, mDatabase.getMaximumSize()); 305 306 // the new maximum size is more than the current size. 307 mDatabase.setMaximumSize(curMaximumSize + 1); 308 assertEquals(curMaximumSize + mDatabase.getPageSize(), mDatabase.getMaximumSize()); 309 assertTrue(mDatabase.getMaximumSize() > curMaximumSize); 310 } 311 312 public void testAccessPageSize() { 313 File databaseFile = new File(mDatabaseDir, "database.db"); 314 if (databaseFile.exists()) { 315 databaseFile.delete(); 316 } 317 SQLiteDatabase database = null; 318 try { 319 database = SQLiteDatabase.openOrCreateDatabase(databaseFile.getPath(), null); 320 321 long initialValue = database.getPageSize(); 322 // check that this does not throw an exception 323 // setting a different page size may not be supported after the DB has been created 324 database.setPageSize(initialValue); 325 assertEquals(initialValue, database.getPageSize()); 326 327 } finally { 328 if (database != null) { 329 database.close(); 330 databaseFile.delete(); 331 } 332 } 333 } 334 335 public void testCompileStatement() { 336 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " 337 + "name TEXT, age INTEGER, address TEXT);"); 338 339 String name = "Mike"; 340 int age = 21; 341 String address = "LA"; 342 343 // at the beginning, there is no record in the database. 344 Cursor cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null); 345 assertNotNull(cursor); 346 assertEquals(0, cursor.getCount()); 347 348 String sql = "INSERT INTO test (name, age, address) VALUES (?, ?, ?);"; 349 SQLiteStatement insertStatement = mDatabase.compileStatement(sql); 350 DatabaseUtils.bindObjectToProgram(insertStatement, 1, name); 351 DatabaseUtils.bindObjectToProgram(insertStatement, 2, age); 352 DatabaseUtils.bindObjectToProgram(insertStatement, 3, address); 353 insertStatement.execute(); 354 insertStatement.close(); 355 cursor.close(); 356 357 cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null); 358 assertNotNull(cursor); 359 assertEquals(1, cursor.getCount()); 360 cursor.moveToNext(); 361 assertEquals(name, cursor.getString(COLUMN_NAME_INDEX)); 362 assertEquals(age, cursor.getInt(COLUMN_AGE_INDEX)); 363 assertEquals(address, cursor.getString(COLUMN_ADDR_INDEX)); 364 cursor.close(); 365 366 SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test"); 367 deleteStatement.execute(); 368 369 cursor = mDatabase.query("test", null, null, null, null, null, null); 370 assertEquals(0, cursor.getCount()); 371 cursor.deactivate(); 372 deleteStatement.close(); 373 cursor.close(); 374 } 375 376 public void testDelete() { 377 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " 378 + "name TEXT, age INTEGER, address TEXT);"); 379 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');"); 380 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');"); 381 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jim', 35, 'Chicago');"); 382 383 // delete one record. 384 int count = mDatabase.delete(TABLE_NAME, "name = 'Mike'", null); 385 assertEquals(1, count); 386 387 Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, 388 null, null, null, null); 389 assertNotNull(cursor); 390 // there are 2 records here. 391 assertEquals(2, cursor.getCount()); 392 cursor.moveToFirst(); 393 assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); 394 assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); 395 assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); 396 cursor.moveToNext(); 397 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 398 assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX)); 399 assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX)); 400 cursor.close(); 401 402 // delete another record. 403 count = mDatabase.delete(TABLE_NAME, "name = ?", new String[] { "Jack" }); 404 assertEquals(1, count); 405 406 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, 407 null, null); 408 assertNotNull(cursor); 409 // there are 1 records here. 410 assertEquals(1, cursor.getCount()); 411 cursor.moveToFirst(); 412 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 413 assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX)); 414 assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX)); 415 cursor.close(); 416 417 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');"); 418 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');"); 419 420 // delete all records. 421 count = mDatabase.delete(TABLE_NAME, null, null); 422 assertEquals(3, count); 423 424 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 425 assertNotNull(cursor); 426 assertEquals(0, cursor.getCount()); 427 cursor.close(); 428 } 429 430 public void testExecSQL() { 431 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " 432 + "name TEXT, age INTEGER, address TEXT);"); 433 434 // add a new record. 435 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');"); 436 437 Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, 438 null, null, null, null); 439 assertNotNull(cursor); 440 assertEquals(1, cursor.getCount()); 441 cursor.moveToFirst(); 442 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 443 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 444 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 445 cursor.close(); 446 447 // add other new record. 448 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');"); 449 450 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 451 assertNotNull(cursor); 452 assertEquals(2, cursor.getCount()); 453 cursor.moveToFirst(); 454 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 455 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 456 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 457 cursor.moveToNext(); 458 assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); 459 assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); 460 assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); 461 cursor.close(); 462 463 // delete a record. 464 mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Jack" }); 465 466 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 467 assertNotNull(cursor); 468 assertEquals(1, cursor.getCount()); 469 cursor.moveToFirst(); 470 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 471 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 472 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 473 cursor.close(); 474 475 // delete a non-exist record. 476 mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Wrong Name" }); 477 478 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 479 assertNotNull(cursor); 480 assertEquals(1, cursor.getCount()); 481 cursor.moveToFirst(); 482 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 483 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 484 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 485 cursor.close(); 486 487 try { 488 // execSQL can not use for query. 489 mDatabase.execSQL("SELECT * FROM test;"); 490 fail("should throw SQLException."); 491 } catch (SQLException e) { 492 } 493 494 // make sure execSQL can't be used to execute more than 1 sql statement at a time 495 mDatabase.execSQL("UPDATE test SET age = 40 WHERE name = 'Mike';" + 496 "UPDATE test SET age = 50 WHERE name = 'Mike';"); 497 // age should be updated to 40 not to 50 498 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 499 assertNotNull(cursor); 500 assertEquals(1, cursor.getCount()); 501 cursor.moveToFirst(); 502 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 503 assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); 504 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 505 cursor.close(); 506 507 // make sure sql injection is NOT allowed or has no effect when using query() 508 String harmfulQuery = "name = 'Mike';UPDATE test SET age = 50 WHERE name = 'Mike'"; 509 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, harmfulQuery, null, null, null, null); 510 assertNotNull(cursor); 511 assertEquals(1, cursor.getCount()); 512 cursor.moveToFirst(); 513 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 514 // row's age column SHOULD NOT be 50 515 assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); 516 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 517 cursor.close();; 518 } 519 520 public void testFindEditTable() { 521 String tables = "table1 table2 table3"; 522 assertEquals("table1", SQLiteDatabase.findEditTable(tables)); 523 524 tables = "table1,table2,table3"; 525 assertEquals("table1", SQLiteDatabase.findEditTable(tables)); 526 527 tables = "table1"; 528 assertEquals("table1", SQLiteDatabase.findEditTable(tables)); 529 530 try { 531 SQLiteDatabase.findEditTable(""); 532 fail("should throw IllegalStateException."); 533 } catch (IllegalStateException e) { 534 } 535 } 536 537 public void testGetPath() { 538 assertEquals(mDatabaseFilePath, mDatabase.getPath()); 539 } 540 541 public void testAccessVersion() { 542 mDatabase.setVersion(1); 543 assertEquals(1, mDatabase.getVersion()); 544 545 mDatabase.setVersion(3); 546 assertEquals(3, mDatabase.getVersion()); 547 } 548 549 public void testInsert() { 550 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " 551 + "name TEXT, age INTEGER, address TEXT);"); 552 553 ContentValues values = new ContentValues(); 554 values.put("name", "Jack"); 555 values.put("age", 20); 556 values.put("address", "LA"); 557 mDatabase.insert(TABLE_NAME, "name", values); 558 559 Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, 560 null, null, null, null); 561 assertNotNull(cursor); 562 assertEquals(1, cursor.getCount()); 563 cursor.moveToFirst(); 564 assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); 565 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 566 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 567 cursor.close(); 568 569 mDatabase.insert(TABLE_NAME, "name", null); 570 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, 571 null, null); 572 assertNotNull(cursor); 573 assertEquals(2, cursor.getCount()); 574 cursor.moveToFirst(); 575 assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); 576 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 577 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 578 cursor.moveToNext(); 579 assertNull(cursor.getString(COLUMN_NAME_INDEX)); 580 cursor.close(); 581 582 values = new ContentValues(); 583 values.put("Wrong Key", "Wrong value"); 584 mDatabase.insert(TABLE_NAME, "name", values); 585 // there are still 2 records. 586 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, 587 null, null); 588 assertNotNull(cursor); 589 assertEquals(2, cursor.getCount()); 590 cursor.close(); 591 592 // delete all record. 593 mDatabase.execSQL("DELETE FROM test;"); 594 595 values = new ContentValues(); 596 values.put("name", "Mike"); 597 values.put("age", 30); 598 values.put("address", "London"); 599 mDatabase.insertOrThrow(TABLE_NAME, "name", values); 600 601 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, 602 null, null); 603 assertNotNull(cursor); 604 assertEquals(1, cursor.getCount()); 605 cursor.moveToFirst(); 606 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 607 assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); 608 assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); 609 cursor.close(); 610 611 mDatabase.insertOrThrow(TABLE_NAME, "name", null); 612 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, 613 null, null); 614 assertNotNull(cursor); 615 assertEquals(2, cursor.getCount()); 616 cursor.moveToFirst(); 617 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 618 assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX)); 619 assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); 620 cursor.moveToNext(); 621 assertNull(cursor.getString(COLUMN_NAME_INDEX)); 622 cursor.close(); 623 624 values = new ContentValues(); 625 values.put("Wrong Key", "Wrong value"); 626 try { 627 mDatabase.insertOrThrow(TABLE_NAME, "name", values); 628 fail("should throw SQLException."); 629 } catch (SQLException e) { 630 } 631 } 632 633 public void testIsOpen() { 634 assertTrue(mDatabase.isOpen()); 635 636 mDatabase.close(); 637 assertFalse(mDatabase.isOpen()); 638 } 639 640 public void testIsReadOnly() { 641 assertFalse(mDatabase.isReadOnly()); 642 643 SQLiteDatabase database = null; 644 try { 645 database = SQLiteDatabase.openDatabase(mDatabaseFilePath, null, 646 SQLiteDatabase.OPEN_READONLY); 647 assertTrue(database.isReadOnly()); 648 } finally { 649 if (database != null) { 650 database.close(); 651 } 652 } 653 } 654 655 public void testReleaseMemory() { 656 SQLiteDatabase.releaseMemory(); 657 } 658 659 public void testSetLockingEnabled() { 660 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 661 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 662 663 mDatabase.setLockingEnabled(false); 664 665 mDatabase.beginTransaction(); 666 setNum(1); 667 assertNum(1); 668 mDatabase.setTransactionSuccessful(); 669 mDatabase.endTransaction(); 670 } 671 672 @SuppressWarnings("deprecation") 673 public void testYieldIfContendedWhenNotContended() { 674 assertFalse(mDatabase.yieldIfContended()); 675 676 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 677 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 678 679 // Make sure that things work outside an explicit transaction. 680 setNum(1); 681 assertNum(1); 682 683 setNum(0); 684 assertFalse(mDatabase.inTransaction()); 685 mDatabase.beginTransaction(); 686 assertTrue(mDatabase.inTransaction()); 687 assertFalse(mDatabase.yieldIfContended()); 688 setNum(1); 689 mDatabase.setTransactionSuccessful(); 690 mDatabase.endTransaction(); 691 692 mDatabase.beginTransaction(); 693 assertTrue(mDatabase.inTransaction()); 694 assertFalse(mDatabase.yieldIfContendedSafely()); 695 setNum(1); 696 mDatabase.setTransactionSuccessful(); 697 mDatabase.endTransaction(); 698 } 699 700 @SuppressWarnings("deprecation") 701 public void testYieldIfContendedWhenContended() throws Exception { 702 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 703 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 704 705 // Begin a transaction and update a value. 706 mDatabase.beginTransaction(); 707 setNum(1); 708 assertNum(1); 709 710 // On another thread, begin a transaction there. This causes contention 711 // for use of the database. When the main thread yields, the second thread 712 // begin its own transaction. It should perceive the new state that was 713 // committed by the main thread when it yielded. 714 final Semaphore s = new Semaphore(0); 715 Thread t = new Thread() { 716 @Override 717 public void run() { 718 s.release(); // let main thread continue 719 720 mDatabase.beginTransaction(); 721 assertNum(1); 722 setNum(2); 723 assertNum(2); 724 mDatabase.setTransactionSuccessful(); 725 mDatabase.endTransaction(); 726 } 727 }; 728 t.start(); 729 730 // Wait for thread to try to begin its transaction. 731 s.acquire(); 732 Thread.sleep(500); 733 734 // Yield. There should be contention for the database now, so yield will 735 // return true. 736 assertTrue(mDatabase.yieldIfContendedSafely()); 737 738 // Since we reacquired the transaction, the other thread must have finished 739 // its transaction. We should observe its changes and our own within this transaction. 740 assertNum(2); 741 setNum(3); 742 assertNum(3); 743 744 // Go ahead and finish the transaction. 745 mDatabase.setTransactionSuccessful(); 746 mDatabase.endTransaction(); 747 assertNum(3); 748 749 t.join(); 750 } 751 752 public void testQuery() { 753 mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " + 754 "name TEXT, month INTEGER, salary INTEGER);"); 755 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 756 "VALUES ('Mike', '1', '1000');"); 757 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 758 "VALUES ('Mike', '2', '3000');"); 759 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 760 "VALUES ('jack', '1', '2000');"); 761 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 762 "VALUES ('jack', '3', '1500');"); 763 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 764 "VALUES ('Jim', '1', '1000');"); 765 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 766 "VALUES ('Jim', '3', '3500');"); 767 768 Cursor cursor = mDatabase.query(true, "employee", new String[] { "name", "sum(salary)" }, 769 null, null, "name", "sum(salary)>1000", "name", null); 770 assertNotNull(cursor); 771 assertEquals(3, cursor.getCount()); 772 773 final int COLUMN_NAME_INDEX = 0; 774 final int COLUMN_SALARY_INDEX = 1; 775 cursor.moveToFirst(); 776 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 777 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 778 cursor.moveToNext(); 779 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 780 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 781 cursor.moveToNext(); 782 assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); 783 assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); 784 cursor.close(); 785 786 CursorFactory factory = new CursorFactory() { 787 public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, 788 String editTable, SQLiteQuery query) { 789 return new MockSQLiteCursor(db, masterQuery, editTable, query); 790 } 791 }; 792 cursor = mDatabase.queryWithFactory(factory, true, "employee", 793 new String[] { "name", "sum(salary)" }, 794 null, null, "name", "sum(salary) > 1000", "name", null); 795 assertNotNull(cursor); 796 assertTrue(cursor instanceof MockSQLiteCursor); 797 cursor.moveToFirst(); 798 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 799 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 800 cursor.moveToNext(); 801 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 802 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 803 cursor.moveToNext(); 804 assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); 805 assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); 806 cursor.close(); 807 808 cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" }, 809 null, null, "name", "sum(salary) <= 4000", "name"); 810 assertNotNull(cursor); 811 assertEquals(2, cursor.getCount()); 812 813 cursor.moveToFirst(); 814 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 815 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 816 cursor.moveToNext(); 817 assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); 818 assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); 819 cursor.close(); 820 821 cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" }, 822 null, null, "name", "sum(salary) > 1000", "name", "2"); 823 assertNotNull(cursor); 824 assertEquals(2, cursor.getCount()); 825 826 cursor.moveToFirst(); 827 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 828 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 829 cursor.moveToNext(); 830 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 831 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 832 cursor.close(); 833 834 String sql = "SELECT name, month FROM employee WHERE salary > ?;"; 835 cursor = mDatabase.rawQuery(sql, new String[] { "2000" }); 836 assertNotNull(cursor); 837 assertEquals(2, cursor.getCount()); 838 839 final int COLUMN_MONTH_INDEX = 1; 840 cursor.moveToFirst(); 841 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 842 assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX)); 843 cursor.moveToNext(); 844 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 845 assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX)); 846 cursor.close(); 847 848 cursor = mDatabase.rawQueryWithFactory(factory, sql, new String[] { "2000" }, null); 849 assertNotNull(cursor); 850 assertEquals(2, cursor.getCount()); 851 assertTrue(cursor instanceof MockSQLiteCursor); 852 cursor.moveToFirst(); 853 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 854 assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX)); 855 cursor.moveToNext(); 856 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 857 assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX)); 858 cursor.close(); 859 } 860 861 public void testReplace() { 862 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " 863 + "name TEXT, age INTEGER, address TEXT);"); 864 865 ContentValues values = new ContentValues(); 866 values.put("name", "Jack"); 867 values.put("age", 20); 868 values.put("address", "LA"); 869 mDatabase.replace(TABLE_NAME, "name", values); 870 871 Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, 872 null, null, null, null, null); 873 assertNotNull(cursor); 874 assertEquals(1, cursor.getCount()); 875 cursor.moveToFirst(); 876 int id = cursor.getInt(COLUMN_ID_INDEX); 877 assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); 878 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 879 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 880 cursor.close(); 881 882 values = new ContentValues(); 883 values.put("_id", id); 884 values.put("name", "Mike"); 885 values.put("age", 40); 886 values.put("address", "London"); 887 mDatabase.replace(TABLE_NAME, "name", values); 888 889 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 890 assertNotNull(cursor); 891 assertEquals(1, cursor.getCount()); // there is still ONLY 1 record. 892 cursor.moveToFirst(); 893 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 894 assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); 895 assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); 896 cursor.close(); 897 898 values = new ContentValues(); 899 values.put("name", "Jack"); 900 values.put("age", 20); 901 values.put("address", "LA"); 902 mDatabase.replaceOrThrow(TABLE_NAME, "name", values); 903 904 cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null); 905 assertNotNull(cursor); 906 assertEquals(2, cursor.getCount()); 907 cursor.moveToFirst(); 908 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 909 assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX)); 910 assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX)); 911 cursor.moveToNext(); 912 assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX)); 913 assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX)); 914 assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX)); 915 cursor.close(); 916 917 values = new ContentValues(); 918 values.put("Wrong Key", "Wrong value"); 919 try { 920 mDatabase.replaceOrThrow(TABLE_NAME, "name", values); 921 fail("should throw SQLException."); 922 } catch (SQLException e) { 923 } 924 } 925 926 public void testUpdate() { 927 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 928 929 mDatabase.execSQL("INSERT INTO test (data) VALUES ('string1');"); 930 mDatabase.execSQL("INSERT INTO test (data) VALUES ('string2');"); 931 mDatabase.execSQL("INSERT INTO test (data) VALUES ('string3');"); 932 933 String updatedString = "this is an updated test"; 934 ContentValues values = new ContentValues(1); 935 values.put("data", updatedString); 936 assertEquals(1, mDatabase.update("test", values, "_id=1", null)); 937 Cursor cursor = mDatabase.query("test", null, "_id=1", null, null, null, null); 938 assertNotNull(cursor); 939 assertEquals(1, cursor.getCount()); 940 cursor.moveToFirst(); 941 String value = cursor.getString(cursor.getColumnIndexOrThrow("data")); 942 assertEquals(updatedString, value); 943 cursor.close(); 944 } 945 946 public void testNeedUpgrade() { 947 mDatabase.setVersion(0); 948 assertTrue(mDatabase.needUpgrade(1)); 949 mDatabase.setVersion(1); 950 assertFalse(mDatabase.needUpgrade(1)); 951 } 952 953 public void testSetLocale() { 954 final String[] STRINGS = { 955 "c\u00f4t\u00e9", 956 "cote", 957 "c\u00f4te", 958 "cot\u00e9", 959 "boy", 960 "dog", 961 "COTE", 962 }; 963 964 mDatabase.execSQL("CREATE TABLE test (data TEXT COLLATE LOCALIZED);"); 965 for (String s : STRINGS) { 966 mDatabase.execSQL("INSERT INTO test VALUES('" + s + "');"); 967 } 968 969 mDatabase.setLocale(new Locale("en", "US")); 970 971 String sql = "SELECT data FROM test ORDER BY data COLLATE LOCALIZED ASC"; 972 Cursor cursor = mDatabase.rawQuery(sql, null); 973 assertNotNull(cursor); 974 ArrayList<String> items = new ArrayList<String>(); 975 while (cursor.moveToNext()) { 976 items.add(cursor.getString(0)); 977 } 978 String[] results = items.toArray(new String[items.size()]); 979 assertEquals(STRINGS.length, results.length); 980 cursor.close(); 981 982 // The database code currently uses PRIMARY collation strength, 983 // meaning that all versions of a character compare equal (regardless 984 // of case or accents), leaving the "cote" flavors in database order. 985 MoreAsserts.assertEquals(results, new String[] { 986 STRINGS[4], // "boy" 987 STRINGS[0], // sundry forms of "cote" 988 STRINGS[1], 989 STRINGS[2], 990 STRINGS[3], 991 STRINGS[6], // "COTE" 992 STRINGS[5], // "dog" 993 }); 994 } 995 996 public void testOnAllReferencesReleased() { 997 assertTrue(mDatabase.isOpen()); 998 mDatabase.releaseReference(); 999 assertFalse(mDatabase.isOpen()); 1000 } 1001 1002 public void testTransactionWithSQLiteTransactionListener() { 1003 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 1004 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 1005 1006 assertEquals(mTransactionListenerOnBeginCalled, false); 1007 assertEquals(mTransactionListenerOnCommitCalled, false); 1008 assertEquals(mTransactionListenerOnRollbackCalled, false); 1009 mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener()); 1010 1011 // Assert that the transcation has started 1012 assertEquals(mTransactionListenerOnBeginCalled, true); 1013 assertEquals(mTransactionListenerOnCommitCalled, false); 1014 assertEquals(mTransactionListenerOnRollbackCalled, false); 1015 1016 setNum(1); 1017 1018 // State shouldn't have changed 1019 assertEquals(mTransactionListenerOnBeginCalled, true); 1020 assertEquals(mTransactionListenerOnCommitCalled, false); 1021 assertEquals(mTransactionListenerOnRollbackCalled, false); 1022 1023 // commit the transaction 1024 mDatabase.setTransactionSuccessful(); 1025 mDatabase.endTransaction(); 1026 1027 // the listener should have been told that commit was called 1028 assertEquals(mTransactionListenerOnBeginCalled, true); 1029 assertEquals(mTransactionListenerOnCommitCalled, true); 1030 assertEquals(mTransactionListenerOnRollbackCalled, false); 1031 } 1032 1033 public void testRollbackTransactionWithSQLiteTransactionListener() { 1034 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 1035 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 1036 1037 assertEquals(mTransactionListenerOnBeginCalled, false); 1038 assertEquals(mTransactionListenerOnCommitCalled, false); 1039 assertEquals(mTransactionListenerOnRollbackCalled, false); 1040 mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener()); 1041 1042 // Assert that the transcation has started 1043 assertEquals(mTransactionListenerOnBeginCalled, true); 1044 assertEquals(mTransactionListenerOnCommitCalled, false); 1045 assertEquals(mTransactionListenerOnRollbackCalled, false); 1046 1047 setNum(1); 1048 1049 // State shouldn't have changed 1050 assertEquals(mTransactionListenerOnBeginCalled, true); 1051 assertEquals(mTransactionListenerOnCommitCalled, false); 1052 assertEquals(mTransactionListenerOnRollbackCalled, false); 1053 1054 // commit the transaction 1055 mDatabase.endTransaction(); 1056 1057 // the listener should have been told that commit was called 1058 assertEquals(mTransactionListenerOnBeginCalled, true); 1059 assertEquals(mTransactionListenerOnCommitCalled, false); 1060 assertEquals(mTransactionListenerOnRollbackCalled, true); 1061 } 1062 1063 private class TestSQLiteTransactionListener implements SQLiteTransactionListener { 1064 public void onBegin() { 1065 mTransactionListenerOnBeginCalled = true; 1066 } 1067 1068 public void onCommit() { 1069 mTransactionListenerOnCommitCalled = true; 1070 } 1071 1072 public void onRollback() { 1073 mTransactionListenerOnRollbackCalled = true; 1074 } 1075 } 1076 1077 public void testGroupConcat() { 1078 mDatabase.execSQL("CREATE TABLE test (i INT, j TEXT);"); 1079 1080 // insert 2 rows 1081 String sql = "INSERT INTO test (i) VALUES (?);"; 1082 SQLiteStatement insertStatement = mDatabase.compileStatement(sql); 1083 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); 1084 insertStatement.execute(); 1085 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 2); 1086 insertStatement.execute(); 1087 insertStatement.close(); 1088 1089 // make sure there are 2 rows in the table 1090 Cursor cursor = mDatabase.rawQuery("SELECT count(*) FROM test", null); 1091 assertNotNull(cursor); 1092 assertEquals(1, cursor.getCount()); 1093 cursor.moveToNext(); 1094 assertEquals(2, cursor.getInt(0)); 1095 cursor.close(); 1096 1097 // concatenate column j from all the rows. should return NULL 1098 cursor = mDatabase.rawQuery("SELECT group_concat(j, ' ') FROM test", null); 1099 assertNotNull(cursor); 1100 assertEquals(1, cursor.getCount()); 1101 cursor.moveToNext(); 1102 assertNull(cursor.getString(0)); 1103 cursor.close(); 1104 1105 // drop the table 1106 mDatabase.execSQL("DROP TABLE test;"); 1107 // should get no exceptions 1108 } 1109 1110 public void testSchemaChanges() { 1111 mDatabase.execSQL("CREATE TABLE test (i INT, j INT);"); 1112 1113 // at the beginning, there is no record in the database. 1114 Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null); 1115 assertNotNull(cursor); 1116 assertEquals(0, cursor.getCount()); 1117 cursor.close(); 1118 1119 String sql = "INSERT INTO test VALUES (?, ?);"; 1120 SQLiteStatement insertStatement = mDatabase.compileStatement(sql); 1121 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); 1122 DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2); 1123 insertStatement.execute(); 1124 insertStatement.close(); 1125 1126 // read the data from the table and make sure it is correct 1127 cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); 1128 assertNotNull(cursor); 1129 assertEquals(1, cursor.getCount()); 1130 cursor.moveToNext(); 1131 assertEquals(1, cursor.getInt(0)); 1132 assertEquals(2, cursor.getInt(1)); 1133 cursor.close(); 1134 1135 // alter the table and execute another statement 1136 mDatabase.execSQL("ALTER TABLE test ADD COLUMN k int;"); 1137 sql = "INSERT INTO test VALUES (?, ?, ?);"; 1138 insertStatement = mDatabase.compileStatement(sql); 1139 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3); 1140 DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4); 1141 DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5); 1142 insertStatement.execute(); 1143 insertStatement.close(); 1144 1145 // read the data from the table and make sure it is correct 1146 cursor = mDatabase.rawQuery("SELECT i,j,k FROM test", null); 1147 assertNotNull(cursor); 1148 assertEquals(2, cursor.getCount()); 1149 cursor.moveToNext(); 1150 assertEquals(1, cursor.getInt(0)); 1151 assertEquals(2, cursor.getInt(1)); 1152 assertNull(cursor.getString(2)); 1153 cursor.moveToNext(); 1154 assertEquals(3, cursor.getInt(0)); 1155 assertEquals(4, cursor.getInt(1)); 1156 assertEquals(5, cursor.getInt(2)); 1157 cursor.close(); 1158 1159 // make sure the old statement - which should *try to reuse* cached query plan - 1160 // still works 1161 cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); 1162 assertNotNull(cursor); 1163 assertEquals(2, cursor.getCount()); 1164 cursor.moveToNext(); 1165 assertEquals(1, cursor.getInt(0)); 1166 assertEquals(2, cursor.getInt(1)); 1167 cursor.moveToNext(); 1168 assertEquals(3, cursor.getInt(0)); 1169 assertEquals(4, cursor.getInt(1)); 1170 cursor.close(); 1171 1172 SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test"); 1173 deleteStatement.execute(); 1174 deleteStatement.close(); 1175 } 1176 1177 public void testSchemaChangesNewTable() { 1178 mDatabase.execSQL("CREATE TABLE test (i INT, j INT);"); 1179 1180 // at the beginning, there is no record in the database. 1181 Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null); 1182 assertNotNull(cursor); 1183 assertEquals(0, cursor.getCount()); 1184 cursor.close(); 1185 1186 String sql = "INSERT INTO test VALUES (?, ?);"; 1187 SQLiteStatement insertStatement = mDatabase.compileStatement(sql); 1188 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); 1189 DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2); 1190 insertStatement.execute(); 1191 insertStatement.close(); 1192 1193 // read the data from the table and make sure it is correct 1194 cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); 1195 assertNotNull(cursor); 1196 assertEquals(1, cursor.getCount()); 1197 cursor.moveToNext(); 1198 assertEquals(1, cursor.getInt(0)); 1199 assertEquals(2, cursor.getInt(1)); 1200 cursor.close(); 1201 1202 // alter the table and execute another statement 1203 mDatabase.execSQL("CREATE TABLE test_new (i INT, j INT, k INT);"); 1204 sql = "INSERT INTO test_new VALUES (?, ?, ?);"; 1205 insertStatement = mDatabase.compileStatement(sql); 1206 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3); 1207 DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4); 1208 DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5); 1209 insertStatement.execute(); 1210 insertStatement.close(); 1211 1212 // read the data from the table and make sure it is correct 1213 cursor = mDatabase.rawQuery("SELECT i,j,k FROM test_new", null); 1214 assertNotNull(cursor); 1215 assertEquals(1, cursor.getCount()); 1216 cursor.moveToNext(); 1217 assertEquals(3, cursor.getInt(0)); 1218 assertEquals(4, cursor.getInt(1)); 1219 assertEquals(5, cursor.getInt(2)); 1220 cursor.close(); 1221 1222 // make sure the old statement - which should *try to reuse* cached query plan - 1223 // still works 1224 cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); 1225 assertNotNull(cursor); 1226 assertEquals(1, cursor.getCount()); 1227 cursor.moveToNext(); 1228 assertEquals(1, cursor.getInt(0)); 1229 assertEquals(2, cursor.getInt(1)); 1230 cursor.close(); 1231 1232 SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test"); 1233 deleteStatement.execute(); 1234 deleteStatement.close(); 1235 1236 SQLiteStatement deleteStatement2 = mDatabase.compileStatement("DELETE FROM test_new"); 1237 deleteStatement2.execute(); 1238 deleteStatement2.close(); 1239 } 1240 1241 public void testSchemaChangesDropTable() { 1242 mDatabase.execSQL("CREATE TABLE test (i INT, j INT);"); 1243 1244 // at the beginning, there is no record in the database. 1245 Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null); 1246 assertNotNull(cursor); 1247 assertEquals(0, cursor.getCount()); 1248 cursor.close(); 1249 1250 String sql = "INSERT INTO test VALUES (?, ?);"; 1251 SQLiteStatement insertStatement = mDatabase.compileStatement(sql); 1252 DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1); 1253 DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2); 1254 insertStatement.execute(); 1255 insertStatement.close(); 1256 1257 // read the data from the table and make sure it is correct 1258 cursor = mDatabase.rawQuery("SELECT i,j FROM test", null); 1259 assertNotNull(cursor); 1260 assertEquals(1, cursor.getCount()); 1261 cursor.moveToNext(); 1262 assertEquals(1, cursor.getInt(0)); 1263 assertEquals(2, cursor.getInt(1)); 1264 } 1265 1266 /** 1267 * With sqlite's write-ahead-logging (WAL) enabled, readers get old version of data 1268 * from the table that a writer is modifying at the same time. 1269 * <p> 1270 * This method does the following to test this sqlite3 feature 1271 * <ol> 1272 * <li>creates a table in the database and populates it with 5 rows of data</li> 1273 * <li>do "select count(*) from this_table" and expect to receive 5</li> 1274 * <li>start a writer thread who BEGINs a transaction, INSERTs a single row 1275 * into this_table</li> 1276 * <li>writer stops the transaction at this point, kicks off a reader thread - which will 1277 * do the above SELECT query: "select count(*) from this_table"</li> 1278 * <li>this query should return value 5 - because writer is still in transaction and 1279 * sqlite returns OLD version of the data</li> 1280 * <li>writer ends the transaction, thus making the extra row now visible to everyone</li> 1281 * <li>reader is kicked off again to do the same query. this time query should 1282 * return value = 6 which includes the newly inserted row into this_table.</li> 1283 *</p> 1284 * @throws InterruptedException 1285 */ 1286 @LargeTest 1287 public void testReaderGetsOldVersionOfDataWhenWriterIsInXact() throws InterruptedException { 1288 // redo setup to create WAL enabled database 1289 closeAndDeleteDatabase(); 1290 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); 1291 boolean rslt = mDatabase.enableWriteAheadLogging(); 1292 assertTrue(rslt); 1293 assertNotNull(mDatabase); 1294 1295 // create a new table and insert 5 records into it. 1296 mDatabase.execSQL("CREATE TABLE t1 (i int, j int);"); 1297 mDatabase.beginTransaction(); 1298 for (int i = 0; i < 5; i++) { 1299 mDatabase.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""}); 1300 } 1301 mDatabase.setTransactionSuccessful(); 1302 mDatabase.endTransaction(); 1303 1304 // make sure a reader can read the above data 1305 ReaderQueryingData r1 = new ReaderQueryingData(5); 1306 r1.start(); 1307 Thread.yield(); 1308 try {r1.join();} catch (Exception e) {} 1309 1310 WriterDoingSingleTransaction w = new WriterDoingSingleTransaction(); 1311 w.start(); 1312 w.join(); 1313 } 1314 1315 private class WriterDoingSingleTransaction extends Thread { 1316 @Override public void run() { 1317 // start a transaction 1318 mDatabase.beginTransactionNonExclusive(); 1319 mDatabase.execSQL("insert into t1 values(?,?);", new String[] {"11", "11"}); 1320 assertTrue(mDatabase.isOpen()); 1321 1322 // while the writer is in a transaction, start a reader and make sure it can still 1323 // read 5 rows of data (= old data prior to the current transaction) 1324 ReaderQueryingData r1 = new ReaderQueryingData(5); 1325 r1.start(); 1326 try {r1.join();} catch (Exception e) {} 1327 1328 // now, have the writer do the select count(*) 1329 // it should execute on the same connection as this transaction 1330 // and count(*) should reflect the newly inserted row 1331 Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null); 1332 assertEquals(6, l.intValue()); 1333 1334 // end transaction 1335 mDatabase.setTransactionSuccessful(); 1336 mDatabase.endTransaction(); 1337 1338 // reader should now be able to read 6 rows = new data AFTER this transaction 1339 r1 = new ReaderQueryingData(6); 1340 r1.start(); 1341 try {r1.join();} catch (Exception e) {} 1342 } 1343 } 1344 1345 private class ReaderQueryingData extends Thread { 1346 private int count; 1347 /** 1348 * constructor with a param to indicate the number of rows expected to be read 1349 */ 1350 public ReaderQueryingData(int count) { 1351 this.count = count; 1352 } 1353 @Override public void run() { 1354 Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null); 1355 assertEquals(count, l.intValue()); 1356 } 1357 } 1358 1359 public void testExceptionsFromEnableWriteAheadLogging() { 1360 // attach a database 1361 // redo setup to create WAL enabled database 1362 closeAndDeleteDatabase(); 1363 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); 1364 1365 // attach a database and call enableWriteAheadLogging - should not be allowed 1366 mDatabase.execSQL("attach database ':memory:' as memoryDb"); 1367 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1368 assertFalse(mDatabase.enableWriteAheadLogging()); 1369 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1370 1371 // enableWriteAheadLogging on memory database is not allowed 1372 SQLiteDatabase db = SQLiteDatabase.create(null); 1373 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1374 assertFalse(db.enableWriteAheadLogging()); 1375 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1376 db.close(); 1377 } 1378 1379 public void testEnableThenDisableWriteAheadLogging() { 1380 // Enable WAL. 1381 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1382 assertTrue(mDatabase.enableWriteAheadLogging()); 1383 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1384 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1385 .equalsIgnoreCase("WAL")); 1386 1387 // Enabling when already enabled should have no observable effect. 1388 assertTrue(mDatabase.enableWriteAheadLogging()); 1389 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1390 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1391 .equalsIgnoreCase("WAL")); 1392 1393 // Disabling when there are no connections should work. 1394 mDatabase.disableWriteAheadLogging(); 1395 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1396 } 1397 1398 public void testDisableWriteAheadLogging() { 1399 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1400 mDatabase.disableWriteAheadLogging(); 1401 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1402 // Verify that default journal mode is set if WAL is disabled 1403 String defaultJournalMode = SQLiteGlobal.getDefaultJournalMode(); 1404 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1405 .equalsIgnoreCase(defaultJournalMode)); 1406 } 1407 1408 public void testEnableThenDisableWriteAheadLoggingUsingOpenFlag() { 1409 closeAndDeleteDatabase(); 1410 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile.getPath(), null, 1411 SQLiteDatabase.CREATE_IF_NECESSARY | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING, 1412 null); 1413 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1414 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1415 .equalsIgnoreCase("WAL")); 1416 1417 // Enabling when already enabled should have no observable effect. 1418 assertTrue(mDatabase.enableWriteAheadLogging()); 1419 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1420 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1421 .equalsIgnoreCase("WAL")); 1422 1423 // Disabling when there are no connections should work. 1424 mDatabase.disableWriteAheadLogging(); 1425 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1426 } 1427 1428 public void testEnableWriteAheadLoggingFromContextUsingModeFlag() { 1429 // Without the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens without WAL. 1430 closeAndDeleteDatabase(); 1431 mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME, 1432 Context.MODE_PRIVATE, null); 1433 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1434 1435 // With the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens with WAL. 1436 closeAndDeleteDatabase(); 1437 mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME, 1438 Context.MODE_PRIVATE | Context.MODE_ENABLE_WRITE_AHEAD_LOGGING, null); 1439 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1440 mDatabase.close(); 1441 } 1442 1443 public void testEnableWriteAheadLoggingShouldThrowIfTransactionInProgress() { 1444 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1445 String oldJournalMode = DatabaseUtils.stringForQuery( 1446 mDatabase, "PRAGMA journal_mode", null); 1447 1448 // Begin transaction. 1449 mDatabase.beginTransaction(); 1450 1451 try { 1452 // Attempt to enable WAL should fail. 1453 mDatabase.enableWriteAheadLogging(); 1454 fail("Expected IllegalStateException"); 1455 } catch (IllegalStateException ex) { 1456 // expected 1457 } 1458 1459 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1460 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1461 .equalsIgnoreCase(oldJournalMode)); 1462 } 1463 1464 public void testDisableWriteAheadLoggingShouldThrowIfTransactionInProgress() { 1465 // Enable WAL. 1466 assertFalse(mDatabase.isWriteAheadLoggingEnabled()); 1467 assertTrue(mDatabase.enableWriteAheadLogging()); 1468 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1469 1470 // Begin transaction. 1471 mDatabase.beginTransaction(); 1472 1473 try { 1474 // Attempt to disable WAL should fail. 1475 mDatabase.disableWriteAheadLogging(); 1476 fail("Expected IllegalStateException"); 1477 } catch (IllegalStateException ex) { 1478 // expected 1479 } 1480 1481 assertTrue(mDatabase.isWriteAheadLoggingEnabled()); 1482 assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null) 1483 .equalsIgnoreCase("WAL")); 1484 } 1485 1486 public void testEnableAndDisableForeignKeys() { 1487 // Initially off. 1488 assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null)); 1489 1490 // Enable foreign keys. 1491 mDatabase.setForeignKeyConstraintsEnabled(true); 1492 assertEquals(1, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null)); 1493 1494 // Disable foreign keys. 1495 mDatabase.setForeignKeyConstraintsEnabled(false); 1496 assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null)); 1497 1498 // Cannot configure foreign keys if there are transactions in progress. 1499 mDatabase.beginTransaction(); 1500 try { 1501 mDatabase.setForeignKeyConstraintsEnabled(true); 1502 fail("Expected IllegalStateException"); 1503 } catch (IllegalStateException ex) { 1504 // expected 1505 } 1506 assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null)); 1507 mDatabase.endTransaction(); 1508 1509 // Enable foreign keys should work again after transaction complete. 1510 mDatabase.setForeignKeyConstraintsEnabled(true); 1511 assertEquals(1, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null)); 1512 } 1513 1514 public void testOpenDatabaseLookasideConfig() { 1515 // First check that lookaside is enabled (except low-RAM devices) 1516 boolean expectDisabled = mContext.getSystemService(ActivityManager.class).isLowRamDevice(); 1517 verifyLookasideStats(expectDisabled); 1518 // Reopen test db with lookaside disabled 1519 mDatabase.close(); 1520 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1521 .setLookasideConfig(0, 0).build(); 1522 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1523 verifyLookasideStats(true); 1524 // Reopen test db with custom lookaside config 1525 mDatabase.close(); 1526 params = new SQLiteDatabase.OpenParams.Builder().setLookasideConfig(10000, 10).build(); 1527 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1528 // Lookaside is always disabled on low-RAM devices 1529 verifyLookasideStats(expectDisabled); 1530 } 1531 1532 public void testOpenParamsSetLookasideConfigValidation() { 1533 try { 1534 new SQLiteDatabase.OpenParams.Builder().setLookasideConfig(-1, 0).build(); 1535 fail("Negative slot size should be rejected"); 1536 } catch (IllegalArgumentException expected) { 1537 } 1538 try { 1539 new SQLiteDatabase.OpenParams.Builder().setLookasideConfig(0, -10).build(); 1540 fail("Negative slot count should be rejected"); 1541 } catch (IllegalArgumentException expected) { 1542 } 1543 } 1544 1545 private void verifyLookasideStats(boolean expectDisabled) { 1546 boolean dbStatFound = false; 1547 SQLiteDebug.PagerStats info = SQLiteDebug.getDatabaseInfo(); 1548 for (SQLiteDebug.DbStats dbStat : info.dbStats) { 1549 if (dbStat.dbName.endsWith(mDatabaseFile.getName())) { 1550 dbStatFound = true; 1551 Log.i(TAG, "Lookaside for " + dbStat.dbName + " " + dbStat.lookaside); 1552 if (expectDisabled) { 1553 assertTrue("lookaside slots count should be zero", dbStat.lookaside == 0); 1554 } else { 1555 assertTrue("lookaside slots count should be greater than zero", 1556 dbStat.lookaside > 0); 1557 } 1558 } 1559 } 1560 assertTrue("No dbstat found for " + mDatabaseFile.getName(), dbStatFound); 1561 } 1562 1563 public void testCloseIdleConnection() throws Exception { 1564 mDatabase.close(); 1565 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1566 .setIdleConnectionTimeout(5000).build(); 1567 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1568 // Wait a bit and check that connection is still open 1569 Thread.sleep(600); 1570 String output = getDbInfoOutput(); 1571 assertTrue("Connection #0 should be open. Output: " + output, 1572 output.contains("Connection #0:")); 1573 1574 // Now cause idle timeout and check that connection is closed 1575 // We wait up to 10 seconds, which is longer than required 1 s to accommodate for delays in 1576 // message processing when system is busy 1577 boolean connectionWasClosed = waitForConnectionToClose(20, 500); 1578 assertTrue("Connection #0 should be closed", connectionWasClosed); 1579 } 1580 1581 public void testNoCloseIdleConnectionForAttachDb() throws Exception { 1582 mDatabase.close(); 1583 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1584 .setIdleConnectionTimeout(50).build(); 1585 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1586 // Attach db and verify size of the list of attached databases (includes main db) 1587 assertEquals(1, mDatabase.getAttachedDbs().size()); 1588 mDatabase.execSQL("ATTACH DATABASE ':memory:' as memdb"); 1589 assertEquals(2, mDatabase.getAttachedDbs().size()); 1590 // Wait longer (500ms) to catch cases when timeout processing was delayed 1591 boolean connectionWasClosed = waitForConnectionToClose(5, 100); 1592 assertFalse("Connection #0 should be open", connectionWasClosed); 1593 } 1594 1595 public void testSetIdleConnectionTimeoutValidation() throws Exception { 1596 try { 1597 new SQLiteDatabase.OpenParams.Builder().setIdleConnectionTimeout(-1).build(); 1598 fail("Negative timeout should be rejected"); 1599 } catch (IllegalArgumentException expected) { 1600 } 1601 } 1602 1603 public void testDefaultJournalModeNotWAL() { 1604 String defaultJournalMode = SQLiteGlobal.getDefaultJournalMode(); 1605 assertFalse("Default journal mode should not be WAL", 1606 "WAL".equalsIgnoreCase(defaultJournalMode)); 1607 } 1608 1609 /** 1610 * Test that app can specify journal mode/synchronous mode 1611 */ 1612 public void testJournalModeSynchronousModeOverride() { 1613 mDatabase.close(); 1614 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1615 .setJournalMode("DELETE").setSynchronousMode("OFF").build(); 1616 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1617 1618 String journalMode = DatabaseUtils 1619 .stringForQuery(mDatabase, "PRAGMA journal_mode", null); 1620 1621 assertEquals("DELETE", journalMode.toUpperCase()); 1622 String syncMode = DatabaseUtils 1623 .stringForQuery(mDatabase, "PRAGMA synchronous", null); 1624 1625 assertEquals("0", syncMode); 1626 } 1627 1628 /** 1629 * Test that enableWriteAheadLogging is not affected by app's journal mode setting, 1630 * but app can still control synchronous mode. 1631 */ 1632 public void testEnableWalOverridesJournalModeSyncModePreserved() { 1633 mDatabase.close(); 1634 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1635 .setJournalMode("DELETE").setSynchronousMode("OFF").build(); 1636 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1637 mDatabase.enableWriteAheadLogging(); 1638 1639 String journalMode = DatabaseUtils 1640 .stringForQuery(mDatabase, "PRAGMA journal_mode", null); 1641 1642 assertEquals("WAL", journalMode.toUpperCase()); 1643 String syncMode = DatabaseUtils 1644 .stringForQuery(mDatabase, "PRAGMA synchronous", null); 1645 1646 assertEquals("0" /* OFF */, syncMode); 1647 } 1648 1649 /** 1650 * This test starts a transaction and verifies that other threads are blocked on 1651 * accessing the database. Waiting threads should be unblocked once transaction is complete. 1652 * 1653 * This is done to ensure that Compatibility WAL follows the original transaction semantics of 1654 * {@link SQLiteDatabase} instance when {@link SQLiteDatabase#ENABLE_WRITE_AHEAD_LOGGING} flag 1655 * is not set. 1656 */ 1657 public void testActiveTransactionIsBlocking() throws Exception { 1658 mDatabase.beginTransactionNonExclusive(); 1659 mDatabase.execSQL("CREATE TABLE t1 (i int);"); 1660 final List<Throwable> errors = new ArrayList<>(); 1661 1662 Thread readThread = new Thread( 1663 () -> { 1664 try { 1665 DatabaseUtils.longForQuery(mDatabase, "SELECT count(*) from t1", null); 1666 } catch (Throwable t) { 1667 Log.e(TAG, "ReadThread failed", t); 1668 errors.add(t); 1669 } 1670 }); 1671 readThread.start(); 1672 readThread.join(500L); 1673 assertTrue("ReadThread should be blocked while transaction is active", 1674 readThread.isAlive()); 1675 1676 mDatabase.setTransactionSuccessful(); 1677 mDatabase.endTransaction(); 1678 1679 readThread.join(500L); 1680 assertFalse("ReadThread should finish after transaction has ended", 1681 readThread.isAlive()); 1682 1683 assertTrue("ReadThread failed with errors: " + errors, errors.isEmpty()); 1684 } 1685 1686 public void testSqliteLibraryVersion() { 1687 // SQLite uses semantic versioning in a form of MAJOR.MINOR.PATCH. 1688 // Major/minor should match, while patch can be newer 1689 String fullVersionStr = DatabaseUtils 1690 .stringForQuery(mDatabase, "select sqlite_version()", null); 1691 int lastDot = fullVersionStr.lastIndexOf('.'); 1692 assertTrue( 1693 "Unexpected version format, expected MAJOR.MINOR.PATCH but was " + fullVersionStr, 1694 lastDot > 0); 1695 String majorMinor = fullVersionStr.substring(0, lastDot); 1696 assertEquals( 1697 "Expected SQLite library version " + EXPECTED_MAJOR_MINOR_VERSION + ", but was " 1698 + fullVersionStr, EXPECTED_MAJOR_MINOR_VERSION, majorMinor); 1699 int patchLevel = Integer.valueOf(fullVersionStr.substring(lastDot + 1)); 1700 assertTrue("Expected minimum patch level " + EXPECTED_MIN_PATCH_LEVEL + ", but was " 1701 + patchLevel, patchLevel >= EXPECTED_MIN_PATCH_LEVEL); 1702 } 1703 1704 } 1705
