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