165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori/* 265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * Copyright (C) 2010 The Android Open Source Project 365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * 465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * Licensed under the Apache License, Version 2.0 (the "License"); 565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * you may not use this file except in compliance with the License. 665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * You may obtain a copy of the License at 765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * 865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * http://www.apache.org/licenses/LICENSE-2.0 965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * 1065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * Unless required by applicable law or agreed to in writing, software 1165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * distributed under the License is distributed on an "AS IS" BASIS, 1265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 1365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * See the License for the specific language governing permissions and 1465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori * limitations under the License. 1565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori */ 1665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 1765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noripackage android.database.sqlite; 1865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 19b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport android.content.ContentValues; 2065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noriimport android.content.Context; 21b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport android.database.Cursor; 2265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noriimport android.test.AndroidTestCase; 23c2ce721fcf684189b7251a5ecbf386426490d68eVasu Noriimport android.test.suitebuilder.annotation.LargeTest; 2465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 2565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noriimport java.io.File; 26b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport java.util.HashSet; 27b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport java.util.Set; 2865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 2965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noripublic class SQLiteCursorTest extends AndroidTestCase { 3065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori private SQLiteDatabase mDatabase; 3165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori private File mDatabaseFile; 3265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori private static final String TABLE_NAME = "testCursor"; 3365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori @Override 3465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori protected void setUp() throws Exception { 3565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori super.setUp(); 3665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 3765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); 3865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabaseFile = new File(dbDir, "sqlitecursor_test.db"); 3965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori if (mDatabaseFile.exists()) { 4065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabaseFile.delete(); 4165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 4265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); 4365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertNotNull(mDatabase); 4465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // create a test table 4565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.execSQL("CREATE TABLE " + TABLE_NAME + " (i int, j int);"); 4665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 4765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 4865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori @Override 4965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori protected void tearDown() throws Exception { 5065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.close(); 5165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabaseFile.delete(); 5265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori super.tearDown(); 5365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 5465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 55790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori /** 56e5360fbf3efe85427f7e7f59afe7bbeddb4949acJeff Brown * this test could take a while to execute. so, designate it as LargeTest 57790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori */ 58790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori @LargeTest 59b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori public void testFillWindow() { 60b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // create schema 61b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori final String testTable = "testV"; 62b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 63b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.execSQL("CREATE TABLE " + testTable + " (col1 int, desc text not null);"); 64b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 65b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 66b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 67b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // populate the table with data 68b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // create a big string that will almost fit a page but not quite. 69b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // since sqlite wants to make sure each row is in a page, this string will allocate 70b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // a new database page for each row. 71b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori StringBuilder buff = new StringBuilder(); 72b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int i = 0; i < 500; i++) { 73b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori buff.append(i % 10 + ""); 74b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 75b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori ContentValues values = new ContentValues(); 76b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori values.put("desc", buff.toString()); 77b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 78b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // insert more than 1MB of data in the table. this should ensure that the entire tabledata 79b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // will need more than one CursorWindow 80b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int N = 5000; 81b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori Set<Integer> rows = new HashSet<Integer>(); 82b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 83b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int j = 0; j < N; j++) { 84b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori values.put("col1", j); 85b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.insert(testTable, null, values); 86b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori rows.add(j); // store in a hashtable so we can verify the results from cursor later on 87b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 88b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 89b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 90b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(N, rows.size()); 91b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori Cursor c1 = mDatabase.rawQuery("select * from " + testTable, null); 92b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(N, c1.getCount()); 93b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c1.close(); 94b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 95b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // scroll through ALL data in the table using a cursor. should cause multiple calls to 96b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // native_fill_window (and re-fills of the CursorWindow object) 97b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori Cursor c = mDatabase.query(testTable, new String[]{"col1", "desc"}, 98b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori null, null, null, null, null); 99b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int i = 0; 100b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori while (c.moveToNext()) { 101b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int val = c.getInt(0); 102b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.contains(val)); 103b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.remove(val)); 104b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 105b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // did I see all the rows in the table? 106b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.isEmpty()); 107b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 108b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // change data and make sure the cursor picks up new data & count 109b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori rows = new HashSet<Integer>(); 110b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 111b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int M = N + 1000; 112b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int j = 0; j < M; j++) { 113b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori rows.add(j); 114b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori if (j < N) { 115b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori continue; 116b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 117b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori values.put("col1", j); 118b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.insert(testTable, null, values); 119b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 120b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 121b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 122b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(M, rows.size()); 123b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.requery(); 124b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori i = 0; 125b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori while (c.moveToNext()) { 126b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int val = c.getInt(0); 127b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.contains(val)); 128b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.remove(val)); 129b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 130b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // did I see all data from the modified table 131b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.isEmpty()); 132b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 133b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // move cursor back to 1st row and scroll to about halfway in the result set 134b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // and then delete 75% of data - and then do requery 135b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.moveToFirst(); 136b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int K = N / 2; 137b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int p = 0; p < K && c.moveToNext(); p++) { 138b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // nothing to do - just scrolling to about half-point in the resultset 139b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 140b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 141b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.delete(testTable, "col1 < ?", new String[]{ (3 * M / 4) + ""}); 142b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 143b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 144b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.requery(); 145b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(M / 4, c.getCount()); 146b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori while (c.moveToNext()) { 147b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // just move the cursor to next row - to make sure it can go through the entire 148b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // resultset without any problems 149b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 150b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.close(); 151b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 15265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori} 153