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