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