Mercurial > gemma
annotate schema/updates/README.md @ 4005:5c7e797347a3
Fixup rev. eb11ada33fa7: add schema version
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 18 Jul 2019 19:49:56 +0200 |
parents | 0eb6b5fbeea3 |
children | f2f0b776ba12 |
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. |