A few years ago we developed a SQLite authoring tool named SmartData. A recap at the following link…
We’ve decided to donate the source code to public domain. We’ve mainly used it for Windows 32-bit compiles, but @Jean-Yves_Pochez confirmed that it also compiles successfully on macos arm 64 bits.
Please feel free to use the code however you see fit.
We created a similar tool for our in-house development. Most, if not all, of our applications are multi-client accounting applications, so we make structural changes very frequently with new applications. In addition to creating databases, tables, fields, and exporting options, it will also generate what we call a DBVERSION table that is compiled into our applications. This table contains the current version of the database and when a user selects a database they created with our applications, the first thing it does is check to see if the structure is up to date by comparing the version number in our DBVERSION (which is located where ever their EXE is in Windows and in the Resources folder on a Mac) with the version number in the DBVERSION we add the databases they create.
If the database needs to be updated, we provide a message that their database needs to updated and it will automatically make a backup of their current database, then create a new one and import all their data from the old one into the new one… then delete the old one and rename the new one to the correct name. We’ve generalized all the importing so it is pretty bullet proof.
It’s quite a process but an absolutely necessity if your users have the ability to create an unlimited number of databases. We’ve enhanced it and used it for over a decade and it has made it easy to make structural changes, test them out in house, then when we are ready we post a new version of our application with the updated DBVERSION table. When they install the new version, it will compare versions and do the updating that will make their database compatible with the updated application.
Anyone else had to something like this.
I hit the same problem aka “DBVERSION”; unfortunately, not only database structure could be changed but also the way of working with that database by application itself, i.e. from given version application would perform additional inserts into another table for some reasons. So I have given up the solution of “DBVERSION” and replace it by “minimum version” of database and application separately. Now, when you run the application, it check with my main database if it is being the minimum version allowed to run - if not - online upgrade if offered. Then the application check the local database version, if it is lower than acceptable one by the current application - it will perform the process of backup/update structure/restore.
I can’t remember a time when we modified our database structure without also modifying the application to take advantage of those changes to the database. In face, changes to the application is what drives the changes required to the database so the two go hand-in-hand. In our case, the minimum DB structure is the current one.