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