ShortcutRepositoryImplLog.java revision 883c1bf364e38c5b133afb55f8493a14b65f4dd4
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 public boolean isSuggestionShortcut() { 302 return true; 303 } 304 } 305 306 /** 307 * Builds a parameter list for the query returned by {@link #buildShortcutQuery(boolean)}. 308 */ 309 private static String[] buildShortcutQueryParams(String query, long now) { 310 return new String[]{ query, nextString(query), String.valueOf(now) }; 311 } 312 313 /** 314 * Given a string x, this method returns the least string y such that x is not a prefix of y. 315 * This is useful to implement prefix filtering by comparison, since the only strings z that 316 * have x as a prefix are such that z is greater than or equal to x and z is less than y. 317 * 318 * @param str A non-empty string. The contract above is not honored for an empty input string, 319 * since all strings have the empty string as a prefix. 320 */ 321 private static String nextString(String str) { 322 int len = str.length(); 323 if (len == 0) { 324 return str; 325 } 326 // The last code point in the string. Within the Basic Multilingual Plane, 327 // this is the same as str.charAt(len-1) 328 int codePoint = str.codePointBefore(len); 329 // This should be safe from overflow, since the largest code point 330 // representable in UTF-16 is U+10FFFF. 331 int nextCodePoint = codePoint + 1; 332 // The index of the start of the last code point. 333 // Character.charCount(codePoint) is always 1 (in the BMP) or 2 334 int lastIndex = len - Character.charCount(codePoint); 335 return new StringBuilder(len) 336 .append(str, 0, lastIndex) // append everything but the last code point 337 .appendCodePoint(nextCodePoint) // instead of the last code point, use successor 338 .toString(); 339 } 340 341 /** 342 * Returns the source ranking for sources with a minimum number of clicks. 343 * 344 * @param minClicks The minimum number of clicks a source must have. 345 * @return The list of sources, ranked by total clicks. 346 */ 347 Map<String,Integer> getCorpusScores(int minClicks) { 348 SQLiteDatabase db = mOpenHelper.getReadableDatabase(); 349 final Cursor cursor = db.rawQuery( 350 SOURCE_RANKING_SQL, new String[] { String.valueOf(minClicks) }); 351 try { 352 Map<String,Integer> corpora = new HashMap<String,Integer>(cursor.getCount()); 353 while (cursor.moveToNext()) { 354 String name = cursor.getString(SourceStats.corpus.ordinal()); 355 int clicks = cursor.getInt(SourceStats.total_clicks.ordinal()); 356 corpora.put(name, clicks); 357 } 358 return corpora; 359 } finally { 360 cursor.close(); 361 } 362 } 363 364 private ContentValues makeShortcutRow(SuggestionCursor suggestion) { 365 String intentAction = suggestion.getSuggestionIntentAction(); 366 String intentData = suggestion.getSuggestionIntentDataString(); 367 String intentQuery = suggestion.getSuggestionQuery(); 368 String intentExtraData = suggestion.getSuggestionIntentExtraData(); 369 370 ComponentName source = suggestion.getSuggestionSource().getComponentName(); 371 StringBuilder key = new StringBuilder(source.flattenToShortString()); 372 key.append("#"); 373 if (intentData != null) { 374 key.append(intentData); 375 } 376 key.append("#"); 377 if (intentAction != null) { 378 key.append(intentAction); 379 } 380 key.append("#"); 381 if (intentQuery != null) { 382 key.append(intentQuery); 383 } 384 // A string of the form source#intentData#intentAction#intentQuery 385 // for use as a unique identifier of a suggestion. 386 String intentKey = key.toString(); 387 388 ContentValues cv = new ContentValues(); 389 cv.put(Shortcuts.intent_key.name(), intentKey); 390 cv.put(Shortcuts.source.name(), source.flattenToShortString()); 391 cv.put(Shortcuts.format.name(), suggestion.getSuggestionFormat()); 392 cv.put(Shortcuts.title.name(), suggestion.getSuggestionText1()); 393 cv.put(Shortcuts.description.name(), suggestion.getSuggestionText2()); 394 cv.put(Shortcuts.icon1.name(), suggestion.getSuggestionIcon1()); 395 cv.put(Shortcuts.icon2.name(), suggestion.getSuggestionIcon2()); 396 cv.put(Shortcuts.intent_action.name(), intentAction); 397 cv.put(Shortcuts.intent_data.name(), intentData); 398 cv.put(Shortcuts.intent_query.name(), intentQuery); 399 cv.put(Shortcuts.intent_extradata.name(), intentExtraData); 400 cv.put(Shortcuts.shortcut_id.name(), suggestion.getShortcutId()); 401 if (suggestion.isSpinnerWhileRefreshing()) { 402 cv.put(Shortcuts.spinner_while_refreshing.name(), "true"); 403 } 404 405 return cv; 406 } 407 408 /* package for testing */ void reportClickAtTime(SuggestionCursor suggestion, 409 int position, long now) { 410 suggestion.moveTo(position); 411 if (DBG) { 412 Log.d(TAG, "logClicked(" + suggestion + ")"); 413 } 414 415 if (SearchManager.SUGGEST_NEVER_MAKE_SHORTCUT.equals(suggestion.getShortcutId())) { 416 if (DBG) Log.d(TAG, "clicked suggestion requested not to be shortcuted"); 417 return; 418 } 419 420 // Once the user has clicked on a shortcut, don't bother refreshing 421 // (especially if this is a new shortcut) 422 mRefresher.onShortcutRefreshed(suggestion); 423 424 SQLiteDatabase db = mOpenHelper.getWritableDatabase(); 425 426 // Add or update suggestion info 427 // Since intent_key is the primary key, any existing 428 // suggestion with the same source+data+action will be replaced 429 ContentValues shortcut = makeShortcutRow(suggestion); 430 String intentKey = shortcut.getAsString(Shortcuts.intent_key.name()); 431 if (DBG) Log.d(TAG, "Adding shortcut: " + shortcut); 432 db.replaceOrThrow(Shortcuts.TABLE_NAME, null, shortcut); 433 434 // Log click for shortcut 435 { 436 final ContentValues cv = new ContentValues(); 437 cv.put(ClickLog.intent_key.name(), intentKey); 438 cv.put(ClickLog.query.name(), suggestion.getUserQuery()); 439 cv.put(ClickLog.hit_time.name(), now); 440 db.insertOrThrow(ClickLog.TABLE_NAME, null, cv); 441 } 442 443 // Log click for corpus 444 Corpus corpus = mCorpora.getCorpusForSource(suggestion.getSuggestionSource()); 445 logCorpusClick(db, corpus, now); 446 447 postSourceEventCleanup(now); 448 } 449 450 private void logCorpusClick(SQLiteDatabase db, Corpus corpus, long now) { 451 if (corpus == null) return; 452 ContentValues cv = new ContentValues(); 453 cv.put(SourceLog.corpus.name(), corpus.getName()); 454 cv.put(SourceLog.time.name(), now); 455 cv.put(SourceLog.click_count.name(), 1); 456 db.insertOrThrow(SourceLog.TABLE_NAME, null, cv); 457 } 458 459 /** 460 * Execute queries necessary to keep things up to date after inserting into {@link SourceLog}. 461 * 462 * TODO: Switch back to using a trigger? 463 * 464 * @param now Millis since epoch of "now". 465 */ 466 private void postSourceEventCleanup(long now) { 467 SQLiteDatabase db = mOpenHelper.getWritableDatabase(); 468 469 // purge old log entries 470 db.execSQL("DELETE FROM " + SourceLog.TABLE_NAME + " WHERE " 471 + SourceLog.time.name() + " <" 472 + now + " - " + mConfig.getMaxSourceEventAgeMillis() + ";"); 473 474 // update the source stats 475 final String columns = SourceLog.corpus + "," + 476 "SUM(" + SourceLog.click_count.fullName + ")"; 477 db.execSQL("DELETE FROM " + SourceStats.TABLE_NAME); 478 db.execSQL("INSERT INTO " + SourceStats.TABLE_NAME + " " 479 + "SELECT " + columns + " FROM " + SourceLog.TABLE_NAME + " GROUP BY " 480 + SourceLog.corpus.name()); 481 } 482 483// -------------------------- TABLES -------------------------- 484 485 /** 486 * shortcuts table 487 */ 488 enum Shortcuts { 489 intent_key, 490 source, 491 format, 492 title, 493 description, 494 icon1, 495 icon2, 496 intent_action, 497 intent_data, 498 intent_query, 499 intent_extradata, 500 shortcut_id, 501 spinner_while_refreshing; 502 503 static final String TABLE_NAME = "shortcuts"; 504 505 public final String fullName; 506 507 Shortcuts() { 508 fullName = TABLE_NAME + "." + name(); 509 } 510 } 511 512 /** 513 * clicklog table. Has one record for each click. 514 */ 515 enum ClickLog { 516 _id, 517 intent_key, 518 query, 519 hit_time; 520 521 static final String[] COLUMNS = initColumns(); 522 523 static final String TABLE_NAME = "clicklog"; 524 525 private static String[] initColumns() { 526 ClickLog[] vals = ClickLog.values(); 527 String[] columns = new String[vals.length]; 528 for (int i = 0; i < vals.length; i++) { 529 columns[i] = vals[i].fullName; 530 } 531 return columns; 532 } 533 534 public final String fullName; 535 536 ClickLog() { 537 fullName = TABLE_NAME + "." + name(); 538 } 539 } 540 541 /** 542 * We store stats about clicks and impressions per source to facilitate the ranking of 543 * the sources, and which are promoted vs under the "more results" entry. 544 */ 545 enum SourceLog { 546 _id, 547 corpus, 548 time, 549 click_count, 550 impression_count; 551 552 static final String[] COLUMNS = initColumns(); 553 554 static final String TABLE_NAME = "sourceeventlog"; 555 556 private static String[] initColumns() { 557 SourceLog[] vals = SourceLog.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 SourceLog() { 568 fullName = TABLE_NAME + "." + name(); 569 } 570 } 571 572 /** 573 * This is an aggregate table of {@link SourceLog} that stays up to date with the total 574 * clicks for each source. This makes computing the source ranking more 575 * more efficient, at the expense of some extra work when the source clicks 576 * are reported. 577 */ 578 enum SourceStats { 579 corpus, 580 total_clicks; 581 582 static final String TABLE_NAME = "sourcetotals"; 583 584 static final String[] COLUMNS = initColumns(); 585 586 private static String[] initColumns() { 587 SourceStats[] vals = SourceStats.values(); 588 String[] columns = new String[vals.length]; 589 for (int i = 0; i < vals.length; i++) { 590 columns[i] = vals[i].fullName; 591 } 592 return columns; 593 } 594 595 public final String fullName; 596 597 SourceStats() { 598 fullName = TABLE_NAME + "." + name(); 599 } 600 } 601 602// -------------------------- END TABLES -------------------------- 603 604 // contains creation and update logic 605 private static class DbOpenHelper extends SQLiteOpenHelper { 606 private Config mConfig; 607 private String mPath; 608 private static final String SHORTCUT_ID_INDEX 609 = Shortcuts.TABLE_NAME + "_" + Shortcuts.shortcut_id.name(); 610 private static final String CLICKLOG_QUERY_INDEX 611 = ClickLog.TABLE_NAME + "_" + ClickLog.query.name(); 612 private static final String CLICKLOG_HIT_TIME_INDEX 613 = ClickLog.TABLE_NAME + "_" + ClickLog.hit_time.name(); 614 private static final String CLICKLOG_PURGE_TRIGGER 615 = ClickLog.TABLE_NAME + "_purge"; 616 private static final String SHORTCUTS_DELETE_TRIGGER 617 = Shortcuts.TABLE_NAME + "_delete"; 618 private static final String SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER 619 = Shortcuts.TABLE_NAME + "_update_intent_key"; 620 621 public DbOpenHelper(Context context, String name, int version, Config config) { 622 super(context, name, null, version); 623 mConfig = config; 624 } 625 626 public String getPath() { 627 return mPath; 628 } 629 630 @Override 631 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 632 // The shortcuts info is not all that important, so we just drop the tables 633 // and re-create empty ones. 634 Log.i(TAG, "Upgrading shortcuts DB from version " + 635 + oldVersion + " to " + newVersion + ". This deletes all shortcuts."); 636 dropTables(db); 637 onCreate(db); 638 } 639 640 private void dropTables(SQLiteDatabase db) { 641 db.execSQL("DROP TRIGGER IF EXISTS " + CLICKLOG_PURGE_TRIGGER); 642 db.execSQL("DROP TRIGGER IF EXISTS " + SHORTCUTS_DELETE_TRIGGER); 643 db.execSQL("DROP TRIGGER IF EXISTS " + SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER); 644 db.execSQL("DROP INDEX IF EXISTS " + CLICKLOG_HIT_TIME_INDEX); 645 db.execSQL("DROP INDEX IF EXISTS " + CLICKLOG_QUERY_INDEX); 646 db.execSQL("DROP INDEX IF EXISTS " + SHORTCUT_ID_INDEX); 647 db.execSQL("DROP TABLE IF EXISTS " + ClickLog.TABLE_NAME); 648 db.execSQL("DROP TABLE IF EXISTS " + Shortcuts.TABLE_NAME); 649 db.execSQL("DROP TABLE IF EXISTS " + SourceLog.TABLE_NAME); 650 db.execSQL("DROP TABLE IF EXISTS " + SourceStats.TABLE_NAME); 651 } 652 653 private void clearDatabase(SQLiteDatabase db) { 654 db.delete(ClickLog.TABLE_NAME, null, null); 655 db.delete(Shortcuts.TABLE_NAME, null, null); 656 db.delete(SourceLog.TABLE_NAME, null, null); 657 db.delete(SourceStats.TABLE_NAME, null, null); 658 } 659 660 /** 661 * Deletes the database file. 662 */ 663 public void deleteDatabase() { 664 close(); 665 if (mPath == null) return; 666 try { 667 new File(mPath).delete(); 668 if (DBG) Log.d(TAG, "deleted " + mPath); 669 } catch (Exception e) { 670 Log.w(TAG, "couldn't delete " + mPath, e); 671 } 672 } 673 674 @Override 675 public void onOpen(SQLiteDatabase db) { 676 super.onOpen(db); 677 mPath = db.getPath(); 678 } 679 680 @Override 681 public void onCreate(SQLiteDatabase db) { 682 db.execSQL("CREATE TABLE " + Shortcuts.TABLE_NAME + " (" + 683 // COLLATE UNICODE is needed to make it possible to use nextString() 684 // to implement fast prefix filtering. 685 Shortcuts.intent_key.name() + " TEXT NOT NULL COLLATE UNICODE PRIMARY KEY, " + 686 Shortcuts.source.name() + " TEXT NOT NULL, " + 687 Shortcuts.format.name() + " TEXT, " + 688 Shortcuts.title.name() + " TEXT, " + 689 Shortcuts.description.name() + " TEXT, " + 690 Shortcuts.icon1.name() + " TEXT, " + 691 Shortcuts.icon2.name() + " TEXT, " + 692 Shortcuts.intent_action.name() + " TEXT, " + 693 Shortcuts.intent_data.name() + " TEXT, " + 694 Shortcuts.intent_query.name() + " TEXT, " + 695 Shortcuts.intent_extradata.name() + " TEXT, " + 696 Shortcuts.shortcut_id.name() + " TEXT, " + 697 Shortcuts.spinner_while_refreshing.name() + " TEXT" + 698 ");"); 699 700 // index for fast lookup of shortcuts by shortcut_id 701 db.execSQL("CREATE INDEX " + SHORTCUT_ID_INDEX 702 + " ON " + Shortcuts.TABLE_NAME 703 + "(" + Shortcuts.shortcut_id.name() + ", " + Shortcuts.source.name() + ")"); 704 705 db.execSQL("CREATE TABLE " + ClickLog.TABLE_NAME + " ( " + 706 ClickLog._id.name() + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " + 707 // type must match Shortcuts.intent_key 708 ClickLog.intent_key.name() + " TEXT NOT NULL COLLATE UNICODE REFERENCES " 709 + Shortcuts.TABLE_NAME + "(" + Shortcuts.intent_key + "), " + 710 ClickLog.query.name() + " TEXT, " + 711 ClickLog.hit_time.name() + " INTEGER" + 712 ");"); 713 714 // index for fast lookup of clicks by query 715 db.execSQL("CREATE INDEX " + CLICKLOG_QUERY_INDEX 716 + " ON " + ClickLog.TABLE_NAME + "(" + ClickLog.query.name() + ")"); 717 718 // index for finding old clicks quickly 719 db.execSQL("CREATE INDEX " + CLICKLOG_HIT_TIME_INDEX 720 + " ON " + ClickLog.TABLE_NAME + "(" + ClickLog.hit_time.name() + ")"); 721 722 // trigger for purging old clicks, i.e. those such that 723 // hit_time < now - MAX_MAX_STAT_AGE_MILLIS, where now is the 724 // hit_time of the inserted record 725 db.execSQL("CREATE TRIGGER " + CLICKLOG_PURGE_TRIGGER + " AFTER INSERT ON " 726 + ClickLog.TABLE_NAME 727 + " BEGIN" 728 + " DELETE FROM " + ClickLog.TABLE_NAME + " WHERE " 729 + ClickLog.hit_time.name() + " <" 730 + " NEW." + ClickLog.hit_time.name() 731 + " - " + mConfig.getMaxStatAgeMillis() + ";" 732 + " END"); 733 734 // trigger for deleting clicks about a shortcut once that shortcut has been 735 // deleted 736 db.execSQL("CREATE TRIGGER " + SHORTCUTS_DELETE_TRIGGER + " AFTER DELETE ON " 737 + Shortcuts.TABLE_NAME 738 + " BEGIN" 739 + " DELETE FROM " + ClickLog.TABLE_NAME + " WHERE " 740 + ClickLog.intent_key.name() 741 + " = OLD." + Shortcuts.intent_key.name() + ";" 742 + " END"); 743 744 // trigger for updating click log entries when a shortcut changes its intent_key 745 db.execSQL("CREATE TRIGGER " + SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER 746 + " AFTER UPDATE ON " + Shortcuts.TABLE_NAME 747 + " WHEN NEW." + Shortcuts.intent_key.name() 748 + " != OLD." + Shortcuts.intent_key.name() 749 + " BEGIN" 750 + " UPDATE " + ClickLog.TABLE_NAME + " SET " 751 + ClickLog.intent_key.name() + " = NEW." + Shortcuts.intent_key.name() 752 + " WHERE " 753 + ClickLog.intent_key.name() + " = OLD." + Shortcuts.intent_key.name() 754 + ";" 755 + " END"); 756 757 db.execSQL("CREATE TABLE " + SourceLog.TABLE_NAME + " ( " + 758 SourceLog._id.name() + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " + 759 SourceLog.corpus.name() + " TEXT NOT NULL COLLATE UNICODE, " + 760 SourceLog.time.name() + " INTEGER, " + 761 SourceLog.click_count + " INTEGER);" 762 ); 763 764 db.execSQL("CREATE TABLE " + SourceStats.TABLE_NAME + " ( " + 765 SourceStats.corpus.name() + " TEXT NOT NULL COLLATE UNICODE PRIMARY KEY, " + 766 SourceStats.total_clicks + " INTEGER);" 767 ); 768 } 769 } 770} 771