Home.md

@@ -62,7 +62,7 @@ The same data are presented in different views for different purpose:
 
 At the top of every schemata are three support views starting with a underscore \_.
 
-- The \__column_definition_ view contains_ comprehensive information about a columns properties regarding its description, data type, constraints or keys and should therefore serve as a tool for importing activities.
+- The \__column_definition_ view contains\_ comprehensive information about a columns properties regarding its description, data type, constraints or keys and should therefore serve as a tool for importing activities.
 - The \__controlled_vocabularies_ contains predefined column entries originally stored in hidden and only maintained by _tethys_ca_. They serve as foreign key for \__import_ views and are marked as such in the \__column_definition_ view. The reason to limit their creation rights lies in their low diversity but simultaneously high number of references, which therefore enables the _tethys_ca_ to preserve data integrity from below.
 - The \__table_definition_ view contains information about all views and tables of its schema and should therefore enhance your knowledge about the database.
 
@@ -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 <span dir="">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
@@ -138,12 +136,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).
@@ -169,7 +167,6 @@ Values are checked based on values in other columns: If a measurement is marked
 
 <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
@@ -204,6 +201,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.
@@ -338,3 +336,7 @@ CREATE TABLE hidden.samples_def PARTITION OF hidden.samples DEFAULT;
 ## 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.
+
+## Updating: Deleting all dependant objects before installing new update
+
+In the case of an update, one might to delete all dependant objects of all/certain tables in order to be able to alter something in the table structure. To speed up the process of finding and deleting mostly views, script 23_RESET_VIEWS offers the opportunity of deleting specific dependencies of a table or all dependant objects of all tables at once. 
\ No newline at end of file