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
17package com.android.messaging.datamodel;
18
19import android.content.ContentValues;
20import android.content.Context;
21import android.database.Cursor;
22import android.database.DatabaseUtils;
23import android.database.sqlite.SQLiteDatabase;
24import android.database.sqlite.SQLiteFullException;
25import android.database.sqlite.SQLiteQueryBuilder;
26import android.database.sqlite.SQLiteStatement;
27import android.util.SparseArray;
28
29import com.android.messaging.Factory;
30import com.android.messaging.R;
31import com.android.messaging.util.Assert;
32import com.android.messaging.util.BugleGservicesKeys;
33import com.android.messaging.util.DebugUtils;
34import com.android.messaging.util.LogUtil;
35import com.android.messaging.util.UiUtils;
36
37import java.util.Locale;
38import java.util.Stack;
39import java.util.regex.Pattern;
40
41public 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
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
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
103    private void maybePlayDebugNoise() {
104        DebugUtils.maybePlayDebugNoise(mContext, DebugUtils.DEBUG_SOUND_DB_OP);
105    }
106
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
119    public Context getContext() {
120        return mContext;
121    }
122
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
134    public void setTransactionSuccessful() {
135        final TransactionData f = sTransactionDepth.get().peek();
136        f.transactionSuccessful = true;
137        mDatabase.setTransactionSuccessful();
138    }
139
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
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
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
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
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
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
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
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
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
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
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
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
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
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
419    public void setLocale(final Locale locale) {
420        mDatabase.setLocale(locale);
421    }
422
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
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
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
479    public SQLiteDatabase getDatabase() {
480        return mDatabase;
481    }
482}
483