(Meta) Data versioning and changelog tables are defined

General Remark

Only processing logs that were operating on the database will go to the changelog tables defined below.
Processing logs from database insertion from the raw data (which was communicated with database providers) will be kept separately outside the database.

Changelog Tables

StationmetaChangelog

column name	type	description	required
datetime	timestamp with time zone		Yes
station_id	bigint		Yes
type_of_change	int	controlled -- see below	Yes
description	text		Yes
old_value	character varying(256)		Yes
new_value	character varying(256)		Yes
author	foreign reference unity	(see below)	Yes

Remarks:

column name	type	description	required
coordinate_validation_status	string		Yes
coordinate_validation_date	timestamp with time zone		Yes
coordinate_validator_id	integer		Yes

See Update from Oct 8th, 2020 (https://gitlab.version.fz-juelich.de/toar/toardb_fastapi/-/issues/17#note_61137) regarding coordinate_validation_status.

will be removed from StationmetaCoreBase and will only appear in the StationsChangelog table.

TimeseriesChangelog

All data ingestions (also extensions of time series) will also create a changelog entry!

column name	type	description	required
datetime	timestamp with time zone		Yes
timeseries_id	bigint		Yes
type_of_change	int	controlled -- see below	Yes
description	text		Yes
old_value	character varying(254)	(also for metadata updates)	Yes
new_value	character varying(254)	(also for metadata updates)	Yes
period_start	timestamp with time zone	for changes on data	No
period_end	timestamp with time zone	for changes on data	No
version	char(28)	the label of the new version, which is introduced by this change	Yes
author	foreign reference unity	(see below)	Yes

type_of_change – try with a common controlled vocabulary for both tables:

0 – created
1 – single value correction in metadata
2 – comprehensive metadata revision
3 – typographic correction of metadata
4 – unspecified data value corrections (this holds also, if there is only one single value to be corrected; the addition "unspecified" keeps all possibilities open to add "specified" corrections later (e. g. from QC)
5 – replaced data with a new version
6 – data value flagging
7 -

“whodunit“ (author):

(from "The FREE online Pro Git book"):
You may be wondering what the difference is between author and committer. The author is the person who originally wrote the patch, whereas the committer is the person who last applied the patch. So, if you send in a patch to a project and one of the core members applies the patch, both of you get credit — you as the author and the core member as the committer.
decision for TOAR database: Role of author is sufficient (no committer needed).

(Meta)Data Versions:

EXCURSUS: VERSION NUMBERS (STANDARDS)

PEP 386 defines the standard way to specify versions within the Python community.
verlib 0.1:
pip install verlib (Last released: Feb 15, 2012)

The most common scenarios:

• Major.Minor.Micro
• Major.Minor.Micro.alpha/beta

Here is an example that conforms to PEP 386:

version = { 'major': ...,
            'minor': ...,
            'micro': ...,
            'releaselevel': ...,  ('alpha', 'beta', 'final') 
            'serial': ... 
          }

Python Enhancement Proposals (PEPs): https://www.python.org/dev/peps

ACTUAL DECISIONS FOR TOARDB

Format of version string: "major.minor.micro" (e. g. 000001.000001.20200911100000)

micro:

• timestamp (yyyymmddhhMMss)
• used for all data insertions
  (by this approach snapshots of the database given a special date in the past can be accomplished even if time series have been extended)
• will not be logged in changelog tables

minor:

• string with 6 digits
• will be logged in changelog tables

major:

• string with 6 digits
• will be logged in changelog tables
• preliminary data will always have major version "000000"

Tests on Data Versions:

Tests on data versions will be done to clarify if indexing the string format may be slow (when accessing the data).
There might be a switch to big int and three columns for major, minor, micro.

some thoughts (not to be forgotten):

• use special hash index for version string
• what will happen, if (f. ex.):
  data available for the whole year, March has to be scaled (due to conversion error)
  --> period_start: March 1st, period_end: March 31st, description: "unit conversion..."
  --> version changes from 1.0 to 1.1 (for March)
  ==> now March values will be doubled:
  - version 1.0 ("wrong" values):These values will go to table "data_archive".
  - version 1.1 (converted values): These values will go to table "data".

added .todo label

changed the description

changed the description

changed the description

changed the description

changed the description

1. Extend extension "toar_controlled_vocabulary-0.1" with "type_of_change":
   new table CL_vocabulary ("ChangeLog vocabulary")

Done in commit e834cc26 (see below).

changed the description

mentioned in commit e834cc26

2. Create changelog tables

CREATE TABLE stationmeta_changelog (
    datetime timestamp with time zone DEFAULT now() NOT NULL PRIMARY KEY,
    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
);

ALTER TABLE stationmeta_changelog
    ADD CONSTRAINT stationmeta_changelog_station_id_fk_stationmeta_core_id FOREIGN KEY(station_id) REFERENCES stationmeta_core(id),
    ADD CONSTRAINT stationmeta_changelog_type_of_change_fk_cl_vocabulary_enum_val FOREIGN KEY(type_of_change) REFERENCES cl_vocabulary(enum_val),
    ADD CONSTRAINT stationmeta_changelog_author_id_fk_auth_user_id FOREIGN KEY(author_id) REFERENCES auth_user(id);

CREATE TABLE timeseries_changelog (
    datetime timestamp with time zone DEFAULT now() NOT NULL PRIMARY KEY,
    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
);

ALTER TABLE timeseries_changelog
    ADD CONSTRAINT timeseries_changelog_timeseries_id_fk_timeseries_id FOREIGN KEY(timeseries_id) REFERENCES timeseries(id),
    ADD CONSTRAINT timeseries_changelog_type_of_change_fk_cl_vocabulary_enum_val FOREIGN KEY(type_of_change) REFERENCES cl_vocabulary(enum_val),
    ADD CONSTRAINT timeseries_changelog_author_id_fk_auth_user_id FOREIGN KEY(author_id) REFERENCES auth_user(id);

changed the description

3. Extend data table with version string:

ALTER TABLE data
    ADD COLUMN version character(28) NOT NULL DEFAULT '000001.000000.00000000000000';

4. Remove coordinate_validation_status, coordinate_validation_date, and coordinate_validator_id from table "stationmeta_core":

ALTER TABLE stationmeta_core
    DROP COLUMN coordinate_validation_status,
    DROP COLUMN coordinate_validation_date,
    DROP COLUMN coordinate_validator_id;

BUT:

There are already entries from old table "stations", that will need to create entries in the table "stationmeta_changelog" (this holds already for UBA data already inserted in the new prototype database).

toardb_v2=# SELECT codes FROM stationmeta_core WHERE coordinate_validation_status=1;
   codes
-----------
 {DEUB028}
 {DEBW004}
 {DEBW022}
 {DEUB040}
 {DEUB002}
 {DEUB029}
(6 rows)

See Update from Oct 8th, 2020 (https://gitlab.version.fz-juelich.de/toar/toardb_fastapi/-/issues/17#note_61137) regarding coordinate_validation_status.

Just for information:
Get information for a specific station code (directly with psql from the database):

SELECT * FROM stationmeta_core WHERE 'DENW094'=ANY(codes);

changed the description

5. Implement new tables in source code

Done in commit ed5aab53 (see below).

mentioned in commit ed5aab53

mentioned in issue #9 (closed)

mentioned in commit 8640a43c

mentioned in commit f8d7c85e

mentioned in commit b3a7c520

changed the description

mentioned in commit 6d7ba4fa

mentioned in commit 0daad653

changed the description

Update:
coordinate_validation_status should stay in table "stationmeta_core"!

Therefore this column was again added to the table by the following command:

ALTER TABLE stationmeta_core
    ADD COLUMN coordinate_validation_status integer DEFAULT 0 NOT NULL,
    ADD CONSTRAINT stationmeta_core_coordinate_validation_status_fk_cv_vocabulary_enum_val FOREIGN KEY(coordinate_validation_status) REFERENCES cv_vocabulary(enum_val);

changed the description

mentioned in commit 870ac743

mentioned in commit 90d30342

mentioned in commit dd565d18

mentioned in commit b08950ba

removed .todo label

added .doing label

added enhancement label

to be discussed:

• data version string has to be required!!!
• does changes to metadata also result in a new version?! (we only have data versions!)

added discussion label

removed .doing label

added .done label

closed

changed the description