20231219_DML_CLEARINGHOUSE_STARTINGPOINT

[roger-mahler]

Create a new startingpoint for clearinghouse with only ceramics and isotope data.

The @2023.12 release will include full (and initial) releases of BugsCEP and dendrchronology data. All previously imported data of these data types are removed from the system. This includes data in the clearinghouse system. Current startingpoint for clearinghouse include dendrochronology, ceramics and isotope data.

[roger-mahler]

Created CR 20231219_DML_CLEARINGHOUSE_STARTINGPOINT.

bin/add-change-request --project subsystem --change 20231219_DML_CLEARINGHOUSE_STARTINGPOINT \
             --create-issue --note "Setup ClearingHouse with only ceramics and isotope data"

[roger-mahler]

A new startingpoint is created usingt the following steps:

1. Deploy a new staging database with current clearinghouse startingpoint.
2. Delete dendrochronology submissions using UDF fn_delete_submission.
3. Export remaining data from tables in clearing_house schema into gzip-files using export-tables script.
4. Deprecate previous startingpoint.

[roger-mahler]

Deploy a new staging database with current clearinghouse startingpoint

./bin/deploy-staging --create-database --on-conflict drop --source-type dump \
              --source ./starting_point/sead_master_9_public.sql.gz --target-db-name sead_staging_temp \
              --deploy-to-tag @2022.12

[roger-mahler]

Delete dendrochronology submissions.

select clearing_house.fn_delete_submission(2, true, true);
select clearing_house.fn_delete_submission(3, true, true);

[roger-mahler]

Deprecate previous startingpoint.

bin/rm-cr -c 20191221_DML_CLEARINGHOUSE_STARTINGPOINT

[roger-mahler]

Changes to clearinghouse schema

1. Fix identities in tbl_clearinghouse_submission_tables (using ids in current production)
2. Add new tables only if they don't exists.
3. Never remove tables from tbl_clearinghouse_submission_tables.
4. Add a check in fn_delete_submission that guards for non-existing tables.

[roger-mahler]

λ  mkdir -p subsystem/deploy/20231219_DML_CLEARINGHOUSE_STARTINGPOINT
λ bin/export-database --compress --target-folder subsystem/deploy/20231219_DML_CLEARINGHOUSE_STARTINGPOINT \
       sead_staging_temp clearing_house
info: dumped 46 tables to folder "subsystem/deploy/20231219_DML_CLEARINGHOUSE_STARTINGPOINT"
ignored 123 empty tables

Content of subsystem/deploy/20231219_DML_CLEARINGHOUSE_STARTINGPOINT/copy.in added to 20231219_DML_CLEARINGHOUSE_STARTINGPOINT with adjusted paths.