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