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 Noriimport android.test.suitebuilder.annotation.SmallTest; 25b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport android.util.Log; 2665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 2765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noriimport java.io.File; 28b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport java.util.HashSet; 29b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Noriimport java.util.Set; 3065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 3165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Noripublic class SQLiteCursorTest extends AndroidTestCase { 3265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori private SQLiteDatabase mDatabase; 3365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori private File mDatabaseFile; 3465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori private static final String TABLE_NAME = "testCursor"; 3565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori @Override 3665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori protected void setUp() throws Exception { 3765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori super.setUp(); 3865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 3965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); 4065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabaseFile = new File(dbDir, "sqlitecursor_test.db"); 4165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori if (mDatabaseFile.exists()) { 4265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabaseFile.delete(); 4365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 4465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); 4565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertNotNull(mDatabase); 4665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // create a test table 4765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.execSQL("CREATE TABLE " + TABLE_NAME + " (i int, j int);"); 4865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 4965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 5065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori @Override 5165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori protected void tearDown() throws Exception { 5265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.close(); 5365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabaseFile.delete(); 5465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori super.tearDown(); 5565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 5665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori 5765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori @SmallTest 5865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori public void testQueryObjReassignment() { 5965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.enableWriteAheadLogging(); 6065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // have a few connections in the database connection pool 6165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori DatabaseConnectionPool pool = mDatabase.mConnectionPool; 6265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori pool.setMaxPoolSize(5); 6365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori SQLiteCursor cursor = 6465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori (SQLiteCursor) mDatabase.rawQuery("select * from " + TABLE_NAME, null); 6565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertNotNull(cursor); 6665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // it should use a pooled database connection 6765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori SQLiteDatabase db = cursor.getDatabase(); 6865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(db.mConnectionNum > 0); 6965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertFalse(mDatabase.equals(db)); 7065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertEquals(mDatabase, db.mParentConnObj); 7165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(pool.getConnectionList().contains(db)); 7265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(db.isOpen()); 7365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // do a requery. cursor should continue to use the above pooled connection 7465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori cursor.requery(); 7565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori SQLiteDatabase dbAgain = cursor.getDatabase(); 7665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertEquals(db, dbAgain); 7765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // disable WAL so that the pooled connection held by the above cursor is closed 7865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.disableWriteAheadLogging(); 7965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertFalse(db.isOpen()); 8065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertNull(mDatabase.mConnectionPool); 8165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // requery - which should make the cursor use mDatabase connection since the pooled 8265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // connection is no longer available 8365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori cursor.requery(); 8465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori SQLiteDatabase db1 = cursor.getDatabase(); 8565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(db1.mConnectionNum == 0); 8665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertEquals(mDatabase, db1); 8765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertNull(mDatabase.mConnectionPool); 8865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(db1.isOpen()); 8965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertFalse(mDatabase.equals(db)); 9065a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori // enable WAL and requery - this time a pooled connection should be used 9165a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori mDatabase.enableWriteAheadLogging(); 9265a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori cursor.requery(); 9365a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori db = cursor.getDatabase(); 9465a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(db.mConnectionNum > 0); 9565a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertFalse(mDatabase.equals(db)); 9665a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertEquals(mDatabase, db.mParentConnObj); 9765a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(mDatabase.mConnectionPool.getConnectionList().contains(db)); 9865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori assertTrue(db.isOpen()); 9965a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori } 100b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 101790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori /** 102790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori * this test could take a while to execute. so, designate it as LargetTest 103790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori */ 104790762ca8f4fa458b87f75e7e8cde965c9efe3e5Vasu Nori @LargeTest 105b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori public void testFillWindow() { 106b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // create schema 107b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori final String testTable = "testV"; 108b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 109b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.execSQL("CREATE TABLE " + testTable + " (col1 int, desc text not null);"); 110b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 111b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 112b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 113b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // populate the table with data 114b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // create a big string that will almost fit a page but not quite. 115b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // since sqlite wants to make sure each row is in a page, this string will allocate 116b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // a new database page for each row. 117b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori StringBuilder buff = new StringBuilder(); 118b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int i = 0; i < 500; i++) { 119b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori buff.append(i % 10 + ""); 120b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 121b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori ContentValues values = new ContentValues(); 122b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori values.put("desc", buff.toString()); 123b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 124b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // insert more than 1MB of data in the table. this should ensure that the entire tabledata 125b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // will need more than one CursorWindow 126b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int N = 5000; 127b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori Set<Integer> rows = new HashSet<Integer>(); 128b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 129b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int j = 0; j < N; j++) { 130b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori values.put("col1", j); 131b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.insert(testTable, null, values); 132b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori rows.add(j); // store in a hashtable so we can verify the results from cursor later on 133b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 134b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 135b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 136b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(N, rows.size()); 137b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori Cursor c1 = mDatabase.rawQuery("select * from " + testTable, null); 138b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(N, c1.getCount()); 139b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c1.close(); 140b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 141b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // scroll through ALL data in the table using a cursor. should cause multiple calls to 142b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // native_fill_window (and re-fills of the CursorWindow object) 143b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori Cursor c = mDatabase.query(testTable, new String[]{"col1", "desc"}, 144b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori null, null, null, null, null); 145b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int i = 0; 146b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori while (c.moveToNext()) { 147b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int val = c.getInt(0); 148b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.contains(val)); 149b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.remove(val)); 150b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 151b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // did I see all the rows in the table? 152b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.isEmpty()); 153b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 154b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // change data and make sure the cursor picks up new data & count 155b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori rows = new HashSet<Integer>(); 156b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 157b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int M = N + 1000; 158b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int j = 0; j < M; j++) { 159b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori rows.add(j); 160b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori if (j < N) { 161b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori continue; 162b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 163b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori values.put("col1", j); 164b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.insert(testTable, null, values); 165b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 166b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 167b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 168b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(M, rows.size()); 169b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.requery(); 170b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori i = 0; 171b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori while (c.moveToNext()) { 172b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int val = c.getInt(0); 173b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.contains(val)); 174b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.remove(val)); 175b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 176b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // did I see all data from the modified table 177b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertTrue(rows.isEmpty()); 178b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori 179b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // move cursor back to 1st row and scroll to about halfway in the result set 180b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // and then delete 75% of data - and then do requery 181b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.moveToFirst(); 182b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori int K = N / 2; 183b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori for (int p = 0; p < K && c.moveToNext(); p++) { 184b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // nothing to do - just scrolling to about half-point in the resultset 185b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 186b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.beginTransaction(); 187b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.delete(testTable, "col1 < ?", new String[]{ (3 * M / 4) + ""}); 188b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.setTransactionSuccessful(); 189b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori mDatabase.endTransaction(); 190b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.requery(); 191b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori assertEquals(M / 4, c.getCount()); 192b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori while (c.moveToNext()) { 193b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // just move the cursor to next row - to make sure it can go through the entire 194b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori // resultset without any problems 195b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 196b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori c.close(); 197b18f27dbf43ee9028a11cafbca23d3fa318e278bVasu Nori } 19865a8883f0e605bb8a73a692987b47ce5da632e72Vasu Nori} 199