ReaderLikeTable.java

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.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, long wpComUserId) {
        if (post == null) {
            return;
        }
        if (isLiked) {
            ContentValues values = new ContentValues();
            values.put("blog_id", post.blogId);
            values.put("post_id", post.postId);
            values.put("user_id", wpComUserId);
            ReaderDatabase.getWritableDb().insert("tbl_post_likes", null, values);
        } else {
            String args[] = {Long.toString(post.blogId), Long.toString(post.postId), Long.toString(wpComUserId)};
            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, long wpComUserId) {
        if (comment == null) {
            return;
        }

        if (isLiked) {
            ContentValues values = new ContentValues();
            values.put("blog_id", comment.blogId);
            values.put("comment_id", comment.commentId);
            values.put("user_id", wpComUserId);
            ReaderDatabase.getWritableDb().insert("tbl_comment_likes", null, values);
        } else {
            String args[] = {Long.toString(comment.blogId),
                             Long.toString(comment.commentId),
                             Long.toString(wpComUserId)};
            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);
        }
    }
}