Sunset Future Oracle googlesheet as the location of lookup tables

zoyafuso-NOAA commented 7 months ago

Issue

The "Future Oracle.xlsx" google sheet is what we were working on to collate all of the reference or lookup tables (I assume I can use both terms interchangeably) used in the GAP_PRODUCTS process. These tables (represented by different tabs in the google sheet) include:

[ ] AREA: stratum/subarea/management area/region areas and descriptions
[ ] STRATUM_GROUPS: stratum groupings for each management area/region
[ ] SPECIES_YEAR: starting date for some taxa where taxonomic confidence occurs after the established start year of the time series, e.g., NRS/SRS 1996-on, etc.
[ ] SURVEY_DESIGN: tracks which version of the strata to use (coded by the DESIGN_YEAR field) to either update stratum areas or transition between different stanzas of stratification like in the GOA.
[ ] METADATA_TABLE, METADATA_COLUMN: field and table descriptions. Used in the metadata production of the GAP_PRODUCTS tables.

The current GAP_PRODUCTS production workflow is to download this google sheet locally within a user's cloned gap_products repo as flat files and then upload those files to the Oracle GAP_PRODUCTS schema before the Standard Data Products tables are created. The gapindex R package references the lookup tables in GAP_PRODUCTS and not the google sheet. Google sheets was an easy place to collate these reference tables because we can all view and easily manipulate the table during the development phase.

Proposed Solution

I think once we're close to finalizing these lookup tables (which I think we're fairly close), we should work to maintain these reference tables within Oracle and set up audit triggers whenever we add/delete/modify records. This would streamline the production run process and we could include those audit tables in a changelog. I think an audit system for these tables is appropriate because we expect these tables to not change as much as the Standard Data Products annually. I'm not sure how to set up audit triggers for a table in Oracle, so Ned will have to lead that effort if we decided to go down this route.

Expected Deadline

May 2024

zoyafuso-NOAA commented 4 months ago

@Ned-Laman-NOAA . In the push above, I created a sql script that houses some calls to create keys, audit tables, and insertion/deletion triggers for most of our GAP_PRODUCTS tables. I think I have a path for how to apply these audit tables to the four main tables (CPUE, BIOMASS, SIZECOMP, AGECOMP) so we don't have to drop the entire table and reupload every time we rerun the production scripts and instead we update the records that changed from the last production run. Glossing over a bunch of detail there but see if I coded these commands correctly.

Ned-Laman-NOAA commented 1 month ago

@zoyafuso-NOAA these look coded correctly to me. alternatively, you can roll all 3 triggers into a single trigger if you'd prefer. You can do these are Pre or Post triggers too.

--E.G., CREATE OR REPLACE TRIGGER RACE_EDIT.PREINSUPDDEL_RB2_MIXTURE BEFORE DELETE OR INSERT OR UPDATE ON RACE_EDIT.RB2_MIXTURE REFERENCING OLD AS PREV NEW AS NEW FOR EACH ROW BEGIN

IF INSERTING THEN

IF :NEW.PK_ID IS NULL THEN SELECT RB2_MIXTURE_PKSEQ.NextVal INTO :NEW.PK_ID FROM DUAL; END IF;

:NEW.DATE_ENTERED := SYSDATE; :NEW.ENTERED_BY := USER;

ELSIF UPDATING THEN

IF :PREV.EDITING_NOTE IS NOT NULL THEN SELECT :PREV.EDITING_NOTE||' / '||:NEW.EDITING_NOTE INTO :NEW.EDITING_NOTE FROM DUAL; END IF;

:NEW.DATE_MODIFIED := SYSDATE; :NEW.MODIFIED_BY := USER;

ELSIF DELETING THEN

INSERT INTO RACE_EDIT.RB2_MIXTURE_DELETIONS (
     PK_ID,
     DATE_ENTERED,
     AUDIT_FLAG,
     VESSEL,
     CRUISE,
     HAUL,
     SPECIES_CODE,
     SPECIES_NAME,
     NONSUB_WEIGHT,
     SUBSAMPLE_WEIGHT,
     SUBSAMPLE_NUMBERS,
     CALCULATED_WEIGHT,
     CALCULATED_NUMBERS,
     TOTAL_WEIGHT,
     TOTAL_NUMBERS,
     AVERAGE_WEIGHT,
     ENTRY_ORDER,
     DATE_AUDITED,
     RESOLVED_BY,
     DATE_RESOLVED,
     EDITING_NOTE,
     ENTERED_BY,
     DATE_MODIFIED,
     MODIFIED_BY,
     DATE_DELETED,
     DELETED_BY)
values(:PREV.PK_ID,
     :PREV.DATE_ENTERED,
     :PREV.AUDIT_FLAG,
     :PREV.VESSEL,
     :PREV.CRUISE,
     :PREV.HAUL,
     :PREV.SPECIES_CODE,
     :PREV.SPECIES_NAME,
     :PREV.NONSUB_WEIGHT,
     :PREV.SUBSAMPLE_WEIGHT,
     :PREV.SUBSAMPLE_NUMBERS,
     :PREV.CALCULATED_WEIGHT,
     :PREV.CALCULATED_NUMBERS,
     :PREV.TOTAL_WEIGHT,
     :PREV.TOTAL_NUMBERS,
     :PREV.AVERAGE_WEIGHT,
     :PREV.ENTRY_ORDER,
     :PREV.DATE_AUDITED,
     :PREV.RESOLVED_BY,
     :PREV.DATE_RESOLVED,
     :PREV.EDITING_NOTE,
     :PREV.ENTERED_BY,
     :PREV.DATE_MODIFIED,
     :PREV.MODIFIED_BY,
     SYSDATE,
     USER);

ELSE raise_application_error(-20000, 'Nope'); END IF;

END; /

zoyafuso-NOAA commented 1 month ago

Cool, thanks @Ned-Laman-NOAA for passing that along. I have started to play around with setting up the triggers so I'll incorporate those as well.

afsc-gap-products / gap_products