changelogs make use of controlled vocabulary (for reporting old_values) when patches are logged
Changelogs should make use of the controlled vocabulary (for reporting old_values) when patches are logged.
At the moment, this is inconsistent, f. ex.:
{"datetime":"2021-10-12T16:08:20.570789+00:00","description":"add station country","old_value":"{'country': 239}","new_value":"{'country': 'CL'}","station_id":58,"author_id":1,"type_of_change":"single value correction in metadata"}
In PostgreSQL, one can get information on foreign keys (for example which table fields use which controlled vocabulary) from the pg_catalog.pg_constraint-table.
For our purpose one could use the following command:
SELECT c.conname, c1.conname FROM pg_catalog.pg_constraint c INNER JOIN pg_catalog.pg_constraint c1 ON c.confrelid=c1.conrelid AND c1.conname LIKE '%%enum_val%%' AND c.conname LIKE '%%_fk_%%_voc%%';
to get the following table:
conname | conname |
---|---|
data_archive_flags_fk_df_vocabulary_enum_val | df_enum_val_unique |
data_flags_fk_df_vocabulary_enum_val | df_enum_val_unique |
organisations_kind_fk_ok_vocabulary_enum_val | ok_enum_val_unique |
stationmeta_annotations_kind_fk_ak_vocabulary_enum_val | ak_enum_val_unique |
stationmeta_changelog_type_of_change_fk_cl_vocabulary_enum_val | cl_enum_val_unique |
stationmeta_core_coordinate_validation_status_fk_cv_vocabulary_ | cv_enum_val_unique |
stationmeta_core_country_fk_cn_vocabulary_enum_val | cn_enum_val_unique |
stationmeta_core_timezone_fk_tz_vocabulary_enum_val | tz_enum_val_unique |
stationmeta_core_type_of_area_fk_ta_vocabulary_enum_val | ta_enum_val_unique |
stationmeta_glob_dominant_landcover_year2012_fk_dl_voc_enum_val | dl_enum_val_unique |
stationmeta_global_climatic_zone_fk_cz_at_vocabulary_enum_val | cz_enum_val_unique |
stationmeta_global_htap_region_tier1_fk_tr_vocabulary_enum_val | tr_enum_val_unique |
stationmeta_global_toar1_category_fk_tc_vocabulary_enum_val | tc_enum_val_unique |
stationmeta_roles_role_fk_rc_vocabulary_enum_val | rc_enum_val_unique |
stationmeta_roles_status_fk_rs_vocabulary_enum_val | rs_enum_val_unique |
timeseries_aggregation_fk_at_vocabulary_enum_val | at_enum_val_unique |
timeseries_changelog_type_of_change_fk_cl_vocabulary_enum_val | cl_enum_val_unique |
timeseries_roles_role_fk_rc_vocabulary_enum_val | rc_enum_val_unique |
timeseries_roles_status_fk_rs_vocabulary_enum_val | rs_enum_val_unique |
timeseries_sampling_frequency_fk_sf_vocabulary_enum_val | sf_enum_val_unique |
timeseries_data_origin_type_fk_ot_vocabulary_enum_val | ds_enum_val_unique |
timeseries_data_origin_fk_do_vocabulary_enum_val | mm_enum_val_unique |
stationmeta_core_type_fk_st_vocabulary_enum_val | st_enum_val_unique |
organisations_country_fk_cn_vocabulary_enum_val | cn_enum_val_unique |