ShortcutRepositoryImplLog.java revision 38eb02e676db9e5a633e3c88a90beb8a477b1ca1
1/* 2 * Copyright (C) 2009 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.quicksearchbox; 18 19import com.android.quicksearchbox.util.Consumer; 20import com.android.quicksearchbox.util.Consumers; 21import com.android.quicksearchbox.util.SQLiteAsyncQuery; 22import com.android.quicksearchbox.util.SQLiteTransaction; 23import com.android.quicksearchbox.util.Util; 24import com.google.common.annotations.VisibleForTesting; 25 26import android.app.SearchManager; 27import android.content.ComponentName; 28import android.content.ContentResolver; 29import android.content.ContentValues; 30import android.content.Context; 31import android.database.Cursor; 32import android.database.sqlite.SQLiteDatabase; 33import android.database.sqlite.SQLiteOpenHelper; 34import android.database.sqlite.SQLiteQueryBuilder; 35import android.net.Uri; 36import android.os.Handler; 37import android.text.TextUtils; 38import android.util.Log; 39 40import java.io.File; 41import java.util.Collection; 42import java.util.HashMap; 43import java.util.Map; 44import java.util.concurrent.Executor; 45 46/** 47 * A shortcut repository implementation that uses a log of every click. 48 * 49 * To inspect DB: 50 * # sqlite3 /data/data/com.android.quicksearchbox/databases/qsb-log.db 51 * 52 * TODO: Refactor this class. 53 */ 54public class ShortcutRepositoryImplLog implements ShortcutRepository { 55 56 private static final boolean DBG = false; 57 private static final String TAG = "QSB.ShortcutRepositoryImplLog"; 58 59 private static final String DB_NAME = "qsb-log.db"; 60 private static final int DB_VERSION = 31; 61 62 private static final String HAS_HISTORY_QUERY = 63 "SELECT " + Shortcuts.intent_key.fullName + " FROM " + Shortcuts.TABLE_NAME; 64 private String mEmptyQueryShortcutQuery ; 65 private String mShortcutQuery; 66 67 private static final String SHORTCUT_BY_ID_WHERE = 68 Shortcuts.shortcut_id.name() + "=? AND " + Shortcuts.source.name() + "=?"; 69 70 private static final String SOURCE_RANKING_SQL = buildSourceRankingSql(); 71 72 private final Context mContext; 73 private final Config mConfig; 74 private final Corpora mCorpora; 75 private final ShortcutRefresher mRefresher; 76 private final Handler mUiThread; 77 // Used to perform log write operations asynchronously 78 private final Executor mLogExecutor; 79 private final DbOpenHelper mOpenHelper; 80 private final String mSearchSpinner; 81 82 /** 83 * Create an instance to the repo. 84 */ 85 public static ShortcutRepository create(Context context, Config config, 86 Corpora sources, ShortcutRefresher refresher, Handler uiThread, 87 Executor logExecutor) { 88 return new ShortcutRepositoryImplLog(context, config, sources, refresher, 89 uiThread, logExecutor, DB_NAME); 90 } 91 92 /** 93 * @param context Used to create / open db 94 * @param name The name of the database to create. 95 */ 96 @VisibleForTesting 97 ShortcutRepositoryImplLog(Context context, Config config, Corpora corpora, 98 ShortcutRefresher refresher, Handler uiThread, Executor logExecutor, String name) { 99 mContext = context; 100 mConfig = config; 101 mCorpora = corpora; 102 mRefresher = refresher; 103 mUiThread = uiThread; 104 mLogExecutor = logExecutor; 105 mOpenHelper = new DbOpenHelper(context, name, DB_VERSION, config); 106 buildShortcutQueries(); 107 108 mSearchSpinner = Util.getResourceUri(mContext, R.drawable.search_spinner).toString(); 109 } 110 111 // clicklog first, since that's where restrict the result set 112 private static final String TABLES = ClickLog.TABLE_NAME + " INNER JOIN " + 113 Shortcuts.TABLE_NAME + " ON " + ClickLog.intent_key.fullName + " = " + 114 Shortcuts.intent_key.fullName; 115 116 private static final String AS = " AS "; 117 118 private static final String[] SHORTCUT_QUERY_COLUMNS = { 119 Shortcuts.intent_key.fullName, 120 Shortcuts.source.fullName, 121 Shortcuts.source_version_code.fullName, 122 Shortcuts.format.fullName + AS + SearchManager.SUGGEST_COLUMN_FORMAT, 123 Shortcuts.title + AS + SearchManager.SUGGEST_COLUMN_TEXT_1, 124 Shortcuts.description + AS + SearchManager.SUGGEST_COLUMN_TEXT_2, 125 Shortcuts.description_url + AS + SearchManager.SUGGEST_COLUMN_TEXT_2_URL, 126 Shortcuts.icon1 + AS + SearchManager.SUGGEST_COLUMN_ICON_1, 127 Shortcuts.icon2 + AS + SearchManager.SUGGEST_COLUMN_ICON_2, 128 Shortcuts.intent_action + AS + SearchManager.SUGGEST_COLUMN_INTENT_ACTION, 129 Shortcuts.intent_component.fullName, 130 Shortcuts.intent_data + AS + SearchManager.SUGGEST_COLUMN_INTENT_DATA, 131 Shortcuts.intent_query + AS + SearchManager.SUGGEST_COLUMN_QUERY, 132 Shortcuts.intent_extradata + AS + SearchManager.SUGGEST_COLUMN_INTENT_EXTRA_DATA, 133 Shortcuts.shortcut_id + AS + SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, 134 Shortcuts.spinner_while_refreshing + AS + 135 SearchManager.SUGGEST_COLUMN_SPINNER_WHILE_REFRESHING, 136 Shortcuts.log_type + AS + CursorBackedSuggestionCursor.SUGGEST_COLUMN_LOG_TYPE, 137 }; 138 139 // Avoid GLOB by using >= AND <, with some manipulation (see nextString(String)). 140 // to figure out the upper bound (e.g. >= "abc" AND < "abd" 141 // This allows us to use parameter binding and still take advantage of the 142 // index on the query column. 143 private static final String PREFIX_RESTRICTION = 144 ClickLog.query.fullName + " >= ?1 AND " + ClickLog.query.fullName + " < ?2"; 145 146 private static final String LAST_HIT_TIME_EXPR = "MAX(" + ClickLog.hit_time.fullName + ")"; 147 private static final String GROUP_BY = ClickLog.intent_key.fullName; 148 private static final String PREFER_LATEST_PREFIX = 149 "(" + LAST_HIT_TIME_EXPR + " = (SELECT " + LAST_HIT_TIME_EXPR + " FROM " + 150 ClickLog.TABLE_NAME + " WHERE "; 151 private static final String PREFER_LATEST_SUFFIX = "))"; 152 153 private void buildShortcutQueries() { 154 // SQL expression for the time before which no clicks should be counted. 155 String cutOffTime_expr = "(?3 - " + mConfig.getMaxStatAgeMillis() + ")"; 156 // Filter out clicks that are too old 157 String ageRestriction = ClickLog.hit_time.fullName + " >= " + cutOffTime_expr; 158 String having = null; 159 // Order by sum of hit times (seconds since cutoff) for the clicks for each shortcut. 160 // This has the effect of multiplying the average hit time with the click count 161 String ordering_expr = 162 "SUM((" + ClickLog.hit_time.fullName + " - " + cutOffTime_expr + ") / 1000)"; 163 164 String where = ageRestriction; 165 String preferLatest = PREFER_LATEST_PREFIX + where + PREFER_LATEST_SUFFIX; 166 String orderBy = preferLatest + " DESC, " + ordering_expr + " DESC"; 167 mEmptyQueryShortcutQuery = SQLiteQueryBuilder.buildQueryString( 168 false, TABLES, SHORTCUT_QUERY_COLUMNS, where, GROUP_BY, having, orderBy, null); 169 if (DBG) Log.d(TAG, "Empty shortcut query:\n" + mEmptyQueryShortcutQuery); 170 171 where = PREFIX_RESTRICTION + " AND " + ageRestriction; 172 preferLatest = PREFER_LATEST_PREFIX + where + PREFER_LATEST_SUFFIX; 173 orderBy = preferLatest + " DESC, " + ordering_expr + " DESC"; 174 mShortcutQuery = SQLiteQueryBuilder.buildQueryString( 175 false, TABLES, SHORTCUT_QUERY_COLUMNS, where, GROUP_BY, having, orderBy, null); 176 if (DBG) Log.d(TAG, "Empty shortcut:\n" + mShortcutQuery); 177 } 178 179 /** 180 * @return sql that ranks sources by total clicks, filtering out sources 181 * without enough clicks. 182 */ 183 private static String buildSourceRankingSql() { 184 final String orderingExpr = SourceStats.total_clicks.name(); 185 final String tables = SourceStats.TABLE_NAME; 186 final String[] columns = SourceStats.COLUMNS; 187 final String where = SourceStats.total_clicks + " >= $1"; 188 final String groupBy = null; 189 final String having = null; 190 final String orderBy = orderingExpr + " DESC"; 191 final String limit = null; 192 return SQLiteQueryBuilder.buildQueryString( 193 false, tables, columns, where, groupBy, having, orderBy, limit); 194 } 195 196 protected DbOpenHelper getOpenHelper() { 197 return mOpenHelper; 198 } 199 200 private void runTransactionAsync(final SQLiteTransaction transaction) { 201 mLogExecutor.execute(new Runnable() { 202 public void run() { 203 transaction.run(mOpenHelper.getWritableDatabase()); 204 } 205 }); 206 } 207 208 private <A> void runQueryAsync(final SQLiteAsyncQuery<A> query, final Consumer<A> consumer) { 209 mLogExecutor.execute(new Runnable() { 210 public void run() { 211 query.run(mOpenHelper.getReadableDatabase(), consumer); 212 } 213 }); 214 } 215 216// --------------------- Interface ShortcutRepository --------------------- 217 218 public void hasHistory(Consumer<Boolean> consumer) { 219 runQueryAsync(new SQLiteAsyncQuery<Boolean>() { 220 @Override 221 protected Boolean performQuery(SQLiteDatabase db) { 222 return hasHistory(db); 223 } 224 }, consumer); 225 } 226 227 public void clearHistory() { 228 runTransactionAsync(new SQLiteTransaction() { 229 @Override 230 public boolean performTransaction(SQLiteDatabase db) { 231 db.delete(ClickLog.TABLE_NAME, null, null); 232 db.delete(Shortcuts.TABLE_NAME, null, null); 233 db.delete(SourceStats.TABLE_NAME, null, null); 234 return true; 235 } 236 }); 237 } 238 239 @VisibleForTesting 240 public void deleteRepository() { 241 getOpenHelper().deleteDatabase(); 242 } 243 244 public void close() { 245 getOpenHelper().close(); 246 } 247 248 public void reportClick(final SuggestionCursor suggestions, final int position) { 249 final long now = System.currentTimeMillis(); 250 reportClickAtTime(suggestions, position, now); 251 } 252 253 public void getShortcutsForQuery(final String query, final Collection<Corpus> allowedCorpora, 254 final Consumer<ShortcutCursor> consumer) { 255 final long now = System.currentTimeMillis(); 256 mLogExecutor.execute(new Runnable() { 257 public void run() { 258 ShortcutCursor shortcuts = getShortcutsForQuery(query, allowedCorpora, now); 259 Consumers.consumeCloseable(consumer, shortcuts); 260 } 261 }); 262 } 263 264 public void updateShortcut(Source source, String shortcutId, SuggestionCursor refreshed) { 265 refreshShortcut(source, shortcutId, refreshed); 266 } 267 268 public void getCorpusScores(final Consumer<Map<String, Integer>> consumer) { 269 runQueryAsync(new SQLiteAsyncQuery<Map<String, Integer>>() { 270 @Override 271 protected Map<String, Integer> performQuery(SQLiteDatabase db) { 272 return getCorpusScores(); 273 } 274 }, consumer); 275 } 276 277// -------------------------- end ShortcutRepository -------------------------- 278 279 private boolean hasHistory(SQLiteDatabase db) { 280 Cursor cursor = db.rawQuery(HAS_HISTORY_QUERY, null); 281 try { 282 if (DBG) Log.d(TAG, "hasHistory(): cursor=" + cursor); 283 return cursor != null && cursor.getCount() > 0; 284 } finally { 285 if (cursor != null) cursor.close(); 286 } 287 } 288 289 private Map<String,Integer> getCorpusScores() { 290 return getCorpusScores(mConfig.getMinClicksForSourceRanking()); 291 } 292 293 private boolean shouldRefresh(Suggestion suggestion) { 294 return mRefresher.shouldRefresh(suggestion.getSuggestionSource(), 295 suggestion.getShortcutId()); 296 } 297 298 @VisibleForTesting 299 ShortcutCursor getShortcutsForQuery(String query, Collection<Corpus> allowedCorpora, long now) { 300 if (DBG) Log.d(TAG, "getShortcutsForQuery(" + query + "," + allowedCorpora + ")"); 301 String sql = query.length() == 0 ? mEmptyQueryShortcutQuery : mShortcutQuery; 302 String[] params = buildShortcutQueryParams(query, now); 303 304 SQLiteDatabase db = mOpenHelper.getReadableDatabase(); 305 Cursor cursor = db.rawQuery(sql, params); 306 if (cursor.getCount() == 0) { 307 cursor.close(); 308 return null; 309 } 310 311 if (DBG) Log.d(TAG, "Allowed sources: "); 312 HashMap<String,Source> allowedSources = new HashMap<String,Source>(); 313 for (Corpus corpus : allowedCorpora) { 314 for (Source source : corpus.getSources()) { 315 if (DBG) Log.d(TAG, "\t" + source.getName()); 316 allowedSources.put(source.getName(), source); 317 } 318 } 319 320 return new ShortcutCursor(new SuggestionCursorImpl(allowedSources, query, cursor), 321 mUiThread, mRefresher, this); 322 } 323 324 @VisibleForTesting 325 void refreshShortcut(Source source, final String shortcutId, 326 SuggestionCursor refreshed) { 327 if (source == null) throw new NullPointerException("source"); 328 if (shortcutId == null) throw new NullPointerException("shortcutId"); 329 330 final String[] whereArgs = { shortcutId, source.getName() }; 331 final ContentValues shortcut; 332 if (refreshed == null || refreshed.getCount() == 0) { 333 shortcut = null; 334 } else { 335 refreshed.moveTo(0); 336 shortcut = makeShortcutRow(refreshed); 337 } 338 339 runTransactionAsync(new SQLiteTransaction() { 340 @Override 341 protected boolean performTransaction(SQLiteDatabase db) { 342 if (shortcut == null) { 343 if (DBG) Log.d(TAG, "Deleting shortcut: " + shortcutId); 344 db.delete(Shortcuts.TABLE_NAME, SHORTCUT_BY_ID_WHERE, whereArgs); 345 } else { 346 if (DBG) Log.d(TAG, "Updating shortcut: " + shortcut); 347 db.updateWithOnConflict(Shortcuts.TABLE_NAME, shortcut, 348 SHORTCUT_BY_ID_WHERE, whereArgs, SQLiteDatabase.CONFLICT_REPLACE); 349 } 350 return true; 351 } 352 }); 353 } 354 355 private class SuggestionCursorImpl extends CursorBackedSuggestionCursor { 356 357 private final HashMap<String, Source> mAllowedSources; 358 359 public SuggestionCursorImpl(HashMap<String,Source> allowedSources, 360 String userQuery, Cursor cursor) { 361 super(userQuery, cursor); 362 mAllowedSources = allowedSources; 363 } 364 365 @Override 366 public Source getSuggestionSource() { 367 int srcCol = mCursor.getColumnIndex(Shortcuts.source.name()); 368 String srcStr = mCursor.getString(srcCol); 369 if (srcStr == null) { 370 throw new NullPointerException("Missing source for shortcut."); 371 } 372 Source source = mAllowedSources.get(srcStr); 373 if (source == null) { 374 if (DBG) { 375 Log.d(TAG, "Source " + srcStr + " (position " + mCursor.getPosition() + 376 ") not allowed"); 377 } 378 return null; 379 } 380 int versionCode = mCursor.getInt(Shortcuts.source_version_code.ordinal()); 381 if (!source.isVersionCodeCompatible(versionCode)) { 382 if (DBG) { 383 Log.d(TAG, "Version " + versionCode + " not compatible with " + 384 source.getVersionCode() + " for source " + srcStr); 385 } 386 return null; 387 } 388 return source; 389 } 390 391 @Override 392 public ComponentName getSuggestionIntentComponent() { 393 int componentCol = mCursor.getColumnIndex(Shortcuts.intent_component.name()); 394 // We don't fall back to getSuggestionSource().getIntentComponent() because 395 // we want to return the same value that getSuggestionIntentComponent() did for the 396 // original suggestion. 397 return stringToComponentName(mCursor.getString(componentCol)); 398 } 399 400 @Override 401 public String getSuggestionIcon2() { 402 if (isSpinnerWhileRefreshing() && shouldRefresh(this)) { 403 if (DBG) Log.d(TAG, "shortcut " + getShortcutId() + " refreshing"); 404 return mSearchSpinner; 405 } 406 if (DBG) Log.d(TAG, "shortcut " + getShortcutId() + " NOT refreshing"); 407 return super.getSuggestionIcon2(); 408 } 409 410 public boolean isSuggestionShortcut() { 411 return true; 412 } 413 414 } 415 416 /** 417 * Builds a parameter list for the queries built by {@link #buildShortcutQueries}. 418 */ 419 private static String[] buildShortcutQueryParams(String query, long now) { 420 return new String[]{ query, nextString(query), String.valueOf(now) }; 421 } 422 423 /** 424 * Given a string x, this method returns the least string y such that x is not a prefix of y. 425 * This is useful to implement prefix filtering by comparison, since the only strings z that 426 * have x as a prefix are such that z is greater than or equal to x and z is less than y. 427 * 428 * @param str A non-empty string. The contract above is not honored for an empty input string, 429 * since all strings have the empty string as a prefix. 430 */ 431 private static String nextString(String str) { 432 int len = str.length(); 433 if (len == 0) { 434 return str; 435 } 436 // The last code point in the string. Within the Basic Multilingual Plane, 437 // this is the same as str.charAt(len-1) 438 int codePoint = str.codePointBefore(len); 439 // This should be safe from overflow, since the largest code point 440 // representable in UTF-16 is U+10FFFF. 441 int nextCodePoint = codePoint + 1; 442 // The index of the start of the last code point. 443 // Character.charCount(codePoint) is always 1 (in the BMP) or 2 444 int lastIndex = len - Character.charCount(codePoint); 445 return new StringBuilder(len) 446 .append(str, 0, lastIndex) // append everything but the last code point 447 .appendCodePoint(nextCodePoint) // instead of the last code point, use successor 448 .toString(); 449 } 450 451 /** 452 * Returns the source ranking for sources with a minimum number of clicks. 453 * 454 * @param minClicks The minimum number of clicks a source must have. 455 * @return The list of sources, ranked by total clicks. 456 */ 457 Map<String,Integer> getCorpusScores(int minClicks) { 458 SQLiteDatabase db = mOpenHelper.getReadableDatabase(); 459 final Cursor cursor = db.rawQuery( 460 SOURCE_RANKING_SQL, new String[] { String.valueOf(minClicks) }); 461 try { 462 Map<String,Integer> corpora = new HashMap<String,Integer>(cursor.getCount()); 463 while (cursor.moveToNext()) { 464 String name = cursor.getString(SourceStats.corpus.ordinal()); 465 int clicks = cursor.getInt(SourceStats.total_clicks.ordinal()); 466 corpora.put(name, clicks); 467 } 468 return corpora; 469 } finally { 470 cursor.close(); 471 } 472 } 473 474 private ContentValues makeShortcutRow(Suggestion suggestion) { 475 String intentAction = suggestion.getSuggestionIntentAction(); 476 String intentComponent = componentNameToString(suggestion.getSuggestionIntentComponent()); 477 String intentData = suggestion.getSuggestionIntentDataString(); 478 String intentQuery = suggestion.getSuggestionQuery(); 479 String intentExtraData = suggestion.getSuggestionIntentExtraData(); 480 481 Source source = suggestion.getSuggestionSource(); 482 String sourceName = source.getName(); 483 StringBuilder key = new StringBuilder(sourceName); 484 key.append("#"); 485 if (intentData != null) { 486 key.append(intentData); 487 } 488 key.append("#"); 489 if (intentAction != null) { 490 key.append(intentAction); 491 } 492 key.append("#"); 493 if (intentComponent != null) { 494 key.append(intentComponent); 495 } 496 key.append("#"); 497 if (intentQuery != null) { 498 key.append(intentQuery); 499 } 500 // A string of the form source#intentData#intentAction#intentQuery 501 // for use as a unique identifier of a suggestion. 502 String intentKey = key.toString(); 503 504 // Get URIs for all icons, to make sure that they are stable 505 String icon1Uri = getIconUriString(source, suggestion.getSuggestionIcon1()); 506 String icon2Uri = getIconUriString(source, suggestion.getSuggestionIcon2()); 507 508 ContentValues cv = new ContentValues(); 509 cv.put(Shortcuts.intent_key.name(), intentKey); 510 cv.put(Shortcuts.source.name(), sourceName); 511 cv.put(Shortcuts.source_version_code.name(), source.getVersionCode()); 512 cv.put(Shortcuts.format.name(), suggestion.getSuggestionFormat()); 513 cv.put(Shortcuts.title.name(), suggestion.getSuggestionText1()); 514 cv.put(Shortcuts.description.name(), suggestion.getSuggestionText2()); 515 cv.put(Shortcuts.description_url.name(), suggestion.getSuggestionText2Url()); 516 cv.put(Shortcuts.icon1.name(), icon1Uri); 517 cv.put(Shortcuts.icon2.name(), icon2Uri); 518 cv.put(Shortcuts.intent_action.name(), intentAction); 519 cv.put(Shortcuts.intent_component.name(), intentComponent); 520 cv.put(Shortcuts.intent_data.name(), intentData); 521 cv.put(Shortcuts.intent_query.name(), intentQuery); 522 cv.put(Shortcuts.intent_extradata.name(), intentExtraData); 523 cv.put(Shortcuts.shortcut_id.name(), suggestion.getShortcutId()); 524 if (suggestion.isSpinnerWhileRefreshing()) { 525 cv.put(Shortcuts.spinner_while_refreshing.name(), "true"); 526 } 527 cv.put(Shortcuts.log_type.name(), suggestion.getSuggestionLogType()); 528 529 return cv; 530 } 531 532 private String componentNameToString(ComponentName component) { 533 return component == null ? null : component.flattenToShortString(); 534 } 535 536 private ComponentName stringToComponentName(String str) { 537 return str == null ? null : ComponentName.unflattenFromString(str); 538 } 539 540 private String getIconUriString(Source source, String drawableId) { 541 // Fast path for empty icons 542 if (TextUtils.isEmpty(drawableId) || "0".equals(drawableId)) { 543 return null; 544 } 545 // Fast path for icon URIs 546 if (drawableId.startsWith(ContentResolver.SCHEME_ANDROID_RESOURCE) 547 || drawableId.startsWith(ContentResolver.SCHEME_CONTENT) 548 || drawableId.startsWith(ContentResolver.SCHEME_FILE)) { 549 return drawableId; 550 } 551 Uri uri = source.getIconUri(drawableId); 552 return uri == null ? null : uri.toString(); 553 } 554 555 @VisibleForTesting 556 void reportClickAtTime(SuggestionCursor suggestion, 557 int position, long now) { 558 suggestion.moveTo(position); 559 if (DBG) { 560 Log.d(TAG, "logClicked(" + suggestion + ")"); 561 } 562 563 if (SearchManager.SUGGEST_NEVER_MAKE_SHORTCUT.equals(suggestion.getShortcutId())) { 564 if (DBG) Log.d(TAG, "clicked suggestion requested not to be shortcuted"); 565 return; 566 } 567 568 Corpus corpus = mCorpora.getCorpusForSource(suggestion.getSuggestionSource()); 569 if (corpus == null) { 570 Log.w(TAG, "no corpus for clicked suggestion"); 571 return; 572 } 573 574 // Once the user has clicked on a shortcut, don't bother refreshing 575 // (especially if this is a new shortcut) 576 mRefresher.markShortcutRefreshed(suggestion.getSuggestionSource(), 577 suggestion.getShortcutId()); 578 579 // Add or update suggestion info 580 // Since intent_key is the primary key, any existing 581 // suggestion with the same source+data+action will be replaced 582 final ContentValues shortcut = makeShortcutRow(suggestion); 583 String intentKey = shortcut.getAsString(Shortcuts.intent_key.name()); 584 585 // Log click for shortcut 586 final ContentValues click = new ContentValues(); 587 click.put(ClickLog.intent_key.name(), intentKey); 588 click.put(ClickLog.query.name(), suggestion.getUserQuery()); 589 click.put(ClickLog.hit_time.name(), now); 590 click.put(ClickLog.corpus.name(), corpus.getName()); 591 592 runTransactionAsync(new SQLiteTransaction() { 593 @Override 594 protected boolean performTransaction(SQLiteDatabase db) { 595 if (DBG) Log.d(TAG, "Adding shortcut: " + shortcut); 596 db.replaceOrThrow(Shortcuts.TABLE_NAME, null, shortcut); 597 db.insertOrThrow(ClickLog.TABLE_NAME, null, click); 598 return true; 599 } 600 }); 601 } 602 603// -------------------------- TABLES -------------------------- 604 605 /** 606 * shortcuts table 607 */ 608 enum Shortcuts { 609 intent_key, 610 source, 611 source_version_code, 612 format, 613 title, 614 description, 615 description_url, 616 icon1, 617 icon2, 618 intent_action, 619 intent_component, 620 intent_data, 621 intent_query, 622 intent_extradata, 623 shortcut_id, 624 spinner_while_refreshing, 625 log_type; 626 627 static final String TABLE_NAME = "shortcuts"; 628 629 public final String fullName; 630 631 Shortcuts() { 632 fullName = TABLE_NAME + "." + name(); 633 } 634 } 635 636 /** 637 * clicklog table. Has one record for each click. 638 */ 639 enum ClickLog { 640 _id, 641 intent_key, 642 query, 643 hit_time, 644 corpus; 645 646 static final String[] COLUMNS = initColumns(); 647 648 static final String TABLE_NAME = "clicklog"; 649 650 private static String[] initColumns() { 651 ClickLog[] vals = ClickLog.values(); 652 String[] columns = new String[vals.length]; 653 for (int i = 0; i < vals.length; i++) { 654 columns[i] = vals[i].fullName; 655 } 656 return columns; 657 } 658 659 public final String fullName; 660 661 ClickLog() { 662 fullName = TABLE_NAME + "." + name(); 663 } 664 } 665 666 /** 667 * This is an aggregate table of {@link ClickLog} that stays up to date with the total 668 * clicks for each corpus. This makes computing the corpus ranking more 669 * more efficient, at the expense of some extra work when the clicks are reported. 670 */ 671 enum SourceStats { 672 corpus, 673 total_clicks; 674 675 static final String TABLE_NAME = "sourcetotals"; 676 677 static final String[] COLUMNS = initColumns(); 678 679 private static String[] initColumns() { 680 SourceStats[] vals = SourceStats.values(); 681 String[] columns = new String[vals.length]; 682 for (int i = 0; i < vals.length; i++) { 683 columns[i] = vals[i].fullName; 684 } 685 return columns; 686 } 687 688 public final String fullName; 689 690 SourceStats() { 691 fullName = TABLE_NAME + "." + name(); 692 } 693 } 694 695// -------------------------- END TABLES -------------------------- 696 697 // contains creation and update logic 698 private static class DbOpenHelper extends SQLiteOpenHelper { 699 private final Config mConfig; 700 private String mPath; 701 private static final String SHORTCUT_ID_INDEX 702 = Shortcuts.TABLE_NAME + "_" + Shortcuts.shortcut_id.name(); 703 private static final String CLICKLOG_QUERY_INDEX 704 = ClickLog.TABLE_NAME + "_" + ClickLog.query.name(); 705 private static final String CLICKLOG_HIT_TIME_INDEX 706 = ClickLog.TABLE_NAME + "_" + ClickLog.hit_time.name(); 707 private static final String CLICKLOG_INSERT_TRIGGER 708 = ClickLog.TABLE_NAME + "_insert"; 709 private static final String SHORTCUTS_DELETE_TRIGGER 710 = Shortcuts.TABLE_NAME + "_delete"; 711 private static final String SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER 712 = Shortcuts.TABLE_NAME + "_update_intent_key"; 713 714 public DbOpenHelper(Context context, String name, int version, Config config) { 715 super(context, name, null, version); 716 mConfig = config; 717 } 718 719 @Override 720 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 721 // The shortcuts info is not all that important, so we just drop the tables 722 // and re-create empty ones. 723 Log.i(TAG, "Upgrading shortcuts DB from version " + 724 + oldVersion + " to " + newVersion + ". This deletes all shortcuts."); 725 dropTables(db); 726 onCreate(db); 727 } 728 729 private void dropTables(SQLiteDatabase db) { 730 db.execSQL("DROP TRIGGER IF EXISTS " + CLICKLOG_INSERT_TRIGGER); 731 db.execSQL("DROP TRIGGER IF EXISTS " + SHORTCUTS_DELETE_TRIGGER); 732 db.execSQL("DROP TRIGGER IF EXISTS " + SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER); 733 db.execSQL("DROP INDEX IF EXISTS " + CLICKLOG_HIT_TIME_INDEX); 734 db.execSQL("DROP INDEX IF EXISTS " + CLICKLOG_QUERY_INDEX); 735 db.execSQL("DROP INDEX IF EXISTS " + SHORTCUT_ID_INDEX); 736 db.execSQL("DROP TABLE IF EXISTS " + ClickLog.TABLE_NAME); 737 db.execSQL("DROP TABLE IF EXISTS " + Shortcuts.TABLE_NAME); 738 db.execSQL("DROP TABLE IF EXISTS " + SourceStats.TABLE_NAME); 739 } 740 741 /** 742 * Deletes the database file. 743 */ 744 public void deleteDatabase() { 745 close(); 746 if (mPath == null) return; 747 try { 748 new File(mPath).delete(); 749 if (DBG) Log.d(TAG, "deleted " + mPath); 750 } catch (Exception e) { 751 Log.w(TAG, "couldn't delete " + mPath, e); 752 } 753 } 754 755 @Override 756 public void onOpen(SQLiteDatabase db) { 757 super.onOpen(db); 758 mPath = db.getPath(); 759 } 760 761 @Override 762 public void onCreate(SQLiteDatabase db) { 763 db.execSQL("CREATE TABLE " + Shortcuts.TABLE_NAME + " (" + 764 // COLLATE UNICODE is needed to make it possible to use nextString() 765 // to implement fast prefix filtering. 766 Shortcuts.intent_key.name() + " TEXT NOT NULL COLLATE UNICODE PRIMARY KEY, " + 767 Shortcuts.source.name() + " TEXT NOT NULL, " + 768 Shortcuts.source_version_code.name() + " INTEGER NOT NULL, " + 769 Shortcuts.format.name() + " TEXT, " + 770 Shortcuts.title.name() + " TEXT, " + 771 Shortcuts.description.name() + " TEXT, " + 772 Shortcuts.description_url.name() + " TEXT, " + 773 Shortcuts.icon1.name() + " TEXT, " + 774 Shortcuts.icon2.name() + " TEXT, " + 775 Shortcuts.intent_action.name() + " TEXT, " + 776 Shortcuts.intent_component.name() + " TEXT, " + 777 Shortcuts.intent_data.name() + " TEXT, " + 778 Shortcuts.intent_query.name() + " TEXT, " + 779 Shortcuts.intent_extradata.name() + " TEXT, " + 780 Shortcuts.shortcut_id.name() + " TEXT, " + 781 Shortcuts.spinner_while_refreshing.name() + " TEXT, " + 782 Shortcuts.log_type.name() + " TEXT" + 783 ");"); 784 785 // index for fast lookup of shortcuts by shortcut_id 786 db.execSQL("CREATE INDEX " + SHORTCUT_ID_INDEX 787 + " ON " + Shortcuts.TABLE_NAME 788 + "(" + Shortcuts.shortcut_id.name() + ", " + Shortcuts.source.name() + ")"); 789 790 db.execSQL("CREATE TABLE " + ClickLog.TABLE_NAME + " ( " + 791 ClickLog._id.name() + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " + 792 // type must match Shortcuts.intent_key 793 ClickLog.intent_key.name() + " TEXT NOT NULL COLLATE UNICODE REFERENCES " 794 + Shortcuts.TABLE_NAME + "(" + Shortcuts.intent_key + "), " + 795 ClickLog.query.name() + " TEXT, " + 796 ClickLog.hit_time.name() + " INTEGER," + 797 ClickLog.corpus.name() + " TEXT" + 798 ");"); 799 800 // index for fast lookup of clicks by query 801 db.execSQL("CREATE INDEX " + CLICKLOG_QUERY_INDEX 802 + " ON " + ClickLog.TABLE_NAME + "(" + ClickLog.query.name() + ")"); 803 804 // index for finding old clicks quickly 805 db.execSQL("CREATE INDEX " + CLICKLOG_HIT_TIME_INDEX 806 + " ON " + ClickLog.TABLE_NAME + "(" + ClickLog.hit_time.name() + ")"); 807 808 // trigger for purging old clicks, i.e. those such that 809 // hit_time < now - MAX_MAX_STAT_AGE_MILLIS, where now is the 810 // hit_time of the inserted record, and for updating the SourceStats table 811 db.execSQL("CREATE TRIGGER " + CLICKLOG_INSERT_TRIGGER + " AFTER INSERT ON " 812 + ClickLog.TABLE_NAME 813 + " BEGIN" 814 + " DELETE FROM " + ClickLog.TABLE_NAME + " WHERE " 815 + ClickLog.hit_time.name() + " <" 816 + " NEW." + ClickLog.hit_time.name() 817 + " - " + mConfig.getMaxStatAgeMillis() + ";" 818 + " DELETE FROM " + SourceStats.TABLE_NAME + ";" 819 + " INSERT INTO " + SourceStats.TABLE_NAME + " " 820 + "SELECT " + ClickLog.corpus + "," + "COUNT(*) FROM " 821 + ClickLog.TABLE_NAME + " GROUP BY " + ClickLog.corpus.name() + ";" 822 + " END"); 823 824 // trigger for deleting clicks about a shortcut once that shortcut has been 825 // deleted 826 db.execSQL("CREATE TRIGGER " + SHORTCUTS_DELETE_TRIGGER + " AFTER DELETE ON " 827 + Shortcuts.TABLE_NAME 828 + " BEGIN" 829 + " DELETE FROM " + ClickLog.TABLE_NAME + " WHERE " 830 + ClickLog.intent_key.name() 831 + " = OLD." + Shortcuts.intent_key.name() + ";" 832 + " END"); 833 834 // trigger for updating click log entries when a shortcut changes its intent_key 835 db.execSQL("CREATE TRIGGER " + SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER 836 + " AFTER UPDATE ON " + Shortcuts.TABLE_NAME 837 + " WHEN NEW." + Shortcuts.intent_key.name() 838 + " != OLD." + Shortcuts.intent_key.name() 839 + " BEGIN" 840 + " UPDATE " + ClickLog.TABLE_NAME + " SET " 841 + ClickLog.intent_key.name() + " = NEW." + Shortcuts.intent_key.name() 842 + " WHERE " 843 + ClickLog.intent_key.name() + " = OLD." + Shortcuts.intent_key.name() 844 + ";" 845 + " END"); 846 847 db.execSQL("CREATE TABLE " + SourceStats.TABLE_NAME + " ( " + 848 SourceStats.corpus.name() + " TEXT NOT NULL COLLATE UNICODE PRIMARY KEY, " + 849 SourceStats.total_clicks + " INTEGER);" 850 ); 851 } 852 } 853} 854