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