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