1 package com.xtremelabs.robolectric.shadows; 2 3 import android.content.ContentValues; 4 import android.database.Cursor; 5 import android.database.sqlite.SQLiteDatabase; 6 import com.xtremelabs.robolectric.Robolectric; 7 import com.xtremelabs.robolectric.WithTestDefaultsRunner; 8 import org.junit.After; 9 import org.junit.Before; 10 import org.junit.Test; 11 import org.junit.runner.RunWith; 12 13 import java.sql.ResultSet; 14 import java.sql.SQLException; 15 import java.sql.Statement; 16 17 import static com.xtremelabs.robolectric.Robolectric.shadowOf; 18 import static org.hamcrest.CoreMatchers.equalTo; 19 import static org.hamcrest.CoreMatchers.not; 20 import static org.junit.Assert.*; 21 22 23 public abstract class DatabaseTestBase { 24 protected SQLiteDatabase database; 25 protected ShadowSQLiteDatabase shDatabase; 26 27 @Before 28 public void setUp() throws Exception { 29 database = SQLiteDatabase.openDatabase("path", null, 0); 30 shDatabase = Robolectric.shadowOf(database); 31 database.execSQL("CREATE TABLE table_name (\n" + 32 " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + 33 " first_column VARCHAR(255),\n" + 34 " second_column BINARY,\n" + 35 " name VARCHAR(255),\n" + 36 " big_int INTEGER\n" + 37 ");"); 38 39 database.execSQL("CREATE TABLE rawtable (\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 exectable (\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 String stringColumnValue = "column_value"; 56 byte[] byteColumnValue = new byte[]{1, 2, 3}; 57 58 ContentValues values = new ContentValues(); 59 60 values.put("first_column", stringColumnValue); 61 values.put("second_column", byteColumnValue); 62 63 database.insert("rawtable", null, values); 64 //////////////////////////////////////////////// 65 String stringColumnValue2 = "column_value2"; 66 byte[] byteColumnValue2 = new byte[]{4, 5, 6}; 67 ContentValues values2 = new ContentValues(); 68 69 values2.put("first_column", stringColumnValue2); 70 values2.put("second_column", byteColumnValue2); 71 72 database.insert("rawtable", null, values2); 73 } 74 75 76 @After 77 public void tearDown() throws Exception { 78 database.close(); 79 } 80 81 @Test() 82 public void testInsertAndQuery() throws Exception { 83 String stringColumnValue = "column_value"; 84 byte[] byteColumnValue = new byte[]{1, 2, 3}; 85 86 ContentValues values = new ContentValues(); 87 88 values.put("first_column", stringColumnValue); 89 values.put("second_column", byteColumnValue); 90 91 database.insert("table_name", null, values); 92 93 Cursor cursor = database.query("table_name", new String[]{"second_column", "first_column"}, null, null, null, null, null); 94 95 assertThat(cursor.moveToFirst(), equalTo(true)); 96 97 byte[] byteValueFromDatabase = cursor.getBlob(0); 98 String stringValueFromDatabase = cursor.getString(1); 99 100 assertThat(stringValueFromDatabase, equalTo(stringColumnValue)); 101 assertThat(byteValueFromDatabase, equalTo(byteColumnValue)); 102 } 103 104 @Test 105 public void testInsertAndRawQuery() throws Exception { 106 String stringColumnValue = "column_value"; 107 byte[] byteColumnValue = new byte[]{1, 2, 3}; 108 109 ContentValues values = new ContentValues(); 110 111 values.put("first_column", stringColumnValue); 112 values.put("second_column", byteColumnValue); 113 114 database.insert("table_name", null, values); 115 116 Cursor cursor = database.rawQuery("select second_column, first_column from table_name", null); 117 118 assertThat(cursor.moveToFirst(), equalTo(true)); 119 120 byte[] byteValueFromDatabase = cursor.getBlob(0); 121 String stringValueFromDatabase = cursor.getString(1); 122 123 assertThat(stringValueFromDatabase, equalTo(stringColumnValue)); 124 assertThat(byteValueFromDatabase, equalTo(byteColumnValue)); 125 } 126 127 @Test(expected = android.database.SQLException.class) 128 public void testInsertOrThrowWithSQLException() { 129 shDatabase.setThrowOnInsert(true); 130 database.insertOrThrow("table_name", null, new ContentValues()); 131 } 132 133 @Test 134 public void testInsertOrThrow() { 135 String stringColumnValue = "column_value"; 136 byte[] byteColumnValue = new byte[]{1, 2, 3}; 137 ContentValues values = new ContentValues(); 138 values.put("first_column", stringColumnValue); 139 values.put("second_column", byteColumnValue); 140 database.insertOrThrow("table_name", null, values); 141 142 Cursor cursor = database.rawQuery("select second_column, first_column from table_name", null); 143 assertThat(cursor.moveToFirst(), equalTo(true)); 144 byte[] byteValueFromDatabase = cursor.getBlob(0); 145 String stringValueFromDatabase = cursor.getString(1); 146 assertThat(stringValueFromDatabase, equalTo(stringColumnValue)); 147 assertThat(byteValueFromDatabase, equalTo(byteColumnValue)); 148 } 149 150 @Test(expected = IllegalArgumentException.class) 151 public void testRawQueryThrowsIndex0NullException() throws Exception { 152 database.rawQuery("select second_column, first_column from rawtable WHERE `id` = ?", new String[]{null}); 153 } 154 155 @Test(expected = IllegalArgumentException.class) 156 public void testRawQueryThrowsIndex0NullException2() throws Exception { 157 database.rawQuery("select second_column, first_column from rawtable", new String[]{null}); 158 } 159 160 @Test 161 public void testRawQueryCount() throws Exception { 162 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable WHERE `id` = ?", new String[]{"1"}); 163 assertThat(cursor.getCount(), equalTo(1)); 164 } 165 166 @Test 167 public void testRawQueryCount2() throws Exception { 168 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable", null); 169 assertThat(cursor.getCount(), equalTo(2)); 170 } 171 172 @Test 173 public void testRawQueryCount3() throws Exception { 174 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable", new String[]{}); 175 assertThat(cursor.getCount(), equalTo(2)); 176 } 177 /* 178 * Reason why testRawQueryCount4() and testRawQueryCount5() expects exceptions even though exceptions are not found in Android. 179 * 180 * The code in Android acts inconsistently under API version 2.1_r1 (and perhaps other APIs).. 181 * What happens is that rawQuery() remembers the selectionArgs of previous queries, 182 * and uses them if no selectionArgs are given in subsequent queries. 183 * If they were never given selectionArgs THEN they return empty cursors. 184 * 185 * 186 * if you run { 187 * db.rawQuery("select * from exercise WHERE name = ?",null); //this returns an empty cursor 188 * db.rawQuery("select * from exercise WHERE name = ?",new String[]{}); //this returns an empty cursor 189 * } 190 * 191 * but if you run { 192 * db.rawQuery("select * from exercise WHERE name = ?",new String[]{"Leg Press"}); //this returns 1 exercise named "Leg Press" 193 * db.rawQuery("select * from exercise WHERE name = ?",null); //this too returns 1 exercise named "Leg Press" 194 * db.rawQuery("select * from exercise WHERE name = ?",new String[]{}); //this too returns 1 exercise named "Leg Press" 195 * } 196 * 197 * so SQLite + Android work inconsistently (it maintains state that it should not) 198 * whereas H2 just throws an exception for not supplying the selectionArgs 199 * 200 * So the question is should Robolectric: 201 * 1) throw an exception, the way H2 does. 202 * 2) return an empty Cursor. 203 * 3) mimic Android\SQLite precisely and return inconsistent results based on previous state 204 * 205 * Returning an empty cursor all the time would be bad 206 * because Android doesn't always return an empty cursor. 207 * But just mimicing Android would not be helpful, 208 * since it would be less than obvious where the problem is coming from. 209 * One should just avoid ever calling a statement without selectionArgs (when one has a ? placeholder), 210 * so it is best to throw an Exception to let the programmer know that this isn't going to turn out well if they try to run it under Android. 211 * Because we are running in the context of a test we do not have to mimic Android precisely (if it is more helpful not to!), we just need to help 212 * the testing programmer figure out what is going on. 213 */ 214 215 @Test(expected = Exception.class) 216 public void testRawQueryCount4() throws Exception { 217 //Android and SQLite don't normally throw an exception here. See above explanation as to why Robolectric should. 218 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable WHERE `id` = ?", null); 219 } 220 221 @Test(expected = Exception.class) 222 public void testRawQueryCount5() throws Exception { 223 //Android and SQLite don't normally throw an exception here. See above explanation as to why Robolectric should. 224 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable WHERE `id` = ?", new String[]{}); 225 } 226 227 @Test(expected = android.database.sqlite.SQLiteException.class) 228 public void testRawQueryCount8() throws Exception { 229 Cursor cursor = database.rawQuery("select second_column, first_column from rawtable", new String[]{"1"}); 230 } 231 232 @Test 233 public void testInsertWithException() { 234 ContentValues values = new ContentValues(); 235 236 assertEquals(-1, database.insert("table_that_doesnt_exist", null, values)); 237 } 238 239 240 @Test 241 public void testEmptyTable() throws Exception { 242 Cursor cursor = database.query("table_name", new String[]{"second_column", "first_column"}, null, null, null, null, null); 243 244 assertThat(cursor.moveToFirst(), equalTo(false)); 245 } 246 247 @Test 248 public void testInsertRowIdGeneration() throws Exception { 249 ContentValues values = new ContentValues(); 250 values.put("name", "Chuck"); 251 252 long id = database.insert("table_name", null, values); 253 254 assertThat(id, not(equalTo(0L))); 255 } 256 257 @Test 258 public void testInsertKeyGeneration() throws Exception { 259 ContentValues values = new ContentValues(); 260 values.put("name", "Chuck"); 261 262 long key = database.insertWithOnConflict("table_name", null, values, SQLiteDatabase.CONFLICT_IGNORE); 263 264 assertThat(key, not(equalTo(0L))); 265 } 266 267 @Test 268 public void testUpdate() throws Exception { 269 addChuck(); 270 271 assertThat(updateName(1234L, "Buster"), equalTo(1)); 272 273 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 274 assertThat(cursor.moveToFirst(), equalTo(true)); 275 assertThat(cursor.getCount(), equalTo(1)); 276 277 assertIdAndName(cursor, 1234L, "Buster"); 278 } 279 280 @Test 281 public void testUpdateNoMatch() throws Exception { 282 addChuck(); 283 284 assertThat(updateName(5678L, "Buster"), equalTo(0)); 285 286 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 287 assertThat(cursor.moveToFirst(), equalTo(true)); 288 assertThat(cursor.getCount(), equalTo(1)); 289 290 assertIdAndName(cursor, 1234L, "Chuck"); 291 } 292 293 @Test 294 public void testUpdateAll() throws Exception { 295 addChuck(); 296 addJulie(); 297 298 assertThat(updateName("Belvedere"), equalTo(2)); 299 300 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 301 assertThat(cursor.moveToFirst(), equalTo(true)); 302 assertThat(cursor.getCount(), equalTo(2)); 303 304 assertIdAndName(cursor, 1234L, "Belvedere"); 305 assertThat(cursor.moveToNext(), equalTo(true)); 306 307 assertIdAndName(cursor, 1235L, "Belvedere"); 308 assertThat(cursor.isLast(), equalTo(true)); 309 assertThat(cursor.moveToNext(), equalTo(false)); 310 assertThat(cursor.isAfterLast(), equalTo(true)); 311 assertThat(cursor.moveToNext(), equalTo(false)); 312 } 313 314 @Test 315 public void testDelete() throws Exception { 316 addChuck(); 317 318 int deleted = database.delete("table_name", "id=1234", null); 319 assertThat(deleted, equalTo(1)); 320 321 assertEmptyDatabase(); 322 } 323 324 @Test 325 public void testDeleteNoMatch() throws Exception { 326 addChuck(); 327 328 int deleted = database.delete("table_name", "id=5678", null); 329 assertThat(deleted, equalTo(0)); 330 331 assertNonEmptyDatabase(); 332 } 333 334 @Test 335 public void testDeleteAll() throws Exception { 336 addChuck(); 337 addJulie(); 338 339 int deleted = database.delete("table_name", "1", null); 340 assertThat(deleted, equalTo(2)); 341 342 assertEmptyDatabase(); 343 } 344 345 346 @Test 347 public void testExecSQL() throws Exception { 348 Statement statement; 349 ResultSet resultSet; 350 351 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 352 353 statement = shadowOf(database).getConnection().createStatement(); 354 resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name"); 355 assertThat(resultSet.next(), equalTo(true)); 356 assertThat(resultSet.getInt(1), equalTo(1)); 357 358 statement = shadowOf(database).getConnection().createStatement(); 359 resultSet = statement.executeQuery("SELECT * FROM table_name"); 360 assertThat(resultSet.next(), equalTo(true)); 361 assertThat(resultSet.getInt(1), equalTo(1234)); 362 assertThat(resultSet.getString(4), equalTo("Chuck")); 363 } 364 365 @Test 366 public void testExecSQLParams() throws Exception { 367 Statement statement; 368 ResultSet resultSet; 369 370 database.execSQL("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 , UNIQUE (`name`)) ", new Object[]{}); 371 database.execSQL("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)", new Object[]{"Leg Press", 0}); 372 database.execSQL("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)", new Object[]{"Bench Press", 1}); 373 374 statement = shadowOf(database).getConnection().createStatement(); 375 resultSet = statement.executeQuery("SELECT COUNT(*) FROM `routine`"); 376 assertThat(resultSet.next(), equalTo(true)); 377 assertThat(resultSet.getInt(1), equalTo(2)); 378 379 statement = shadowOf(database).getConnection().createStatement(); 380 resultSet = statement.executeQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`"); 381 assertThat(resultSet.next(), equalTo(true)); 382 assertThat(resultSet.getInt(1), equalTo(1)); 383 assertThat(resultSet.getString(2), equalTo("Leg Press")); 384 assertThat(resultSet.getInt(3), equalTo(0)); 385 assertThat(resultSet.next(), equalTo(true)); 386 assertThat(resultSet.getLong(1), equalTo(2L)); 387 assertThat(resultSet.getString(2), equalTo("Bench Press")); 388 assertThat(resultSet.getInt(3), equalTo(1)); 389 } 390 391 @Test(expected = android.database.SQLException.class) 392 public void testExecSQLException() throws Exception { 393 database.execSQL("INSERT INTO table_name;"); // invalid SQL 394 } 395 396 @Test(expected = IllegalArgumentException.class) 397 public void testExecSQLException2() throws Exception { 398 database.execSQL("insert into exectable (first_column) values (?);", null); 399 } 400 401 @Test(expected = IllegalArgumentException.class) 402 public void testExecSQLException4() throws Exception { 403 database.execSQL("insert into exectable (first_column) values ('sdfsfs');", null); 404 } 405 406 @Test(expected = Exception.class) 407 public void testExecSQLException5() throws Exception { 408 //TODO: make this throw android.database.SQLException.class 409 database.execSQL("insert into exectable (first_column) values ('kjhk');", new String[]{"xxxx"}); 410 } 411 412 @Test(expected = Exception.class) 413 public void testExecSQLException6() throws Exception { 414 //TODO: make this throw android.database.SQLException.class 415 database.execSQL("insert into exectable (first_column) values ('kdfd');", new String[]{null}); 416 } 417 418 @Test 419 public void testExecSQL2() throws Exception { 420 database.execSQL("insert into exectable (first_column) values ('eff');", new String[]{}); 421 } 422 423 @Test 424 public void testExecSQLInsertNull() throws Exception { 425 String name = "nullone"; 426 427 database.execSQL("insert into exectable (first_column, name) values (?,?);", new String[]{null, name}); 428 429 Cursor cursor = database.rawQuery("select * from exectable WHERE `name` = ?", new String[]{name}); 430 cursor.moveToFirst(); 431 int firstIndex = cursor.getColumnIndex("first_column"); 432 int nameIndex = cursor.getColumnIndex("name"); 433 assertThat(cursor.getString(nameIndex), equalTo(name)); 434 assertThat(cursor.getString(firstIndex), equalTo(null)); 435 436 } 437 438 @Test(expected = Exception.class) 439 public void testExecSQLInsertNullShouldBeException() throws Exception { 440 //this inserts null in android, but it when it happens it is likely an error. H2 throws an exception. So we'll make Robolectric expect an Exception so that the error can be found. 441 442 database.delete("exectable", null, null); 443 444 Cursor cursor = database.rawQuery("select * from exectable", null); 445 cursor.moveToFirst(); 446 assertThat(cursor.getCount(), equalTo(0)); 447 448 database.execSQL("insert into exectable (first_column) values (?);", new String[]{}); 449 Cursor cursor2 = database.rawQuery("select * from exectable", new String[]{null}); 450 cursor.moveToFirst(); 451 assertThat(cursor2.getCount(), equalTo(1)); 452 453 } 454 455 @Test 456 public void testExecSQLAutoIncrementSQLite() throws Exception { 457 database.execSQL("CREATE TABLE auto_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255));"); 458 459 ContentValues values = new ContentValues(); 460 values.put("name", "Chuck"); 461 462 long key = database.insert("auto_table", null, values); 463 assertThat(key, not(equalTo(0L))); 464 465 long key2 = database.insert("auto_table", null, values); 466 assertThat(key2, not(equalTo(key))); 467 } 468 469 @Test(expected = IllegalStateException.class) 470 public void testClose() throws Exception { 471 database.close(); 472 473 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 474 } 475 476 @Test 477 public void testIsOpen() throws Exception { 478 assertThat(database.isOpen(), equalTo(true)); 479 database.close(); 480 assertThat(database.isOpen(), equalTo(false)); 481 } 482 483 @Test 484 public void shouldStoreGreatBigHonkinIntegersCorrectly() throws Exception { 485 database.execSQL("INSERT INTO table_name(big_int) VALUES(1234567890123456789);"); 486 Cursor cursor = database.query("table_name", new String[]{"big_int"}, null, null, null, null, null); 487 cursor.moveToFirst(); 488 assertEquals(1234567890123456789L, cursor.getLong(0)); 489 } 490 491 @Test 492 public void testSuccessTransaction() throws SQLException { 493 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 494 database.beginTransaction(); 495 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 496 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 497 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 498 database.setTransactionSuccessful(); 499 assertThat(shDatabase.isTransactionSuccess(), equalTo(true)); 500 database.endTransaction(); 501 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 502 503 Statement statement = shadowOf(database).getConnection().createStatement(); 504 ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name"); 505 assertThat(resultSet.next(), equalTo(true)); 506 assertThat(resultSet.getInt(1), equalTo(1)); 507 } 508 509 @Test 510 public void testFailureTransaction() throws Exception { 511 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 512 database.beginTransaction(); 513 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 514 515 database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');"); 516 517 Statement statement = shadowOf(database).getConnection().createStatement(); 518 final String select = "SELECT COUNT(*) FROM table_name"; 519 520 ResultSet rs = statement.executeQuery(select); 521 assertThat(rs.next(), equalTo(true)); 522 assertThat(rs.getInt(1), equalTo(1)); 523 rs.close(); 524 525 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 526 database.endTransaction(); 527 528 statement = shadowOf(database).getConnection().createStatement(); 529 rs = statement.executeQuery(select); 530 assertThat(rs.next(), equalTo(true)); 531 assertThat(rs.getInt(1), equalTo(0)); 532 533 assertThat(shDatabase.isTransactionSuccess(), equalTo(false)); 534 } 535 536 @Test 537 public void testTransactionAlreadySuccessful() { 538 database.beginTransaction(); 539 database.setTransactionSuccessful(); 540 try { 541 database.setTransactionSuccessful(); 542 fail("didn't receive the expected IllegalStateException"); 543 } catch (IllegalStateException e) { 544 assertThat(e.getMessage(), equalTo("transaction already successfully")); 545 } 546 } 547 548 @Test 549 public void testInTransaction() throws Exception { 550 assertThat( database.inTransaction(), equalTo(false) ); 551 database.beginTransaction(); 552 assertThat( database.inTransaction(), equalTo(true) ); 553 database.endTransaction(); 554 assertThat( database.inTransaction(), equalTo(false) ); 555 } 556 557 protected long addChuck() { 558 return addPerson(1234L, "Chuck"); 559 } 560 561 protected long addJulie() { 562 return addPerson(1235L, "Julie"); 563 } 564 565 protected long addPerson(long id, String name) { 566 ContentValues values = new ContentValues(); 567 values.put("id", id); 568 values.put("name", name); 569 return database.insert("table_name", null, values); 570 } 571 572 protected int updateName(long id, String name) { 573 ContentValues values = new ContentValues(); 574 values.put("name", name); 575 return database.update("table_name", values, "id=" + id, null); 576 } 577 578 protected int updateName(String name) { 579 ContentValues values = new ContentValues(); 580 values.put("name", name); 581 return database.update("table_name", values, null, null); 582 } 583 584 protected void assertIdAndName(Cursor cursor, long id, String name) { 585 long idValueFromDatabase; 586 String stringValueFromDatabase; 587 588 idValueFromDatabase = cursor.getLong(0); 589 stringValueFromDatabase = cursor.getString(1); 590 assertThat(idValueFromDatabase, equalTo(id)); 591 assertThat(stringValueFromDatabase, equalTo(name)); 592 } 593 594 protected void assertEmptyDatabase() { 595 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 596 assertThat(cursor.moveToFirst(), equalTo(false)); 597 assertThat(cursor.isClosed(), equalTo(false)); 598 assertThat(cursor.getCount(), equalTo(0)); 599 } 600 601 protected void assertNonEmptyDatabase() { 602 Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null); 603 assertThat(cursor.moveToFirst(), equalTo(true)); 604 assertThat(cursor.getCount(), not(equalTo(0))); 605 } 606 } 607
