Mercurial > gemma

comparison schema/updates/README.md @ 3994:0eb6b5fbeea3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.