1 files changed, 84 insertions, 0 deletions
diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderSearchTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderSearchTable.java
new file mode 100644
index 000000000..77c54cf98
--- /dev/null
+++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderSearchTable.java
@@ -0,0 +1,84 @@
+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);
+    }
+}