I'm working on an Android app where we need to migrate from some legacy SQLite code to a Room based implementation. But I've run into a couple of issues & I'm not sure how to solve them.
Environment:
- Android 6.0+
- SQLite implementation is in one database file
- Room implementation is in a separate database file
- Both will be in /data/user/0/.../databases/
What sorta works, but is slow:
We have a working test implementation to pull all the legacy SQLite tables' data into Kotlin objects, then do inserts into the Room based tables. But, it's fairly slow for our test cases (about 70 secs for 10k+ rows in at least one table). Real life cases could top 100k rows in at least one table. As far as I can tell, the select is one transaction and the inserts are wrapped in a transaction. So, I don't think it's transaction overhead slowing us down.
What we want to make work:
What I'm hoping to do is use SQLite's attach database and detach commands to load the legacy SQLite database into a Room DB connection. Then, I can do an insert into new_table.table_name select * from old_table.table_name. The actual SQL will be slightly different due to slight schema changes. But, the insert-select pattern will be the basic idea.
I'm using ContextWrapper to get the full database path for the legacy database file. That works fine.
What issues I'm seeing:
When I try to do the attach command, though, I was getting an IllegalStateException with something about not being able to enable/disable write-ahead logging while in a transaction or the db is open. But, the legacy DB is not open or in a transaction at that point.
So, I modified the approach to set the journal mode to TRUNCATE in the Room db connection setup. That allowed me to do the attach command. And the insert-select query seems to work as far as I can tell (I haven't fully tested that yet). BUT, when I tried the detach command, I got a SQLite 1 error about the database being locked.
For reference, my basic code for both above attach approaches is:
roomDb.execSQL("attach database '$fullLegacyDbPath' as old_db")
roomDb.execSQL($insertSelectQuery) // the "INSERT INTO ... SELECT ..." query
roomDb.execSQL("detach old_db")
Any ideas how I make this work (preferably without setting the journal mode to truncate)?