@@ -93,11 +93,13 @@ 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
...
...
@@ -136,16 +138,12 @@ Constraints: This database makes use of five user relevant constraints.
<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).SQL_Umsetzung: CONSTRAINT check_catchment_area_range CHECK ( catchment_area_km2 \> 0 AND catchment_area_km2 \< 7000000 )
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))
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).
The Accreditation status of measurements are managed by a boolean column with controlled vocabulary used according to the following schema:
-**yes, fully**: true
...
...
@@ -205,7 +204,6 @@ 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.
...
...
@@ -288,7 +286,7 @@ Data between different tables are connected by id-columns, so called foreign key
</details>
**NOTE**: After updating or deleting rows, you might be asked to select a key (compare issue #10). Please select **Custom Key** and then choose the id column. This needs to be done only once for every table on every computer.
**NOTE**: After updating or deleting rows, you might be asked to select a key (compare issue #10). Please select **Custom Key** and then choose the id column. This needs to be done only once for every table on every computer. Additionally, please not that the table soil_sample_belonging_to_site has no id column, the equivalent choice are the two identifier columns of sample and site.
## 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.
\ No newline at end of file
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.
