path: root/WordPress/src/main/java/org/wordpress/android/datasets/CommentTable.java

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