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