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