1/*
2 * Copyright (C) 2006 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 static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_COLUMNNAME_INDEX;
20import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_DEFAULT_INDEX;
21
22import android.content.ContentValues;
23import android.content.Context;
24import android.database.sqlite.SQLiteDatabase;
25import android.database.sqlite.SQLiteDebug;
26import android.database.sqlite.SQLiteException;
27import android.os.Parcel;
28import android.support.test.InstrumentationRegistry;
29import android.support.test.uiautomator.UiDevice;
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.SmallTest;
35import android.util.Log;
36import android.util.Pair;
37
38import junit.framework.Assert;
39
40import java.io.File;
41import java.util.ArrayList;
42import java.util.Arrays;
43import java.util.List;
44import java.util.Locale;
45
46/**
47 * Usage:  bit FrameworksCoreTests:android.database.DatabaseGeneralTest
48 */
49public class DatabaseGeneralTest extends AndroidTestCase implements PerformanceTestCase {
50    private static final String TAG = "DatabaseGeneralTest";
51
52    private static final String sString1 = "this is a test";
53    private static final String sString2 = "and yet another test";
54    private static final String sString3 = "this string is a little longer, but still a test";
55    private static final String PHONE_NUMBER = "16175551212";
56
57    private static final int CURRENT_DATABASE_VERSION = 42;
58    private SQLiteDatabase mDatabase;
59    private File mDatabaseFile;
60
61    @Override
62    protected void setUp() throws Exception {
63        super.setUp();
64        File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE);
65        mDatabaseFile = new File(dbDir, "database_test.db");
66        if (mDatabaseFile.exists()) {
67            mDatabaseFile.delete();
68        }
69        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null);
70        assertNotNull(mDatabase);
71        mDatabase.setVersion(CURRENT_DATABASE_VERSION);
72    }
73
74    @Override
75    protected void tearDown() throws Exception {
76        mDatabase.close();
77        SQLiteDatabase.deleteDatabase(mDatabaseFile);
78        super.tearDown();
79    }
80
81    public boolean isPerformanceOnly() {
82        return false;
83    }
84
85    // These test can only be run once.
86    public int startPerformance(Intermediates intermediates) {
87        return 1;
88    }
89
90    private void populateDefaultTable() {
91        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
92
93        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');");
94        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');");
95        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');");
96    }
97
98    @MediumTest
99    public void testVersion() throws Exception {
100        assertEquals(CURRENT_DATABASE_VERSION, mDatabase.getVersion());
101        mDatabase.setVersion(11);
102        assertEquals(11, mDatabase.getVersion());
103    }
104
105    @MediumTest
106    public void testUpdate() throws Exception {
107        populateDefaultTable();
108
109        ContentValues values = new ContentValues(1);
110        values.put("data", "this is an updated test");
111        assertEquals(1, mDatabase.update("test", values, "_id=1", null));
112        Cursor c = mDatabase.query("test", null, "_id=1", null, null, null, null);
113        assertNotNull(c);
114        assertEquals(1, c.getCount());
115        c.moveToFirst();
116        String value = c.getString(c.getColumnIndexOrThrow("data"));
117        assertEquals("this is an updated test", value);
118    }
119
120    @MediumTest
121    public void testPhoneNumbersEqual() throws Exception {
122        mDatabase.execSQL("CREATE TABLE phones (num TEXT);");
123        mDatabase.execSQL("INSERT INTO phones (num) VALUES ('911');");
124        mDatabase.execSQL("INSERT INTO phones (num) VALUES ('5555');");
125        mDatabase.execSQL("INSERT INTO phones (num) VALUES ('+" + PHONE_NUMBER + "');");
126
127        String number;
128        Cursor c;
129
130        c = mDatabase.query("phones", null,
131                "PHONE_NUMBERS_EQUAL(num, '504-555-7683')", null, null, null, null);
132        assertTrue(c == null || c.getCount() == 0);
133        c.close();
134
135        c = mDatabase.query("phones", null,
136                "PHONE_NUMBERS_EQUAL(num, '911')", null, null, null, null);
137        assertNotNull(c);
138        assertEquals(1, c.getCount());
139        c.moveToFirst();
140        number = c.getString(c.getColumnIndexOrThrow("num"));
141        assertEquals("911", number);
142        c.close();
143
144        c = mDatabase.query("phones", null,
145                "PHONE_NUMBERS_EQUAL(num, '5555')", null, null, null, null);
146        assertNotNull(c);
147        assertEquals(1, c.getCount());
148        c.moveToFirst();
149        number = c.getString(c.getColumnIndexOrThrow("num"));
150        assertEquals("5555", number);
151        c.close();
152
153        c = mDatabase.query("phones", null,
154                "PHONE_NUMBERS_EQUAL(num, '180055555555')", null, null, null, null);
155        assertTrue(c == null || c.getCount() == 0);
156        c.close();
157
158        c = mDatabase.query("phones", null,
159                "PHONE_NUMBERS_EQUAL(num, '+" + PHONE_NUMBER + "')", null, null, null, null);
160        assertNotNull(c);
161        assertEquals(1, c.getCount());
162        c.moveToFirst();
163        number = c.getString(c.getColumnIndexOrThrow("num"));
164        assertEquals("+" + PHONE_NUMBER, number);
165        c.close();
166
167        c = mDatabase.query("phones", null,
168                "PHONE_NUMBERS_EQUAL(num, '+1 (617).555-1212')", null, null, null, null);
169        assertNotNull(c);
170        assertEquals(1, c.getCount());
171        c.moveToFirst();
172        number = c.getString(c.getColumnIndexOrThrow("num"));
173        assertEquals("+" + PHONE_NUMBER, number);
174        c.close();
175
176        c = mDatabase.query("phones", null,
177                "PHONE_NUMBERS_EQUAL(num, '" + PHONE_NUMBER + "')", null, null, null, null);
178        assertNotNull(c);
179        assertEquals(1, c.getCount());
180        c.moveToFirst();
181        number = c.getString(c.getColumnIndexOrThrow("num"));
182        assertEquals("+" + PHONE_NUMBER, number);
183        c.close();
184
185        /*
186        c = mDatabase.query("phones", null,
187                "PHONE_NUMBERS_EQUAL(num, '5551212')", null, null, null, null);
188        assertNotNull(c);
189        assertEquals(1, c.getCount());
190        c.moveToFirst();
191        number = c.getString(c.getColumnIndexOrThrow("num"));
192        assertEquals("+" + PHONE_NUMBER, number);
193        c.close();
194        */
195
196        c = mDatabase.query("phones", null,
197                "PHONE_NUMBERS_EQUAL(num, '011" + PHONE_NUMBER + "')", null, null, null, null);
198        assertNotNull(c);
199        assertEquals(1, c.getCount());
200        c.moveToFirst();
201        number = c.getString(c.getColumnIndexOrThrow("num"));
202        assertEquals("+" + PHONE_NUMBER, number);
203        c.close();
204
205        c = mDatabase.query("phones", null,
206                "PHONE_NUMBERS_EQUAL(num, '00" + PHONE_NUMBER + "')", null, null, null, null);
207        assertNotNull(c);
208        assertEquals(1, c.getCount());
209        c.moveToFirst();
210        number = c.getString(c.getColumnIndexOrThrow("num"));
211        assertEquals("+" + PHONE_NUMBER, number);
212        c.close();
213    }
214
215    private void phoneNumberCompare(String phone1, String phone2, boolean equal,
216            boolean useStrictComparation) {
217        String[] temporalPhoneNumbers = new String[2];
218        temporalPhoneNumbers[0] = phone1;
219        temporalPhoneNumbers[1] = phone2;
220
221        Cursor cursor = mDatabase.rawQuery(
222                String.format(
223                        "SELECT CASE WHEN PHONE_NUMBERS_EQUAL(?, ?, %d) " +
224                        "THEN 'equal' ELSE 'not equal' END",
225                        (useStrictComparation ? 1 : 0)),
226                temporalPhoneNumbers);
227        try {
228            assertNotNull(cursor);
229            assertTrue(cursor.moveToFirst());
230            if (equal) {
231                assertEquals(String.format("Unexpectedly, \"%s != %s\".", phone1, phone2),
232                        "equal", cursor.getString(0));
233            } else {
234                assertEquals(String.format("Unexpectedly, \"%s\" == \"%s\".", phone1, phone2),
235                        "not equal", cursor.getString(0));
236            }
237        } finally {
238            if (cursor != null) {
239                cursor.close();
240            }
241        }
242    }
243
244    private void assertPhoneNumberEqual(String phone1, String phone2) throws Exception {
245        assertPhoneNumberEqual(phone1, phone2, true);
246        assertPhoneNumberEqual(phone1, phone2, false);
247    }
248
249    private void assertPhoneNumberEqual(String phone1, String phone2, boolean useStrict)
250            throws Exception {
251        phoneNumberCompare(phone1, phone2, true, useStrict);
252    }
253
254    private void assertPhoneNumberNotEqual(String phone1, String phone2) throws Exception {
255        assertPhoneNumberNotEqual(phone1, phone2, true);
256        assertPhoneNumberNotEqual(phone1, phone2, false);
257    }
258
259    private void assertPhoneNumberNotEqual(String phone1, String phone2, boolean useStrict)
260            throws Exception {
261        phoneNumberCompare(phone1, phone2, false, useStrict);
262    }
263
264    /**
265     * Tests international matching issues for the PHONE_NUMBERS_EQUAL function.
266     *
267     * @throws Exception
268     */
269    @SmallTest
270    public void testPhoneNumbersEqualInternational() throws Exception {
271        assertPhoneNumberEqual("1", "1");
272        assertPhoneNumberEqual("123123", "123123");
273        assertPhoneNumberNotEqual("123123", "923123");
274        assertPhoneNumberNotEqual("123123", "123129");
275        assertPhoneNumberNotEqual("123123", "1231234");
276        assertPhoneNumberNotEqual("123123", "0123123", false);
277        assertPhoneNumberNotEqual("123123", "0123123", true);
278        assertPhoneNumberEqual("650-253-0000", "6502530000");
279        assertPhoneNumberEqual("650-253-0000", "650 253 0000");
280        assertPhoneNumberEqual("650 253 0000", "6502530000");
281        assertPhoneNumberEqual("+1 650-253-0000", "6502530000");
282        assertPhoneNumberEqual("001 650-253-0000", "6502530000");
283        assertPhoneNumberEqual("0111 650-253-0000", "6502530000");
284
285        // Russian trunk digit
286        assertPhoneNumberEqual("+79161234567", "89161234567");
287
288        // French trunk digit
289        assertPhoneNumberEqual("+33123456789", "0123456789");
290
291        // Hungarian two digit trunk (currently only works for loose comparison)
292        assertPhoneNumberEqual("+36 1 234 5678", "06 1234-5678", false);
293
294        // Mexican two digit trunk (currently only works for loose comparison)
295        assertPhoneNumberEqual("+52 55 1234 5678", "01 55 1234 5678", false);
296
297        // Mongolian two digit trunk (currently only works for loose comparison)
298        assertPhoneNumberEqual("+976 1 123 4567", "01 1 23 4567", false);
299        assertPhoneNumberEqual("+976 2 234 5678", "02 2 34 5678", false);
300
301        // Trunk digit for city codes in the Netherlands
302        assertPhoneNumberEqual("+31771234567", "0771234567");
303
304        // Test broken caller ID seen on call from Thailand to the US
305        assertPhoneNumberEqual("+66811234567", "166811234567");
306
307        // Test the same in-country number with different country codes
308        assertPhoneNumberNotEqual("+33123456789", "+1123456789");
309
310        // Test one number with country code and the other without
311        assertPhoneNumberEqual("5125551212", "+15125551212");
312
313        // Test two NANP numbers that only differ in the area code
314        assertPhoneNumberNotEqual("5125551212", "6505551212");
315
316        // Japanese phone numbers
317        assertPhoneNumberEqual("090-1234-5678", "+819012345678");
318        assertPhoneNumberEqual("090(1234)5678", "+819012345678");
319        assertPhoneNumberEqual("090-1234-5678", "+81-90-1234-5678");
320
321        // Equador
322        assertPhoneNumberEqual("+593(800)123-1234", "8001231234");
323        assertPhoneNumberEqual("+593-2-1234-123", "21234123");
324
325        // Two continuous 0 at the beginning of the phone string should not be
326        // treated as trunk prefix in the strict comparation.
327        assertPhoneNumberEqual("008001231234", "8001231234", false);
328        assertPhoneNumberNotEqual("008001231234", "8001231234", true);
329
330        // Confirm that the bug found before does not re-appear
331        assertPhoneNumberNotEqual("080-1234-5678", "+819012345678");
332
333        // Wrong prefix for Japan (currently only works for loose comparison)
334        assertPhoneNumberNotEqual("290-1234-5678", "+819012345678", false);
335        assertPhoneNumberNotEqual("+819012345678", "290-1234-5678", false);
336
337        // Wrong prefix for USA
338        assertPhoneNumberNotEqual("550-450-3605", "+14504503605");
339        assertPhoneNumberNotEqual("550-450-3605", "+15404503605");
340        assertPhoneNumberNotEqual("550-450-3605", "+15514503605");
341        assertPhoneNumberNotEqual("5504503605", "+14504503605");
342        assertPhoneNumberNotEqual("+14504503605", "550-450-3605");
343        assertPhoneNumberNotEqual("+15404503605", "550-450-3605");
344        assertPhoneNumberNotEqual("+15514503605", "550-450-3605");
345        assertPhoneNumberNotEqual("+14504503605", "5504503605");
346    }
347
348    @MediumTest
349    public void testCopyString() throws Exception {
350        mDatabase.execSQL("CREATE TABLE guess (numi INTEGER, numf FLOAT, str TEXT);");
351        mDatabase.execSQL(
352                "INSERT INTO guess (numi,numf,str) VALUES (0,0.0,'ZoomZoomZoomZoom');");
353        mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (2000000000,3.1415926535,'');");
354        String chinese = "\u4eac\u4ec5 \u5c3d\u5f84\u60ca";
355        String[] arr = new String[1];
356        arr[0] = chinese;
357        mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (-32768,-1.0,?)", arr);
358
359        Cursor c;
360
361        c = mDatabase.rawQuery("SELECT * FROM guess", null);
362
363        c.moveToFirst();
364
365        CharArrayBuffer buf = new CharArrayBuffer(14);
366
367        String compareTo = c.getString(c.getColumnIndexOrThrow("numi"));
368        int numiIdx = c.getColumnIndexOrThrow("numi");
369        int numfIdx = c.getColumnIndexOrThrow("numf");
370        int strIdx = c.getColumnIndexOrThrow("str");
371
372        c.copyStringToBuffer(numiIdx, buf);
373        assertEquals(1, buf.sizeCopied);
374        assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied));
375
376        c.copyStringToBuffer(strIdx, buf);
377        assertEquals("ZoomZoomZoomZoom", new String(buf.data, 0, buf.sizeCopied));
378
379        c.moveToNext();
380        compareTo = c.getString(numfIdx);
381
382        c.copyStringToBuffer(numfIdx, buf);
383        assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied));
384        c.copyStringToBuffer(strIdx, buf);
385        assertEquals(0, buf.sizeCopied);
386
387        c.moveToNext();
388        c.copyStringToBuffer(numfIdx, buf);
389        assertEquals(-1.0, Double.valueOf(
390                new String(buf.data, 0, buf.sizeCopied)).doubleValue());
391
392        c.copyStringToBuffer(strIdx, buf);
393        compareTo = c.getString(strIdx);
394        assertEquals(chinese, compareTo);
395
396        assertEquals(chinese, new String(buf.data, 0, buf.sizeCopied));
397        c.close();
398    }
399
400    @MediumTest
401    public void testSchemaChange1() throws Exception {
402        SQLiteDatabase db1 = mDatabase;
403        Cursor cursor;
404
405        db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
406
407        cursor = db1.query("db1", null, null, null, null, null, null);
408        assertNotNull("Cursor is null", cursor);
409
410        db1.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);");
411
412        assertEquals(0, cursor.getCount());
413        cursor.deactivate();
414    }
415
416    @MediumTest
417    public void testSchemaChange2() throws Exception {
418        mDatabase.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
419        Cursor cursor = mDatabase.query("db1", null, null, null, null, null, null);
420        assertNotNull(cursor);
421        assertEquals(0, cursor.getCount());
422        cursor.close();
423    }
424
425    @MediumTest
426    public void testSchemaChange3() throws Exception {
427        mDatabase.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
428        mDatabase.execSQL("INSERT INTO db1 (data) VALUES ('test');");
429        mDatabase.execSQL("ALTER TABLE db1 ADD COLUMN blah int;");
430        Cursor c = null;
431        try {
432            c = mDatabase.rawQuery("select blah from db1", null);
433        } catch (SQLiteException e) {
434            fail("unexpected exception: " + e.getMessage());
435        } finally {
436            if (c != null) {
437                c.close();
438            }
439        }
440    }
441
442    @MediumTest
443    public void testSelectionArgs() throws Exception {
444        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
445        ContentValues values = new ContentValues(1);
446        values.put("data", "don't forget to handled 's");
447        mDatabase.insert("test", "data", values);
448        values.clear();
449        values.put("data", "no apostrophes here");
450        mDatabase.insert("test", "data", values);
451        Cursor c = mDatabase.query(
452                "test", null, "data GLOB ?", new String[]{"*'*"}, null, null, null);
453        assertEquals(1, c.getCount());
454        assertTrue(c.moveToFirst());
455        assertEquals("don't forget to handled 's", c.getString(1));
456        c.deactivate();
457
458        // make sure code should checking null string properly so that
459        // it won't crash
460        try {
461            mDatabase.query("test", new String[]{"_id"},
462                    "_id=?", new String[]{null}, null, null, null);
463            fail("expected exception not thrown");
464        } catch (IllegalArgumentException e) {
465            // expected
466        }
467    }
468
469    @MediumTest
470    public void testTokenize() throws Exception {
471        Cursor c;
472        mDatabase.execSQL("CREATE TABLE tokens (" +
473                "token TEXT COLLATE unicode," +
474                "source INTEGER," +
475                "token_index INTEGER," +
476                "tag TEXT" +
477                ");");
478        mDatabase.execSQL("CREATE TABLE tokens_no_index (" +
479                "token TEXT COLLATE unicode," +
480                "source INTEGER" +
481                ");");
482
483        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
484                "SELECT _TOKENIZE(NULL, NULL, NULL, NULL)", null));
485        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
486                "SELECT _TOKENIZE('tokens', NULL, NULL, NULL)", null));
487        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
488                "SELECT _TOKENIZE('tokens', 10, NULL, NULL)", null));
489        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
490                "SELECT _TOKENIZE('tokens', 10, 'some string', NULL)", null));
491
492        Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase,
493                "SELECT _TOKENIZE('tokens', 11, 'some string ok', ' ', 1, 'foo')", null));
494        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
495                "SELECT _TOKENIZE('tokens', 11, 'second field', ' ', 1, 'bar')", null));
496
497        Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase,
498                "SELECT _TOKENIZE('tokens_no_index', 20, 'some string ok', ' ')", null));
499        Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase,
500                "SELECT _TOKENIZE('tokens_no_index', 21, 'foo bar baz', ' ', 0)", null));
501
502        // test Chinese
503        String chinese = new String("\u4eac\u4ec5 \u5c3d\u5f84\u60ca");
504        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
505                "SELECT _TOKENIZE('tokens', 12,'" + chinese + "', ' ', 1)", null));
506
507        String icustr = new String("Fr\u00e9d\u00e9ric Hj\u00f8nnev\u00e5g");
508
509        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
510                "SELECT _TOKENIZE('tokens', 13, '" + icustr + "', ' ', 1)", null));
511
512        Assert.assertEquals(9, DatabaseUtils.longForQuery(mDatabase,
513                "SELECT count(*) from tokens;", null));
514
515        String key = DatabaseUtils.getHexCollationKey("Frederic Hjonneva");
516        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
517                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
518        Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase,
519                "SELECT source from tokens where token GLOB '" + key + "*'", null));
520        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
521                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
522        key = DatabaseUtils.getHexCollationKey("Hjonneva");
523        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
524                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
525        Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase,
526                "SELECT source from tokens where token GLOB '" + key + "*'", null));
527        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
528                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
529
530        key = DatabaseUtils.getHexCollationKey("some string ok");
531        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
532                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
533        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
534                "SELECT source from tokens where token GLOB '" + key + "*'", null));
535        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
536                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
537        Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase,
538                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
539        key = DatabaseUtils.getHexCollationKey("string");
540        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
541                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
542        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
543                "SELECT source from tokens where token GLOB '" + key + "*'", null));
544        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
545                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
546        Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase,
547                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
548        key = DatabaseUtils.getHexCollationKey("ok");
549        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
550                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
551        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
552                "SELECT source from tokens where token GLOB '" + key + "*'", null));
553        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
554                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
555        Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase,
556                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
557
558        key = DatabaseUtils.getHexCollationKey("second field");
559        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
560                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
561        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
562                "SELECT source from tokens where token GLOB '" + key + "*'", null));
563        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
564                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
565        Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase,
566                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
567        key = DatabaseUtils.getHexCollationKey("field");
568        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
569                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
570        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
571                "SELECT source from tokens where token GLOB '" + key + "*'", null));
572        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
573                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
574        Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase,
575                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
576
577        key = DatabaseUtils.getHexCollationKey(chinese);
578        String[] a = new String[1];
579        a[0] = key;
580        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
581                "SELECT count(*) from tokens where token= ?", a));
582        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
583                "SELECT source from tokens where token= ?", a));
584        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
585                "SELECT token_index from tokens where token= ?", a));
586        a[0] += "*";
587        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
588             "SELECT count(*) from tokens where token GLOB ?", a));
589        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
590                "SELECT source from tokens where token GLOB ?", a));
591        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
592                "SELECT token_index from tokens where token GLOB ?", a));
593
594       Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
595                "SELECT count(*) from tokens where token= '" + key + "'", null));
596       Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
597               "SELECT source from tokens where token= '" + key + "'", null));
598       Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
599               "SELECT token_index from tokens where token= '" + key + "'", null));
600
601        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
602                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
603        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
604                "SELECT source from tokens where token GLOB '" + key + "*'", null));
605        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
606                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
607
608        key = DatabaseUtils.getHexCollationKey("\u4eac\u4ec5");
609        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
610                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
611        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
612                "SELECT source from tokens where token GLOB '" + key + "*'", null));
613        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
614                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
615
616        key = DatabaseUtils.getHexCollationKey("\u5c3d\u5f84\u60ca");
617        Log.d("DatabaseGeneralTest", "key = " + key);
618        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
619                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
620        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
621                "SELECT source from tokens where token GLOB '" + key + "*'", null));
622        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
623                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
624
625        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
626                "SELECT count(*) from tokens where token GLOB 'ab*'", null));
627
628        key = DatabaseUtils.getHexCollationKey("some string ok");
629        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
630                "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null));
631        Assert.assertEquals(20, DatabaseUtils.longForQuery(mDatabase,
632                "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null));
633
634        key = DatabaseUtils.getHexCollationKey("bar");
635        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
636                "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null));
637        Assert.assertEquals(21, DatabaseUtils.longForQuery(mDatabase,
638                "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null));
639    }
640
641    @MediumTest
642    public void testTransactions() throws Exception {
643        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
644        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
645
646        // Make sure that things work outside an explicit transaction.
647        setNum(1);
648        checkNum(1);
649
650        // Test a single-level transaction.
651        setNum(0);
652        mDatabase.beginTransaction();
653        setNum(1);
654        mDatabase.setTransactionSuccessful();
655        mDatabase.endTransaction();
656        checkNum(1);
657        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
658
659        // Test a rolled-back transaction.
660        setNum(0);
661        mDatabase.beginTransaction();
662        setNum(1);
663        mDatabase.endTransaction();
664        checkNum(0);
665        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
666
667        // We should get an error if we end a non-existent transaction.
668        assertThrowsIllegalState(new Runnable() { public void run() {
669            mDatabase.endTransaction();
670        }});
671
672        // We should get an error if a set a non-existent transaction as clean.
673        assertThrowsIllegalState(new Runnable() { public void run() {
674            mDatabase.setTransactionSuccessful();
675        }});
676
677        mDatabase.beginTransaction();
678        mDatabase.setTransactionSuccessful();
679        // We should get an error if we mark a transaction as clean twice.
680        assertThrowsIllegalState(new Runnable() { public void run() {
681            mDatabase.setTransactionSuccessful();
682        }});
683        // We should get an error if we begin a transaction after marking the parent as clean.
684        assertThrowsIllegalState(new Runnable() { public void run() {
685            mDatabase.beginTransaction();
686        }});
687        mDatabase.endTransaction();
688        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
689
690        // Test a two-level transaction.
691        setNum(0);
692        mDatabase.beginTransaction();
693        mDatabase.beginTransaction();
694        setNum(1);
695        mDatabase.setTransactionSuccessful();
696        mDatabase.endTransaction();
697        mDatabase.setTransactionSuccessful();
698        mDatabase.endTransaction();
699        checkNum(1);
700        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
701
702        // Test rolling back an inner transaction.
703        setNum(0);
704        mDatabase.beginTransaction();
705        mDatabase.beginTransaction();
706        setNum(1);
707        mDatabase.endTransaction();
708        mDatabase.setTransactionSuccessful();
709        mDatabase.endTransaction();
710        checkNum(0);
711        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
712
713        // Test rolling back an outer transaction.
714        setNum(0);
715        mDatabase.beginTransaction();
716        mDatabase.beginTransaction();
717        setNum(1);
718        mDatabase.setTransactionSuccessful();
719        mDatabase.endTransaction();
720        mDatabase.endTransaction();
721        checkNum(0);
722        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
723    }
724
725    private void setNum(int num) {
726        mDatabase.execSQL("UPDATE test SET num = " + num);
727    }
728
729    private void checkNum(int num) {
730        Assert.assertEquals(
731                num, DatabaseUtils.longForQuery(mDatabase, "SELECT num FROM test", null));
732    }
733
734    private void assertThrowsIllegalState(Runnable r) {
735        boolean ok = false;
736        try {
737            r.run();
738        } catch (IllegalStateException e) {
739            ok = true;
740        }
741        Assert.assertTrue(ok);
742    }
743
744    // Disable these until we can explicitly mark them as stress tests
745    public void xxtestMem1() throws Exception {
746        populateDefaultTable();
747
748        for (int i = 0; i < 50000; i++) {
749            Cursor cursor = mDatabase.query("test", null, null, null, null, null, null);
750            cursor.moveToFirst();
751            cursor.close();
752//                Log.i("~~~~", "Finished round " + i);
753        }
754    }
755
756    // Disable these until we can explicitly mark them as stress tests
757    public void xxtestMem2() throws Exception {
758        populateDefaultTable();
759
760        for (int i = 0; i < 50000; i++) {
761            Cursor cursor = mDatabase.query("test", null, null, null, null, null, null);
762            cursor.close();
763//                Log.i("~~~~", "Finished round " + i);
764        }
765    }
766
767    // Disable these until we can explicitly mark them as stress tests
768    public void xxtestMem3() throws Exception {
769        populateDefaultTable();
770
771        for (int i = 0; i < 50000; i++) {
772            Cursor cursor = mDatabase.query("test", null, null, null, null, null, null);
773            cursor.deactivate();
774//                Log.i("~~~~", "Finished round " + i);
775        }
776    }
777
778    @MediumTest
779    public void testContentValues() throws Exception {
780        ContentValues values = new ContentValues();
781        values.put("string", "value");
782        assertEquals("value", values.getAsString("string"));
783        byte[] bytes = new byte[42];
784        Arrays.fill(bytes, (byte) 0x28);
785        values.put("byteArray", bytes);
786        assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray")));
787
788        // Write the ContentValues to a Parcel and then read them out
789        Parcel p = Parcel.obtain();
790        values.writeToParcel(p, 0);
791        p.setDataPosition(0);
792        values = ContentValues.CREATOR.createFromParcel(p);
793
794        // Read the values out again and make sure they're the same
795        assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray")));
796        assertEquals("value", values.get("string"));
797    }
798
799    @MediumTest
800    public void testTableInfoPragma() throws Exception {
801        mDatabase.execSQL("CREATE TABLE pragma_test (" +
802                "i INTEGER DEFAULT 1234, " +
803                "j INTEGER, " +
804                "s TEXT DEFAULT 'hello', " +
805                "t TEXT, " +
806                "'select' TEXT DEFAULT \"hello\")");
807        try {
808            Cursor cur = mDatabase.rawQuery("PRAGMA table_info(pragma_test)", null);
809            Assert.assertEquals(5, cur.getCount());
810
811            Assert.assertTrue(cur.moveToNext());
812            Assert.assertEquals("i",
813                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
814            Assert.assertEquals("1234",
815                    cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
816
817            Assert.assertTrue(cur.moveToNext());
818            Assert.assertEquals("j",
819                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
820            Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
821
822            Assert.assertTrue(cur.moveToNext());
823            Assert.assertEquals("s",
824                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
825            Assert.assertEquals("'hello'",
826                    cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
827
828            Assert.assertTrue(cur.moveToNext());
829            Assert.assertEquals("t",
830                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
831            Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
832
833            Assert.assertTrue(cur.moveToNext());
834            Assert.assertEquals("select",
835                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
836            Assert.assertEquals("\"hello\"",
837                    cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
838
839            cur.close();
840        } catch (Throwable t) {
841            throw new RuntimeException(
842                    "If you see this test fail, it's likely that something about " +
843                    "sqlite's PRAGMA table_info(...) command has changed.", t);
844        }
845    }
846
847    @MediumTest
848    public void testInsertHelper() throws Exception {
849        Cursor cur;
850        ContentValues cv;
851        long row;
852
853        mDatabase.execSQL("CREATE TABLE insert_test (" +
854                "_id INTEGER PRIMARY KEY, " +
855                "s TEXT NOT NULL UNIQUE, " +
856                "t TEXT NOT NULL DEFAULT 'hello world', " +
857                "i INTEGER, " +
858                "j INTEGER NOT NULL DEFAULT 1234, " +
859                "'select' TEXT)");
860
861        DatabaseUtils.InsertHelper ih =
862            new DatabaseUtils.InsertHelper(mDatabase, "insert_test");
863
864        cv = new ContentValues();
865        cv.put("s", "one");
866        row = ih.insert(cv);
867        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
868        Assert.assertTrue(cur.moveToFirst());
869        Assert.assertEquals("one", cur.getString(1));
870        Assert.assertEquals("hello world", cur.getString(2));
871        Assert.assertNull(cur.getString(3));
872        Assert.assertEquals(1234, cur.getLong(4));
873        Assert.assertNull(cur.getString(5));
874        cur.close();
875
876        cv = new ContentValues();
877        cv.put("s", "two");
878        cv.put("t", "goodbye world");
879        row = ih.insert(cv);
880        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
881        Assert.assertTrue(cur.moveToFirst());
882        Assert.assertEquals("two", cur.getString(1));
883        Assert.assertEquals("goodbye world", cur.getString(2));
884        Assert.assertNull(cur.getString(3));
885        Assert.assertEquals(1234, cur.getLong(4));
886        Assert.assertNull(cur.getString(5));
887        cur.close();
888
889        cv = new ContentValues();
890        cv.put("t", "goodbye world");
891        row = ih.insert(cv);
892        Assert.assertEquals(-1, row);
893
894        cv = new ContentValues();
895        cv.put("s", "three");
896        cv.put("i", 2345);
897        cv.put("j", 3456);
898        cv.put("select", "tricky");
899        row = ih.insert(cv);
900        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
901        Assert.assertTrue(cur.moveToFirst());
902        Assert.assertEquals("three", cur.getString(1));
903        Assert.assertEquals("hello world", cur.getString(2));
904        Assert.assertEquals(2345, cur.getLong(3));
905        Assert.assertEquals(3456, cur.getLong(4));
906        Assert.assertEquals("tricky", cur.getString(5));
907        cur.close();
908
909        cv = new ContentValues();
910        cv.put("s", "three");
911        cv.put("i", 6789);
912        row = ih.insert(cv);
913        Assert.assertEquals(-1, row);
914        row = ih.replace(cv);
915        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
916        Assert.assertTrue(cur.moveToFirst());
917        Assert.assertEquals("three", cur.getString(1));
918        Assert.assertEquals("hello world", cur.getString(2));
919        Assert.assertEquals(6789, cur.getLong(3));
920        cur.close();
921
922        ih.close();
923    }
924
925    @MediumTest
926    public void testSemicolonsInStatements() throws Exception {
927        mDatabase.execSQL("CREATE TABLE pragma_test (" +
928                "i INTEGER DEFAULT 1234, " +
929                "j INTEGER, " +
930                "s TEXT DEFAULT 'hello', " +
931                "t TEXT, " +
932                "'select' TEXT DEFAULT \"hello\")");
933        try {
934            // ending the sql statement with  semicolons shouldn't be a problem.
935            Cursor cur = mDatabase.rawQuery("PRAGMA database_list;", null);
936            cur.close();
937            // two semicolons in the statement shouldn't be a problem.
938            cur = mDatabase.rawQuery("PRAGMA database_list;;", null);
939            cur.close();
940        } catch (Throwable t) {
941            fail("unexpected, of course");
942        }
943    }
944
945    @MediumTest
946    public void testUnionsWithBindArgs() {
947        /* make sure unions with bindargs work http://b/issue?id=1061291 */
948        mDatabase.execSQL("CREATE TABLE A (i int);");
949        mDatabase.execSQL("create table B (k int);");
950        mDatabase.execSQL("create table C (n int);");
951        mDatabase.execSQL("insert into A values(1);");
952        mDatabase.execSQL("insert into A values(2);");
953        mDatabase.execSQL("insert into A values(3);");
954        mDatabase.execSQL("insert into B values(201);");
955        mDatabase.execSQL("insert into B values(202);");
956        mDatabase.execSQL("insert into B values(203);");
957        mDatabase.execSQL("insert into C values(901);");
958        mDatabase.execSQL("insert into C values(902);");
959        String s = "select i from A where i > 2 " +
960                "UNION select k from B where k > 201 " +
961                "UNION select n from C where n !=900;";
962        Cursor c = mDatabase.rawQuery(s, null);
963        int n = c.getCount();
964        c.close();
965        String s1 = "select i from A where i > ? " +
966                "UNION select k from B where k > ? " +
967                "UNION select n from C where n != ?;";
968        Cursor c1 = mDatabase.rawQuery(s1, new String[]{"2", "201", "900"});
969        assertEquals(n, c1.getCount());
970        c1.close();
971    }
972
973    /**
974     * This test is available only when the platform has a locale with the language "ja".
975     * It finishes without failure when it is not available.
976     */
977    @MediumTest
978    public void testCollateLocalizedForJapanese() throws Exception {
979        final String testName = "DatabaseGeneralTest#testCollateLocalizedForJapanese()";
980        final Locale[] localeArray = Locale.getAvailableLocales();
981        final String japanese = Locale.JAPANESE.getLanguage();
982        final String english = Locale.ENGLISH.getLanguage();
983        Locale japaneseLocale = null;
984        Locale englishLocale = null;
985        for (Locale locale : localeArray) {
986            if (locale != null) {
987                final String language = locale.getLanguage();
988                if (language == null) {
989                    continue;
990                } else if (language.equals(japanese)) {
991                    japaneseLocale = locale;
992                } else if (language.equals(english)) {
993                    englishLocale = locale;
994                }
995            }
996
997            if (japaneseLocale != null && englishLocale != null) {
998                break;
999            }
1000        }
1001
1002        if (japaneseLocale == null || englishLocale == null) {
1003            Log.d(TAG, testName + "n is silently skipped since " +
1004                    (englishLocale == null ?
1005                            (japaneseLocale == null ?
1006                                    "Both English and Japanese locales do not exist." :
1007                                    "English locale does not exist.") :
1008                            (japaneseLocale == null ?
1009                                    "Japanese locale does not exist." :
1010                                    "...why?")));
1011            return;
1012        }
1013
1014        Locale originalLocale = Locale.getDefault();
1015        try {
1016
1017            final String dbName = "collate_localized_test";
1018            mDatabase.execSQL("CREATE TABLE " + dbName + " (" +
1019                    "_id INTEGER PRIMARY KEY, " +
1020                    "s TEXT COLLATE LOCALIZED) ");
1021            DatabaseUtils.InsertHelper ih =
1022                new DatabaseUtils.InsertHelper(mDatabase, dbName);
1023            ContentValues cv = new ContentValues();
1024
1025            cv = new ContentValues();  //
1026            cv.put("s", "\uFF75\uFF77\uFF85\uFF9C");  // O-ki-na-wa in half-width Katakana
1027            ih.insert(cv);
1028
1029            cv = new ContentValues();  //
1030            cv.put("s", "\u306B\u307B\u3093");  // Ni-ho-n in Hiragana
1031            ih.insert(cv);
1032
1033            cv = new ContentValues();  //
1034            cv.put("s", "\u30A2\u30E1\u30EA\u30AB");  // A-me-ri-ca in hull-width Katakana
1035            ih.insert(cv);
1036
1037            // Assume setLocale() does REINDEX and an English locale does not consider
1038            // Japanese-specific LOCALIZED order.
1039            Locale.setDefault(englishLocale);
1040            Locale.setDefault(japaneseLocale);
1041
1042            Cursor cur = mDatabase.rawQuery(
1043                    "SELECT * FROM " + dbName + " ORDER BY s", null);
1044            assertTrue(cur.moveToFirst());
1045            assertEquals("\u30A2\u30E1\u30EA\u30AB", cur.getString(1));
1046            assertTrue(cur.moveToNext());
1047            assertEquals("\uFF75\uFF77\uFF85\uFF9C", cur.getString(1));
1048            assertTrue(cur.moveToNext());
1049            assertEquals("\u306B\u307B\u3093", cur.getString(1));
1050        } finally {
1051            if (originalLocale != null) {
1052                try {
1053                    Locale.setDefault(originalLocale);
1054                } catch (Exception e) {
1055                }
1056            }
1057        }
1058    }
1059
1060    @SmallTest
1061    public void testSetMaxCahesize() {
1062        mDatabase.execSQL("CREATE TABLE test (i int, j int);");
1063        mDatabase.execSQL("insert into test values(1,1);");
1064        // set cache size
1065        int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
1066        mDatabase.setMaxSqlCacheSize(N);
1067
1068        // try reduce cachesize
1069        try {
1070            mDatabase.setMaxSqlCacheSize(1);
1071        } catch (IllegalStateException e) {
1072            assertTrue(e.getMessage().contains("cannot set cacheSize to a value less than"));
1073        }
1074    }
1075
1076    @SmallTest
1077    public void testOpenDatabaseLookasideConfig() {
1078        // First check that lookaside is active
1079        verifyLookasideStats(false);
1080        // Reopen test db with lookaside disabled
1081        mDatabase.close();
1082        SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
1083                .setLookasideConfig(0, 0).build();
1084        mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
1085        verifyLookasideStats(true);
1086    }
1087
1088    @SmallTest
1089    public void testOpenParamsSetLookasideConfigValidation() {
1090        try {
1091            SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
1092                    .setLookasideConfig(-1, 0).build();
1093            fail("Negative slot size should be rejected");
1094        } catch (IllegalArgumentException expected) {
1095        }
1096        try {
1097            SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
1098                    .setLookasideConfig(0, -10).build();
1099            fail("Negative slot count should be rejected");
1100        } catch (IllegalArgumentException expected) {
1101        }
1102    }
1103
1104    void verifyLookasideStats(boolean expectDisabled) {
1105        boolean dbStatFound = false;
1106        SQLiteDebug.PagerStats info = SQLiteDebug.getDatabaseInfo();
1107        for (SQLiteDebug.DbStats dbStat : info.dbStats) {
1108            if (dbStat.dbName.endsWith(mDatabaseFile.getName())) {
1109                dbStatFound = true;
1110                Log.i(TAG, "Lookaside for " + dbStat.dbName + " " + dbStat.lookaside);
1111                if (expectDisabled) {
1112                    assertTrue("lookaside slots count should be zero", dbStat.lookaside == 0);
1113                } else {
1114                    assertTrue("lookaside slots count should be greater than zero",
1115                            dbStat.lookaside > 0);
1116                }
1117            }
1118        }
1119        assertTrue("No dbstat found for " + mDatabaseFile.getName(), dbStatFound);
1120    }
1121
1122    @LargeTest
1123    public void testDefaultDatabaseErrorHandler() {
1124        DefaultDatabaseErrorHandler errorHandler = new DefaultDatabaseErrorHandler();
1125
1126        // close the database. and call corruption handler.
1127        // it should delete the database file.
1128        File dbfile = new File(mDatabase.getPath());
1129        mDatabase.close();
1130        assertFalse(mDatabase.isOpen());
1131        assertTrue(dbfile.exists());
1132        try {
1133            errorHandler.onCorruption(mDatabase);
1134            assertFalse(dbfile.exists());
1135        } catch (Exception e) {
1136            fail("unexpected");
1137        }
1138
1139        // create an in-memory database. and corruption handler shouldn't try to delete it
1140        SQLiteDatabase memoryDb = SQLiteDatabase.openOrCreateDatabase(":memory:", null);
1141        assertNotNull(memoryDb);
1142        memoryDb.close();
1143        assertFalse(memoryDb.isOpen());
1144        try {
1145            errorHandler.onCorruption(memoryDb);
1146        } catch (Exception e) {
1147            fail("unexpected");
1148        }
1149
1150        // create a database, keep it open, call corruption handler. database file should be deleted
1151        SQLiteDatabase dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null);
1152        assertTrue(dbfile.exists());
1153        assertNotNull(dbObj);
1154        assertTrue(dbObj.isOpen());
1155        try {
1156            errorHandler.onCorruption(dbObj);
1157            assertFalse(dbfile.exists());
1158        } catch (Exception e) {
1159            fail("unexpected");
1160        }
1161
1162        // create a database, attach 2 more databases to it
1163        //    attached database # 1: ":memory:"
1164        //    attached database # 2: mDatabase.getPath() + "1";
1165        // call corruption handler. database files including the one for attached database # 2
1166        // should be deleted
1167        String attachedDb1File = mDatabase.getPath() + "1";
1168        dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null);
1169        dbObj.execSQL("ATTACH DATABASE ':memory:' as memoryDb");
1170        dbObj.execSQL("ATTACH DATABASE '" +  attachedDb1File + "' as attachedDb1");
1171        assertTrue(dbfile.exists());
1172        assertTrue(new File(attachedDb1File).exists());
1173        assertNotNull(dbObj);
1174        assertTrue(dbObj.isOpen());
1175        List<Pair<String, String>> attachedDbs = dbObj.getAttachedDbs();
1176        try {
1177            errorHandler.onCorruption(dbObj);
1178            assertFalse(dbfile.exists());
1179            assertFalse(new File(attachedDb1File).exists());
1180        } catch (Exception e) {
1181            fail("unexpected");
1182        }
1183
1184        // same as above, except this is a bit of stress testing. attach 5 database files
1185        // and make sure they are all removed.
1186        int N = 5;
1187        ArrayList<String> attachedDbFiles = new ArrayList<String>(N);
1188        for (int i = 0; i < N; i++) {
1189            attachedDbFiles.add(mDatabase.getPath() + i);
1190        }
1191        dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null);
1192        dbObj.execSQL("ATTACH DATABASE ':memory:' as memoryDb");
1193        for (int i = 0; i < N; i++) {
1194            dbObj.execSQL("ATTACH DATABASE '" +  attachedDbFiles.get(i) + "' as attachedDb" + i);
1195        }
1196        assertTrue(dbfile.exists());
1197        for (int i = 0; i < N; i++) {
1198            assertTrue(new File(attachedDbFiles.get(i)).exists());
1199        }
1200        assertNotNull(dbObj);
1201        assertTrue(dbObj.isOpen());
1202        attachedDbs = dbObj.getAttachedDbs();
1203        try {
1204            errorHandler.onCorruption(dbObj);
1205            assertFalse(dbfile.exists());
1206            for (int i = 0; i < N; i++) {
1207                assertFalse(new File(attachedDbFiles.get(i)).exists());
1208            }
1209        } catch (Exception e) {
1210            fail("unexpected");
1211        }
1212    }
1213
1214    @MediumTest
1215    public void testCloseIdleConnection() throws Exception {
1216        mDatabase.close();
1217        SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
1218                .setIdleConnectionTimeout(1000).build();
1219        mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
1220        // Wait a bit and check that connection is still open
1221        Thread.sleep(100);
1222        String output = executeShellCommand("dumpsys dbinfo " + getContext().getPackageName());
1223        assertTrue("Connection #0 should be open. Output: " + output,
1224                output.contains("Connection #0:"));
1225
1226        // Now cause idle timeout and check that connection is closed
1227        Thread.sleep(1000);
1228        output = executeShellCommand("dumpsys dbinfo " + getContext().getPackageName());
1229        assertFalse("Connection #0 should be closed. Output: " + output,
1230                output.contains("Connection #0:"));
1231    }
1232
1233    @SmallTest
1234    public void testSetIdleConnectionTimeoutValidation() throws Exception {
1235        try {
1236            new SQLiteDatabase.OpenParams.Builder().setIdleConnectionTimeout(-1).build();
1237            fail("Negative timeout should be rejected");
1238        } catch (IllegalArgumentException expected) {
1239        }
1240    }
1241
1242    private String executeShellCommand(String cmd) throws Exception {
1243        return UiDevice.getInstance(
1244                InstrumentationRegistry.getInstrumentation()).executeShellCommand(cmd);
1245    }
1246
1247    @SmallTest
1248    public void testSavepointRollbacks() {
1249        try (SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(":memory:", null)) {
1250            db.execSQL("drop table if exists data");
1251            db.execSQL("create table if not exists data (id INTEGER PRIMARY KEY, val TEXT)");
1252            db.execSQL("begin deferred transaction");
1253            db.execSQL("insert into data (val) values('row 1')");
1254            db.execSQL("savepoint foo");
1255            db.execSQL("insert into data (val) values('row 2')");
1256            db.execSQL("rollback to foo");
1257            db.execSQL("commit transaction");
1258            long rowCount = DatabaseUtils.longForQuery(db, "select count(*) from data", null);
1259            assertEquals(1, rowCount);
1260        }
1261    }
1262}
1263