1/* 2 * Copyright (C) 2017 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License 15 */ 16 17package android.database; 18 19import android.content.ContentValues; 20import android.content.Context; 21import android.database.sqlite.SQLiteDatabase; 22import android.perftests.utils.BenchmarkState; 23import android.perftests.utils.PerfStatusReporter; 24import android.support.test.InstrumentationRegistry; 25import android.support.test.filters.LargeTest; 26import android.support.test.runner.AndroidJUnit4; 27 28import org.junit.After; 29import org.junit.Before; 30import org.junit.Rule; 31import org.junit.Test; 32import org.junit.runner.RunWith; 33 34import java.util.Random; 35 36import static org.junit.Assert.assertEquals; 37import static org.junit.Assert.assertTrue; 38 39/** 40 * Performance tests for typical CRUD operations and loading rows into the Cursor 41 * 42 * <p>To run: bit CorePerfTests:android.database.SQLiteDatabasePerfTest 43 */ 44@RunWith(AndroidJUnit4.class) 45@LargeTest 46public class SQLiteDatabasePerfTest { 47 // TODO b/64262688 Add Concurrency tests to compare WAL vs DELETE read/write 48 private static final String DB_NAME = "dbperftest"; 49 private static final int DEFAULT_DATASET_SIZE = 1000; 50 51 @Rule 52 public PerfStatusReporter mPerfStatusReporter = new PerfStatusReporter(); 53 private SQLiteDatabase mDatabase; 54 private Context mContext; 55 56 @Before 57 public void setUp() { 58 mContext = InstrumentationRegistry.getTargetContext(); 59 mContext.deleteDatabase(DB_NAME); 60 mDatabase = mContext.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null); 61 mDatabase.execSQL("CREATE TABLE T1 " 62 + "(_ID INTEGER PRIMARY KEY, COL_A INTEGER, COL_B VARCHAR(100), COL_C REAL)"); 63 mDatabase.execSQL("CREATE TABLE T2 (" 64 + "_ID INTEGER PRIMARY KEY, COL_A VARCHAR(100), T1_ID INTEGER," 65 + "FOREIGN KEY(T1_ID) REFERENCES T1 (_ID))"); 66 } 67 68 @After 69 public void tearDown() { 70 mDatabase.close(); 71 mContext.deleteDatabase(DB_NAME); 72 } 73 74 @Test 75 public void testSelect() { 76 insertT1TestDataSet(); 77 78 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 79 80 Random rnd = new Random(0); 81 while (state.keepRunning()) { 82 int index = rnd.nextInt(DEFAULT_DATASET_SIZE); 83 try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 " 84 + "WHERE _ID=?", new String[]{String.valueOf(index)})) { 85 assertTrue(cursor.moveToNext()); 86 assertEquals(index, cursor.getInt(0)); 87 assertEquals(index, cursor.getInt(1)); 88 assertEquals("T1Value" + index, cursor.getString(2)); 89 assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d); 90 } 91 } 92 } 93 94 @Test 95 public void testSelectMultipleRows() { 96 insertT1TestDataSet(); 97 98 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 99 Random rnd = new Random(0); 100 final int querySize = 50; 101 while (state.keepRunning()) { 102 int index = rnd.nextInt(DEFAULT_DATASET_SIZE - querySize - 1); 103 try (Cursor cursor = mDatabase.rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 " 104 + "WHERE _ID BETWEEN ? and ? ORDER BY _ID", 105 new String[]{String.valueOf(index), String.valueOf(index + querySize - 1)})) { 106 int i = 0; 107 while(cursor.moveToNext()) { 108 assertEquals(index, cursor.getInt(0)); 109 assertEquals(index, cursor.getInt(1)); 110 assertEquals("T1Value" + index, cursor.getString(2)); 111 assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d); 112 index++; 113 i++; 114 } 115 assertEquals(querySize, i); 116 } 117 } 118 } 119 120 @Test 121 public void testCursorIterateForward() { 122 // A larger dataset is needed to exceed default CursorWindow size 123 int datasetSize = DEFAULT_DATASET_SIZE * 50; 124 insertT1TestDataSet(datasetSize); 125 126 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 127 while (state.keepRunning()) { 128 try (Cursor cursor = mDatabase 129 .rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 ORDER BY _ID", null)) { 130 int i = 0; 131 while(cursor.moveToNext()) { 132 assertEquals(i, cursor.getInt(0)); 133 assertEquals(i, cursor.getInt(1)); 134 assertEquals("T1Value" + i, cursor.getString(2)); 135 assertEquals(1.1 * i, cursor.getDouble(3), 0.0000001d); 136 i++; 137 } 138 assertEquals(datasetSize, i); 139 } 140 } 141 } 142 143 @Test 144 public void testCursorIterateBackwards() { 145 // A larger dataset is needed to exceed default CursorWindow size 146 int datasetSize = DEFAULT_DATASET_SIZE * 50; 147 insertT1TestDataSet(datasetSize); 148 149 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 150 while (state.keepRunning()) { 151 try (Cursor cursor = mDatabase 152 .rawQuery("SELECT _ID, COL_A, COL_B, COL_C FROM T1 ORDER BY _ID", null)) { 153 int i = datasetSize - 1; 154 while(cursor.moveToPosition(i)) { 155 assertEquals(i, cursor.getInt(0)); 156 assertEquals(i, cursor.getInt(1)); 157 assertEquals("T1Value" + i, cursor.getString(2)); 158 assertEquals(1.1 * i, cursor.getDouble(3), 0.0000001d); 159 i--; 160 } 161 assertEquals(-1, i); 162 } 163 } 164 } 165 166 @Test 167 public void testInnerJoin() { 168 mDatabase.setForeignKeyConstraintsEnabled(true); 169 mDatabase.beginTransaction(); 170 insertT1TestDataSet(); 171 insertT2TestDataSet(); 172 mDatabase.setTransactionSuccessful(); 173 mDatabase.endTransaction(); 174 175 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 176 177 Random rnd = new Random(0); 178 while (state.keepRunning()) { 179 int index = rnd.nextInt(1000); 180 try (Cursor cursor = mDatabase.rawQuery( 181 "SELECT T1._ID, T1.COL_A, T1.COL_B, T1.COL_C, T2.COL_A FROM T1 " 182 + "INNER JOIN T2 on T2.T1_ID=T1._ID WHERE T1._ID = ?", 183 new String[]{String.valueOf(index)})) { 184 assertTrue(cursor.moveToNext()); 185 assertEquals(index, cursor.getInt(0)); 186 assertEquals(index, cursor.getInt(1)); 187 assertEquals("T1Value" + index, cursor.getString(2)); 188 assertEquals(1.1 * index, cursor.getDouble(3), 0.0000001d); 189 assertEquals("T2Value" + index, cursor.getString(4)); 190 } 191 } 192 } 193 194 @Test 195 public void testInsert() { 196 insertT1TestDataSet(); 197 198 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 199 200 ContentValues cv = new ContentValues(); 201 cv.put("_ID", DEFAULT_DATASET_SIZE); 202 cv.put("COL_B", "NewValue"); 203 cv.put("COL_C", 1.1); 204 String[] deleteArgs = new String[]{String.valueOf(DEFAULT_DATASET_SIZE)}; 205 while (state.keepRunning()) { 206 assertEquals(DEFAULT_DATASET_SIZE, mDatabase.insert("T1", null, cv)); 207 state.pauseTiming(); 208 assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs)); 209 state.resumeTiming(); 210 } 211 } 212 213 @Test 214 public void testDelete() { 215 insertT1TestDataSet(); 216 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 217 String[] deleteArgs = new String[]{String.valueOf(DEFAULT_DATASET_SIZE)}; 218 Object[] insertsArgs = new Object[]{DEFAULT_DATASET_SIZE, DEFAULT_DATASET_SIZE, 219 "ValueToDelete", 1.1}; 220 221 while (state.keepRunning()) { 222 state.pauseTiming(); 223 mDatabase.execSQL("INSERT INTO T1 VALUES (?, ?, ?, ?)", insertsArgs); 224 state.resumeTiming(); 225 assertEquals(1, mDatabase.delete("T1", "_ID=?", deleteArgs)); 226 } 227 } 228 229 @Test 230 public void testUpdate() { 231 insertT1TestDataSet(); 232 BenchmarkState state = mPerfStatusReporter.getBenchmarkState(); 233 234 Random rnd = new Random(0); 235 int i = 0; 236 ContentValues cv = new ContentValues(); 237 String[] argArray = new String[1]; 238 while (state.keepRunning()) { 239 int id = rnd.nextInt(DEFAULT_DATASET_SIZE); 240 cv.put("COL_A", i); 241 cv.put("COL_B", "UpdatedValue"); 242 cv.put("COL_C", i); 243 argArray[0] = String.valueOf(id); 244 assertEquals(1, mDatabase.update("T1", cv, "_ID=?", argArray)); 245 i++; 246 } 247 } 248 249 private void insertT1TestDataSet() { 250 insertT1TestDataSet(DEFAULT_DATASET_SIZE); 251 } 252 253 private void insertT1TestDataSet(int size) { 254 mDatabase.beginTransaction(); 255 for (int i = 0; i < size; i++) { 256 mDatabase.execSQL("INSERT INTO T1 VALUES (?, ?, ?, ?)", 257 new Object[]{i, i, "T1Value" + i, i * 1.1}); 258 } 259 mDatabase.setTransactionSuccessful(); 260 mDatabase.endTransaction(); 261 } 262 263 private void insertT2TestDataSet() { 264 mDatabase.beginTransaction(); 265 for (int i = 0; i < DEFAULT_DATASET_SIZE; i++) { 266 mDatabase.execSQL("INSERT INTO T2 VALUES (?, ?, ?)", 267 new Object[]{i, "T2Value" + i, i}); 268 } 269 mDatabase.setTransactionSuccessful(); 270 mDatabase.endTransaction(); 271 } 272} 273 274