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