1 package org.robolectric.shadows; 2 3 import static android.database.sqlite.SQLiteDatabase.OPEN_READWRITE; 4 import static java.nio.charset.StandardCharsets.UTF_8; 5 import static org.assertj.core.api.Assertions.assertThat; 6 import static org.junit.Assert.assertEquals; 7 import static org.junit.Assert.fail; 8 9 import android.content.ContentValues; 10 import android.database.Cursor; 11 import android.database.sqlite.SQLiteDatabase; 12 import android.database.sqlite.SQLiteException; 13 import android.os.CancellationSignal; 14 import android.os.OperationCanceledException; 15 import com.google.common.io.Files; 16 import java.io.File; 17 import java.io.IOException; 18 import java.nio.file.Path; 19 import java.util.ArrayList; 20 import java.util.List; 21 import java.util.concurrent.CountDownLatch; 22 import org.junit.After; 23 import org.junit.Before; 24 import org.junit.Test; 25 import org.junit.runner.RunWith; 26 import org.robolectric.RobolectricTestRunner; 27 import org.robolectric.RuntimeEnvironment; 28 import org.robolectric.res.FileFsFile; 29 import org.robolectric.util.TempDirectory; 30 import org.robolectric.util.TestUtil; 31 32 @RunWith(RobolectricTestRunner.class) 33 public class SQLiteDatabaseTest { 34 private SQLiteDatabase database; 35 private List<SQLiteDatabase> openDatabases = new ArrayList<>(); 36 private static final String ANY_VALID_SQL = "SELECT 1"; 37 private File databasePath; 38 39 @Before 40 public void setUp() throws Exception { 41 databasePath = RuntimeEnvironment.application.getDatabasePath("database.db"); 42 databasePath.getParentFile().mkdirs(); 43 44 database = openOrCreateDatabase(databasePath); 45 database.execSQL("CREATE TABLE table_name (\n" + 46 " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + 47 " first_column VARCHAR(255),\n" + 48 " second_column BINARY,\n" + 49 " name VARCHAR(255),\n" + 50 " big_int INTEGER\n" + 51 ");"); 52 53 database.execSQL("CREATE TABLE rawtable (\n" + 54 " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + 55 " first_column VARCHAR(255),\n" + 56 " second_column BINARY,\n" + 57 " name VARCHAR(255),\n" + 58 " big_int INTEGER\n" + 59 ");"); 60 61 database.execSQL("CREATE TABLE exectable (\n" + 62 " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + 63 " first_column VARCHAR(255),\n" + 64 " second_column BINARY,\n" + 65 " name VARCHAR(255),\n" + 66 " big_int INTEGER\n" + 67 ");"); 68 69 database.execSQL("CREATE TABLE blob_table (\n" + 70 " id INTEGER PRIMARY KEY,\n" + 71 " blob_col BLOB\n" + 72 ");"); 73 74 String stringColumnValue = "column_value"; 75 byte[] byteColumnValue = new byte[]{1, 2, 3}; 76 77 ContentValues values = new ContentValues(); 78 79 values.put("first_column", stringColumnValue); 80 values.put("second_column", byteColumnValue); 81 82 database.insert("rawtable", null, values); 83 //////////////////////////////////////////////// 84 String stringColumnValue2 = "column_value2"; 85 byte[] byteColumnValue2 = new byte[]{4, 5, 6}; 86 ContentValues values2 = new ContentValues(); 87 88 values2.put("first_column", stringColumnValue2); 89 values2.put("second_column", byteColumnValue2); 90 91 database.insert("rawtable", null, values2); 92 } 93 94 @After 95 public void tearDown() throws Exception { 96 for (SQLiteDatabase openDatabase : openDatabases) { 97 openDatabase.close(); 98 } 99 } 100 101 @Test 102 public void testInsertAndQuery() throws Exception { 103 String stringColumnValue = "column_value"; 104 byte[] byteColumnValue = new byte[]{1, 2, 3}; 105 106 ContentValues values = new ContentValues(); 107 108 values.put("first_column", stringColumnValue); 109 values.put("second_column", byteColumnValue); 110 111 database.insert("table_name", null, values); 112 113 Cursor cursor = database.query("table_name", new String[]{"second_column", "first_column"}, null, null, null, null, null); 114 115 assertThat(cursor.moveToFirst()).isTrue(); 116 117 byte[] byteValueFromDatabase = cursor.getBlob(0); 118 String stringValueFromDatabase = cursor.getString(1); 119 120 assertThat(stringValueFromDatabase).isEqualTo(stringColumnValue); 121 assertThat(byteValueFromDatabase).isEqualTo(byteColumnValue); 122 } 123 124 @Test 125 public void testInsertAndRawQuery() throws Exception { 126 String stringColumnValue = "column_value"; 127 byte[] byteColumnValue = new byte[]{1, 2, 3}; 128 129 ContentValues values = new ContentValues(); 130 131 values.put("first_column", stringColumnValue); 132 values.put("second_column", byteColumnValue); 133 134 database.insert("table_name", null, values); 135 136 Cursor cursor = database.rawQuery("select second_column, first_column from table_name", null); 137 138 assertThat(cursor.moveToFirst()).isTrue(); 139 140 byte[] byteValueFromDatabase = cursor.getBlob(0); 141 String stringValueFromDatabase = cursor.getString(1); 142 143 assertThat(stringValueFromDatabase).isEqualTo(stringColumnValue); 144 assertThat(byteValueFromDatabase).isEqualTo(byteColumnValue); 145 } 146 147 @Test(expected = android.database.SQLException.class) 148 public void testInsertOrThrowWithSQLException() { 149 ContentValues values = new ContentValues(); 150 values.put("id", 1); 151 152 database.insertOrThrow("table_name", null, values); 153 database.insertOrThrow("table_name", null, values); 154 } 155 156 @Test 157 public void testInsertOrThrow() { 158 String stringColumnValue = "column_value"; 159 byte[] byteColumnValue = new byte[]{1, 2, 3}; 160 ContentValues values = new ContentValues(); 161 values.put("first_column", stringColumnValue); 162 values.put("second_column", byteColumnValue); 163 database.insertOrThrow("table_name", null, values); 164 165 Cursor cursor = database.rawQuery("select second_column, first_column from table_name", null); 166 assertThat(cursor.moveToFirst()).isTrue(); 167 byte[] byteValueFromDatabase = cursor.getBlob(0); 168 String stringValueFromDatabase = cursor.getString(1); 169 assertThat(stringValueFromDatabase).isEqualTo(stringColumnValue); 170 assertThat(byteValueFromDatabase).isEqualTo(byteColumnValue); 171 } 172 173 @Test(expected = IllegalArgumentException.class) 174 public void testRawQueryThrowsIndex0NullException() throws Exception { 175 database.rawQuery("select second_column, first_column from rawtable WHERE `id` = ?", new String[]{null}); 176 } 177 178 @Test(expected = IllegalArgumentException.class) 179 public void testRawQueryThrowsIndex0NullException2() throws Exception { 180 database.rawQuery("select second_column, first_column from rawtable", new String[]{null}); 181 } 182 183 @Test 184 public void testRawQueryCountWithOneArgument() throws Exception { 185 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable WHERE `id` = ?", new String[]{"1"}); 186 assertThat(cursor.getCount()).isEqualTo(1); 187 } 188 189 @Test 190 public void testRawQueryCountWithNullArgs() throws Exception { 191 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable", null); 192 assertThat(cursor.getCount()).isEqualTo(2); 193 } 194 195 @Test 196 public void testRawQueryCountWithEmptyArguments() throws Exception { 197 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable", new String[]{}); 198 assertThat(cursor.getCount()).isEqualTo(2); 199 } 200 201 @Test(expected = IllegalArgumentException.class) 202 public void shouldThrowWhenArgumentsDoNotMatchQuery() throws Exception { 203 database.rawQuery("select second_column, first_column from rawtable", new String[]{"1"}); 204 } 205 206 @Test 207 public void testInsertWithException() { 208 ContentValues values = new ContentValues(); 209 210 assertEquals(-1, database.insert("table_that_doesnt_exist", null, values)); 211 } 212 213 214 @Test 215 public void testEmptyTable() throws Exception { 216 Cursor cursor = database.query("table_name", new String[]{"second_column", "first_column"}, null, null, null, null, null); 217 218 assertThat(cursor.moveToFirst()).isFalse(); 219 } 220 221 @Test 222 public void testInsertRowIdGeneration() throws Exception { 223 ContentValues values = new ContentValues(); 224 values.put("name", "Chuck"); 225 226 long id = database.insert("table_name", null, values); 227 228 assertThat(id).isNotEqualTo(0L); 229 } 230 231 @Test 232 public void testInsertKeyGeneration() throws Exception { 233 ContentValues values = new ContentValues(); 234 values.put("name", "Chuck"); 235 236 long key = database.insertWithOnConflict("table_name", null, values, SQLiteDatabase.CONFLICT_IGNORE); 237 238 assertThat(key).isNotEqualTo(0L); 239 } 240 241 @Test 242 public void testInsertEmptyBlobArgument() throws Exception { 243 ContentValues emptyBlobValues = new ContentValues(); 244 emptyBlobValues.put("id", 1); 245 emptyBlobValues.put("blob_col", new byte[]{}); 246 247 ContentValues nullBlobValues = new ContentValues(); 248 nullBlobValues.put("id", 2); 249 nullBlobValues.put("blob_col", (byte[])null); 250 251 long key = database.insertWithOnConflict("blob_table", null, emptyBlobValues, SQLiteDatabase.CONFLICT_FAIL); 252 assertThat(key).isNotEqualTo(0L); 253 key = database.insertWithOnConflict("blob_table", null, nullBlobValues, SQLiteDatabase.CONFLICT_FAIL); 254 assertThat(key).isNotEqualTo(0L); 255 256 Cursor cursor = database.query("blob_table", new String[]{"blob_col"}, "id=1", null, null, null, null); 257 try { 258 assertThat(cursor.moveToFirst()).isTrue(); 259 assertThat(cursor.getBlob(cursor.getColumnIndexOrThrow("blob_col"))).isNotNull(); 260 } finally { 261 cursor.close(); 262 } 263 264 cursor = database.query("blob_table", new String[]{"blob_col"}, "id=2", null, null, null, null); 265 try { 266 assertThat(cursor.moveToFirst()).isTrue(); 267 assertThat(cursor.getBlob(cursor.getColumnIndexOrThrow("blob_col"))).isNull(); 268 } finally { 269 cursor.close(); 270 } 271 272 } 273 274 @Test 275 public void testUpdate() throws Exception { 276 addChuck(); 277 278 assertThat(updateName(1234L, "Buster")).isEqualTo(1); 279 280 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 281 assertThat(cursor.moveToFirst()).isTrue(); 282 assertThat(cursor.getCount()).isEqualTo(1); 283 284 assertIdAndName(cursor, 1234L, "Buster"); 285 } 286 287 @Test 288 public void testUpdateNoMatch() throws Exception { 289 addChuck(); 290 291 assertThat(updateName(5678L, "Buster")).isEqualTo(0); 292 293 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 294 assertThat(cursor.moveToFirst()).isTrue(); 295 assertThat(cursor.getCount()).isEqualTo(1); 296 297 assertIdAndName(cursor, 1234L, "Chuck"); 298 } 299 300 @Test 301 public void testUpdateAll() throws Exception { 302 addChuck(); 303 addJulie(); 304 305 assertThat(updateName("Belvedere")).isEqualTo(2); 306 307 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 308 assertThat(cursor.moveToFirst()).isTrue(); 309 assertThat(cursor.getCount()).isEqualTo(2); 310 311 assertIdAndName(cursor, 1234L, "Belvedere"); 312 assertThat(cursor.moveToNext()).isTrue(); 313 314 assertIdAndName(cursor, 1235L, "Belvedere"); 315 assertThat(cursor.isLast()).isTrue(); 316 assertThat(cursor.moveToNext()).isFalse(); 317 assertThat(cursor.isAfterLast()).isTrue(); 318 assertThat(cursor.moveToNext()).isFalse(); 319 } 320 321 @Test 322 public void testDelete() throws Exception { 323 addChuck(); 324 325 int deleted = database.delete("table_name", "id=1234", null); 326 assertThat(deleted).isEqualTo(1); 327 328 assertEmptyDatabase(); 329 } 330 331 @Test 332 public void testDeleteNoMatch() throws Exception { 333 addChuck(); 334 335 int deleted = database.delete("table_name", "id=5678", null); 336 assertThat(deleted).isEqualTo(0); 337 338 assertNonEmptyDatabase(); 339 } 340 341 @Test 342 public void testDeleteAll() throws Exception { 343 addChuck(); 344 addJulie(); 345 346 int deleted = database.delete("table_name", "1", null); 347 assertThat(deleted).isEqualTo(2); 348 349 assertEmptyDatabase(); 350 } 351 352 @Test 353 public void testExecSQL() throws Exception { 354 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 355 356 Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM table_name", null); 357 assertThat(cursor).isNotNull(); 358 assertThat(cursor.moveToNext()).isTrue(); 359 assertThat(cursor.getInt(0)).isEqualTo(1); 360 361 cursor = database.rawQuery("SELECT * FROM table_name", null); 362 assertThat(cursor).isNotNull(); 363 assertThat(cursor.moveToNext()).isTrue(); 364 365 assertThat(cursor.getInt(cursor.getColumnIndex("id"))).isEqualTo(1234); 366 assertThat(cursor.getString(cursor.getColumnIndex("name"))).isEqualTo("Chuck"); 367 } 368 369 @Test 370 public void testExecSQLParams() throws Exception { 371 database.execSQL("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 , UNIQUE (`name`)) ", new Object[]{}); 372 database.execSQL("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)", new Object[]{"Leg Press", 0}); 373 database.execSQL("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)", new Object[]{"Bench Press", 1}); 374 375 Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM `routine`", null); 376 assertThat(cursor).isNotNull(); 377 assertThat(cursor.moveToNext()).isTrue(); 378 assertThat(cursor.getInt(0)).isEqualTo(2); 379 380 cursor = database.rawQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`", null); 381 assertThat(cursor).isNotNull(); 382 assertThat(cursor.moveToNext()).isTrue(); 383 384 assertThat(cursor.getInt(cursor.getColumnIndex("id"))).isEqualTo(1); 385 assertThat(cursor.getInt(cursor.getColumnIndex("lastUsed"))).isEqualTo(0); 386 assertThat(cursor.getString(cursor.getColumnIndex("name"))).isEqualTo("Leg Press"); 387 388 assertThat(cursor.moveToNext()).isTrue(); 389 390 assertThat(cursor.getInt(cursor.getColumnIndex("id"))).isEqualTo(2); 391 assertThat(cursor.getInt(cursor.getColumnIndex("lastUsed"))).isEqualTo(1); 392 assertThat(cursor.getString(cursor.getColumnIndex("name"))).isEqualTo("Bench Press"); 393 } 394 395 @Test(expected = SQLiteException.class) 396 public void execSqlShouldThrowOnBadQuery() throws Exception { 397 database.execSQL("INSERT INTO table_name;"); // invalid SQL 398 } 399 400 @Test(expected = IllegalArgumentException.class) 401 public void testExecSQLExceptionParametersWithoutArguments() throws Exception { 402 database.execSQL("insert into exectable (first_column) values (?);", null); 403 } 404 405 @Test(expected = IllegalArgumentException.class) 406 public void testExecSQLWithNullBindArgs() throws Exception { 407 database.execSQL("insert into exectable (first_column) values ('sdfsfs');", null); 408 } 409 410 @Test(expected = IllegalArgumentException.class) 411 public void testExecSQLTooManyBindArguments() throws Exception { 412 database.execSQL("insert into exectable (first_column) values ('kjhk');", new String[]{"xxxx"}); 413 } 414 415 @Test 416 public void testExecSQLWithEmptyBindArgs() throws Exception { 417 database.execSQL("insert into exectable (first_column) values ('eff');", new String[]{}); 418 } 419 420 @Test 421 public void testExecSQLInsertNull() throws Exception { 422 String name = "nullone"; 423 424 database.execSQL("insert into exectable (first_column, name) values (?,?);", new String[]{null, name}); 425 426 Cursor cursor = database.rawQuery("select * from exectable WHERE `name` = ?", new String[]{name}); 427 cursor.moveToFirst(); 428 int firstIndex = cursor.getColumnIndex("first_column"); 429 int nameIndex = cursor.getColumnIndex("name"); 430 assertThat(cursor.getString(nameIndex)).isEqualTo(name); 431 assertThat(cursor.getString(firstIndex)).isEqualTo(null); 432 433 } 434 435 @Test 436 public void testExecSQLAutoIncrementSQLite() throws Exception { 437 database.execSQL("CREATE TABLE auto_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255));"); 438 439 ContentValues values = new ContentValues(); 440 values.put("name", "Chuck"); 441 442 long key = database.insert("auto_table", null, values); 443 assertThat(key).isNotEqualTo(0L); 444 445 long key2 = database.insert("auto_table", null, values); 446 assertThat(key2).isNotEqualTo(key); 447 } 448 449 @Test(expected = IllegalStateException.class) 450 public void testClose() throws Exception { 451 database.close(); 452 453 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 454 } 455 456 @Test 457 public void testIsOpen() throws Exception { 458 assertThat(database.isOpen()).isTrue(); 459 database.close(); 460 assertThat(database.isOpen()).isFalse(); 461 } 462 463 @Test 464 public void shouldStoreGreatBigHonkingIntegersCorrectly() throws Exception { 465 database.execSQL("INSERT INTO table_name(big_int) VALUES(1234567890123456789);"); 466 Cursor cursor = database.query("table_name", new String[]{"big_int"}, null, null, null, null, null); 467 assertThat(cursor.moveToFirst()).isTrue(); 468 assertEquals(1234567890123456789L, cursor.getLong(0)); 469 } 470 471 @Test 472 public void testSuccessTransaction() throws Exception { 473 database.beginTransaction(); 474 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 475 database.setTransactionSuccessful(); 476 database.endTransaction(); 477 478 Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM table_name", null); 479 assertThat(cursor.moveToNext()).isTrue(); 480 assertThat(cursor.getInt(0)).isEqualTo(1); 481 } 482 483 @Test 484 public void testFailureTransaction() throws Exception { 485 database.beginTransaction(); 486 487 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 488 489 final String select = "SELECT COUNT(*) FROM table_name"; 490 491 Cursor cursor = database.rawQuery(select, null); 492 assertThat(cursor.moveToNext()).isTrue(); 493 assertThat(cursor.getInt(0)).isEqualTo(1); 494 cursor.close(); 495 496 database.endTransaction(); 497 498 cursor = database.rawQuery(select, null); 499 assertThat(cursor.moveToNext()).isTrue(); 500 assertThat(cursor.getInt(0)).isEqualTo(0); 501 } 502 503 @Test 504 public void testSuccessNestedTransaction() throws Exception { 505 database.beginTransaction(); 506 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 507 database.beginTransaction(); 508 database.execSQL("INSERT INTO table_name (id, name) VALUES(12345, 'Julie');"); 509 database.setTransactionSuccessful(); 510 database.endTransaction(); 511 database.setTransactionSuccessful(); 512 database.endTransaction(); 513 514 Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM table_name", null); 515 assertThat(cursor.moveToNext()).isTrue(); 516 assertThat(cursor.getInt(0)).isEqualTo(2); 517 } 518 519 @Test 520 public void testFailureNestedTransaction() throws Exception { 521 database.beginTransaction(); 522 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 523 database.beginTransaction(); 524 database.execSQL("INSERT INTO table_name (id, name) VALUES(12345, 'Julie');"); 525 database.endTransaction(); 526 database.setTransactionSuccessful(); 527 database.endTransaction(); 528 529 Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM table_name", null); 530 assertThat(cursor.moveToNext()).isTrue(); 531 assertThat(cursor.getInt(0)).isEqualTo(0); 532 } 533 534 @Test 535 public void testTransactionAlreadySuccessful() { 536 database.beginTransaction(); 537 database.setTransactionSuccessful(); 538 try { 539 database.setTransactionSuccessful(); 540 fail("didn't receive the expected IllegalStateException"); 541 } catch (IllegalStateException e) { 542 assertThat(e.getMessage()).contains("transaction").contains("successful"); 543 } 544 } 545 546 @Test 547 public void testInTransaction() throws Exception { 548 assertThat(database.inTransaction()).isFalse(); 549 database.beginTransaction(); 550 assertThat(database.inTransaction()).isTrue(); 551 database.endTransaction(); 552 assertThat(database.inTransaction()).isFalse(); 553 } 554 555 @Test 556 public void testReplace() throws Exception { 557 long id = addChuck(); 558 assertThat(id).isNotEqualTo(-1L); 559 560 ContentValues values = new ContentValues(); 561 values.put("id", id); 562 values.put("name", "Norris"); 563 564 long replaceId = database.replace("table_name", null, values); 565 assertThat(replaceId).isEqualTo(id); 566 567 String query = "SELECT name FROM table_name where id = " + id; 568 Cursor cursor = executeQuery(query); 569 570 assertThat(cursor.moveToNext()).isTrue(); 571 assertThat(cursor.getString(cursor.getColumnIndex("name"))).isEqualTo("Norris"); 572 } 573 574 @Test 575 public void testReplaceIsReplacing() throws Exception { 576 final String query = "SELECT first_column FROM table_name WHERE id = "; 577 String stringValueA = "column_valueA"; 578 String stringValueB = "column_valueB"; 579 long id = 1; 580 581 ContentValues valuesA = new ContentValues(); 582 valuesA.put("id", id); 583 valuesA.put("first_column", stringValueA); 584 585 ContentValues valuesB = new ContentValues(); 586 valuesB.put("id", id); 587 valuesB.put("first_column", stringValueB); 588 589 long firstId = database.replaceOrThrow("table_name", null, valuesA); 590 Cursor firstCursor = executeQuery(query + firstId); 591 assertThat(firstCursor.moveToNext()).isTrue(); 592 long secondId = database.replaceOrThrow("table_name", null, valuesB); 593 Cursor secondCursor = executeQuery(query + secondId); 594 assertThat(secondCursor.moveToNext()).isTrue(); 595 596 assertThat(firstId).isEqualTo(id); 597 assertThat(secondId).isEqualTo(id); 598 assertThat(firstCursor.getString(0)).isEqualTo(stringValueA); 599 assertThat(secondCursor.getString(0)).isEqualTo(stringValueB); 600 } 601 602 @Test 603 public void shouldCreateDefaultCursorFactoryWhenNullFactoryPassedToRawQuery() throws Exception { 604 database.rawQueryWithFactory(null, ANY_VALID_SQL, null, null); 605 } 606 607 @Test 608 public void shouldCreateDefaultCursorFactoryWhenNullFactoryPassedToQuery() throws Exception { 609 database.queryWithFactory(null, false, "table_name", null, null, null, null, null, null, null); 610 } 611 612 @Test 613 public void shouldOpenExistingDatabaseFromFileSystemIfFileExists() throws Exception { 614 615 database.close(); 616 617 SQLiteDatabase db = SQLiteDatabase.openDatabase(databasePath.getAbsolutePath(), null, OPEN_READWRITE); 618 Cursor c = db.rawQuery("select * from rawtable", null); 619 assertThat(c).isNotNull(); 620 assertThat(c.getCount()).isEqualTo(2); 621 assertThat(db.isOpen()).isTrue(); 622 db.close(); 623 assertThat(db.isOpen()).isFalse(); 624 625 SQLiteDatabase reopened = SQLiteDatabase.openDatabase(databasePath.getAbsolutePath(), null, OPEN_READWRITE); 626 assertThat(reopened).isNotSameAs(db); 627 assertThat(reopened.isOpen()).isTrue(); 628 } 629 630 @Test(expected = SQLiteException.class) 631 public void shouldThrowIfFileDoesNotExist() throws Exception { 632 File testDb = new File("/i/do/not/exist"); 633 assertThat(testDb.exists()).isFalse(); 634 SQLiteDatabase.openOrCreateDatabase(testDb.getAbsolutePath(), null); 635 } 636 637 @Test 638 public void shouldUseInMemoryDatabaseWhenCallingCreate() throws Exception { 639 SQLiteDatabase db = SQLiteDatabase.create(null); 640 assertThat(db.isOpen()).isTrue(); 641 assertThat(db.getPath()).isEqualTo(":memory:"); 642 } 643 644 @Test 645 public void shouldSetAndGetVersion() throws Exception { 646 assertThat(database.getVersion()).isEqualTo(0); 647 database.setVersion(20); 648 assertThat(database.getVersion()).isEqualTo(20); 649 } 650 651 @Test 652 public void testTwoConcurrentDbConnections() throws Exception { 653 SQLiteDatabase db1 = openOrCreateDatabase("db1"); 654 SQLiteDatabase db2 = openOrCreateDatabase("db2"); 655 656 db1.execSQL("CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);"); 657 db2.execSQL("CREATE TABLE bar(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);"); 658 659 ContentValues d1 = new ContentValues(); 660 d1.put("data", "d1"); 661 662 ContentValues d2 = new ContentValues(); 663 d2.put("data", "d2"); 664 665 db1.insert("foo", null, d1); 666 db2.insert("bar", null, d2); 667 668 Cursor c = db1.rawQuery("select * from foo", null); 669 assertThat(c).isNotNull(); 670 assertThat(c.getCount()).isEqualTo(1); 671 assertThat(c.moveToNext()).isTrue(); 672 assertThat(c.getString(c.getColumnIndex("data"))).isEqualTo("d1"); 673 674 c = db2.rawQuery("select * from bar", null); 675 assertThat(c).isNotNull(); 676 assertThat(c.getCount()).isEqualTo(1); 677 assertThat(c.moveToNext()).isTrue(); 678 assertThat(c.getString(c.getColumnIndex("data"))).isEqualTo("d2"); 679 } 680 681 @Test(expected = SQLiteException.class) 682 public void testQueryThrowsSQLiteException() throws Exception { 683 SQLiteDatabase db1 = openOrCreateDatabase("db1"); 684 db1.query("FOO", null, null, null, null, null, null); 685 } 686 687 @Test(expected = SQLiteException.class) 688 public void testShouldThrowSQLiteExceptionIfOpeningNonexistentDatabase() { 689 SQLiteDatabase.openDatabase("/does/not/exist", null, OPEN_READWRITE); 690 } 691 692 @Test 693 public void testCreateAndDropTable() throws Exception { 694 SQLiteDatabase db = openOrCreateDatabase("db1"); 695 db.execSQL("CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);"); 696 Cursor c = db.query("FOO", null, null, null, null, null, null); 697 assertThat(c).isNotNull(); 698 c.close(); 699 db.close(); 700 db = openOrCreateDatabase("db1"); 701 db.execSQL("DROP TABLE IF EXISTS foo;"); 702 try { 703 c = db.query("FOO", null, null, null, null, null, null); 704 fail("expected no such table exception"); 705 } catch (SQLiteException e) { 706 // TODO 707 } 708 db.close(); 709 } 710 711 @Test 712 public void testCreateAndAlterTable() throws Exception { 713 SQLiteDatabase db = openOrCreateDatabase("db1"); 714 db.execSQL("CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);"); 715 Cursor c = db.query("FOO", null, null, null, null, null, null); 716 assertThat(c).isNotNull(); 717 c.close(); 718 db.close(); 719 db = openOrCreateDatabase("db1"); 720 db.execSQL("ALTER TABLE foo ADD COLUMN more TEXT NULL;"); 721 c = db.query("FOO", null, null, null, null, null, null); 722 assertThat(c).isNotNull(); 723 int moreIndex = c.getColumnIndex("more"); 724 assertThat(moreIndex).isGreaterThanOrEqualTo(0); 725 c.close(); 726 } 727 728 @Test 729 public void testDataInMemoryDatabaseIsPersistentAfterClose() throws Exception { 730 SQLiteDatabase db1 = openOrCreateDatabase("db1"); 731 db1.execSQL("CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);"); 732 ContentValues d1 = new ContentValues(); 733 d1.put("data", "d1"); 734 db1.insert("foo", null, d1); 735 db1.close(); 736 737 SQLiteDatabase db2 = openOrCreateDatabase("db1"); 738 Cursor c = db2.rawQuery("select * from foo", null); 739 assertThat(c).isNotNull(); 740 assertThat(c.getCount()).isEqualTo(1); 741 assertThat(c.moveToNext()).isTrue(); 742 assertThat(c.getString(c.getColumnIndex("data"))).isEqualTo("d1"); 743 } 744 745 @Test 746 public void testRawQueryWithFactoryAndCancellationSignal() throws Exception { 747 CancellationSignal signal = new CancellationSignal(); 748 749 Cursor cursor = database.rawQueryWithFactory(null, "select * from table_name", null, null, signal); 750 assertThat(cursor).isNotNull(); 751 assertThat(cursor.getColumnCount()).isEqualTo(5); 752 assertThat(cursor.isClosed()).isFalse(); 753 754 signal.cancel(); 755 756 try { 757 cursor.moveToNext(); 758 fail("did not get cancellation signal"); 759 } catch (OperationCanceledException e) { 760 // expected 761 } 762 } 763 764 @Test 765 public void shouldThrowWhenForeignKeysConstraintIsViolated() { 766 database.execSQL("CREATE TABLE master (master_value INTEGER)"); 767 database.execSQL("CREATE TABLE slave (master_value INTEGER REFERENCES master(master_value))"); 768 database.execSQL("PRAGMA foreign_keys=ON"); 769 try { 770 database.execSQL("INSERT INTO slave(master_value) VALUES (1)"); 771 fail("Foreign key constraint is violated but exception is not thrown"); 772 } catch (SQLiteException e) { 773 assertThat(e.getCause()).hasMessageContaining("foreign"); 774 } 775 } 776 777 778 @Test 779 public void shouldBeAbleToBeUsedFromDifferentThread() { 780 final CountDownLatch sync = new CountDownLatch(1); 781 final Throwable[] error = {null}; 782 783 new Thread() { 784 @Override 785 public void run() { 786 try { 787 executeQuery("select * from table_name"); 788 } catch (Throwable e) { 789 e.printStackTrace(); 790 error[0] = e; 791 } finally { 792 sync.countDown(); 793 } 794 } 795 } 796 .start(); 797 798 try { 799 sync.await(); 800 } catch (InterruptedException e) { 801 throw new RuntimeException(e); 802 } 803 804 assertThat(error[0]).isNull(); 805 } 806 807 808 private Cursor executeQuery(String query) { 809 return database.rawQuery(query, null); 810 } 811 812 private long addChuck() { 813 return addPerson(1234L, "Chuck"); 814 } 815 816 private long addJulie() { 817 return addPerson(1235L, "Julie"); 818 } 819 820 private long addPerson(long id, String name) { 821 ContentValues values = new ContentValues(); 822 values.put("id", id); 823 values.put("name", name); 824 return database.insert("table_name", null, values); 825 } 826 827 private int updateName(long id, String name) { 828 ContentValues values = new ContentValues(); 829 values.put("name", name); 830 return database.update("table_name", values, "id=" + id, null); 831 } 832 833 private int updateName(String name) { 834 ContentValues values = new ContentValues(); 835 values.put("name", name); 836 return database.update("table_name", values, null, null); 837 } 838 839 private void assertIdAndName(Cursor cursor, long id, String name) { 840 long idValueFromDatabase; 841 String stringValueFromDatabase; 842 843 idValueFromDatabase = cursor.getLong(0); 844 stringValueFromDatabase = cursor.getString(1); 845 assertThat(idValueFromDatabase).isEqualTo(id); 846 assertThat(stringValueFromDatabase).isEqualTo(name); 847 } 848 849 private void assertEmptyDatabase() { 850 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 851 assertThat(cursor.moveToFirst()).isFalse(); 852 assertThat(cursor.isClosed()).isFalse(); 853 assertThat(cursor.getCount()).isEqualTo(0); 854 } 855 856 private void assertNonEmptyDatabase() { 857 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 858 assertThat(cursor.moveToFirst()).isTrue(); 859 assertThat(cursor.getCount()).isNotEqualTo(0); 860 } 861 862 @Test 863 public void shouldAlwaysReturnCorrectIdFromInsert() throws Exception { 864 database.execSQL("CREATE TABLE table_A (\n" + 865 " _id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + 866 " id INTEGER DEFAULT 0\n" + 867 ");"); 868 869 database.execSQL("CREATE VIRTUAL TABLE new_search USING fts3 (id);"); 870 871 database.execSQL("CREATE TRIGGER t1 AFTER INSERT ON table_A WHEN new.id=0 BEGIN UPDATE table_A SET id=-new._id WHERE _id=new._id AND id=0; END;"); 872 database.execSQL("CREATE TRIGGER t2 AFTER INSERT ON table_A BEGIN INSERT INTO new_search (id) VALUES (new._id); END;"); 873 database.execSQL("CREATE TRIGGER t3 BEFORE UPDATE ON table_A BEGIN DELETE FROM new_search WHERE id MATCH old._id; END;"); 874 database.execSQL("CREATE TRIGGER t4 AFTER UPDATE ON table_A BEGIN INSERT INTO new_search (id) VALUES (new._id); END;"); 875 876 long[] returnedIds = new long[]{ 877 database.insert("table_A", "id", new ContentValues()), 878 database.insert("table_A", "id", new ContentValues()) 879 }; 880 881 Cursor c = database.query("table_A", new String[]{"_id"}, null, null, null, null, null); 882 assertThat(c).isNotNull(); 883 884 long[] actualIds = new long[c.getCount()]; 885 for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) { 886 actualIds[c.getPosition()] = c.getLong(c.getColumnIndexOrThrow("_id")); 887 } 888 c.close(); 889 890 assertThat(returnedIds).containsOnly(actualIds); 891 } 892 893 @Test 894 public void shouldCorrectlyReturnNullValues() { 895 database.execSQL("CREATE TABLE null_test (col_int INTEGER, col_text TEXT, col_real REAL, col_blob BLOB)"); 896 897 ContentValues data = new ContentValues(); 898 data.putNull("col_int"); 899 data.putNull("col_text"); 900 data.putNull("col_real"); 901 data.putNull("col_blob"); 902 assertThat(database.insert("null_test", null, data)).isGreaterThan(0); 903 904 Cursor nullValuesCursor = database.query("null_test", null, null, null, null, null, null); 905 nullValuesCursor.moveToFirst(); 906 final int colsCount = 4; 907 for (int i = 0; i < colsCount; i++) { 908 assertThat(nullValuesCursor.getType(i)).isEqualTo(Cursor.FIELD_TYPE_NULL); 909 assertThat(nullValuesCursor.getString(i)).isNull(); 910 } 911 assertThat(nullValuesCursor.getBlob(3)).isNull(); 912 } 913 914 @Test 915 public void shouldGetBlobFromString() { 916 ContentValues values = new ContentValues(); 917 values.put("first_column", "this is a string"); 918 database.insert("table_name", null, values); 919 920 Cursor data = database.query("table_name", new String[]{"first_column"}, null, null, null, null, null); 921 assertThat(data.getCount()).isEqualTo(1); 922 data.moveToFirst(); 923 assertThat(data.getBlob(0)).isEqualTo(values.getAsString("first_column").getBytes(UTF_8)); 924 } 925 926 ///////////////////// 927 928 private SQLiteDatabase openOrCreateDatabase(String name) { 929 return openOrCreateDatabase(RuntimeEnvironment.application.getDatabasePath(name)); 930 } 931 932 private SQLiteDatabase openOrCreateDatabase(File databasePath) { 933 SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(databasePath, null); 934 openDatabases.add(database); 935 return database; 936 } 937 } 938
