Mercurial > gemma

annotate schema/updates/README.md @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents a3cbf5a2c1ef
children
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
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.