Android Open Source - MySms Database Handler

From Project

Back to project page MySms.
License

The source code is released under:
Apache License
If you think the Android project MySms listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.
Java Source Code

package com.henningta.mysms;
/*from  w w w .j av a  2  s. c o  m*/
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

/**
 * Class to help with creation and modification of local database
 */
public class DatabaseHandler extends SQLiteOpenHelper {

  /**
   * Database name
   */
  private static final String DATABASE_NAME = "sms_manager";

  /**
   * Database version
   */
  private static final int DATABASE_VERSION = 7;

  /**
   * Conversations table name
   */
  private static final String TABLE_CONVERSATIONS = "conversations";
  /**
   * Messages table name
   */
  private static final String TABLE_MESSAGES = "messages";
  /**
   * Notifications table name
   */
  private static final String TABLE_NOTIFICATIONS = "notifications";

  public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    final String CREATE_TABLE_CONVERSATIONS =
        "create table if not exists " + TABLE_CONVERSATIONS + " (" +
            Settings.KEY_SOURCE + " text not null, " +
            Settings.KEY_NAME + " text not null, " +
            Settings.KEY_HAS_NEW_MESSAGES + " int not null, " +
            Settings.KEY_TIME + " long not null, " +
            Settings.KEY_IS_CONTACT + " long not null, " +
            "primary key (" + Settings.KEY_SOURCE + ")" +
            ");";

    final String CREATE_TABLE_MESSAGES =
        "create table if not exists " + TABLE_MESSAGES + " (" +
            Settings.KEY_SOURCE + " integer not null, " +
            Settings.KEY_MESSAGE + " text not null, " +  // TODO 160 limit causing multi-part sms issues? EDIT: changed to text... check results
            Settings.KEY_TIME + " long not null, " +
            Settings.KEY_STATUS + " int not null, " +
            "primary key (" + Settings.KEY_SOURCE + "," + Settings.KEY_MESSAGE + "," +
            Settings.KEY_TIME + "), " +
            "foreign key (" + Settings.KEY_SOURCE + ") " +
            "references " + TABLE_CONVERSATIONS + " (" + Settings.KEY_SOURCE + ")" +
            ");";

    final String CREATE_TABLE_NOTIFICATIONS =
        "create table if not exists " + TABLE_NOTIFICATIONS + " (" +
            Settings.KEY_SOURCE + " text not null, " +
            Settings.KEY_COUNT + " int not null, " +
            "primary key (" + Settings.KEY_SOURCE + "), " +
            "foreign key (" + Settings.KEY_SOURCE + ") " +
            "references " + TABLE_CONVERSATIONS + " (" + Settings.KEY_SOURCE + ")" +
            ");";

