| ... | ... | @@ -199,6 +199,37 @@ Furthermore the composite primary key is used to assure, that measurements, samp |
|
|
|
|
|
|
|
To allow the compartment to be included twice as foreign key referencing once the compartments table directly and once the compartment as foreign key referencing e.g. the sampling sites compartment from the samples table, so called "generated columns" are used to fill the additional columns and check constraints to assure, that the compartment information matches.
|
|
|
|
|
|
|
|
## Steps to set up the database from DDL scripts
|
|
|
|
- [ ] Export the DDL script from ERMaster
|
|
|
|
- [ ] Perform "Search and replace" for 'SESSION_USER' by SESSION_USER in the DDL script to make this default value a variable instead of the test string.
|
|
|
|
- [ ] Add table partitioning if you expect the tables to become big. Find the table creation statements in the DDL script and append them as follows:
|
|
|
|
<details><summary>Click to expand</summary>
|
|
|
|
|
|
|
|
```sql
|
|
|
|
|
|
|
|
CREATE TABLE hidden.meas
|
|
|
|
(...) PARTITION BY LIST(compartment);
|
|
|
|
|
|
|
|
CREATE TABLE hidden.meas_groundw PARTITION OF hidden.meas FOR VALUES IN ('groundwater');
|
|
|
|
CREATE TABLE hidden.meas_surfw PARTITION OF hidden.meas FOR VALUES IN ('surfacewater');
|
|
|
|
CREATE TABLE hidden.meas_wastew PARTITION OF hidden.meas FOR VALUES IN ('wastewater');
|
|
|
|
CREATE TABLE hidden.meas_stormw PARTITION OF hidden.meas FOR VALUES IN ('stormwater runoff');
|
|
|
|
CREATE TABLE hidden.meas_soil PARTITION OF hidden.meas FOR VALUES IN ('soil');
|
|
|
|
CREATE TABLE hidden.meas_def PARTITION OF hidden.meas DEFAULT;
|
|
|
|
|
|
|
|
|
|
|
|
CREATE TABLE hidden.samples
|
|
|
|
(...) PARTITION BY LIST(compartment);
|
|
|
|
|
|
|
|
CREATE TABLE hidden.samples_groundw PARTITION OF hidden.samples FOR VALUES IN ('groundwater');
|
|
|
|
CREATE TABLE hidden.samples_surfw PARTITION OF hidden.samples FOR VALUES IN ('surfacewater');
|
|
|
|
CREATE TABLE hidden.samples_wastew PARTITION OF hidden.samples FOR VALUES IN ('wastewater');
|
|
|
|
CREATE TABLE hidden.samples_stormw PARTITION OF hidden.samples FOR VALUES IN ('stormwater runoff');
|
|
|
|
CREATE TABLE hidden.samples_soil PARTITION OF hidden.samples FOR VALUES IN ('soil');
|
|
|
|
CREATE TABLE hidden.samples_def PARTITION OF hidden.samples DEFAULT;
|
|
|
|
|
|
|
|
```
|
|
|
|
|
|
|
|
</details>
|
|
|
|
|
|
|
|
- [ ] Run scripts in \SQL_scripts\Make_scripts and pay attention to comments in the beginning of the scripts, e.g. putting the name of your database. |
