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