last constraints, checks, defaults, and indices for database columns were added

Database Review

Are all constraints, checks, default values, and indices for database columns installed properly?
Did we have other default values in the old database?
Can we propose default values for every column, so we do not have to define nullable columns within the database (slow access to those)?

Check all of these constraints and check, whether sqlacodegen still generates the same classes as coded in this repository.

Also check on "ON DELETE CASCADE" table attributes. Notes regarding this issue can be found in comment https://gitlab.version.fz-juelich.de/esde/toar-data/toardb_fastapi/-/issues/7#note_82561.

Changes to the database (including on delete cascade): see comment https://gitlab.version.fz-juelich.de/esde/toar-data/toardb_fastapi/-/issues/7#note_86643

changed due date to June 10, 2020

added .doing label

changed the description

All default values that correspond to 'undefined' within controlled vocabulary have to be defined in those tables (related to #3 (closed))!

Default values for stationmeta (from old database):

column	type	default
station_nightlight_5km	real	'-999.0'::numeric
station_climatic_zone	integer	'-1'::integer
station_wheat_production	real	'-999.0'::numeric
station_rice_production	real	'-999.0'::numeric
station_nox_emissions	real	'-999.0'::numeric
station_omi_no2_column	real	'-999.0'::numeric
station_toar_category	integer	'-1'::integer
station_htap_region	integer	'-1'::integer
station_alt_flag	integer	0
station_coordinate_status	integer	'-1'::integer
station_google_alt	real	'-999.0'::numeric
station_etopo_alt	real	'-999.0'::numeric
station_etopo_min_alt_5km	real	'-999.0'::numeric
station_etopo_relative_alt	real	'-999.0'::numeric
station_dominant_landcover	integer	'-1'::integer
station_max_nightlight_25km	real	'-999.0'::numeric
station_max_population_density_25km	integer	'-1'::integer
station_nightlight_1km	real	'-999.0'::numeric
station_population_density	integer	'-1'::integer
google_resolution	real	'-999.0'::numeric
station_max_population_density_5km	integer	'-1'::integer

Default values for timeseries (from old database):

column	type	default
creation_date	timestamp with time zone	now()
modification_date	timestamp with time zone	now()

The above defaults are already established in my local version -- will be uploaded with the next database dump ('toardb_dump.sql').
BTW: The fields population_density, max_population_density changed from integer to double precision!

mentioned in commit 38717b35

mentioned in commit 390133f4

mentioned in commit 76cc99bb

Default values for persons:

column	type	default
phone	character varying(32)	''::character varying
isprivate	boolean	true

The above defaults are already established in my local version -- will be uploaded with the next database dump ('toardb_v2_dump.sql').
Of course, they will also appear in source code!

Tables "annotations" (at the moment two: stationmeta_annotations and timeseries_annotations -- these should be reduced to one table later) did not make use of controlled vocabulary for column kind.

Controlled vocabulary was added, models and schemas were adapted, and the following command was interactively executed on the database:

ALTER TABLE stationmeta_annotations
ALTER COLUMN kind SET DEFAULT 0,
ADD CONSTRAINT stationmeta_annotations_kind_fk_ak_vocabulary_enum_val FOREIGN KEY(kind) REFERENCES ak_vocabulary(enum_val);

The above commands are added to db_changes.psql in the rollout branch (see #22 (closed)).

Commit 870ac743 (and parts of commit 90d30342) contains these changes and adaptions.

mentioned in commit 870ac743

mentioned in commit 90d30342

added discussion label

changed due date to March 19, 2021

Some notes on ON_DELETE_CASCADE
(We do not want to delete any data from the database.)
Although we never really want to use it, we want to introduce it to the database tables. Somehow it can happen that we want to remove old (meta) data, and if you don't have a "cascade", it becomes very tedious. The data in the "staging database" would actually be deleted if it had been transferred. And that should be as identical as possible with the operational database.

We decided to cascade deletes on contacts(id) (persons(id)/organisations(id)), stationmeta_core(id), and timeseries(id) -- but not on variables(id).
This affects the following tables:
1. stationmeta_core(id):
stationmeta_core_stationmeta_annotations
stationmeta_aux_doc
stationmeta_aux_image
stationmeta_aux_url
stationmeta_changelog
stationmeta_core_stationmeta_roles
stationmeta_global_services (?! is station_id needed at all?!)
stationmeta_global
timeseries
2. timeseries(id):
data_archive
data
timeseries_changelog
timeseries_timeseries_annotations
timeseries_timeseries_roles
3. contacts(id) (persons(id)/organisations(id)):
stationmeta_roles
stationmeta_core_stationmeta_roles (now for stationmeta_roles(id))
timeseries_roles
timeseries_timeseries_roles (now for timeseries_roles(id))

What about stationmeta_annotation, timeseries_annotation? Should it be possible to delete these (which has then also to be cascaded).

Default values / constraints / nullable / on delete cascade for all tables

(in alphabetical order)

[[TOC]]

contacts

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#contact

                             Table "public.contacts"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('contacts_id_seq'::regclass)
person_id	integer		not null	0
organisation_id	integer		not null	0

Indexes:
"contacts_pkey" PRIMARY KEY, btree (id)
Check constraints:
"ck_contact" CHECK (person_id > 0 AND organisation_id = 0 OR person_id = 0 AND organisation_id > 0)
Foreign-key constraints:
"contacts_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE
"contacts_person_id_fkey" FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
Referenced by:
TABLE "stationmeta_roles" CONSTRAINT "stationmeta_roles_contact_id_fk_contacts_id" FOREIGN KEY (contact_id) REFERENCES contacts(id) DEFERRABLE INITIALLY DEFERRED
TABLE "timeseries_roles" CONSTRAINT "timeseries_roles_contact_id_fk_contacts_id" FOREIGN KEY (contact_id) REFERENCES contacts(id) DEFERRABLE INITIALLY DEFERRED

data / data_archive

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#data

                                       Table "public.data"

Column	Type	Collation	Nullable	Default
datetime	timestamp with time zone		not null
value	double precision		not null
flags	integer		not null
timeseries_id	integer		not null
version	character(28)		not null	'000001.000000.00000000000000'::bpchar

                                       Table "public.data_archive"

Column	Type	Collation	Nullable	Default
datetime	timestamp with time zone		not null
value	double precision		not null
flags	integer		not null
timeseries_id	integer		not null
version	character(28)		not null

Indexes:
"data_pkey" PRIMARY KEY, btree (timeseries_id, datetime)
"data_datetime_4b248149" btree (datetime)
"data_timeseries_id_a38c5a1a" btree (timeseries_id)
"data_timeseries_id_datetime_7daa6d17_uniq" UNIQUE CONSTRAINT, btree (timeseries_id, datetime)
"data_value_idx" btree (value)
Check constraints:
"data_flags_check" CHECK (flags >= 0)
Foreign-key constraints:
"data_flags_fk_df_vocabulary_enum_val" FOREIGN KEY (flags) REFERENCES df_vocabulary(enum_val)
"data_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) ON DELETE CASCADE
Referenced by:
-

organisations

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#organisation

                                    Table "public.organisations"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('organisations_id_seq'::regclass)
name	character varying(32)		not null
longname	character varying(256)		not null
kind	integer		not null
city	character varying(64)		not null
postcode	character varying(16)		not null
street_address	character varying(128)		not null
country	character varying(64)		not null
homepage	character varying(200)		not null

Indexes:
"organisations_pkey" PRIMARY KEY, btree (id)
Check constraints:
"organisations_kind_check" CHECK (kind >= 0)
Foreign-key constraints:
"organisations_kind_fk_ok_vocabulary_enum_val" FOREIGN KEY (kind) REFERENCES ok_vocabulary(enum_val)
Referenced by:
TABLE "contacts" CONSTRAINT "contacts_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisations(id)

persons

http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#person

                                 Table "public.persons"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('persons_id_seq'::regclass)
name	character varying(64)		not null
email	character varying(128)		not null
phone	character varying(32)		not null	''::character varying
orcid	character(19)		not null	'0000-0000-0000-0000'
isprivate	boolean		not null	true

Indexes:
"persons_pkey" PRIMARY KEY, btree (id)
"persons_name_email" UNIQUE CONSTRAINT, btree (name, email)
Check constraints:
-
Foreign-key constraints:
-
Referenced by:
TABLE "contacts" CONSTRAINT "contacts_person_id_fkey" FOREIGN KEY (person_id) REFERENCES persons(id)

stationmeta_annotations

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetaannotation

                                     Table "public.stationmeta_annotations"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_annotations_id_seq'::regclass)
kind	integer		not null
text	text		not null
date_added	timestamp with time zone		not null
approved	boolean		not null
contributor_id	integer		not null

Indexes:
"stationmeta_annotations_pkey" PRIMARY KEY, btree (id)
"stationmeta_annotations_contributor_id_a5009820" btree (contributor_id)
Check constraints:
"stationmeta_annotations_kind_check" CHECK (kind >= 0)
Foreign-key constraints:
"stationmeta_annotations_contributor_id_a5009820_fk_auth_user_id" FOREIGN KEY (contributor_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"stationmeta_annotations_kind_fk_ak_vocabulary_enum_val" FOREIGN KEY (kind) REFERENCES ak_vocabulary(enum_val)
Referenced by:
TABLE "stationmeta_core_stationmeta_annotations" CONSTRAINT "stationmeta_core_stationmeta_annotations_annotation_id_fkey" FOREIGN KEY (annotation_id) REFERENCES stationmeta_annotations(id)

stationmeta_aux_doc

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetaauxdoc

                                        Table "public.stationmeta_aux_doc"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_aux_doc_id_seq'::regclass)
resource_description	text		not null
date_added	timestamp with time zone		not null
resource	character varying(100)		not null
station_id	integer

Indexes:
"stationmeta_aux_doc_pkey" PRIMARY KEY, btree (id)
"stationmeta_aux_doc_station_id_17bdb5f2" btree (station_id)
Check constraints:
-
Foreign-key constraints:
"stationmeta_aux_doc_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) ON DELETE CASCADE
Referenced by:
-

stationmeta_aux_image

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetaauximage

                                        Table "public.stationmeta_aux_image"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_aux_image_id_seq'::regclass)
resource_description	text		not null
date_added	timestamp with time zone		not null
resource	character varying(100)		not null
image_height	integer		not null
image_width	integer		not null
station_id	integer

Indexes:
"stationmeta_aux_image_pkey" PRIMARY KEY, btree (id)
"stationmeta_aux_image_station_id_fbfbdb29" btree (station_id)
Check constraints:
"stationmeta_aux_image_image_height_check" CHECK (image_height >= 0)
"stationmeta_aux_image_image_width_check" CHECK (image_width >= 0)
Foreign-key constraints:
"stationmeta_aux_image_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) ON DELETE CASCADE
Referenced by:
-

stationmeta_aux_url

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetaauxurl

                                        Table "public.stationmeta_aux_url"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_aux_url_id_seq'::regclass)
resource_description	text		not null
date_added	timestamp with time zone		not null
resource	character varying(200)		not null
station_id	integer

Indexes:
"stationmeta_aux_url_pkey" PRIMARY KEY, btree (id)
"stationmeta_aux_url_station_id_727571bd" btree (station_id)
Check constraints:
-
Foreign-key constraints:
"stationmeta_aux_url_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) ON DELETE CASCADE
Referenced by:
-

stationmeta_changelog

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetachangelog

                Table "public.stationmeta_changelog"

Column	Type	Collation	Nullable	Default
datetime	timestamp with time zone		not null	now()
station_id	bigint		not null
type_of_change	integer		not null
description	text		not null
old_value	character varying(256)		not null
new_value	character varying(256)		not null
author_id	integer		not null

Indexes:
"stationmeta_changelog_pkey" PRIMARY KEY, btree (datetime)
Check constraints:
-
Foreign-key constraints:
"stationmeta_changelog_author_id_fk_auth_user_id" FOREIGN KEY (author_id) REFERENCES auth_user(id)
"stationmeta_changelog_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) ON DELETE CASCADE "stationmeta_changelog_type_of_change_fk_cl_vocabulary_enum_val" FOREIGN KEY (type_of_change) REFERENCES cl_vocabulary(enum_val)
Referenced by:
-

stationmeta_core

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetacore

                                           Table "public.stationmeta_core"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_core_id_seq'::regclass)
codes	character varying(16)[]
name	character varying(128)		not null
coordinates	geometry(PointZ,4326)		not null
state	character varying(128)		not null
type_of_environment	integer		not null
type_of_area	integer		not null
additional_metadata	jsonb		not null
coordinate_validation_status	integer		not null	0
country	integer		not null	'-1'::integer
timezone	integer		not null	588

Indexes:
"stationmeta_core_pkey" PRIMARY KEY, btree (id)
"stationmeta_core_coordinates_id" gist (coordinates gist_geometry_ops_nd)
"stationmeta_core_state_85025a96" btree (state)
"stationmeta_core_state_85025a96_like" btree (state varchar_pattern_ops)
Check constraints:
"stationmeta_core_type_of_area_check" CHECK (type_of_area >= 0)
"stationmeta_core_type_of_environment_check" CHECK (type_of_environment >= 0)
Foreign-key constraints:
"stationmeta_core_coordinate_validation_status_fk_cv_vocabulary_" FOREIGN KEY (coordinate_validation_status) REFERENCES cv_vocabulary(enum_val)
"stationmeta_core_country_fk_cn_vocabulary_enum_val" FOREIGN KEY (country) REFERENCES cn_vocabulary(enum_val)
"stationmeta_core_timezone_fk_tz_vocabulary_enum_val" FOREIGN KEY (timezone) REFERENCES tz_vocabulary(enum_val)
"stationmeta_core_type_of_area_fk_ta_vocabulary_enum_val" FOREIGN KEY (type_of_area) REFERENCES ta_vocabulary(enum_val)
"stationmeta_core_type_of_environment_fk_st_vocabulary_enum_val" FOREIGN KEY (type_of_environment) REFERENCES st_vocabulary(enum_val)
Referenced by:
TABLE "stationmeta_aux_doc" CONSTRAINT "stationmeta_aux_doc_station_id_17bdb5f2_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED
TABLE "stationmeta_aux_image" CONSTRAINT "stationmeta_aux_imag_station_id_fbfbdb29_fk_stationme" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED
TABLE "stationmeta_aux_url" CONSTRAINT "stationmeta_aux_url_station_id_727571bd_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED
TABLE "stationmeta_changelog" CONSTRAINT "stationmeta_changelog_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
TABLE "stationmeta_core_stationmeta_annotations" CONSTRAINT "stationmeta_core_stationmeta_annotations_station_id_fkey" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
TABLE "stationmeta_core_stationmeta_roles" CONSTRAINT "stationmeta_core_stationmeta_roles_station_id_fkey" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
TABLE "stationmeta_global_services" CONSTRAINT "stationmeta_global_services_station_id_fkey" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
TABLE "stationmeta_global" CONSTRAINT "stationmeta_global_station_id_29ff53dd_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED
TABLE "timeseries" CONSTRAINT "timeseries_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)

stationmeta_global

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetaglobal

                                            Table "public.stationmeta_global"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_global_id_seq'::regclass)
population_density_year2010	double precision		not null	'-1.0'::numeric
max_population_density_25km_year2010	double precision		not null	'-1.0'::numeric
climatic_zone	integer		not null	'-1'::integer
nightlight_1km_year2013	double precision		not null	'-999.0'::numeric
nightlight_5km_year2013	double precision		not null	'-999.0'::numeric
max_nightlight_25km_year2013	double precision		not null	'-999.0'::numeric
wheat_production_year2000	double precision		not null	'-999.0'::numeric
rice_production_year2000	double precision		not null	'-999.0'::numeric
edgar_htap_v2_nox_emissions_year2010	double precision		not null	'-999.0'::numeric
omi_no2_column_years2011to2015	double precision		not null	'-999.0'::numeric
htap_region_tier1	integer		not null	'-1'::integer
etopo_alt	double precision		not null	'-999.0'::numeric
etopo_min_alt_5km	double precision		not null	'-999.0'::numeric
etopo_relative_alt	double precision		not null	'-999.0'::numeric
dominant_landcover_year2012	integer		not null	'-1'::integer
toar1_category	integer		not null	'-1'::integer
station_id	integer		not null

Indexes:
"stationmeta_global_pkey" PRIMARY KEY, btree (id)
"stationmeta_global_station_id_key" UNIQUE CONSTRAINT, btree (station_id)
Check constraints:
-
Foreign-key constraints:
"stationmeta_glob_dominant_landcover_year2012_fk_dl_voc_enum_val" FOREIGN KEY (dominant_landcover_year2012) REFERENCES dl_vocabulary(enum_val)
"stationmeta_global_climatic_zone_fk_cz_at_vocabulary_enum_val" FOREIGN KEY (climatic_zone) REFERENCES cz_vocabulary(enum_val)
"stationmeta_global_htap_region_tier1_fk_tr_vocabulary_enum_val" FOREIGN KEY (htap_region_tier1) REFERENCES tr_vocabulary(enum_val)
"stationmeta_global_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) ON DELETE CASCADE
"stationmeta_global_toar1_category_fk_tc_vocabulary_enum_val" FOREIGN KEY (toar1_category) REFERENCES tc_vocabulary(enum_val)
Referenced by:
-

stationmeta_global_services

field description: not publically available

                                      Table "public.stationmeta_global_services"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_global_services_id_seq'::regclass)
variable_name	character varying(64)		not null
result_type	integer		not null
result_nvalues	integer		not null
service_valid_year	integer
service_url	character varying(200)		not null
station_id	integer		not null

Indexes:
"stationmeta_global_services_pkey" PRIMARY KEY, btree (id)
"stationmeta_global_services_service_url_c8ee80c5_like" btree (service_url varchar_pattern_ops)
"stationmeta_global_services_service_url_key" UNIQUE CONSTRAINT, btree (service_url)
Check constraints:
"stationmeta_global_services_result_nvalues_check" CHECK (result_nvalues >= 0)
"stationmeta_global_services_result_type_check" CHECK (result_type >= 0)
"stationmeta_global_services_service_valid_year_check" CHECK (service_valid_year >= 0)
Foreign-key constraints:
"stationmeta_global_services_result_type_fk_rt_voc_enum_val" FOREIGN KEY (result_type) REFERENCES rt_vocabulary(enum_val)
"stationmeta_global_services_station_id_fkey" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
Referenced by:
-

stationmeta_roles

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#stationmetarole

                          Table "public.stationmeta_roles"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('stationmeta_roles_id_seq'::regclass)
role	integer		not null
status	integer		not null
contact_id	integer		not null

Indexes:
"stationmeta_roles_pkey" PRIMARY KEY, btree (id)
"stationmeta_roles_contact_id" btree (contact_id)
Check constraints:
"stationmeta_roles_role_check" CHECK (role >= 0)
"stationmeta_roles_status_check" CHECK (status >= 0)
Foreign-key constraints:
"stationmeta_roles_contact_id_fk_contacts_id" FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
"stationmeta_roles_role_fk_rc_vocabulary_enum_val" FOREIGN KEY (role) REFERENCES rc_vocabulary(enum_val)
"stationmeta_roles_status_fk_rs_vocabulary_enum_val" FOREIGN KEY (status) REFERENCES rs_vocabulary(enum_val)
Referenced by:
TABLE "stationmeta_core_stationmeta_roles" CONSTRAINT "stationmeta_core_stationmeta_roles_role_id_fkey" FOREIGN KEY (role_id) REFERENCES stationmeta_roles(id)

timeseries

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#timeseries

                                       Table "public.timeseries"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('timeseries_id_seq'::regclass)
label	character varying(128)		not null
order	integer		not null
access_rights	integer		not null
sampling_frequency	integer		not null
aggregation	integer		not null
data_start_date	timestamp with time zone		not null
data_end_date	timestamp with time zone		not null
sampling_height	double precision		not null
additional_metadata	jsonb		not null
station_id	integer
variable_id	integer
data_origin	integer		not null	1
data_origin_type	integer		not null	1
version	character varying(28)		not null	''
data_license_accepted	timestamp with time zone		not null	'1900-01-01'
programme_id	integer		not null	0

Indexes:
"timeseries_pkey" PRIMARY KEY, btree (id)
"timeseries_station_id_0f4fed9c" btree (station_id)
"timeseries_station_id_variable_id_label_055cfe3a_uniq" UNIQUE CONSTRAINT, btree (station_id, variable_id, label)
"timeseries_variable_id_dd9603f5" btree (variable_id)
Check constraints:
"timeseries_access_rights_check" CHECK (access_rights >= 0)
"timeseries_aggregation_check" CHECK (aggregation >= 0)
"timeseries_order_check" CHECK ("order" >= 0)
"timeseries_sampling_frequency_check" CHECK (sampling_frequency >= 0)
Foreign-key constraints:
"timeseries_access_rights_fk_da_vocabulary_enum_val" FOREIGN KEY (access_rights) REFERENCES da_vocabulary(enum_val)
"timeseries_aggregation_fk_at_vocabulary_enum_val" FOREIGN KEY (aggregation) REFERENCES at_vocabulary(enum_val)
"timeseries_measurement_method_fk_mm_vocabulary_enum_val" FOREIGN KEY (measurement_method) REFERENCES mm_vocabulary(enum_val)
"timeseries_programme_id_fk_timeseries_programmes_id" FOREIGN KEY (programme_id) REFERENCES timeseries_programmes(id)
"timeseries_sampling_frequency_fk_sf_vocabulary_enum_val" FOREIGN KEY (sampling_frequency) REFERENCES sf_vocabulary(enum_val)
"timeseries_data_origin_type_fk_ot_vocabulary_enum_val" FOREIGN KEY (data_origin_type) REFERENCES ot_vocabulary(enum_val) "timeseries_data_origin_fk_do_vocabulary_enum_val" FOREIGN KEY (data_origin) REFERENCES do_vocabulary(enum_val)
"timeseries_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) ON DELETE CASCADE
"timeseries_variable_id_dd9603f5_fk_variables_id" FOREIGN KEY (variable_id) REFERENCES variables(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "data_archive" CONSTRAINT "data_archive_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
TABLE "data" CONSTRAINT "data_timeseries_id_a38c5a1a_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
TABLE "timeseries_changelog" CONSTRAINT "timeseries_changelog_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id)
TABLE "timeseries_timeseries_annotations" CONSTRAINT "timeseries_timeseries_annotations_timeseries_id_fkey" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id)
TABLE "timeseries_timeseries_roles" CONSTRAINT "timeseries_timeseries_roles_timeseries_id_fkey" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id)

timeseries_annotations

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#timeseriesannotation

                                     Table "public.timeseries_annotations"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('timeseries_annotations_id_seq'::regclass)
kind	integer		not null
text	text		not null
date_added	timestamp with time zone		not null
approved	boolean		not null
contributor_id	integer		not null

Indexes:
"timeseries_annotations_pkey" PRIMARY KEY, btree (id)
"timeseries_annotations_contributor_id_7a1758f6" btree (contributor_id)
Check constraints:
"timeseries_annotations_kind_check" CHECK (kind >= 0)
Foreign-key constraints:
"timeseries_annotations_contributor_id_7a1758f6_fk_auth_user_id" FOREIGN KEY (contributor_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"timeseries_annotations_kind_fk_ak_vocabulary_enum_val" FOREIGN KEY (kind) REFERENCES ak_vocabulary(enum_val)
Referenced by:
TABLE "timeseries_timeseries_annotations" CONSTRAINT "timeseries_timeseries_annotations_annotation_id_fkey" FOREIGN KEY (annotation_id) REFERENCES timeseries_annotations(id)

timeseries_changelog

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#timeserieschangelog

                Table "public.timeseries_changelog"

Column	Type	Collation	Nullable	Default
datetime	timestamp with time zone		not null	now()
timeseries_id	bigint		not null
type_of_change	integer		not null
description	text		not null
old_value	character varying(256)		not null
new_value	character varying(256)		not null
period_start	timestamp with time zone
period_end	timestamp with time zone
version	character(28)
author_id	integer		not null

Indexes:
"timeseries_changelog_pkey" PRIMARY KEY, btree (datetime)
Check constraints:
-
Foreign-key constraints:
"timeseries_changelog_author_id_fk_auth_user_id" FOREIGN KEY (author_id) REFERENCES auth_user(id)
"timeseries_changelog_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id)
"timeseries_changelog_type_of_change_fk_cl_vocabulary_enum_val" FOREIGN KEY (type_of_change) REFERENCES cl_vocabulary(enum_val)
Referenced by:
-

timeseries_programmes

field description: timeseries_programmes

                                  Table "public.timeseries_programmes"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('timeseries_programmes_id_seq'::regclass)
name	character varying(32)		not null
longname	character varying(128)		not null
homepage	character varying(200)		not null
description	text		not null

Indexes:
"timeseries_programmes_pkey" PRIMARY KEY, btree (id)
Check constraints:
-
Foreign-key constraints:
-
Referenced by:
TABLE "timeseries" CONSTRAINT "timeseries_programme_id_fk_timeseries_programmes_id" FOREIGN KEY (programme_id) REFERENCES timeseries_programmes(id)

timeseries_roles

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#timeseriesrole

                          Table "public.timeseries_roles"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('timeseries_roles_id_seq'::regclass)
role	integer		not null
status	integer		not null
contact_id	integer		not null

Indexes:
"timeseries_roles_pkey" PRIMARY KEY, btree (id)
"timeseries_roles_contact_id" btree (contact_id)
Check constraints:
"timeseries_roles_role_check" CHECK (role >= 0)
"timeseries_roles_status_check" CHECK (status >= 0)
Foreign-key constraints:
"timeseries_roles_contact_id_fk_contacts_id" FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
"timeseries_roles_role_fk_rc_vocabulary_enum_val" FOREIGN KEY (role) REFERENCES rc_vocabulary(enum_val)
"timeseries_roles_status_fk_rs_vocabulary_enum_val" FOREIGN KEY (status) REFERENCES rs_vocabulary(enum_val)
Referenced by:
TABLE "timeseries_timeseries_roles" CONSTRAINT "timeseries_timeseries_roles_role_id_fkey" FOREIGN KEY (role_id) REFERENCES timeseries_roles(id)

variables

field description: http://esde.pages.jsc.fz-juelich.de/toar-data/toardb_fastapi/docs/toardb_fastapi.html#variable

                                     Table "public.variables"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('variables_id_seq'::regclass)
name	character varying(32)		not null
longname	character varying(128)		not null
displayname	character varying(128)		not null
cf_standardname	character varying(128)		not null
units	character varying(64)		not null
chemical_formula	character varying(128)		not null

Indexes:
"variables_pkey" PRIMARY KEY, btree (id)
"variables_name_9bc98a13_like" btree (name varchar_pattern_ops)
"variables_name_key" UNIQUE CONSTRAINT, btree (name)
Check constraints:
-
Foreign-key constraints:
-
Referenced by:
TABLE "timeseries" CONSTRAINT "timeseries_variable_id_dd9603f5_fk_variables_id" FOREIGN KEY (variable_id) REFERENCES variables(id) DEFERRABLE INITIALLY DEFERRED

changed the description

Changes to the database (including on delete cascade):

ALTER TABLE persons
    ADD COLUMN orcid CHARACTER(19) DEFAULT '0000-0000-0000-0000' NOT NULL;
ALTER TABLE timeseries
    ADD COLUMN version VARCHAR(28) DEFAULT '' NOT NULL;
ALTER TABLE timeseries
    ADD COLUMN data_license_accepted timestamp with time zone DEFAULT '1900-01-01' NOT NULL;
ALTER TABLE timeseries
    DROP COLUMN date_added;
ALTER TABLE timeseries
    DROP COLUMN date_modified;
ALTER TABLE timeseries
    DROP CONSTRAINT timeseries_station_id_0f4fed9c_fk_stationmeta_core_id,
    ADD CONSTRAINT timeseries_station_id_fk_stationmeta_core_id
    FOREIGN KEY (station_id)
    REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_core_stationmeta_roles 
    DROP CONSTRAINT stationmeta_core_stationmeta_roles_station_id_fkey,
    ADD CONSTRAINT stationmeta_core_stationmeta_roles_station_id_fkey
    FOREIGN KEY (station_id)
    REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_changelog 
    DROP CONSTRAINT stationmeta_changelog_station_id_fk_stationmeta_core_id,
    ADD CONSTRAINT stationmeta_changelog_station_id_fk_stationmeta_core_id
    FOREIGN KEY (station_id)
    REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_core_stationmeta_annotations
    DROP CONSTRAINT stationmeta_core_stationmeta_annotations_station_id_fkey,
    ADD CONSTRAINT stationmeta_core_stationmeta_annotations_fk_stationmeta_core_id
    FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_aux_doc
    DROP CONSTRAINT stationmeta_aux_doc_station_id_17bdb5f2_fk_stationmeta_core_id,
    ADD CONSTRAINT stationmeta_aux_doc_station_id_fk_stationmeta_core_id
    FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_aux_image
    DROP CONSTRAINT stationmeta_aux_imag_station_id_fbfbdb29_fk_stationme,
    ADD CONSTRAINT stationmeta_aux_image_station_id_fk_stationmeta_core_id
    FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_aux_url
    DROP CONSTRAINT stationmeta_aux_url_station_id_727571bd_fk_stationmeta_core_id,
    ADD CONSTRAINT stationmeta_aux_url_fk_stationmeta_core_id
    FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_global 
    DROP CONSTRAINT stationmeta_global_station_id_fk_stationmeta_core_id,
    ADD CONSTRAINT stationmeta_global_station_id_fk_stationmeta_core_id
    FOREIGN KEY (station_id) REFERENCES stationmeta_core(id)
    ON DELETE CASCADE;
ALTER TABLE timeseries_changelog 
    DROP CONSTRAINT timeseries_changelog_timeseries_id_fk_timeseries_id,
    ADD CONSTRAINT timeseries_changelog_timeseries_id_fk_timeseries_id
    FOREIGN KEY (timeseries_id)
    REFERENCES timeseries(id)
    ON DELETE CASCADE;
ALTER TABLE data      
    DROP CONSTRAINT data_timeseries_id_a38c5a1a_fk_timeseries_id,
    ADD CONSTRAINT data_timeseries_id_fk_timeseries_id         
    FOREIGN KEY (timeseries_id)
    REFERENCES timeseries(id)
    ON DELETE CASCADE;
ALTER TABLE data_archive      
    DROP CONSTRAINT data_archive_timeseries_id_fk_timeseries_id,
    ADD CONSTRAINT data_archive_timeseries_id_fk_timeseries_id         
    FOREIGN KEY (timeseries_id)
    REFERENCES timeseries(id)
    ON DELETE CASCADE;
ALTER TABLE timeseries_timeseries_roles 
    DROP CONSTRAINT timeseries_timeseries_roles_timeseries_id_fkey,
    ADD CONSTRAINT timeseries_timeseries_roles_timeseries_id_fkey
    FOREIGN KEY (timeseries_id)
    REFERENCES timeseries(id)
    ON DELETE CASCADE;
ALTER TABLE timeseries_timeseries_annotations
    DROP CONSTRAINT timeseries_timeseries_annotations_timeseries_id_fkey,
    ADD CONSTRAINT timeseries_timeseries_annotations_fk_timeseries_id
    FOREIGN KEY (timeseries_id) REFERENCES timeseries(id)
    ON DELETE CASCADE;
ALTER TABLE contacts 
    DROP CONSTRAINT contacts_organisation_id_fkey,
    ADD CONSTRAINT contacts_organisation_id_fkey
    FOREIGN KEY (organisation_id)
    REFERENCES organisations(id)
    ON DELETE CASCADE;
ALTER TABLE contacts 
    DROP CONSTRAINT contacts_person_id_fkey,
    ADD CONSTRAINT contacts_person_id_fkey
    FOREIGN KEY (person_id)
    REFERENCES persons(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_roles
    DROP CONSTRAINT stationmeta_roles_contact_id_fk_contacts_id,
    ADD CONSTRAINT stationmeta_roles_contact_id_fk_contacts_id
    FOREIGN KEY (contact_id)
    REFERENCES contacts(id)
    ON DELETE CASCADE;
ALTER TABLE stationmeta_core_stationmeta_roles 
    DROP CONSTRAINT stationmeta_core_stationmeta_roles_role_id_fkey,
    ADD CONSTRAINT stationmeta_core_stationmeta_roles_role_id_fkey
    FOREIGN KEY (role_id)
    REFERENCES stationmeta_roles(id)
    ON DELETE CASCADE;
ALTER TABLE timeseries_roles 
    DROP CONSTRAINT timeseries_roles_contact_id_fk_contacts_id,
    ADD CONSTRAINT timeseries_roles_contact_id_fk_contacts_id
    FOREIGN KEY (contact_id)
    REFERENCES contacts(id)
    ON DELETE CASCADE;
ALTER TABLE timeseries_timeseries_roles 
    DROP CONSTRAINT timeseries_timeseries_roles_role_id_fkey,
    ADD CONSTRAINT timeseries_timeseries_roles_role_id_fkey
    FOREIGN KEY (role_id)
    REFERENCES timeseries_roles(id)
    ON DELETE CASCADE;
ALTER TABLE timeseries
    RENAME COLUMN source TO data_origin_type;
ALTER TABLE timeseries
    RENAME COLUMN measurement_method TO data_origin;

After upgrading the controlled vocabulary rename constraints:

ALTER TABLE timeseries
    RENAME CONSTRAINT timeseries_source_fk_ds_vocabulary_enum_val TO timeseries_data_origin_type_fk_ot_vocabulary_enum_val;
ALTER TABLE timeseries
    RENAME CONSTRAINT timeseries_measurement_method_fk_mm_vocabulary_enum_val TO timeseries_data_origin_fk_do_vocabulary_enum_val;

For reasons of consistency the ordering within the controlled vocabulary changed (to always use 0 as default value):

ALTER TABLE timeseries ALTER COLUMN data_origin_type SET DEFAULT 0;
ALTER TABLE timeseries ALTER COLUMN data_origin SET DEFAULT 0;

changed the description

mentioned in commit f872505e

mentioned in commit 2830f88b

mentioned in commit d2564e3d