    db.execSQL(CREATE_TABLE_CONVERSATIONS);
    db.execSQL(CREATE_TABLE_MESSAGES);
    db.execSQL(CREATE_TABLE_NOTIFICATIONS);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    /*
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONVERSATIONS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGES);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTIFICATIONS);

    // Create tables again
    onCreate(db);
    */
  }

  /* ----------------------- */
  /* Conversation operations */
  /* ----------------------- */

  /**
   * Add conversation to conversations table
   */
  public void addConversation(Conversation conversation) {
    SQLiteDatabase db = this.getWritableDatabase();

    // set values
    ContentValues values = new ContentValues();
    values.put(Settings.KEY_SOURCE, conversation.getSource());
    values.put(Settings.KEY_NAME, conversation.getNameSql());
    values.put(Settings.KEY_HAS_NEW_MESSAGES, conversation.hasNewMessages() ? 1 : 0);
    values.put(Settings.KEY_TIME, conversation.getTime());
    values.put(Settings.KEY_IS_CONTACT, conversation.isContact());

    // insert values
    db.insert(TABLE_CONVERSATIONS, null, values);

    db.close();
  }

  /**
   * Get single conversation by source
   */
  public Conversation getConversation(String source) {
    SQLiteDatabase db = this.getReadableDatabase();

    String query = "select * from " + TABLE_CONVERSATIONS +
        " where " + Settings.KEY_SOURCE + "=?";

    // query
    Cursor c = db.rawQuery(query, new String[] { source });

    // get conversation
    Conversation conversation = null;
    if (c.moveToFirst()) {
      conversation = new Conversation(
          c.getString(c.getColumnIndex(Settings.KEY_SOURCE)),
          c.getString(c.getColumnIndex(Settings.KEY_NAME)).replace("''", "'"),
          c.getInt(c.getColumnIndex(Settings.KEY_HAS_NEW_MESSAGES)) == 1,
          c.getLong(c.getColumnIndex(Settings.KEY_TIME)));
    }

    c.close();
    db.close();
    return conversation;
  }

  /**
   * Get all conversations
   */
  public ArrayList<Conversation> getConversationList() {
    ArrayList<Conversation> conversationList = new ArrayList<Conversation>();

    // Select All Query
    String selectQuery = "SELECT * FROM " + TABLE_CONVERSATIONS;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
      do {
        Conversation conversation = new Conversation(
            c.getString(c.getColumnIndex(Settings.KEY_SOURCE)),
            c.getString(c.getColumnIndex(Settings.KEY_NAME)).replace("''", "'"),
            c.getInt(c.getColumnIndex(Settings.KEY_HAS_NEW_MESSAGES)) == 1,
            c.getLong(c.getColumnIndex(Settings.KEY_TIME)));

        // Adding contact to list
        conversationList.add(conversation);
      } while (c.moveToNext());
    }

    c.close();
    db.close();

    return conversationList;
  }

  /**
   * Update conversation in conversations table
   */
  public int updateConversation(Conversation conversation) {
    SQLiteDatabase db = this.getWritableDatabase();

    // set values
    ContentValues values = new ContentValues();
    values.put(Settings.KEY_SOURCE, conversation.getSource());
    values.put(Settings.KEY_NAME, conversation.getNameSql());
    values.put(Settings.KEY_HAS_NEW_MESSAGES, conversation.hasNewMessages() ? 1 : 0);
    values.put(Settings.KEY_TIME, conversation.getTime());
    values.put(Settings.KEY_IS_CONTACT, conversation.isContact());

    // updating row
    int i = db.update(
        TABLE_CONVERSATIONS,
        values,
        Settings.KEY_SOURCE + "=?",
        new String[] { conversation.getSource() });

    // close database
    db.close();
    return i;
  }

  /**
   * Delete conversation with matching source
   */
  public void deleteConversation(String source) {
    SQLiteDatabase db = this.getWritableDatabase();

    // remove conversation with matching id
    db.delete(
        TABLE_CONVERSATIONS,
        Settings.KEY_SOURCE + " = ?",
        new String[] { source });

    // close database
    db.close();
  }

  /* ------------------ */
  /* Message operations */
  /* ------------------ */

  /**
   * Add message to messages table
   */
  public void addMessage(Message message) {
    SQLiteDatabase db = this.getWritableDatabase();

    // set values
    ContentValues values = new ContentValues();
    values.put(Settings.KEY_SOURCE, message.getSource());
    values.put(Settings.KEY_MESSAGE, message.getText());
    values.put(Settings.KEY_TIME, message.getTime());
    values.put(Settings.KEY_STATUS, message.getStatus());

    // insert values
    db.insert(TABLE_MESSAGES, null, values);

    db.close();
  }

  /**
   * Get message by source and time
   */
  public Message getMessage(String source, String text, long time) {
    SQLiteDatabase db = this.getReadableDatabase();

    String query = "select * from " + TABLE_MESSAGES +
        " where " + Settings.KEY_SOURCE + "=? and " + Settings.KEY_MESSAGE + "=? and " +
        Settings.KEY_TIME + "=?";

    // find message
    Cursor c = db.rawQuery(query, new String[] { source, text, Long.toString(time) });

    // return message
    Message message = null;
    if (c.moveToFirst()) {
      message = new Message(
          c.getString(c.getColumnIndex(Settings.KEY_SOURCE)),
          c.getString(c.getColumnIndex(Settings.KEY_MESSAGE)),
          c.getLong(c.getColumnIndex(Settings.KEY_TIME)),
          c.getInt(c.getColumnIndex(Settings.KEY_STATUS)));
    }

    db.close();
    c.close();
    return message;
  }

  /**
   * Get all messages for source
   */
  public ArrayList<Message> getMessages(String source) {
    ArrayList<Message> messageList = new ArrayList<Message>();

    // Select All Query
    String selectQuery = "select * from " + TABLE_MESSAGES +
        " where " + Settings.KEY_SOURCE + "=? order by " + Settings.KEY_TIME;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, new String[] { source });

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
      do {
        Message message = new Message(
            c.getString(c.getColumnIndex(Settings.KEY_SOURCE)),
            c.getString(c.getColumnIndex(Settings.KEY_MESSAGE)),
            c.getLong(c.getColumnIndex(Settings.KEY_TIME)),
            c.getInt(c.getColumnIndex(Settings.KEY_STATUS)));

        // Adding message to list
        messageList.add(message);
      } while (c.moveToNext());
    }

    db.close();
    c.close();

    // return message list
    return messageList;
  }

  /**
   * Update message in messages table
   */
  public int updateMessage(Message message) {
    SQLiteDatabase db = this.getWritableDatabase();

    // set values
    ContentValues values = new ContentValues();
    values.put(Settings.KEY_SOURCE, message.getSource());
    values.put(Settings.KEY_MESSAGE, message.getText());
    values.put(Settings.KEY_TIME, message.getTime());
    values.put(Settings.KEY_STATUS, message.getStatus());

    // update row
    int i = db.update(
        TABLE_MESSAGES,
        values,
        Settings.KEY_SOURCE + "=? and " +
            Settings.KEY_MESSAGE + "=? and " +
            Settings.KEY_TIME + "=?",
        new String[] { message.getSource(), message.getText(), Long.toString(message.getTime()) });

    // close database
    db.close();
    return i;
  }

  /**
   * Delete message
   */
  public void deleteMessage(Message message) {
    deleteMessage(message.getSource(), message.getText(), message.getTime());
  }

  /**
   * Delete message with matching source and time
   */
  public void deleteMessage(String source, String text, long time) {
    SQLiteDatabase db = this.getWritableDatabase();

    // remove message with matching id
    db.delete(
        TABLE_MESSAGES,
        Settings.KEY_SOURCE + "=? and " +
            Settings.KEY_MESSAGE + "=? and " +
            Settings.KEY_TIME + "=?",
        new String[] { source, text, Long.toString(time) });

    // close database
    db.close();
  }

  /**
   * Delete all messages for a given conversation
   */
  public void deleteMessages(String source) {
    SQLiteDatabase db = this.getWritableDatabase();

    // remove message with matching id
    db.delete(
        TABLE_MESSAGES,
        Settings.KEY_SOURCE + "=?",
        new String[] { source });

    // close database
    db.close();
  }

  /* ----------------------- */
  /* Notification operations */
  /* ----------------------- */

  /**
   * Add message to messages table
   */
  public void addNotificationSource(String source) {
    SQLiteDatabase db = this.getWritableDatabase();

    // set values
    ContentValues values = new ContentValues();
    values.put(Settings.KEY_SOURCE, source);
    values.put(Settings.KEY_COUNT, 1);

    // insert values
    db.insert(TABLE_NOTIFICATIONS, null, values);

    db.close();
  }

  public ArrayList<String> getNotificationSources() {
    ArrayList<String> sources = new ArrayList<String>();

    String select = "select " + Settings.KEY_SOURCE + " from " + TABLE_NOTIFICATIONS;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(select, null);

    if (c.moveToFirst()) {
      do {
        String source = c.getString(c.getColumnIndex(Settings.KEY_SOURCE));
        sources.add(source);
      } while (c.moveToNext());
    }

    c.close();
    db.close();
    return sources;
  }

  public ArrayList<String> getNotificationNames() {
    ArrayList<String> names = new ArrayList<String>();

    // Select All Query
    String selectQuery = "select " + Settings.KEY_NAME + " from " + TABLE_CONVERSATIONS + " c " +
        "join " + TABLE_NOTIFICATIONS + " n " +
        "on c." + Settings.KEY_SOURCE + "=n." + Settings.KEY_SOURCE;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
      do {
        String name = c.getString(c.getColumnIndex(Settings.KEY_NAME));
        names.add(name.replace("''", "'"));
      } while (c.moveToNext());
    }

    c.close();
    db.close();

    return names;
  }

  public boolean containsNotificationSource(String source) {
    return getNotificationSources().contains(source);
  }

  public void incrementNotificationCount(String source) {
    SQLiteDatabase db = this.getWritableDatabase();

    final String query = "update " + TABLE_NOTIFICATIONS +
        " set " + Settings.KEY_COUNT + "=" + Settings.KEY_COUNT + "+1" +
        " where " + Settings.KEY_SOURCE + "=?";

    db.execSQL(query, new String[] { source });

    db.close();
  }

  /**
   * Delete all notification
   */
  public void deleteNotifications() {
    SQLiteDatabase db = this.getWritableDatabase();

    // remove message with matching id
    db.delete(
        TABLE_NOTIFICATIONS,
        null,
        null);

    // close database
    db.close();
  }

  /**
   * Get notification count
   */
  public int getNotificationCount() {
    // return count
    return getNotificationCount(null);
  }

  /**
   * Get notification count by specified id
   */
  public int getNotificationCount(String source) {
    // Select All Query
    String selectQuery = "select * from " + TABLE_NOTIFICATIONS;
    String[] args = null;

    if (source != null) {
      selectQuery += " where " + Settings.KEY_SOURCE + "=?";
      args = new String[] { source };
    }

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, args);

    int count = 0;

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
      do {
        count += c.getInt(c.getColumnIndex(Settings.KEY_COUNT));
      } while (c.moveToNext());
    }

    c.close();
    db.close();

    // return count
    return count;
  }

}
Java Source Code List
