diff options
Diffstat (limited to 'WordPress/src/main/java/org/wordpress/android/datasets')
15 files changed, 4102 insertions, 0 deletions
diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/AccountTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/AccountTable.java new file mode 100644 index 000000000..c66776c36 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/AccountTable.java @@ -0,0 +1,124 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; + +import org.wordpress.android.WordPress; +import org.wordpress.android.models.Account; +import org.wordpress.android.util.DateTimeUtils; +import org.wordpress.android.util.SqlUtils; + +public class AccountTable { + // Warning: the "accounts" table in WordPressDB is actually where blogs are stored. + private static final String ACCOUNT_TABLE = "tbl_accounts"; + + private static SQLiteDatabase getReadableDb() { + return WordPress.wpDB.getDatabase(); + } + private static SQLiteDatabase getWritableDb() { + return WordPress.wpDB.getDatabase(); + } + + public static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE " + ACCOUNT_TABLE + " (" + + "local_id INTEGER PRIMARY KEY DEFAULT 0," + + "user_name TEXT," + + "user_id INTEGER DEFAULT 0," + + "display_name TEXT," + + "profile_url TEXT," + + "avatar_url TEXT," + + "primary_blog_id INTEGER DEFAULT 0," + + "site_count INTEGER DEFAULT 0," + + "visible_site_count INTEGER DEFAULT 0," + + "access_token TEXT)"); + } + + public static void migrationAddEmailAddressField(SQLiteDatabase db) { + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD email TEXT DEFAULT '';"); + } + + public static void migrationAddFirstNameLastNameAboutMeFields(SQLiteDatabase db) { + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD first_name TEXT DEFAULT '';"); + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD last_name TEXT DEFAULT '';"); + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD about_me TEXT DEFAULT '';"); + } + + public static void migrationAddDateFields(SQLiteDatabase db) { + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD date TEXT DEFAULT '';"); + } + + public static void migrationAddAccountSettingsFields(SQLiteDatabase db) { + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD new_email TEXT DEFAULT '';"); + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD pending_email_change BOOLEAN DEFAULT false;"); + db.execSQL("ALTER TABLE " + ACCOUNT_TABLE + " ADD web_address TEXT DEFAULT '';"); + } + + private static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE); + } + + public static void save(Account account) { + save(account, getWritableDb()); + } + + public static void save(Account account, SQLiteDatabase database) { + ContentValues values = new ContentValues(); + // we only support one wpcom user at the moment: local_id is always 0 + values.put("local_id", 0); + values.put("user_name", account.getUserName()); + values.put("user_id", account.getUserId()); + values.put("display_name", account.getDisplayName()); + values.put("profile_url", account.getProfileUrl()); + values.put("avatar_url", account.getAvatarUrl()); + values.put("primary_blog_id", account.getPrimaryBlogId()); + values.put("site_count", account.getSiteCount()); + values.put("visible_site_count", account.getVisibleSiteCount()); + values.put("access_token", account.getAccessToken()); + values.put("email", account.getEmail()); + values.put("first_name", account.getFirstName()); + values.put("last_name", account.getLastName()); + values.put("about_me", account.getAboutMe()); + values.put("date", DateTimeUtils.iso8601FromDate(account.getDateCreated())); + values.put("new_email", account.getNewEmail()); + values.put("pending_email_change", account.getPendingEmailChange()); + values.put("web_address", account.getWebAddress()); + database.insertWithOnConflict(ACCOUNT_TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE); + } + + public static Account getDefaultAccount() { + return getAccountByLocalId(0); + } + + private static Account getAccountByLocalId(long localId) { + Account account = new Account(); + + String[] args = {Long.toString(localId)}; + Cursor c = getReadableDb().rawQuery("SELECT * FROM " + ACCOUNT_TABLE + " WHERE local_id=?", args); + + try { + if (c.moveToFirst()) { + account.setUserName(c.getString(c.getColumnIndex("user_name"))); + account.setUserId(c.getLong(c.getColumnIndex("user_id"))); + account.setDisplayName(c.getString(c.getColumnIndex("display_name"))); + account.setProfileUrl(c.getString(c.getColumnIndex("profile_url"))); + account.setAvatarUrl(c.getString(c.getColumnIndex("avatar_url"))); + account.setPrimaryBlogId(c.getLong(c.getColumnIndex("primary_blog_id"))); + account.setSiteCount(c.getInt(c.getColumnIndex("site_count"))); + account.setVisibleSiteCount(c.getInt(c.getColumnIndex("visible_site_count"))); + account.setAccessToken(c.getString(c.getColumnIndex("access_token"))); + account.setEmail(c.getString(c.getColumnIndex("email"))); + account.setFirstName(c.getString(c.getColumnIndex("first_name"))); + account.setLastName(c.getString(c.getColumnIndex("last_name"))); + account.setAboutMe(c.getString(c.getColumnIndex("about_me"))); + account.setDateCreated(DateTimeUtils.dateFromIso8601(c.getString(c.getColumnIndex("date")))); + account.setNewEmail(c.getString(c.getColumnIndex("new_email"))); + account.setPendingEmailChange(c.getInt(c.getColumnIndex("pending_email_change")) > 0); + account.setWebAddress(c.getString(c.getColumnIndex("web_address"))); + } + return account; + } finally { + SqlUtils.closeCursor(c); + } + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/CommentTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/CommentTable.java new file mode 100644 index 000000000..9545ec10f --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/CommentTable.java @@ -0,0 +1,419 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteException; +import android.database.sqlite.SQLiteStatement; + +import org.wordpress.android.WordPress; +import org.wordpress.android.WordPressDB; +import org.wordpress.android.models.Comment; +import org.wordpress.android.models.CommentList; +import org.wordpress.android.models.CommentStatus; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.SqlUtils; +import org.wordpress.android.util.StringUtils; + +/** + * replaces the comments table used in versions prior to 2.6.1, which didn't use a primary key + * and missed a few important fields + */ +public class CommentTable { + public static final String COMMENTS_TABLE = "comments"; + + public static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE IF NOT EXISTS " + COMMENTS_TABLE + " (" + + " blog_id INTEGER DEFAULT 0," + + " post_id INTEGER DEFAULT 0," + + " comment_id INTEGER DEFAULT 0," + + " comment TEXT," + + " published TEXT," + + " status TEXT," + + " author_name TEXT," + + " author_url TEXT," + + " author_email TEXT," + + " post_title TEXT," + + " profile_image_url TEXT," + + " PRIMARY KEY (blog_id, post_id, comment_id)" + + " );"); + } + + private static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS " + COMMENTS_TABLE); + } + + public static void reset(SQLiteDatabase db) { + AppLog.i(AppLog.T.COMMENTS, "resetting comment table"); + dropTables(db); + createTables(db); + } + + private static SQLiteDatabase getReadableDb() { + return WordPress.wpDB.getDatabase(); + } + private static SQLiteDatabase getWritableDb() { + return WordPress.wpDB.getDatabase(); + } + + /* + * purge comments attached to blogs that no longer exist, and remove older comments + * TODO: call after hiding or deleting blogs + */ + private static final int MAX_COMMENTS = 1000; + public static int purge(SQLiteDatabase db) { + int numDeleted = 0; + + // get rid of comments on blogs that don't exist or are hidden + String sql = " blog_id NOT IN (SELECT DISTINCT id FROM " + WordPressDB.BLOGS_TABLE + + " WHERE isHidden = 0)"; + numDeleted += db.delete(COMMENTS_TABLE, sql, null); + + // get rid of older comments if we've reached the max + int numExisting = (int)SqlUtils.getRowCount(db, COMMENTS_TABLE); + if (numExisting > MAX_COMMENTS) { + int numToPurge = numExisting - MAX_COMMENTS; + sql = " comment_id IN (SELECT DISTINCT comment_id FROM " + COMMENTS_TABLE + + " ORDER BY published LIMIT " + Integer.toString(numToPurge) + ")"; + numDeleted += db.delete(COMMENTS_TABLE, sql, null); + } + + return numDeleted; + } + + /** + * add a single comment - will update existing comment with same IDs + * @param localBlogId - unique id in account table for the blog the comment is from + * @param comment - comment object to store + */ + public static void addComment(int localBlogId, final Comment comment) { + if (comment == null) + return; + + ContentValues values = new ContentValues(); + values.put("blog_id", localBlogId); + values.put("post_id", comment.postID); + values.put("comment_id", comment.commentID); + values.put("author_name", comment.getAuthorName()); + values.put("author_url", comment.getAuthorUrl()); + values.put("comment", SqlUtils.maxSQLiteText(comment.getCommentText())); + values.put("status", comment.getStatus()); + values.put("author_email", comment.getAuthorEmail()); + values.put("post_title", comment.getPostTitle()); + values.put("published", comment.getPublished()); + values.put("profile_image_url", comment.getProfileImageUrl()); + + getWritableDb().insertWithOnConflict(COMMENTS_TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE); + } + + /** + * retrieve a single comment + * @param localBlogId - unique id in account table for the blog the comment is from + * @param commentId - commentId of the actual comment + * @return Comment if found, null otherwise + */ + public static Comment getComment(int localBlogId, long commentId) { + String[] args = {Integer.toString(localBlogId), Long.toString(commentId)}; + Cursor c = getReadableDb().rawQuery("SELECT * FROM " + COMMENTS_TABLE + " WHERE blog_id=? AND comment_id=?", args); + try { + if (!c.moveToFirst()) { + return null; + } + return getCommentFromCursor(c); + } finally { + SqlUtils.closeCursor(c); + } + } + + /** + * get all comments for a blog + * @param localBlogId - unique id in account table for this blog + * @return list of comments for this blog + */ + public static CommentList getCommentsForBlog(int localBlogId) { + CommentList comments = new CommentList(); + + String[] args = {Integer.toString(localBlogId)}; + Cursor c = getReadableDb().rawQuery( + "SELECT * FROM " + COMMENTS_TABLE + " WHERE blog_id=? ORDER BY published DESC", args); + + try { + while (c.moveToNext()) { + Comment comment = getCommentFromCursor(c); + comments.add(comment); + } + + return comments; + } finally { + SqlUtils.closeCursor(c); + } + } + + /** + * get comments for a blog that have a specific status + * @param localBlogId - unique id in account table for this blog + * @param filter - status to filter comments by + * @return list of comments for this blog + */ + public static CommentList getCommentsForBlogWithFilter(int localBlogId, CommentStatus filter) { + CommentList comments = new CommentList(); + Cursor c; + + //aggregating 'all' to include approved and unapproved comments + if (CommentStatus.UNKNOWN.equals(filter)){ + //we need to get the filter values for both XMLrpc and REST api as in the case of a migration where existing + // data is present on a device, we still need to be able to filter both values + String[] args = {Integer.toString(localBlogId), + CommentStatus.toString(CommentStatus.APPROVED), + CommentStatus.toString(CommentStatus.UNAPPROVED), + CommentStatus.toRESTString(CommentStatus.APPROVED), + CommentStatus.toRESTString(CommentStatus.UNAPPROVED)}; + c = getReadableDb().rawQuery( + "SELECT * FROM " + COMMENTS_TABLE + " WHERE blog_id=? AND (status=? OR status=? OR status=? OR status=?) ORDER BY published DESC", args); + + } else { + //we need to get the filter values for both XMLrpc and REST api as in the case of a migration where existing + // data is present on a device, we still need to be able to filter both values + String[] args = {Integer.toString(localBlogId), CommentStatus.toString(filter), CommentStatus.toRESTString(filter)}; + c = getReadableDb().rawQuery( + "SELECT * FROM " + COMMENTS_TABLE + " WHERE blog_id=? AND (status=? OR status=?) ORDER BY published DESC", args); + } + + try { + while (c.moveToNext()) { + Comment comment = getCommentFromCursor(c); + comments.add(comment); + } + + return comments; + } finally { + SqlUtils.closeCursor(c); + } + } + + /** + * delete all comments for a blog + * @param localBlogId - unique id in account table for this blog + * @return number of comments deleted + */ + public static int deleteCommentsForBlog(int localBlogId) { + return getWritableDb().delete(COMMENTS_TABLE, "blog_id=?", new String[]{Integer.toString(localBlogId)}); + } + + /** + * delete comments for a blog that match a specific status + * @param localBlogId - unique id in account table for this blog + * @param filter - status to use to filter the query + * @return number of comments deleted + */ + public static int deleteCommentsForBlogWithFilter(int localBlogId, CommentStatus filter) { + if (CommentStatus.UNKNOWN.equals(filter)){ + //we need to get the filter values for both XMLrpc and REST api as in the case of a migration where existing + // data is present on a device, we still need to be able to filter both values + String[] args = {Integer.toString(localBlogId), + CommentStatus.toString(CommentStatus.APPROVED), + CommentStatus.toString(CommentStatus.UNAPPROVED), + CommentStatus.toRESTString(CommentStatus.APPROVED), + CommentStatus.toRESTString(CommentStatus.UNAPPROVED)}; + return getWritableDb().delete(COMMENTS_TABLE, "blog_id=? AND (status=? OR status=? OR status=? OR status=?)", args); + + } else { + //we need to get the filter values for both XMLrpc and REST api as in the case of a migration where existing + // data is present on a device, we still need to be able to filter both values + String[] args = {Integer.toString(localBlogId), CommentStatus.toString(filter), CommentStatus.toRESTString(filter)}; + return getWritableDb().delete(COMMENTS_TABLE, "blog_id=? AND (status=? OR status=?)", args); + } + } + + /** + * saves comments for passed blog to local db, overwriting existing ones if necessary + * @param localBlogId - unique id in account table for this blog + * @param comments - list of comments to save + * @return true if saved, false on failure + */ + public static boolean saveComments(int localBlogId, final CommentList comments) { + if (comments == null || comments.size() == 0) + return false; + + final String sql = " INSERT OR REPLACE INTO " + COMMENTS_TABLE + "(" + + " blog_id," // 1 + + " post_id," // 2 + + " comment_id," // 3 + + " comment," // 4 + + " published," // 5 + + " status," // 6 + + " author_name," // 7 + + " author_url," // 8 + + " author_email," // 9 + + " post_title," // 10 + + " profile_image_url" // 11 + + " ) VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11)"; + + SQLiteDatabase db = getWritableDb(); + SQLiteStatement stmt = db.compileStatement(sql); + db.beginTransaction(); + try { + try { + for (Comment comment: comments) { + stmt.bindLong ( 1, localBlogId); + stmt.bindLong ( 2, comment.postID); + stmt.bindLong ( 3, comment.commentID); + stmt.bindString( 4, SqlUtils.maxSQLiteText(comment.getCommentText())); + stmt.bindString( 5, comment.getPublished()); + stmt.bindString( 6, comment.getStatus()); + stmt.bindString( 7, comment.getAuthorName()); + stmt.bindString( 8, comment.getAuthorUrl()); + stmt.bindString( 9, comment.getAuthorEmail()); + stmt.bindString(10, comment.getPostTitle()); + stmt.bindString(11, comment.getProfileImageUrl()); + stmt.execute(); + } + + db.setTransactionSuccessful(); + return true; + } catch (SQLiteException e) { + AppLog.e(AppLog.T.COMMENTS, e); + return false; + } + } finally { + db.endTransaction(); + SqlUtils.closeStatement(stmt); + } + } + + /** + * updates the passed comment + * @param localBlogId - unique id in account table for this blog + * @param comment - comment to update + */ + public static void updateComment(int localBlogId, final Comment comment) { + // this will replace the existing comment + addComment(localBlogId, comment); + } + + /** + * updates the status for the passed comment + * @param localBlogId - unique id in account table for this blog + * @param commentId - id of comment (returned by api) + * @param newStatus - status to change to + */ + public static void updateCommentStatus(int localBlogId, long commentId, String newStatus) { + ContentValues values = new ContentValues(); + values.put("status", newStatus); + String[] args = {Integer.toString(localBlogId), + Long.toString(commentId)}; + getWritableDb().update(COMMENTS_TABLE, values, "blog_id=? AND comment_id=?", args); + } + + /** + * updates the status for the passed list of comments + * @param localBlogId - unique id in account table for this blog + * @param comments - list of comments to update + * @param newStatus - status to change to + */ + public static void updateCommentsStatus(int localBlogId, final CommentList comments, String newStatus) { + if (comments == null || comments.size() == 0) + return; + getWritableDb().beginTransaction(); + try { + for (Comment comment: comments) { + updateCommentStatus(localBlogId, comment.commentID, newStatus); + } + getWritableDb().setTransactionSuccessful(); + } finally { + getWritableDb().endTransaction(); + } + } + + /** + * updates the post title for the passed comment + * @param localBlogId - unique id in account table for this blog + * @param postTitle - title to update to + * @return true if title updated + */ + public static boolean updateCommentPostTitle(int localBlogId, long commentId, String postTitle) { + ContentValues values = new ContentValues(); + values.put("post_title", StringUtils.notNullStr(postTitle)); + String[] args = {Integer.toString(localBlogId), + Long.toString(commentId)}; + int count = getWritableDb().update(COMMENTS_TABLE, values, "blog_id=? AND comment_id=?", args); + return (count > 0); + } + + /** + * delete a single comment + * @param localBlogId - unique id in account table for this blog + * @param commentId - commentId of the actual comment + * @return true if comment deleted, false otherwise + */ + public static boolean deleteComment(int localBlogId, long commentId) { + String[] args = {Integer.toString(localBlogId), + Long.toString(commentId)}; + int count = getWritableDb().delete(COMMENTS_TABLE, "blog_id=? AND comment_id=?", args); + return (count > 0); + } + + /** + * delete a list of comments + * @param localBlogId - unique id in account table for this blog + * @param comments - list of comments to delete + */ + public static void deleteComments(int localBlogId, final CommentList comments) { + if (comments == null || comments.size() == 0) + return; + getWritableDb().beginTransaction(); + try { + for (Comment comment: comments) { + deleteComment(localBlogId, comment.commentID); + } + getWritableDb().setTransactionSuccessful(); + } finally { + getWritableDb().endTransaction(); + } + } + + /** + * returns the number of unmoderated comments for a specific blog + * @param localBlogId - unique id in account table for this blog + */ + public static int getUnmoderatedCommentCount(int localBlogId) { + String sql = "SELECT COUNT(*) FROM " + COMMENTS_TABLE + " WHERE blog_id=? AND status=?"; + String[] args = {Integer.toString(localBlogId), "hold"}; + return SqlUtils.intForQuery(getReadableDb(), sql, args); + } + + private static Comment getCommentFromCursor(Cursor c) { + final String authorName = c.getString(c.getColumnIndex("author_name")); + final String content = c.getString(c.getColumnIndex("comment")); + final String published = c.getString(c.getColumnIndex("published")); + final String status = c.getString(c.getColumnIndex("status")); + final String authorUrl = c.getString(c.getColumnIndex("author_url")); + final String authorEmail = c.getString(c.getColumnIndex("author_email")); + final String postTitle = c.getString(c.getColumnIndex("post_title")); + final String profileImageUrl = c.getString(c.getColumnIndex("profile_image_url")); + + int postId = c.getInt(c.getColumnIndex("post_id")); + int commentId = c.getInt(c.getColumnIndex("comment_id")); + + return new Comment( + postId, + commentId, + authorName, + published, + content, + status, + postTitle, + authorUrl, + authorEmail, + profileImageUrl); + } + + + /** + * Delete big comments (Maximum 512 * 1024 = 524288) (fix #2855) + * @return number of deleted comments + */ + public static int deleteBigComments(SQLiteDatabase db) { + return db.delete(COMMENTS_TABLE, "LENGTH(comment) >= 524288", null); + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/PeopleTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/PeopleTable.java new file mode 100644 index 000000000..84ac02dba --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/PeopleTable.java @@ -0,0 +1,354 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteQueryBuilder; +import android.support.annotation.Nullable; + +import org.wordpress.android.WordPress; +import org.wordpress.android.models.Person; +import org.wordpress.android.models.Role; +import org.wordpress.android.ui.people.utils.PeopleUtils; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.SqlUtils; + +import java.util.ArrayList; +import java.util.List; + +public class PeopleTable { + private static final String TEAM_TABLE = "people_team"; + private static final String FOLLOWERS_TABLE = "people_followers"; + private static final String EMAIL_FOLLOWERS_TABLE = "people_email_followers"; + private static final String VIEWERS_TABLE = "people_viewers"; + + private static SQLiteDatabase getReadableDb() { + return WordPress.wpDB.getDatabase(); + } + private static SQLiteDatabase getWritableDb() { + return WordPress.wpDB.getDatabase(); + } + + public static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE " + TEAM_TABLE + " (" + + "person_id INTEGER DEFAULT 0," + + "local_blog_id INTEGER DEFAULT 0," + + "user_name TEXT," + + "display_name TEXT," + + "avatar_url TEXT," + + "role TEXT," + + "PRIMARY KEY (person_id, local_blog_id)" + + ");"); + + db.execSQL("CREATE TABLE " + FOLLOWERS_TABLE + " (" + + "person_id INTEGER DEFAULT 0," + + "local_blog_id INTEGER DEFAULT 0," + + "user_name TEXT," + + "display_name TEXT," + + "avatar_url TEXT," + + "subscribed TEXT," + + "PRIMARY KEY (person_id, local_blog_id)" + + ");"); + + db.execSQL("CREATE TABLE " + EMAIL_FOLLOWERS_TABLE + " (" + + "person_id INTEGER DEFAULT 0," + + "local_blog_id INTEGER DEFAULT 0," + + "display_name TEXT," + + "avatar_url TEXT," + + "subscribed TEXT," + + "PRIMARY KEY (person_id, local_blog_id)" + + ");"); + } + + public static void createViewersTable(SQLiteDatabase db) { + db.execSQL("CREATE TABLE " + VIEWERS_TABLE + " (" + + "person_id INTEGER DEFAULT 0," + + "local_blog_id INTEGER DEFAULT 0," + + "user_name TEXT," + + "display_name TEXT," + + "avatar_url TEXT," + + "PRIMARY KEY (person_id, local_blog_id)" + + ");"); + } + + private static void dropTables(SQLiteDatabase db) { + // People table is not used anymore, each filter now has it's own table + db.execSQL("DROP TABLE IF EXISTS people"); + + db.execSQL("DROP TABLE IF EXISTS " + TEAM_TABLE); + db.execSQL("DROP TABLE IF EXISTS " + FOLLOWERS_TABLE); + db.execSQL("DROP TABLE IF EXISTS " + EMAIL_FOLLOWERS_TABLE); + db.execSQL("DROP TABLE IF EXISTS " + VIEWERS_TABLE); + } + + public static void reset(SQLiteDatabase db) { + AppLog.i(AppLog.T.PEOPLE, "resetting people table"); + dropTables(db); + createTables(db); + } + + public static void saveUser(Person person) { + save(TEAM_TABLE, person, getWritableDb()); + } + + private static void save(String table, Person person, SQLiteDatabase database) { + ContentValues values = new ContentValues(); + values.put("person_id", person.getPersonID()); + values.put("local_blog_id", person.getLocalTableBlogId()); + values.put("display_name", person.getDisplayName()); + values.put("avatar_url", person.getAvatarUrl()); + + switch (table) { + case TEAM_TABLE: + values.put("user_name", person.getUsername()); + if (person.getRole() != null) { + values.put("role", person.getRole().toString()); + } + break; + case FOLLOWERS_TABLE: + values.put("user_name", person.getUsername()); + values.put("subscribed", person.getSubscribed()); + break; + case EMAIL_FOLLOWERS_TABLE: + values.put("subscribed", person.getSubscribed()); + break; + case VIEWERS_TABLE: + values.put("user_name", person.getUsername()); + break; + } + + database.insertWithOnConflict(table, null, values, SQLiteDatabase.CONFLICT_REPLACE); + } + + public static void saveUsers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) { + savePeople(TEAM_TABLE, peopleList, localTableBlogId, isFreshList); + } + + public static void saveFollowers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) { + savePeople(FOLLOWERS_TABLE, peopleList, localTableBlogId, isFreshList); + } + + public static void saveEmailFollowers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) { + savePeople(EMAIL_FOLLOWERS_TABLE, peopleList, localTableBlogId, isFreshList); + } + + public static void saveViewers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) { + savePeople(VIEWERS_TABLE, peopleList, localTableBlogId, isFreshList); + } + + private static void savePeople(String table, List<Person> peopleList, int localTableBlogId, boolean isFreshList) { + getWritableDb().beginTransaction(); + try { + // We have a fresh list, remove the previous list of people in case it was deleted on remote + if (isFreshList) { + PeopleTable.deletePeople(table, localTableBlogId); + } + + for (Person person : peopleList) { + PeopleTable.save(table, person, getWritableDb()); + } + getWritableDb().setTransactionSuccessful(); + } finally { + getWritableDb().endTransaction(); + } + } + + public static void deletePeopleForLocalBlogId(int localTableBlogId) { + deletePeople(TEAM_TABLE, localTableBlogId); + deletePeople(FOLLOWERS_TABLE, localTableBlogId); + deletePeople(EMAIL_FOLLOWERS_TABLE, localTableBlogId); + deletePeople(VIEWERS_TABLE, localTableBlogId); + } + + private static void deletePeople(String table, int localTableBlogId) { + String[] args = new String[]{Integer.toString(localTableBlogId)}; + getWritableDb().delete(table, "local_blog_id=?1", args); + } + + /** + * In order to avoid syncing issues, this method will be called when People page is created. We only keep + * the first page of users, so we don't show an empty screen. When fresh data is received, it'll replace + * the existing page. + * @param localTableBlogId - the local blog id people will be deleted from + */ + public static void deletePeopleExceptForFirstPage(int localTableBlogId) { + int fetchLimit = PeopleUtils.FETCH_LIMIT; + String[] tables = {TEAM_TABLE, FOLLOWERS_TABLE, EMAIL_FOLLOWERS_TABLE, VIEWERS_TABLE}; + + getWritableDb().beginTransaction(); + try { + for (String table : tables) { + int size = getPeopleCountForLocalBlogId(table, localTableBlogId); + if (size > fetchLimit) { + String where = "local_blog_id=" + localTableBlogId; + String[] columns = {"person_id"}; + String limit = Integer.toString(size - fetchLimit); + String orderBy; + if (shouldOrderAlphabetically(table)) { + orderBy = "lower(display_name) DESC, lower(user_name) DESC"; + } else { + orderBy = "ROWID DESC"; + } + String inQuery = SQLiteQueryBuilder.buildQueryString(false, table, columns, where, null, null, + orderBy, limit); + + String[] args = new String[] {Integer.toString(localTableBlogId)}; + getWritableDb().delete(table, "local_blog_id=?1 AND person_id IN (" + inQuery + ")", args); + } + } + getWritableDb().setTransactionSuccessful(); + } finally { + getWritableDb().endTransaction(); + } + } + + public static int getUsersCountForLocalBlogId(int localTableBlogId) { + return getPeopleCountForLocalBlogId(TEAM_TABLE, localTableBlogId); + } + + public static int getViewersCountForLocalBlogId(int localTableBlogId) { + return getPeopleCountForLocalBlogId(VIEWERS_TABLE, localTableBlogId); + } + + private static int getPeopleCountForLocalBlogId(String table, int localTableBlogId) { + String[] args = new String[]{Integer.toString(localTableBlogId)}; + String sql = "SELECT COUNT(*) FROM " + table + " WHERE local_blog_id=?"; + return SqlUtils.intForQuery(getReadableDb(), sql, args); + } + + public static void deletePerson(long personID, int localTableBlogId, Person.PersonType personType) { + String table = getTableForPersonType(personType); + if (table != null) { + deletePerson(table, personID, localTableBlogId); + } + } + + private static void deletePerson(String table, long personID, int localTableBlogId) { + String[] args = new String[]{Long.toString(personID), Integer.toString(localTableBlogId)}; + getWritableDb().delete(table, "person_id=? AND local_blog_id=?", args); + } + + public static List<Person> getUsers(int localTableBlogId) { + return PeopleTable.getPeople(TEAM_TABLE, localTableBlogId); + } + + public static List<Person> getFollowers(int localTableBlogId) { + return PeopleTable.getPeople(FOLLOWERS_TABLE, localTableBlogId); + } + + public static List<Person> getEmailFollowers(int localTableBlogId) { + return PeopleTable.getPeople(EMAIL_FOLLOWERS_TABLE, localTableBlogId); + } + + public static List<Person> getViewers(int localTableBlogId) { + return PeopleTable.getPeople(VIEWERS_TABLE, localTableBlogId); + } + + private static List<Person> getPeople(String table, int localTableBlogId) { + String[] args = {Integer.toString(localTableBlogId)}; + String orderBy; + if (shouldOrderAlphabetically(table)) { + orderBy = " ORDER BY lower(display_name), lower(user_name)"; + } else { + // we want the server-side order for followers & viewers + orderBy = " ORDER BY ROWID"; + } + Cursor c = getReadableDb().rawQuery("SELECT * FROM " + table + " WHERE local_blog_id=?" + orderBy, args); + + List<Person> people = new ArrayList<>(); + try { + while (c.moveToNext()) { + Person person = getPersonFromCursor(c, table, localTableBlogId); + people.add(person); + } + } finally { + SqlUtils.closeCursor(c); + } + return people; + } + + @Nullable + public static Person getPerson(long personId, int localTableBlogId, Person.PersonType personType) { + String table = getTableForPersonType(personType); + if (table != null) { + return getPerson(table, personId, localTableBlogId); + } + return null; + } + + public static Person getUser(long personId, int localTableBlogId) { + return getPerson(TEAM_TABLE, personId, localTableBlogId); + } + + /** + * retrieve a person + * @param table - sql table the person record is in + * @param personId - id of a person in a particular blog + * @param localTableBlogId - the local blog id the user belongs to + * @return Person if found, null otherwise + */ + private static Person getPerson(String table, long personId, int localTableBlogId) { + String[] args = { Long.toString(personId), Integer.toString(localTableBlogId)}; + Cursor c = getReadableDb().rawQuery("SELECT * FROM " + table + + " WHERE person_id=? AND local_blog_id=?", args); + try { + if (!c.moveToFirst()) { + return null; + } + return getPersonFromCursor(c, table, localTableBlogId); + } finally { + SqlUtils.closeCursor(c); + } + } + + private static Person getPersonFromCursor(Cursor c, String table, int localTableBlogId) { + long personId = c.getInt(c.getColumnIndex("person_id")); + + Person person = new Person(personId, localTableBlogId); + person.setDisplayName(c.getString(c.getColumnIndex("display_name"))); + person.setAvatarUrl(c.getString(c.getColumnIndex("avatar_url"))); + switch (table) { + case TEAM_TABLE: + person.setUsername(c.getString(c.getColumnIndex("user_name"))); + String role = c.getString(c.getColumnIndex("role")); + person.setRole(Role.fromString(role)); + person.setPersonType(Person.PersonType.USER); + break; + case FOLLOWERS_TABLE: + person.setUsername(c.getString(c.getColumnIndex("user_name"))); + person.setSubscribed(c.getString(c.getColumnIndex("subscribed"))); + person.setPersonType(Person.PersonType.FOLLOWER); + break; + case EMAIL_FOLLOWERS_TABLE: + person.setSubscribed(c.getString(c.getColumnIndex("subscribed"))); + person.setPersonType(Person.PersonType.EMAIL_FOLLOWER); + break; + case VIEWERS_TABLE: + person.setUsername(c.getString(c.getColumnIndex("user_name"))); + person.setPersonType(Person.PersonType.VIEWER); + break; + } + + return person; + } + + // order is disabled for followers & viewers for now since the API is not supporting it + private static boolean shouldOrderAlphabetically(String table) { + return table.equals(TEAM_TABLE); + } + + @Nullable + private static String getTableForPersonType(Person.PersonType personType) { + switch (personType) { + case USER: + return TEAM_TABLE; + case FOLLOWER: + return FOLLOWERS_TABLE; + case EMAIL_FOLLOWER: + return EMAIL_FOLLOWERS_TABLE; + case VIEWER: + return VIEWERS_TABLE; + } + return null; + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderBlogTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderBlogTable.java new file mode 100644 index 000000000..591b628c3 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderBlogTable.java @@ -0,0 +1,382 @@ +package org.wordpress.android.datasets; + +import android.database.Cursor; +import android.database.SQLException; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; +import android.text.TextUtils; + +import org.wordpress.android.models.ReaderBlog; +import org.wordpress.android.models.ReaderBlogList; +import org.wordpress.android.models.ReaderRecommendBlogList; +import org.wordpress.android.models.ReaderRecommendedBlog; +import org.wordpress.android.models.ReaderUrlList; +import org.wordpress.android.ui.reader.ReaderConstants; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.DateTimeUtils; +import org.wordpress.android.util.SqlUtils; +import org.wordpress.android.util.UrlUtils; + +import java.util.Date; + +/** + * tbl_blog_info contains information about blogs viewed in the reader, and blogs the + * user is following. Note that this table is populated from two endpoints: + * + * 1. sites/{$siteId} + * 2. read/following/mine?meta=site,feed + * + * The first endpoint is called when the user views blog preview, the second is called + * to get the full list of blogs the user is following + */ +public class ReaderBlogTable { + + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_blog_info (" + + " blog_id INTEGER DEFAULT 0," // will be same as feedId for feeds + + " feed_id INTEGER DEFAULT 0," // will be 0 for blogs + + " blog_url TEXT NOT NULL COLLATE NOCASE," + + " image_url TEXT," + + " feed_url TEXT," + + " name TEXT," + + " description TEXT," + + " is_private INTEGER DEFAULT 0," + + " is_jetpack INTEGER DEFAULT 0," + + " is_following INTEGER DEFAULT 0," + + " num_followers INTEGER DEFAULT 0," + + " date_updated TEXT," + + " PRIMARY KEY (blog_id)" + + ")"); + + db.execSQL("CREATE TABLE tbl_recommended_blogs (" + + " blog_id INTEGER DEFAULT 0," + + " follow_reco_id INTEGER DEFAULT 0," + + " score INTEGER DEFAULT 0," + + " title TEXT COLLATE NOCASE," + + " blog_url TEXT COLLATE NOCASE," + + " image_url TEXT," + + " reason TEXT," + + " PRIMARY KEY (blog_id)" + + ")"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_blog_info"); + db.execSQL("DROP TABLE IF EXISTS tbl_recommended_blogs"); + } + + public static ReaderBlog getBlogInfo(long blogId) { + if (blogId == 0) { + return null; + } + String[] args = {Long.toString(blogId)}; + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_blog_info WHERE blog_id=?", args); + try { + if (!cursor.moveToFirst()) { + return null; + } + return getBlogInfoFromCursor(cursor); + } finally { + SqlUtils.closeCursor(cursor); + } + } + + public static ReaderBlog getFeedInfo(long feedId) { + if (feedId == 0) { + return null; + } + String[] args = {Long.toString(feedId)}; + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_blog_info WHERE feed_id=?", args); + try { + if (!cursor.moveToFirst()) { + return null; + } + return getBlogInfoFromCursor(cursor); + } finally { + SqlUtils.closeCursor(cursor); + } + } + + public static long getFeedIdFromUrl(String url) { + if (TextUtils.isEmpty(url)) { + return 0; + } + String[] args = {UrlUtils.normalizeUrl(url)}; + return SqlUtils.longForQuery(ReaderDatabase.getReadableDb(), + "SELECT feed_id FROM tbl_blog_info WHERE feed_url=?", + args); + } + + private static ReaderBlog getBlogInfoFromCursor(Cursor c) { + if (c == null) { + return null; + } + + ReaderBlog blogInfo = new ReaderBlog(); + blogInfo.blogId = c.getLong(c.getColumnIndex("blog_id")); + blogInfo.feedId = c.getLong(c.getColumnIndex("feed_id")); + blogInfo.setUrl(c.getString(c.getColumnIndex("blog_url"))); + blogInfo.setImageUrl(c.getString(c.getColumnIndex("image_url"))); + blogInfo.setFeedUrl(c.getString(c.getColumnIndex("feed_url"))); + blogInfo.setName(c.getString(c.getColumnIndex("name"))); + blogInfo.setDescription(c.getString(c.getColumnIndex("description"))); + blogInfo.isPrivate = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_private"))); + blogInfo.isJetpack = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_jetpack"))); + blogInfo.isFollowing = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_following"))); + blogInfo.numSubscribers = c.getInt(c.getColumnIndex("num_followers")); + + return blogInfo; + } + + public static void addOrUpdateBlog(ReaderBlog blogInfo) { + if (blogInfo == null) { + return; + } + String sql = "INSERT OR REPLACE INTO tbl_blog_info" + + " (blog_id, feed_id, blog_url, image_url, feed_url, name, description, is_private, is_jetpack, is_following, num_followers, date_updated)" + + " VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)"; + SQLiteStatement stmt = ReaderDatabase.getWritableDb().compileStatement(sql); + try { + stmt.bindLong (1, blogInfo.blogId); + stmt.bindLong (2, blogInfo.feedId); + stmt.bindString(3, blogInfo.getUrl()); + stmt.bindString(4, blogInfo.getImageUrl()); + stmt.bindString(5, blogInfo.getFeedUrl()); + stmt.bindString(6, blogInfo.getName()); + stmt.bindString(7, blogInfo.getDescription()); + stmt.bindLong (8, SqlUtils.boolToSql(blogInfo.isPrivate)); + stmt.bindLong (9, SqlUtils.boolToSql(blogInfo.isJetpack)); + stmt.bindLong (10, SqlUtils.boolToSql(blogInfo.isFollowing)); + stmt.bindLong (11, blogInfo.numSubscribers); + stmt.bindString(12, DateTimeUtils.iso8601FromDate(new Date())); + stmt.execute(); + } finally { + SqlUtils.closeStatement(stmt); + } + } + + /* + * returns blogInfo for all followed blogs + */ + public static ReaderBlogList getFollowedBlogs() { + Cursor c = ReaderDatabase.getReadableDb().rawQuery( + "SELECT * FROM tbl_blog_info WHERE is_following!=0 ORDER BY name COLLATE NOCASE, blog_url", + null); + try { + ReaderBlogList blogs = new ReaderBlogList(); + if (c.moveToFirst()) { + do { + ReaderBlog blogInfo = getBlogInfoFromCursor(c); + blogs.add(blogInfo); + } while (c.moveToNext()); + } + return blogs; + } finally { + SqlUtils.closeCursor(c); + } + } + + /* + * set followed blogs from the read/following/mine endpoint + */ + public static void setFollowedBlogs(ReaderBlogList followedBlogs) { + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + try { + // first set all existing blogs to not followed + db.execSQL("UPDATE tbl_blog_info SET is_following=0"); + + // then insert passed ones + if (followedBlogs != null) { + for (ReaderBlog blog: followedBlogs) { + addOrUpdateBlog(blog); + } + } + + db.setTransactionSuccessful(); + + } finally { + db.endTransaction(); + } + } + + /* + * return list of URLs of followed blogs + */ + public static ReaderUrlList getFollowedBlogUrls() { + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT DISTINCT blog_url FROM tbl_blog_info WHERE is_following!=0", null); + try { + ReaderUrlList urls = new ReaderUrlList(); + if (c.moveToFirst()) { + do { + urls.add(c.getString(0)); + } while (c.moveToNext()); + } + return urls; + } finally { + SqlUtils.closeCursor(c); + } + } + + /* + * sets the follow state for passed blog without creating a record for it if it doesn't exist + */ + public static void setIsFollowedBlogId(long blogId, boolean isFollowed) { + ReaderDatabase.getWritableDb().execSQL( + "UPDATE tbl_blog_info SET is_following=" + + SqlUtils.boolToSql(isFollowed) + + " WHERE blog_id=?", + new String[]{Long.toString(blogId)}); + } + + public static void setIsFollowedFeedId(long feedId, boolean isFollowed) { + ReaderDatabase.getWritableDb().execSQL( + "UPDATE tbl_blog_info SET is_following=" + + SqlUtils.boolToSql(isFollowed) + + " WHERE feed_id=?", + new String[]{Long.toString(feedId)}); + } + + public static boolean hasFollowedBlogs() { + String sql = "SELECT 1 FROM tbl_blog_info WHERE is_following!=0 LIMIT 1"; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), sql, null); + } + + public static boolean isFollowedBlogUrl(String blogUrl) { + if (TextUtils.isEmpty(blogUrl)) { + return false; + } + String sql = "SELECT 1 FROM tbl_blog_info WHERE is_following!=0 AND blog_url=?"; + String[] args = {UrlUtils.normalizeUrl(blogUrl)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + public static boolean isFollowedBlog(long blogId) { + String sql = "SELECT 1 FROM tbl_blog_info WHERE is_following!=0 AND blog_id=?"; + String[] args = {Long.toString(blogId)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + public static boolean isFollowedFeedUrl(String feedUrl) { + if (TextUtils.isEmpty(feedUrl)) { + return false; + } + String sql = "SELECT 1 FROM tbl_blog_info WHERE is_following!=0 AND feed_url=?"; + String[] args = {UrlUtils.normalizeUrl(feedUrl)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + public static boolean isFollowedFeed(long feedId) { + String sql = "SELECT 1 FROM tbl_blog_info WHERE is_following!=0 AND feed_id=?"; + String[] args = {Long.toString(feedId)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + public static ReaderRecommendBlogList getRecommendedBlogs() { + String sql = " SELECT * FROM tbl_recommended_blogs ORDER BY title"; + Cursor c = ReaderDatabase.getReadableDb().rawQuery(sql, null); + try { + ReaderRecommendBlogList blogs = new ReaderRecommendBlogList(); + if (c.moveToFirst()) { + do { + ReaderRecommendedBlog blog = new ReaderRecommendedBlog(); + blog.blogId = c.getLong(c.getColumnIndex("blog_id")); + blog.followRecoId = c.getLong(c.getColumnIndex("follow_reco_id")); + blog.score = c.getInt(c.getColumnIndex("score")); + blog.setTitle(c.getString(c.getColumnIndex("title"))); + blog.setBlogUrl(c.getString(c.getColumnIndex("blog_url"))); + blog.setImageUrl(c.getString(c.getColumnIndex("image_url"))); + blog.setReason(c.getString(c.getColumnIndex("reason"))); + blogs.add(blog); + } while (c.moveToNext()); + } + return blogs; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static void setRecommendedBlogs(ReaderRecommendBlogList blogs) { + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + SQLiteStatement stmt = db.compileStatement( + "INSERT INTO tbl_recommended_blogs" + + " (blog_id, follow_reco_id, score, title, blog_url, image_url, reason)" + + " VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)"); + db.beginTransaction(); + try { + try { + // first delete all recommended blogs + SqlUtils.deleteAllRowsInTable(db, "tbl_recommended_blogs"); + + // then insert the passed ones + if (blogs != null && blogs.size() > 0) { + for (ReaderRecommendedBlog blog : blogs) { + stmt.bindLong (1, blog.blogId); + stmt.bindLong (2, blog.followRecoId); + stmt.bindLong (3, blog.score); + stmt.bindString(4, blog.getTitle()); + stmt.bindString(5, blog.getBlogUrl()); + stmt.bindString(6, blog.getImageUrl()); + stmt.bindString(7, blog.getReason()); + stmt.execute(); + } + } + db.setTransactionSuccessful(); + + } catch (SQLException e) { + AppLog.e(AppLog.T.READER, e); + } + } finally { + SqlUtils.closeStatement(stmt); + db.endTransaction(); + } + } + + /* + * determine whether the passed blog info should be updated based on when it was last updated + */ + public static boolean isTimeToUpdateBlogInfo(ReaderBlog blogInfo) { + int minutes = minutesSinceLastUpdate(blogInfo); + if (minutes == NEVER_UPDATED) { + return true; + } + return (minutes >= ReaderConstants.READER_AUTO_UPDATE_DELAY_MINUTES); + } + + private static String getBlogInfoLastUpdated(ReaderBlog blogInfo) { + if (blogInfo == null) { + return ""; + } + if (blogInfo.blogId != 0) { + String[] args = {Long.toString(blogInfo.blogId)}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), + "SELECT date_updated FROM tbl_blog_info WHERE blog_id=?", + args); + } else { + String[] args = {Long.toString(blogInfo.feedId)}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), + "SELECT date_updated FROM tbl_blog_info WHERE feed_id=?", + args); + } + } + + private static final int NEVER_UPDATED = -1; + private static int minutesSinceLastUpdate(ReaderBlog blogInfo) { + if (blogInfo == null) { + return 0; + } + + String updated = getBlogInfoLastUpdated(blogInfo); + if (TextUtils.isEmpty(updated)) { + return NEVER_UPDATED; + } + + Date dtUpdated = DateTimeUtils.dateFromIso8601(updated); + if (dtUpdated == null) { + return 0; + } + + Date dtNow = new Date(); + return DateTimeUtils.minutesBetween(dtUpdated, dtNow); + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderCommentTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderCommentTable.java new file mode 100644 index 000000000..dc61bbb3a --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderCommentTable.java @@ -0,0 +1,336 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; + +import org.wordpress.android.models.ReaderComment; +import org.wordpress.android.models.ReaderCommentList; +import org.wordpress.android.models.ReaderPost; +import org.wordpress.android.util.SqlUtils; + +/** + * stores comments on reader posts + */ +public class ReaderCommentTable { + private static final String COLUMN_NAMES = + " blog_id," + + " post_id," + + " comment_id," + + " parent_id," + + " author_name," + + " author_avatar," + + " author_url," + + " author_id," + + " author_blog_id," + + " published," + + " timestamp," + + " status," + + " text," + + " num_likes," + + " is_liked," + + " page_number"; + + + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_comments (" + + " blog_id INTEGER DEFAULT 0," + + " post_id INTEGER DEFAULT 0," + + " comment_id INTEGER DEFAULT 0," + + " parent_id INTEGER DEFAULT 0," + + " author_name TEXT," + + " author_avatar TEXT," + + " author_url TEXT," + + " author_id INTEGER DEFAULT 0," + + " author_blog_id INTEGER DEFAULT 0," + + " published TEXT," + + " timestamp INTEGER DEFAULT 0," + + " status TEXT," + + " text TEXT," + + " num_likes INTEGER DEFAULT 0," + + " is_liked INTEGER DEFAULT 0," + + " page_number INTEGER DEFAULT 0," + + " PRIMARY KEY (blog_id, post_id, comment_id))"); + db.execSQL("CREATE INDEX idx_page_number ON tbl_comments(page_number)"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_comments"); + } + + protected static void reset(SQLiteDatabase db) { + dropTables(db); + createTables(db); + } + + protected static int purge(SQLiteDatabase db) { + // purge comments attached to posts that no longer exist + int numDeleted = db.delete("tbl_comments", "post_id NOT IN (SELECT DISTINCT post_id FROM tbl_posts)", null); + + // purge all but the first page of comments + numDeleted += db.delete("tbl_comments", "page_number != 1", null); + + return numDeleted; + } + + public static boolean isEmpty() { + return (getNumComments()==0); + } + + private static int getNumComments() { + long count = SqlUtils.getRowCount(ReaderDatabase.getReadableDb(), "tbl_comments"); + return (int)count; + } + + /* + * returns the highest page_number for comments on the passed post + */ + public static int getLastPageNumberForPost(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT MAX(page_number) FROM tbl_comments WHERE blog_id=? AND post_id=?", args); + } + + /* + * returns the page number for a specific comment + */ + public static int getPageNumberForComment(long blogId, long postId, long commentId) { + String[] args = {Long.toString(blogId), Long.toString(postId), Long.toString(commentId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT page_number FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=?", args); + } + + /* + * removes all comments for the passed post + */ + public static void purgeCommentsForPost(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + ReaderDatabase.getWritableDb().delete("tbl_comments", "blog_id=? AND post_id=?", args); + } + + /* + * returns the #comments stored locally for this post, which may differ from ReaderPostTable.getNumCommentsOnPost + * (which is the #comments the server says exist for this post) + */ + public static int getNumCommentsForPost(ReaderPost post) { + if (post == null) { + return 0; + } + return getNumCommentsForPost(post.blogId, post.postId); + } + private static int getNumCommentsForPost(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), "SELECT count(*) FROM tbl_comments WHERE blog_id=? AND post_id=?", args); + } + + public static ReaderCommentList getCommentsForPost(ReaderPost post) { + if (post == null) { + return new ReaderCommentList(); + } + + String[] args = {Long.toString(post.blogId), Long.toString(post.postId)}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_comments WHERE blog_id=? AND post_id=? ORDER BY timestamp", args); + try { + ReaderCommentList comments = new ReaderCommentList(); + if (c.moveToFirst()) { + do { + comments.add(getCommentFromCursor(c)); + } while (c.moveToNext()); + } + return comments; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static void addOrUpdateComment(ReaderComment comment) { + if (comment == null) { + return; + } + ReaderCommentList comments = new ReaderCommentList(); + comments.add(comment); + addOrUpdateComments(comments); + } + + public static void addOrUpdateComments(ReaderCommentList comments) { + if (comments == null || comments.size() == 0) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + SQLiteStatement stmt = db.compileStatement("INSERT OR REPLACE INTO tbl_comments (" + + COLUMN_NAMES + + ") VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16)"); + try { + for (ReaderComment comment: comments) { + stmt.bindLong (1, comment.blogId); + stmt.bindLong (2, comment.postId); + stmt.bindLong (3, comment.commentId); + stmt.bindLong (4, comment.parentId); + stmt.bindString(5, comment.getAuthorName()); + stmt.bindString(6, comment.getAuthorAvatar()); + stmt.bindString(7, comment.getAuthorUrl()); + stmt.bindLong (8, comment.authorId); + stmt.bindLong (9, comment.authorBlogId); + stmt.bindString(10, comment.getPublished()); + stmt.bindLong (11, comment.timestamp); + stmt.bindString(12, comment.getStatus()); + stmt.bindString(13, comment.getText()); + stmt.bindLong (14, comment.numLikes); + stmt.bindLong (15, SqlUtils.boolToSql(comment.isLikedByCurrentUser)); + stmt.bindLong (16, comment.pageNumber); + + stmt.execute(); + } + + db.setTransactionSuccessful(); + + } finally { + db.endTransaction(); + SqlUtils.closeStatement(stmt); + } + } + + public static ReaderComment getComment(long blogId, long postId, long commentId) { + String[] args = new String[] {Long.toString(blogId), Long.toString(postId), Long.toString(commentId)}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery( + "SELECT * FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=? LIMIT 1", args); + try { + if (!c.moveToFirst()) { + return null; + } + return getCommentFromCursor(c); + } finally { + SqlUtils.closeCursor(c); + } + } + + public static void deleteComment(ReaderPost post, long commentId) { + if (post == null) { + return; + } + String[] args = {Long.toString(post.blogId), Long.toString(post.postId), Long.toString(commentId)}; + ReaderDatabase.getWritableDb().delete("tbl_comments", "blog_id=? AND post_id=? AND comment_id=?", args); + } + + /* + * returns true if any of the passed comments don't already exist + * IMPORTANT: assumes passed comments are all for the same post + */ + public static boolean hasNewComments(ReaderCommentList comments) { + if (comments == null || comments.size() == 0) { + return false; + } + + StringBuilder sb = new StringBuilder( + "SELECT COUNT(*) FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id IN ("); + boolean isFirst = true; + for (ReaderComment comment: comments) { + if (isFirst) { + isFirst = false; + } else { + sb.append(","); + } + sb.append(comment.commentId); + } + sb.append(")"); + + String[] args = {Long.toString(comments.get(0).blogId), + Long.toString(comments.get(0).postId)}; + int numExisting = SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), sb.toString(), args); + return numExisting != comments.size(); + } + + /* + * returns the #likes known to exist for this comment + */ + public static int getNumLikesForComment(long blogId, long postId, long commentId) { + String[] args = {Long.toString(blogId), + Long.toString(postId), + Long.toString(commentId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT num_likes FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=?", + args); + } + + /* + * updates both the like count for a comment and whether it's liked by the current user + */ + public static void setLikesForComment(ReaderComment comment, int numLikes, boolean isLikedByCurrentUser) { + if (comment == null) { + return; + } + + String[] args = + {Long.toString(comment.blogId), + Long.toString(comment.postId), + Long.toString(comment.commentId)}; + + ContentValues values = new ContentValues(); + values.put("num_likes", numLikes); + values.put("is_liked", SqlUtils.boolToSql(isLikedByCurrentUser)); + + ReaderDatabase.getWritableDb().update( + "tbl_comments", + values, + "blog_id=? AND post_id=? AND comment_id=?", + args); + } + + public static boolean isCommentLikedByCurrentUser(ReaderComment comment) { + if (comment == null) { + return false; + } + return isCommentLikedByCurrentUser(comment.blogId, comment.postId, comment.commentId); + } + public static boolean isCommentLikedByCurrentUser(long blogId, long postId, long commentId) { + String[] args = {Long.toString(blogId), + Long.toString(postId), + Long.toString(commentId)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT is_liked FROM tbl_comments WHERE blog_id=? AND post_id=? and comment_id=?", + args); + } + + public static boolean commentExists(long blogId, long postId, long commentId) { + String[] args = {Long.toString(blogId), + Long.toString(postId), + Long.toString(commentId)}; + + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT 1 FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=?", args); + } + + private static ReaderComment getCommentFromCursor(Cursor c) { + if (c == null) { + throw new IllegalArgumentException("null comment cursor"); + } + + ReaderComment comment = new ReaderComment(); + + comment.commentId = c.getLong(c.getColumnIndex("comment_id")); + comment.blogId = c.getLong(c.getColumnIndex("blog_id")); + comment.postId = c.getLong(c.getColumnIndex("post_id")); + comment.parentId = c.getLong(c.getColumnIndex("parent_id")); + + comment.setPublished(c.getString(c.getColumnIndex("published"))); + comment.timestamp = c.getLong(c.getColumnIndex("timestamp")); + + comment.setAuthorAvatar(c.getString(c.getColumnIndex("author_avatar"))); + comment.setAuthorName(c.getString(c.getColumnIndex("author_name"))); + comment.setAuthorUrl(c.getString(c.getColumnIndex("author_url"))); + comment.authorId = c.getLong(c.getColumnIndex("author_id")); + comment.authorBlogId = c.getLong(c.getColumnIndex("author_blog_id")); + + comment.setStatus(c.getString(c.getColumnIndex("status"))); + comment.setText(c.getString(c.getColumnIndex("text"))); + + comment.numLikes = c.getInt(c.getColumnIndex("num_likes")); + comment.isLikedByCurrentUser = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_liked"))); + comment.pageNumber = c.getInt(c.getColumnIndex("page_number")); + + return comment; + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderDatabase.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderDatabase.java new file mode 100644 index 000000000..9ae4f5521 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderDatabase.java @@ -0,0 +1,255 @@ +package org.wordpress.android.datasets; + +import android.content.Context; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteOpenHelper; + +import org.wordpress.android.WordPress; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.AppLog.T; + +import java.io.FileInputStream; +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStream; + +/** + * database for all reader information + */ +public class ReaderDatabase extends SQLiteOpenHelper { + protected static final String DB_NAME = "wpreader.db"; + private static final int DB_VERSION = 125; + + /* + * version history + * 67 - added tbl_blog_info to ReaderBlogTable + * 68 - added author_blog_id to ReaderCommentTable + * 69 - renamed tbl_blog_urls to tbl_followed_blogs in ReaderBlogTable + * 70 - added author_id to ReaderCommentTable and ReaderPostTable + * 71 - added blog_id to ReaderUserTable + * 72 - removed tbl_followed_blogs from ReaderBlogTable + * 73 - added tbl_recommended_blogs to ReaderBlogTable + * 74 - added primary_tag to ReaderPostTable + * 75 - added secondary_tag to ReaderPostTable + * 76 - added feed_id to ReaderBlogTable + * 77 - restructured tag tables (ReaderTagTable) + * 78 - added tag_type to ReaderPostTable.tbl_post_tags + * 79 - added is_likes_enabled and is_sharing_enabled to tbl_posts + * 80 - added tbl_comment_likes in ReaderLikeTable, added num_likes to tbl_comments + * 81 - added image_url to tbl_blog_info + * 82 - added idx_posts_timestamp to tbl_posts + * 83 - removed tag_list from tbl_posts + * 84 - added tbl_attachments + * 85 - removed tbl_attachments, added attachments_json to tbl_posts + * 90 - added default values for all INTEGER columns that were missing them (hotfix 3.1.1) + * 92 - added default values for all INTEGER columns that were missing them (3.2) + * 93 - tbl_posts text is now truncated to a max length (3.3) + * 94 - added is_jetpack to tbl_posts (3.4) + * 95 - added page_number to tbl_comments (3.4) + * 96 - removed tbl_tag_updates, added date_updated to tbl_tags (3.4) + * 97 - added short_url to tbl_posts + * 98 - added feed_id to tbl_posts + * 99 - added feed_url to tbl_blog_info + * 100 - changed primary key on tbl_blog_info + * 101 - dropped is_reblogged from ReaderPostTable + * 102 - changed primary key of tbl_blog_info from blog_id+feed_id to just blog_id + * 103 - added discover_json to ReaderPostTable + * 104 - added word_count to ReaderPostTable + * 105 - added date_updated to ReaderBlogTable + * 106 - dropped is_likes_enabled and is_sharing_enabled from tbl_posts + * 107 - "Blogs I Follow" renamed to "Followed Sites" + * 108 - added "has_gap_marker" to tbl_post_tags + * 109 - added "feed_item_id" to tbl_posts + * 110 - added xpost_post_id and xpost_blog_id to tbl_posts + * 111 - added author_first_name to tbl_posts + * 112 - no structural change, just reset db + * 113 - added tag_title to tag tables + * 114 - renamed tag_name to tag_slug in tag tables + * 115 - added ReaderSearchTable + * 116 - added tag_display_name to tag tables + * 117 - changed tbl_posts.timestamp from INTEGER to REAL + * 118 - renamed tbl_search_history to tbl_search_suggestions + * 119 - renamed tbl_posts.timestamp to sort_index + * 120 - added "format" to tbl_posts + * 121 - removed word_count from tbl_posts + * 122 - changed tbl_posts primary key to pseudo_id + * 123 - changed tbl_posts.published to tbl_posts.date + * 124 - returned tbl_posts.published + * 125 - added tbl_posts.railcar_json + */ + + /* + * database singleton + */ + private static ReaderDatabase mReaderDb; + private final static Object mDbLock = new Object(); + public static ReaderDatabase getDatabase() { + if (mReaderDb == null) { + synchronized(mDbLock) { + if (mReaderDb == null) { + mReaderDb = new ReaderDatabase(WordPress.getContext()); + // this ensures that onOpen() is called with a writable database (open will fail if app calls getReadableDb() first) + mReaderDb.getWritableDatabase(); + } + } + } + return mReaderDb; + } + + public static SQLiteDatabase getReadableDb() { + return getDatabase().getReadableDatabase(); + } + public static SQLiteDatabase getWritableDb() { + return getDatabase().getWritableDatabase(); + } + + @Override + public void onOpen(SQLiteDatabase db) { + super.onOpen(db); + //copyDatabase(db); + } + + /* + * resets (clears) the reader database + */ + public static void reset() { + // note that we must call getWritableDb() before getDatabase() in case the database + // object hasn't been created yet + SQLiteDatabase db = getWritableDb(); + getDatabase().reset(db); + } + + public ReaderDatabase(Context context) { + super(context, DB_NAME, null, DB_VERSION); + } + + @Override + public void onCreate(SQLiteDatabase db) { + createAllTables(db); + } + + @Override + public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { + // for now just reset the db when upgrading, future versions may want to avoid this + // and modify table structures, etc., on upgrade while preserving data + AppLog.i(T.READER, "Upgrading database from version " + oldVersion + " to version " + newVersion); + reset(db); + } + + @Override + public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { + // IMPORTANT: do NOT call super() here - doing so throws a SQLiteException + AppLog.w(T.READER, "Downgrading database from version " + oldVersion + " to version " + newVersion); + reset(db); + } + + private void createAllTables(SQLiteDatabase db) { + ReaderCommentTable.createTables(db); + ReaderLikeTable.createTables(db); + ReaderPostTable.createTables(db); + ReaderTagTable.createTables(db); + ReaderUserTable.createTables(db); + ReaderThumbnailTable.createTables(db); + ReaderBlogTable.createTables(db); + ReaderSearchTable.createTables(db); + } + + private void dropAllTables(SQLiteDatabase db) { + ReaderCommentTable.dropTables(db); + ReaderLikeTable.dropTables(db); + ReaderPostTable.dropTables(db); + ReaderTagTable.dropTables(db); + ReaderUserTable.dropTables(db); + ReaderThumbnailTable.dropTables(db); + ReaderBlogTable.dropTables(db); + ReaderSearchTable.dropTables(db); + } + + /* + * drop & recreate all tables (essentially clears the db of all data) + */ + private void reset(SQLiteDatabase db) { + db.beginTransaction(); + try { + dropAllTables(db); + createAllTables(db); + db.setTransactionSuccessful(); + } finally { + db.endTransaction(); + } + } + + /* + * purge older/unattached data - use purgeAsync() to do this in the background + */ + private static void purge() { + SQLiteDatabase db = getWritableDb(); + db.beginTransaction(); + try { + int numPostsDeleted = ReaderPostTable.purge(db); + + // don't bother purging other data unless posts were purged + if (numPostsDeleted > 0) { + AppLog.i(T.READER, String.format("%d total posts purged", numPostsDeleted)); + + // purge unattached comments + int numCommentsDeleted = ReaderCommentTable.purge(db); + if (numCommentsDeleted > 0) { + AppLog.i(T.READER, String.format("%d comments purged", numCommentsDeleted)); + } + + // purge unattached likes + int numLikesDeleted = ReaderLikeTable.purge(db); + if (numLikesDeleted > 0) { + AppLog.i(T.READER, String.format("%d likes purged", numLikesDeleted)); + } + + // purge unattached thumbnails + int numThumbsPurged = ReaderThumbnailTable.purge(db); + if (numThumbsPurged > 0) { + AppLog.i(T.READER, String.format("%d thumbnails purged", numThumbsPurged)); + } + } + db.setTransactionSuccessful(); + } finally { + db.endTransaction(); + } + } + + public static void purgeAsync() { + new Thread() { + @Override + public void run() { + purge(); + } + }.start(); + } + + /* + * used during development to copy database to external storage so we can access it via DDMS + */ + private void copyDatabase(SQLiteDatabase db) { + String copyFrom = db.getPath(); + String copyTo = WordPress.getContext().getExternalFilesDir(null).getAbsolutePath() + "/" + DB_NAME; + + try { + InputStream input = new FileInputStream(copyFrom); + OutputStream output = new FileOutputStream(copyTo); + + byte[] buffer = new byte[1024]; + int length; + while ((length = input.read(buffer)) > 0) { + output.write(buffer, 0, length); + } + + output.flush(); + output.close(); + input.close(); + } catch (IOException e) { + AppLog.e(T.DB, "failed to copy reader database", e); + } + } + + +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderLikeTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderLikeTable.java new file mode 100644 index 000000000..7144bdaab --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderLikeTable.java @@ -0,0 +1,222 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; + +import org.wordpress.android.models.ReaderComment; +import org.wordpress.android.models.ReaderPost; +import org.wordpress.android.models.ReaderUserIdList; +import org.wordpress.android.models.AccountHelper; +import org.wordpress.android.util.SqlUtils; + +/** + * stores likes for Reader posts and comments + */ +public class ReaderLikeTable { + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_post_likes (" + + " post_id INTEGER DEFAULT 0," + + " blog_id INTEGER DEFAULT 0," + + " user_id INTEGER DEFAULT 0," + + " PRIMARY KEY (blog_id, post_id, user_id))"); + + db.execSQL("CREATE TABLE tbl_comment_likes (" + + " comment_id INTEGER DEFAULT 0," + + " blog_id INTEGER DEFAULT 0," + + " user_id INTEGER DEFAULT 0," + + " PRIMARY KEY (blog_id, comment_id, user_id))"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_post_likes"); + db.execSQL("DROP TABLE IF EXISTS tbl_comment_likes"); + } + + protected static void reset(SQLiteDatabase db) { + dropTables(db); + createTables(db); + } + + /* + * purge likes attached to posts/comments that no longer exist + */ + protected static int purge(SQLiteDatabase db) { + int numDeleted = db.delete("tbl_post_likes", "post_id NOT IN (SELECT DISTINCT post_id FROM tbl_posts)", null); + numDeleted += db.delete("tbl_comment_likes", "comment_id NOT IN (SELECT DISTINCT comment_id FROM tbl_comments)", null); + return numDeleted; + } + + /* + * returns userIds of users who like the passed post + */ + public static ReaderUserIdList getLikesForPost(ReaderPost post) { + ReaderUserIdList userIds = new ReaderUserIdList(); + if (post == null) { + return userIds; + } + + String[] args = {Long.toString(post.blogId), Long.toString(post.postId)}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT user_id FROM tbl_post_likes WHERE blog_id=? AND post_id=?", args); + try { + if (c.moveToFirst()) { + do { + userIds.add(c.getLong(0)); + } while (c.moveToNext()); + } + + return userIds; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static int getNumLikesForPost(ReaderPost post) { + if (post == null) { + return 0; + } + String[] args = {Long.toString(post.blogId), Long.toString(post.postId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), "SELECT count(*) FROM tbl_post_likes WHERE blog_id=? AND post_id=?", args); + } + + public static void setCurrentUserLikesPost(ReaderPost post, boolean isLiked) { + if (post == null) { + return; + } + long currentUserId = AccountHelper.getDefaultAccount().getUserId(); + if (isLiked) { + ContentValues values = new ContentValues(); + values.put("blog_id", post.blogId); + values.put("post_id", post.postId); + values.put("user_id", currentUserId); + ReaderDatabase.getWritableDb().insert("tbl_post_likes", null, values); + } else { + String args[] = {Long.toString(post.blogId), Long.toString(post.postId), Long.toString(currentUserId)}; + ReaderDatabase.getWritableDb().delete("tbl_post_likes", "blog_id=? AND post_id=? AND user_id=?", args); + } + } + + public static void setLikesForPost(ReaderPost post, ReaderUserIdList userIds) { + if (post == null) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + SQLiteStatement stmt = db.compileStatement("INSERT INTO tbl_post_likes (blog_id, post_id, user_id) VALUES (?1,?2,?3)"); + try { + // first delete all likes for this post + String[] args = {Long.toString(post.blogId), Long.toString(post.postId)}; + db.delete("tbl_post_likes", "blog_id=? AND post_id=?", args); + + // now insert the passed likes + if (userIds != null) { + stmt.bindLong(1, post.blogId); + stmt.bindLong(2, post.postId); + for (Long userId: userIds) { + stmt.bindLong(3, userId); + stmt.execute(); + } + } + + db.setTransactionSuccessful(); + + } finally { + db.endTransaction(); + SqlUtils.closeStatement(stmt); + } + } + + + /**** + * comment likes + */ + + public static ReaderUserIdList getLikesForComment(ReaderComment comment) { + ReaderUserIdList userIds = new ReaderUserIdList(); + if (comment == null) { + return userIds; + } + + String[] args = {Long.toString(comment.blogId), + Long.toString(comment.commentId)}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery( + "SELECT user_id FROM tbl_comment_likes WHERE blog_id=? AND comment_id=?", args); + try { + if (c.moveToFirst()) { + do { + userIds.add(c.getLong(0)); + } while (c.moveToNext()); + } + + return userIds; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static int getNumLikesForComment(ReaderComment comment) { + if (comment == null) { + return 0; + } + String[] args = {Long.toString(comment.blogId), + Long.toString(comment.commentId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT count(*) FROM tbl_comment_likes WHERE blog_id=? AND comment_id=?", args); + } + + public static void setCurrentUserLikesComment(ReaderComment comment, boolean isLiked) { + if (comment == null) { + return; + } + + long currentUserId = AccountHelper.getDefaultAccount().getUserId(); + if (isLiked) { + ContentValues values = new ContentValues(); + values.put("blog_id", comment.blogId); + values.put("comment_id", comment.commentId); + values.put("user_id", currentUserId); + ReaderDatabase.getWritableDb().insert("tbl_comment_likes", null, values); + } else { + String args[] = {Long.toString(comment.blogId), + Long.toString(comment.commentId), + Long.toString(currentUserId)}; + ReaderDatabase.getWritableDb().delete("tbl_comment_likes", + "blog_id=? AND comment_id=? AND user_id=?", args); + } + } + + public static void setLikesForComment(ReaderComment comment, ReaderUserIdList userIds) { + if (comment == null) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + SQLiteStatement stmt = db.compileStatement( + "INSERT INTO tbl_comment_likes (blog_id, comment_id, user_id) VALUES (?1,?2,?3)"); + try { + String[] args = {Long.toString(comment.blogId), + Long.toString(comment.commentId)}; + db.delete("tbl_comment_likes", "blog_id=? AND comment_id=?", args); + + if (userIds != null) { + stmt.bindLong(1, comment.blogId); + stmt.bindLong(2, comment.commentId); + for (Long userId: userIds) { + stmt.bindLong(3, userId); + stmt.execute(); + } + } + + db.setTransactionSuccessful(); + + } finally { + db.endTransaction(); + SqlUtils.closeStatement(stmt); + } + } +} + + diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderPostTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderPostTable.java new file mode 100644 index 000000000..af9b06891 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderPostTable.java @@ -0,0 +1,933 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; + +import org.wordpress.android.R; +import org.wordpress.android.WordPress; +import org.wordpress.android.models.ReaderPost; +import org.wordpress.android.models.ReaderPostList; +import org.wordpress.android.models.ReaderTag; +import org.wordpress.android.models.ReaderTagList; +import org.wordpress.android.models.ReaderTagType; +import org.wordpress.android.ui.reader.ReaderConstants; +import org.wordpress.android.ui.reader.actions.ReaderActions; +import org.wordpress.android.ui.reader.models.ReaderBlogIdPostId; +import org.wordpress.android.ui.reader.models.ReaderBlogIdPostIdList; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.CrashlyticsUtils; +import org.wordpress.android.util.SqlUtils; + +/** + * tbl_posts contains all reader posts + * tbl_post_tags stores the association between posts and tags (posts can exist in more than one tag) + * + */ +public class ReaderPostTable { + private static final String COLUMN_NAMES = + "post_id," // 1 + + "blog_id," // 2 + + "feed_id," // 3 + + "feed_item_id," // 4 + + "pseudo_id," // 5 + + "author_name," // 6 + + "author_first_name," // 7 + + "author_id," // 8 + + "title," // 9 + + "text," // 10 + + "excerpt," // 11 + + "format," // 12 + + "url," // 13 + + "short_url," // 14 + + "blog_url," // 15 + + "blog_name," // 16 + + "featured_image," // 17 + + "featured_video," // 18 + + "post_avatar," // 19 + + "sort_index," // 20 + + "date," // 21 + + "published," // 22 + + "num_replies," // 23 + + "num_likes," // 24 + + "is_liked," // 25 + + "is_followed," // 26 + + "is_comments_open," // 27 + + "is_external," // 28 + + "is_private," // 29 + + "is_videopress," // 30 + + "is_jetpack," // 31 + + "primary_tag," // 32 + + "secondary_tag," // 33 + + "attachments_json," // 34 + + "discover_json," // 35 + + "xpost_post_id," // 36 + + "xpost_blog_id," // 37 + + "railcar_json"; // 38 + + // used when querying multiple rows and skipping tbl_posts.text + private static final String COLUMN_NAMES_NO_TEXT = + "tbl_posts.post_id," // 1 + + "tbl_posts.blog_id," // 2 + + "tbl_posts.feed_id," // 3 + + "tbl_posts.feed_item_id," // 4 + + "tbl_posts.author_id," // 5 + + "tbl_posts.pseudo_id," // 6 + + "tbl_posts.author_name," // 7 + + "tbl_posts.author_first_name," // 8 + + "tbl_posts.blog_name," // 9 + + "tbl_posts.blog_url," // 10 + + "tbl_posts.excerpt," // 11 + + "tbl_posts.format," // 12 + + "tbl_posts.featured_image," // 13 + + "tbl_posts.featured_video," // 14 + + "tbl_posts.title," // 15 + + "tbl_posts.url," // 16 + + "tbl_posts.short_url," // 17 + + "tbl_posts.post_avatar," // 18 + + "tbl_posts.sort_index," // 19 + + "tbl_posts.date," // 20 + + "tbl_posts.published," // 21 + + "tbl_posts.num_replies," // 22 + + "tbl_posts.num_likes," // 23 + + "tbl_posts.is_liked," // 24 + + "tbl_posts.is_followed," // 25 + + "tbl_posts.is_comments_open," // 26 + + "tbl_posts.is_external," // 27 + + "tbl_posts.is_private," // 28 + + "tbl_posts.is_videopress," // 29 + + "tbl_posts.is_jetpack," // 30 + + "tbl_posts.primary_tag," // 31 + + "tbl_posts.secondary_tag," // 32 + + "tbl_posts.attachments_json," // 33 + + "tbl_posts.discover_json," // 34 + + "tbl_posts.xpost_post_id," // 35 + + "tbl_posts.xpost_blog_id," // 36 + + "tbl_posts.railcar_json"; // 37 + + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_posts (" + + " post_id INTEGER DEFAULT 0," + + " blog_id INTEGER DEFAULT 0," + + " feed_id INTEGER DEFAULT 0," + + " feed_item_id INTEGER DEFAULT 0," + + " pseudo_id TEXT NOT NULL," + + " author_name TEXT," + + " author_first_name TEXT," + + " author_id INTEGER DEFAULT 0," + + " title TEXT," + + " text TEXT," + + " excerpt TEXT," + + " format TEXT," + + " url TEXT," + + " short_url TEXT," + + " blog_url TEXT," + + " blog_name TEXT," + + " featured_image TEXT," + + " featured_video TEXT," + + " post_avatar TEXT," + + " sort_index REAL DEFAULT 0," + + " date TEXT," + + " published TEXT," + + " num_replies INTEGER DEFAULT 0," + + " num_likes INTEGER DEFAULT 0," + + " is_liked INTEGER DEFAULT 0," + + " is_followed INTEGER DEFAULT 0," + + " is_comments_open INTEGER DEFAULT 0," + + " is_external INTEGER DEFAULT 0," + + " is_private INTEGER DEFAULT 0," + + " is_videopress INTEGER DEFAULT 0," + + " is_jetpack INTEGER DEFAULT 0," + + " primary_tag TEXT," + + " secondary_tag TEXT," + + " attachments_json TEXT," + + " discover_json TEXT," + + " xpost_post_id INTEGER DEFAULT 0," + + " xpost_blog_id INTEGER DEFAULT 0," + + " railcar_json TEXT," + + " PRIMARY KEY (pseudo_id)" + + ")"); + + db.execSQL("CREATE UNIQUE INDEX idx_posts_post_id_blog_id ON tbl_posts(post_id, blog_id)"); + db.execSQL("CREATE INDEX idx_posts_sort_index ON tbl_posts(sort_index)"); + + db.execSQL("CREATE TABLE tbl_post_tags (" + + " post_id INTEGER DEFAULT 0," + + " blog_id INTEGER DEFAULT 0," + + " feed_id INTEGER DEFAULT 0," + + " pseudo_id TEXT NOT NULL," + + " tag_name TEXT NOT NULL COLLATE NOCASE," + + " tag_type INTEGER DEFAULT 0," + + " has_gap_marker INTEGER DEFAULT 0," + + " PRIMARY KEY (pseudo_id, tag_name, tag_type)" + + ")"); + + db.execSQL("CREATE INDEX idx_post_tags_tag_name ON tbl_post_tags(tag_name)"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_posts"); + db.execSQL("DROP TABLE IF EXISTS tbl_post_tags"); + } + + protected static void reset(SQLiteDatabase db) { + dropTables(db); + createTables(db); + } + + /* + * purge table of unattached/older posts - no need to wrap this in a transaction since it's + * only called from ReaderDatabase.purge() which already creates a transaction + */ + protected static int purge(SQLiteDatabase db) { + // delete posts in tbl_post_tags attached to tags that no longer exist + int numDeleted = db.delete("tbl_post_tags", "tag_name NOT IN (SELECT DISTINCT tag_name FROM tbl_tags)", null); + + // delete excess posts on a per-tag basis + ReaderTagList tags = ReaderTagTable.getAllTags(); + for (ReaderTag tag: tags) { + numDeleted += purgePostsForTag(db, tag); + } + + // delete search results + numDeleted += purgeSearchResults(db); + + // delete posts in tbl_posts that no longer exist in tbl_post_tags + numDeleted += db.delete("tbl_posts", "pseudo_id NOT IN (SELECT DISTINCT pseudo_id FROM tbl_post_tags)", null); + + return numDeleted; + } + + /* + * purge excess posts in the passed tag + */ + private static final int MAX_POSTS_PER_TAG = ReaderConstants.READER_MAX_POSTS_TO_DISPLAY; + private static int purgePostsForTag(SQLiteDatabase db, ReaderTag tag) { + int numPosts = getNumPostsWithTag(tag); + if (numPosts <= MAX_POSTS_PER_TAG) { + return 0; + } + + int numToPurge = numPosts - MAX_POSTS_PER_TAG; + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt()), Integer.toString(numToPurge)}; + String where = "pseudo_id IN (" + + " SELECT tbl_posts.pseudo_id FROM tbl_posts, tbl_post_tags" + + " WHERE tbl_posts.pseudo_id = tbl_post_tags.pseudo_id" + + " AND tbl_post_tags.tag_name=?" + + " AND tbl_post_tags.tag_type=?" + + " ORDER BY tbl_posts.sort_index" + + " LIMIT ?" + + ")"; + int numDeleted = db.delete("tbl_post_tags", where, args); + AppLog.d(AppLog.T.READER, String.format("reader post table > purged %d posts in tag %s", numDeleted, tag.getTagNameForLog())); + return numDeleted; + } + + /* + * purge all posts that were retained from previous searches + */ + private static int purgeSearchResults(SQLiteDatabase db) { + String[] args = {Integer.toString(ReaderTagType.SEARCH.toInt())}; + return db.delete("tbl_post_tags", "tag_type=?", args); + } + + public static int getNumPostsInBlog(long blogId) { + if (blogId == 0) { + return 0; + } + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT count(*) FROM tbl_posts WHERE blog_id=?", + new String[]{Long.toString(blogId)}); + } + + public static int getNumPostsInFeed(long feedId) { + if (feedId == 0) { + return 0; + } + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT count(*) FROM tbl_posts WHERE feed_id=?", + new String[]{Long.toString(feedId)}); + } + + public static int getNumPostsWithTag(ReaderTag tag) { + if (tag == null) { + return 0; + } + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT count(*) FROM tbl_post_tags WHERE tag_name=? AND tag_type=?", + args); + } + + public static void addOrUpdatePost(ReaderPost post) { + if (post == null) { + return; + } + ReaderPostList posts = new ReaderPostList(); + posts.add(post); + addOrUpdatePosts(null, posts); + } + + public static ReaderPost getPost(long blogId, long postId, boolean excludeTextColumn) { + + String columns = (excludeTextColumn ? COLUMN_NAMES_NO_TEXT : "*"); + String sql = "SELECT " + columns + " FROM tbl_posts WHERE blog_id=? AND post_id=? LIMIT 1"; + + String[] args = new String[] {Long.toString(blogId), Long.toString(postId)}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery(sql, args); + try { + if (!c.moveToFirst()) { + return null; + } + return getPostFromCursor(c); + } finally { + SqlUtils.closeCursor(c); + } + } + + public static String getPostTitle(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), + "SELECT title FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + public static String getPostText(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), + "SELECT text FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + public static boolean postExists(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT 1 FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + /* + * returns whether any of the passed posts are new or changed - used after posts are retrieved + */ + public static ReaderActions.UpdateResult comparePosts(ReaderPostList posts) { + if (posts == null || posts.size() == 0) { + return ReaderActions.UpdateResult.UNCHANGED; + } + + boolean hasChanges = false; + for (ReaderPost post: posts) { + ReaderPost existingPost = getPost(post.blogId, post.postId, true); + if (existingPost == null) { + return ReaderActions.UpdateResult.HAS_NEW; + } else if (!hasChanges && !post.isSamePost(existingPost)) { + hasChanges = true; + } + } + + return (hasChanges ? ReaderActions.UpdateResult.CHANGED : ReaderActions.UpdateResult.UNCHANGED); + } + + /* + * returns true if any posts in the passed list exist in this list + */ + public static boolean hasOverlap(ReaderPostList posts) { + for (ReaderPost post: posts) { + if (postExists(post.blogId, post.postId)) { + return true; + } + } + return false; + } + + /* + * returns the #comments known to exist for this post (ie: #comments the server says this post has), which + * may differ from ReaderCommentTable.getNumCommentsForPost (which returns # local comments for this post) + */ + public static int getNumCommentsForPost(ReaderPost post) { + if (post == null) { + return 0; + } + String[] args = new String[] {Long.toString(post.blogId), Long.toString(post.postId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT num_replies FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + /* + * returns the #likes known to exist for this post (ie: #likes the server says this post has), which + * may differ from ReaderPostTable.getNumLikesForPost (which returns # local likes for this post) + */ + public static int getNumLikesForPost(long blogId, long postId) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), + "SELECT num_likes FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + public static boolean isPostLikedByCurrentUser(ReaderPost post) { + return post != null && isPostLikedByCurrentUser(post.blogId, post.postId); + } + public static boolean isPostLikedByCurrentUser(long blogId, long postId) { + String[] args = new String[] {Long.toString(blogId), Long.toString(postId)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT is_liked FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + /* + * updates both the like count for a post and whether it's liked by the current user + */ + public static void setLikesForPost(ReaderPost post, int numLikes, boolean isLikedByCurrentUser) { + if (post == null) { + return; + } + + String[] args = {Long.toString(post.blogId), Long.toString(post.postId)}; + + ContentValues values = new ContentValues(); + values.put("num_likes", numLikes); + values.put("is_liked", SqlUtils.boolToSql(isLikedByCurrentUser)); + + ReaderDatabase.getWritableDb().update( + "tbl_posts", + values, + "blog_id=? AND post_id=?", + args); + } + + + public static boolean isPostFollowed(ReaderPost post) { + if (post == null) { + return false; + } + String[] args = new String[] {Long.toString(post.blogId), Long.toString(post.postId)}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT is_followed FROM tbl_posts WHERE blog_id=? AND post_id=?", + args); + } + + public static int deletePostsWithTag(final ReaderTag tag) { + if (tag == null) { + return 0; + } + + // first delete posts from tbl_post_tags, and if any were deleted next delete posts in tbl_posts that no longer exist in tbl_post_tags + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + int numDeleted = ReaderDatabase.getWritableDb().delete("tbl_post_tags", + "tag_name=? AND tag_type=?", + args); + + if (numDeleted > 0) + ReaderDatabase.getWritableDb().delete("tbl_posts", + "pseudo_id NOT IN (SELECT DISTINCT pseudo_id FROM tbl_post_tags)", + null); + + return numDeleted; + } + + public static int deletePostsInBlog(long blogId) { + String[] args = {Long.toString(blogId)}; + return ReaderDatabase.getWritableDb().delete("tbl_posts", "blog_id = ?", args); + } + + /* + * ensure that posts in blogs that are no longer followed don't have their followed status + * set to true + */ + public static void updateFollowedStatus() { + SQLiteStatement statement = ReaderDatabase.getWritableDb().compileStatement( + "UPDATE tbl_posts SET is_followed = 0" + + " WHERE is_followed != 0" + + " AND blog_id NOT IN (SELECT DISTINCT blog_id FROM tbl_blog_info WHERE is_followed != 0)"); + try { + int count = statement.executeUpdateDelete(); + if (count > 0) { + AppLog.d(AppLog.T.READER, String.format("reader post table > marked %d posts unfollowed", count)); + } + } finally { + statement.close(); + } + } + + /* + * returns the iso8601 date of the oldest post with the passed tag + */ + public static String getOldestDateWithTag(final ReaderTag tag) { + if (tag == null) { + return ""; + } + + String sql = "SELECT tbl_posts.date FROM tbl_posts, tbl_post_tags" + + " WHERE tbl_posts.pseudo_id = tbl_post_tags.pseudo_id" + + " AND tbl_post_tags.tag_name=? AND tbl_post_tags.tag_type=?" + + " ORDER BY date LIMIT 1"; + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + /* + * returns the iso8601 date of the oldest post in the passed blog + */ + public static String getOldestDateInBlog(long blogId) { + String sql = "SELECT date FROM tbl_posts" + + " WHERE blog_id = ?" + + " ORDER BY date LIMIT 1"; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), sql, new String[]{Long.toString(blogId)}); + } + + public static String getOldestDateInFeed(long feedId) { + String sql = "SELECT date FROM tbl_posts" + + " WHERE feed_id = ?" + + " ORDER BY date LIMIT 1"; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), sql, new String[]{Long.toString(feedId)}); + } + + public static void removeGapMarkerForTag(final ReaderTag tag) { + if (tag == null) return; + + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + String sql = "UPDATE tbl_post_tags SET has_gap_marker=0 WHERE has_gap_marker!=0 AND tag_name=? AND tag_type=?"; + ReaderDatabase.getWritableDb().execSQL(sql, args); + } + + /* + * returns the blogId/postId of the post with the passed tag that has a gap marker, or null if none exists + */ + public static ReaderBlogIdPostId getGapMarkerIdsForTag(final ReaderTag tag) { + if (tag == null) { + return null; + } + + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + String sql = "SELECT blog_id, post_id FROM tbl_post_tags WHERE has_gap_marker!=0 AND tag_name=? AND tag_type=?"; + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery(sql, args); + try { + if (cursor.moveToFirst()) { + long blogId = cursor.getLong(0); + long postId = cursor.getLong(1); + return new ReaderBlogIdPostId(blogId, postId); + } else { + return null; + } + } finally { + SqlUtils.closeCursor(cursor); + } + } + + public static void setGapMarkerForTag(long blogId, long postId, ReaderTag tag) { + if (tag == null) return; + + String[] args = { + Long.toString(blogId), + Long.toString(postId), + tag.getTagSlug(), + Integer.toString(tag.tagType.toInt()) + }; + String sql = "UPDATE tbl_post_tags SET has_gap_marker=1 WHERE blog_id=? AND post_id=? AND tag_name=? AND tag_type=?"; + ReaderDatabase.getWritableDb().execSQL(sql, args); + } + + public static String getGapMarkerDateForTag(ReaderTag tag) { + ReaderBlogIdPostId ids = getGapMarkerIdsForTag(tag); + if (ids == null) { + return null; + } + String[] args = {Long.toString(ids.getBlogId()), Long.toString(ids.getPostId())}; + String sql = "SELECT date FROM tbl_posts WHERE blog_id=? AND post_id=?"; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + private static long getGapMarkerSortIndexForTag(ReaderTag tag) { + ReaderBlogIdPostId ids = getGapMarkerIdsForTag(tag); + if (ids == null) { + return 0; + } + + String[] args = {Long.toString(ids.getBlogId()), Long.toString(ids.getPostId())}; + String sql = "SELECT sort_index FROM tbl_posts WHERE blog_id=? AND post_id=?"; + return SqlUtils.longForQuery(ReaderDatabase.getReadableDb(), sql, args); + } + + /* + * delete posts with the passed tag that come before the one with the gap marker for + * this tag - note this may leave some stray posts in tbl_posts, but these will + * be cleaned up by the next purge + */ + public static void deletePostsBeforeGapMarkerForTag(ReaderTag tag) { + long sortIndex = getGapMarkerSortIndexForTag(tag); + if (sortIndex == 0) return; + + String[] args = {Long.toString(sortIndex), tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + String where = "pseudo_id IN (SELECT tbl_posts.pseudo_id FROM tbl_posts, tbl_post_tags" + + " WHERE tbl_posts.sort_index < ?" + + " AND tbl_posts.pseudo_id = tbl_post_tags.pseudo_id" + + " AND tbl_post_tags.tag_name=? AND tbl_post_tags.tag_type=?)"; + int numDeleted = ReaderDatabase.getWritableDb().delete("tbl_post_tags", where, args); + AppLog.d(AppLog.T.READER, "removed " + numDeleted + " posts older than gap marker"); + } + + public static void setFollowStatusForPostsInBlog(long blogId, boolean isFollowed) { + setFollowStatusForPosts(blogId, 0, isFollowed); + } + public static void setFollowStatusForPostsInFeed(long feedId, boolean isFollowed) { + setFollowStatusForPosts(0, feedId, isFollowed); + } + private static void setFollowStatusForPosts(long blogId, long feedId, boolean isFollowed) { + if (blogId == 0 && feedId == 0) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + try { + if (blogId != 0) { + String sql = "UPDATE tbl_posts SET is_followed=" + SqlUtils.boolToSql(isFollowed) + + " WHERE blog_id=?"; + db.execSQL(sql, new String[]{Long.toString(blogId)}); + } else { + String sql = "UPDATE tbl_posts SET is_followed=" + SqlUtils.boolToSql(isFollowed) + + " WHERE feed_id=?"; + db.execSQL(sql, new String[]{Long.toString(feedId)}); + } + + + // if blog/feed is no longer followed, remove its posts tagged with "Followed Sites" in + // tbl_post_tags + if (!isFollowed) { + if (blogId != 0) { + db.delete("tbl_post_tags", "blog_id=? AND tag_name=?", + new String[]{Long.toString(blogId), ReaderTag.TAG_TITLE_FOLLOWED_SITES}); + } else { + db.delete("tbl_post_tags", "feed_id=? AND tag_name=?", + new String[]{Long.toString(feedId), ReaderTag.TAG_TITLE_FOLLOWED_SITES}); + } + } + + db.setTransactionSuccessful(); + } finally { + db.endTransaction(); + } + } + + /* + * Android's CursorWindow has a max size of 2MB per row which can be exceeded + * with a very large text column, causing an IllegalStateException when the + * row is read - prevent this by limiting the amount of text that's stored in + * the text column - note that this situation very rarely occurs + * https://github.com/android/platform_frameworks_base/blob/b77bc869241644a662f7e615b0b00ecb5aee373d/core/res/res/values/config.xml#L1268 + * https://github.com/android/platform_frameworks_base/blob/3bdbf644d61f46b531838558fabbd5b990fc4913/core/java/android/database/CursorWindow.java#L103 + */ + private static final int MAX_TEXT_LEN = (1024 * 1024) / 2; + private static String maxText(final ReaderPost post) { + if (post.getText().length() <= MAX_TEXT_LEN) { + return post.getText(); + } + // if the post has an excerpt (which should always be the case), store it as the full text + // with a link to the full article + if (post.hasExcerpt()) { + AppLog.w(AppLog.T.READER, "reader post table > max text exceeded, storing excerpt"); + return "<p>" + post.getExcerpt() + "</p>" + + String.format("<p style='text-align:center'><a href='%s'>%s</a></p>", + post.getUrl(), WordPress.getContext().getString(R.string.reader_label_view_original)); + } else { + AppLog.w(AppLog.T.READER, "reader post table > max text exceeded, storing truncated text"); + return post.getText().substring(0, MAX_TEXT_LEN); + } + } + + public static void addOrUpdatePosts(final ReaderTag tag, ReaderPostList posts) { + if (posts == null || posts.size() == 0) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + SQLiteStatement stmtPosts = db.compileStatement( + "INSERT OR REPLACE INTO tbl_posts (" + + COLUMN_NAMES + + ") VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16,?17,?18,?19,?20,?21,?22,?23,?24,?25,?26,?27,?28,?29,?30,?31,?32,?33,?34,?35,?36,?37,?38)"); + SQLiteStatement stmtTags = db.compileStatement( + "INSERT OR REPLACE INTO tbl_post_tags (post_id, blog_id, feed_id, pseudo_id, tag_name, tag_type) VALUES (?1,?2,?3,?4,?5,?6)"); + + db.beginTransaction(); + try { + // first insert into tbl_posts + for (ReaderPost post: posts) { + stmtPosts.bindLong (1, post.postId); + stmtPosts.bindLong (2, post.blogId); + stmtPosts.bindLong (3, post.feedId); + stmtPosts.bindLong (4, post.feedItemId); + stmtPosts.bindString(5, post.getPseudoId()); + stmtPosts.bindString(6, post.getAuthorName()); + stmtPosts.bindString(7, post.getAuthorFirstName()); + stmtPosts.bindLong (8, post.authorId); + stmtPosts.bindString(9, post.getTitle()); + stmtPosts.bindString(10, maxText(post)); + stmtPosts.bindString(11, post.getExcerpt()); + stmtPosts.bindString(12, post.getFormat()); + stmtPosts.bindString(13, post.getUrl()); + stmtPosts.bindString(14, post.getShortUrl()); + stmtPosts.bindString(15, post.getBlogUrl()); + stmtPosts.bindString(16, post.getBlogName()); + stmtPosts.bindString(17, post.getFeaturedImage()); + stmtPosts.bindString(18, post.getFeaturedVideo()); + stmtPosts.bindString(19, post.getPostAvatar()); + stmtPosts.bindDouble(20, post.sortIndex); + stmtPosts.bindString(21, post.getDate()); + stmtPosts.bindString(22, post.getPubDate()); + stmtPosts.bindLong (23, post.numReplies); + stmtPosts.bindLong (24, post.numLikes); + stmtPosts.bindLong (25, SqlUtils.boolToSql(post.isLikedByCurrentUser)); + stmtPosts.bindLong (26, SqlUtils.boolToSql(post.isFollowedByCurrentUser)); + stmtPosts.bindLong (27, SqlUtils.boolToSql(post.isCommentsOpen)); + stmtPosts.bindLong (28, SqlUtils.boolToSql(post.isExternal)); + stmtPosts.bindLong (29, SqlUtils.boolToSql(post.isPrivate)); + stmtPosts.bindLong (30, SqlUtils.boolToSql(post.isVideoPress)); + stmtPosts.bindLong (31, SqlUtils.boolToSql(post.isJetpack)); + stmtPosts.bindString(32, post.getPrimaryTag()); + stmtPosts.bindString(33, post.getSecondaryTag()); + stmtPosts.bindString(34, post.getAttachmentsJson()); + stmtPosts.bindString(35, post.getDiscoverJson()); + stmtPosts.bindLong (36, post.xpostPostId); + stmtPosts.bindLong (37, post.xpostBlogId); + stmtPosts.bindString(38, post.getRailcarJson()); + stmtPosts.execute(); + } + + // now add to tbl_post_tags if a tag was passed + if (tag != null) { + String tagName = tag.getTagSlug(); + int tagType = tag.tagType.toInt(); + for (ReaderPost post: posts) { + stmtTags.bindLong (1, post.postId); + stmtTags.bindLong (2, post.blogId); + stmtTags.bindLong (3, post.feedId); + stmtTags.bindString(4, post.getPseudoId()); + stmtTags.bindString(5, tagName); + stmtTags.bindLong (6, tagType); + stmtTags.execute(); + } + } + + db.setTransactionSuccessful(); + + } finally { + db.endTransaction(); + SqlUtils.closeStatement(stmtPosts); + SqlUtils.closeStatement(stmtTags); + } + } + + public static ReaderPostList getPostsWithTag(ReaderTag tag, int maxPosts, boolean excludeTextColumn) { + if (tag == null) { + return new ReaderPostList(); + } + + String columns = (excludeTextColumn ? COLUMN_NAMES_NO_TEXT : "tbl_posts.*"); + String sql = "SELECT " + columns + " FROM tbl_posts, tbl_post_tags" + + " WHERE tbl_posts.pseudo_id = tbl_post_tags.pseudo_id" + + " AND tbl_post_tags.tag_name=?" + + " AND tbl_post_tags.tag_type=?"; + + if (tag.tagType == ReaderTagType.DEFAULT) { + // skip posts that are no longer liked if this is "Posts I Like", skip posts that are no + // longer followed if this is "Followed Sites" + if (tag.isPostsILike()) { + sql += " AND tbl_posts.is_liked != 0"; + } else if (tag.isFollowedSites()) { + sql += " AND tbl_posts.is_followed != 0"; + } + } + + sql += " ORDER BY tbl_posts.sort_index DESC"; + + if (maxPosts > 0) { + sql += " LIMIT " + Integer.toString(maxPosts); + } + + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery(sql, args); + try { + return getPostListFromCursor(cursor); + } finally { + SqlUtils.closeCursor(cursor); + } + } + + public static ReaderPostList getPostsInBlog(long blogId, int maxPosts, boolean excludeTextColumn) { + String columns = (excludeTextColumn ? COLUMN_NAMES_NO_TEXT : "tbl_posts.*"); + String sql = "SELECT " + columns + " FROM tbl_posts WHERE blog_id = ? ORDER BY tbl_posts.sort_index DESC"; + + if (maxPosts > 0) { + sql += " LIMIT " + Integer.toString(maxPosts); + } + + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery(sql, new String[]{Long.toString(blogId)}); + try { + return getPostListFromCursor(cursor); + } finally { + SqlUtils.closeCursor(cursor); + } + } + + public static ReaderPostList getPostsInFeed(long feedId, int maxPosts, boolean excludeTextColumn) { + String columns = (excludeTextColumn ? COLUMN_NAMES_NO_TEXT : "tbl_posts.*"); + String sql = "SELECT " + columns + " FROM tbl_posts WHERE feed_id = ? ORDER BY tbl_posts.sort_index DESC"; + + if (maxPosts > 0) { + sql += " LIMIT " + Integer.toString(maxPosts); + } + + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery(sql, new String[]{Long.toString(feedId)}); + try { + return getPostListFromCursor(cursor); + } finally { + SqlUtils.closeCursor(cursor); + } + } + + /* + * same as getPostsWithTag() but only returns the blogId/postId pairs + */ + public static ReaderBlogIdPostIdList getBlogIdPostIdsWithTag(ReaderTag tag, int maxPosts) { + ReaderBlogIdPostIdList idList = new ReaderBlogIdPostIdList(); + if (tag == null) { + return idList; + } + + String sql = "SELECT tbl_posts.blog_id, tbl_posts.post_id FROM tbl_posts, tbl_post_tags" + + " WHERE tbl_posts.pseudo_id = tbl_post_tags.pseudo_id" + + " AND tbl_post_tags.tag_name=?" + + " AND tbl_post_tags.tag_type=?"; + + if (tag.tagType == ReaderTagType.DEFAULT) { + if (tag.isPostsILike()) { + sql += " AND tbl_posts.is_liked != 0"; + } else if (tag.isFollowedSites()) { + sql += " AND tbl_posts.is_followed != 0"; + } + } + + sql += " ORDER BY tbl_posts.sort_index DESC"; + + if (maxPosts > 0) { + sql += " LIMIT " + Integer.toString(maxPosts); + } + + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery(sql, args); + try { + if (cursor != null && cursor.moveToFirst()) { + do { + idList.add(new ReaderBlogIdPostId(cursor.getLong(0), cursor.getLong(1))); + } while (cursor.moveToNext()); + } + return idList; + } finally { + SqlUtils.closeCursor(cursor); + } + } + + /* + * same as getPostsInBlog() but only returns the blogId/postId pairs + */ + public static ReaderBlogIdPostIdList getBlogIdPostIdsInBlog(long blogId, int maxPosts) { + String sql = "SELECT post_id FROM tbl_posts WHERE blog_id = ? ORDER BY tbl_posts.sort_index DESC"; + + if (maxPosts > 0) { + sql += " LIMIT " + Integer.toString(maxPosts); + } + + Cursor cursor = ReaderDatabase.getReadableDb().rawQuery(sql, new String[]{Long.toString(blogId)}); + try { + ReaderBlogIdPostIdList idList = new ReaderBlogIdPostIdList(); + if (cursor != null && cursor.moveToFirst()) { + do { + idList.add(new ReaderBlogIdPostId(blogId, cursor.getLong(0))); + } while (cursor.moveToNext()); + } + + return idList; + } finally { + SqlUtils.closeCursor(cursor); + } + } + + private static ReaderPost getPostFromCursor(Cursor c) { + if (c == null) { + throw new IllegalArgumentException("getPostFromCursor > null cursor"); + } + + ReaderPost post = new ReaderPost(); + + // text column is skipped when retrieving multiple rows + int idxText = c.getColumnIndex("text"); + if (idxText > -1) { + post.setText(c.getString(idxText)); + } + + post.postId = c.getLong(c.getColumnIndex("post_id")); + post.blogId = c.getLong(c.getColumnIndex("blog_id")); + post.feedId = c.getLong(c.getColumnIndex("feed_id")); + post.feedItemId = c.getLong(c.getColumnIndex("feed_item_id")); + post.authorId = c.getLong(c.getColumnIndex("author_id")); + post.setPseudoId(c.getString(c.getColumnIndex("pseudo_id"))); + + post.setAuthorName(c.getString(c.getColumnIndex("author_name"))); + post.setAuthorFirstName(c.getString(c.getColumnIndex("author_first_name"))); + post.setBlogName(c.getString(c.getColumnIndex("blog_name"))); + post.setBlogUrl(c.getString(c.getColumnIndex("blog_url"))); + post.setExcerpt(c.getString(c.getColumnIndex("excerpt"))); + post.setFormat(c.getString(c.getColumnIndex("format"))); + post.setFeaturedImage(c.getString(c.getColumnIndex("featured_image"))); + post.setFeaturedVideo(c.getString(c.getColumnIndex("featured_video"))); + + post.setTitle(c.getString(c.getColumnIndex("title"))); + post.setUrl(c.getString(c.getColumnIndex("url"))); + post.setShortUrl(c.getString(c.getColumnIndex("short_url"))); + post.setPostAvatar(c.getString(c.getColumnIndex("post_avatar"))); + + post.sortIndex = c.getDouble(c.getColumnIndex("sort_index")); + post.setDate(c.getString(c.getColumnIndex("date"))); + post.setPubDate(c.getString(c.getColumnIndex("published"))); + + post.numReplies = c.getInt(c.getColumnIndex("num_replies")); + post.numLikes = c.getInt(c.getColumnIndex("num_likes")); + + post.isLikedByCurrentUser = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_liked"))); + post.isFollowedByCurrentUser = SqlUtils.sqlToBool(c.getInt( c.getColumnIndex("is_followed"))); + post.isCommentsOpen = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_comments_open"))); + post.isExternal = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_external"))); + post.isPrivate = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_private"))); + post.isVideoPress = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_videopress"))); + post.isJetpack = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_jetpack"))); + + post.setPrimaryTag(c.getString(c.getColumnIndex("primary_tag"))); + post.setSecondaryTag(c.getString(c.getColumnIndex("secondary_tag"))); + + post.setAttachmentsJson(c.getString(c.getColumnIndex("attachments_json"))); + post.setDiscoverJson(c.getString(c.getColumnIndex("discover_json"))); + + post.xpostPostId = c.getLong(c.getColumnIndex("xpost_post_id")); + post.xpostBlogId = c.getLong(c.getColumnIndex("xpost_blog_id")); + + post.setRailcarJson(c.getString(c.getColumnIndex("railcar_json"))); + + return post; + } + + private static ReaderPostList getPostListFromCursor(Cursor cursor) { + ReaderPostList posts = new ReaderPostList(); + try { + if (cursor != null && cursor.moveToFirst()) { + do { + posts.add(getPostFromCursor(cursor)); + } while (cursor.moveToNext()); + } + } catch (IllegalStateException e) { + CrashlyticsUtils.logException(e, CrashlyticsUtils.ExceptionType.SPECIFIC); + AppLog.e(AppLog.T.READER, e); + } + return posts; + } +} 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); + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderTagTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderTagTable.java new file mode 100644 index 000000000..b9f962c42 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderTagTable.java @@ -0,0 +1,381 @@ +package org.wordpress.android.datasets; + +import android.database.Cursor; +import android.database.SQLException; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; +import android.text.TextUtils; + +import org.wordpress.android.models.ReaderTag; +import org.wordpress.android.models.ReaderTagList; +import org.wordpress.android.models.ReaderTagType; +import org.wordpress.android.ui.reader.ReaderConstants; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.AppLog.T; +import org.wordpress.android.util.DateTimeUtils; +import org.wordpress.android.util.SqlUtils; + +import java.util.Date; + +/** + * tbl_tags stores the list of tags the user subscribed to or has by default + * tbl_tags_recommended stores the list of recommended tags returned by the api + */ +public class ReaderTagTable { + + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_tags (" + + " tag_slug TEXT COLLATE NOCASE," + + " tag_display_name TEXT COLLATE NOCASE," + + " tag_title TEXT COLLATE NOCASE," + + " tag_type INTEGER DEFAULT 0," + + " endpoint TEXT," + + " date_updated TEXT," + + " PRIMARY KEY (tag_slug, tag_type)" + + ")"); + + db.execSQL("CREATE TABLE tbl_tags_recommended (" + + " tag_slug TEXT COLLATE NOCASE," + + " tag_display_name TEXT COLLATE NOCASE," + + " tag_title TEXT COLLATE NOCASE," + + " tag_type INTEGER DEFAULT 0," + + " endpoint TEXT," + + " PRIMARY KEY (tag_slug, tag_type)" + + ")"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_tags"); + db.execSQL("DROP TABLE IF EXISTS tbl_tags_recommended"); + } + + /* + * returns true if tbl_tags is empty + */ + public static boolean isEmpty() { + return (SqlUtils.getRowCount(ReaderDatabase.getReadableDb(), "tbl_tags") == 0); + } + + /* + * replaces all tags with the passed list + */ + public static void replaceTags(ReaderTagList tags) { + if (tags == null || tags.size() == 0) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + try { + try { + // first delete all existing tags, then insert the passed ones + db.execSQL("DELETE FROM tbl_tags"); + addOrUpdateTags(tags); + db.setTransactionSuccessful(); + } catch (SQLException e) { + AppLog.e(T.READER, e); + } + } finally { + db.endTransaction(); + } + } + + /* + * similar to the above but only replaces followed tags + */ + public static void replaceFollowedTags(ReaderTagList tags) { + if (tags == null || tags.size() == 0) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + try { + try { + // first delete all existing followed tags, then insert the passed ones + String[] args = {Integer.toString(ReaderTagType.FOLLOWED.toInt())}; + db.execSQL("DELETE FROM tbl_tags WHERE tag_type=?", args); + addOrUpdateTags(tags); + db.setTransactionSuccessful(); + } catch (SQLException e) { + AppLog.e(T.READER, e); + } + } finally { + db.endTransaction(); + } + } + + public static void addOrUpdateTag(ReaderTag tag) { + if (tag == null) { + return; + } + ReaderTagList tags = new ReaderTagList(); + tags.add(tag); + addOrUpdateTags(tags); + } + + private static void addOrUpdateTags(ReaderTagList tagList) { + if (tagList == null || tagList.size() == 0) { + return; + } + SQLiteStatement stmt = null; + try { + stmt = ReaderDatabase.getWritableDb().compileStatement( + "INSERT OR REPLACE INTO tbl_tags (tag_slug, tag_display_name, tag_title, tag_type, endpoint) VALUES (?1,?2,?3,?4,?5)" + ); + + for (ReaderTag tag: tagList) { + stmt.bindString(1, tag.getTagSlug()); + stmt.bindString(2, tag.getTagDisplayName()); + stmt.bindString(3, tag.getTagTitle()); + stmt.bindLong (4, tag.tagType.toInt()); + stmt.bindString(5, tag.getEndpoint()); + stmt.execute(); + } + + } finally { + SqlUtils.closeStatement(stmt); + } + } + + /* + * returns true if the passed tag exists, regardless of type + */ + public static boolean tagExists(ReaderTag tag) { + if (tag == null) { + return false; + } + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT 1 FROM tbl_tags WHERE tag_slug=?1 AND tag_type=?2", + args); + } + + /* + * returns true if the passed tag exists and it has the passed type + */ + private static boolean tagExistsOfType(String tagSlug, ReaderTagType tagType) { + if (TextUtils.isEmpty(tagSlug) || tagType == null) { + return false; + } + + String[] args = {tagSlug, Integer.toString(tagType.toInt())}; + return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(), + "SELECT 1 FROM tbl_tags WHERE tag_slug=?1 AND tag_type=?2", + args); + } + + public static boolean isFollowedTagName(String tagSlug) { + return tagExistsOfType(tagSlug, ReaderTagType.FOLLOWED); + } + + private static ReaderTag getTagFromCursor(Cursor c) { + if (c == null) { + throw new IllegalArgumentException("null tag cursor"); + } + + String tagSlug = c.getString(c.getColumnIndex("tag_slug")); + String tagDisplayName = c.getString(c.getColumnIndex("tag_display_name")); + String tagTitle = c.getString(c.getColumnIndex("tag_title")); + String endpoint = c.getString(c.getColumnIndex("endpoint")); + ReaderTagType tagType = ReaderTagType.fromInt(c.getInt(c.getColumnIndex("tag_type"))); + + return new ReaderTag(tagSlug, tagDisplayName, tagTitle, endpoint, tagType); + } + + public static ReaderTag getTag(String tagSlug, ReaderTagType tagType) { + if (TextUtils.isEmpty(tagSlug)) { + return null; + } + + String[] args = {tagSlug, Integer.toString(tagType.toInt())}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_tags WHERE tag_slug=? AND tag_type=? LIMIT 1", args); + try { + if (!c.moveToFirst()) { + return null; + } + return getTagFromCursor(c); + } finally { + SqlUtils.closeCursor(c); + } + } + + public static String getEndpointForTag(ReaderTag tag) { + if (tag == null) { + return null; + } + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), + "SELECT endpoint FROM tbl_tags WHERE tag_slug=? AND tag_type=?", + args); + } + + public static ReaderTagList getDefaultTags() { + return getTagsOfType(ReaderTagType.DEFAULT); + } + + public static ReaderTagList getFollowedTags() { + return getTagsOfType(ReaderTagType.FOLLOWED); + } + + public static ReaderTagList getCustomListTags() { + return getTagsOfType(ReaderTagType.CUSTOM_LIST); + } + + private static ReaderTagList getTagsOfType(ReaderTagType tagType) { + String[] args = {Integer.toString(tagType.toInt())}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_tags WHERE tag_type=? ORDER BY tag_slug", args); + try { + ReaderTagList tagList = new ReaderTagList(); + if (c.moveToFirst()) { + do { + tagList.add(getTagFromCursor(c)); + } while (c.moveToNext()); + } + return tagList; + } finally { + SqlUtils.closeCursor(c); + } + } + + static ReaderTagList getAllTags() { + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_tags ORDER BY tag_slug", null); + try { + ReaderTagList tagList = new ReaderTagList(); + if (c.moveToFirst()) { + do { + tagList.add(getTagFromCursor(c)); + } while (c.moveToNext()); + } + return tagList; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static void deleteTag(ReaderTag tag) { + if (tag == null) { + return; + } + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + ReaderDatabase.getWritableDb().delete("tbl_tags", "tag_slug=? AND tag_type=?", args); + } + + + public static String getTagLastUpdated(ReaderTag tag) { + if (tag == null) { + return ""; + } + String[] args = {tag.getTagSlug(), Integer.toString(tag.tagType.toInt())}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), + "SELECT date_updated FROM tbl_tags WHERE tag_slug=? AND tag_type=?", + args); + } + + public static void setTagLastUpdated(ReaderTag tag) { + if (tag == null) { + return; + } + + String date = DateTimeUtils.iso8601FromDate(new Date()); + String sql = "UPDATE tbl_tags SET date_updated=?1 WHERE tag_slug=?2 AND tag_type=?3"; + SQLiteStatement stmt = ReaderDatabase.getWritableDb().compileStatement(sql); + try { + stmt.bindString(1, date); + stmt.bindString(2, tag.getTagSlug()); + stmt.bindLong (3, tag.tagType.toInt()); + stmt.execute(); + } finally { + SqlUtils.closeStatement(stmt); + } + } + + /* + * determine whether the passed tag should be auto-updated based on when it was last updated + */ + public static boolean shouldAutoUpdateTag(ReaderTag tag) { + int minutes = minutesSinceLastUpdate(tag); + if (minutes == NEVER_UPDATED) { + return true; + } + return (minutes >= ReaderConstants.READER_AUTO_UPDATE_DELAY_MINUTES); + } + + private static final int NEVER_UPDATED = -1; + private static int minutesSinceLastUpdate(ReaderTag tag) { + if (tag == null) { + return 0; + } + + String updated = getTagLastUpdated(tag); + if (TextUtils.isEmpty(updated)) { + return NEVER_UPDATED; + } + + Date dtUpdated = DateTimeUtils.dateFromIso8601(updated); + if (dtUpdated == null) { + return 0; + } + + Date dtNow = new Date(); + return DateTimeUtils.minutesBetween(dtUpdated, dtNow); + } + + /** + * recommended tags - stored in a separate table from default/subscribed tags, but have the same column names + **/ + public static ReaderTagList getRecommendedTags(boolean excludeSubscribed) { + Cursor c; + if (excludeSubscribed) { + c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_tags_recommended WHERE tag_slug NOT IN (SELECT tag_slug FROM tbl_tags) ORDER BY tag_slug", null); + } else { + c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_tags_recommended ORDER BY tag_slug", null); + } + try { + ReaderTagList tagList = new ReaderTagList(); + if (c.moveToFirst()) { + do { + tagList.add(getTagFromCursor(c)); + } while (c.moveToNext()); + } + return tagList; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static void setRecommendedTags(ReaderTagList tagList) { + if (tagList == null) { + return; + } + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + SQLiteStatement stmt = db.compileStatement + ("INSERT INTO tbl_tags_recommended (tag_slug, tag_display_name, tag_title, tag_type, endpoint) VALUES (?1,?2,?3,?4,?5)"); + db.beginTransaction(); + try { + try { + // first delete all recommended tags + db.execSQL("DELETE FROM tbl_tags_recommended"); + + // then insert the passed ones + for (ReaderTag tag: tagList) { + stmt.bindString(1, tag.getTagSlug()); + stmt.bindString(2, tag.getTagDisplayName()); + stmt.bindString(3, tag.getTagTitle()); + stmt.bindLong (4, tag.tagType.toInt()); + stmt.bindString(5, tag.getEndpoint()); + stmt.execute(); + } + + db.setTransactionSuccessful(); + + } catch (SQLException e) { + AppLog.e(T.READER, e); + } + } finally { + SqlUtils.closeStatement(stmt); + db.endTransaction(); + } + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderThumbnailTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderThumbnailTable.java new file mode 100644 index 000000000..d9fd46307 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderThumbnailTable.java @@ -0,0 +1,56 @@ +package org.wordpress.android.datasets; + +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; +import android.text.TextUtils; + +import org.wordpress.android.util.SqlUtils; + +/** + * stores thumbnail urls for videos embedded in Reader posts + */ +public class ReaderThumbnailTable { + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_thumbnails (" + + " full_url TEXT COLLATE NOCASE PRIMARY KEY," + + " thumbnail_url TEXT NOT NULL," + + " post_id INTEGER DEFAULT 0)"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_thumbnails"); + } + + /* + * purge table of thumbnails attached to posts that no longer exist + */ + protected static int purge(SQLiteDatabase db) { + return db.delete("tbl_thumbnails", "post_id NOT IN (SELECT DISTINCT post_id FROM tbl_posts)", null); + } + + public static void addThumbnail(long postId, String fullUrl, String thumbnailUrl) { + if (TextUtils.isEmpty(fullUrl) || TextUtils.isEmpty(thumbnailUrl)) + return; + + SQLiteStatement stmt = ReaderDatabase.getWritableDb().compileStatement("INSERT OR REPLACE INTO tbl_thumbnails (full_url, thumbnail_url, post_id) VALUES (?1,?2,?3)"); + try { + stmt.bindString(1, fullUrl); + stmt.bindString(2, thumbnailUrl); + stmt.bindLong (3, postId); + stmt.execute(); + } finally { + SqlUtils.closeStatement(stmt); + } + } + + public static String getThumbnailUrl(String fullUrl) { + if (TextUtils.isEmpty(fullUrl)) { + return null; + } + return SqlUtils.stringForQuery( + ReaderDatabase.getReadableDb(), + "SELECT thumbnail_url FROM tbl_thumbnails WHERE full_url=?", + new String[]{fullUrl}); + } + +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/ReaderUserTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderUserTable.java new file mode 100644 index 000000000..dcf94704e --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/ReaderUserTable.java @@ -0,0 +1,211 @@ +package org.wordpress.android.datasets; + +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteStatement; + +import org.wordpress.android.models.ReaderUser; +import org.wordpress.android.models.ReaderUserIdList; +import org.wordpress.android.models.ReaderUserList; +import org.wordpress.android.models.AccountHelper; +import org.wordpress.android.util.GravatarUtils; +import org.wordpress.android.util.SqlUtils; + +import java.util.ArrayList; + +/** + * stores info about the current user and liking users + */ +public class ReaderUserTable { + protected static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE tbl_users (" + + " user_id INTEGER PRIMARY KEY," + + " blog_id INTEGER DEFAULT 0," + + " user_name TEXT," + + " display_name TEXT COLLATE NOCASE," + + " url TEXT," + + " profile_url TEXT," + + " avatar_url TEXT)"); + } + + protected static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS tbl_users"); + } + + public static void addOrUpdateUser(ReaderUser user) { + if (user==null) + return; + + ReaderUserList users = new ReaderUserList(); + users.add(user); + addOrUpdateUsers(users); + } + + private static final String COLUMN_NAMES = + " user_id," // 1 + + " blog_id," // 2 + + " user_name," // 3 + + " display_name," // 4 + + " url," // 5 + + " profile_url," // 6 + + " avatar_url"; // 7 + + public static void addOrUpdateUsers(ReaderUserList users) { + if (users==null || users.size()==0) + return; + + SQLiteDatabase db = ReaderDatabase.getWritableDb(); + db.beginTransaction(); + SQLiteStatement stmt = db.compileStatement("INSERT OR REPLACE INTO tbl_users (" + COLUMN_NAMES + ") VALUES (?1,?2,?3,?4,?5,?6,?7)"); + try { + for (ReaderUser user: users) { + stmt.bindLong (1, user.userId); + stmt.bindLong (2, user.blogId); + stmt.bindString(3, user.getUserName()); + stmt.bindString(4, user.getDisplayName()); + stmt.bindString(5, user.getUrl()); + stmt.bindString(6, user.getProfileUrl()); + stmt.bindString(7, user.getAvatarUrl()); + stmt.execute(); + } + + db.setTransactionSuccessful(); + + } finally { + db.endTransaction(); + SqlUtils.closeStatement(stmt); + } + } + + /* + * returns avatar urls for the passed user ids - used by post detail to show avatars for liking users + */ + public static ArrayList<String> getAvatarUrls(ReaderUserIdList userIds, int max, int avatarSz) { + ArrayList<String> avatars = new ArrayList<String>(); + if (userIds==null || userIds.size()==0) + return avatars; + + StringBuilder sb = new StringBuilder("SELECT user_id, avatar_url FROM tbl_users WHERE user_id IN ("); + + // make sure current user's avatar is returned if the passed list contains them - this is + // important since it may not otherwise be returned when a "max" is passed, and we want + // the current user to appear first in post detail when they like a post + long currentUserId = AccountHelper.getDefaultAccount().getUserId(); + boolean containsCurrentUser = userIds.contains(currentUserId); + if (containsCurrentUser) + sb.append(currentUserId); + + int numAdded = (containsCurrentUser ? 1 : 0); + for (Long id: userIds) { + // skip current user since we added them already + if (id!=currentUserId) { + if (numAdded > 0) + sb.append(","); + sb.append(id); + numAdded++; + if (max > 0 && numAdded >= max) + break; + } + } + sb.append(")"); + + Cursor c = ReaderDatabase.getReadableDb().rawQuery(sb.toString(), null); + try { + if (c.moveToFirst()) { + do { + long userId = c.getLong(0); + String url = GravatarUtils.fixGravatarUrl(c.getString(1), avatarSz); + // add current user to the top + if (userId==currentUserId) { + avatars.add(0, url); + } else { + avatars.add(url); + } + } while (c.moveToNext()); + } + return avatars; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static ReaderUser getCurrentUser() { + return getUser(AccountHelper.getDefaultAccount().getUserId()); + } + + private static ReaderUser getUser(long userId) { + String args[] = {Long.toString(userId)}; + Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_users WHERE user_id=?", args); + try { + if (!c.moveToFirst()) + return null; + return getUserFromCursor(c); + } finally { + SqlUtils.closeCursor(c); + } + } + + private static String getAvatarForUser(long userId) { + String args[] = {Long.toString(userId)}; + return SqlUtils.stringForQuery(ReaderDatabase.getReadableDb(), "SELECT avatar_url FROM tbl_users WHERE user_id=?", args); + } + + public static ReaderUserList getUsersWhoLikePost(long blogId, long postId, int max) { + String[] args = {Long.toString(blogId), Long.toString(postId)}; + String sql = "SELECT * from tbl_users WHERE user_id IN (SELECT user_id FROM tbl_post_likes WHERE blog_id=? AND post_id=?) ORDER BY display_name"; + if (max > 0) { + sql += " LIMIT " + Integer.toString(max); + } + + Cursor c = ReaderDatabase.getReadableDb().rawQuery(sql, args); + try { + ReaderUserList users = new ReaderUserList(); + if (c.moveToFirst()) { + do { + users.add(getUserFromCursor(c)); + } while (c.moveToNext()); + } + return users; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static ReaderUserList getUsersWhoLikeComment(long blogId, long commentId, int max) { + String[] args = {Long.toString(blogId), + Long.toString(commentId)}; + String sql = "SELECT * from tbl_users WHERE user_id IN" + + " (SELECT user_id FROM tbl_comment_likes WHERE blog_id=? AND comment_id=?)" + + " ORDER BY display_name"; + if (max > 0) { + sql += " LIMIT " + Integer.toString(max); + } + + Cursor c = ReaderDatabase.getReadableDb().rawQuery(sql, args); + try { + ReaderUserList users = new ReaderUserList(); + if (c.moveToFirst()) { + do { + users.add(getUserFromCursor(c)); + } while (c.moveToNext()); + } + return users; + } finally { + SqlUtils.closeCursor(c); + } + } + + private static ReaderUser getUserFromCursor(Cursor c) { + ReaderUser user = new ReaderUser(); + + user.userId = c.getLong(c.getColumnIndex("user_id")); + user.blogId = c.getLong(c.getColumnIndex("blog_id")); + user.setUserName(c.getString(c.getColumnIndex("user_name"))); + user.setDisplayName(c.getString(c.getColumnIndex("display_name"))); + user.setUrl(c.getString(c.getColumnIndex("url"))); + user.setProfileUrl(c.getString(c.getColumnIndex("profile_url"))); + user.setAvatarUrl(c.getString(c.getColumnIndex("avatar_url"))); + + return user; + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/SQLTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/SQLTable.java new file mode 100644 index 000000000..ba73f9ca3 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/SQLTable.java @@ -0,0 +1,68 @@ +package org.wordpress.android.datasets; + +import java.util.Map; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.net.Uri; + +/** + * A class to represent an database table. + */ + +public abstract class SQLTable { + public abstract String getName(); + + protected abstract String getUniqueConstraint(); + + protected abstract Map<String, String> getColumnMapping(); + + protected static class BaseColumns { + protected final static String _ID = "_id"; + } + + public String toCreateQuery() { + String createQuery = "CREATE TABLE IF NOT EXISTS " + getName() + " ("; + + Map<String, String> columns = getColumnMapping(); + + for (String column : columns.keySet()) { + createQuery += column + " " + columns.get(column) + ", "; + } + + createQuery += getUniqueConstraint() + ");"; + + return createQuery; + } + + public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion); + + public Cursor query(final SQLiteDatabase database, final Uri uri, final String[] projection, final String selection, final String[] selectionArgs, final String sortOrder) { + return database.query(getName(), projection, selection, selectionArgs, null, null, sortOrder); + } + + public long insert(final SQLiteDatabase database, final Uri uri, final ContentValues values) { + return database.insert(getName(), null, values); + } + + public long insert(final SQLiteDatabase database, final ContentValues values) { + return insert(database, null, values); + } + + public int update(final SQLiteDatabase database, final Uri uri, final ContentValues values, final String selection, final String[] selectionArgs) { + return database.update(getName(), values, selection, selectionArgs); + } + + public int update(final SQLiteDatabase database, final ContentValues values, final String selection, final String[] selectionArgs) { + return update(database, null, values, selection, selectionArgs); + } + + public int delete(final SQLiteDatabase database, final Uri uri, final String selection, final String[] selectionArgs) { + return database.delete(getName(), selection, selectionArgs); + } + + public int delete(final SQLiteDatabase database, final String selection, final String[] selectionArgs) { + return delete(database, null, selection, selectionArgs); + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/SiteSettingsTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/SiteSettingsTable.java new file mode 100644 index 000000000..e872d4274 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/SiteSettingsTable.java @@ -0,0 +1,104 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; + +import org.wordpress.android.WordPress; +import org.wordpress.android.models.CategoryModel; +import org.wordpress.android.models.SiteSettingsModel; + +import java.util.HashMap; +import java.util.Map; + +public final class SiteSettingsTable { + public static final String CATEGORIES_TABLE_NAME = "site_categories"; + + private static final String CREATE_CATEGORIES_TABLE_SQL = + "CREATE TABLE IF NOT EXISTS " + + CATEGORIES_TABLE_NAME + + " (" + + CategoryModel.ID_COLUMN_NAME + " INTEGER PRIMARY KEY, " + + CategoryModel.NAME_COLUMN_NAME + " TEXT, " + + CategoryModel.SLUG_COLUMN_NAME + " TEXT, " + + CategoryModel.DESC_COLUMN_NAME + " TEXT, " + + CategoryModel.PARENT_ID_COLUMN_NAME + " INTEGER, " + + CategoryModel.POST_COUNT_COLUMN_NAME + " INTEGER" + + ");"; + + public static void createTable(SQLiteDatabase db) { + if (db != null) { + db.execSQL(SiteSettingsModel.CREATE_SETTINGS_TABLE_SQL); + db.execSQL(CREATE_CATEGORIES_TABLE_SQL); + } + } + + public static Map<Integer, CategoryModel> getAllCategories() { + String sqlCommand = sqlSelectAllCategories() + ";"; + Cursor cursor = WordPress.wpDB.getDatabase().rawQuery(sqlCommand, null); + + if (cursor == null || !cursor.moveToFirst() || cursor.getCount() == 0) return null; + + Map<Integer, CategoryModel> models = new HashMap<>(); + for (int i = 0; i < cursor.getCount(); ++i) { + CategoryModel model = new CategoryModel(); + model.deserializeFromDatabase(cursor); + models.put(model.id, model); + cursor.moveToNext(); + } + + return models; + } + + public static Cursor getCategory(long id) { + if (id < 0) return null; + + String sqlCommand = sqlSelectAllCategories() + sqlWhere(CategoryModel.ID_COLUMN_NAME, Long.toString(id)) + ";"; + return WordPress.wpDB.getDatabase().rawQuery(sqlCommand, null); + } + + public static Cursor getSettings(long id) { + if (id < 0) return null; + + String sqlCommand = sqlSelectAllSettings() + sqlWhere(SiteSettingsModel.ID_COLUMN_NAME, Long.toString(id)) + ";"; + return WordPress.wpDB.getDatabase().rawQuery(sqlCommand, null); + } + + public static void saveCategory(CategoryModel category) { + if (category == null) return; + + ContentValues values = category.serializeToDatabase(); + category.isInLocalTable = WordPress.wpDB.getDatabase().insertWithOnConflict( + CATEGORIES_TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_REPLACE) != -1; + } + + public static void saveCategories(CategoryModel[] categories) { + if (categories == null) return; + + for (CategoryModel category : categories) { + saveCategory(category); + } + } + + public static void saveSettings(SiteSettingsModel settings) { + if (settings == null) return; + + ContentValues values = settings.serializeToDatabase(); + settings.isInLocalTable = WordPress.wpDB.getDatabase().insertWithOnConflict( + SiteSettingsModel.SETTINGS_TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_REPLACE) != -1; + + saveCategories(settings.categories); + } + + private static String sqlSelectAllCategories() { + return "SELECT * FROM " + CATEGORIES_TABLE_NAME + " "; + } + + private static String sqlSelectAllSettings() { + return "SELECT * FROM " + SiteSettingsModel.SETTINGS_TABLE_NAME + " "; + } + + private static String sqlWhere(String variable, String value) { + return "WHERE " + variable + "=\"" + value + "\" "; + } +} diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/SuggestionTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/SuggestionTable.java new file mode 100644 index 000000000..27dd698a3 --- /dev/null +++ b/WordPress/src/main/java/org/wordpress/android/datasets/SuggestionTable.java @@ -0,0 +1,173 @@ +package org.wordpress.android.datasets; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; + +import org.wordpress.android.WordPress; +import org.wordpress.android.models.Suggestion; +import org.wordpress.android.models.Tag; +import org.wordpress.android.util.AppLog; +import org.wordpress.android.util.SqlUtils; + +import java.util.ArrayList; +import java.util.List; + +public class SuggestionTable { + private static final String SUGGESTIONS_TABLE = "suggestions"; + private static final String TAXONOMY_TABLE = "taxonomy"; + + public static void createTables(SQLiteDatabase db) { + db.execSQL("CREATE TABLE IF NOT EXISTS " + SUGGESTIONS_TABLE + " (" + + " site_id INTEGER DEFAULT 0," + + " user_login TEXT," + + " display_name TEXT," + + " image_url TEXT," + + " taxonomy TEXT," + + " PRIMARY KEY (user_login)" + + " );"); + db.execSQL("CREATE TABLE IF NOT EXISTS " + TAXONOMY_TABLE + " (" + + " site_id INTEGER DEFAULT 0," + + " tag TEXT," + + " PRIMARY KEY (site_id, tag)" + + " );"); + } + + private static void dropTables(SQLiteDatabase db) { + db.execSQL("DROP TABLE IF EXISTS " + SUGGESTIONS_TABLE); + db.execSQL("DROP TABLE IF EXISTS " + TAXONOMY_TABLE); + } + + public static void reset(SQLiteDatabase db) { + AppLog.i(AppLog.T.SUGGESTION, "resetting suggestion tables"); + dropTables(db); + createTables(db); + } + + private static SQLiteDatabase getReadableDb() { + return WordPress.wpDB.getDatabase(); + } + private static SQLiteDatabase getWritableDb() { + return WordPress.wpDB.getDatabase(); + } + + public static void insertSuggestionsForSite(final int siteId, final List<Suggestion> suggestions) { + // we want to delete the current suggestions, so that removed users will not show up as a suggestion + deleteSuggestionsForSite(siteId); + + if (suggestions != null) { + for (Suggestion suggestion : suggestions) { + addSuggestion(suggestion); + } + } + } + public static void addSuggestion(final Suggestion suggestion) { + if (suggestion == null) + return; + + ContentValues values = new ContentValues(); + values.put("site_id", suggestion.siteID); + values.put("user_login", suggestion.getUserLogin()); + values.put("display_name", suggestion.getDisplayName()); + values.put("image_url", suggestion.getImageUrl()); + values.put("taxonomy", suggestion.getTaxonomy()); + + getWritableDb().insertWithOnConflict(SUGGESTIONS_TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE); + } + + public static List<Suggestion> getSuggestionsForSite(int siteId) { + List<Suggestion> suggestions = new ArrayList<Suggestion>(); + + String[] args = {Integer.toString(siteId)}; + Cursor c = getReadableDb().rawQuery("SELECT * FROM " + SUGGESTIONS_TABLE + " WHERE site_id=? ORDER BY user_login ASC", args); + + try { + if (c.moveToFirst()) { + do { + Suggestion comment = getSuggestionFromCursor(c); + suggestions.add(comment); + } while (c.moveToNext()); + } + + return suggestions; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static int deleteSuggestionsForSite(int siteId) { + return getWritableDb().delete(SUGGESTIONS_TABLE, "site_id=?", new String[]{Integer.toString(siteId)}); + } + + private static Suggestion getSuggestionFromCursor(Cursor c) { + final String userLogin = c.getString(c.getColumnIndex("user_login")); + final String displayName = c.getString(c.getColumnIndex("display_name")); + final String imageUrl = c.getString(c.getColumnIndex("image_url")); + final String taxonomy = c.getString(c.getColumnIndex("taxonomy")); + + int siteId = c.getInt(c.getColumnIndex("site_id")); + + return new Suggestion( + siteId, + userLogin, + displayName, + imageUrl, + taxonomy); + } + + public static void insertTagsForSite(final int siteId, final List<Tag> tags) { + // we want to delete the current tags, so that removed tags will not show up + deleteTagsForSite(siteId); + + if (tags != null) { + for (Tag tag : tags) { + addTag(tag); + } + } + } + + public static void addTag(final Tag tag) { + if (tag == null) + return; + + ContentValues values = new ContentValues(); + values.put("site_id", tag.siteID); + values.put("tag", tag.getTag()); + + getWritableDb().insertWithOnConflict(TAXONOMY_TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE); + } + + public static List<Tag> getTagsForSite(int siteId) { + List<Tag> tags = new ArrayList<Tag>(); + + String[] args = {Integer.toString(siteId)}; + Cursor c = getReadableDb().rawQuery("SELECT * FROM " + TAXONOMY_TABLE + " WHERE site_id=? ORDER BY tag ASC", args); + + try { + if (c.moveToFirst()) { + do { + Tag comment = getTagFromCursor(c); + tags.add(comment); + } while (c.moveToNext()); + } + + return tags; + } finally { + SqlUtils.closeCursor(c); + } + } + + public static int deleteTagsForSite(int siteId) { + return getWritableDb().delete(TAXONOMY_TABLE, "site_id=?", new String[]{Integer.toString(siteId)}); + } + + private static Tag getTagFromCursor(Cursor c) { + final String tag = c.getString(c.getColumnIndex("tag")); + + int siteId = c.getInt(c.getColumnIndex("site_id")); + + return new Tag( + siteId, + tag); + } +} |