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