jeromecc
commented
7 years ago Trial of a new versioning system has begun with patients database (patientbase plugin)
New version table is called SCHEMA_CHANGES:
INSERT INTO SCHEMA_CHANGES (VERSION_NUMBER, SCRIPT_NAME, TIMESTAMP_UTC_APPLIED)
VALUES ('1', 'updatepatients1.sql', UTC_TIMESTAMP());
Schema updates (for both sqlite and msql/mariadb) are performed by sql files that are inside a sql dir. These files are incorporated to the code with Qt (qrc) ressource system so that we don't lose them because of convoluted build scripts.
Each update is performed by a separate script. To get from version x to version y, you need to run all the scripts between x+1 and y. From 2 to 4: run script 3, then script 4. Then database is version n°4. The last query of the script updates the SCHEMA_CHANGES table itself by adding a timestamped row.
We need to keep the first time db creation system as it is because it is intertwined with the way queries are performed by fhio all over the code. In theory, we should write an initial sql script, update scripts of former db version and a baseline script (even if they are not directly used for now) so that we can debug database bugs easily by recreating the desired version quicky. It might also be useful if users want to come from or go back to FreeMedForms.
The current update mechanism using C++ / Qt functions to add or modify databases is highly impractical and messy. Let's use simple sql scripts, one for each new modification of the database. Let's use a simple version number: 1 integer inside a table named SETTINGS Useful guidelines: http://enterprisecraftsmanship.com/2015/08/10/database-versioning-best-practices/