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