src.android.database.SQLiteDatabasePerfTest Maven / Gradle / Ivy
Show all versions of android-all Show documentation
/*
* 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 androidx.test.InstrumentationRegistry;
import androidx.test.filters.LargeTest;
import androidx.test.runner.AndroidJUnit4;
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.runner.RunWith;
import java.util.Random;
/**
* Performance tests for typical CRUD operations and loading rows into the Cursor
*
* 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);
mDatabase = mContext.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
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))");
}
@After
public void tearDown() {
mDatabase.close();
mContext.deleteDatabase(DB_NAME);
}
@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 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));
}
}
}
@Test
public void testInsert() {
insertT1TestDataSet();
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()) {
assertEquals(DEFAULT_DATASET_SIZE, mDatabase.insert("T1", null, cv));
state.pauseTiming();
assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs));
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));
}
}
@Test
public void testUpdate() {
insertT1TestDataSet();
BenchmarkState state = mPerfStatusReporter.getBenchmarkState();
Random rnd = new Random(0);
int i = 0;
ContentValues cv = new ContentValues();
String[] argArray = new String[1];
while (state.keepRunning()) {
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++;
}
}
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();
}
}