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