1 /*
2  * Copyright (C) 2009 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.sqlite.cts;
18 
19 import static android.database.sqlite.cts.DatabaseTestUtils.getDbInfoOutput;
20 import static android.database.sqlite.cts.DatabaseTestUtils.waitForConnectionToClose;
21 
22 import static org.junit.Assert.assertEquals;
23 import static org.junit.Assert.assertFalse;
24 import static org.junit.Assert.assertNotNull;
25 import static org.junit.Assert.assertNull;
26 import static org.junit.Assert.assertTrue;
27 import static org.junit.Assert.fail;
28 
29 import android.app.ActivityManager;
30 import android.content.ContentValues;
31 import android.content.Context;
32 import android.database.Cursor;
33 import android.database.DatabaseUtils;
34 import android.database.SQLException;
35 import android.database.sqlite.Flags;
36 import android.database.sqlite.SQLiteCantOpenDatabaseException;
37 import android.database.sqlite.SQLiteCursor;
38 import android.database.sqlite.SQLiteCursorDriver;
39 import android.database.sqlite.SQLiteDatabase;
40 import android.database.sqlite.SQLiteDatabase.CursorFactory;
41 import android.database.sqlite.SQLiteDebug;
42 import android.database.sqlite.SQLiteException;
43 import android.database.sqlite.SQLiteGlobal;
44 import android.database.sqlite.SQLiteQuery;
45 import android.database.sqlite.SQLiteRawStatement;
46 import android.database.sqlite.SQLiteStatement;
47 import android.database.sqlite.SQLiteTransactionListener;
48 import android.icu.text.Collator;
49 import android.icu.util.ULocale;
50 import android.platform.test.annotations.RequiresFlagsEnabled;
51 import android.platform.test.flag.junit.CheckFlagsRule;
52 import android.platform.test.flag.junit.DeviceFlagsValueProvider;
53 import android.test.MoreAsserts;
54 import android.util.Log;
55 
56 import androidx.test.filters.LargeTest;
57 import androidx.test.filters.SmallTest;
58 import androidx.test.platform.app.InstrumentationRegistry;
59 import androidx.test.runner.AndroidJUnit4;
60 
61 import com.android.compatibility.common.util.CddTest;
62 
63 import org.junit.After;
64 import org.junit.Before;
65 import org.junit.Rule;
66 import org.junit.Test;
67 import org.junit.runner.RunWith;
68 
69 import java.io.File;
70 import java.io.IOException;
71 import java.util.ArrayList;
72 import java.util.Arrays;
73 import java.util.Collections;
74 import java.util.List;
75 import java.util.Locale;
76 import java.util.concurrent.ExecutorService;
77 import java.util.concurrent.Executors;
78 import java.util.concurrent.Phaser;
79 import java.util.concurrent.Semaphore;
80 import java.util.concurrent.TimeUnit;
81 import java.util.function.BinaryOperator;
82 import java.util.function.UnaryOperator;
83 import java.util.stream.Collectors;
84 
85 @RunWith(AndroidJUnit4.class)
86 @SmallTest
87 public class SQLiteDatabaseTest {
88 
89     private static final String TAG = "SQLiteDatabaseTest";
90 
91     private final Context mContext = InstrumentationRegistry.getInstrumentation().getContext();
92 
93     private SQLiteDatabase mDatabase;
94     private File mDatabaseFile;
95     private String mDatabaseFilePath;
96     private String mDatabaseDir;
97 
98     private boolean mTransactionListenerOnBeginCalled;
99     private boolean mTransactionListenerOnCommitCalled;
100     private boolean mTransactionListenerOnRollbackCalled;
101 
102     private static final String DATABASE_FILE_NAME = "database_test.db";
103     private static final String TABLE_NAME = "test";
104 
105     private static final int COLUMN_ID_INDEX = 0;
106     private static final int COLUMN_NAME_INDEX = 1;
107     private static final int COLUMN_AGE_INDEX = 2;
108     private static final int COLUMN_ADDR_INDEX = 3;
109     private static final String[] TEST_PROJECTION = new String[] {
110             "_id",      // 0
111             "name",     // 1
112             "age",      // 2
113             "address"   // 3
114     };
115 
getContext()116     private Context getContext() {
117         return mContext;
118     }
119 
120     @Rule
121     public final CheckFlagsRule mCheckFlagsRule =
122             DeviceFlagsValueProvider.createCheckFlagsRule();
123 
124     @Before
setUp()125     public void setUp() throws Exception {
126         getContext().deleteDatabase(DATABASE_FILE_NAME);
127         mDatabaseFilePath = getContext().getDatabasePath(DATABASE_FILE_NAME).getPath();
128         mDatabaseFile = getContext().getDatabasePath(DATABASE_FILE_NAME);
129         mDatabaseDir = mDatabaseFile.getParent();
130         mDatabaseFile.getParentFile().mkdirs(); // directory may not exist
131         mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null);
132         assertNotNull(mDatabase);
133 
134         mTransactionListenerOnBeginCalled = false;
135         mTransactionListenerOnCommitCalled = false;
136         mTransactionListenerOnRollbackCalled = false;
137     }
138 
139     @After
tearDown()140     public void tearDown() throws Exception {
141         closeAndDeleteDatabase();
142     }
143 
closeAndDeleteDatabase()144     private void closeAndDeleteDatabase() {
145         mDatabase.close();
146         SQLiteDatabase.deleteDatabase(mDatabaseFile);
147     }
148 
149     @Test
testOpenDatabase()150     public void testOpenDatabase() {
151         CursorFactory factory = MockSQLiteCursor::new;
152         SQLiteDatabase db = SQLiteDatabase.openDatabase(mDatabaseFilePath,
153                 factory, SQLiteDatabase.CREATE_IF_NECESSARY);
154         assertNotNull(db);
155         db.close();
156 
157         File dbFile = new File(mDatabaseDir, "database_test12345678.db");
158         dbFile.delete();
159         assertFalse(dbFile.exists());
160         db = SQLiteDatabase.openOrCreateDatabase(dbFile.getPath(), factory);
161         assertNotNull(db);
162         db.close();
163         dbFile.delete();
164 
165         dbFile = new File(mDatabaseDir, DATABASE_FILE_NAME);
166         db = SQLiteDatabase.openOrCreateDatabase(dbFile, factory);
167         assertNotNull(db);
168         db.close();
169         dbFile.delete();
170 
171         db = SQLiteDatabase.create(factory);
172         assertNotNull(db);
173         db.close();
174     }
175 
176     @Test
testOpenDatabase_fail_no_path()177     public void testOpenDatabase_fail_no_path() {
178         CursorFactory factory = MockSQLiteCursor::new;
179         SQLiteDatabase db = null;
180         try {
181             db = SQLiteDatabase.openDatabase("filename.db",
182                     factory, SQLiteDatabase.CREATE_IF_NECESSARY);
183         } catch (SQLiteCantOpenDatabaseException e) {
184             assertTrue(
185                     "Wrong exception message: " + e.getMessage(),
186                     e.getMessage().contains("Directory not specified in the file path"));
187             assertFalse(
188                     "Wrong exception message: " + e.getMessage(),
189                     e.getMessage().contains("Unknown reason"));
190         } finally {
191             if (db != null) {
192                 db.close();
193             }
194         }
195     }
196 
197     @Test
testOpenDatabase_fail_root_path_create_if_necessary()198     public void testOpenDatabase_fail_root_path_create_if_necessary() {
199         CursorFactory factory = MockSQLiteCursor::new;
200         SQLiteDatabase db = null;
201         try {
202             db = SQLiteDatabase.openDatabase("/filename.db",
203                     factory, SQLiteDatabase.CREATE_IF_NECESSARY);
204         } catch (SQLiteCantOpenDatabaseException e) {
205             assertTrue(
206                     "Wrong exception message: " + e.getMessage(),
207                     e.getMessage().contains(
208                             "File /filename.db doesn't exist and CREATE_IF_NECESSARY is set"));
209             assertFalse(
210                     "Wrong exception message: " + e.getMessage(),
211                     e.getMessage().contains("Unknown reason"));
212         } finally {
213             if (db != null) {
214                 db.close();
215             }
216         }
217     }
218 
219     @Test
testOpenDatabase_fail_root_path_no_create()220     public void testOpenDatabase_fail_root_path_no_create() {
221         CursorFactory factory = MockSQLiteCursor::new;
222         SQLiteDatabase db = null;
223         try {
224             db = SQLiteDatabase.openDatabase("/filename.db",
225                     factory, 0);
226         } catch (SQLiteCantOpenDatabaseException e) {
227             assertTrue(
228                     "Wrong exception message: " + e.getMessage(),
229                     e.getMessage().contains("File /filename.db doesn't exist"));
230             assertFalse(
231                     "Wrong exception message: " + e.getMessage(),
232                     e.getMessage().contains("CREATE_IF_NECESSARY"));
233             assertFalse(
234                     "Wrong exception message: " + e.getMessage(),
235                     e.getMessage().contains("Unknown reason"));
236         } finally {
237             if (db != null) {
238                 db.close();
239             }
240         }
241     }
242 
243     @Test
testDeleteDatabase()244     public void testDeleteDatabase() throws IOException {
245         File dbFile = new File(mDatabaseDir, "database_test12345678.db");
246         File journalFile = new File(dbFile.getPath() + "-journal");
247         File shmFile = new File(dbFile.getPath() + "-shm");
248         File walFile = new File(dbFile.getPath() + "-wal");
249         File mjFile1 = new File(dbFile.getPath() + "-mj00000000");
250         File mjFile2 = new File(dbFile.getPath() + "-mj00000001");
251         File innocentFile = new File(dbFile.getPath() + "-innocent");
252 
253         dbFile.createNewFile();
254         journalFile.createNewFile();
255         shmFile.createNewFile();
256         walFile.createNewFile();
257         mjFile1.createNewFile();
258         mjFile2.createNewFile();
259         innocentFile.createNewFile();
260 
261         boolean deleted = SQLiteDatabase.deleteDatabase(dbFile);
262         assertTrue(deleted);
263 
264         assertFalse(dbFile.exists());
265         assertFalse(journalFile.exists());
266         assertFalse(shmFile.exists());
267         assertFalse(walFile.exists());
268         assertFalse(mjFile1.exists());
269         assertFalse(mjFile2.exists());
270         assertTrue(innocentFile.exists());
271 
272         innocentFile.delete();
273 
274         boolean deletedAgain = SQLiteDatabase.deleteDatabase(dbFile);
275         assertFalse(deletedAgain);
276     }
277 
278     private class MockSQLiteCursor extends SQLiteCursor {
MockSQLiteCursor(SQLiteDatabase db, SQLiteCursorDriver driver, String editTable, SQLiteQuery query)279         public MockSQLiteCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
280                 String editTable, SQLiteQuery query) {
281             super(db, driver, editTable, query);
282         }
283     }
284 
285     @Test
testTransaction()286     public void testTransaction() {
287         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
288         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
289 
290         // test execSQL without any explicit transactions.
291         setNum(1);
292         assertNum(1);
293 
294         // Test a single-level transaction.
295         setNum(0);
296         assertFalse(mDatabase.inTransaction());
297         mDatabase.beginTransaction();
298         assertTrue(mDatabase.inTransaction());
299         setNum(1);
300         mDatabase.setTransactionSuccessful();
301         mDatabase.endTransaction();
302         assertFalse(mDatabase.inTransaction());
303         assertNum(1);
304         assertFalse(mDatabase.isDbLockedByCurrentThread());
305         assertFalse(mDatabase.isDbLockedByOtherThreads());
306 
307         // Test a rolled-back transaction.
308         setNum(0);
309         assertFalse(mDatabase.inTransaction());
310         mDatabase.beginTransaction();
311         setNum(1);
312         assertTrue(mDatabase.inTransaction());
313         mDatabase.endTransaction();
314         assertFalse(mDatabase.inTransaction());
315         assertNum(0);
316         assertFalse(mDatabase.isDbLockedByCurrentThread());
317         assertFalse(mDatabase.isDbLockedByOtherThreads());
318 
319         // it should throw IllegalStateException if we end a non-existent transaction.
320         assertThrowsIllegalState(new Runnable() {
321             public void run() {
322                 mDatabase.endTransaction();
323             }
324         });
325 
326         // it should throw IllegalStateException if a set a non-existent transaction as clean.
327         assertThrowsIllegalState(new Runnable() {
328             public void run() {
329                 mDatabase.setTransactionSuccessful();
330             }
331         });
332 
333         mDatabase.beginTransaction();
334         mDatabase.setTransactionSuccessful();
335         // it should throw IllegalStateException if we mark a transaction as clean twice.
336         assertThrowsIllegalState(new Runnable() {
337             public void run() {
338                 mDatabase.setTransactionSuccessful();
339             }
340         });
341         // it should throw IllegalStateException if we begin a transaction after marking the
342         // parent as clean.
343         assertThrowsIllegalState(new Runnable() {
344             public void run() {
345                 mDatabase.beginTransaction();
346             }
347         });
348         mDatabase.endTransaction();
349         assertFalse(mDatabase.isDbLockedByCurrentThread());
350         assertFalse(mDatabase.isDbLockedByOtherThreads());
351 
352         assertFalse(mDatabase.inTransaction());
353         // Test a two-level transaction.
354         setNum(0);
355         mDatabase.beginTransaction();
356         assertTrue(mDatabase.inTransaction());
357         mDatabase.beginTransaction();
358         assertTrue(mDatabase.inTransaction());
359         setNum(1);
360         mDatabase.setTransactionSuccessful();
361         mDatabase.endTransaction();
362         assertTrue(mDatabase.inTransaction());
363         mDatabase.setTransactionSuccessful();
364         mDatabase.endTransaction();
365         assertFalse(mDatabase.inTransaction());
366         assertNum(1);
367         assertFalse(mDatabase.isDbLockedByCurrentThread());
368         assertFalse(mDatabase.isDbLockedByOtherThreads());
369 
370         // Test rolling back an inner transaction.
371         setNum(0);
372         mDatabase.beginTransaction();
373         mDatabase.beginTransaction();
374         setNum(1);
375         mDatabase.endTransaction();
376         mDatabase.setTransactionSuccessful();
377         mDatabase.endTransaction();
378         assertNum(0);
379         assertFalse(mDatabase.isDbLockedByCurrentThread());
380         assertFalse(mDatabase.isDbLockedByOtherThreads());
381 
382         // Test rolling back an outer transaction.
383         setNum(0);
384         mDatabase.beginTransaction();
385         mDatabase.beginTransaction();
386         setNum(1);
387         mDatabase.setTransactionSuccessful();
388         mDatabase.endTransaction();
389         mDatabase.endTransaction();
390         assertNum(0);
391         assertFalse(mDatabase.isDbLockedByCurrentThread());
392         assertFalse(mDatabase.isDbLockedByOtherThreads());
393     }
394 
setNum(int num)395     private void setNum(int num) {
396         mDatabase.execSQL("UPDATE test SET num = " + num);
397     }
398 
assertNum(int num)399     private void assertNum(int num) {
400         assertEquals(num, DatabaseUtils.longForQuery(mDatabase,
401                 "SELECT num FROM test", null));
402     }
403 
assertThrowsIllegalState(Runnable r)404     private void assertThrowsIllegalState(Runnable r) {
405         try {
406             r.run();
407             fail("did not throw expected IllegalStateException");
408         } catch (IllegalStateException e) {
409         }
410     }
411 
collect(Cursor c)412     private static List<String> collect(Cursor c) {
413         List<String> res = new ArrayList<>();
414         while (c.moveToNext()) {
415             res.add(c.getString(0));
416         }
417         return res;
418     }
419 
420     @Test
testAccessMaximumSize()421     public void testAccessMaximumSize() {
422         long curMaximumSize = mDatabase.getMaximumSize();
423 
424         // the new maximum size is less than the current size.
425         mDatabase.setMaximumSize(curMaximumSize - 1);
426         assertEquals(curMaximumSize, mDatabase.getMaximumSize());
427 
428         // the new maximum size is more than the current size.
429         mDatabase.setMaximumSize(curMaximumSize + 1);
430         assertEquals(curMaximumSize + mDatabase.getPageSize(), mDatabase.getMaximumSize());
431         assertTrue(mDatabase.getMaximumSize() > curMaximumSize);
432     }
433 
434     @Test
testAccessPageSize()435     public void testAccessPageSize() {
436         File databaseFile = new File(mDatabaseDir, "database.db");
437         if (databaseFile.exists()) {
438             databaseFile.delete();
439         }
440         SQLiteDatabase database = null;
441         try {
442             database = SQLiteDatabase.openOrCreateDatabase(databaseFile.getPath(), null);
443 
444             long initialValue = database.getPageSize();
445             // check that this does not throw an exception
446             // setting a different page size may not be supported after the DB has been created
447             database.setPageSize(initialValue);
448             assertEquals(initialValue, database.getPageSize());
449 
450         } finally {
451             if (database != null) {
452                 database.close();
453                 databaseFile.delete();
454             }
455         }
456     }
457 
458     @Test
testCompileStatement()459     public void testCompileStatement() {
460         mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
461                 + "name TEXT, age INTEGER, address TEXT);");
462 
463         String name = "Mike";
464         int age = 21;
465         String address = "LA";
466 
467         // at the beginning, there is no record in the database.
468         Cursor cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null);
469         assertNotNull(cursor);
470         assertEquals(0, cursor.getCount());
471 
472         String sql = "INSERT INTO test (name, age, address) VALUES (?, ?, ?);";
473         SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
474         DatabaseUtils.bindObjectToProgram(insertStatement, 1, name);
475         DatabaseUtils.bindObjectToProgram(insertStatement, 2, age);
476         DatabaseUtils.bindObjectToProgram(insertStatement, 3, address);
477         insertStatement.execute();
478         insertStatement.close();
479         cursor.close();
480 
481         cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null);
482         assertNotNull(cursor);
483         assertEquals(1, cursor.getCount());
484         cursor.moveToNext();
485         assertEquals(name, cursor.getString(COLUMN_NAME_INDEX));
486         assertEquals(age, cursor.getInt(COLUMN_AGE_INDEX));
487         assertEquals(address, cursor.getString(COLUMN_ADDR_INDEX));
488         cursor.close();
489 
490         SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test");
491         deleteStatement.execute();
492 
493         cursor = mDatabase.query("test", null, null, null, null, null, null);
494         assertEquals(0, cursor.getCount());
495         cursor.deactivate();
496         deleteStatement.close();
497         cursor.close();
498     }
499 
500     @Test
testDelete()501     public void testDelete() {
502         mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
503                 + "name TEXT, age INTEGER, address TEXT);");
504         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');");
505         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');");
506         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jim', 35, 'Chicago');");
507 
508         // delete one record.
509         int count = mDatabase.delete(TABLE_NAME, "name = 'Mike'", null);
510         assertEquals(1, count);
511 
512         Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null,
513                 null, null, null, null);
514         assertNotNull(cursor);
515         // there are 2 records here.
516         assertEquals(2, cursor.getCount());
517         cursor.moveToFirst();
518         assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
519         assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
520         assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
521         cursor.moveToNext();
522         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
523         assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX));
524         assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX));
525         cursor.close();
526 
527         // delete another record.
528         count = mDatabase.delete(TABLE_NAME, "name = ?", new String[] { "Jack" });
529         assertEquals(1, count);
530 
531         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
532                 null, null);
533         assertNotNull(cursor);
534         // there are 1 records here.
535         assertEquals(1, cursor.getCount());
536         cursor.moveToFirst();
537         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
538         assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX));
539         assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX));
540         cursor.close();
541 
542         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');");
543         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');");
544 
545         // delete all records.
546         count = mDatabase.delete(TABLE_NAME, null, null);
547         assertEquals(3, count);
548 
549         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
550         assertNotNull(cursor);
551         assertEquals(0, cursor.getCount());
552         cursor.close();
553     }
554 
555     @Test
testExecSQL()556     public void testExecSQL() {
557         mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
558                 + "name TEXT, age INTEGER, address TEXT);");
559 
560         // add a new record.
561         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');");
562 
563         Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null,
564                 null, null, null, null);
565         assertNotNull(cursor);
566         assertEquals(1, cursor.getCount());
567         cursor.moveToFirst();
568         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
569         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
570         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
571         cursor.close();
572 
573         // add other new record.
574         mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');");
575 
576         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
577         assertNotNull(cursor);
578         assertEquals(2, cursor.getCount());
579         cursor.moveToFirst();
580         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
581         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
582         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
583         cursor.moveToNext();
584         assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
585         assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
586         assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
587         cursor.close();
588 
589         // delete a record.
590         mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Jack" });
591 
592         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
593         assertNotNull(cursor);
594         assertEquals(1, cursor.getCount());
595         cursor.moveToFirst();
596         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
597         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
598         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
599         cursor.close();
600 
601         // delete a non-exist record.
602         mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Wrong Name" });
603 
604         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
605         assertNotNull(cursor);
606         assertEquals(1, cursor.getCount());
607         cursor.moveToFirst();
608         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
609         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
610         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
611         cursor.close();
612 
613         try {
614             // execSQL can not use for query.
615             mDatabase.execSQL("SELECT * FROM test;");
616             fail("should throw SQLException.");
617         } catch (SQLException e) {
618         }
619 
620         // make sure execSQL can't be used to execute more than 1 sql statement at a time
621         mDatabase.execSQL("UPDATE test SET age = 40 WHERE name = 'Mike';" +
622                 "UPDATE test SET age = 50 WHERE name = 'Mike';");
623         // age should be updated to 40 not to 50
624         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
625         assertNotNull(cursor);
626         assertEquals(1, cursor.getCount());
627         cursor.moveToFirst();
628         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
629         assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
630         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
631         cursor.close();
632 
633         // make sure sql injection is NOT allowed or has no effect when using query()
634         String harmfulQuery = "name = 'Mike';UPDATE test SET age = 50 WHERE name = 'Mike'";
635         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, harmfulQuery, null, null, null, null);
636         assertNotNull(cursor);
637         assertEquals(1, cursor.getCount());
638         cursor.moveToFirst();
639         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
640         // row's age column SHOULD NOT be 50
641         assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
642         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
643         cursor.close();;
644     }
645 
646     @Test
testExecPerConnectionSQL()647     public void testExecPerConnectionSQL() {
648         final List<String> data = Arrays.asList(
649                 "ABC", "abc", "pinyin", "가나다", "바사", "테스트", "马",
650                 "嘛", "妈", "骂", "吗", "码", "玛", "麻", "中", "梵", "苹果", "久了", "伺候");
651         final String values = data.stream().map((d) -> "('" + d + "')")
652                 .collect(Collectors.joining(","));
653 
654         mDatabase.execSQL("CREATE TABLE employee (name TEXT);");
655         mDatabase.execSQL("INSERT INTO employee (name) VALUES " + values + ";");
656 
657         for (ULocale locale : new ULocale[] {
658                 new ULocale("zh"),
659                 new ULocale("zh@collation=pinyin"),
660                 new ULocale("zh@collation=stroke"),
661                 new ULocale("zh@collation=zhuyin"),
662         }) {
663             final String collationName = "cts_" + System.nanoTime();
664             mDatabase.execPerConnectionSQL("SELECT icu_load_collation(?, ?);",
665                     new Object[] { locale.getName(), collationName });
666 
667             // Assert that sorting is identical between SQLite and ICU4J
668             try (Cursor c = mDatabase.query(true, "employee", new String[] { "name" },
669                     null, null, null, null, "name COLLATE " + collationName + " ASC", null)) {
670                 data.sort(Collator.getInstance(locale));
671                 assertEquals(data, collect(c));
672             }
673         }
674     }
675 
676     @Test
testExecPerConnectionSQLPragma()677     public void testExecPerConnectionSQLPragma() {
678         mDatabase.execPerConnectionSQL("PRAGMA busy_timeout = 12000;", null);
679 
680         // Assert connection has busy timeout configured
681         try (Cursor c = mDatabase.rawQuery("PRAGMA busy_timeout;", null)) {
682             assertTrue(c.moveToNext());
683             assertEquals(c.getInt(0), 12000);
684         }
685     }
686 
687     @Test
testFindEditTable()688     public void testFindEditTable() {
689         String tables = "table1 table2 table3";
690         assertEquals("table1", SQLiteDatabase.findEditTable(tables));
691 
692         tables = "table1,table2,table3";
693         assertEquals("table1", SQLiteDatabase.findEditTable(tables));
694 
695         tables = "table1";
696         assertEquals("table1", SQLiteDatabase.findEditTable(tables));
697 
698         try {
699             SQLiteDatabase.findEditTable("");
700             fail("should throw IllegalStateException.");
701         } catch (IllegalStateException e) {
702         }
703     }
704 
705     @Test
testGetPath()706     public void testGetPath() {
707         assertEquals(mDatabaseFilePath, mDatabase.getPath());
708     }
709 
710     @Test
testAccessVersion()711     public void testAccessVersion() {
712         mDatabase.setVersion(1);
713         assertEquals(1, mDatabase.getVersion());
714 
715         mDatabase.setVersion(3);
716         assertEquals(3, mDatabase.getVersion());
717     }
718 
719     @Test
testInsert()720     public void testInsert() {
721         mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
722                 + "name TEXT, age INTEGER, address TEXT);");
723 
724         ContentValues values = new ContentValues();
725         values.put("name", "Jack");
726         values.put("age", 20);
727         values.put("address", "LA");
728         mDatabase.insert(TABLE_NAME, "name", values);
729 
730         Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null,
731                 null, null, null, null);
732         assertNotNull(cursor);
733         assertEquals(1, cursor.getCount());
734         cursor.moveToFirst();
735         assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
736         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
737         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
738         cursor.close();
739 
740         mDatabase.insert(TABLE_NAME, "name", null);
741         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
742                 null, null);
743         assertNotNull(cursor);
744         assertEquals(2, cursor.getCount());
745         cursor.moveToFirst();
746         assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
747         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
748         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
749         cursor.moveToNext();
750         assertNull(cursor.getString(COLUMN_NAME_INDEX));
751         cursor.close();
752 
753         values = new ContentValues();
754         values.put("Wrong Key", "Wrong value");
755         mDatabase.insert(TABLE_NAME, "name", values);
756         // there are still 2 records.
757         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
758                 null, null);
759         assertNotNull(cursor);
760         assertEquals(2, cursor.getCount());
761         cursor.close();
762 
763         // delete all record.
764         mDatabase.execSQL("DELETE FROM test;");
765 
766         values = new ContentValues();
767         values.put("name", "Mike");
768         values.put("age", 30);
769         values.put("address", "London");
770         mDatabase.insertOrThrow(TABLE_NAME, "name", values);
771 
772         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
773                 null, null);
774         assertNotNull(cursor);
775         assertEquals(1, cursor.getCount());
776         cursor.moveToFirst();
777         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
778         assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
779         assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
780         cursor.close();
781 
782         mDatabase.insertOrThrow(TABLE_NAME, "name", null);
783         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
784                 null, null);
785         assertNotNull(cursor);
786         assertEquals(2, cursor.getCount());
787         cursor.moveToFirst();
788         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
789         assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
790         assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
791         cursor.moveToNext();
792         assertNull(cursor.getString(COLUMN_NAME_INDEX));
793         cursor.close();
794 
795         values = new ContentValues();
796         values.put("Wrong Key", "Wrong value");
797         try {
798             mDatabase.insertOrThrow(TABLE_NAME, "name", values);
799             fail("should throw SQLException.");
800         } catch (SQLException e) {
801         }
802     }
803 
804     @Test
testIsOpen()805     public void testIsOpen() {
806         assertTrue(mDatabase.isOpen());
807 
808         mDatabase.close();
809         assertFalse(mDatabase.isOpen());
810     }
811 
812     @Test
testIsReadOnly()813     public void testIsReadOnly() {
814         assertFalse(mDatabase.isReadOnly());
815 
816         SQLiteDatabase database = null;
817         try {
818             database = SQLiteDatabase.openDatabase(mDatabaseFilePath, null,
819                     SQLiteDatabase.OPEN_READONLY);
820             assertTrue(database.isReadOnly());
821         } finally {
822             if (database != null) {
823                 database.close();
824             }
825         }
826     }
827 
828     @Test
testReleaseMemory()829     public void testReleaseMemory() {
830         SQLiteDatabase.releaseMemory();
831     }
832 
833     @Test
testSetLockingEnabled()834     public void testSetLockingEnabled() {
835         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
836         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
837 
838         mDatabase.setLockingEnabled(false);
839 
840         mDatabase.beginTransaction();
841         setNum(1);
842         assertNum(1);
843         mDatabase.setTransactionSuccessful();
844         mDatabase.endTransaction();
845     }
846 
847     @SuppressWarnings("deprecation")
848     @Test
testYieldIfContendedWhenNotContended()849     public void testYieldIfContendedWhenNotContended() {
850         assertFalse(mDatabase.yieldIfContended());
851 
852         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
853         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
854 
855         // Make sure that things work outside an explicit transaction.
856         setNum(1);
857         assertNum(1);
858 
859         setNum(0);
860         assertFalse(mDatabase.inTransaction());
861         mDatabase.beginTransaction();
862         assertTrue(mDatabase.inTransaction());
863         assertFalse(mDatabase.yieldIfContended());
864         setNum(1);
865         mDatabase.setTransactionSuccessful();
866         mDatabase.endTransaction();
867 
868         mDatabase.beginTransaction();
869         assertTrue(mDatabase.inTransaction());
870         assertFalse(mDatabase.yieldIfContendedSafely());
871         setNum(1);
872         mDatabase.setTransactionSuccessful();
873         mDatabase.endTransaction();
874     }
875 
876     @SuppressWarnings("deprecation")
877     @Test
testYieldIfContendedWhenContended()878     public void testYieldIfContendedWhenContended() throws Exception {
879         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
880         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
881 
882         // Begin a transaction and update a value.
883         mDatabase.beginTransaction();
884         setNum(1);
885         assertNum(1);
886 
887         // On another thread, begin a transaction there.  This causes contention
888         // for use of the database.  When the main thread yields, the second thread
889         // begin its own transaction.  It should perceive the new state that was
890         // committed by the main thread when it yielded.
891         final Semaphore s = new Semaphore(0);
892         Thread t = new Thread() {
893             @Override
894             public void run() {
895                 s.release(); // let main thread continue
896 
897                 mDatabase.beginTransaction();
898                 assertNum(1);
899                 setNum(2);
900                 assertNum(2);
901                 mDatabase.setTransactionSuccessful();
902                 mDatabase.endTransaction();
903             }
904         };
905         t.start();
906 
907         // Wait for thread to try to begin its transaction.
908         s.acquire();
909         Thread.sleep(500);
910 
911         // Yield.  There should be contention for the database now, so yield will
912         // return true.
913         assertTrue(mDatabase.yieldIfContendedSafely());
914 
915         // Since we reacquired the transaction, the other thread must have finished
916         // its transaction.  We should observe its changes and our own within this transaction.
917         assertNum(2);
918         setNum(3);
919         assertNum(3);
920 
921         // Go ahead and finish the transaction.
922         mDatabase.setTransactionSuccessful();
923         mDatabase.endTransaction();
924         assertNum(3);
925 
926         t.join();
927     }
928 
929     @Test
testQuery()930     public void testQuery() {
931         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
932                 "name TEXT, month INTEGER, salary INTEGER);");
933         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
934                 "VALUES ('Mike', '1', '1000');");
935         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
936                 "VALUES ('Mike', '2', '3000');");
937         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
938                 "VALUES ('jack', '1', '2000');");
939         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
940                 "VALUES ('jack', '3', '1500');");
941         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
942                 "VALUES ('Jim', '1', '1000');");
943         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
944                 "VALUES ('Jim', '3', '3500');");
945 
946         Cursor cursor = mDatabase.query(true, "employee", new String[] { "name", "sum(salary)" },
947                 null, null, "name", "sum(salary)>1000", "name", null);
948         assertNotNull(cursor);
949         assertEquals(3, cursor.getCount());
950 
951         final int COLUMN_NAME_INDEX = 0;
952         final int COLUMN_SALARY_INDEX = 1;
953         cursor.moveToFirst();
954         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
955         assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX));
956         cursor.moveToNext();
957         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
958         assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
959         cursor.moveToNext();
960         assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX));
961         assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX));
962         cursor.close();
963 
964         CursorFactory factory = new CursorFactory() {
965             public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
966                     String editTable, SQLiteQuery query) {
967                 return new MockSQLiteCursor(db, masterQuery, editTable, query);
968             }
969         };
970         cursor = mDatabase.queryWithFactory(factory, true, "employee",
971                 new String[] { "name", "sum(salary)" },
972                 null, null, "name", "sum(salary) > 1000", "name", null);
973         assertNotNull(cursor);
974         assertTrue(cursor instanceof MockSQLiteCursor);
975         cursor.moveToFirst();
976         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
977         assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX));
978         cursor.moveToNext();
979         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
980         assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
981         cursor.moveToNext();
982         assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX));
983         assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX));
984         cursor.close();
985 
986         cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" },
987                 null, null, "name", "sum(salary) <= 4000", "name");
988         assertNotNull(cursor);
989         assertEquals(2, cursor.getCount());
990 
991         cursor.moveToFirst();
992         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
993         assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
994         cursor.moveToNext();
995         assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX));
996         assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX));
997         cursor.close();
998 
999         cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" },
1000                 null, null, "name", "sum(salary) > 1000", "name", "2");
1001         assertNotNull(cursor);
1002         assertEquals(2, cursor.getCount());
1003 
1004         cursor.moveToFirst();
1005         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
1006         assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX));
1007         cursor.moveToNext();
1008         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
1009         assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
1010         cursor.close();
1011 
1012         String sql = "SELECT name, month FROM employee WHERE salary > ?;";
1013         cursor = mDatabase.rawQuery(sql, new String[] { "2000" });
1014         assertNotNull(cursor);
1015         assertEquals(2, cursor.getCount());
1016 
1017         final int COLUMN_MONTH_INDEX = 1;
1018         cursor.moveToFirst();
1019         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
1020         assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX));
1021         cursor.moveToNext();
1022         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
1023         assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX));
1024         cursor.close();
1025 
1026         cursor = mDatabase.rawQueryWithFactory(factory, sql, new String[] { "2000" }, null);
1027         assertNotNull(cursor);
1028         assertEquals(2, cursor.getCount());
1029         assertTrue(cursor instanceof MockSQLiteCursor);
1030         cursor.moveToFirst();
1031         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
1032         assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX));
1033         cursor.moveToNext();
1034         assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
1035         assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX));
1036         cursor.close();
1037     }
1038 
1039     private static UnaryOperator<String> sReverse = (arg) -> {
1040         if (arg == null) return null;
1041         final StringBuilder sb = new StringBuilder(arg.length());
1042         for (int i = arg.length() - 1; i >= 0; i--) {
1043             sb.append(arg.charAt(i));
1044         }
1045         return sb.toString();
1046     };
1047 
1048     @Test
testCustomScalarFunction()1049     public void testCustomScalarFunction() {
1050         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1051                 "name TEXT, month INTEGER, salary INTEGER);");
1052         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1053                 "VALUES ('Mike', '1', '1000');");
1054         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1055                 "VALUES ('Jim', '2', '3000');");
1056 
1057         mDatabase.setCustomScalarFunction("CTS_REVERSE", sReverse);
1058         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1059                 "CTS_REVERSE(name)", "salary"
1060         }, null, null, null, null, "salary", null)) {
1061             assertTrue(c.moveToNext());
1062             assertEquals("ekiM", c.getString(0));
1063             assertEquals(1000, c.getLong(1));
1064 
1065             assertTrue(c.moveToNext());
1066             assertEquals("miJ", c.getString(0));
1067             assertEquals(3000, c.getLong(1));
1068         }
1069     }
1070 
1071     @Test
testCustomScalarFunction_Null()1072     public void testCustomScalarFunction_Null() {
1073         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1074                 "name TEXT, month INTEGER, salary INTEGER);");
1075         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1076                 "VALUES (NULL, '2', '2000');");
1077         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1078                 "VALUES (NULL, '3', '3000');");
1079         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1080                 "VALUES (NULL, '4', '4000');");
1081 
1082         mDatabase.setCustomScalarFunction("CTS_REVERSE", sReverse);
1083         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1084                 "CTS_REVERSE(name)", "salary"
1085         }, null, null, null, null, "salary", null)) {
1086             assertEquals(3, c.getCount());
1087             while (c.moveToNext()) {
1088                 assertTrue(c.isNull(0));
1089             }
1090         }
1091     }
1092 
1093     @Test
testCustomScalarFunction_Throws()1094     public void testCustomScalarFunction_Throws() {
1095         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1096                 "name TEXT, month INTEGER, salary INTEGER);");
1097         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1098                 "VALUES ('Mike', '1', '1000');");
1099 
1100         mDatabase.setCustomScalarFunction("CTS_THROWS", (arg) -> {
1101             // Anything thrown from Java is translated into a SQLITE_ERROR
1102             throw new IllegalArgumentException();
1103         });
1104         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1105                 "CTS_THROWS(name)", "salary"
1106         }, null, null, null, null, "salary", null)) {
1107             c.moveToFirst();
1108             fail();
1109         } catch (SQLException expected) {
1110         }
1111     }
1112 
1113     private static BinaryOperator<String> sLongest = (arg0, arg1) -> {
1114         if (arg0 == null) {
1115             return arg1;
1116         } else if (arg1 == null) {
1117             return arg0;
1118         } else {
1119             return (arg0.length() >= arg1.length()) ? arg0 : arg1;
1120         }
1121     };
1122 
1123     @Test
testCustomAggregateFunction()1124     public void testCustomAggregateFunction() {
1125         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1126                 "name TEXT, month INTEGER, salary INTEGER);");
1127         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1128                 "VALUES ('Mike', '1', '1000');");
1129         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1130                 "VALUES ('Jim', '2', '2000');");
1131         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1132                 "VALUES ('Robert', '3', '3000');");
1133 
1134         mDatabase.setCustomAggregateFunction("CTS_LONGEST", sLongest);
1135         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1136                 "CTS_LONGEST(name)",
1137         }, null, null, null, null, null, null)) {
1138             assertEquals(1, c.getCount());
1139             assertTrue(c.moveToFirst());
1140             assertEquals("Robert", c.getString(0));
1141         }
1142     }
1143 
1144     @Test
testCustomAggregateFunction_Zero()1145     public void testCustomAggregateFunction_Zero() {
1146         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1147                 "name TEXT, month INTEGER, salary INTEGER);");
1148 
1149         mDatabase.setCustomAggregateFunction("CTS_LONGEST", sLongest);
1150         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1151                 "CTS_LONGEST(name)",
1152         }, null, null, null, null, null, null)) {
1153             assertEquals(1, c.getCount());
1154             assertTrue(c.moveToFirst());
1155             assertTrue(c.isNull(0));
1156         }
1157     }
1158 
1159     @Test
testCustomAggregateFunction_One()1160     public void testCustomAggregateFunction_One() {
1161         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1162                 "name TEXT, month INTEGER, salary INTEGER);");
1163         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1164                 "VALUES ('Mike', '1', '1000');");
1165 
1166         mDatabase.setCustomAggregateFunction("CTS_LONGEST", sLongest);
1167         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1168                 "CTS_LONGEST(name)",
1169         }, null, null, null, null, null, null)) {
1170             assertEquals(1, c.getCount());
1171             assertTrue(c.moveToFirst());
1172             assertEquals("Mike", c.getString(0));
1173         }
1174     }
1175 
1176     @Test
testCustomAggregateFunction_OneNull()1177     public void testCustomAggregateFunction_OneNull() {
1178         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1179                 "name TEXT, month INTEGER, salary INTEGER);");
1180         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1181                 "VALUES (NULL, '1', '1000');");
1182 
1183         mDatabase.setCustomAggregateFunction("CTS_LONGEST", sLongest);
1184         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1185                 "CTS_LONGEST(name)",
1186         }, null, null, null, null, null, null)) {
1187             assertEquals(1, c.getCount());
1188             assertTrue(c.moveToFirst());
1189             assertTrue(c.isNull(0));
1190         }
1191     }
1192 
1193     @Test
testCustomAggregateFunction_Two()1194     public void testCustomAggregateFunction_Two() {
1195         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1196                 "name TEXT, month INTEGER, salary INTEGER);");
1197         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1198                 "VALUES ('Mike', '1', '1000');");
1199         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1200                 "VALUES ('Bob', '1', '1000');");
1201 
1202         mDatabase.setCustomAggregateFunction("CTS_LONGEST", sLongest);
1203         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1204                 "CTS_LONGEST(name)",
1205         }, null, null, null, null, null, null)) {
1206             assertEquals(1, c.getCount());
1207             assertTrue(c.moveToFirst());
1208             assertEquals("Mike", c.getString(0));
1209         }
1210     }
1211 
1212     @Test
testCustomAggregateFunction_TwoNull()1213     public void testCustomAggregateFunction_TwoNull() {
1214         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1215                 "name TEXT, month INTEGER, salary INTEGER);");
1216         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1217                 "VALUES (NULL, '1', '1000');");
1218         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1219                 "VALUES (NULL, '1', '1000');");
1220 
1221         mDatabase.setCustomAggregateFunction("CTS_LONGEST", sLongest);
1222         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1223                 "CTS_LONGEST(name)",
1224         }, null, null, null, null, null, null)) {
1225             assertEquals(1, c.getCount());
1226             assertTrue(c.moveToFirst());
1227             assertTrue(c.isNull(0));
1228         }
1229     }
1230 
1231     @Test
testCustomAggregateFunction_Throws()1232     public void testCustomAggregateFunction_Throws() {
1233         mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
1234                 "name TEXT, month INTEGER, salary INTEGER);");
1235         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1236                 "VALUES ('Mike', '1', '1000');");
1237         mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
1238                 "VALUES ('Bob', '1', '1000');");
1239 
1240         mDatabase.setCustomAggregateFunction("CTS_THROWS", (arg0, arg1) -> {
1241             // Anything thrown from Java is translated into a SQLITE_ERROR
1242             throw new IllegalArgumentException();
1243         });
1244         try (Cursor c = mDatabase.query(true, "employee", new String[] {
1245                 "CTS_THROWS(name)", "salary"
1246         }, null, null, null, null, "salary", null)) {
1247             c.moveToFirst();
1248             fail();
1249         } catch (SQLException expected) {
1250         }
1251     }
1252 
1253     @Test
testReplace()1254     public void testReplace() {
1255         mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
1256                 + "name TEXT, age INTEGER, address TEXT);");
1257 
1258         ContentValues values = new ContentValues();
1259         values.put("name", "Jack");
1260         values.put("age", 20);
1261         values.put("address", "LA");
1262         mDatabase.replace(TABLE_NAME, "name", values);
1263 
1264         Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION,
1265                 null, null, null, null, null);
1266         assertNotNull(cursor);
1267         assertEquals(1, cursor.getCount());
1268         cursor.moveToFirst();
1269         int id = cursor.getInt(COLUMN_ID_INDEX);
1270         assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
1271         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
1272         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
1273         cursor.close();
1274 
1275         values = new ContentValues();
1276         values.put("_id", id);
1277         values.put("name", "Mike");
1278         values.put("age", 40);
1279         values.put("address", "London");
1280         mDatabase.replace(TABLE_NAME, "name", values);
1281 
1282         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
1283         assertNotNull(cursor);
1284         assertEquals(1, cursor.getCount()); // there is still ONLY 1 record.
1285         cursor.moveToFirst();
1286         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
1287         assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
1288         assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
1289         cursor.close();
1290 
1291         values = new ContentValues();
1292         values.put("name", "Jack");
1293         values.put("age", 20);
1294         values.put("address", "LA");
1295         mDatabase.replaceOrThrow(TABLE_NAME, "name", values);
1296 
1297         cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
1298         assertNotNull(cursor);
1299         assertEquals(2, cursor.getCount());
1300         cursor.moveToFirst();
1301         assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
1302         assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
1303         assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
1304         cursor.moveToNext();
1305         assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
1306         assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
1307         assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
1308         cursor.close();
1309 
1310         values = new ContentValues();
1311         values.put("Wrong Key", "Wrong value");
1312         try {
1313             mDatabase.replaceOrThrow(TABLE_NAME, "name", values);
1314             fail("should throw SQLException.");
1315         } catch (SQLException e) {
1316         }
1317     }
1318 
1319     @Test
testUpdate()1320     public void testUpdate() {
1321         mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
1322 
1323         mDatabase.execSQL("INSERT INTO test (data) VALUES ('string1');");
1324         mDatabase.execSQL("INSERT INTO test (data) VALUES ('string2');");
1325         mDatabase.execSQL("INSERT INTO test (data) VALUES ('string3');");
1326 
1327         String updatedString = "this is an updated test";
1328         ContentValues values = new ContentValues(1);
1329         values.put("data", updatedString);
1330         assertEquals(1, mDatabase.update("test", values, "_id=1", null));
1331         Cursor cursor = mDatabase.query("test", null, "_id=1", null, null, null, null);
1332         assertNotNull(cursor);
1333         assertEquals(1, cursor.getCount());
1334         cursor.moveToFirst();
1335         String value = cursor.getString(cursor.getColumnIndexOrThrow("data"));
1336         assertEquals(updatedString, value);
1337         cursor.close();
1338     }
1339 
1340     @Test
testNeedUpgrade()1341     public void testNeedUpgrade() {
1342         mDatabase.setVersion(0);
1343         assertTrue(mDatabase.needUpgrade(1));
1344         mDatabase.setVersion(1);
1345         assertFalse(mDatabase.needUpgrade(1));
1346     }
1347 
1348     @Test
testSetLocale()1349     public void testSetLocale() {
1350         final String[] STRINGS = {
1351                 "c\u00f4t\u00e9",
1352                 "cote",
1353                 "c\u00f4te",
1354                 "cot\u00e9",
1355                 "boy",
1356                 "dog",
1357                 "COTE",
1358         };
1359 
1360         mDatabase.execSQL("CREATE TABLE test (data TEXT COLLATE LOCALIZED);");
1361         for (String s : STRINGS) {
1362             mDatabase.execSQL("INSERT INTO test VALUES('" + s + "');");
1363         }
1364 
1365         mDatabase.setLocale(new Locale("en", "US"));
1366 
1367         String sql = "SELECT data FROM test ORDER BY data COLLATE LOCALIZED ASC";
1368         Cursor cursor = mDatabase.rawQuery(sql, null);
1369         assertNotNull(cursor);
1370         ArrayList<String> items = new ArrayList<String>();
1371         while (cursor.moveToNext()) {
1372             items.add(cursor.getString(0));
1373         }
1374         String[] results = items.toArray(new String[items.size()]);
1375         assertEquals(STRINGS.length, results.length);
1376         cursor.close();
1377 
1378         // The database code currently uses PRIMARY collation strength,
1379         // meaning that all versions of a character compare equal (regardless
1380         // of case or accents), leaving the "cote" flavors in database order.
1381         MoreAsserts.assertEquals(results, new String[] {
1382                 STRINGS[4],  // "boy"
1383                 STRINGS[0],  // sundry forms of "cote"
1384                 STRINGS[1],
1385                 STRINGS[2],
1386                 STRINGS[3],
1387                 STRINGS[6],  // "COTE"
1388                 STRINGS[5],  // "dog"
1389         });
1390     }
1391 
1392     @Test
testOnAllReferencesReleased()1393     public void testOnAllReferencesReleased() {
1394         assertTrue(mDatabase.isOpen());
1395         mDatabase.releaseReference();
1396         assertFalse(mDatabase.isOpen());
1397     }
1398 
1399     @Test
testTransactionWithSQLiteTransactionListener()1400     public void testTransactionWithSQLiteTransactionListener() {
1401         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
1402         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
1403 
1404         assertEquals(mTransactionListenerOnBeginCalled, false);
1405         assertEquals(mTransactionListenerOnCommitCalled, false);
1406         assertEquals(mTransactionListenerOnRollbackCalled, false);
1407         mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener());
1408 
1409         // Assert that the transcation has started
1410         assertEquals(mTransactionListenerOnBeginCalled, true);
1411         assertEquals(mTransactionListenerOnCommitCalled, false);
1412         assertEquals(mTransactionListenerOnRollbackCalled, false);
1413 
1414         setNum(1);
1415 
1416         // State shouldn't have changed
1417         assertEquals(mTransactionListenerOnBeginCalled, true);
1418         assertEquals(mTransactionListenerOnCommitCalled, false);
1419         assertEquals(mTransactionListenerOnRollbackCalled, false);
1420 
1421         // commit the transaction
1422         mDatabase.setTransactionSuccessful();
1423         mDatabase.endTransaction();
1424 
1425         // the listener should have been told that commit was called
1426         assertEquals(mTransactionListenerOnBeginCalled, true);
1427         assertEquals(mTransactionListenerOnCommitCalled, true);
1428         assertEquals(mTransactionListenerOnRollbackCalled, false);
1429     }
1430 
1431     @Test
testRollbackTransactionWithSQLiteTransactionListener()1432     public void testRollbackTransactionWithSQLiteTransactionListener() {
1433         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
1434         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
1435 
1436         assertEquals(mTransactionListenerOnBeginCalled, false);
1437         assertEquals(mTransactionListenerOnCommitCalled, false);
1438         assertEquals(mTransactionListenerOnRollbackCalled, false);
1439         mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener());
1440 
1441         // Assert that the transcation has started
1442         assertEquals(mTransactionListenerOnBeginCalled, true);
1443         assertEquals(mTransactionListenerOnCommitCalled, false);
1444         assertEquals(mTransactionListenerOnRollbackCalled, false);
1445 
1446         setNum(1);
1447 
1448         // State shouldn't have changed
1449         assertEquals(mTransactionListenerOnBeginCalled, true);
1450         assertEquals(mTransactionListenerOnCommitCalled, false);
1451         assertEquals(mTransactionListenerOnRollbackCalled, false);
1452 
1453         // commit the transaction
1454         mDatabase.endTransaction();
1455 
1456         // the listener should have been told that commit was called
1457         assertEquals(mTransactionListenerOnBeginCalled, true);
1458         assertEquals(mTransactionListenerOnCommitCalled, false);
1459         assertEquals(mTransactionListenerOnRollbackCalled, true);
1460     }
1461 
1462     private class TestSQLiteTransactionListener implements SQLiteTransactionListener {
onBegin()1463         public void onBegin() {
1464             mTransactionListenerOnBeginCalled = true;
1465         }
1466 
onCommit()1467         public void onCommit() {
1468             mTransactionListenerOnCommitCalled = true;
1469         }
1470 
onRollback()1471         public void onRollback() {
1472             mTransactionListenerOnRollbackCalled = true;
1473         }
1474     }
1475 
1476     @Test
testGroupConcat()1477     public void testGroupConcat() {
1478         mDatabase.execSQL("CREATE TABLE test (i INT, j TEXT);");
1479 
1480         // insert 2 rows
1481         String sql = "INSERT INTO test (i) VALUES (?);";
1482         SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
1483         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
1484         insertStatement.execute();
1485         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 2);
1486         insertStatement.execute();
1487         insertStatement.close();
1488 
1489         // make sure there are 2 rows in the table
1490         Cursor cursor = mDatabase.rawQuery("SELECT count(*) FROM test", null);
1491         assertNotNull(cursor);
1492         assertEquals(1, cursor.getCount());
1493         cursor.moveToNext();
1494         assertEquals(2, cursor.getInt(0));
1495         cursor.close();
1496 
1497         // concatenate column j from all the rows. should return NULL
1498         cursor = mDatabase.rawQuery("SELECT group_concat(j, ' ') FROM test", null);
1499         assertNotNull(cursor);
1500         assertEquals(1, cursor.getCount());
1501         cursor.moveToNext();
1502         assertNull(cursor.getString(0));
1503         cursor.close();
1504 
1505         // drop the table
1506         mDatabase.execSQL("DROP TABLE test;");
1507         // should get no exceptions
1508     }
1509 
1510     @Test
testSchemaChanges()1511     public void testSchemaChanges() {
1512         mDatabase.execSQL("CREATE TABLE test (i INT, j INT);");
1513 
1514         // at the beginning, there is no record in the database.
1515         Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null);
1516         assertNotNull(cursor);
1517         assertEquals(0, cursor.getCount());
1518         cursor.close();
1519 
1520         String sql = "INSERT INTO test VALUES (?, ?);";
1521         SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
1522         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
1523         DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2);
1524         insertStatement.execute();
1525         insertStatement.close();
1526 
1527         // read the data from the table and make sure it is correct
1528         cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
1529         assertNotNull(cursor);
1530         assertEquals(1, cursor.getCount());
1531         cursor.moveToNext();
1532         assertEquals(1, cursor.getInt(0));
1533         assertEquals(2, cursor.getInt(1));
1534         cursor.close();
1535 
1536         // alter the table and execute another statement
1537         mDatabase.execSQL("ALTER TABLE test ADD COLUMN k int;");
1538         sql = "INSERT INTO test VALUES (?, ?, ?);";
1539         insertStatement = mDatabase.compileStatement(sql);
1540         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3);
1541         DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4);
1542         DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5);
1543         insertStatement.execute();
1544         insertStatement.close();
1545 
1546         // read the data from the table and make sure it is correct
1547         cursor = mDatabase.rawQuery("SELECT i,j,k FROM test", null);
1548         assertNotNull(cursor);
1549         assertEquals(2, cursor.getCount());
1550         cursor.moveToNext();
1551         assertEquals(1, cursor.getInt(0));
1552         assertEquals(2, cursor.getInt(1));
1553         assertNull(cursor.getString(2));
1554         cursor.moveToNext();
1555         assertEquals(3, cursor.getInt(0));
1556         assertEquals(4, cursor.getInt(1));
1557         assertEquals(5, cursor.getInt(2));
1558         cursor.close();
1559 
1560         // make sure the old statement - which should *try to reuse* cached query plan -
1561         // still works
1562         cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
1563         assertNotNull(cursor);
1564         assertEquals(2, cursor.getCount());
1565         cursor.moveToNext();
1566         assertEquals(1, cursor.getInt(0));
1567         assertEquals(2, cursor.getInt(1));
1568         cursor.moveToNext();
1569         assertEquals(3, cursor.getInt(0));
1570         assertEquals(4, cursor.getInt(1));
1571         cursor.close();
1572 
1573         SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test");
1574         deleteStatement.execute();
1575         deleteStatement.close();
1576     }
1577 
1578     @Test
testSchemaChangesNewTable()1579     public void testSchemaChangesNewTable() {
1580         mDatabase.execSQL("CREATE TABLE test (i INT, j INT);");
1581 
1582         // at the beginning, there is no record in the database.
1583         Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null);
1584         assertNotNull(cursor);
1585         assertEquals(0, cursor.getCount());
1586         cursor.close();
1587 
1588         String sql = "INSERT INTO test VALUES (?, ?);";
1589         SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
1590         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
1591         DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2);
1592         insertStatement.execute();
1593         insertStatement.close();
1594 
1595         // read the data from the table and make sure it is correct
1596         cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
1597         assertNotNull(cursor);
1598         assertEquals(1, cursor.getCount());
1599         cursor.moveToNext();
1600         assertEquals(1, cursor.getInt(0));
1601         assertEquals(2, cursor.getInt(1));
1602         cursor.close();
1603 
1604         // alter the table and execute another statement
1605         mDatabase.execSQL("CREATE TABLE test_new (i INT, j INT, k INT);");
1606         sql = "INSERT INTO test_new VALUES (?, ?, ?);";
1607         insertStatement = mDatabase.compileStatement(sql);
1608         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3);
1609         DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4);
1610         DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5);
1611         insertStatement.execute();
1612         insertStatement.close();
1613 
1614         // read the data from the table and make sure it is correct
1615         cursor = mDatabase.rawQuery("SELECT i,j,k FROM test_new", null);
1616         assertNotNull(cursor);
1617         assertEquals(1, cursor.getCount());
1618         cursor.moveToNext();
1619         assertEquals(3, cursor.getInt(0));
1620         assertEquals(4, cursor.getInt(1));
1621         assertEquals(5, cursor.getInt(2));
1622         cursor.close();
1623 
1624         // make sure the old statement - which should *try to reuse* cached query plan -
1625         // still works
1626         cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
1627         assertNotNull(cursor);
1628         assertEquals(1, cursor.getCount());
1629         cursor.moveToNext();
1630         assertEquals(1, cursor.getInt(0));
1631         assertEquals(2, cursor.getInt(1));
1632         cursor.close();
1633 
1634         SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test");
1635         deleteStatement.execute();
1636         deleteStatement.close();
1637 
1638         SQLiteStatement deleteStatement2 = mDatabase.compileStatement("DELETE FROM test_new");
1639         deleteStatement2.execute();
1640         deleteStatement2.close();
1641     }
1642 
1643     @Test
testSchemaChangesDropTable()1644     public void testSchemaChangesDropTable() {
1645         mDatabase.execSQL("CREATE TABLE test (i INT, j INT);");
1646 
1647         // at the beginning, there is no record in the database.
1648         Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null);
1649         assertNotNull(cursor);
1650         assertEquals(0, cursor.getCount());
1651         cursor.close();
1652 
1653         String sql = "INSERT INTO test VALUES (?, ?);";
1654         SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
1655         DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
1656         DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2);
1657         insertStatement.execute();
1658         insertStatement.close();
1659 
1660         // read the data from the table and make sure it is correct
1661         cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
1662         assertNotNull(cursor);
1663         assertEquals(1, cursor.getCount());
1664         cursor.moveToNext();
1665         assertEquals(1, cursor.getInt(0));
1666         assertEquals(2, cursor.getInt(1));
1667     }
1668 
1669     /**
1670      * With sqlite's write-ahead-logging (WAL) enabled, readers get old version of data
1671      * from the table that a writer is modifying at the same time.
1672      * <p>
1673      * This method does the following to test this sqlite3 feature
1674      * <ol>
1675      *   <li>creates a table in the database and populates it with 5 rows of data</li>
1676      *   <li>do "select count(*) from this_table" and expect to receive 5</li>
1677      *   <li>start a writer thread who BEGINs a transaction, INSERTs a single row
1678      *   into this_table</li>
1679      *   <li>writer stops the transaction at this point, kicks off a reader thread - which will
1680      *       do  the above SELECT query: "select count(*) from this_table"</li>
1681      *   <li>this query should return value 5 - because writer is still in transaction and
1682      *    sqlite returns OLD version of the data</li>
1683      *   <li>writer ends the transaction, thus making the extra row now visible to everyone</li>
1684      *   <li>reader is kicked off again to do the same query. this time query should
1685      *   return value = 6 which includes the newly inserted row into this_table.</li>
1686      *</p>
1687      * @throws InterruptedException
1688      */
1689     @LargeTest
1690     @Test
testReaderGetsOldVersionOfDataWhenWriterIsInXact()1691     public void testReaderGetsOldVersionOfDataWhenWriterIsInXact() throws InterruptedException {
1692         // redo setup to create WAL enabled database
1693         closeAndDeleteDatabase();
1694         mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null);
1695         boolean rslt = mDatabase.enableWriteAheadLogging();
1696         assertTrue(rslt);
1697         assertNotNull(mDatabase);
1698 
1699         // create a new table and insert 5 records into it.
1700         mDatabase.execSQL("CREATE TABLE t1 (i int, j int);");
1701         mDatabase.beginTransaction();
1702         for (int i = 0; i < 5; i++) {
1703             mDatabase.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""});
1704         }
1705         mDatabase.setTransactionSuccessful();
1706         mDatabase.endTransaction();
1707 
1708         // make sure a reader can read the above data
1709         ReaderQueryingData r1 = new ReaderQueryingData(5);
1710         r1.start();
1711         Thread.yield();
1712         try {r1.join();} catch (Exception e) {}
1713 
1714         WriterDoingSingleTransaction w = new WriterDoingSingleTransaction();
1715         w.start();
1716         w.join();
1717     }
1718 
1719     private class WriterDoingSingleTransaction extends Thread {
run()1720         @Override public void run() {
1721             // start a transaction
1722             mDatabase.beginTransactionNonExclusive();
1723             mDatabase.execSQL("insert into t1 values(?,?);", new String[] {"11", "11"});
1724             assertTrue(mDatabase.isOpen());
1725 
1726             // while the writer is in a transaction, start a reader and make sure it can still
1727             // read 5 rows of data (= old data prior to the current transaction)
1728             ReaderQueryingData r1 = new ReaderQueryingData(5);
1729             r1.start();
1730             try {r1.join();} catch (Exception e) {}
1731 
1732             // now, have the writer do the select count(*)
1733             // it should execute on the same connection as this transaction
1734             // and count(*) should reflect the newly inserted row
1735             Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null);
1736             assertEquals(6, l.intValue());
1737 
1738             // end transaction
1739             mDatabase.setTransactionSuccessful();
1740             mDatabase.endTransaction();
1741 
1742             // reader should now be able to read 6 rows = new data AFTER this transaction
1743             r1 = new ReaderQueryingData(6);
1744             r1.start();
1745             try {r1.join();} catch (Exception e) {}
1746         }
1747     }
1748 
1749     private class ReaderQueryingData extends Thread {
1750         private int count;
1751         /**
1752          * constructor with a param to indicate the number of rows expected to be read
1753          */
ReaderQueryingData(int count)1754         public ReaderQueryingData(int count) {
1755             this.count = count;
1756         }
run()1757         @Override public void run() {
1758             Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null);
1759             assertEquals(count, l.intValue());
1760         }
1761     }
1762 
1763     @Test
testExceptionsFromEnableWriteAheadLogging()1764     public void testExceptionsFromEnableWriteAheadLogging() {
1765         // attach a database
1766         // redo setup to create WAL enabled database
1767         closeAndDeleteDatabase();
1768         mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null);
1769 
1770         // attach a database and call enableWriteAheadLogging - should not be allowed
1771         mDatabase.execSQL("attach database ':memory:' as memoryDb");
1772         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1773         assertFalse(mDatabase.enableWriteAheadLogging());
1774         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1775 
1776         // enableWriteAheadLogging on memory database is not allowed
1777         SQLiteDatabase db = SQLiteDatabase.create(null);
1778         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1779         assertFalse(db.enableWriteAheadLogging());
1780         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1781         db.close();
1782     }
1783 
1784     @Test
testEnableThenDisableWriteAheadLogging()1785     public void testEnableThenDisableWriteAheadLogging() {
1786         // Enable WAL.
1787         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1788         assertTrue(mDatabase.enableWriteAheadLogging());
1789         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1790         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1791                 .equalsIgnoreCase("WAL"));
1792 
1793         // Enabling when already enabled should have no observable effect.
1794         assertTrue(mDatabase.enableWriteAheadLogging());
1795         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1796         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1797                 .equalsIgnoreCase("WAL"));
1798 
1799         // Disabling when there are no connections should work.
1800         mDatabase.disableWriteAheadLogging();
1801         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1802     }
1803 
1804     @Test
testDisableWriteAheadLogging()1805     public void testDisableWriteAheadLogging() {
1806         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1807         mDatabase.disableWriteAheadLogging();
1808         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1809         // Verify that default journal mode is set if WAL is disabled
1810         String defaultJournalMode = SQLiteGlobal.getDefaultJournalMode();
1811         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1812                 .equalsIgnoreCase(defaultJournalMode));
1813     }
1814 
1815     @Test
testEnableThenDisableWriteAheadLoggingUsingOpenFlag()1816     public void testEnableThenDisableWriteAheadLoggingUsingOpenFlag() {
1817         closeAndDeleteDatabase();
1818         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile.getPath(), null,
1819                 SQLiteDatabase.CREATE_IF_NECESSARY | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING,
1820                 null);
1821         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1822         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1823                 .equalsIgnoreCase("WAL"));
1824 
1825         // Enabling when already enabled should have no observable effect.
1826         assertTrue(mDatabase.enableWriteAheadLogging());
1827         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1828         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1829                 .equalsIgnoreCase("WAL"));
1830 
1831         // Disabling when there are no connections should work.
1832         mDatabase.disableWriteAheadLogging();
1833         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1834     }
1835 
1836     @Test
testEnableWriteAheadLoggingFromContextUsingModeFlag()1837     public void testEnableWriteAheadLoggingFromContextUsingModeFlag() {
1838         // Without the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens without WAL.
1839         closeAndDeleteDatabase();
1840         mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME,
1841                 Context.MODE_PRIVATE, null);
1842         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1843 
1844         // With the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens with WAL.
1845         closeAndDeleteDatabase();
1846         mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME,
1847                 Context.MODE_PRIVATE | Context.MODE_ENABLE_WRITE_AHEAD_LOGGING, null);
1848         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1849         mDatabase.close();
1850     }
1851 
1852     @Test
testEnableWriteAheadLoggingShouldThrowIfTransactionInProgress()1853     public void testEnableWriteAheadLoggingShouldThrowIfTransactionInProgress() {
1854         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1855         String oldJournalMode = DatabaseUtils.stringForQuery(
1856                 mDatabase, "PRAGMA journal_mode", null);
1857 
1858         // Begin transaction.
1859         mDatabase.beginTransaction();
1860 
1861         try {
1862             // Attempt to enable WAL should fail.
1863             mDatabase.enableWriteAheadLogging();
1864             fail("Expected IllegalStateException");
1865         } catch (IllegalStateException ex) {
1866             // expected
1867         }
1868 
1869         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1870         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1871                 .equalsIgnoreCase(oldJournalMode));
1872     }
1873 
1874     @Test
testDisableWriteAheadLoggingShouldThrowIfTransactionInProgress()1875     public void testDisableWriteAheadLoggingShouldThrowIfTransactionInProgress() {
1876         // Enable WAL.
1877         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
1878         assertTrue(mDatabase.enableWriteAheadLogging());
1879         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1880 
1881         // Begin transaction.
1882         mDatabase.beginTransaction();
1883 
1884         try {
1885             // Attempt to disable WAL should fail.
1886             mDatabase.disableWriteAheadLogging();
1887             fail("Expected IllegalStateException");
1888         } catch (IllegalStateException ex) {
1889             // expected
1890         }
1891 
1892         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
1893         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
1894                 .equalsIgnoreCase("WAL"));
1895     }
1896 
1897     @Test
testEnableAndDisableForeignKeys()1898     public void testEnableAndDisableForeignKeys() {
1899         // Initially off.
1900         assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
1901 
1902         // Enable foreign keys.
1903         mDatabase.setForeignKeyConstraintsEnabled(true);
1904         assertEquals(1, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
1905 
1906         // Disable foreign keys.
1907         mDatabase.setForeignKeyConstraintsEnabled(false);
1908         assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
1909 
1910         // Cannot configure foreign keys if there are transactions in progress.
1911         mDatabase.beginTransaction();
1912         try {
1913             mDatabase.setForeignKeyConstraintsEnabled(true);
1914             fail("Expected IllegalStateException");
1915         } catch (IllegalStateException ex) {
1916             // expected
1917         }
1918         assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
1919         mDatabase.endTransaction();
1920 
1921         // Enable foreign keys should work again after transaction complete.
1922         mDatabase.setForeignKeyConstraintsEnabled(true);
1923         assertEquals(1, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
1924     }
1925 
1926     @Test
testOpenDatabaseLookasideConfig()1927     public void testOpenDatabaseLookasideConfig() {
1928         // First check that lookaside is enabled (except low-RAM devices)
1929         boolean expectDisabled = mContext.getSystemService(ActivityManager.class).isLowRamDevice();
1930         verifyLookasideStats(expectDisabled);
1931         // Reopen test db with lookaside disabled
1932         mDatabase.close();
1933         SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
1934                 .setLookasideConfig(0, 0).build();
1935         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
1936         verifyLookasideStats(true);
1937         // Reopen test db with custom lookaside config
1938         mDatabase.close();
1939         params = new SQLiteDatabase.OpenParams.Builder().setLookasideConfig(10000, 10).build();
1940         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
1941         // Lookaside is always disabled on low-RAM devices
1942         verifyLookasideStats(expectDisabled);
1943     }
1944 
1945     @Test
testOpenParamsSetLookasideConfigValidation()1946     public void testOpenParamsSetLookasideConfigValidation() {
1947         try {
1948             new SQLiteDatabase.OpenParams.Builder().setLookasideConfig(-1, 0).build();
1949             fail("Negative slot size should be rejected");
1950         } catch (IllegalArgumentException expected) {
1951         }
1952         try {
1953             new SQLiteDatabase.OpenParams.Builder().setLookasideConfig(0, -10).build();
1954             fail("Negative slot count should be rejected");
1955         } catch (IllegalArgumentException expected) {
1956         }
1957     }
1958 
verifyLookasideStats(boolean expectDisabled)1959     private void verifyLookasideStats(boolean expectDisabled) {
1960         boolean dbStatFound = false;
1961         SQLiteDebug.PagerStats info = SQLiteDebug.getDatabaseInfo();
1962         for (SQLiteDebug.DbStats dbStat : info.dbStats) {
1963             if (dbStat.dbName.endsWith(mDatabaseFile.getName()) && !dbStat.arePoolStats) {
1964                 dbStatFound = true;
1965                 Log.i(TAG, "Lookaside for " + dbStat.dbName + " " + dbStat.lookaside);
1966                 if (expectDisabled) {
1967                     assertTrue("lookaside slots count should be zero", dbStat.lookaside == 0);
1968                 } else {
1969                     assertTrue("lookaside slots count should be greater than zero",
1970                             dbStat.lookaside > 0);
1971                 }
1972             }
1973         }
1974         assertTrue("No dbstat found for " + mDatabaseFile.getName(), dbStatFound);
1975     }
1976 
1977     @Test
testCloseIdleConnection()1978     public void testCloseIdleConnection() throws Exception {
1979         mDatabase.close();
1980         SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
1981                 .setIdleConnectionTimeout(5000).build();
1982         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
1983         // Wait a bit and check that connection is still open
1984         Thread.sleep(600);
1985         String output = getDbInfoOutput();
1986         assertTrue("Connection #0 should be open. Output: " + output,
1987                 output.contains("Connection #0:"));
1988 
1989         // Now cause idle timeout and check that connection is closed
1990         // We wait up to 10 seconds, which is longer than required 1 s to accommodate for delays in
1991         // message processing when system is busy
1992         boolean connectionWasClosed = waitForConnectionToClose(20, 500);
1993         assertTrue("Connection #0 should be closed", connectionWasClosed);
1994     }
1995 
1996     @Test
testNoCloseIdleConnectionForAttachDb()1997     public void testNoCloseIdleConnectionForAttachDb() throws Exception {
1998         mDatabase.close();
1999         SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
2000                 .setIdleConnectionTimeout(50).build();
2001         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
2002         // Attach db and verify size of the list of attached databases (includes main db)
2003         assertEquals(1, mDatabase.getAttachedDbs().size());
2004         mDatabase.execSQL("ATTACH DATABASE ':memory:' as memdb");
2005         assertEquals(2, mDatabase.getAttachedDbs().size());
2006         // Wait longer (500ms) to catch cases when timeout processing was delayed
2007         boolean connectionWasClosed = waitForConnectionToClose(5, 100);
2008         assertFalse("Connection #0 should be open", connectionWasClosed);
2009     }
2010 
2011     @Test
testSetIdleConnectionTimeoutValidation()2012     public void testSetIdleConnectionTimeoutValidation() throws Exception {
2013         try {
2014             new SQLiteDatabase.OpenParams.Builder().setIdleConnectionTimeout(-1).build();
2015             fail("Negative timeout should be rejected");
2016         } catch (IllegalArgumentException expected) {
2017         }
2018     }
2019 
2020     @Test
testDefaultJournalModeNotWAL()2021     public void testDefaultJournalModeNotWAL() {
2022         String defaultJournalMode = SQLiteGlobal.getDefaultJournalMode();
2023         assertFalse("Default journal mode should not be WAL",
2024                 "WAL".equalsIgnoreCase(defaultJournalMode));
2025     }
2026 
2027     /**
2028      * Test that app can specify journal mode/synchronous mode
2029      */
2030     @Test
testJournalModeSynchronousModeOverride()2031     public void testJournalModeSynchronousModeOverride() {
2032         mDatabase.close();
2033         SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
2034                 .setJournalMode("DELETE").setSynchronousMode("OFF").build();
2035         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
2036 
2037         String journalMode = DatabaseUtils
2038                 .stringForQuery(mDatabase, "PRAGMA journal_mode", null);
2039 
2040         assertEquals("DELETE", journalMode.toUpperCase());
2041         String syncMode = DatabaseUtils
2042                 .stringForQuery(mDatabase, "PRAGMA synchronous", null);
2043 
2044         assertEquals("0", syncMode);
2045     }
2046 
2047     /**
2048      * Test that enableWriteAheadLogging is not affected by app's journal mode setting,
2049      * but app can still control synchronous mode.
2050      */
2051     @Test
testEnableWalOverridesJournalModeSyncModePreserved()2052     public void testEnableWalOverridesJournalModeSyncModePreserved() {
2053         mDatabase.close();
2054         SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
2055                 .setJournalMode("DELETE").setSynchronousMode("OFF").build();
2056         mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params);
2057         mDatabase.enableWriteAheadLogging();
2058 
2059         String journalMode = DatabaseUtils
2060                 .stringForQuery(mDatabase, "PRAGMA journal_mode", null);
2061 
2062         assertEquals("WAL", journalMode.toUpperCase());
2063         String syncMode = DatabaseUtils
2064                 .stringForQuery(mDatabase, "PRAGMA synchronous", null);
2065 
2066         assertEquals("0" /* OFF */, syncMode);
2067     }
2068 
2069     /**
2070      * This test starts a transaction and verifies that other threads are blocked on
2071      * accessing the database. Waiting threads should be unblocked once transaction is complete.
2072      *
2073      * This is done to ensure that Compatibility WAL follows the original transaction semantics of
2074      * {@link SQLiteDatabase} instance when {@link SQLiteDatabase#ENABLE_WRITE_AHEAD_LOGGING} flag
2075      * is not set.
2076      */
2077     @Test
testActiveTransactionIsBlocking()2078     public void testActiveTransactionIsBlocking() throws Exception {
2079         mDatabase.beginTransactionNonExclusive();
2080         mDatabase.execSQL("CREATE TABLE t1 (i int);");
2081         final List<Throwable> errors = new ArrayList<>();
2082 
2083         Thread readThread = new Thread(
2084                 () -> {
2085                     try {
2086                         DatabaseUtils.longForQuery(mDatabase, "SELECT count(*) from t1", null);
2087                     } catch (Throwable t) {
2088                         Log.e(TAG, "ReadThread failed", t);
2089                         errors.add(t);
2090                     }
2091                 });
2092         readThread.start();
2093         readThread.join(500L);
2094         assertTrue("ReadThread should be blocked while transaction is active",
2095                 readThread.isAlive());
2096 
2097         mDatabase.setTransactionSuccessful();
2098         mDatabase.endTransaction();
2099 
2100         readThread.join(500L);
2101         assertFalse("ReadThread should finish after transaction has ended",
2102                 readThread.isAlive());
2103 
2104         assertTrue("ReadThread failed with errors: " + errors, errors.isEmpty());
2105     }
2106 
2107     /**
2108      * Count the number of rows in the database <count> times.  The answer must match <expected>
2109      * every time.  Any errors are reported back to the main thread through the <errors>
2110      * array. The ticker forces the database reads to be interleaved with database operations from
2111      * the sibling threads.
2112      */
concurrentReadOnlyReader(SQLiteDatabase database, int count, long expected, List<Throwable> errors, Phaser ticker)2113     private void concurrentReadOnlyReader(SQLiteDatabase database, int count, long expected,
2114             List<Throwable> errors, Phaser ticker) {
2115         mDatabase.beginTransactionReadOnly();
2116         try {
2117             for (int i = count; i > 0; i--) {
2118                 ticker.arriveAndAwaitAdvance();
2119                 long r = DatabaseUtils.longForQuery(database, "SELECT count(*) from t1", null);
2120                 if (r != expected) {
2121                     // The type of the exception is not important.  Only the message matters.
2122                     throw new RuntimeException(
2123                         String.format("concurrentRead expected %d, got %d", expected, r));
2124                 }
2125             }
2126             mDatabase.setTransactionSuccessful();
2127         } catch (Throwable t) {
2128             errors.add(t);
2129         } finally {
2130             mDatabase.endTransaction();
2131             ticker.arriveAndDeregister();
2132         }
2133     }
2134 
2135     /**
2136      * Insert a new row <count> times.  Any errors are reported back to the main thread through
2137      * the <errors> array. The ticker forces the database reads to be interleaved with database
2138      * operations from the sibling threads.
2139      */
concurrentImmediateWriter(SQLiteDatabase database, int count, List<Throwable> errors, Phaser ticker)2140     private void concurrentImmediateWriter(SQLiteDatabase database, int count,
2141             List<Throwable> errors, Phaser ticker) {
2142         mDatabase.beginTransaction();
2143         try {
2144             int n = 100;
2145             for (int i = count; i > 0; i--) {
2146                 ticker.arriveAndAwaitAdvance();
2147                 mDatabase.execSQL(String.format("INSERT INTO t1 (i) VALUES (%d)", n++));
2148             }
2149             mDatabase.setTransactionSuccessful();
2150         } catch (Throwable t) {
2151             errors.add(t);
2152         } finally {
2153             mDatabase.endTransaction();
2154             ticker.arriveAndDeregister();
2155         }
2156     }
2157 
2158     /**
2159      * This test verifies that a read-only transaction can be started, and it is deferred.  A
2160      * deferred transaction does not take a database locks until the database is accessed.
2161      */
2162     @RequiresFlagsEnabled(Flags.FLAG_SQLITE_APIS_35)
2163     @Test
testReadOnlyTransaction()2164     public void testReadOnlyTransaction() throws Exception {
2165         // Enable WAL.
2166         assertFalse(mDatabase.isWriteAheadLoggingEnabled());
2167         assertTrue(mDatabase.enableWriteAheadLogging());
2168         assertTrue(mDatabase.isWriteAheadLoggingEnabled());
2169         assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
2170                 .equalsIgnoreCase("WAL"));
2171 
2172         // Create the t1 table and put some data in it.
2173         mDatabase.beginTransaction();
2174         try {
2175             mDatabase.execSQL("CREATE TABLE t1 (i int);");
2176             mDatabase.execSQL("INSERT INTO t1 (i) VALUES (2)");
2177             mDatabase.execSQL("INSERT INTO t1 (i) VALUES (3)");
2178             mDatabase.setTransactionSuccessful();
2179         } finally {
2180             mDatabase.endTransaction();
2181         }
2182 
2183         // Threads install errors in this array.
2184         final List<Throwable> errors = Collections.synchronizedList(new ArrayList<Throwable>());
2185 
2186         // This forces the read and write threads to execute in a lock-step, round-robin fashion.
2187         Phaser ticker = new Phaser(3);
2188 
2189         // Create three threads that will perform transactions.  One thread is a writer and two
2190         // are readers.  The intent is that the readers begin before the writer commits, so the
2191         // readers always see a database with two rows.
2192         Thread readerA = new Thread(() -> {
2193               concurrentReadOnlyReader(mDatabase, 4, 2, errors, ticker);
2194         });
2195         Thread readerB = new Thread(() -> {
2196               concurrentReadOnlyReader(mDatabase, 4, 2, errors, ticker);
2197         });
2198         Thread writerC = new Thread(() -> {
2199               concurrentImmediateWriter(mDatabase, 4, errors, ticker);
2200         });
2201 
2202         readerA.start();
2203         readerB.start();
2204         writerC.start();
2205 
2206         // All three threads should have completed.  Give the total set 1s.  The 10ms delay for
2207         // the second and third threads is just a small, positive number.
2208         readerA.join(1000);
2209         assertFalse(readerA.isAlive());
2210         readerB.join(10);
2211         assertFalse(readerB.isAlive());
2212         writerC.join(10);
2213         assertFalse(writerC.isAlive());
2214 
2215         // The writer added 4 rows to the database.
2216         long r = DatabaseUtils.longForQuery(mDatabase, "SELECT count(*) from t1", null);
2217         assertEquals(6, r);
2218 
2219         assertTrue("ReadThread failed with errors: " + errors, errors.isEmpty());
2220     }
2221 
2222     @Test
testTransactionWithListenerReadOnly()2223     public void testTransactionWithListenerReadOnly() {
2224         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
2225         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
2226 
2227         assertEquals(mTransactionListenerOnBeginCalled, false);
2228         assertEquals(mTransactionListenerOnCommitCalled, false);
2229         assertEquals(mTransactionListenerOnRollbackCalled, false);
2230         mDatabase.beginTransactionWithListenerReadOnly(new TestSQLiteTransactionListener());
2231 
2232         try {
2233 
2234             // Assert that the transaction has started
2235             assertEquals(mTransactionListenerOnBeginCalled, true);
2236             assertEquals(mTransactionListenerOnCommitCalled, false);
2237             assertEquals(mTransactionListenerOnRollbackCalled, false);
2238 
2239             assertNum(0);
2240 
2241             // State shouldn't have changed
2242             assertEquals(mTransactionListenerOnBeginCalled, true);
2243             assertEquals(mTransactionListenerOnCommitCalled, false);
2244             assertEquals(mTransactionListenerOnRollbackCalled, false);
2245 
2246             // commit the transaction
2247             mDatabase.setTransactionSuccessful();
2248         } finally {
2249             mDatabase.endTransaction();
2250         }
2251 
2252         // the listener should have been told that commit was called
2253         assertEquals(mTransactionListenerOnBeginCalled, true);
2254         assertEquals(mTransactionListenerOnCommitCalled, true);
2255         assertEquals(mTransactionListenerOnRollbackCalled, false);
2256     }
2257 
2258     @RequiresFlagsEnabled(Flags.FLAG_SQLITE_APIS_35)
2259     @Test
testTransactionReadOnlyIsReadOnly()2260     public void testTransactionReadOnlyIsReadOnly() {
2261         mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
2262         mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");
2263         mDatabase.beginTransactionReadOnly();
2264         try {
2265             try {
2266                 setNum(1);
2267                 fail("write operations are not permitted");
2268             } catch (AssertionError e) {
2269                 // Let the failure pass through
2270                 throw e;
2271             } catch (SQLiteException e) {
2272                 // This test is somewhat fragile as it depends on the text of the exception thrown
2273                 // from the SQLite APIs.
2274                 if (!e.getMessage().contains("connection is read-only")) {
2275                     throw e;
2276                 }
2277                 // The test passes because this exception was expected.
2278             }
2279         } finally {
2280             mDatabase.endTransaction();
2281         }
2282     }
2283 
2284     // Return true if the actual version matches the expected version.
versionIsOkay(int[] expected, int[] actual)2285     private static boolean versionIsOkay(int[] expected, int[] actual) {
2286         return expected[0] == actual[0]
2287                 && expected[1] == actual[1]
2288                 && expected[2] >= actual[2];
2289     }
2290 
2291     @Test
testSqliteLibraryVersion()2292     public void testSqliteLibraryVersion() {
2293         // SQLite uses semantic versioning in a form of MAJOR.MINOR.PATCH.  In general, the major
2294         // and minor versions must match exactly but the actual patch version can be higher than
2295         // what is expected.
2296         String fullVersionStr = DatabaseUtils
2297                 .stringForQuery(mDatabase, "select sqlite_version()", null);
2298         String[] strVersion = fullVersionStr.split("\\.");
2299         final int versionSize = 3;
2300         assertEquals("Unable to parse SQLite version " + fullVersionStr,
2301                 versionSize, strVersion.length);
2302 
2303         int[] actual = new int[versionSize];
2304         for (int i = 0; i < versionSize; i++) {
2305             // This will throw NumberFormatException if the version string is not a sequence of
2306             // unsigned integers.
2307             actual[i] = Integer.parseUnsignedInt(strVersion[i]);
2308         }
2309 
2310         // Compare the actual version to the permitted SQLite release.  The test can compare to
2311         // multiple releases here, if multiple releases are permitted.
2312         final int[] expectedVersion342 = { 3, 42, 0 };
2313         final int[] expectedVersion344 = { 3, 44, 3 };
2314         if (versionIsOkay(expectedVersion342, actual)
2315                 || versionIsOkay(expectedVersion344, actual)) {
2316             return;
2317         }
2318 
2319         // The current version does not match any of the permitted versions.
2320         fail("Invalid SQLite version " + fullVersionStr);
2321     }
2322 
2323     // http://b/147928666
2324     @Test
testDefaultLegacyAlterTableEnabled()2325     public void testDefaultLegacyAlterTableEnabled() {
2326         mDatabase.beginTransaction();
2327         mDatabase.execSQL("CREATE TABLE \"t1\" (\"c1\" INTEGER, PRIMARY KEY(\"c1\"));");
2328         mDatabase.execSQL("CREATE TABLE \"t2\" (\"c1\" INTEGER);");
2329         mDatabase.execSQL("CREATE VIEW \"v1\" AS SELECT c1 from t1;");
2330         mDatabase.execSQL("DROP TABLE t1;");
2331         // The following statement will fail to execute without the legacy flag because
2332         // we have a view in the schema with a dangling reference to a table that doesn't
2333         // exist any more.
2334         mDatabase.execSQL("ALTER TABLE \"t2\" RENAME TO \"t1\";");
2335         mDatabase.endTransaction();
2336     }
2337 
2338     @Test
testStatementDDLEvictsCache()2339     public void testStatementDDLEvictsCache() {
2340         // The following will be cached (key is SQL string)
2341         String selectQuery = "SELECT * FROM t1";
2342 
2343         mDatabase.beginTransaction();
2344         mDatabase.execSQL("CREATE TABLE `t1` (`c1` INTEGER NOT NULL PRIMARY KEY, data TEXT)");
2345         try (Cursor c = mDatabase.rawQuery(selectQuery, null)) {
2346             assertEquals(2, c.getColumnCount());
2347         }
2348         // Alter the schema in such a way that if the cached query is used it would produce wrong
2349         // results due to the change in column amounts.
2350         mDatabase.execSQL("ALTER TABLE `t1` RENAME TO `t1_old`");
2351         mDatabase.execSQL("CREATE TABLE `t1` (`c1` INTEGER NOT NULL PRIMARY KEY)");
2352         // Execute cached query (that should have been evicted), validating it sees the new schema.
2353         try (Cursor c = mDatabase.rawQuery(selectQuery, null)) {
2354             assertEquals(1, c.getColumnCount());
2355         }
2356         mDatabase.setTransactionSuccessful();
2357         mDatabase.endTransaction();
2358     }
2359 
2360     @Test
testStressDDLEvicts()2361     public void testStressDDLEvicts() {
2362         mDatabase.enableWriteAheadLogging();
2363         mDatabase.execSQL("CREATE TABLE `t1` (`c1` INTEGER NOT NULL PRIMARY KEY, data TEXT)");
2364         final int iterations = 1000;
2365         ExecutorService exec = Executors.newFixedThreadPool(2);
2366         exec.execute(() -> {
2367                     boolean pingPong = true;
2368                     for (int i = 0; i < iterations; i++) {
2369                         mDatabase.beginTransaction();
2370                         if (pingPong) {
2371                             mDatabase.execSQL("ALTER TABLE `t1` RENAME TO `t1_old`");
2372                             mDatabase.execSQL("CREATE TABLE `t1` (`c1` INTEGER NOT NULL "
2373                                 + "PRIMARY KEY)");
2374                             pingPong = false;
2375                         } else {
2376                             mDatabase.execSQL("DROP TABLE `t1`");
2377                             mDatabase.execSQL("ALTER TABLE `t1_old` RENAME TO `t1`");
2378                             pingPong = true;
2379                         }
2380                         mDatabase.setTransactionSuccessful();
2381                         mDatabase.endTransaction();
2382                     }
2383                 });
2384         exec.execute(() -> {
2385                     for (int i = 0; i < iterations; i++) {
2386                         try (Cursor c = mDatabase.rawQuery("SELECT * FROM t1", null)) {
2387                             c.getCount();
2388                         }
2389                     }
2390                 });
2391         try {
2392             exec.shutdown();
2393             assertTrue(exec.awaitTermination(1, TimeUnit.MINUTES));
2394         } catch (InterruptedException e) {
2395             fail("Timed out");
2396         }
2397     }
2398 
2399     /**
2400      * Create a database with one table with three columns.
2401      */
createComplexDatabase()2402     private void createComplexDatabase() {
2403         mDatabase.beginTransaction();
2404         try {
2405             mDatabase.execSQL("CREATE TABLE t1 (i int, d double, t text);");
2406             mDatabase.setTransactionSuccessful();
2407         } finally {
2408             mDatabase.endTransaction();
2409         }
2410     }
2411 
2412     /**
2413      * A three-value insert for the complex database.
2414      */
createComplexInsert()2415     private String createComplexInsert() {
2416         return "INSERT INTO t1 (i, d, t) VALUES (?1, ?2, ?3)";
2417     }
2418 
2419     @CddTest(requirements = { "3.1/C-0-1,C-0-3" })
2420     @RequiresFlagsEnabled(Flags.FLAG_SQLITE_APIS_35)
2421     @Test
testAutomaticCounters()2422     public void testAutomaticCounters() {
2423         final int size = 10;
2424 
2425         createComplexDatabase();
2426 
2427         // Put 10 lines in the database.
2428         mDatabase.beginTransaction();
2429         try {
2430             try (SQLiteRawStatement s = mDatabase.createRawStatement(createComplexInsert())) {
2431                 for (int i = 0; i < size; i++) {
2432                     int vi = i * 3;
2433                     double vd = i * 2.5;
2434                     String vt = String.format("text%02dvalue", i);
2435                     s.bindInt(1, vi);
2436                     s.bindDouble(2, vd);
2437                     s.bindText(3, vt);
2438                     boolean r = s.step();
2439                     // No row is returned by this query.
2440                     assertFalse(r);
2441                     s.reset();
2442                     assertEquals(i + 1, mDatabase.getLastInsertRowId());
2443                     assertEquals(1, mDatabase.getLastChangedRowCount());
2444                     assertEquals(i + 2, mDatabase.getTotalChangedRowCount());
2445                 }
2446             }
2447             mDatabase.setTransactionSuccessful();
2448         } finally {
2449             mDatabase.endTransaction();
2450         }
2451 
2452         // Put a second 10 lines in the database.
2453         mDatabase.beginTransaction();
2454         try {
2455             try (SQLiteRawStatement s = mDatabase.createRawStatement(createComplexInsert())) {
2456                 for (int i = 0; i < size; i++) {
2457                     int vi = i * 3;
2458                     double vd = i * 2.5;
2459                     String vt = String.format("text%02dvalue", i);
2460                     s.bindInt(1, vi);
2461                     s.bindDouble(2, vd);
2462                     s.bindText(3, vt);
2463                     boolean r = s.step();
2464                     // No row is returned by this query.
2465                     assertFalse(r);
2466                     s.reset();
2467                     assertEquals(size + i + 1, mDatabase.getLastInsertRowId());
2468                     assertEquals(1, mDatabase.getLastChangedRowCount());
2469                     assertEquals(size + i + 2, mDatabase.getTotalChangedRowCount());
2470                 }
2471             }
2472             mDatabase.setTransactionSuccessful();
2473         } finally {
2474             mDatabase.endTransaction();
2475         }
2476     }
2477 
2478     @CddTest(requirements = { "3.1/C-0-1,C-0-3" })
2479     @RequiresFlagsEnabled(Flags.FLAG_SQLITE_APIS_35)
2480     @Test
testAutomaticCountersOutsideTransactions()2481     public void testAutomaticCountersOutsideTransactions() {
2482         try {
2483             mDatabase.getLastChangedRowCount();
2484             fail("getLastChangedRowCount() succeeded outside a transaction");
2485         } catch (IllegalStateException e) {
2486             // This exception is expected.
2487         }
2488 
2489         try {
2490             mDatabase.getTotalChangedRowCount();
2491             fail("getTotalChangedRowCount() succeeded outside a transaction");
2492         } catch (IllegalStateException e) {
2493             // This exception is expected.
2494         }
2495     }
2496 }
2497