@@ -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
...
...
@@ -133,34 +131,33 @@ The following procedures are implemented to ascertain data quality in the databa
- Primary key: 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: 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: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
-<details><summary>Example2</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). CONSTRAINT
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).
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.CONSTRAINT check_lod_available CHECK ((lod \> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))
<details>
<summary>Example2</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.
`CONSTRAINT check_lod_available CHECK ((lod \\\> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))`
</details>
- Unique key: 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).
- Foreign keys: 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).
- 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:
<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
-**no, but the values are of high reliability**: false
-**no**: Exclude such data from data base
-**unknown**: NULL
</details>
- Quality assessment workflow: As described below.
...
...
@@ -179,6 +176,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.
