Merge remote-tracking branch 'origin/upstream-master' into masterHEADstudio-3.4.0studio-3.2.1studio-3.1.2studio-3.0studio-2.3gradle_3.4.0gradle_3.1.2gradle_3.0.0gradle_2.3.0studio-master-devmirror-goog-studio-master-devmastermain

Change-Id: I63f5e16d09297c48432192761b840310935eb903

1 files changed, 419 insertions, 0 deletions

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);
+    }
+}