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