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