Room DB Upgrade need to write full insert query

Question

Hi I am developing an android app and I am using room DB and I have created one table and now I want to add another table I need to write a migration for that and in-migration I need to write full SQL queries for the new table assume I have more than 20 fields how complex will be the query.

In SQLite, we need to write such complex queries which sometimes becomes complicated to write and find errors So, room DB came to resue but now we need to do the same in the room DB migration. How does it useful then?

If we use SQLite, then if we already added one table and now We want to add another table then we can just uninstall the application and new tables will be generated, but in-room DB this is not a case I tried the same thing but it is still showing me that you need to write a migration for the new table. So, in this case, while developing there will a lot of migration scripts that will be hard to maintain at some point in the future.

How does it useful then I have to write a multiple create queries while developing the app this is a very basic flow in any application.

Once we go to prodution then it makes sense to write migration for every table but not in the developing mode.

How does room DB make developr job eaiser?

What is actually your question? – solaza Oct 10 '19 at 07:46 — solaza, Oct 10 '19 at 07:46

score 2 · Answer 1 · answered Oct 10 '19 at 08:34

I have more than 20 fields how complex will be the query.

It can be very simple as an Entity defines an Object e.g. your 20 columns and to get the 20 columns can be as simple as

@Query(SELECT * FROM thetable)
List<Thetable> getAll();

The above being in an Interface that is annotated with @Dao and all you do in the code is retrieve an instance from the built room database and then use the getAll method which returns a List of Thetable objects. Each with all the member variables populated from the database.

e.g. you could have :-

  mMyTheTableDaoObject = mMyBuiltRoomDatabase.getAll();
  List<TheTable> myTheTableList = mMyTheTableDaoObject.getAll();
  for(TheTable t: myTheTableList) {
     int current???? = t.get????();
  }

While using standard/non-room then you would have to do something along the lines of :-

SQLitedatabase db = whatever_you_need_to_do_to_get_an_SQLiteDatabase_instance;
Cursor c = db.query("theTable",null,null,null,null,null,null);
ArrayList<TheTable> myTheTableList  = new ArrayList();
while(c.moveToNext()) {
    currentTheTable = new TheTable();
    current.TheTable.setId = c.getLong(c.getColumnIndex("id");
    current.TheTable.setNextColumn1 = c.getString("next_column1");
    current.TheTable.setNextColumn2 = c.getString("next_column2");
    ........ another 17 similar lines of code
    currentTheTable.setNextColumn20 = c.getString("next_column20"); 
    myTheTableList.add(currentTheTable);
}
for(TheTable t: myTheTableList) {
   int current???? = t.get????();
}

If we use SQLite, then if we already added one table and now We want to add another table then we can just uninstall the application and new tables will be generated, but in-room DB this is not a case I tried the same thing but it is still showing me that you need to write a migration for the new table.

Once we go to production then it makes sense to write migration for every table but not in the developing mode.

Rather then migrating simply delete the database (delete the App's data or uninstall the App, the database is stored in the default location (data/data/package_name/databases)) and rerun without changing the version. The database will be created as per the new schema. Perhaps utilising temporary code to load data accordingly.

How does room DB make developr job eaiser?

In Short ROOM generates what is termed as the boilerplate code from relatively simple code e.g the @Query above writes the underlying code to extract the data and build the objects (e.g. the code as above).

thanks, mike for your such details explanation. I uninstall the application and did not change the DB version number but it is still showing me this error "java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number." How should I achieve this? — Harsh Shah, Oct 10 '19 at 10:33
@HarshShah, without specifics such as the code and the actual steps taken it's hard, perhaps impossible. The message indicates that you have run the app and thus created the database and are then making changes to an existing database that has already been opened by Room (and thus has set the identity hash in the room_master_table). To change the database, without a migration you should uninstall the app and apply the changes to the entities and room will then build the database according to the changed entities. — MikeT, Oct 10 '19 at 23:55
@HarshShah if this answered your question or helped please Tick it to show this. — MikeT, Oct 11 '19 at 05:34

score 1 · Answer 2 · answered Oct 10 '19 at 08:00

1

Please check the official document: https://developer.android.com/training/data-storage/room/migrating-db-versions

Actually Room using SQLITE behind the scene. It provide you plethora of other facilities. In case of Migration you have to write full code to create table.

answered Oct 10 '19 at 08:00

Md. Asaduzzaman

14,963
2
34
46

yes, you are right but what is the meaning of room if we need to do the same thing as like SQLite – Harsh Shah Oct 10 '19 at 10:34
Do you only write query to create table only? I think no. Just check **getAll()** query in Room and Traditional SQLite. You see the difference yourself. Room is better, it doesn't mean Room do everything for you. It definitely reduce some of your task. Thanks – Md. Asaduzzaman Oct 10 '19 at 10:39

score 1 · Answer 3 · answered Oct 10 '19 at 08:38

Harsh your question is valid in some way but as you know android is maintaining SQLite database and libraries like room, greendao or even native SQLiteOpenHelper is handling the transaction with sqllite behind the scene for the developers.

In all the earlier libraries too you have to maintain versions of your database and which fields or tables have been added to your database and write migrations for the version upgrades of the database.

The beauty of room comes in play in how easy they have made the CRUD operations on the SQLite database and getting data wrapped in LiveData or Observable, not that you don't need to write migrations.

Room DB Upgrade need to write full insert query

3 Answers3