Add sources for API 34 https://dl.google.com/android/repository/sources-34_r01.zip Test: None Change-Id: I254306ce746dcadecd8f756a445c667d8fecbd2a
diff --git a/android-34/android/database/SQLiteDatabasePerfTest.java b/android-34/android/database/SQLiteDatabasePerfTest.java new file mode 100644 index 0000000..b7460cd --- /dev/null +++ b/android-34/android/database/SQLiteDatabasePerfTest.java
@@ -0,0 +1,558 @@ +/* + * Copyright (C) 2017 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License + */ + +package android.database; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +import android.content.ContentValues; +import android.content.Context; +import android.database.sqlite.SQLiteDatabase; +import android.perftests.utils.BenchmarkState; +import android.perftests.utils.PerfStatusReporter; +import android.util.Log; +import androidx.test.InstrumentationRegistry; +import androidx.test.filters.LargeTest; +import androidx.test.runner.AndroidJUnit4; +import java.io.File; +import java.util.ArrayList; +import java.util.Random; +import org.junit.After; +import org.junit.Before; +import org.junit.Rule; +import org.junit.Test; +import org.junit.runner.RunWith; + +/** + * Performance tests for typical CRUD operations and loading rows into the Cursor + * + * <p>To run: bit CorePerfTests:android.database.SQLiteDatabasePerfTest + */ +@RunWith(AndroidJUnit4.class) +@LargeTest +public class SQLiteDatabasePerfTest { + // TODO b/64262688 Add Concurrency tests to compare WAL vs DELETE read/write + private static final String DB_NAME = "dbperftest"; + private static final int DEFAULT_DATASET_SIZE = 1000; + + @Rule + public PerfStatusReporter mPerfStatusReporter = new PerfStatusReporter(); + private SQLiteDatabase mDatabase; + private Context mContext; + + @Before + public void setUp() { + mContext = InstrumentationRegistry.getTargetContext(); + mContext.deleteDatabase(DB_NAME); + + createOrOpenTestDatabase( + SQLiteDatabase.JOURNAL_MODE_TRUNCATE, SQLiteDatabase.SYNC_MODE_FULL); + } + + @After + public void tearDown() { + mDatabase.close(); + mContext.deleteDatabase(DB_NAME); + } + + private void createOrOpenTestDatabase(String journalMode, String syncMode) { + SQLiteDatabase.OpenParams.Builder paramsBuilder = new SQLiteDatabase.OpenParams.Builder(); + File dbFile = mContext.getDatabasePath(DB_NAME); + if (journalMode != null) { + paramsBuilder.setJournalMode(journalMode); + } + if (syncMode != null) { + paramsBuilder.setSynchronousMode(syncMode); + } + paramsBuilder.addOpenFlags(SQLiteDatabase.CREATE_IF_NECESSARY); + + mDatabase = SQLiteDatabase.openDatabase(dbFile, paramsBuilder.build()); + mDatabase.execSQL("CREATE TABLE T1 " + + "(_ID INTEGER PRIMARY KEY, COL_A INTEGER, COL_B VARCHAR(100), COL_C REAL)"); + mDatabase.execSQL("CREATE TABLE T2 (" + + "_ID INTEGER PRIMARY KEY, COL_A VARCHAR(100), T1_ID INTEGER," + + "FOREIGN KEY(T1_ID) REFERENCES T1 (_ID))"); + } + + @Test + public void testSelect() { + insertT1TestDataSet(); + + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + + Random rnd = new Random(0); + while (state.keepRunning()) { + int index = rnd.nextInt(DEFAULT_DATASET_SIZE); + try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 " + + "WHERE _ID=?", new String[]{String.valueOf(index)})) { + assertTrue(cursor.moveToNext()); + assertEquals(index, cursor.getInt(0)); + assertEquals(index, cursor.getInt(1)); + assertEquals("T1Value" + index, cursor.getString(2)); + assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d); + } + } + } + + @Test + public void testSelectCacheMissRate() { + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + + insertT1TestDataSet(); + + ArrayList<String> queryPool = new ArrayList<>(); + queryPool.add("SELECT _ID, COL_A, COL_B, COL_C FROM T1 WHERE _ID=?"); + queryPool.add("SELECT _ID FROM T1 WHERE _ID=?"); + queryPool.add("SELECT COL_A FROM T1 WHERE _ID=?"); + queryPool.add("SELECT COL_B FROM T1 WHERE _ID=?"); + queryPool.add("SELECT COL_C FROM T1 WHERE _ID=?"); + queryPool.add("SELECT _ID, COL_A FROM T1 WHERE _ID=?"); + queryPool.add("SELECT _ID, COL_B FROM T1 WHERE _ID=?"); + queryPool.add("SELECT _ID, COL_C FROM T1 WHERE _ID=?"); + queryPool.add("SELECT COL_A, COL_B FROM T1 WHERE _ID=?"); + queryPool.add("SELECT COL_A, COL_C FROM T1 WHERE _ID=?"); + queryPool.add("SELECT COL_B, COL_C FROM T1 WHERE _ID=?"); + while (state.keepRunning()) { + Random rnd = new Random(0); + + int queries = 1000; + for (int iQuery = 0; iQuery < queries; ++iQuery) { + int queryIndex = rnd.nextInt(queryPool.size()); + int index = rnd.nextInt(DEFAULT_DATASET_SIZE); + + try (Cursor cursor = mDatabase.rawQuery( + queryPool.get(queryIndex), new String[] {String.valueOf(index)})) { + assertTrue(cursor.moveToNext()); + } + } + } + + Log.d("testSelectMemory", + "cacheMissRate: " + mDatabase.getStatementCacheMissRate() + + "Total Statements: " + mDatabase.getTotalPreparedStatements() + + ". Misses: " + mDatabase.getTotalStatementCacheMisses()); + + // Make sure caching is working and our miss rate should definitely be less than 100% + // however, we would expect this number to be actually closer to 0. + assertTrue(mDatabase.getStatementCacheMissRate() < 1); + mDatabase.close(); + mContext.deleteDatabase(DB_NAME); + } + + @Test + public void testSelectMultipleRows() { + insertT1TestDataSet(); + + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + Random rnd = new Random(0); + final int querySize = 50; + while (state.keepRunning()) { + int index = rnd.nextInt(DEFAULT_DATASET_SIZE - querySize - 1); + try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 " + + "WHERE _ID BETWEEN ? and ? ORDER BY _ID", + new String[]{String.valueOf(index), String.valueOf(index + querySize - 1)})) { + int i = 0; + while(cursor.moveToNext()) { + assertEquals(index, cursor.getInt(0)); + assertEquals(index, cursor.getInt(1)); + assertEquals("T1Value" + index, cursor.getString(2)); + assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d); + index++; + i++; + } + assertEquals(querySize, i); + } + } + } + + @Test + public void testCursorIterateForward() { + // A larger dataset is needed to exceed default CursorWindow size + int datasetSize = DEFAULT_DATASET_SIZE * 50; + insertT1TestDataSet(datasetSize); + + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + while (state.keepRunning()) { + try (Cursor cursor = mDatabase + .rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 ORDER BY _ID", null)) { + int i = 0; + while(cursor.moveToNext()) { + assertEquals(i, cursor.getInt(0)); + assertEquals(i, cursor.getInt(1)); + assertEquals("T1Value" + i, cursor.getString(2)); + assertEquals(1.1 * i, cursor.getDouble(3), 0.0000001d); + i++; + } + assertEquals(datasetSize, i); + } + } + } + + @Test + public void testCursorIterateBackwards() { + // A larger dataset is needed to exceed default CursorWindow size + int datasetSize = DEFAULT_DATASET_SIZE * 50; + insertT1TestDataSet(datasetSize); + + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + while (state.keepRunning()) { + try (Cursor cursor = mDatabase + .rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 ORDER BY _ID", null)) { + int i = datasetSize - 1; + while(cursor.moveToPosition(i)) { + assertEquals(i, cursor.getInt(0)); + assertEquals(i, cursor.getInt(1)); + assertEquals("T1Value" + i, cursor.getString(2)); + assertEquals(1.1 * i, cursor.getDouble(3), 0.0000001d); + i--; + } + assertEquals(-1, i); + } + } + } + + @Test + public void testInnerJoin() { + mDatabase.setForeignKeyConstraintsEnabled(true); + mDatabase.beginTransaction(); + insertT1TestDataSet(); + insertT2TestDataSet(); + mDatabase.setTransactionSuccessful(); + mDatabase.endTransaction(); + + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + + Random rnd = new Random(0); + while (state.keepRunning()) { + int index = rnd.nextInt(1000); + try (Cursor cursor = mDatabase.rawQuery( + "SELECT T1._ID, T1.COL_A, T1.COL_B, T1.COL_C, T2.COL_A FROM T1 " + + "INNER JOIN T2 on T2.T1_ID=T1._ID WHERE T1._ID = ?", + new String[]{String.valueOf(index)})) { + assertTrue(cursor.moveToNext()); + assertEquals(index, cursor.getInt(0)); + assertEquals(index, cursor.getInt(1)); + assertEquals("T1Value" + index, cursor.getString(2)); + assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d); + assertEquals("T2Value" + index, cursor.getString(4)); + } + } + } + + /** + * This test measures the insertion of a single row into a database using DELETE journal and + * synchronous modes. + */ + @Test + public void testInsert() { + insertT1TestDataSet(); + + testInsertInternal("testInsert"); + } + + @Test + public void testInsertWithPersistFull() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_PERSIST, SQLiteDatabase.SYNC_MODE_FULL); + insertT1TestDataSet(); + testInsertInternal("testInsertWithPersistFull"); + } + + private void testInsertInternal(String traceTag) { + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + + ContentValues cv = new ContentValues(); + cv.put("_ID", DEFAULT_DATASET_SIZE); + cv.put("COL_B", "NewValue"); + cv.put("COL_C", 1.1); + String[] deleteArgs = new String[] {String.valueOf(DEFAULT_DATASET_SIZE)}; + + while (state.keepRunning()) { + android.os.Trace.beginSection(traceTag); + assertEquals(DEFAULT_DATASET_SIZE, mDatabase.insert("T1", null, cv)); + state.pauseTiming(); + assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs)); + state.resumeTiming(); + android.os.Trace.endSection(); + } + } + + /** + * This test measures the insertion of a single row into a database using WAL journal mode and + * NORMAL synchronous mode. + */ + @Test + public void testInsertWithWalNormalMode() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL); + insertT1TestDataSet(); + + testInsertInternal("testInsertWithWalNormalMode"); + } + + /** + * This test measures the insertion of a single row into a database using WAL journal mode and + * FULL synchronous mode. The goal is to see the difference between NORMAL vs FULL sync modes. + */ + @Test + public void testInsertWithWalFullMode() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_FULL); + + insertT1TestDataSet(); + + testInsertInternal("testInsertWithWalFullMode"); + } + + /** + * This test measures the insertion of a multiple rows in a single transaction using WAL journal + * mode and NORMAL synchronous mode. + */ + @Test + public void testBulkInsertWithWalNormalMode() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL); + testBulkInsertInternal("testBulkInsertWithWalNormalMode"); + } + + @Test + public void testBulkInsertWithPersistFull() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_PERSIST, SQLiteDatabase.SYNC_MODE_FULL); + testBulkInsertInternal("testBulkInsertWithPersistFull"); + } + + /** + * This test measures the insertion of a multiple rows in a single transaction using TRUNCATE + * journal mode and FULL synchronous mode. + */ + @Test + public void testBulkInsert() { + testBulkInsertInternal("testBulkInsert"); + } + + private void testBulkInsertInternal(String traceTag) { + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + + String[] statements = new String[DEFAULT_DATASET_SIZE]; + for (int i = 0; i < DEFAULT_DATASET_SIZE; ++i) { + statements[i] = "INSERT INTO T1 VALUES (?,?,?,?)"; + } + + while (state.keepRunning()) { + android.os.Trace.beginSection(traceTag); + mDatabase.beginTransaction(); + for (int i = 0; i < DEFAULT_DATASET_SIZE; ++i) { + mDatabase.execSQL(statements[i], new Object[] {i, i, "T1Value" + i, i * 1.1}); + } + mDatabase.setTransactionSuccessful(); + mDatabase.endTransaction(); + android.os.Trace.endSection(); + + state.pauseTiming(); + mDatabase.execSQL("DELETE FROM T1"); + state.resumeTiming(); + } + } + + @Test + public void testDelete() { + insertT1TestDataSet(); + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + String[] deleteArgs = new String[]{String.valueOf(DEFAULT_DATASET_SIZE)}; + Object[] insertsArgs = new Object[]{DEFAULT_DATASET_SIZE, DEFAULT_DATASET_SIZE, + "ValueToDelete", 1.1}; + + while (state.keepRunning()) { + state.pauseTiming(); + mDatabase.execSQL("INSERT INTO T1 VALUES (?, ?, ?, ?)", insertsArgs); + state.resumeTiming(); + assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs)); + } + } + + /** + * This test measures the update of a random row in a database. + */ + @Test + public void testUpdateWithWalNormalMode() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL); + insertT1TestDataSet(); + testUpdateInternal("testUpdateWithWalNormalMode"); + } + + @Test + public void testUpdateWithPersistFull() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_PERSIST, SQLiteDatabase.SYNC_MODE_FULL); + insertT1TestDataSet(); + testUpdateInternal("testUpdateWithPersistFull"); + } + + @Test + public void testUpdate() { + insertT1TestDataSet(); + testUpdateInternal("testUpdate"); + } + + private void testUpdateInternal(String traceTag) { + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + + Random rnd = new Random(0); + int i = 0; + ContentValues cv = new ContentValues(); + String[] argArray = new String[1]; + while (state.keepRunning()) { + android.os.Trace.beginSection(traceTag); + int id = rnd.nextInt(DEFAULT_DATASET_SIZE); + cv.put("COL_A", i); + cv.put("COL_B", "UpdatedValue"); + cv.put("COL_C", i); + argArray[0] = String.valueOf(id); + assertEquals(1, mDatabase.update("T1", cv, "_ID=?", argArray)); + i++; + android.os.Trace.endSection(); + } + } + + /** + * This test measures a multi-threaded read-write environment where there are 2 readers and + * 1 writer in the database using TRUNCATE journal mode and FULL syncMode. + */ + @Test + public void testMultithreadedReadWrite() { + insertT1TestDataSet(); + performMultithreadedReadWriteTest(); + } + + private void doReadLoop(int totalIterations) { + Random rnd = new Random(0); + int currentIteration = 0; + while (currentIteration < totalIterations) { + android.os.Trace.beginSection("ReadDatabase"); + int index = rnd.nextInt(DEFAULT_DATASET_SIZE); + try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 " + + "WHERE _ID=?", + new String[] {String.valueOf(index)})) { + cursor.moveToNext(); + cursor.getInt(0); + cursor.getInt(1); + cursor.getString(2); + cursor.getDouble(3); + } + ++currentIteration; + android.os.Trace.endSection(); + } + } + + private void doReadLoop(BenchmarkState state) { + Random rnd = new Random(0); + while (state.keepRunning()) { + android.os.Trace.beginSection("ReadDatabase"); + int index = rnd.nextInt(DEFAULT_DATASET_SIZE); + try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 " + + "WHERE _ID=?", + new String[] {String.valueOf(index)})) { + cursor.moveToNext(); + cursor.getInt(0); + cursor.getInt(1); + cursor.getString(2); + cursor.getDouble(3); + } + android.os.Trace.endSection(); + } + } + + private void doUpdateLoop(int totalIterations) { + SQLiteDatabase db = mContext.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null); + Random rnd = new Random(0); + int i = 0; + ContentValues cv = new ContentValues(); + String[] argArray = new String[1]; + + while (i < totalIterations) { + android.os.Trace.beginSection("UpdateDatabase"); + int id = rnd.nextInt(DEFAULT_DATASET_SIZE); + cv.put("COL_A", i); + cv.put("COL_B", "UpdatedValue"); + cv.put("COL_C", i); + argArray[0] = String.valueOf(id); + db.update("T1", cv, "_ID=?", argArray); + i++; + android.os.Trace.endSection(); + } + } + + /** + * This test measures a multi-threaded read-write environment where there are 2 readers and + * 1 writer in the database using WAL journal mode and NORMAL syncMode. + */ + @Test + public void testMultithreadedReadWriteWithWalNormal() { + recreateTestDatabase(SQLiteDatabase.JOURNAL_MODE_WAL, SQLiteDatabase.SYNC_MODE_NORMAL); + insertT1TestDataSet(); + + performMultithreadedReadWriteTest(); + } + + private void performMultithreadedReadWriteTest() { + int totalBGIterations = 10000; + // Writer - Fixed iterations to avoid consuming cycles from mainloop benchmark iterations + Thread updateThread = new Thread(() -> { doUpdateLoop(totalBGIterations); }); + + // Reader 1 - Fixed iterations to avoid consuming cycles from mainloop benchmark iterations + Thread readerThread = new Thread(() -> { doReadLoop(totalBGIterations); }); + + updateThread.start(); + readerThread.start(); + + // Reader 2 + BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); + doReadLoop(state); + + try { + updateThread.join(); + readerThread.join(); + } catch (Exception e) { + } + } + + private void insertT1TestDataSet() { + insertT1TestDataSet(DEFAULT_DATASET_SIZE); + } + + private void insertT1TestDataSet(int size) { + mDatabase.beginTransaction(); + for (int i = 0; i < size; i++) { + mDatabase.execSQL("INSERT INTO T1 VALUES (?, ?, ?, ?)", + new Object[]{i, i, "T1Value" + i, i * 1.1}); + } + mDatabase.setTransactionSuccessful(); + mDatabase.endTransaction(); + } + + private void insertT2TestDataSet() { + mDatabase.beginTransaction(); + for (int i = 0; i < DEFAULT_DATASET_SIZE; i++) { + mDatabase.execSQL("INSERT INTO T2 VALUES (?, ?, ?)", + new Object[]{i, "T2Value" + i, i}); + } + mDatabase.setTransactionSuccessful(); + mDatabase.endTransaction(); + } + + private void recreateTestDatabase(String journalMode, String syncMode) { + mDatabase.close(); + mContext.deleteDatabase(DB_NAME); + createOrOpenTestDatabase(journalMode, syncMode); + } +} +