Mercurial > gemma
annotate schema/updates/README.md @ 5279:a17c2a0b8e44
client: use current time in the review layer request
* avoid using value from time slider in the request for review layer
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Thu, 11 Jun 2020 15:50:11 +0200 |
parents | a3cbf5a2c1ef |
children |
rev | line source |
---|---|
3994
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 # Database schema updates |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 ## Usage |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 DON'T APPLY THE SQL SCRIPTS IN THE DIRECTORIES MANUALLY! |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 Use the script `schema/update-db.sh` instead, this will guaranty |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 consistent database updates and maintain the `gemma_schema_version` |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 table in the database. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 Running `update-db.sh` will automatically detect which schema updates |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 are necessary and apply them. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 ## Concept |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 The gemma database schema is versioned. Each update to the schema |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 increments the version number of the schema. To get the version of |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 the schema your database is currently using, you can use the database |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 function `get_schema_version()`: |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 ```sql |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 SELECT get_schema_version(); |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 ``` |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 |
4452
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
25 It is an design goal to allow any older version of a gemma data base |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
26 to be updated to the most current schema by calling `update-db.sh` |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
27 from the current gemma distribution. |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
28 |
3994
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 ## Directory Layout |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 the `schema/updates` directory contains subdirectories with names |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 corresponding to the version of the database schema. Each |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 subdirectory contains the necessary SQL scripts to update the |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 database schema from the previous version to the one represented by |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 the directory name. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 Each script is name according to the scheme: `NN.NAME.sql` where `NN` |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 is a number determining the order of execution and `NAME` is a |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 short string describing what the script implements. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 ### Example: |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 ``` |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 schema/updates |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 ├── 0000 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 │ └── 01.add_schema_version.sql |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 ├── 0301 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 │ ├── 01.dismar-wwname.sql |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 │ └── 02.search_functions.sql |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 └── 1000 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 └── 01.pwreset.sql |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 ``` |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 ## Adding updates |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 To implement a change to the gemma database schema: |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 - Create a new directory `schema/updates/XXXX` where `XXXX` represents |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 the number of the new database version. The new number must be |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 greater than the current database version. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
62 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
63 - Create the update scripts in the new subdirectory. When more than |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
64 one script is used, the numeration in the scripts name determines |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 the order of execution. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 Note that all scripts in one version update are executed in a single |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
68 transaction by the `update-db.sh` script. |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 |
4452
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
70 **Important:** all sql scripts in one update directory have to be |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
71 self contained and *must not* include any files from the regular |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
72 schema definition (or from other update versions). This is |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
73 necessary to allow for updates over multiple versions of the schema, |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
74 where multiple updates might make incremental changes to the same |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
75 functions/files of the schema. |
a3cbf5a2c1ef
Clarify that db update scripts must not include regular schema files.
Sascha Wilde <wilde@intevation.de>
parents:
4326
diff
changeset
|
76 |
3994
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 - Modify the scripts in `schema/` (outside the updates directory) to |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
78 reflect the changes. The schema of a freshly created database using |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
79 `schema/install-db.sh` must always be identical to the schema of a |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
80 database updated using `schema/update-db.sh`! |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
81 |
0eb6b5fbeea3
Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
82 - Update `schema/version.sql`, so that it reflects the new schema |
4326
f2f0b776ba12
schema: improve docs for db updates
Bernhard Reiter <bernhard@intevation.de>
parents:
3994
diff
changeset
|
83 version you've just created. |