I guess this comes down to be this your project, or are we talking about wide distribution?
One of my goals was that if my phone were to 100% die, or go bad?
I did not want my data in the database to go “down” with the phone.
So, I place the database in the external folder. (This may, or may not be an actual SD card). In my case it is.
So, when you plug your phone to computer (USB) then you can browser the fodders and data.
If you place the database in the external folder, then that folder is exposed and thus you can copy to/from the android phone to update the database.
However, back to the first issue:
You modified the database.
So, your choices are:
Run some code on start-up (or even in some setup menu area), you simply have to execute some sql commands to add the extra new columns (or tables) to that existing database.
And if say you had to add a table with some data?
Well, then on start-up, simply copy that database into your save default external.
You can then open that database, and pull data from it into the existing database.
So, yes you need some kind of “version” number, or you can perhaps on start-up check for a missing table, (or column) and if not found, then you run those SQL commands to add that column.
So, perhaps as you note a table with “one” row that has some version number etc. could be used. If the database version number is “lower”, then you call those routines.
In fact, it a good habit. Thus for each structure (schema) changes, ALWAYS write a script to modify. (and give that script some kind of version number).
And you could have the version number in your B4A project if you don’t have some version number in the database. (but, it would be better to add a version number table to the wor
Then, if for some reason say you made about 3 changes to the database schema? Well, just run those scripts on start up for all script updates LESS than your current version number.
In fact, I would setup a table. With a few columns. (version number, sql update string etc. just as you suggested).
Thus in theory you update your software, and even if the database is 2-3 versions behind, the all of the “version” update scripts would in fact run, and then you update the version number as the last step.
So yes, in the past – this idea of a table with one row and some version information is a good approach.
So, you can simple loop + process that version update table in that “upgrade.db”.
And while you check for existence of the production database (and not over write), the upgrade.db, you can always over-write the “older” upgrade db each time on startup.
So, your idea(s) outlined are sensible.
I just plug in my phone browse to that application folder, copy the db to my PC and then open up “db browser” for sqlite.
One nasty: (cost me a whole day).
I find that you can copy the database from the phone. But before you copy back, I re-boot the phone. Some phones seem to “cache” the db file, and a file copy did not take.
So, just before I copy from phone:
Copy file from phone to PC
Delete file on phone.
Copy file back to phone,
Now I could probably skip one of the above re-boots, but I don’t have time to check right now – I just do it, and thankfully I don’t do this much often. (but for some reason, some kind of caching was going on here – this was a Moto G or E phone – but I just don’t want you to get bitten by this “nasty”.
So, I copied the db from my phone, make the changes, and then put it back. This works for your person dev cycle.
But, for any kind of users?
Well then your idea of a table + version + sql scripts? Yes, that’s idea.
So add a “upgrade.db” to this mix. You can copy that each time to your default external working folder each time.
And thus process that information into the existing db that you don’t want (and did not) overwrite.
Albert D. Kallal
Edmonton, Alberta Canada