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