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