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