DatabaseGeneralTest.java revision d606b4bf2c1a2308b40785860853cfb95a77bf58 
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 android.content.ContentValues;
20import android.content.Context;
21import android.database.ContentObserver;
22import android.database.Cursor;
23import android.database.DatabaseUtils;
24import android.database.CharArrayBuffer;
25import android.database.sqlite.SQLiteDatabase;
26import android.database.sqlite.SQLiteStatement;
27import android.os.Environment;
28import android.os.Handler;
29import android.os.Parcel;
30import android.test.AndroidTestCase;
31import android.test.PerformanceTestCase;
32import android.test.suitebuilder.annotation.MediumTest;
33import android.test.suitebuilder.annotation.SmallTest;
34import android.util.Log;
35
36import java.io.File;
37import java.io.UnsupportedEncodingException;
38import java.text.Collator;
39import java.util.Arrays;
40
41import junit.framework.Assert;
42import junit.framework.TestCase;
43
44import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_COLUMNNAME_INDEX;
45import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_DEFAULT_INDEX;
46
47public class DatabaseGeneralTest extends AndroidTestCase implements PerformanceTestCase {
48
49    private static final String sString1 = "this is a test";
50    private static final String sString2 = "and yet another test";
51    private static final String sString3 = "this string is a little longer, but still a test";
52    private static final String PHONE_NUMBER = "16175551212";
53
54    private static final int CURRENT_DATABASE_VERSION = 42;
55    private SQLiteDatabase mDatabase;
56    private File mDatabaseFile;
57
58    @Override
59    protected void setUp() throws Exception {
60        super.setUp();
61        File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE);
62        mDatabaseFile = new File(dbDir, "database_test.db");
63        if (mDatabaseFile.exists()) {
64            mDatabaseFile.delete();
65        }
66        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null);
67        assertNotNull(mDatabase);
68        mDatabase.setVersion(CURRENT_DATABASE_VERSION);
69    }
70
71    @Override
72    protected void tearDown() throws Exception {
73        mDatabase.close();
74        mDatabaseFile.delete();
75        super.tearDown();
76    }
77
78    public boolean isPerformanceOnly() {
79        return false;
80    }
81
82    // These test can only be run once.
83    public int startPerformance(Intermediates intermediates) {
84        return 1;
85    }
86
87    private void populateDefaultTable() {
88        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
89
90        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');");
91        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');");
92        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');");
93    }
94
95    @MediumTest
96    public void testVersion() throws Exception {
97        assertEquals(CURRENT_DATABASE_VERSION, mDatabase.getVersion());
98        mDatabase.setVersion(11);
99        assertEquals(11, mDatabase.getVersion());
100    }
101
102    @MediumTest
103    public void testUpdate() throws Exception {
104        populateDefaultTable();
105
106        ContentValues values = new ContentValues(1);
107        values.put("data", "this is an updated test");
108        assertEquals(1, mDatabase.update("test", values, "_id=1", null));
109        Cursor c = mDatabase.query("test", null, "_id=1", null, null, null, null);
110        assertNotNull(c);
111        assertEquals(1, c.getCount());
112        c.moveToFirst();
113        String value = c.getString(c.getColumnIndexOrThrow("data"));
114        assertEquals("this is an updated test", value);
115    }
116
117    @MediumTest
118    public void testPhoneNumbersEqual() throws Exception {
119        mDatabase.execSQL("CREATE TABLE phones (num TEXT);");
120        mDatabase.execSQL("INSERT INTO phones (num) VALUES ('911');");
121        mDatabase.execSQL("INSERT INTO phones (num) VALUES ('5555');");
122        mDatabase.execSQL("INSERT INTO phones (num) VALUES ('+" + PHONE_NUMBER + "');");
123
124        String number;
125        Cursor c;
126
127        c = mDatabase.query("phones", null,
128                "PHONE_NUMBERS_EQUAL(num, '504-555-7683')", null, null, null, null);
129        assertTrue(c == null || c.getCount() == 0);
130        c.close();
131
132        c = mDatabase.query("phones", null,
133                "PHONE_NUMBERS_EQUAL(num, '911')", null, null, null, null);
134        assertNotNull(c);
135        assertEquals(1, c.getCount());
136        c.moveToFirst();
137        number = c.getString(c.getColumnIndexOrThrow("num"));
138        assertEquals("911", number);
139        c.close();
140
141        c = mDatabase.query("phones", null,
142                "PHONE_NUMBERS_EQUAL(num, '5555')", null, null, null, null);
143        assertNotNull(c);
144        assertEquals(1, c.getCount());
145        c.moveToFirst();
146        number = c.getString(c.getColumnIndexOrThrow("num"));
147        assertEquals("5555", number);
148        c.close();
149
150        c = mDatabase.query("phones", null,
151                "PHONE_NUMBERS_EQUAL(num, '180055555555')", null, null, null, null);
152        assertTrue(c == null || c.getCount() == 0);
153        c.close();
154
155        c = mDatabase.query("phones", null,
156                "PHONE_NUMBERS_EQUAL(num, '+" + PHONE_NUMBER + "')", null, null, null, null);
157        assertNotNull(c);
158        assertEquals(1, c.getCount());
159        c.moveToFirst();
160        number = c.getString(c.getColumnIndexOrThrow("num"));
161        assertEquals("+" + PHONE_NUMBER, number);
162        c.close();
163
164        c = mDatabase.query("phones", null,
165                "PHONE_NUMBERS_EQUAL(num, '+1 (617).555-1212')", null, null, null, null);
166        assertNotNull(c);
167        assertEquals(1, c.getCount());
168        c.moveToFirst();
169        number = c.getString(c.getColumnIndexOrThrow("num"));
170        assertEquals("+" + PHONE_NUMBER, number);
171        c.close();
172
173        c = mDatabase.query("phones", null,
174                "PHONE_NUMBERS_EQUAL(num, '" + PHONE_NUMBER + "')", null, null, null, null);
175        assertNotNull(c);
176        assertEquals(1, c.getCount());
177        c.moveToFirst();
178        number = c.getString(c.getColumnIndexOrThrow("num"));
179        assertEquals("+" + PHONE_NUMBER, number);
180        c.close();
181
182        /*
183        c = mDatabase.query("phones", null,
184                "PHONE_NUMBERS_EQUAL(num, '5551212')", null, null, null, null);
185        assertNotNull(c);
186        assertEquals(1, c.getCount());
187        c.moveToFirst();
188        number = c.getString(c.getColumnIndexOrThrow("num"));
189        assertEquals("+" + PHONE_NUMBER, number);
190        c.close();
191        */
192
193        c = mDatabase.query("phones", null,
194                "PHONE_NUMBERS_EQUAL(num, '011" + PHONE_NUMBER + "')", null, null, null, null);
195        assertNotNull(c);
196        assertEquals(1, c.getCount());
197        c.moveToFirst();
198        number = c.getString(c.getColumnIndexOrThrow("num"));
199        assertEquals("+" + PHONE_NUMBER, number);
200        c.close();
201
202        c = mDatabase.query("phones", null,
203                "PHONE_NUMBERS_EQUAL(num, '00" + PHONE_NUMBER + "')", null, null, null, null);
204        assertNotNull(c);
205        assertEquals(1, c.getCount());
206        c.moveToFirst();
207        number = c.getString(c.getColumnIndexOrThrow("num"));
208        assertEquals("+" + PHONE_NUMBER, number);
209        c.close();
210    }
211
212    private void phoneNumberCompare(String phone1, String phone2, boolean equal,
213            boolean useStrictComparation) {
214        String[] temporalPhoneNumbers = new String[2];
215        temporalPhoneNumbers[0] = phone1;
216        temporalPhoneNumbers[1] = phone2;
217
218        Cursor cursor = mDatabase.rawQuery(
219                String.format(
220                        "SELECT CASE WHEN PHONE_NUMBERS_EQUAL(?, ?, %d) " +
221                        "THEN 'equal' ELSE 'not equal' END",
222                        (useStrictComparation ? 1 : 0)),
223                temporalPhoneNumbers);
224        try {
225            assertNotNull(cursor);
226            assertTrue(cursor.moveToFirst());
227            if (equal) {
228                assertEquals(String.format("Unexpectedly, \"%s != %s\".", phone1, phone2),
229                        "equal", cursor.getString(0));
230            } else {
231                assertEquals(String.format("Unexpectedly, \"%s\" == \"%s\".", phone1, phone2),
232                        "not equal", cursor.getString(0));
233            }
234        } finally {
235            if (cursor != null) {
236                cursor.close();
237            }
238        }
239    }
240
241    private void assertPhoneNumberEqual(String phone1, String phone2) throws Exception {
242        assertPhoneNumberEqual(phone1, phone2, true);
243        assertPhoneNumberEqual(phone1, phone2, false);
244    }
245
246    private void assertPhoneNumberEqual(String phone1, String phone2, boolean useStrict)
247            throws Exception {
248        phoneNumberCompare(phone1, phone2, true, useStrict);
249    }
250
251    private void assertPhoneNumberNotEqual(String phone1, String phone2) throws Exception {
252        assertPhoneNumberNotEqual(phone1, phone2, true);
253        assertPhoneNumberNotEqual(phone1, phone2, false);
254    }
255
256    private void assertPhoneNumberNotEqual(String phone1, String phone2, boolean useStrict)
257            throws Exception {
258        phoneNumberCompare(phone1, phone2, false, useStrict);
259    }
260
261    /**
262     * Tests international matching issues for the PHONE_NUMBERS_EQUAL function.
263     *
264     * @throws Exception
265     */
266    @SmallTest
267    public void testPhoneNumbersEqualInternationl() throws Exception {
268        assertPhoneNumberEqual("1", "1");
269        assertPhoneNumberEqual("123123", "123123");
270        assertPhoneNumberNotEqual("123123", "923123");
271        assertPhoneNumberNotEqual("123123", "123129");
272        assertPhoneNumberNotEqual("123123", "1231234");
273        assertPhoneNumberNotEqual("123123", "0123123", false);
274        assertPhoneNumberNotEqual("123123", "0123123", true);
275        assertPhoneNumberEqual("650-253-0000", "6502530000");
276        assertPhoneNumberEqual("650-253-0000", "650 253 0000");
277        assertPhoneNumberEqual("650 253 0000", "6502530000");
278        assertPhoneNumberEqual("+1 650-253-0000", "6502530000");
279        assertPhoneNumberEqual("001 650-253-0000", "6502530000");
280        assertPhoneNumberEqual("0111 650-253-0000", "6502530000");
281
282        // Russian trunk digit
283        assertPhoneNumberEqual("+79161234567", "89161234567");
284
285        // French trunk digit
286        assertPhoneNumberEqual("+33123456789", "0123456789");
287
288        // Trunk digit for city codes in the Netherlands
289        assertPhoneNumberEqual("+31771234567", "0771234567");
290
291        // Test broken caller ID seen on call from Thailand to the US
292        assertPhoneNumberEqual("+66811234567", "166811234567");
293
294        // Test the same in-country number with different country codes
295        assertPhoneNumberNotEqual("+33123456789", "+1123456789");
296
297        // Test one number with country code and the other without
298        assertPhoneNumberEqual("5125551212", "+15125551212");
299
300        // Test two NANP numbers that only differ in the area code
301        assertPhoneNumberNotEqual("5125551212", "6505551212");
302
303        // Japanese phone numbers
304        assertPhoneNumberEqual("090-1234-5678", "+819012345678");
305        assertPhoneNumberEqual("090(1234)5678", "+819012345678");
306        assertPhoneNumberEqual("090-1234-5678", "+81-90-1234-5678");
307
308        // Equador
309        assertPhoneNumberEqual("+593(800)123-1234", "8001231234");
310        assertPhoneNumberEqual("+593-2-1234-123", "21234123");
311
312        // Two continuous 0 at the beginning of the phone string should not be
313        // treated as trunk prefix in the strict comparation.
314        assertPhoneNumberEqual("008001231234", "8001231234", false);
315        assertPhoneNumberNotEqual("008001231234", "8001231234", true);
316
317        // Confirm that the bug found before does not re-appear in the strict compalation
318        assertPhoneNumberEqual("080-1234-5678", "+819012345678", false);
319        assertPhoneNumberNotEqual("080-1234-5678", "+819012345678", true);
320    }
321
322    @MediumTest
323    public void testCopyString() throws Exception {
324        mDatabase.execSQL("CREATE TABLE guess (numi INTEGER, numf FLOAT, str TEXT);");
325        mDatabase.execSQL(
326                "INSERT INTO guess (numi,numf,str) VALUES (0,0.0,'ZoomZoomZoomZoom');");
327        mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (2000000000,3.1415926535,'');");
328        String chinese = "\u4eac\u4ec5 \u5c3d\u5f84\u60ca";
329        String[] arr = new String[1];
330        arr[0] = chinese;
331        mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (-32768,-1.0,?)", arr);
332
333        Cursor c;
334
335        c = mDatabase.rawQuery("SELECT * FROM guess", null);
336
337        c.moveToFirst();
338
339        CharArrayBuffer buf = new CharArrayBuffer(14);
340
341        String compareTo = c.getString(c.getColumnIndexOrThrow("numi"));
342        int numiIdx = c.getColumnIndexOrThrow("numi");
343        int numfIdx = c.getColumnIndexOrThrow("numf");
344        int strIdx = c.getColumnIndexOrThrow("str");
345
346        c.copyStringToBuffer(numiIdx, buf);
347        assertEquals(1, buf.sizeCopied);
348        assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied));
349
350        c.copyStringToBuffer(strIdx, buf);
351        assertEquals("ZoomZoomZoomZoom", new String(buf.data, 0, buf.sizeCopied));
352
353        c.moveToNext();
354        compareTo = c.getString(numfIdx);
355
356        c.copyStringToBuffer(numfIdx, buf);
357        assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied));
358        c.copyStringToBuffer(strIdx, buf);
359        assertEquals(0, buf.sizeCopied);
360
361        c.moveToNext();
362        c.copyStringToBuffer(numfIdx, buf);
363        assertEquals(-1.0, Double.valueOf(
364                new String(buf.data, 0, buf.sizeCopied)).doubleValue());
365
366        c.copyStringToBuffer(strIdx, buf);
367        compareTo = c.getString(strIdx);
368        assertEquals(chinese, compareTo);
369
370        assertEquals(chinese, new String(buf.data, 0, buf.sizeCopied));
371        c.close();
372    }
373
374    @MediumTest
375    public void testSchemaChange1() throws Exception {
376        SQLiteDatabase db1 = mDatabase;
377        Cursor cursor;
378
379        db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
380
381        cursor = db1.query("db1", null, null, null, null, null, null);
382        assertNotNull("Cursor is null", cursor);
383
384        db1.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);");
385
386        assertEquals(0, cursor.getCount());
387        cursor.deactivate();
388    }
389
390    @MediumTest
391    public void testSchemaChange2() throws Exception {
392        SQLiteDatabase db1 = mDatabase;
393        SQLiteDatabase db2 = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null);
394        Cursor cursor;
395
396        db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
397
398        cursor = db1.query("db1", null, null, null, null, null, null);
399        assertNotNull("Cursor is null", cursor);
400        assertEquals(0, cursor.getCount());
401        cursor.deactivate();
402        // this cause exception because we're still using sqlite_prepate16 and not
403        // sqlite_prepare16_v2. The v2 variant added the ability to check the
404        // schema version and handle the case when the schema has changed
405        // Marco Nelissen claim it was 2x slower to compile SQL statements so
406        // I reverted back to the v1 variant.
407        /* db2.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);");
408
409        cursor = db1.query("db1", null, null, null, null, null, null);
410        assertNotNull("Cursor is null", cursor);
411        assertEquals(0, cursor.count());
412        cursor.deactivate();
413        */
414    }
415
416    @MediumTest
417    public void testSchemaChange3() throws Exception {
418        SQLiteDatabase db1 = mDatabase;
419        SQLiteDatabase db2 = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null);
420        Cursor cursor;
421
422
423        db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
424        db1.execSQL("INSERT INTO db1 (data) VALUES ('test');");
425
426        cursor = db1.query("db1", null, null, null, null, null, null);
427        // this cause exception because we're still using sqlite_prepate16 and not
428        // sqlite_prepare16_v2. The v2 variant added the ability to check the
429        // schema version and handle the case when the schema has changed
430        // Marco Nelissen claim it was 2x slower to compile SQL statements so
431        // I reverted back to the v1 variant.
432        /* db2.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);");
433
434        assertNotNull("Cursor is null", cursor);
435        assertEquals(1, cursor.count());
436        assertTrue(cursor.first());
437        assertEquals("test", cursor.getString(cursor.getColumnIndexOrThrow("data")));
438        cursor.deactivate();
439        */
440    }
441
442    private class ChangeObserver extends ContentObserver {
443        private int mCursorNotificationCount = 0;
444        private int mNotificationCount = 0;
445
446        public int getCursorNotificationCount() {
447            return mCursorNotificationCount;
448        }
449
450        public int getNotificationCount() {
451            return mNotificationCount;
452        }
453
454        public ChangeObserver(boolean cursor) {
455            super(new Handler());
456            mCursor = cursor;
457        }
458
459        @Override
460        public boolean deliverSelfNotifications() {
461            return true;
462        }
463
464        @Override
465        public void onChange(boolean selfChange) {
466            if (mCursor) {
467                mCursorNotificationCount++;
468            } else {
469                mNotificationCount++;
470            }
471        }
472
473        boolean mCursor;
474    }
475
476    @MediumTest
477    public void testNotificationTest1() throws Exception {
478        /*
479        Cursor c = mContentResolver.query(Notes.CONTENT_URI,
480                new String[] {Notes._ID, Notes.NOTE},
481                null, null);
482        c.registerContentObserver(new MyContentObserver(true));
483        int count = c.count();
484
485        MyContentObserver observer = new MyContentObserver(false);
486        mContentResolver.registerContentObserver(Notes.CONTENT_URI, true, observer);
487
488        Uri uri;
489
490        HashMap<String, String> values = new HashMap<String, String>();
491        values.put(Notes.NOTE, "test note1");
492        uri = mContentResolver.insert(Notes.CONTENT_URI, values);
493        assertEquals(1, mCursorNotificationCount);
494        assertEquals(1, mNotificationCount);
495
496        c.requery();
497        assertEquals(count + 1, c.count());
498        c.first();
499        assertEquals("test note1", c.getString(c.getColumnIndex(Notes.NOTE)));
500        c.updateString(c.getColumnIndex(Notes.NOTE), "test note2");
501        c.commitUpdates();
502
503        assertEquals(2, mCursorNotificationCount);
504        assertEquals(2, mNotificationCount);
505
506        mContentResolver.delete(uri, null);
507
508        assertEquals(3, mCursorNotificationCount);
509        assertEquals(3, mNotificationCount);
510
511        mContentResolver.unregisterContentObserver(observer);
512        */
513    }
514
515    @MediumTest
516    public void testSelectionArgs() throws Exception {
517        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
518        ContentValues values = new ContentValues(1);
519        values.put("data", "don't forget to handled 's");
520        mDatabase.insert("test", "data", values);
521        values.clear();
522        values.put("data", "no apostrophes here");
523        mDatabase.insert("test", "data", values);
524        Cursor c = mDatabase.query(
525                "test", null, "data GLOB ?", new String[]{"*'*"}, null, null, null);
526        assertEquals(1, c.getCount());
527        assertTrue(c.moveToFirst());
528        assertEquals("don't forget to handled 's", c.getString(1));
529        c.deactivate();
530
531        // make sure code should checking null string properly so that
532        // it won't crash
533        try {
534            mDatabase.query("test", new String[]{"_id"},
535                    "_id=?", new String[]{null}, null, null, null);
536            fail("expected exception not thrown");
537        } catch (IllegalArgumentException e) {
538            // expected
539        }
540    }
541
542    @MediumTest
543    public void testTokenize() throws Exception {
544        Cursor c;
545        mDatabase.execSQL("CREATE TABLE tokens (" +
546                "token TEXT COLLATE unicode," +
547                "source INTEGER," +
548                "token_index INTEGER," +
549                "tag TEXT" +
550                ");");
551        mDatabase.execSQL("CREATE TABLE tokens_no_index (" +
552                "token TEXT COLLATE unicode," +
553                "source INTEGER" +
554                ");");
555
556        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
557                "SELECT _TOKENIZE(NULL, NULL, NULL, NULL)", null));
558        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
559                "SELECT _TOKENIZE('tokens', NULL, NULL, NULL)", null));
560        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
561                "SELECT _TOKENIZE('tokens', 10, NULL, NULL)", null));
562        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
563                "SELECT _TOKENIZE('tokens', 10, 'some string', NULL)", null));
564
565        Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase,
566                "SELECT _TOKENIZE('tokens', 11, 'some string ok', ' ', 1, 'foo')", null));
567        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
568                "SELECT _TOKENIZE('tokens', 11, 'second field', ' ', 1, 'bar')", null));
569
570        Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase,
571                "SELECT _TOKENIZE('tokens_no_index', 20, 'some string ok', ' ')", null));
572        Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase,
573                "SELECT _TOKENIZE('tokens_no_index', 21, 'foo bar baz', ' ', 0)", null));
574
575        // test Chinese
576        String chinese = new String("\u4eac\u4ec5 \u5c3d\u5f84\u60ca");
577        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
578                "SELECT _TOKENIZE('tokens', 12,'" + chinese + "', ' ', 1)", null));
579
580        String icustr = new String("Fr\u00e9d\u00e9ric Hj\u00f8nnev\u00e5g");
581
582        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
583                "SELECT _TOKENIZE('tokens', 13, '" + icustr + "', ' ', 1)", null));
584
585        Assert.assertEquals(9, DatabaseUtils.longForQuery(mDatabase,
586                "SELECT count(*) from tokens;", null));
587
588        String key = DatabaseUtils.getHexCollationKey("Frederic Hjonneva");
589        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
590                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
591        Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase,
592                "SELECT source from tokens where token GLOB '" + key + "*'", null));
593        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
594                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
595        key = DatabaseUtils.getHexCollationKey("Hjonneva");
596        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
597                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
598        Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase,
599                "SELECT source from tokens where token GLOB '" + key + "*'", null));
600        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
601                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
602
603        key = DatabaseUtils.getHexCollationKey("some string ok");
604        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
605                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
606        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
607                "SELECT source from tokens where token GLOB '" + key + "*'", null));
608        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
609                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
610        Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase,
611                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
612        key = DatabaseUtils.getHexCollationKey("string");
613        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
614                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
615        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
616                "SELECT source from tokens where token GLOB '" + key + "*'", null));
617        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
618                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
619        Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase,
620                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
621        key = DatabaseUtils.getHexCollationKey("ok");
622        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
623                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
624        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
625                "SELECT source from tokens where token GLOB '" + key + "*'", null));
626        Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
627                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
628        Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase,
629                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
630
631        key = DatabaseUtils.getHexCollationKey("second field");
632        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
633                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
634        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
635                "SELECT source from tokens where token GLOB '" + key + "*'", null));
636        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
637                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
638        Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase,
639                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
640        key = DatabaseUtils.getHexCollationKey("field");
641        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
642                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
643        Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase,
644                "SELECT source from tokens where token GLOB '" + key + "*'", null));
645        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
646                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
647        Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase,
648                "SELECT tag from tokens where token GLOB '" + key + "*'", null));
649
650        key = DatabaseUtils.getHexCollationKey(chinese);
651        String[] a = new String[1];
652        a[0] = key;
653        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
654                "SELECT count(*) from tokens where token= ?", a));
655        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
656                "SELECT source from tokens where token= ?", a));
657        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
658                "SELECT token_index from tokens where token= ?", a));
659        a[0] += "*";
660        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
661             "SELECT count(*) from tokens where token GLOB ?", a));
662        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
663                "SELECT source from tokens where token GLOB ?", a));
664        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
665                "SELECT token_index from tokens where token GLOB ?", a));
666
667       Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
668                "SELECT count(*) from tokens where token= '" + key + "'", null));
669       Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
670               "SELECT source from tokens where token= '" + key + "'", null));
671       Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
672               "SELECT token_index from tokens where token= '" + key + "'", null));
673
674        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
675                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
676        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
677                "SELECT source from tokens where token GLOB '" + key + "*'", null));
678        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
679                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
680
681        key = DatabaseUtils.getHexCollationKey("\u4eac\u4ec5");
682        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
683                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
684        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
685                "SELECT source from tokens where token GLOB '" + key + "*'", null));
686        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
687                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
688
689        key = DatabaseUtils.getHexCollationKey("\u5c3d\u5f84\u60ca");
690        Log.d("DatabaseGeneralTest", "key = " + key);
691        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
692                "SELECT count(*) from tokens where token GLOB '" + key + "*'", null));
693        Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase,
694                "SELECT source from tokens where token GLOB '" + key + "*'", null));
695        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
696                "SELECT token_index from tokens where token GLOB '" + key + "*'", null));
697
698        Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase,
699                "SELECT count(*) from tokens where token GLOB 'ab*'", null));
700
701        key = DatabaseUtils.getHexCollationKey("some string ok");
702        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
703                "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null));
704        Assert.assertEquals(20, DatabaseUtils.longForQuery(mDatabase,
705                "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null));
706
707        key = DatabaseUtils.getHexCollationKey("bar");
708        Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
709                "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null));
710        Assert.assertEquals(21, DatabaseUtils.longForQuery(mDatabase,
711                "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null));
712    }
713
714    @MediumTest
715    public void testTransactions() throws Exception {
716        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
717        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
718
719        // Make sure that things work outside an explicit transaction.
720        setNum(1);
721        checkNum(1);
722
723        // Test a single-level transaction.
724        setNum(0);
725        mDatabase.beginTransaction();
726        setNum(1);
727        mDatabase.setTransactionSuccessful();
728        mDatabase.endTransaction();
729        checkNum(1);
730        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
731
732        // Test a rolled-back transaction.
733        setNum(0);
734        mDatabase.beginTransaction();
735        setNum(1);
736        mDatabase.endTransaction();
737        checkNum(0);
738        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
739
740        // We should get an error if we end a non-existent transaction.
741        assertThrowsIllegalState(new Runnable() { public void run() {
742            mDatabase.endTransaction();
743        }});
744
745        // We should get an error if a set a non-existent transaction as clean.
746        assertThrowsIllegalState(new Runnable() { public void run() {
747            mDatabase.setTransactionSuccessful();
748        }});
749
750        mDatabase.beginTransaction();
751        mDatabase.setTransactionSuccessful();
752        // We should get an error if we mark a transaction as clean twice.
753        assertThrowsIllegalState(new Runnable() { public void run() {
754            mDatabase.setTransactionSuccessful();
755        }});
756        // We should get an error if we begin a transaction after marking the parent as clean.
757        assertThrowsIllegalState(new Runnable() { public void run() {
758            mDatabase.beginTransaction();
759        }});
760        mDatabase.endTransaction();
761        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
762
763        // Test a two-level transaction.
764        setNum(0);
765        mDatabase.beginTransaction();
766        mDatabase.beginTransaction();
767        setNum(1);
768        mDatabase.setTransactionSuccessful();
769        mDatabase.endTransaction();
770        mDatabase.setTransactionSuccessful();
771        mDatabase.endTransaction();
772        checkNum(1);
773        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
774
775        // Test rolling back an inner transaction.
776        setNum(0);
777        mDatabase.beginTransaction();
778        mDatabase.beginTransaction();
779        setNum(1);
780        mDatabase.endTransaction();
781        mDatabase.setTransactionSuccessful();
782        mDatabase.endTransaction();
783        checkNum(0);
784        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
785
786        // Test rolling back an outer transaction.
787        setNum(0);
788        mDatabase.beginTransaction();
789        mDatabase.beginTransaction();
790        setNum(1);
791        mDatabase.setTransactionSuccessful();
792        mDatabase.endTransaction();
793        mDatabase.endTransaction();
794        checkNum(0);
795        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
796    }
797
798    private void setNum(int num) {
799        mDatabase.execSQL("UPDATE test SET num = " + num);
800    }
801
802    private void checkNum(int num) {
803        Assert.assertEquals(
804                num, DatabaseUtils.longForQuery(mDatabase, "SELECT num FROM test", null));
805    }
806
807    private void assertThrowsIllegalState(Runnable r) {
808        boolean ok = false;
809        try {
810            r.run();
811        } catch (IllegalStateException e) {
812            ok = true;
813        }
814        Assert.assertTrue(ok);
815    }
816
817    // Disable these until we can explicitly mark them as stress tests
818    public void xxtestMem1() throws Exception {
819        populateDefaultTable();
820
821        for (int i = 0; i < 50000; i++) {
822            Cursor cursor = mDatabase.query("test", null, null, null, null, null, null);
823            cursor.moveToFirst();
824            cursor.close();
825//                Log.i("~~~~", "Finished round " + i);
826        }
827    }
828
829    // Disable these until we can explicitly mark them as stress tests
830    public void xxtestMem2() throws Exception {
831        populateDefaultTable();
832
833        for (int i = 0; i < 50000; i++) {
834            Cursor cursor = mDatabase.query("test", null, null, null, null, null, null);
835            cursor.close();
836//                Log.i("~~~~", "Finished round " + i);
837        }
838    }
839
840    // Disable these until we can explicitly mark them as stress tests
841    public void xxtestMem3() throws Exception {
842        populateDefaultTable();
843
844        for (int i = 0; i < 50000; i++) {
845            Cursor cursor = mDatabase.query("test", null, null, null, null, null, null);
846            cursor.deactivate();
847//                Log.i("~~~~", "Finished round " + i);
848        }
849    }
850
851    @MediumTest
852    public void testContentValues() throws Exception {
853        ContentValues values = new ContentValues();
854        values.put("string", "value");
855        assertEquals("value", values.getAsString("string"));
856        byte[] bytes = new byte[42];
857        Arrays.fill(bytes, (byte) 0x28);
858        values.put("byteArray", bytes);
859        assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray")));
860
861        // Write the ContentValues to a Parcel and then read them out
862        Parcel p = Parcel.obtain();
863        values.writeToParcel(p, 0);
864        p.setDataPosition(0);
865        values = ContentValues.CREATOR.createFromParcel(p);
866
867        // Read the values out again and make sure they're the same
868        assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray")));
869        assertEquals("value", values.get("string"));
870    }
871
872    @MediumTest
873    public void testTableInfoPragma() throws Exception {
874        mDatabase.execSQL("CREATE TABLE pragma_test (" +
875                "i INTEGER DEFAULT 1234, " +
876                "j INTEGER, " +
877                "s TEXT DEFAULT 'hello', " +
878                "t TEXT, " +
879                "'select' TEXT DEFAULT \"hello\")");
880        try {
881            Cursor cur = mDatabase.rawQuery("PRAGMA table_info(pragma_test)", null);
882            Assert.assertEquals(5, cur.getCount());
883
884            Assert.assertTrue(cur.moveToNext());
885            Assert.assertEquals("i",
886                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
887            Assert.assertEquals("1234",
888                    cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
889
890            Assert.assertTrue(cur.moveToNext());
891            Assert.assertEquals("j",
892                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
893            Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
894
895            Assert.assertTrue(cur.moveToNext());
896            Assert.assertEquals("s",
897                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
898            Assert.assertEquals("'hello'",
899                    cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
900
901            Assert.assertTrue(cur.moveToNext());
902            Assert.assertEquals("t",
903                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
904            Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
905
906            Assert.assertTrue(cur.moveToNext());
907            Assert.assertEquals("select",
908                    cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
909            Assert.assertEquals("\"hello\"",
910                    cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));
911
912            cur.close();
913        } catch (Throwable t) {
914            throw new RuntimeException(
915                    "If you see this test fail, it's likely that something about " +
916                    "sqlite's PRAGMA table_info(...) command has changed.", t);
917        }
918    }
919
920    @MediumTest
921    public void testInsertHelper() throws Exception {
922        Cursor cur;
923        ContentValues cv;
924        long row;
925
926        mDatabase.execSQL("CREATE TABLE insert_test (" +
927                "_id INTEGER PRIMARY KEY, " +
928                "s TEXT NOT NULL UNIQUE, " +
929                "t TEXT NOT NULL DEFAULT 'hello world', " +
930                "i INTEGER, " +
931                "j INTEGER NOT NULL DEFAULT 1234, " +
932                "'select' TEXT)");
933
934        DatabaseUtils.InsertHelper ih =
935            new DatabaseUtils.InsertHelper(mDatabase, "insert_test");
936
937        cv = new ContentValues();
938        cv.put("s", "one");
939        row = ih.insert(cv);
940        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
941        Assert.assertTrue(cur.moveToFirst());
942        Assert.assertEquals("one", cur.getString(1));
943        Assert.assertEquals("hello world", cur.getString(2));
944        Assert.assertNull(cur.getString(3));
945        Assert.assertEquals(1234, cur.getLong(4));
946        Assert.assertNull(cur.getString(5));
947        cur.close();
948
949        cv = new ContentValues();
950        cv.put("s", "two");
951        cv.put("t", "goodbye world");
952        row = ih.insert(cv);
953        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
954        Assert.assertTrue(cur.moveToFirst());
955        Assert.assertEquals("two", cur.getString(1));
956        Assert.assertEquals("goodbye world", cur.getString(2));
957        Assert.assertNull(cur.getString(3));
958        Assert.assertEquals(1234, cur.getLong(4));
959        Assert.assertNull(cur.getString(5));
960        cur.close();
961
962        cv = new ContentValues();
963        cv.put("t", "goodbye world");
964        row = ih.insert(cv);
965        Assert.assertEquals(-1, row);
966
967        cv = new ContentValues();
968        cv.put("s", "three");
969        cv.put("i", 2345);
970        cv.put("j", 3456);
971        cv.put("select", "tricky");
972        row = ih.insert(cv);
973        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
974        Assert.assertTrue(cur.moveToFirst());
975        Assert.assertEquals("three", cur.getString(1));
976        Assert.assertEquals("hello world", cur.getString(2));
977        Assert.assertEquals(2345, cur.getLong(3));
978        Assert.assertEquals(3456, cur.getLong(4));
979        Assert.assertEquals("tricky", cur.getString(5));
980        cur.close();
981
982        cv = new ContentValues();
983        cv.put("s", "three");
984        cv.put("i", 6789);
985        row = ih.insert(cv);
986        Assert.assertEquals(-1, row);
987        row = ih.replace(cv);
988        cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null);
989        Assert.assertTrue(cur.moveToFirst());
990        Assert.assertEquals("three", cur.getString(1));
991        Assert.assertEquals("hello world", cur.getString(2));
992        Assert.assertEquals(6789, cur.getLong(3));
993        cur.close();
994
995        ih.close();
996    }
997
998    @MediumTest
999    public void testDbCloseReleasingAllCachedSql() {
1000        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, text1 TEXT, text2 TEXT, " +
1001                "num1 INTEGER, num2 INTEGER, image BLOB);");
1002        final String statement = "DELETE FROM test WHERE _id=?;";
1003        SQLiteStatement statementDoNotClose = mDatabase.compileStatement(statement);
1004        assertTrue(statementDoNotClose.getUniqueId() > 0);
1005        int nStatement = statementDoNotClose.getUniqueId();
1006        assertTrue(statementDoNotClose.getUniqueId() == nStatement);
1007        /* do not close statementDoNotClose object.
1008         * That should leave it in SQLiteDatabase.mPrograms.
1009         * mDatabase.close() in tearDown() should release it.
1010         */
1011    }
1012
1013    @MediumTest
1014    public void testSemicolonsInStatements() throws Exception {
1015        mDatabase.execSQL("CREATE TABLE pragma_test (" +
1016                "i INTEGER DEFAULT 1234, " +
1017                "j INTEGER, " +
1018                "s TEXT DEFAULT 'hello', " +
1019                "t TEXT, " +
1020                "'select' TEXT DEFAULT \"hello\")");
1021        try {
1022            // ending the sql statement with  semicolons shouldn't be a problem.
1023            Cursor cur = mDatabase.rawQuery("PRAGMA database_list;", null);
1024            cur.close();
1025            // two semicolons in the statement shouldn't be a problem.
1026            cur = mDatabase.rawQuery("PRAGMA database_list;;", null);
1027            cur.close();
1028        } catch (Throwable t) {
1029            fail("unexpected, of course");
1030        }
1031    }
1032}
1033