Mercurial > gemma

annotate schema/updates/README.md @ 4214:49564382ffff

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added a import queue job to recalculate the contour lines of the sounding results if the heights have changed.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 16 Aug 2019 13:15:34 +0200
parents 0eb6b5fbeea3
children f2f0b776ba12
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 ## Directory Layout
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 the `schema/updates` directory contains subdirectories with names
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 corresponding to the version of the database schema. Each
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 subdirectory contains the necessary SQL scripts to update the
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 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
31 the directory name.
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 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
34 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
35 short string describing what the script implements.
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 ### Example:
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 ```
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 schema/updates
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 ├── 0000
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 │   └── 01.add_schema_version.sql
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 ├── 0301
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 │   ├── 01.dismar-wwname.sql
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 │   └── 02.search_functions.sql
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 └── 1000
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47     └── 01.pwreset.sql
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 ```
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 ## Adding updates
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 To implement a change to the gemma database schema:
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 - Create a new directory `schema/updates/XXXX` where `XXXX` represents
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 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
57 greater than the current database version.
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 the update scripts in the new subdirectory. When more than
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60 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
61 the order of execution.
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 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
64 transaction by the `update-db.sh` script.
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 - Modify the scripts in `schema/` (outside the updates directory) to
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 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
68 `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
69 database updated using `schema/update-db.sh`!
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
70
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71 - Update `schema/version.sql`, so that it reflects the new schema
0eb6b5fbeea3 Added documentation to database updates.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
72 version you just created.