controlled vocabulary is added for enum entries

Good discussion on:

• how to ensure that no code ever enters incorrect values for a column
• how to be able to extend pre-defined values for a column
• how to show human-readable enums
• ...

can be found here:
https://softwareengineering.stackexchange.com/questions/305148/why-would-you-store-an-enum-in-db

changed due date to June 08, 2020

added .doing label

mentioned in commit b5b51424

The above commit (b5b51424) defines the extension toar_controlled_vocabulary (first examples role codes and role status).

To install the extension to the database, do (as root):

apt-get install postgresql-server-dev-10
cd toardb_fastapi/extension/toar_controlled_vocabulary
make install

The installed extension is now available in the Postgres database system and can be loaded via:

CREATE EXTENSION toar_controlled_vocabulary;

If the extension had already been created in the database, then you can update via:

ALTER EXTENSION toar_controlled_vocabulary UPDATE;

Show version of extension:

SELECT * FROM pg_extension WHERE extname='toar_controlled_vocabulary';

mentioned in commit ffe9cc3b

The above commit (ffe9cc3b) completes the extension toar_controlled_vocabulary.

closed

added .done label and removed .doing label

mentioned in commit 5b8a540e

mentioned in issue #7 (closed)

mentioned in commit a26adfd2

mentioned in commit 5ebb7620

mentioned in commit f3216413

added controlled vocabulary for 'measurement_method' in commit 63edd0da.

mentioned in commit c05d84d8

mentioned in commit e034d2a1

mentioned in commit 2703f8fe

mentioned in commit b6590097

mentioned in commit 291d6ba4

mentioned in commit 0a0b88e3

With commit 0a0b88e3 the controlled vocabulary can be retrieved from the following URLs:

"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Role%20Codes"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Role%20Status"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Kind%20of%20Organizations"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Data%20Access%20Rights"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Sampling%20Frequencies"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Aggregation%20Types"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Data%20Sources"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Measurement%20Methods"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Climatic%20Zones"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Coordinate%20Validity"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Station%20Types"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Station%20Type%20of%20Area"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Station%20TOAR%20Categories"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Station%20HTAP%20Regions"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Station%20Dominant%20Landcover%20Types"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Result%20Types"
"https://join-dev.fz-juelich.de:8080/controlled_vocabulary/Data%20Flags"

mentioned in issue #14 (closed)

mentioned in commit 2e8d1730

mentioned in commit 730917ee

mentioned in merge request !1 (merged)

mentioned in commit 528d891b

mentioned in commit 2259e3fb

mentioned in commit 0480a6bd

mentioned in commit 59c51d5c

mentioned in commit a3818677

mentioned in commit 2201fd21

mentioned in commit 3a7a7d0e

mentioned in commit b9bdb9ba

added discussion label

mentioned in commit 2d7d8ec1

mentioned in commit b2518ee1

mentioned in commit 39c6fb4c

marked this issue as related to #31

The controlled vocabulary is put into a separate schema.

If your controlled_vocabulary is already installed, you can use the following commands to move the tables into their own schema:

CREATE SCHEMA toar_convoc;
ALTER TABLE rc_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE rs_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE ak_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE ok_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE cl_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE da_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE sf_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE at_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE ds_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE mm_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE cz_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE cv_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE st_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE ta_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE tc_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE tr_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE dl_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE rt_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE df_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE cn_vocabulary
  SET SCHEMA toar_convoc;
ALTER TABLE tz_vocabulary
  SET SCHEMA toar_convoc;
ALTER DATABASE toardb_v2
    SET search_path=public,postgis,contrib,tiger,toar_convoc;

Quick double-check:

SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables
  WHERE table_schema = 'toar_convoc';

will result in:

 table_catalog | table_schema |  table_name   | table_type 
---------------+--------------+---------------+------------
 toardb_v2     | toar_convoc  | da_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | rs_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | sf_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | at_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | tr_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | mm_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | ok_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | ta_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | rc_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | cz_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | rt_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | tc_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | dl_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | df_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | st_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | cv_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | ds_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | ak_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | cl_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | cn_vocabulary | BASE TABLE
 toardb_v2     | toar_convoc  | tz_vocabulary | BASE TABLE
(21 rows)

mentioned in commit 44091312

mentioned in commit 354680b5

mentioned in commit ab957a00

mentioned in commit 1efb657c

mentioned in commit 277b9add

mentioned in commit 12d85c72

mentioned in commit 64e8f576

mentioned in commit bf153f0e

mentioned in merge request !19 (merged)

mentioned in commit 25cf0774

mentioned in merge request !20 (merged)