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