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