Delete old table and create new one and prepopulate it - RoomDatabase

Question

I am creating a simple android application in android studio with java. I have a roomdatabase db with a table named user that has four columns name, profession, age, description and I have prepopulated using a database that i made in sqlite studio.

Now, I want to add a column surname on the table but I want to delete all the prepopulated data and prepopulate again the table with a new database that contains also surname.

At first I thought to use auto migrations and to just add a new column. But i don't know how delete all the existing data and prepopulate again the database.

I want to delete the existing data because i want to change all the info that exists in the column description. Also as concern as the column name now it contains the fullname and in some cases is written line "name surname" and other times like "surname name" e.x "Will Smith" "Smith Will". Now I want to have the name and the surname in separate columns name and surname

Could someone recommend me something? Thank you in advance

Answer 1

AutoMigration will not cope with amending the data itself. Thus you will have to do that manually and thus use a destructive migration.

Here's a example (as can be seen actually undertaken)

• Note this assumes no version or version 1 was assigned to the original pre-populated database and also that 1 was used for the version passed to the @Database annotation.

  • an SQLite database has, as part of it's header, a user_version number (offset 60 for 4 bytes). It is comparing this to the version passed to Room that determines the migration/auto migration. As is seen changing this is critical if migrating.

  • If developing you could just start from the changed database by making the changes to the database and the App and uninstalling the App. No need to play with version numbers.

1. Amend the User class by adding the new surname column. e.g.

:-

@Entity
class User {
    @PrimaryKey
    @NonNull
    String name;
    String profession;
    int age;
    String description;
    /* ADDED FOR V2 */
    String surname;
}

2. Compile (Ctrl + F9) and then from Android View located the generated Java and then the class that is then same as the @Database class but suffixed with _Impl.

   1. locate the createAllTables method and then the SQL for the User table. Make a note of the SQL and the definition for the new column e.g. -surname TEXT

3. In SQLite Studio, run the following SQL:- ALTER TABLE user ADD COLUMN surname TEXT; where the text/code after the COLUMN key word is EXACTLY as per the SQL noted above (you can include or omit the enclosing `'s around the column name, they aren't easy to display in SO)

4. Look at the Data e.g. it will now be :-

   1. 

• note that the surname column is populated with nulls.

5. Edit the data accordingly. e.g. :-

   1. 

6. Then run the following SQL PRAGMA user_version; (to check the current version)

7. Then run the following SQL PRAGMA user_version = 2; ( to change the version (guessing 2))

8. Then run the following SQL PRAGMA user_version; (to check that the version is now 2)

9. Quit SQLite Studio

10. Replace the file/asset in the project with the new database. e.g. :-

    1. 

11. In the @Database class (NOT the generated java) :-

    1. change the database version to 2
    2. add the following to the the databaseBuild :-
       1. either .fallbackToDestructiveMigrationFrom(1) if going from 1 to 2
       2. or .fallbackToDestructiveMigration() (not as safe but more encompassing)

12. Run the App and e.g. :-

    1.