/*
* Copyright (C) 2009 The Android Open Source Project
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.android.quicksearchbox;
import com.android.quicksearchbox.util.Consumer;
import com.android.quicksearchbox.util.Consumers;
import com.android.quicksearchbox.util.SQLiteAsyncQuery;
import com.android.quicksearchbox.util.SQLiteTransaction;
import com.android.quicksearchbox.util.Util;
import com.google.common.annotations.VisibleForTesting;
import org.json.JSONException;
import android.app.SearchManager;
import android.content.ComponentName;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.os.Handler;
import android.text.TextUtils;
import android.util.Log;
import java.io.File;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.Executor;
/**
* A shortcut repository implementation that uses a log of every click.
*
* To inspect DB:
* # sqlite3 /data/data/com.android.quicksearchbox/databases/qsb-log.db
*
* TODO: Refactor this class.
*/
public class ShortcutRepositoryImplLog implements ShortcutRepository {
private static final boolean DBG = false;
private static final String TAG = "QSB.ShortcutRepositoryImplLog";
private static final String DB_NAME = "qsb-log.db";
private static final int DB_VERSION = 32;
private static final String HAS_HISTORY_QUERY =
"SELECT " + Shortcuts.intent_key.fullName + " FROM " + Shortcuts.TABLE_NAME;
private String mEmptyQueryShortcutQuery ;
private String mShortcutQuery;
private static final String SHORTCUT_BY_ID_WHERE =
Shortcuts.shortcut_id.name() + "=? AND " + Shortcuts.source.name() + "=?";
private static final String SOURCE_RANKING_SQL = buildSourceRankingSql();
private final Context mContext;
private final Config mConfig;
private final Corpora mCorpora;
private final ShortcutRefresher mRefresher;
private final Handler mUiThread;
// Used to perform log write operations asynchronously
private final Executor mLogExecutor;
private final DbOpenHelper mOpenHelper;
private final String mSearchSpinner;
/**
* Create an instance to the repo.
*/
public static ShortcutRepository create(Context context, Config config,
Corpora sources, ShortcutRefresher refresher, Handler uiThread,
Executor logExecutor) {
return new ShortcutRepositoryImplLog(context, config, sources, refresher,
uiThread, logExecutor, DB_NAME);
}
/**
* @param context Used to create / open db
* @param name The name of the database to create.
*/
@VisibleForTesting
ShortcutRepositoryImplLog(Context context, Config config, Corpora corpora,
ShortcutRefresher refresher, Handler uiThread, Executor logExecutor, String name) {
mContext = context;
mConfig = config;
mCorpora = corpora;
mRefresher = refresher;
mUiThread = uiThread;
mLogExecutor = logExecutor;
mOpenHelper = new DbOpenHelper(context, name, DB_VERSION, config);
buildShortcutQueries();
mSearchSpinner = Util.getResourceUri(mContext, R.drawable.search_spinner).toString();
}
// clicklog first, since that's where restrict the result set
private static final String TABLES = ClickLog.TABLE_NAME + " INNER JOIN " +
Shortcuts.TABLE_NAME + " ON " + ClickLog.intent_key.fullName + " = " +
Shortcuts.intent_key.fullName;
private static final String AS = " AS ";
private static final String[] SHORTCUT_QUERY_COLUMNS = {
Shortcuts.intent_key.fullName,
Shortcuts.source.fullName,
Shortcuts.source_version_code.fullName,
Shortcuts.format.fullName + AS + SearchManager.SUGGEST_COLUMN_FORMAT,
Shortcuts.title + AS + SearchManager.SUGGEST_COLUMN_TEXT_1,
Shortcuts.description + AS + SearchManager.SUGGEST_COLUMN_TEXT_2,
Shortcuts.description_url + AS + SearchManager.SUGGEST_COLUMN_TEXT_2_URL,
Shortcuts.icon1 + AS + SearchManager.SUGGEST_COLUMN_ICON_1,
Shortcuts.icon2 + AS + SearchManager.SUGGEST_COLUMN_ICON_2,
Shortcuts.intent_action + AS + SearchManager.SUGGEST_COLUMN_INTENT_ACTION,
Shortcuts.intent_component.fullName,
Shortcuts.intent_data + AS + SearchManager.SUGGEST_COLUMN_INTENT_DATA,
Shortcuts.intent_query + AS + SearchManager.SUGGEST_COLUMN_QUERY,
Shortcuts.intent_extradata + AS + SearchManager.SUGGEST_COLUMN_INTENT_EXTRA_DATA,
Shortcuts.shortcut_id + AS + SearchManager.SUGGEST_COLUMN_SHORTCUT_ID,
Shortcuts.spinner_while_refreshing + AS +
SearchManager.SUGGEST_COLUMN_SPINNER_WHILE_REFRESHING,
Shortcuts.log_type + AS + CursorBackedSuggestionCursor.SUGGEST_COLUMN_LOG_TYPE,
Shortcuts.custom_columns.fullName,
};
// Avoid GLOB by using >= AND <, with some manipulation (see nextString(String)).
// to figure out the upper bound (e.g. >= "abc" AND < "abd"
// This allows us to use parameter binding and still take advantage of the
// index on the query column.
private static final String PREFIX_RESTRICTION =
ClickLog.query.fullName + " >= ?1 AND " + ClickLog.query.fullName + " < ?2";
private static final String LAST_HIT_TIME_EXPR = "MAX(" + ClickLog.hit_time.fullName + ")";
private static final String GROUP_BY = ClickLog.intent_key.fullName;
private static final String PREFER_LATEST_PREFIX =
"(" + LAST_HIT_TIME_EXPR + " = (SELECT " + LAST_HIT_TIME_EXPR + " FROM " +
ClickLog.TABLE_NAME + " WHERE ";
private static final String PREFER_LATEST_SUFFIX = "))";
private void buildShortcutQueries() {
// SQL expression for the time before which no clicks should be counted.
String cutOffTime_expr = "(?3 - " + mConfig.getMaxStatAgeMillis() + ")";
// Filter out clicks that are too old
String ageRestriction = ClickLog.hit_time.fullName + " >= " + cutOffTime_expr;
String having = null;
// Order by sum of hit times (seconds since cutoff) for the clicks for each shortcut.
// This has the effect of multiplying the average hit time with the click count
String ordering_expr =
"SUM((" + ClickLog.hit_time.fullName + " - " + cutOffTime_expr + ") / 1000)";
String where = ageRestriction;
String preferLatest = PREFER_LATEST_PREFIX + where + PREFER_LATEST_SUFFIX;
String orderBy = preferLatest + " DESC, " + ordering_expr + " DESC";
mEmptyQueryShortcutQuery = SQLiteQueryBuilder.buildQueryString(
false, TABLES, SHORTCUT_QUERY_COLUMNS, where, GROUP_BY, having, orderBy, null);
if (DBG) Log.d(TAG, "Empty shortcut query:\n" + mEmptyQueryShortcutQuery);
where = PREFIX_RESTRICTION + " AND " + ageRestriction;
preferLatest = PREFER_LATEST_PREFIX + where + PREFER_LATEST_SUFFIX;
orderBy = preferLatest + " DESC, " + ordering_expr + " DESC";
mShortcutQuery = SQLiteQueryBuilder.buildQueryString(
false, TABLES, SHORTCUT_QUERY_COLUMNS, where, GROUP_BY, having, orderBy, null);
if (DBG) Log.d(TAG, "Empty shortcut:\n" + mShortcutQuery);
}
/**
* @return sql that ranks sources by total clicks, filtering out sources
* without enough clicks.
*/
private static String buildSourceRankingSql() {
final String orderingExpr = SourceStats.total_clicks.name();
final String tables = SourceStats.TABLE_NAME;
final String[] columns = SourceStats.COLUMNS;
final String where = SourceStats.total_clicks + " >= $1";
final String groupBy = null;
final String having = null;
final String orderBy = orderingExpr + " DESC";
final String limit = null;
return SQLiteQueryBuilder.buildQueryString(
false, tables, columns, where, groupBy, having, orderBy, limit);
}
protected DbOpenHelper getOpenHelper() {
return mOpenHelper;
}
private void runTransactionAsync(final SQLiteTransaction transaction) {
mLogExecutor.execute(new Runnable() {
public void run() {
transaction.run(mOpenHelper.getWritableDatabase());
}
});
}
private <A> void runQueryAsync(final SQLiteAsyncQuery<A> query, final Consumer<A> consumer) {
mLogExecutor.execute(new Runnable() {
public void run() {
query.run(mOpenHelper.getReadableDatabase(), consumer);
}
});
}
// --------------------- Interface ShortcutRepository ---------------------
public void hasHistory(Consumer<Boolean> consumer) {
runQueryAsync(new SQLiteAsyncQuery<Boolean>() {
@Override
protected Boolean performQuery(SQLiteDatabase db) {
return hasHistory(db);
}
}, consumer);
}
public void removeFromHistory(SuggestionCursor suggestions, int position) {
suggestions.moveTo(position);
final String intentKey = makeIntentKey(suggestions);
runTransactionAsync(new SQLiteTransaction() {
@Override
public boolean performTransaction(SQLiteDatabase db) {
db.delete(Shortcuts.TABLE_NAME, Shortcuts.intent_key.fullName + " = ?",
new String[]{ intentKey });
return true;
}
});
}
public void clearHistory() {
runTransactionAsync(new SQLiteTransaction() {
@Override
public boolean performTransaction(SQLiteDatabase db) {
db.delete(ClickLog.TABLE_NAME, null, null);
db.delete(Shortcuts.TABLE_NAME, null, null);
db.delete(SourceStats.TABLE_NAME, null, null);
return true;
}
});
}
@VisibleForTesting
public void deleteRepository() {
getOpenHelper().deleteDatabase();
}
public void close() {
getOpenHelper().close();
}
public void reportClick(final SuggestionCursor suggestions, final int position) {
final long now = System.currentTimeMillis();
reportClickAtTime(suggestions, position, now);
}
public void getShortcutsForQuery(final String query, final Collection<Corpus> allowedCorpora,
final boolean allowWebSearchShortcuts, final Consumer<ShortcutCursor> consumer) {
final long now = System.currentTimeMillis();
mLogExecutor.execute(new Runnable() {
public void run() {
ShortcutCursor shortcuts = getShortcutsForQuery(query, allowedCorpora,
allowWebSearchShortcuts, now);
Consumers.consumeCloseable(consumer, shortcuts);
}
});
}
public void updateShortcut(Source source, String shortcutId, SuggestionCursor refreshed) {
refreshShortcut(source, shortcutId, refreshed);
}
public void getCorpusScores(final Consumer<Map<String, Integer>> consumer) {
runQueryAsync(new SQLiteAsyncQuery<Map<String, Integer>>() {
@Override
protected Map<String, Integer> performQuery(SQLiteDatabase db) {
return getCorpusScores();
}
}, consumer);
}
// -------------------------- end ShortcutRepository --------------------------
private boolean hasHistory(SQLiteDatabase db) {
Cursor cursor = db.rawQuery(HAS_HISTORY_QUERY, null);
try {
if (DBG) Log.d(TAG, "hasHistory(): cursor=" + cursor);
return cursor != null && cursor.getCount() > 0;
} finally {
if (cursor != null) cursor.close();
}
}
private Map<String,Integer> getCorpusScores() {
return getCorpusScores(mConfig.getMinClicksForSourceRanking());
}
private boolean shouldRefresh(Suggestion suggestion) {
return mRefresher.shouldRefresh(suggestion.getSuggestionSource(),
suggestion.getShortcutId());
}
@VisibleForTesting
ShortcutCursor getShortcutsForQuery(String query, Collection<Corpus> allowedCorpora,
boolean allowWebSearchShortcuts, long now) {
if (DBG) Log.d(TAG, "getShortcutsForQuery(" + query + "," + allowedCorpora + ")");
String sql = query.length() == 0 ? mEmptyQueryShortcutQuery : mShortcutQuery;
String[] params = buildShortcutQueryParams(query, now);
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery(sql, params);
if (cursor.getCount() == 0) {
cursor.close();
return null;
}
if (DBG) Log.d(TAG, "Allowed sources: ");
HashMap<String,Source> allowedSources = new HashMap<String,Source>();
for (Corpus corpus : allowedCorpora) {
for (Source source : corpus.getSources()) {
if (DBG) Log.d(TAG, "\t" + source.getName());
allowedSources.put(source.getName(), source);
}
}
return new ShortcutCursor(new SuggestionCursorImpl(allowedSources, query, cursor),
allowWebSearchShortcuts, mUiThread, mRefresher, this);
}
@VisibleForTesting
void refreshShortcut(Source source, final String shortcutId,
SuggestionCursor refreshed) {
if (source == null) throw new NullPointerException("source");
if (shortcutId == null) throw new NullPointerException("shortcutId");
final String[] whereArgs = { shortcutId, source.getName() };
final ContentValues shortcut;
if (refreshed == null || refreshed.getCount() == 0) {
shortcut = null;
} else {
refreshed.moveTo(0);
shortcut = makeShortcutRow(refreshed);
}
runTransactionAsync(new SQLiteTransaction() {
@Override
protected boolean performTransaction(SQLiteDatabase db) {
if (shortcut == null) {
if (DBG) Log.d(TAG, "Deleting shortcut: " + shortcutId);
db.delete(Shortcuts.TABLE_NAME, SHORTCUT_BY_ID_WHERE, whereArgs);
} else {
if (DBG) Log.d(TAG, "Updating shortcut: " + shortcut);
db.updateWithOnConflict(Shortcuts.TABLE_NAME, shortcut,
SHORTCUT_BY_ID_WHERE, whereArgs, SQLiteDatabase.CONFLICT_REPLACE);
}
return true;
}
});
}
private class SuggestionCursorImpl extends CursorBackedSuggestionCursor {
private final HashMap<String, Source> mAllowedSources;
private final int mExtrasColumn;
public SuggestionCursorImpl(HashMap<String,Source> allowedSources,
String userQuery, Cursor cursor) {
super(userQuery, cursor);
mAllowedSources = allowedSources;
mExtrasColumn = cursor.getColumnIndex(Shortcuts.custom_columns.name());
}
@Override
public Source getSuggestionSource() {
int srcCol = mCursor.getColumnIndex(Shortcuts.source.name());
String srcStr = mCursor.getString(srcCol);
if (srcStr == null) {
throw new NullPointerException("Missing source for shortcut.");
}
Source source = mAllowedSources.get(srcStr);
if (source == null) {
if (DBG) {
Log.d(TAG, "Source " + srcStr + " (position " + mCursor.getPosition() +
") not allowed");
}
return null;
}
int versionCode = mCursor.getInt(Shortcuts.source_version_code.ordinal());
if (!source.isVersionCodeCompatible(versionCode)) {
if (DBG) {
Log.d(TAG, "Version " + versionCode + " not compatible with " +
source.getVersionCode() + " for source " + srcStr);
}
return null;
}
return source;
}
@Override
public ComponentName getSuggestionIntentComponent() {
int componentCol = mCursor.getColumnIndex(Shortcuts.intent_component.name());
// We don't fall back to getSuggestionSource().getIntentComponent() because
// we want to return the same value that getSuggestionIntentComponent() did for the
// original suggestion.
return stringToComponentName(mCursor.getString(componentCol));
}
@Override
public String getSuggestionIcon2() {
if (isSpinnerWhileRefreshing() && shouldRefresh(this)) {
if (DBG) Log.d(TAG, "shortcut " + getShortcutId() + " refreshing");
return mSearchSpinner;
}
if (DBG) Log.d(TAG, "shortcut " + getShortcutId() + " NOT refreshing");
return super.getSuggestionIcon2();
}
public boolean isSuggestionShortcut() {
return true;
}
public boolean isHistorySuggestion() {
// This always returns false, even for suggestions that originally came
// from server-side history, since we'd otherwise have to parse the Genie
// extra data. This is ok, since this method is only used for the
// "Remove from history" UI, which is also shown for all shortcuts.
return false;
}
@Override
public SuggestionExtras getExtras() {
String json = mCursor.getString(mExtrasColumn);
if (!TextUtils.isEmpty(json)) {
try {
return new JsonBackedSuggestionExtras(json);
} catch (JSONException e) {
Log.e(TAG, "Could not parse JSON extras from DB: " + json);
}
}
return null;
}
public Collection<String> getExtraColumns() {
/*
* We always return null here because:
* - to return an accurate value, we'd have to aggregate all the extra columns in all
* shortcuts in the shortcuts table, which would mean parsing ALL the JSON contained
* therein
* - ListSuggestionCursor does this aggregation, and does it lazily
* - All shortcuts are put into a ListSuggestionCursor during the promotion process, so
* relying on ListSuggestionCursor to do the aggregation means that we only parse the
* JSON for shortcuts that are actually displayed.
*/
return null;
}
}
/**
* Builds a parameter list for the queries built by {@link #buildShortcutQueries}.
*/
private static String[] buildShortcutQueryParams(String query, long now) {
return new String[]{ query, nextString(query), String.valueOf(now) };
}
/**
* Given a string x, this method returns the least string y such that x is not a prefix of y.
* This is useful to implement prefix filtering by comparison, since the only strings z that
* have x as a prefix are such that z is greater than or equal to x and z is less than y.
*
* @param str A non-empty string. The contract above is not honored for an empty input string,
* since all strings have the empty string as a prefix.
*/
private static String nextString(String str) {
int len = str.length();
if (len == 0) {
return str;
}
// The last code point in the string. Within the Basic Multilingual Plane,
// this is the same as str.charAt(len-1)
int codePoint = str.codePointBefore(len);
// This should be safe from overflow, since the largest code point
// representable in UTF-16 is U+10FFFF.
int nextCodePoint = codePoint + 1;
// The index of the start of the last code point.
// Character.charCount(codePoint) is always 1 (in the BMP) or 2
int lastIndex = len - Character.charCount(codePoint);
return new StringBuilder(len)
.append(str, 0, lastIndex) // append everything but the last code point
.appendCodePoint(nextCodePoint) // instead of the last code point, use successor
.toString();
}
/**
* Returns the source ranking for sources with a minimum number of clicks.
*
* @param minClicks The minimum number of clicks a source must have.
* @return The list of sources, ranked by total clicks.
*/
Map<String,Integer> getCorpusScores(int minClicks) {
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
final Cursor cursor = db.rawQuery(
SOURCE_RANKING_SQL, new String[] { String.valueOf(minClicks) });
try {
Map<String,Integer> corpora = new HashMap<String,Integer>(cursor.getCount());
while (cursor.moveToNext()) {
String name = cursor.getString(SourceStats.corpus.ordinal());
int clicks = cursor.getInt(SourceStats.total_clicks.ordinal());
corpora.put(name, clicks);
}
return corpora;
} finally {
cursor.close();
}
}
private ContentValues makeShortcutRow(Suggestion suggestion) {
String intentAction = suggestion.getSuggestionIntentAction();
String intentComponent = componentNameToString(suggestion.getSuggestionIntentComponent());
String intentData = suggestion.getSuggestionIntentDataString();
String intentQuery = suggestion.getSuggestionQuery();
String intentExtraData = suggestion.getSuggestionIntentExtraData();
Source source = suggestion.getSuggestionSource();
String sourceName = source.getName();
String intentKey = makeIntentKey(suggestion);
// Get URIs for all icons, to make sure that they are stable
String icon1Uri = getIconUriString(source, suggestion.getSuggestionIcon1());
String icon2Uri = getIconUriString(source, suggestion.getSuggestionIcon2());
String extrasJson = null;
SuggestionExtras extras = suggestion.getExtras();
if (extras != null) {
// flatten any custom columns to JSON. We need to keep any custom columns so that
// shortcuts for custom suggestion views work properly.
try {
extrasJson = extras.toJsonString();
} catch (JSONException e) {
Log.e(TAG, "Could not flatten extras to JSON from " + suggestion, e);
}
}
ContentValues cv = new ContentValues();
cv.put(Shortcuts.intent_key.name(), intentKey);
cv.put(Shortcuts.source.name(), sourceName);
cv.put(Shortcuts.source_version_code.name(), source.getVersionCode());
cv.put(Shortcuts.format.name(), suggestion.getSuggestionFormat());
cv.put(Shortcuts.title.name(), suggestion.getSuggestionText1());
cv.put(Shortcuts.description.name(), suggestion.getSuggestionText2());
cv.put(Shortcuts.description_url.name(), suggestion.getSuggestionText2Url());
cv.put(Shortcuts.icon1.name(), icon1Uri);
cv.put(Shortcuts.icon2.name(), icon2Uri);
cv.put(Shortcuts.intent_action.name(), intentAction);
cv.put(Shortcuts.intent_component.name(), intentComponent);
cv.put(Shortcuts.intent_data.name(), intentData);
cv.put(Shortcuts.intent_query.name(), intentQuery);
cv.put(Shortcuts.intent_extradata.name(), intentExtraData);
cv.put(Shortcuts.shortcut_id.name(), suggestion.getShortcutId());
if (suggestion.isSpinnerWhileRefreshing()) {
cv.put(Shortcuts.spinner_while_refreshing.name(), "true");
}
cv.put(Shortcuts.log_type.name(), suggestion.getSuggestionLogType());
cv.put(Shortcuts.custom_columns.name(), extrasJson);
return cv;
}
/**
* Makes a string of the form source#intentData#intentAction#intentQuery
* for use as a unique identifier of a suggestion.
* */
private String makeIntentKey(Suggestion suggestion) {
String intentAction = suggestion.getSuggestionIntentAction();
String intentComponent = componentNameToString(suggestion.getSuggestionIntentComponent());
String intentData = suggestion.getSuggestionIntentDataString();
String intentQuery = suggestion.getSuggestionQuery();
Source source = suggestion.getSuggestionSource();
String sourceName = source.getName();
StringBuilder key = new StringBuilder(sourceName);
key.append("#");
if (intentData != null) {
key.append(intentData);
}
key.append("#");
if (intentAction != null) {
key.append(intentAction);
}
key.append("#");
if (intentComponent != null) {
key.append(intentComponent);
}
key.append("#");
if (intentQuery != null) {
key.append(intentQuery);
}
return key.toString();
}
private String componentNameToString(ComponentName component) {
return component == null ? null : component.flattenToShortString();
}
private ComponentName stringToComponentName(String str) {
return str == null ? null : ComponentName.unflattenFromString(str);
}
private String getIconUriString(Source source, String drawableId) {
// Fast path for empty icons
if (TextUtils.isEmpty(drawableId) || "0".equals(drawableId)) {
return null;
}
// Fast path for icon URIs
if (drawableId.startsWith(ContentResolver.SCHEME_ANDROID_RESOURCE)
|| drawableId.startsWith(ContentResolver.SCHEME_CONTENT)
|| drawableId.startsWith(ContentResolver.SCHEME_FILE)) {
return drawableId;
}
Uri uri = source.getIconUri(drawableId);
return uri == null ? null : uri.toString();
}
@VisibleForTesting
void reportClickAtTime(SuggestionCursor suggestion,
int position, long now) {
suggestion.moveTo(position);
if (DBG) {
Log.d(TAG, "logClicked(" + suggestion + ")");
}
if (SearchManager.SUGGEST_NEVER_MAKE_SHORTCUT.equals(suggestion.getShortcutId())) {
if (DBG) Log.d(TAG, "clicked suggestion requested not to be shortcuted");
return;
}
Corpus corpus = mCorpora.getCorpusForSource(suggestion.getSuggestionSource());
if (corpus == null) {
Log.w(TAG, "no corpus for clicked suggestion");
return;
}
// Once the user has clicked on a shortcut, don't bother refreshing
// (especially if this is a new shortcut)
mRefresher.markShortcutRefreshed(suggestion.getSuggestionSource(),
suggestion.getShortcutId());
// Add or update suggestion info
// Since intent_key is the primary key, any existing
// suggestion with the same source+data+action will be replaced
final ContentValues shortcut = makeShortcutRow(suggestion);
String intentKey = shortcut.getAsString(Shortcuts.intent_key.name());
// Log click for shortcut
final ContentValues click = new ContentValues();
click.put(ClickLog.intent_key.name(), intentKey);
click.put(ClickLog.query.name(), suggestion.getUserQuery());
click.put(ClickLog.hit_time.name(), now);
click.put(ClickLog.corpus.name(), corpus.getName());
runTransactionAsync(new SQLiteTransaction() {
@Override
protected boolean performTransaction(SQLiteDatabase db) {
if (DBG) Log.d(TAG, "Adding shortcut: " + shortcut);
db.replaceOrThrow(Shortcuts.TABLE_NAME, null, shortcut);
db.insertOrThrow(ClickLog.TABLE_NAME, null, click);
return true;
}
});
}
// -------------------------- TABLES --------------------------
/**
* shortcuts table
*/
enum Shortcuts {
intent_key,
source,
source_version_code,
format,
title,
description,
description_url,
icon1,
icon2,
intent_action,
intent_component,
intent_data,
intent_query,
intent_extradata,
shortcut_id,
spinner_while_refreshing,
log_type,
custom_columns;
static final String TABLE_NAME = "shortcuts";
public final String fullName;
Shortcuts() {
fullName = TABLE_NAME + "." + name();
}
}
/**
* clicklog table. Has one record for each click.
*/
enum ClickLog {
_id,
intent_key,
query,
hit_time,
corpus;
static final String[] COLUMNS = initColumns();
static final String TABLE_NAME = "clicklog";
private static String[] initColumns() {
ClickLog[] vals = ClickLog.values();
String[] columns = new String[vals.length];
for (int i = 0; i < vals.length; i++) {
columns[i] = vals[i].fullName;
}
return columns;
}
public final String fullName;
ClickLog() {
fullName = TABLE_NAME + "." + name();
}
}
/**
* This is an aggregate table of {@link ClickLog} that stays up to date with the total
* clicks for each corpus. This makes computing the corpus ranking more
* more efficient, at the expense of some extra work when the clicks are reported.
*/
enum SourceStats {
corpus,
total_clicks;
static final String TABLE_NAME = "sourcetotals";
static final String[] COLUMNS = initColumns();
private static String[] initColumns() {
SourceStats[] vals = SourceStats.values();
String[] columns = new String[vals.length];
for (int i = 0; i < vals.length; i++) {
columns[i] = vals[i].fullName;
}
return columns;
}
public final String fullName;
SourceStats() {
fullName = TABLE_NAME + "." + name();
}
}
// -------------------------- END TABLES --------------------------
// contains creation and update logic
private static class DbOpenHelper extends SQLiteOpenHelper {
private final Config mConfig;
private String mPath;
private static final String SHORTCUT_ID_INDEX
= Shortcuts.TABLE_NAME + "_" + Shortcuts.shortcut_id.name();
private static final String CLICKLOG_QUERY_INDEX
= ClickLog.TABLE_NAME + "_" + ClickLog.query.name();
private static final String CLICKLOG_HIT_TIME_INDEX
= ClickLog.TABLE_NAME + "_" + ClickLog.hit_time.name();
private static final String CLICKLOG_INSERT_TRIGGER
= ClickLog.TABLE_NAME + "_insert";
private static final String SHORTCUTS_DELETE_TRIGGER
= Shortcuts.TABLE_NAME + "_delete";
private static final String SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER
= Shortcuts.TABLE_NAME + "_update_intent_key";
public DbOpenHelper(Context context, String name, int version, Config config) {
super(context, name, null, version);
mConfig = config;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// The shortcuts info is not all that important, so we just drop the tables
// and re-create empty ones.
Log.i(TAG, "Upgrading shortcuts DB from version " +
+ oldVersion + " to " + newVersion + ". This deletes all shortcuts.");
dropTables(db);
onCreate(db);
}
private void dropTables(SQLiteDatabase db) {
db.execSQL("DROP TRIGGER IF EXISTS " + CLICKLOG_INSERT_TRIGGER);
db.execSQL("DROP TRIGGER IF EXISTS " + SHORTCUTS_DELETE_TRIGGER);
db.execSQL("DROP TRIGGER IF EXISTS " + SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER);
db.execSQL("DROP INDEX IF EXISTS " + CLICKLOG_HIT_TIME_INDEX);
db.execSQL("DROP INDEX IF EXISTS " + CLICKLOG_QUERY_INDEX);
db.execSQL("DROP INDEX IF EXISTS " + SHORTCUT_ID_INDEX);
db.execSQL("DROP TABLE IF EXISTS " + ClickLog.TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + Shortcuts.TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + SourceStats.TABLE_NAME);
}
/**
* Deletes the database file.
*/
public void deleteDatabase() {
close();
if (mPath == null) return;
try {
new File(mPath).delete();
if (DBG) Log.d(TAG, "deleted " + mPath);
} catch (Exception e) {
Log.w(TAG, "couldn't delete " + mPath, e);
}
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
mPath = db.getPath();
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + Shortcuts.TABLE_NAME + " (" +
// COLLATE UNICODE is needed to make it possible to use nextString()
// to implement fast prefix filtering.
Shortcuts.intent_key.name() + " TEXT NOT NULL COLLATE UNICODE PRIMARY KEY, " +
Shortcuts.source.name() + " TEXT NOT NULL, " +
Shortcuts.source_version_code.name() + " INTEGER NOT NULL, " +
Shortcuts.format.name() + " TEXT, " +
Shortcuts.title.name() + " TEXT, " +
Shortcuts.description.name() + " TEXT, " +
Shortcuts.description_url.name() + " TEXT, " +
Shortcuts.icon1.name() + " TEXT, " +
Shortcuts.icon2.name() + " TEXT, " +
Shortcuts.intent_action.name() + " TEXT, " +
Shortcuts.intent_component.name() + " TEXT, " +
Shortcuts.intent_data.name() + " TEXT, " +
Shortcuts.intent_query.name() + " TEXT, " +
Shortcuts.intent_extradata.name() + " TEXT, " +
Shortcuts.shortcut_id.name() + " TEXT, " +
Shortcuts.spinner_while_refreshing.name() + " TEXT, " +
Shortcuts.log_type.name() + " TEXT, " +
Shortcuts.custom_columns.name() + " TEXT" +
");");
// index for fast lookup of shortcuts by shortcut_id
db.execSQL("CREATE INDEX " + SHORTCUT_ID_INDEX
+ " ON " + Shortcuts.TABLE_NAME
+ "(" + Shortcuts.shortcut_id.name() + ", " + Shortcuts.source.name() + ")");
db.execSQL("CREATE TABLE " + ClickLog.TABLE_NAME + " ( " +
ClickLog._id.name() + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
// type must match Shortcuts.intent_key
ClickLog.intent_key.name() + " TEXT NOT NULL COLLATE UNICODE REFERENCES "
+ Shortcuts.TABLE_NAME + "(" + Shortcuts.intent_key + "), " +
ClickLog.query.name() + " TEXT, " +
ClickLog.hit_time.name() + " INTEGER," +
ClickLog.corpus.name() + " TEXT" +
");");
// index for fast lookup of clicks by query
db.execSQL("CREATE INDEX " + CLICKLOG_QUERY_INDEX
+ " ON " + ClickLog.TABLE_NAME + "(" + ClickLog.query.name() + ")");
// index for finding old clicks quickly
db.execSQL("CREATE INDEX " + CLICKLOG_HIT_TIME_INDEX
+ " ON " + ClickLog.TABLE_NAME + "(" + ClickLog.hit_time.name() + ")");
// trigger for purging old clicks, i.e. those such that
// hit_time < now - MAX_MAX_STAT_AGE_MILLIS, where now is the
// hit_time of the inserted record, and for updating the SourceStats table
db.execSQL("CREATE TRIGGER " + CLICKLOG_INSERT_TRIGGER + " AFTER INSERT ON "
+ ClickLog.TABLE_NAME
+ " BEGIN"
+ " DELETE FROM " + ClickLog.TABLE_NAME + " WHERE "
+ ClickLog.hit_time.name() + " <"
+ " NEW." + ClickLog.hit_time.name()
+ " - " + mConfig.getMaxStatAgeMillis() + ";"
+ " DELETE FROM " + SourceStats.TABLE_NAME + ";"
+ " INSERT INTO " + SourceStats.TABLE_NAME + " "
+ "SELECT " + ClickLog.corpus + "," + "COUNT(*) FROM "
+ ClickLog.TABLE_NAME + " GROUP BY " + ClickLog.corpus.name() + ";"
+ " END");
// trigger for deleting clicks about a shortcut once that shortcut has been
// deleted
db.execSQL("CREATE TRIGGER " + SHORTCUTS_DELETE_TRIGGER + " AFTER DELETE ON "
+ Shortcuts.TABLE_NAME
+ " BEGIN"
+ " DELETE FROM " + ClickLog.TABLE_NAME + " WHERE "
+ ClickLog.intent_key.name()
+ " = OLD." + Shortcuts.intent_key.name() + ";"
+ " END");
// trigger for updating click log entries when a shortcut changes its intent_key
db.execSQL("CREATE TRIGGER " + SHORTCUTS_UPDATE_INTENT_KEY_TRIGGER
+ " AFTER UPDATE ON " + Shortcuts.TABLE_NAME
+ " WHEN NEW." + Shortcuts.intent_key.name()
+ " != OLD." + Shortcuts.intent_key.name()
+ " BEGIN"
+ " UPDATE " + ClickLog.TABLE_NAME + " SET "
+ ClickLog.intent_key.name() + " = NEW." + Shortcuts.intent_key.name()
+ " WHERE "
+ ClickLog.intent_key.name() + " = OLD." + Shortcuts.intent_key.name()
+ ";"
+ " END");
db.execSQL("CREATE TABLE " + SourceStats.TABLE_NAME + " ( " +
SourceStats.corpus.name() + " TEXT NOT NULL COLLATE UNICODE PRIMARY KEY, " +
SourceStats.total_clicks + " INTEGER);"
);
}
}
}