DatabaseCursorTest.java revision 5793a17366997060b34d1877380980683bacb965
1/* 2 * Copyright (C) 2007 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 17package android.database; 18 19import dalvik.annotation.BrokenTest; 20import android.content.ContentValues; 21import android.content.Context; 22import android.database.Cursor; 23import android.database.CursorIndexOutOfBoundsException; 24import android.database.DataSetObserver; 25import android.database.DatabaseUtils; 26import android.database.sqlite.SQLiteCursor; 27import android.database.sqlite.SQLiteCursorDriver; 28import android.database.sqlite.SQLiteDatabase; 29import android.database.sqlite.SQLiteQuery; 30import android.database.sqlite.SQLiteStatement; 31import android.os.Looper; 32import android.test.AndroidTestCase; 33import android.test.PerformanceTestCase; 34import android.test.suitebuilder.annotation.LargeTest; 35import android.test.suitebuilder.annotation.MediumTest; 36import android.test.suitebuilder.annotation.SmallTest; 37import android.test.suitebuilder.annotation.Suppress; 38import android.util.Log; 39 40import java.io.File; 41import java.util.Arrays; 42import java.util.Random; 43 44import junit.framework.TestCase; 45 46public class DatabaseCursorTest extends AndroidTestCase implements PerformanceTestCase { 47 48 private static final String sString1 = "this is a test"; 49 private static final String sString2 = "and yet another test"; 50 private static final String sString3 = "this string is a little longer, but still a test"; 51 52 private static final int CURRENT_DATABASE_VERSION = 42; 53 private SQLiteDatabase mDatabase; 54 private File mDatabaseFile; 55 56 @Override 57 protected void setUp() throws Exception { 58 super.setUp(); 59 File dbDir = getContext().getDir("tests", Context.MODE_PRIVATE); 60 mDatabaseFile = new File(dbDir, "database_test.db"); 61 62 if (mDatabaseFile.exists()) { 63 mDatabaseFile.delete(); 64 } 65 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); 66 assertNotNull(mDatabase); 67 mDatabase.setVersion(CURRENT_DATABASE_VERSION); 68 } 69 70 @Override 71 protected void tearDown() throws Exception { 72 mDatabase.close(); 73 mDatabaseFile.delete(); 74 super.tearDown(); 75 } 76 77 public boolean isPerformanceOnly() { 78 return false; 79 } 80 81 // These test can only be run once. 82 public int startPerformance(Intermediates intermediates) { 83 return 1; 84 } 85 86 private void populateDefaultTable() { 87 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 88 89 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');"); 90 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');"); 91 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');"); 92 } 93 94 @MediumTest 95 public void testCursorUpdate() { 96 mDatabase.execSQL( 97 "CREATE TABLE test (_id INTEGER PRIMARY KEY, d INTEGER, s INTEGER);"); 98 for(int i = 0; i < 20; i++) { 99 mDatabase.execSQL("INSERT INTO test (d, s) VALUES (" + i + 100 "," + i%2 + ");"); 101 } 102 103 Cursor c = mDatabase.query("test", null, "s = 0", null, null, null, null); 104 int dCol = c.getColumnIndexOrThrow("d"); 105 int sCol = c.getColumnIndexOrThrow("s"); 106 107 int count = 0; 108 while (c.moveToNext()) { 109 assertTrue(c.updateInt(dCol, 3)); 110 count++; 111 } 112 assertEquals(10, count); 113 114 assertTrue(c.commitUpdates()); 115 116 assertTrue(c.requery()); 117 118 count = 0; 119 while (c.moveToNext()) { 120 assertEquals(3, c.getInt(dCol)); 121 count++; 122 } 123 124 assertEquals(10, count); 125 assertTrue(c.moveToFirst()); 126 assertTrue(c.deleteRow()); 127 assertEquals(9, c.getCount()); 128 c.close(); 129 } 130 131 @MediumTest 132 public void testBlob() throws Exception { 133 // create table 134 mDatabase.execSQL( 135 "CREATE TABLE test (_id INTEGER PRIMARY KEY, s TEXT, d REAL, l INTEGER, b BLOB);"); 136 // insert blob 137 Object[] args = new Object[4]; 138 139 byte[] blob = new byte[1000]; 140 byte value = 99; 141 Arrays.fill(blob, value); 142 args[3] = blob; 143 144 String s = new String("text"); 145 args[0] = s; 146 Double d = 99.9; 147 args[1] = d; 148 Long l = (long)1000; 149 args[2] = l; 150 151 String sql = "INSERT INTO test (s, d, l, b) VALUES (?,?,?,?)"; 152 mDatabase.execSQL(sql, args); 153 // use cursor to access blob 154 Cursor c = mDatabase.query("test", null, null, null, null, null, null); 155 c.moveToNext(); 156 ContentValues cv = new ContentValues(); 157 DatabaseUtils.cursorRowToContentValues(c, cv); 158 159 int bCol = c.getColumnIndexOrThrow("b"); 160 int sCol = c.getColumnIndexOrThrow("s"); 161 int dCol = c.getColumnIndexOrThrow("d"); 162 int lCol = c.getColumnIndexOrThrow("l"); 163 byte[] cBlob = c.getBlob(bCol); 164 assertTrue(Arrays.equals(blob, cBlob)); 165 assertEquals(s, c.getString(sCol)); 166 assertEquals((double)d, c.getDouble(dCol)); 167 assertEquals((long)l, c.getLong(lCol)); 168 169 // new byte[] 170 byte[] newblob = new byte[1000]; 171 value = 98; 172 Arrays.fill(blob, value); 173 174 c.updateBlob(bCol, newblob); 175 cBlob = c.getBlob(bCol); 176 assertTrue(Arrays.equals(newblob, cBlob)); 177 178 // commit 179 assertTrue(c.commitUpdates()); 180 assertTrue(c.requery()); 181 c.moveToNext(); 182 cBlob = c.getBlob(bCol); 183 assertTrue(Arrays.equals(newblob, cBlob)); 184 c.close(); 185 } 186 187 @MediumTest 188 public void testRealColumns() throws Exception { 189 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data REAL);"); 190 ContentValues values = new ContentValues(); 191 values.put("data", 42.11); 192 long id = mDatabase.insert("test", "data", values); 193 assertTrue(id > 0); 194 Cursor c = mDatabase.rawQuery("SELECT data FROM test", null); 195 assertNotNull(c); 196 assertTrue(c.moveToFirst()); 197 assertEquals(42.11, c.getDouble(0)); 198 c.close(); 199 } 200 201 @MediumTest 202 public void testCursor1() throws Exception { 203 populateDefaultTable(); 204 205 Cursor c = mDatabase.query("test", null, null, null, null, null, null); 206 207 int dataColumn = c.getColumnIndexOrThrow("data"); 208 209 // The cursor should ignore text before the last period when looking for a column. (This 210 // is a temporary hack in all implementations of getColumnIndex.) 211 int dataColumn2 = c.getColumnIndexOrThrow("junk.data"); 212 assertEquals(dataColumn, dataColumn2); 213 214 assertSame(3, c.getCount()); 215 216 assertTrue(c.isBeforeFirst()); 217 218 try { 219 c.getInt(0); 220 fail("CursorIndexOutOfBoundsException expected"); 221 } catch (CursorIndexOutOfBoundsException ex) { 222 // expected 223 } 224 225 c.moveToNext(); 226 assertEquals(1, c.getInt(0)); 227 228 String s = c.getString(dataColumn); 229 assertEquals(sString1, s); 230 231 c.moveToNext(); 232 s = c.getString(dataColumn); 233 assertEquals(sString2, s); 234 235 c.moveToNext(); 236 s = c.getString(dataColumn); 237 assertEquals(sString3, s); 238 239 c.moveToPosition(-1); 240 c.moveToNext(); 241 s = c.getString(dataColumn); 242 assertEquals(sString1, s); 243 244 c.moveToPosition(2); 245 s = c.getString(dataColumn); 246 assertEquals(sString3, s); 247 248 int i; 249 250 for (c.moveToFirst(), i = 0; !c.isAfterLast(); c.moveToNext(), i++) { 251 c.getInt(0); 252 } 253 254 assertEquals(3, i); 255 256 try { 257 c.getInt(0); 258 fail("CursorIndexOutOfBoundsException expected"); 259 } catch (CursorIndexOutOfBoundsException ex) { 260 // expected 261 } 262 c.close(); 263 } 264 265 @MediumTest 266 public void testCursor2() throws Exception { 267 populateDefaultTable(); 268 269 Cursor c = mDatabase.query("test", null, "_id > 1000", null, null, null, null); 270 assertEquals(0, c.getCount()); 271 assertTrue(c.isBeforeFirst()); 272 273 try { 274 c.getInt(0); 275 fail("CursorIndexOutOfBoundsException expected"); 276 } catch (CursorIndexOutOfBoundsException ex) { 277 // expected 278 } 279 280 int i; 281 for (c.moveToFirst(), i = 0; !c.isAfterLast(); c.moveToNext(), i++) { 282 c.getInt(0); 283 } 284 assertEquals(0, i); 285 try { 286 c.getInt(0); 287 fail("CursorIndexOutOfBoundsException expected"); 288 } catch (CursorIndexOutOfBoundsException ex) { 289 // expected 290 } 291 c.close(); 292 } 293 294 @MediumTest 295 public void testLargeField() throws Exception { 296 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 297 298 StringBuilder sql = new StringBuilder(2100); 299 sql.append("INSERT INTO test (data) VALUES ('"); 300 Random random = new Random(System.currentTimeMillis()); 301 StringBuilder randomString = new StringBuilder(1979); 302 for (int i = 0; i < 1979; i++) { 303 randomString.append((random.nextInt() & 0xf) % 10); 304 } 305 sql.append(randomString); 306 sql.append("');"); 307 mDatabase.execSQL(sql.toString()); 308 309 Cursor c = mDatabase.query("test", null, null, null, null, null, null); 310 assertNotNull(c); 311 assertEquals(1, c.getCount()); 312 313 assertTrue(c.moveToFirst()); 314 assertEquals(0, c.getPosition()); 315 String largeString = c.getString(c.getColumnIndexOrThrow("data")); 316 assertNotNull(largeString); 317 assertEquals(randomString.toString(), largeString); 318 c.close(); 319 } 320 321 class TestObserver extends DataSetObserver { 322 int total; 323 SQLiteCursor c; 324 boolean quit = false; 325 public TestObserver(int total_, SQLiteCursor cursor) { 326 c = cursor; 327 total = total_; 328 } 329 330 @Override 331 public void onChanged() { 332 int count = c.getCount(); 333 if (total == count) { 334 int i = 0; 335 while (c.moveToNext()) { 336 assertEquals(i, c.getInt(1)); 337 i++; 338 } 339 assertEquals(count, i); 340 quit = true; 341 Looper.myLooper().quit(); 342 } 343 } 344 345 @Override 346 public void onInvalidated() { 347 } 348 } 349 350 //@Large 351 @Suppress 352 public void testLoadingThreadDelayRegisterData() throws Exception { 353 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);"); 354 355 final int count = 505; 356 String sql = "INSERT INTO test (data) VALUES (?);"; 357 SQLiteStatement s = mDatabase.compileStatement(sql); 358 for (int i = 0; i < count; i++) { 359 s.bindLong(1, i); 360 s.execute(); 361 } 362 363 int maxRead = 500; 364 int initialRead = 5; 365 SQLiteCursor c = (SQLiteCursor)mDatabase.rawQuery("select * from test;", 366 null, initialRead, maxRead); 367 368 TestObserver observer = new TestObserver(count, c); 369 c.getCount(); 370 c.registerDataSetObserver(observer); 371 if (!observer.quit) { 372 Looper.loop(); 373 } 374 c.close(); 375 } 376 377 //@LargeTest 378 @BrokenTest("Consistently times out") 379 @Suppress 380 public void testLoadingThread() throws Exception { 381 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);"); 382 383 final int count = 50000; 384 String sql = "INSERT INTO test (data) VALUES (?);"; 385 SQLiteStatement s = mDatabase.compileStatement(sql); 386 for (int i = 0; i < count; i++) { 387 s.bindLong(1, i); 388 s.execute(); 389 } 390 391 int maxRead = 1000; 392 int initialRead = 5; 393 SQLiteCursor c = (SQLiteCursor)mDatabase.rawQuery("select * from test;", 394 null, initialRead, maxRead); 395 396 TestObserver observer = new TestObserver(count, c); 397 c.registerDataSetObserver(observer); 398 c.getCount(); 399 400 Looper.loop(); 401 c.close(); 402 } 403 404 //@LargeTest 405 @BrokenTest("Consistently times out") 406 @Suppress 407 public void testLoadingThreadClose() throws Exception { 408 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);"); 409 410 final int count = 1000; 411 String sql = "INSERT INTO test (data) VALUES (?);"; 412 SQLiteStatement s = mDatabase.compileStatement(sql); 413 for (int i = 0; i < count; i++) { 414 s.bindLong(1, i); 415 s.execute(); 416 } 417 418 int maxRead = 11; 419 int initialRead = 5; 420 SQLiteCursor c = (SQLiteCursor)mDatabase.rawQuery("select * from test;", 421 null, initialRead, maxRead); 422 423 TestObserver observer = new TestObserver(count, c); 424 c.registerDataSetObserver(observer); 425 c.getCount(); 426 c.close(); 427 } 428 429 @LargeTest 430 public void testLoadingThreadDeactivate() throws Exception { 431 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);"); 432 433 final int count = 1000; 434 String sql = "INSERT INTO test (data) VALUES (?);"; 435 SQLiteStatement s = mDatabase.compileStatement(sql); 436 for (int i = 0; i < count; i++) { 437 s.bindLong(1, i); 438 s.execute(); 439 } 440 441 int maxRead = 11; 442 int initialRead = 5; 443 SQLiteCursor c = (SQLiteCursor)mDatabase.rawQuery("select * from test;", 444 null, initialRead, maxRead); 445 446 TestObserver observer = new TestObserver(count, c); 447 c.registerDataSetObserver(observer); 448 c.getCount(); 449 c.deactivate(); 450 c.close(); 451 } 452 453 @LargeTest 454 public void testManyRowsLong() throws Exception { 455 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);"); 456 457 final int count = 36799; 458 mDatabase.execSQL("BEGIN Transaction;"); 459 for (int i = 0; i < count; i++) { 460 mDatabase.execSQL("INSERT INTO test (data) VALUES (" + i + ");"); 461 } 462 mDatabase.execSQL("COMMIT;"); 463 464 Cursor c = mDatabase.query("test", new String[]{"data"}, null, null, null, null, null); 465 assertNotNull(c); 466 467 int i = 0; 468 while (c.moveToNext()) { 469 assertEquals(i, c.getInt(0)); 470 i++; 471 } 472 assertEquals(count, i); 473 assertEquals(count, c.getCount()); 474 475 Log.d("testManyRows", "count " + Integer.toString(i)); 476 c.close(); 477 } 478 479 @LargeTest 480 public void testManyRowsTxt() throws Exception { 481 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 482 StringBuilder sql = new StringBuilder(2100); 483 sql.append("INSERT INTO test (data) VALUES ('"); 484 Random random = new Random(System.currentTimeMillis()); 485 StringBuilder randomString = new StringBuilder(1979); 486 for (int i = 0; i < 1979; i++) { 487 randomString.append((random.nextInt() & 0xf) % 10); 488 } 489 sql.append(randomString); 490 sql.append("');"); 491 492 // if cursor window size changed, adjust this value too 493 final int count = 600; // more than two fillWindow needed 494 mDatabase.execSQL("BEGIN Transaction;"); 495 for (int i = 0; i < count; i++) { 496 mDatabase.execSQL(sql.toString()); 497 } 498 mDatabase.execSQL("COMMIT;"); 499 500 Cursor c = mDatabase.query("test", new String[]{"data"}, null, null, null, null, null); 501 assertNotNull(c); 502 503 int i = 0; 504 while (c.moveToNext()) { 505 assertEquals(randomString.toString(), c.getString(0)); 506 i++; 507 } 508 assertEquals(count, i); 509 assertEquals(count, c.getCount()); 510 c.close(); 511 } 512 513 @LargeTest 514 public void testManyRowsTxtLong() throws Exception { 515 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, txt TEXT, data INT);"); 516 517 Random random = new Random(System.currentTimeMillis()); 518 StringBuilder randomString = new StringBuilder(1979); 519 for (int i = 0; i < 1979; i++) { 520 randomString.append((random.nextInt() & 0xf) % 10); 521 } 522 523 // if cursor window size changed, adjust this value too 524 final int count = 600; 525 mDatabase.execSQL("BEGIN Transaction;"); 526 for (int i = 0; i < count; i++) { 527 StringBuilder sql = new StringBuilder(2100); 528 sql.append("INSERT INTO test (txt, data) VALUES ('"); 529 sql.append(randomString); 530 sql.append("','"); 531 sql.append(i); 532 sql.append("');"); 533 mDatabase.execSQL(sql.toString()); 534 } 535 mDatabase.execSQL("COMMIT;"); 536 537 Cursor c = mDatabase.query("test", new String[]{"txt", "data"}, null, null, null, null, null); 538 assertNotNull(c); 539 540 int i = 0; 541 while (c.moveToNext()) { 542 assertEquals(randomString.toString(), c.getString(0)); 543 assertEquals(i, c.getInt(1)); 544 i++; 545 } 546 assertEquals(count, i); 547 assertEquals(count, c.getCount()); 548 c.close(); 549 } 550 551 @MediumTest 552 public void testRequery() throws Exception { 553 populateDefaultTable(); 554 555 Cursor c = mDatabase.rawQuery("SELECT * FROM test", null); 556 assertNotNull(c); 557 assertEquals(3, c.getCount()); 558 c.deactivate(); 559 c.requery(); 560 assertEquals(3, c.getCount()); 561 c.close(); 562 } 563 564 @MediumTest 565 public void testRequeryWithSelection() throws Exception { 566 populateDefaultTable(); 567 568 Cursor c = mDatabase.rawQuery("SELECT data FROM test WHERE data = '" + sString1 + "'", 569 null); 570 assertNotNull(c); 571 assertEquals(1, c.getCount()); 572 assertTrue(c.moveToFirst()); 573 assertEquals(sString1, c.getString(0)); 574 c.deactivate(); 575 c.requery(); 576 assertEquals(1, c.getCount()); 577 assertTrue(c.moveToFirst()); 578 assertEquals(sString1, c.getString(0)); 579 c.close(); 580 } 581 582 @MediumTest 583 public void testRequeryWithSelectionArgs() throws Exception { 584 populateDefaultTable(); 585 586 Cursor c = mDatabase.rawQuery("SELECT data FROM test WHERE data = ?", 587 new String[]{sString1}); 588 assertNotNull(c); 589 assertEquals(1, c.getCount()); 590 assertTrue(c.moveToFirst()); 591 assertEquals(sString1, c.getString(0)); 592 c.deactivate(); 593 c.requery(); 594 assertEquals(1, c.getCount()); 595 assertTrue(c.moveToFirst()); 596 assertEquals(sString1, c.getString(0)); 597 c.close(); 598 } 599 600 @MediumTest 601 public void testRequeryWithAlteredSelectionArgs() throws Exception { 602 /** 603 * Test the ability of a subclass of SQLiteCursor to change its query arguments. 604 */ 605 populateDefaultTable(); 606 607 SQLiteDatabase.CursorFactory factory = new SQLiteDatabase.CursorFactory() { 608 public Cursor newCursor( 609 SQLiteDatabase db, SQLiteCursorDriver masterQuery, String editTable, 610 SQLiteQuery query) { 611 return new SQLiteCursor(db, masterQuery, editTable, query) { 612 @Override 613 public boolean requery() { 614 setSelectionArguments(new String[]{"2"}); 615 return super.requery(); 616 } 617 }; 618 } 619 }; 620 Cursor c = mDatabase.rawQueryWithFactory( 621 factory, "SELECT data FROM test WHERE _id <= ?", new String[]{"1"}, 622 null); 623 assertNotNull(c); 624 assertEquals(1, c.getCount()); 625 assertTrue(c.moveToFirst()); 626 assertEquals(sString1, c.getString(0)); 627 628 // Our hacked requery() changes the query arguments in the cursor. 629 c.requery(); 630 631 assertEquals(2, c.getCount()); 632 assertTrue(c.moveToFirst()); 633 assertEquals(sString1, c.getString(0)); 634 assertTrue(c.moveToNext()); 635 assertEquals(sString2, c.getString(0)); 636 637 // Test that setting query args on a deactivated cursor also works. 638 c.deactivate(); 639 c.requery(); 640 } 641} 642