20231120_DML_DENDRO_MODEL_UPDATE

[MattiasSealander]

tbl_dendro_dates needs structural changes based around how seasons are indicated. This means that the table can be simplified and that seasons can be indicated via tbl_seasons instead of the current table (tbl_season_or_qualifier).

The changes that can be made to the table are:

• remove tbl_season_or_qualifier relation
• add tbl_season relation
• remove tbl_error_uncertainty_id
• remove fields for dating error (error_plus and error_minus)

The structure of the table should thus be:

• dendro_date_id (PK)
• season_id (FK)
• dating_uncertainty_id (FK)
• dendro_lookup_id (FK)
• age_type_id (FK)
• analysis_entity_id (FK)
• age_older
• age_younger

[MattiasSealander]

As previous pilot project dataset for dendro is ingested according to the old structure, it will be affected by this change. The smoothest way to manage this is to take this dataset out of the database completely and apply the changes. The dataset will need to be updated anyway due to new splint statistics from the dendro lab in Lund. This update can thus be performed on the data before reingestion according to new structure.

[roger-mahler]

Implementation strategy

The previous pilot project test data will be completely removed from the SEAD database.

This change will be done retroactively i.e. affect all previously released database versions.

Any non-retroactive changes will be added to new CR 20231120_DML_DENDRO_MODEL_UPDATE. This CR might be removed if no (non-retroactively) changes are identified.

[roger-mahler]

Affected change requests

Original dendrochronology schema is defined in

• 20191213_DDL_DENDRO_MODEL

Previously imported lookup lookup data will be kept as is in the database:

• 20191213_DML_DENDRO_LOOKUP

Previous submissions (pilot project). These CRs will be removed from the release chain:

• 20191220_DML_SUBMISSION_DENDRO_ARCHEOLOGY_003_COMMIT
• 20191220_DML_SUBMISSION_DENDRO_BUILDING_002_COMMIT
• 20191221_DML_CLEARINGHOUSE_STARTINGPOINT

The CRs needs to be revised or reworked:

• 20221121_DML_QSE_DENDRO_DATING

The following CR has no effect and need to be investigated and possibly removed:

• 20221121_DML_DENDRO_LOOKUP_STANDARD_UPDATE
• 20221121_DML_DENDRO_ADD_ANALYSIS_METHODS

[roger-mahler]

Implementation checklist

Tasks needed to handle this schema change:

• [x] Rework SEAD dendrochronology database schema (https://github.com/humlab-sead/sead_change_control/issues/135#issuecomment-1820425703)
• [x] Regenerate corresponding tables in SEAD ClearingHouse
• [x] Remove dendro pilot data from 20191221_DML_CLEARINGHOUSE_STARTINGPOINT.
• [x] Review SEAD ClearingHouse UX (probably no change needed)
• [x] Update SEAD ClearingHouse commit system
• [x] Update Excel containing dendro data from pilot project
• [x] Generate and import dendro data from pilot project

Other tasks:

• [x] Remove 20221121_DML_DENDRO_LOOKUP_STANDARD_UPDATE from release chain.

[roger-mahler]

Rework SEAD dendrochronology database schema

• [x] Update dendrochronology model (20221121_DDL_DENDRO_MODEL rework of 20191213_DDL_DENDRO_MODEL):
  • [x] Remove columns season_or_qualifier_id, error_uncertainty_id, error_plus and error_minus
  • [x] Add column season_id
• [x] Revise view postgrest_api.qse_dendro_dating 20221121_DML_QSE_DENDRO_DATING
• [ ] Dendro facet DDL 20220831_DDL_NEW_DENDRO_FACETS
• [x] Remove dangling lookup tables:
  • [x] Remove table tbl_error_uncertainties
  • [x] Remove table tbl_season_or_qualifier

[roger-mahler]

Additional tasks:

• [x] Investigate where view postgrest_api.qse_dendro_dating 20221121_DML_QSE_DENDRO_DATING is used. (breaking change)

Breaking changes: columns error_plus, error_minus, error_uncertainty_type and season_or_qualifier_typeremoved.