MessageDatabaseHelper.java :  » Client » android-gadu » pl » szpadel » android » gadu » Android Open Source

package pl.szpadel.android.gadu;

import java.io.File;
import java.util.Collection;
import java.util.LinkedList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

/// Manages message database
public class MessageDatabaseHelper {
  
  private final static String TAG = "MessageDatabaseHelper";
  
  private MessageDatabaseOpenHelper mOpenHelper; //< Open helper
  
  /// message record
  public class Message {
    public long sender;
    public long recipient;
    public int timestamp;
    public boolean delivered;
    public String text;
  };
  
  /// C-tor
  public MessageDatabaseHelper(Context ctx) {
    // build db path (on external storage
    File dir = new File(Environment.getExternalStorageDirectory(), "AndroidGadu");
    dir.mkdirs();
    File dbfile = new File(dir, "messages.sqlite");
    
    mOpenHelper = new MessageDatabaseOpenHelper(ctx, dbfile.getAbsolutePath());
  }
  
  /// Adds message to the DB
  public void addMessage(long sender, long recipient, int timestamp, boolean delivered, String text) {
    int deliveredInt = delivered ? 1 : 0;
    String insertQuery = 
      "INSERT INTO " + MessageDatabaseOpenHelper.TABLE_MESSAGES + " ( " +
      MessageDatabaseOpenHelper.COLUMN_SENDER + "," +
      MessageDatabaseOpenHelper.COLUMN_RECIPIENT + "," +
      MessageDatabaseOpenHelper.COLUM_TIMESTAMP + "," +
      MessageDatabaseOpenHelper.COLUMN_TEXT + "," +
      MessageDatabaseOpenHelper.COLUMN_DELIVERED + ") VALUES (" +
      sender + "," +
      recipient + "," +
      timestamp + "," +
      "'" + text + "', " +
      deliveredInt + ");";
    
    try {
      SQLiteDatabase db = mOpenHelper.getWritableDatabase();
      db.execSQL(insertQuery);
    } catch (Exception e) {
      AGLog.e(TAG, "Error inserting message to DB, error=" + e + "query=" + insertQuery);
    }
  }
  
  /// Sets delivered flag to true for all messages between specific customers
  public void setDelivered(long sender, long recipient) {
    String updateQuery =
      "UPDATE " + MessageDatabaseOpenHelper.TABLE_MESSAGES + " SET " +
        MessageDatabaseOpenHelper.COLUMN_DELIVERED + "=1 WHERE " +
        MessageDatabaseOpenHelper.COLUMN_SENDER + "=" + sender + " AND " +
        MessageDatabaseOpenHelper.COLUMN_RECIPIENT + "=" + recipient + ";";
    try {
      SQLiteDatabase db = mOpenHelper.getWritableDatabase();
      db.execSQL(updateQuery);
    } catch (Exception e) {
      AGLog.e(TAG, "Error updating message in DB, error=" + e + "query=" + updateQuery);
    }
  }
  
  /// Sets delivered flag to true for message between users with specific timestamp (seq number)
  public void setDelivered(long sender, long recipient, int timestamp) {
    String updateQuery =
      "UPDATE " + MessageDatabaseOpenHelper.TABLE_MESSAGES + " SET " +
        MessageDatabaseOpenHelper.COLUMN_DELIVERED + "=1 WHERE " +
        MessageDatabaseOpenHelper.COLUMN_SENDER + "=" + sender + " AND " +
        MessageDatabaseOpenHelper.COLUMN_RECIPIENT + "=" + recipient + " AND " +
        MessageDatabaseOpenHelper.COLUM_TIMESTAMP + "=" + timestamp + ";";
    try {
      SQLiteDatabase db = mOpenHelper.getWritableDatabase();
      db.execSQL(updateQuery);
    } catch (Exception e) {
      AGLog.e(TAG, "Error updating message in DB, error=" + e + "query=" + updateQuery);
    }
  }
  
  /// Returns last X messages from specific conversation, in chronological order
  Collection<Message> getMessages(long user1, long user2, int limit) {
    LinkedList<Message> result = new LinkedList<Message>();
    
    // construct query
    String queryText =
      "SELECT * FROM " + MessageDatabaseOpenHelper.TABLE_MESSAGES + " WHERE " +
      "(" + MessageDatabaseOpenHelper.COLUMN_SENDER + "=" + user1 + " AND " + MessageDatabaseOpenHelper.COLUMN_RECIPIENT + "=" + user2 +
      ") OR (" +
      MessageDatabaseOpenHelper.COLUMN_RECIPIENT + "=" + user1 + " AND " + MessageDatabaseOpenHelper.COLUMN_SENDER + "=" + user2 + ") " +
      "ORDER BY " + MessageDatabaseOpenHelper.COLUM_TIMESTAMP + " DESC " +
      "LIMIT " + limit +";";
    
    try {
      SQLiteDatabase db = mOpenHelper.getReadableDatabase();
      
      Cursor c = db.rawQuery(queryText, null);
      if (c.moveToFirst()) {
        do {
          Message msg = new Message();
          msg.sender = c.getLong(c.getColumnIndex(MessageDatabaseOpenHelper.COLUMN_SENDER));
          msg.recipient = c.getLong(c.getColumnIndex(MessageDatabaseOpenHelper.COLUMN_RECIPIENT));
          msg.timestamp = c.getInt(c.getColumnIndex(MessageDatabaseOpenHelper.COLUM_TIMESTAMP));
          msg.delivered  = (c.getInt(c.getColumnIndex(MessageDatabaseOpenHelper.COLUMN_DELIVERED)) != 0);
          msg.text = c.getString(c.getColumnIndex(MessageDatabaseOpenHelper.COLUMN_TEXT));
          
          result.addFirst(msg); // push in front
          
        } while(c.moveToNext());
      }
      
    } catch (Exception e) {
      AGLog.e(TAG, "Error reading messages from DB, error=" + e + ", query=" + queryText);
    }
    
    return result;
  }
}