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