| package org.wordpress.android.datasets; |
| |
| import android.database.Cursor; |
| import android.database.sqlite.SQLiteDatabase; |
| import android.database.sqlite.SQLiteStatement; |
| import android.support.annotation.NonNull; |
| import android.text.TextUtils; |
| |
| import org.wordpress.android.util.DateTimeUtils; |
| import org.wordpress.android.util.SqlUtils; |
| |
| import java.util.Date; |
| |
| /** |
| * search suggestion table - populated by user's reader search history |
| */ |
| public class ReaderSearchTable { |
| |
| public static final String COL_ID = "_id"; |
| public static final String COL_QUERY = "query_string"; |
| |
| protected static void createTables(SQLiteDatabase db) { |
| db.execSQL("CREATE TABLE tbl_search_suggestions (" |
| + " _id INTEGER PRIMARY KEY AUTOINCREMENT," |
| + " query_string TEXT NOT NULL COLLATE NOCASE," |
| + " date_used TEXT)"); |
| db.execSQL("CREATE UNIQUE INDEX idx_search_suggestions_query ON tbl_search_suggestions(query_string)"); |
| } |
| |
| protected static void dropTables(SQLiteDatabase db) { |
| db.execSQL("DROP TABLE IF EXISTS tbl_search_suggestions"); |
| } |
| |
| /* |
| * adds the passed query string, updating the usage date |
| */ |
| public static void addOrUpdateQueryString(@NonNull String query) { |
| String date = DateTimeUtils.iso8601FromDate(new Date()); |
| |
| SQLiteStatement stmt = ReaderDatabase.getWritableDb().compileStatement( |
| "INSERT OR REPLACE INTO tbl_search_suggestions (query_string, date_used) VALUES (?1,?2)"); |
| try { |
| stmt.bindString(1, query); |
| stmt.bindString(2, date); |
| stmt.execute(); |
| } finally { |
| SqlUtils.closeStatement(stmt); |
| } |
| } |
| |
| public static void deleteQueryString(@NonNull String query) { |
| String[]args = new String[]{query}; |
| ReaderDatabase.getWritableDb().delete("tbl_search_suggestions", "query_string=?", args); |
| } |
| |
| public static void deleteAllQueries() { |
| SqlUtils.deleteAllRowsInTable(ReaderDatabase.getWritableDb(), "tbl_search_suggestions"); |
| } |
| |
| /** |
| * Returns a cursor containing query strings previously typed by the user |
| * @param filter - filters the list using LIKE syntax (pass null for no filter) |
| * @param max - limit the list to this many items (pass zero for no limit) |
| */ |
| public static Cursor getQueryStringCursor(String filter, int max) { |
| String sql; |
| String[] args; |
| if (TextUtils.isEmpty(filter)) { |
| sql = "SELECT * FROM tbl_search_suggestions"; |
| args = null; |
| } else { |
| sql = "SELECT * FROM tbl_search_suggestions WHERE query_string LIKE ?"; |
| args = new String[]{filter + "%"}; |
| } |
| |
| sql += " ORDER BY date_used DESC"; |
| |
| if (max > 0) { |
| sql += " LIMIT " + max; |
| } |
| |
| return ReaderDatabase.getReadableDb().rawQuery(sql, args); |
| } |
| } |