Migrate Room database with Autoincrement Primary Key

Question

I have an Entity User that creates UID automatically upon save to db.

I'm trying to migrate the table but the old UID of the users don't migrate which is a serious problem.

How can I keep the old UID?

 ContentValues cv = null;
            try {
                Cursor c = database.query("SELECT * FROM users");
                if (c.moveToFirst()) {
                    cv = new ContentValues();
                    do {
                        cv.put("uid", c.getLong(c.getColumnIndex("uid")));
                        cv.put("name", c.getString(c.getColumnIndex("name")));                       
                    } while (c.moveToNext());
                }
            } catch (Exception ex) {
                Logger.ex(TAG, ex);
            }

            database.execSQL("DROP TABLE IF EXISTS 'users'");
            database.execSQL("CREATE TABLE IF NOT EXISTS `users` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                    "`name` TEXT)");

            if (cv != null)
                database.insert("users", 0, cv);

Answer 1

You are at best only saving 1 row.

Using the above you would want an Array of ContentValue's to save multiple users.

e.g.

ArrayList<ContentValues> cvlist = new ArrayList<>();

Cursor c = database.query("SELECT * FROM users");
while(c.moveToNext) {
    ContentValue cv = new ContentValue();
    cv.put("uid",c.getLong(c.getColumnIndex("uid")));
    cv.put("name", c.getString(c.getColumnIndex("name")));
    cvlist.add(cv);
}

database.execSQL("DROP TABLE IF EXISTS 'users'");
        database.execSQL("CREATE TABLE IF NOT EXISTS `users` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "`name` TEXT)");
for(ContentValue cv: cvlist) {
    database.insert("users", 0, cv);
}

---

However, you could simplify the above by using :-

database.execSQL("CREATE TABLE IF NOT EXISTS `temp_users` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
            "`name` TEXT)");
database.execSQL("INSERT INTO `temp_users` SELECT * FROM `users`");
database.execSQL("ALTER TABLE `users` RENAME TO `original_users`");
database.execSQL("ALTER TABLE `temp_users` RENAME TO `users`");
database.execSQL("DROP TABLE IF EXISTS `original_users`");

This creates the new table with a different name (temp_users), copies all the rows from the original table into the new table. It then renames the original table, and then renames the new table to the actual name (so now becomes the actual tables that it used) and finally drops the renamed original table.

(you could just drop the original table instead of altering it's name, however the above is safer).

• Note the above is in-principle code, it has not been run or tested and may therefore contains some errors.