Mercurial > gemma
changeset 3994:0eb6b5fbeea3
Added documentation to database updates.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 17 Jul 2019 16:08:09 +0200 |
parents | 6672b780722f |
children | 074cf9349c71 |
files | schema/updates/README.md |
diffstat | 1 files changed, 72 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/README.md Wed Jul 17 16:08:09 2019 +0200 @@ -0,0 +1,72 @@ +# 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(); +``` + +## 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. + +- 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 just created.