/*
* 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 void runQueryAsync(final SQLiteAsyncQuery query, final Consumer consumer) {
mLogExecutor.execute(new Runnable() {
public void run() {
query.run(mOpenHelper.getReadableDatabase(), consumer);
}
});
}
// --------------------- Interface ShortcutRepository ---------------------
public void hasHistory(Consumer consumer) {
runQueryAsync(new SQLiteAsyncQuery() {
@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 allowedCorpora,
final boolean allowWebSearchShortcuts, final Consumer 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