4

I am using Android SqliteAssetHelper library (https://github.com/jgilfelt/android-sqlite-asset-helper) I have shipped my database using this library and the database contains some records on table1. Now I want to update my app with a new database with additional records which should be inserted in the one which i already shipped. I am not sure how exactly to write the SQL scripts for the upgrade since the schema is same for both the databases. Did anyone try this?

user1930106
  • 779
  • 1
  • 6
  • 19
  • 2
    "How do i refer to the table of my new db and existing db" -- AFAIK, you don't, at least with the existing `SQLiteAssetHelper`. Either you duplicate the data (having it in new database version for new users and in `INSERT` statements for existing users getting the update) or you apply the same `INSERT` statements for new users to your original database content. Now, `SQLiteAssetHelper` could offer a mode where it copies out the new database somewhere, and you could use `ATTACH DATABASE` in your upgrade script to access it, but I do not think this is supported. – CommonsWare May 16 '16 at 20:26
  • Thanks for the answer. Something simple as inserting records while upgrading should have been supported. Just a suggestion, not complaining though since I did not write it :) – user1930106 May 16 '16 at 20:34
  • I opened a ticket also to check their response on it - https://github.com/jgilfelt/android-sqlite-asset-helper/issues/97#issuecomment-219553819 – user1930106 May 17 '16 at 08:02

2 Answers2

0

After comments by the op in other answers, it was clarified that there are two databases to merge together. The op wants to know if there is a convenient way to merge two databases together with the SQLiteAssetHelper library.

Unfortunately there isn't a direct way to do that because that library also uses the same Android pattern of running a script to modify an existing database.

The workaround is to transform the second database (set of 50 records) into 50 INSERT statements that will get put next to the existing 50. (There are various tools all over the internet to simplify that step so you don't have to do it by hand.) So as long as the business logic can work with them all together they can all go in the original table if the schemas are the same; or if you need them separated, use the 50 INSERTs still still but have them INSERT to a different table name instead.

Then, once you have these 50 INSERT statements with the data of the 50 new rows, put the statements in an upgrade script and you can follow the standard library documentation on how to get that script to run via this library.

Jon Adams
  • 24,464
  • 18
  • 82
  • 120
  • Will it not be easier to just add the records in CSV file, put it in asset folder and then read it into the app database? – user1930106 May 17 '16 at 14:09
  • Perhaps. But then you won't get the database version check feature and you'll have to check when to run or not run the code to import that asset file. It's up to you which solution you feel is better for your project. – Jon Adams May 17 '16 at 15:29
  • I tried the CSV file technique and it seems easier. So to sum up - initially the proloaded database will be copied using SQLiteAssetHelper. But for subsequent adding of records, i will use the CSV file. If there are structural changes in the DB then it can be managed by the SQLiteAssetHelper. Guess this method is fine. – user1930106 May 18 '16 at 11:33
0

You can make this happen by using sqldiff to find the differences between an old DB and a new one.

You call sqldiff on your two databases and pipe the output into a file that conforms to SQL Asset Helper's upgrade format (i.e. <database_name>_upgrade_<from_version>-<to_version>.sql).

So, the whole thing would be sqldiff database.db database_new.db > database.db_upgrade_1-2.sql

Then just make sure that .sql file is in the assets/databases directory and change the version numbers in your Java code (in the example case, from 1 to 2).

djeastm
  • 158
  • 2
  • 8