DatabaseCursorTest.java revision 7ce745248d4de0e6543a559c93423df899832100
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 @LargeTest 295 public void testManyRowsLong() throws Exception { 296 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);"); 297 298 final int count = 36799; 299 mDatabase.execSQL("BEGIN Transaction;"); 300 for (int i = 0; i < count; i++) { 301 mDatabase.execSQL("INSERT INTO test (data) VALUES (" + i + ");"); 302 } 303 mDatabase.execSQL("COMMIT;"); 304 305 Cursor c = mDatabase.query("test", new String[]{"data"}, null, null, null, null, null); 306 assertNotNull(c); 307 308 int i = 0; 309 while (c.moveToNext()) { 310 assertEquals(i, c.getInt(0)); 311 i++; 312 } 313 assertEquals(count, i); 314 assertEquals(count, c.getCount()); 315 316 Log.d("testManyRows", "count " + Integer.toString(i)); 317 c.close(); 318 } 319 320 @LargeTest 321 public void testManyRowsTxt() throws Exception { 322 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 323 StringBuilder sql = new StringBuilder(2100); 324 sql.append("INSERT INTO test (data) VALUES ('"); 325 Random random = new Random(System.currentTimeMillis()); 326 StringBuilder randomString = new StringBuilder(1979); 327 for (int i = 0; i < 1979; i++) { 328 randomString.append((random.nextInt() & 0xf) % 10); 329 } 330 sql.append(randomString); 331 sql.append("');"); 332 333 // if cursor window size changed, adjust this value too 334 final int count = 600; // more than two fillWindow needed 335 mDatabase.execSQL("BEGIN Transaction;"); 336 for (int i = 0; i < count; i++) { 337 mDatabase.execSQL(sql.toString()); 338 } 339 mDatabase.execSQL("COMMIT;"); 340 341 Cursor c = mDatabase.query("test", new String[]{"data"}, null, null, null, null, null); 342 assertNotNull(c); 343 344 int i = 0; 345 while (c.moveToNext()) { 346 assertEquals(randomString.toString(), c.getString(0)); 347 i++; 348 } 349 assertEquals(count, i); 350 assertEquals(count, c.getCount()); 351 c.close(); 352 } 353 354 @LargeTest 355 public void testManyRowsTxtLong() throws Exception { 356 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, txt TEXT, data INT);"); 357 358 Random random = new Random(System.currentTimeMillis()); 359 StringBuilder randomString = new StringBuilder(1979); 360 for (int i = 0; i < 1979; i++) { 361 randomString.append((random.nextInt() & 0xf) % 10); 362 } 363 364 // if cursor window size changed, adjust this value too 365 final int count = 600; 366 mDatabase.execSQL("BEGIN Transaction;"); 367 for (int i = 0; i < count; i++) { 368 StringBuilder sql = new StringBuilder(2100); 369 sql.append("INSERT INTO test (txt, data) VALUES ('"); 370 sql.append(randomString); 371 sql.append("','"); 372 sql.append(i); 373 sql.append("');"); 374 mDatabase.execSQL(sql.toString()); 375 } 376 mDatabase.execSQL("COMMIT;"); 377 378 Cursor c = mDatabase.query("test", new String[]{"txt", "data"}, null, null, null, null, null); 379 assertNotNull(c); 380 381 int i = 0; 382 while (c.moveToNext()) { 383 assertEquals(randomString.toString(), c.getString(0)); 384 assertEquals(i, c.getInt(1)); 385 i++; 386 } 387 assertEquals(count, i); 388 assertEquals(count, c.getCount()); 389 c.close(); 390 } 391 392 @MediumTest 393 public void testRequery() throws Exception { 394 populateDefaultTable(); 395 396 Cursor c = mDatabase.rawQuery("SELECT * FROM test", null); 397 assertNotNull(c); 398 assertEquals(3, c.getCount()); 399 c.deactivate(); 400 c.requery(); 401 assertEquals(3, c.getCount()); 402 c.close(); 403 } 404 405 @MediumTest 406 public void testRequeryWithSelection() throws Exception { 407 populateDefaultTable(); 408 409 Cursor c = mDatabase.rawQuery("SELECT data FROM test WHERE data = '" + sString1 + "'", 410 null); 411 assertNotNull(c); 412 assertEquals(1, c.getCount()); 413 assertTrue(c.moveToFirst()); 414 assertEquals(sString1, c.getString(0)); 415 c.deactivate(); 416 c.requery(); 417 assertEquals(1, c.getCount()); 418 assertTrue(c.moveToFirst()); 419 assertEquals(sString1, c.getString(0)); 420 c.close(); 421 } 422 423 @MediumTest 424 public void testRequeryWithSelectionArgs() throws Exception { 425 populateDefaultTable(); 426 427 Cursor c = mDatabase.rawQuery("SELECT data FROM test WHERE data = ?", 428 new String[]{sString1}); 429 assertNotNull(c); 430 assertEquals(1, c.getCount()); 431 assertTrue(c.moveToFirst()); 432 assertEquals(sString1, c.getString(0)); 433 c.deactivate(); 434 c.requery(); 435 assertEquals(1, c.getCount()); 436 assertTrue(c.moveToFirst()); 437 assertEquals(sString1, c.getString(0)); 438 c.close(); 439 } 440 441 @MediumTest 442 public void testRequeryWithAlteredSelectionArgs() throws Exception { 443 /** 444 * Test the ability of a subclass of SQLiteCursor to change its query arguments. 445 */ 446 populateDefaultTable(); 447 448 SQLiteDatabase.CursorFactory factory = new SQLiteDatabase.CursorFactory() { 449 public Cursor newCursor( 450 SQLiteDatabase db, SQLiteCursorDriver masterQuery, String editTable, 451 SQLiteQuery query) { 452 return new SQLiteCursor(db, masterQuery, editTable, query) { 453 @Override 454 public boolean requery() { 455 setSelectionArguments(new String[]{"2"}); 456 return super.requery(); 457 } 458 }; 459 } 460 }; 461 Cursor c = mDatabase.rawQueryWithFactory( 462 factory, "SELECT data FROM test WHERE _id <= ?", new String[]{"1"}, 463 null); 464 assertNotNull(c); 465 assertEquals(1, c.getCount()); 466 assertTrue(c.moveToFirst()); 467 assertEquals(sString1, c.getString(0)); 468 469 // Our hacked requery() changes the query arguments in the cursor. 470 c.requery(); 471 472 assertEquals(2, c.getCount()); 473 assertTrue(c.moveToFirst()); 474 assertEquals(sString1, c.getString(0)); 475 assertTrue(c.moveToNext()); 476 assertEquals(sString2, c.getString(0)); 477 478 // Test that setting query args on a deactivated cursor also works. 479 c.deactivate(); 480 c.requery(); 481 } 482 /** 483 * sometimes CursorWindow creation fails due to non-availability of memory create 484 * another CursorWindow object. One of the scenarios of its occurrence is when 485 * there are too many CursorWindow objects already opened by the process. 486 * This test is for that scenario. 487 */ 488 @LargeTest 489 public void testCursorWindowFailureWhenTooManyCursorWindowsLeftOpen() { 490 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 491 mDatabase.execSQL("INSERT INTO test values(1, 'test');"); 492 int N = 1024; 493 ArrayList<Cursor> cursorList = new ArrayList<Cursor>(); 494 // open many cursors until a failure occurs 495 for (int i = 0; i < N; i++) { 496 try { 497 Cursor cursor = mDatabase.rawQuery("select * from test", null); 498 cursor.getCount(); 499 cursorList.add(cursor); 500 } catch (CursorWindowAllocationException e) { 501 // got the exception we wanted 502 break; 503 } catch (Exception e) { 504 fail("unexpected exception: " + e.getMessage()); 505 e.printStackTrace(); 506 break; 507 } 508 } 509 for (Cursor c : cursorList) { 510 c.close(); 511 } 512 } 513} 514