2

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

nCoder
  • 53
  • 5
  • Why are you deleting the data? Are there more rows than the pre-populated version? Is the objective to basically just add the Surnames? The reasoning as to why could affect the path you take. Perhaps edit your question to elaborate. – MikeT Oct 06 '21 at 22:28
  • Hello @MikeT I updated my explanation. I want to update/delete all the data because I need to do changes in the "description" column and also the "name" column – nCoder Oct 06 '21 at 22:45

1 Answers1

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;
}

  1. 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

  2. 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)

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

    1. enter image description here
  • note that the surname column is populated with nulls.

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

    1. enter image description here

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

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

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

  5. Quit SQLite Studio

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

    1. enter image description here

  7. 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)

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

    1. enter image description here
MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Thank you so much, I figured it out with your help . You have helped me in two of my questions. Your answers are detailed and very understanding. Thank you again – nCoder Oct 07 '21 at 13:33