@@ -93,13 +93,11 @@ The quality assessor can see data created by other users. He can comment on thes
<details>
<summary>Click to expand</summary>
Can the right for modifications be limited to specific columns? This would be perfect. Answer: YES
\-- First, we create a function that checks whether the user is allowed to update certain columns. It does this by checking if some_columns, that he is not allowed to modify stayed the same. CREATE OR REPLACE FUNCTION can_bypassrls_in_columns(row_data your_table, new_data your_table) RETURNS BOOLEAN AS <spandir="">BEGIN IF current_user =\<tethys_qa\> THEN IF new_data.some_column = row_data.some_column THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSE RETURN TRUE; END IF; END; </span>LANGUAGE plpgsql;
CREATE POLICY bypassrls_on_col_policy ON your_table FOR UPDATE TO \<tethys_qa\> USING (can_bypassrls_in_columns(OLD, NEW)) WITH CHECK (true);--the script to enable BYPASSRLS for \<tethys_user\> should look like this\*\*\* ALTER TABLE your_table ENABLE ROW LEVEL SECURITY; ALTER TABLE your_table FORCE ROW LEVEL SECURITY; GRANT BYPASSRLS (every_column, that_he_is_allowed, to_modify) ON your_table TO \<tethys_qa\>;SQL_Umsetzung: References, BYPASSRLS restricted\*\*\* TRUE, WITH INHERIT TRUE.
</details>
### tethys_ca
...
...
@@ -131,51 +129,48 @@ The following procedures are implemented to ascertain data quality in the databa
-**Traceability**: Every record is automatically amended with the information who created it (column created_by) and at what time (column created_at) and who last modified it (column updated_by) and at which time (column updated_by). To document how pre-existing data were mapped on the controlled vocabulary of the database, for several attributed there is an additional text-column, where the original value should be stored. This column has the suffix \_reported. Deleted data from the tables containing the data are not deleted when user delete them, but marked as deleted and kept hidden from the ordinary user ("soft-deletion"). They can be restored by the administrators on request.
Constraints: This database makes use of five user relevant constraints.
-**Primary key constraints**: Ensures that uniqueness as well as a not-null condition of a column entry or a combination of column entries are satisfied. As user are not supposed to import the id column as it is automatically generated, its only relevance to the users is, that the id column needs to get selected as custom key when deleting or updating data (compare issue #10).
-**Not Null constraints**: Ensures that a not-null condition of a column entry or a combination of column entries are satisfied. In the __column_definition_ view those columns are marked in the _mandatory_ column.
-**Not Null constraints**: Ensures that a not-null condition of a column entry or a combination of column entries are satisfied. In the \__column_definition_ view those columns are marked in the _mandatory_ column.
-**Check constraint**: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
<details>
<summary>Example 1</summary>
Check constraints for numeric values: Wherever possible numeric values are checked on insertion for their plausibility (e.g.areas, discharges, inhabitants) to be within a meaningful range, usually \\\> 0 and smaller a maximum value. The catchment area of a river must be larger than 0 km² and smaller than 7000000 km² (size of the Amazon river basin).
Check constraints for numeric values: Wherever possible numeric values are checked on insertion for their plausibility (e.g.areas, discharges, inhabitants) to be within a meaningful range, usually \\\> 0 and smaller a maximum value. The catchment area of a river must be larger than 0 km² and smaller than 7000000 km² (size of the Amazon river basin).SQL_Umsetzung: CONSTRAINT check_catchment_area_range CHECK ( catchment_area_km2 \> 0 AND catchment_area_km2 \< 7000000 )
</details>
<details>
<summary>Example 2</summary>
Values are checked based on values in other columns: If a measurement is marked as below an analytical limit, the value of the limit must be given in the corresponding column. If a measurement is marked as below the analytical limit of detection (LOD), the LOD must be given in the corresponding column.
SQL_Umsetzung: CONSTRAINT check_lod_available CHECK ((lod \\\\\\\> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))
</details>
-**Unique key constraint**: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
A combination of columns are the unique key SQL_Umsetzung: CONSTRAINT unique_measurement UNIQUE (compartment, determinant, identifier_analytical_method, identifier_lab, identifier_sample)
</details>
<details>
<summary>Example 2</summary>
A unique key, in this case an identifier consits of several information of its row
identifier_analytical_method: Identifier of an analytical laboratory method defined in the table determ.analytical_methods. A laboratory method in best case is defined by a Standard (ISO or other) and consists of sample preparation and sample analysis techniques with appropriate measures for quality assurance and a validation procedure.
A unique key, in this case an identifier consits of several information of its row identifier_analytical_method: Identifier of an analytical laboratory method defined in the table determ.analytical_methods. A laboratory method in best case is defined by a Standard (ISO or other) and consists of sample preparation and sample analysis techniques with appropriate measures for quality assurance and a validation procedure.
</details>
-**Foreign key constraint**: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
<details>
<summary>Example</summary>
A foreign key references one or in this example two columns of another table case sensitive.
SQL_Umsetzung: ALTER TABLE hidden.samples ADD FOREIGN KEY (identifier_sampling_site, sampling_site_compartment) REFERENCES hidden.sampling_sites (identifier_sampling_site, compartment) ON UPDATE RESTRICT ON DELETE RESTRICT;
A foreign key references one or in this example two columns of another table case sensitive. SQL_Umsetzung: ALTER TABLE hidden.samples ADD FOREIGN KEY (identifier_sampling_site, sampling_site_compartment) REFERENCES hidden.sampling_sites (identifier_sampling_site, compartment) ON UPDATE RESTRICT ON DELETE RESTRICT;
</details>
-**Controlled vocabulary**: Those are a special version of foreign keys, here used for qualitative attributes: Whenever a qualitative attribute is recorded, controlled vocabularies (a list of allowed values for that column) are used. These assure, that the same thing is always called the same (and without typos) and no further data cleaning is necessary before data evaluation. Controlled vocabularies are stored in separate tables. These tables can not be changed by the ordinary user, but only by admins. Deletion in controlled vocabularies are not recorded in the database itself (no soft delete mechanism), here the admin is responsible to keep a backup. In the \__column_definition\_ view those columns are marked in the \__controlled_vocabulary\_ and. Possible entries for those columns can be seen in the \__controlled_vocabularies\_ view.
<details>
<summary>Example</summary>
The Accreditation status of measurements are managed by a boolean column with controlled vocabulary used according to the following schema:
-**yes, fully**: true
...
...
@@ -187,8 +182,11 @@ The Accreditation status of measurements are managed by a boolean column with co
Especially during importing it is crucial to keep track of the present constraints in table. To simplify this task the following graphic should serve as support sheet.
- Quality assessment workflow: As described below.
...
...
@@ -207,6 +205,7 @@ The 30 minutes embargo time data are not shown to the quality assessor after cre
<details>
<summary>Technical Details</summary>
The quality assessment workflow is based on three columns in the tables:
- quality_assessor: The person doing the quality assessment. Will be filled automatically with the session-user when a user in the role tethys_qa updates the other two columns.
- [ ] 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.
## Special case of compartment dependant obligatory columns
In the case of columns, that are only mandatory for certain compartments, the NOT-NULL constraint is due to its table specific comprehensiveness not applicable for compartment specific views. Hence, the required constraints are now realised as compartment specific check_constraint with names ending with the string "_given", which makes them distinguishable in a SQL query, and therefore embeddable also in the _column_definition view.
