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