Mercurial > gemma
comparison schema/updates/README.md @ 3994:0eb6b5fbeea3
Added documentation to database updates.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 17 Jul 2019 16:08:09 +0200 |
parents | |
children | f2f0b776ba12 |
comparison
equal
deleted
inserted
replaced
3993:6672b780722f | 3994:0eb6b5fbeea3 |
---|---|
1 # Database schema updates | |
2 | |
3 ## Usage | |
4 | |
5 DON'T APPLY THE SQL SCRIPTS IN THE DIRECTORIES MANUALLY! | |
6 | |
7 Use the script `schema/update-db.sh` instead, this will guaranty | |
8 consistent database updates and maintain the `gemma_schema_version` | |
9 table in the database. | |
10 | |
11 Running `update-db.sh` will automatically detect which schema updates | |
12 are necessary and apply them. | |
13 | |
14 ## Concept | |
15 | |
16 The gemma database schema is versioned. Each update to the schema | |
17 increments the version number of the schema. To get the version of | |
18 the schema your database is currently using, you can use the database | |
19 function `get_schema_version()`: | |
20 | |
21 ```sql | |
22 SELECT get_schema_version(); | |
23 ``` | |
24 | |
25 ## Directory Layout | |
26 | |
27 the `schema/updates` directory contains subdirectories with names | |
28 corresponding to the version of the database schema. Each | |
29 subdirectory contains the necessary SQL scripts to update the | |
30 database schema from the previous version to the one represented by | |
31 the directory name. | |
32 | |
33 Each script is name according to the scheme: `NN.NAME.sql` where `NN` | |
34 is a number determining the order of execution and `NAME` is a | |
35 short string describing what the script implements. | |
36 | |
37 ### Example: | |
38 | |
39 ``` | |
40 schema/updates | |
41 ├── 0000 | |
42 │ └── 01.add_schema_version.sql | |
43 ├── 0301 | |
44 │ ├── 01.dismar-wwname.sql | |
45 │ └── 02.search_functions.sql | |
46 └── 1000 | |
47 └── 01.pwreset.sql | |
48 | |
49 ``` | |
50 | |
51 ## Adding updates | |
52 | |
53 To implement a change to the gemma database schema: | |
54 | |
55 - Create a new directory `schema/updates/XXXX` where `XXXX` represents | |
56 the number of the new database version. The new number must be | |
57 greater than the current database version. | |
58 | |
59 - Create the update scripts in the new subdirectory. When more than | |
60 one script is used, the numeration in the scripts name determines | |
61 the order of execution. | |
62 | |
63 Note that all scripts in one version update are executed in a single | |
64 transaction by the `update-db.sh` script. | |
65 | |
66 - Modify the scripts in `schema/` (outside the updates directory) to | |
67 reflect the changes. The schema of a freshly created database using | |
68 `schema/install-db.sh` must always be identical to the schema of a | |
69 database updated using `schema/update-db.sh`! | |
70 | |
71 - Update `schema/version.sql`, so that it reflects the new schema | |
72 version you just created. |