I’ve created an application that can create and update documents. A document is a relational database (it’s a SQLite database).
I’m planning to update the structure of the database (add fields, add relations, add tables)
The database stores the database_version in itself. I plan to embed update SQL scripts into the software and apply them depending on the version of the loaded database. Note I will never update or remove tables or fields as I want full retro-compatibility
Is it a good solution or is there a better one?
3
You are basically home-rolling a database migration tool. While the approach is sound, I’m hesitant to propose that you write this yourself. There are a number of tools out there that can handle this for you that are battle-hardened and have been tested thoroughly across hundreds (if not thousands) of applications. I advise you to look into those.
Let me tell you how this is done where I work:
- We develop our database (Firebird) with an IDE (IB Expert).
- When we come to a point where the the changes are considered complete, we create a structural difference script with the IDE by providing the old and the new database.
- We manually extend the diff script with DML statements, if needed.
- Then, a new version of our client application is released. On its startup, it checks the database version (saved in the database) and – if the version is older – it applies the script to the database. When the script was applied successfully, the database version in the database is updated.
This relies completely on the capabilities of our database IDE. SQLite provides a tool sqldiff.exe for diffing two databases.