If you do decide to go for coronium core, i can give you some pointers regarding the security.
Been working a lot with this lately 
Hi Nick,
Thanks for the response!
During the update, there is no real structure change.
The newly copied master database may contain more rows if new coins have been added or slightly adjusted information from already existing fields but there are never any new columns or tables.
As an example… The master database contains about 600 rows of coin data in a coins table with about 15 columns consisting of things like date, mintage, description, obverse_image, reverse_image, acquired, used_quantity, new_quantity, notes… ect
During an update we might add 5 new rows and update a few pieces of information for 30 or so already existing rows, like the mintage figures or a typo in a description.
There are only 4 pieces of information which the user can change… acquired, used_quantity, new_quantity and notes.
This is the data that gets stored in the arrays, then written back to the new fresh master that gets copied to the documents directory.
In the arrays I always store the coin id against the piece of information so when it comes to writing back to a new master… I just update the columns I need to using the stored id and the piece of information, if that makes sense :/.
Kind Regards,
Krivvenz.
Everytime something changes in the master DB that ships with the app, you could store the insert/update command that generated the change, along with the version number. If the version is greater than the users, loop through the commands to update their DB.
Hi Nick,
That too sounds a good solution, I can see how having like a switch statement on the database version number along with all the various update statements that go for that particular version, the only problem with this is that it will involve a lot of typing of statements to amend the values which I currently do directly in DBBrowser for SQLite.
It’s certainly a possibility but in each update there could be 30 - 50 updates along with whole new rows added and maybe its my inexperience with databases but to me, it sounds quite messy? (Never having been involved in a proper database migration - maybe this is industry standard normal practice and where my problem lies?!).
Someone else suggested the following but I’m not sure if its doable…
Basically it involves separating out the user data into a new table.
user_data_table
coin_id (FK), acquired, new_quantity, used_quantity and notes
coin_table
id (PK), desc, image_reverse, image_obverse, mintage ect…
Then during an update, just perform a DROP on the coin table and COPY the new coin table from the master database.
It sounds quite good in theory but having a quick look I can’t actually see any commands to easily copy a whole table from one DB to another in Corona SDK… I’ll keep researching though. I think if anything happened during the DROP there would be no way to roll back either :(.
Hmmm what to try!!!
Kind Regards,
Mike.
As for typing statements, there is a program called sqldiff into which you should be able to feed in the previous and current versions of your master DB, and it will spit out the statements required to bring them in line. Not tried it myself though.
If I were developing an app like this, I would perhaps consider not updating the master database at all, and store any user updates in a separate db. Basically, use the master db as a read-only db, open the user’s db and grab the user data based on a key.
there should never be a single nanosecond where persisted (user) data isn’t somewhere on disk. rename or copy the current database to a backup for the duration of the update operation. only after the new/updated version has been written AND read-verified should you remove the backup. if any error during update process, then opposite: remove the update and restore backup.