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