Mercurial > gemma
view schema/updates/README.md @ 5197:c352dbbf2778 new-fwa
Started with loading limiting factors and validities.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 07 May 2020 17:50:29 +0200 |
parents | a3cbf5a2c1ef |
children |
line wrap: on
line source
# Database schema updates ## Usage DON'T APPLY THE SQL SCRIPTS IN THE DIRECTORIES MANUALLY! Use the script `schema/update-db.sh` instead, this will guaranty consistent database updates and maintain the `gemma_schema_version` table in the database. Running `update-db.sh` will automatically detect which schema updates are necessary and apply them. ## Concept The gemma database schema is versioned. Each update to the schema increments the version number of the schema. To get the version of the schema your database is currently using, you can use the database function `get_schema_version()`: ```sql SELECT get_schema_version(); ``` It is an design goal to allow any older version of a gemma data base to be updated to the most current schema by calling `update-db.sh` from the current gemma distribution. ## Directory Layout the `schema/updates` directory contains subdirectories with names corresponding to the version of the database schema. Each subdirectory contains the necessary SQL scripts to update the database schema from the previous version to the one represented by the directory name. Each script is name according to the scheme: `NN.NAME.sql` where `NN` is a number determining the order of execution and `NAME` is a short string describing what the script implements. ### Example: ``` schema/updates ├── 0000 │ └── 01.add_schema_version.sql ├── 0301 │ ├── 01.dismar-wwname.sql │ └── 02.search_functions.sql └── 1000 └── 01.pwreset.sql ``` ## Adding updates To implement a change to the gemma database schema: - Create a new directory `schema/updates/XXXX` where `XXXX` represents the number of the new database version. The new number must be greater than the current database version. - Create the update scripts in the new subdirectory. When more than one script is used, the numeration in the scripts name determines the order of execution. Note that all scripts in one version update are executed in a single transaction by the `update-db.sh` script. **Important:** all sql scripts in one update directory have to be self contained and *must not* include any files from the regular schema definition (or from other update versions). This is necessary to allow for updates over multiple versions of the schema, where multiple updates might make incremental changes to the same functions/files of the schema. - Modify the scripts in `schema/` (outside the updates directory) to reflect the changes. The schema of a freshly created database using `schema/install-db.sh` must always be identical to the schema of a database updated using `schema/update-db.sh`! - Update `schema/version.sql`, so that it reflects the new schema version you've just created.