1 /*
2  * Copyright (C) 2015 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 com.android.messaging.datamodel;
18 
19 import android.content.ContentValues;
20 import android.content.Context;
21 import android.database.Cursor;
22 import android.database.DatabaseUtils;
23 import android.database.sqlite.SQLiteDatabase;
24 import android.database.sqlite.SQLiteFullException;
25 import android.database.sqlite.SQLiteQueryBuilder;
26 import android.database.sqlite.SQLiteStatement;
27 import android.util.SparseArray;
28 
29 import com.android.messaging.Factory;
30 import com.android.messaging.R;
31 import com.android.messaging.util.Assert;
32 import com.android.messaging.util.BugleGservicesKeys;
33 import com.android.messaging.util.DebugUtils;
34 import com.android.messaging.util.LogUtil;
35 import com.android.messaging.util.UiUtils;
36 
37 import java.util.Locale;
38 import java.util.Stack;
39 import java.util.regex.Pattern;
40 
41 public class DatabaseWrapper {
42     private static final String TAG = LogUtil.BUGLE_DATABASE_TAG;
43 
44     private final SQLiteDatabase mDatabase;
45     private final Context mContext;
46     private final boolean mLog;
47     /**
48      * Set mExplainQueryPlanRegexp (via {@link BugleGservicesKeys#EXPLAIN_QUERY_PLAN_REGEXP}
49      * to regex matching queries to see query plans. For example, ".*" to show all query plans.
50      */
51     // See
52     private final String mExplainQueryPlanRegexp;
53     private static final int sTimingThreshold = 50;        // in milliseconds
54 
55     public static final int INDEX_INSERT_MESSAGE_PART = 0;
56     public static final int INDEX_INSERT_MESSAGE = 1;
57     public static final int INDEX_QUERY_CONVERSATIONS_LATEST_MESSAGE = 2;
58     public static final int INDEX_QUERY_MESSAGES_LATEST_MESSAGE = 3;
59 
60     private final SparseArray<SQLiteStatement> mCompiledStatements;
61 
62     static class TransactionData {
63         long time;
64         boolean transactionSuccessful;
65     }
66 
67     // track transaction on a per thread basis
68     private static ThreadLocal<Stack<TransactionData>> sTransactionDepth =
69             new ThreadLocal<Stack<TransactionData>>() {
70         @Override
71         public Stack<TransactionData> initialValue() {
72             return new Stack<TransactionData>();
73         }
74     };
75 
76     private static String[] sFormatStrings = new String[] {
77         "took %d ms to %s",
78         "   took %d ms to %s",
79         "      took %d ms to %s",
80     };
81 
DatabaseWrapper(final Context context, final SQLiteDatabase db)82     DatabaseWrapper(final Context context, final SQLiteDatabase db) {
83         mLog = LogUtil.isLoggable(LogUtil.BUGLE_DATABASE_PERF_TAG, LogUtil.VERBOSE);
84         mExplainQueryPlanRegexp = Factory.get().getBugleGservices().getString(
85                 BugleGservicesKeys.EXPLAIN_QUERY_PLAN_REGEXP, null);
86         mDatabase = db;
87         mContext = context;
88         mCompiledStatements = new SparseArray<SQLiteStatement>();
89     }
90 
getStatementInTransaction(final int index, final String statement)91     public SQLiteStatement getStatementInTransaction(final int index, final String statement) {
92         // Use transaction to serialize access to statements
93         Assert.isTrue(mDatabase.inTransaction());
94         SQLiteStatement compiled = mCompiledStatements.get(index);
95         if (compiled == null) {
96             compiled = mDatabase.compileStatement(statement);
97             Assert.isTrue(compiled.toString().contains(statement.trim()));
98             mCompiledStatements.put(index, compiled);
99         }
100         return compiled;
101     }
102 
maybePlayDebugNoise()103     private void maybePlayDebugNoise() {
104         DebugUtils.maybePlayDebugNoise(mContext, DebugUtils.DEBUG_SOUND_DB_OP);
105     }
106 
printTiming(final long t1, final String msg)107     private static void printTiming(final long t1, final String msg) {
108         final int transactionDepth = sTransactionDepth.get().size();
109         final long t2 = System.currentTimeMillis();
110         final long delta = t2 - t1;
111         if (delta > sTimingThreshold) {
112             LogUtil.v(LogUtil.BUGLE_DATABASE_PERF_TAG, String.format(Locale.US,
113                     sFormatStrings[Math.min(sFormatStrings.length - 1, transactionDepth)],
114                     delta,
115                     msg));
116         }
117     }
118 
getContext()119     public Context getContext() {
120         return mContext;
121     }
122 
beginTransaction()123     public void beginTransaction() {
124         final long t1 = System.currentTimeMillis();
125 
126         // push the current time onto the transaction stack
127         final TransactionData f = new TransactionData();
128         f.time = t1;
129         sTransactionDepth.get().push(f);
130 
131         mDatabase.beginTransaction();
132     }
133 
setTransactionSuccessful()134     public void setTransactionSuccessful() {
135         final TransactionData f = sTransactionDepth.get().peek();
136         f.transactionSuccessful = true;
137         mDatabase.setTransactionSuccessful();
138     }
139 
endTransaction()140     public void endTransaction() {
141         long t1 = 0;
142         long transactionStartTime = 0;
143         final TransactionData f = sTransactionDepth.get().pop();
144         if (f.transactionSuccessful == false) {
145             LogUtil.w(TAG, "endTransaction without setting successful");
146             for (final StackTraceElement st : (new Exception()).getStackTrace()) {
147                 LogUtil.w(TAG, "    " + st.toString());
148             }
149         }
150         if (mLog) {
151             transactionStartTime = f.time;
152             t1 = System.currentTimeMillis();
153         }
154         try {
155             mDatabase.endTransaction();
156         } catch (SQLiteFullException ex) {
157             LogUtil.e(TAG, "Database full, unable to endTransaction", ex);
158             UiUtils.showToastAtBottom(R.string.db_full);
159         }
160         if (mLog) {
161             printTiming(t1, String.format(Locale.US,
162                     ">>> endTransaction (total for this transaction: %d)",
163                     (System.currentTimeMillis() - transactionStartTime)));
164         }
165     }
166 
yieldTransaction()167     public void yieldTransaction() {
168         long yieldStartTime = 0;
169         if (mLog) {
170             yieldStartTime = System.currentTimeMillis();
171         }
172         final boolean wasYielded = mDatabase.yieldIfContendedSafely();
173         if (wasYielded && mLog) {
174             printTiming(yieldStartTime, "yieldTransaction");
175         }
176     }
177 
insertWithOnConflict(final String searchTable, final String nullColumnHack, final ContentValues initialValues, final int conflictAlgorithm)178     public void insertWithOnConflict(final String searchTable, final String nullColumnHack,
179             final ContentValues initialValues, final int conflictAlgorithm) {
180         long t1 = 0;
181         if (mLog) {
182             t1 = System.currentTimeMillis();
183         }
184         try {
185             mDatabase.insertWithOnConflict(searchTable, nullColumnHack, initialValues,
186                     conflictAlgorithm);
187         } catch (SQLiteFullException ex) {
188             LogUtil.e(TAG, "Database full, unable to insertWithOnConflict", ex);
189             UiUtils.showToastAtBottom(R.string.db_full);
190         }
191         if (mLog) {
192             printTiming(t1, String.format(Locale.US,
193                     "insertWithOnConflict with ", searchTable));
194         }
195     }
196 
explainQueryPlan(final SQLiteQueryBuilder qb, final SQLiteDatabase db, final String[] projection, final String selection, @SuppressWarnings("unused") final String[] queryArgs, final String groupBy, @SuppressWarnings("unused") final String having, final String sortOrder, final String limit)197     private void explainQueryPlan(final SQLiteQueryBuilder qb, final SQLiteDatabase db,
198             final String[] projection, final String selection,
199             @SuppressWarnings("unused")
200                     final String[] queryArgs,
201             final String groupBy,
202             @SuppressWarnings("unused")
203                     final String having,
204             final String sortOrder, final String limit) {
205         final String queryString = qb.buildQuery(
206                 projection,
207                 selection,
208                 groupBy,
209                 null/*having*/,
210                 sortOrder,
211                 limit);
212         explainQueryPlan(db, queryString, queryArgs);
213     }
214 
explainQueryPlan(final SQLiteDatabase db, final String sql, final String[] queryArgs)215     private void explainQueryPlan(final SQLiteDatabase db, final String sql,
216             final String[] queryArgs) {
217         if (!Pattern.matches(mExplainQueryPlanRegexp, sql)) {
218             return;
219         }
220         final Cursor planCursor = db.rawQuery("explain query plan " + sql, queryArgs);
221         try {
222             if (planCursor != null && planCursor.moveToFirst()) {
223                 final int detailColumn = planCursor.getColumnIndex("detail");
224                 final StringBuilder sb = new StringBuilder();
225                 do {
226                     sb.append(planCursor.getString(detailColumn));
227                     sb.append("\n");
228                 } while (planCursor.moveToNext());
229                 if (sb.length() > 0) {
230                     sb.setLength(sb.length() - 1);
231                 }
232                 LogUtil.v(TAG, "for query " + sql + "\nplan is: "
233                         + sb.toString());
234             }
235         } catch (final Exception e) {
236             LogUtil.w(TAG, "Query plan failed ", e);
237         } finally {
238             if (planCursor != null) {
239                 planCursor.close();
240             }
241         }
242     }
243 
query(final String searchTable, final String[] projection, final String selection, final String[] selectionArgs, final String groupBy, final String having, final String orderBy, final String limit)244     public Cursor query(final String searchTable, final String[] projection,
245             final String selection, final String[] selectionArgs, final String groupBy,
246             final String having, final String orderBy, final String limit) {
247         if (mExplainQueryPlanRegexp != null) {
248             final SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
249             qb.setTables(searchTable);
250             explainQueryPlan(qb, mDatabase, projection, selection, selectionArgs,
251                     groupBy, having, orderBy, limit);
252         }
253 
254         maybePlayDebugNoise();
255         long t1 = 0;
256         if (mLog) {
257             t1 = System.currentTimeMillis();
258         }
259         final Cursor cursor = mDatabase.query(searchTable, projection, selection, selectionArgs,
260                 groupBy, having, orderBy, limit);
261         if (mLog) {
262             printTiming(
263                     t1,
264                     String.format(Locale.US, "query %s with %s ==> %d",
265                             searchTable, selection, cursor.getCount()));
266         }
267         return cursor;
268     }
269 
query(final String searchTable, final String[] columns, final String selection, final String[] selectionArgs, final String groupBy, final String having, final String orderBy)270     public Cursor query(final String searchTable, final String[] columns,
271             final String selection, final String[] selectionArgs, final String groupBy,
272             final String having, final String orderBy) {
273         return query(
274                 searchTable, columns, selection, selectionArgs,
275                 groupBy, having, orderBy, null);
276     }
277 
query(final SQLiteQueryBuilder qb, final String[] projection, final String selection, final String[] queryArgs, final String groupBy, final String having, final String sortOrder, final String limit)278     public Cursor query(final SQLiteQueryBuilder qb,
279             final String[] projection, final String selection, final String[] queryArgs,
280             final String groupBy, final String having, final String sortOrder, final String limit) {
281         if (mExplainQueryPlanRegexp != null) {
282             explainQueryPlan(qb, mDatabase, projection, selection, queryArgs,
283                     groupBy, having, sortOrder, limit);
284         }
285         maybePlayDebugNoise();
286         long t1 = 0;
287         if (mLog) {
288             t1 = System.currentTimeMillis();
289         }
290         final Cursor cursor = qb.query(mDatabase, projection, selection, queryArgs, groupBy,
291                 having, sortOrder, limit);
292         if (mLog) {
293             printTiming(
294                     t1,
295                     String.format(Locale.US, "query %s with %s ==> %d",
296                             qb.getTables(), selection, cursor.getCount()));
297         }
298         return cursor;
299     }
300 
queryNumEntries(final String table, final String selection, final String[] selectionArgs)301     public long queryNumEntries(final String table, final String selection,
302             final String[] selectionArgs) {
303         long t1 = 0;
304         if (mLog) {
305             t1 = System.currentTimeMillis();
306         }
307         maybePlayDebugNoise();
308         final long retval =
309                 DatabaseUtils.queryNumEntries(mDatabase, table, selection, selectionArgs);
310         if (mLog){
311             printTiming(
312                     t1,
313                     String.format(Locale.US, "queryNumEntries %s with %s ==> %d", table,
314                             selection, retval));
315         }
316         return retval;
317     }
318 
rawQuery(final String sql, final String[] args)319     public Cursor rawQuery(final String sql, final String[] args) {
320         if (mExplainQueryPlanRegexp != null) {
321             explainQueryPlan(mDatabase, sql, args);
322         }
323         long t1 = 0;
324         if (mLog) {
325             t1 = System.currentTimeMillis();
326         }
327         maybePlayDebugNoise();
328         final Cursor cursor = mDatabase.rawQuery(sql, args);
329         if (mLog) {
330             printTiming(
331                     t1,
332                     String.format(Locale.US, "rawQuery %s ==> %d", sql, cursor.getCount()));
333         }
334         return cursor;
335     }
336 
update(final String table, final ContentValues values, final String selection, final String[] selectionArgs)337     public int update(final String table, final ContentValues values,
338             final String selection, final String[] selectionArgs) {
339         long t1 = 0;
340         if (mLog) {
341             t1 = System.currentTimeMillis();
342         }
343         maybePlayDebugNoise();
344         int count = 0;
345         try {
346             count = mDatabase.update(table, values, selection, selectionArgs);
347         } catch (SQLiteFullException ex) {
348             LogUtil.e(TAG, "Database full, unable to update", ex);
349             UiUtils.showToastAtBottom(R.string.db_full);
350         }
351         if (mLog) {
352             printTiming(t1, String.format(Locale.US, "update %s with %s ==> %d",
353                     table, selection, count));
354         }
355         return count;
356     }
357 
delete(final String table, final String whereClause, final String[] whereArgs)358     public int delete(final String table, final String whereClause, final String[] whereArgs) {
359         long t1 = 0;
360         if (mLog) {
361             t1 = System.currentTimeMillis();
362         }
363         maybePlayDebugNoise();
364         int count = 0;
365         try {
366             count = mDatabase.delete(table, whereClause, whereArgs);
367         } catch (SQLiteFullException ex) {
368             LogUtil.e(TAG, "Database full, unable to delete", ex);
369             UiUtils.showToastAtBottom(R.string.db_full);
370         }
371         if (mLog) {
372             printTiming(t1,
373                     String.format(Locale.US, "delete from %s with %s ==> %d", table,
374                             whereClause, count));
375         }
376         return count;
377     }
378 
insert(final String table, final String nullColumnHack, final ContentValues values)379     public long insert(final String table, final String nullColumnHack,
380             final ContentValues values) {
381         long t1 = 0;
382         if (mLog) {
383             t1 = System.currentTimeMillis();
384         }
385         maybePlayDebugNoise();
386         long rowId = -1;
387         try {
388             rowId = mDatabase.insert(table, nullColumnHack, values);
389         } catch (SQLiteFullException ex) {
390             LogUtil.e(TAG, "Database full, unable to insert", ex);
391             UiUtils.showToastAtBottom(R.string.db_full);
392         }
393         if (mLog) {
394             printTiming(t1, String.format(Locale.US, "insert to %s", table));
395         }
396         return rowId;
397     }
398 
replace(final String table, final String nullColumnHack, final ContentValues values)399     public long replace(final String table, final String nullColumnHack,
400             final ContentValues values) {
401         long t1 = 0;
402         if (mLog) {
403             t1 = System.currentTimeMillis();
404         }
405         maybePlayDebugNoise();
406         long rowId = -1;
407         try {
408             rowId = mDatabase.replace(table, nullColumnHack, values);
409         } catch (SQLiteFullException ex) {
410             LogUtil.e(TAG, "Database full, unable to replace", ex);
411             UiUtils.showToastAtBottom(R.string.db_full);
412         }
413         if (mLog) {
414             printTiming(t1, String.format(Locale.US, "replace to %s", table));
415         }
416         return rowId;
417     }
418 
setLocale(final Locale locale)419     public void setLocale(final Locale locale) {
420         mDatabase.setLocale(locale);
421     }
422 
execSQL(final String sql, final String[] bindArgs)423     public void execSQL(final String sql, final String[] bindArgs) {
424         long t1 = 0;
425         if (mLog) {
426             t1 = System.currentTimeMillis();
427         }
428         maybePlayDebugNoise();
429         try {
430             mDatabase.execSQL(sql, bindArgs);
431         } catch (SQLiteFullException ex) {
432             LogUtil.e(TAG, "Database full, unable to execSQL", ex);
433             UiUtils.showToastAtBottom(R.string.db_full);
434         }
435 
436         if (mLog) {
437             printTiming(t1, String.format(Locale.US, "execSQL %s", sql));
438         }
439     }
440 
execSQL(final String sql)441     public void execSQL(final String sql) {
442         long t1 = 0;
443         if (mLog) {
444             t1 = System.currentTimeMillis();
445         }
446         maybePlayDebugNoise();
447         try {
448             mDatabase.execSQL(sql);
449         } catch (SQLiteFullException ex) {
450             LogUtil.e(TAG, "Database full, unable to execSQL", ex);
451             UiUtils.showToastAtBottom(R.string.db_full);
452         }
453 
454         if (mLog) {
455             printTiming(t1, String.format(Locale.US, "execSQL %s", sql));
456         }
457     }
458 
execSQLUpdateDelete(final String sql)459     public int execSQLUpdateDelete(final String sql) {
460         long t1 = 0;
461         if (mLog) {
462             t1 = System.currentTimeMillis();
463         }
464         maybePlayDebugNoise();
465         final SQLiteStatement statement = mDatabase.compileStatement(sql);
466         int rowsUpdated = 0;
467         try {
468             rowsUpdated = statement.executeUpdateDelete();
469         } catch (SQLiteFullException ex) {
470             LogUtil.e(TAG, "Database full, unable to execSQLUpdateDelete", ex);
471             UiUtils.showToastAtBottom(R.string.db_full);
472         }
473         if (mLog) {
474             printTiming(t1, String.format(Locale.US, "execSQLUpdateDelete %s", sql));
475         }
476         return rowsUpdated;
477     }
478 
getDatabase()479     public SQLiteDatabase getDatabase() {
480         return mDatabase;
481     }
482 }
483