Multiple tables with same structure - Sqlite

Question

I have a table called Messages.But I want to create a separate table for separate users. ex: if my username is Ciddarth I want a table named Ciddarth with the same structure as Messages

public class DbHandler extends SQLiteOpenHelper {

SQLiteDatabase db;
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "SocialNetwork";

// Contacts table name
private String TABLE_MESSAGES = "Messages";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_MESSAGE = "message";
private static final String KEY_SEND = "send";
private static final String KEY_STATUS = "status";
private static final String KEY_TIME = "time";

public DbHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    //TABLE_MESSAGES=name;
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    this.db=db;
    String CREATE_MESSAGE_TABLE = "CREATE TABLE " + TABLE_MESSAGES + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_MESSAGE + " MEDIUMTEXT,"
            + KEY_SEND + " TEXT," + KEY_STATUS + " INTEGER,"+KEY_TIME+" DATETIME DEFAULT (DATETIME(CURRENT_TIMESTAMP, 'LOCALTIME'))"+")";
    db.execSQL(CREATE_MESSAGE_TABLE);
}

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

    // Create tables again
    onCreate(db);
}

/**
 * All CRUD(Create, Read, Update, Delete) Operations
 */

// Adding new contact
void addMessage(Message message) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_MESSAGE, message.getMessage());
    values.put(KEY_SEND, message.getSend());
    values.put(KEY_STATUS, message.getStatus());

    // Inserting Row
    db.insert(TABLE_MESSAGES, null, values);
    db.close(); // Closing database connection
}

// Getting single contact
Message getMessage(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_MESSAGES, new String[] { KEY_ID,
                    KEY_MESSAGE, KEY_SEND }, KEY_ID + "=?",
            new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    Message message = new Message(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1), cursor.getString(2),cursor.getString(4),Integer.parseInt(cursor.getString(3)));
    return message;

}

// Getting All Contacts
public List<Message> getAllContacts() {
    List<Message> msgList = new ArrayList<Message>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_MESSAGES;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Message message = new Message();
            message.setID(Integer.parseInt(cursor.getString(0)));
            message.setMessage(cursor.getString(1));
            message.setSend(cursor.getString(2));
            message.setStatus(cursor.getInt(3));
            message.setTime(cursor.getString(4));
            // Adding contact to list
            msgList.add(message);
        } while (cursor.moveToNext());
    }

    // return contact list
    return msgList;
}

// Updating single contact
public int updateMessage(Message message) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_MESSAGE, message.getMessage());
    values.put(KEY_SEND, message.getSend());

    // updating row
    return db.update(TABLE_MESSAGES, values, KEY_ID + " = ?",
            new String[] { String.valueOf(message.getID()) });
}

// Deleting single contact
public void deleteMessage(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_MESSAGES, KEY_ID + " = ?",
            new String[] { String.valueOf(id) });
    db.close();
}


// Getting contacts Count
public int getMessageCount() {
    String countQuery = "SELECT  * FROM " + TABLE_MESSAGES;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();
}

public void deleteAll()
{
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("delete from "+ TABLE_MESSAGES);
    db.close();
}

public void deleteTable()
{
    SQLiteDatabase db = this.getReadableDatabase();
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGES);
    db.close();
}

public void changeStatus(int status,int id)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(KEY_STATUS,status);
    db.update(TABLE_MESSAGES, cv, "id="+id, null);
    db.close();
}
}

Now I want to change the TABLES_MESSAGES related to the user how to I do this?? If I change the value of TABLE_MESSAGES to Ciddarth it still doesn't work.

Answer 1

Make a seperate method...

`createNewTable(String tablename){
     SqliteOpenDatabase db = this.getWriteableDatabase();
     String CREATE_MESSAGE_TABLE = "CREATE TABLE " + tablename + "("
         + KEY_ID + " INTEGER PRIMARY KEY," + KEY_MESSAGE + " MEDIUMTEXT," 
         + KEY_SEND + " TEXT," + KEY_STATUS + " INTEGER,"+KEY_TIME+
         "DATETIME DEFAULT (DATETIME(CURRENT_TIMESTAMP, 'LOCALTIME'))"+")";
 db.execSQL(CREATE_MESSAGE_TABLE);`

and add String tablename to other CRUD methods.

Edit: Forgot this.getWritableDatabase();