Table migrations for updated table structures and data insertion

[hepplerj]

About a month ago, I devised a new data insertion script using straight SQL instead of the slightly convoluted way I was once doing this. This also means any new data dumps we get from DataScribe will be more easily handled by the new insertion script (bomr still handles the transformation from wide table to long table and has also been updated). There also exists new initialize up statements to create the initial table structures.

This should be more sustainable (and self explanatory), and depreciates the old updater Go script I wrote a while back, as well as removing any need on my end to fiddle with the database itself which I occasionally had to do. Local testing on my database indicates this all works fine. I'm documenting this here for any discussion before I move ahead with these changes.

There are two ways I think we can proceed, but I'm happy to hear ideas if there's something I've not thought of.

1. Perhaps the most straightforward is to simply dump the current tables and start fresh using initialize.up.sql. The new insert data script should work seamlessly with this. The data is already in a state that can be inserted, so any downtime should be minimal. I know @jmotis wants to complete some data/math checks before doing something this drastic, but on the other hand if we go this route we can have all available data in the databases (we're still behind on getting Bodleian updates in; this process would resolve that).
2. We can modify the existing tables. I've devised the ALTER TABLE statements we would need to do this, which I'll include below in an appendix. This still carries a possibility of minimal downtime. Local testing against the old DDLs and these commands all succeed.
3. Another potential option, which I haven't explored yet, would be to find a way to have something else (i.e., go-migrate) generate migration scripts for us to reflect table changes, similarly to how Django creates a path of migration scripts for table changes. In a way, we could treat the commands in the Appendix as a kind of migration step.

In all cases, there are also a couple of changes that need to happen in Apiary to reflect these updates. I'm already proceeding with the appropriate updates in a branch that we can merge the moment the database changes are complete.

See also #146, #165

Appendix: ALTER Table

The following SQL commands would mean following option 2 above and must be applied in the order that they appear here in order to avoid any CONSTRAINT violations (years; weeks; parishes; causes_of_death; bill_of_mortality).

Update the years table:

BEGIN;

-- First verify no violations
SELECT year 
FROM bom.year 
WHERE year <= 1400 OR year >= 1800;

-- If no violations found, add the constraint
ALTER TABLE bom.year
    ADD CONSTRAINT year_range_check 
    CHECK (year > 1400 AND year < 1800);

COMMIT;

Update the weeks table:

BEGIN;

-- First verify no violations of the new constraints
SELECT 'start_day violations' as check_type, count(*) 
FROM bom.week 
WHERE start_day < 1 OR start_day > 31
UNION ALL
SELECT 'end_day violations', count(*) 
FROM bom.week 
WHERE end_day < 1 OR end_day > 31
UNION ALL
SELECT 'week_no violations', count(*) 
FROM bom.week 
WHERE week_no < 1 OR week_no > 90;

-- Add the CHECK constraints if no violations found
ALTER TABLE bom.week
    ADD CONSTRAINT week_start_day_check 
        CHECK (start_day >= 1 AND start_day <= 31),
    ADD CONSTRAINT week_end_day_check 
        CHECK (end_day >= 1 AND end_day <= 31),
    ADD CONSTRAINT week_week_no_check 
        CHECK (week_no >= 1 AND week_no <= 90);

-- Add comment on the week_no constraint
COMMENT ON CONSTRAINT week_week_no_check ON bom.week 
    IS 'Week numbers 1-53 for regular weeks, 90 used to denote annual records';

-- Add new columns
ALTER TABLE bom.week 
    ADD COLUMN unique_identifier text,
    ADD COLUMN inserted_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN week_id text,
    ADD COLUMN year_range text;

COMMIT;

Update the parishes table:

BEGIN;

ALTER TABLE bom.parishes 
    ADD COLUMN inserted_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP;

COMMIT;

Update the causes of death table:

BEGIN;

-- Check for potential violations of the new unique constraint
SELECT death, year, week_id, COUNT(*)
FROM bom.causes_of_death
GROUP BY death, year, week_id
HAVING COUNT(*) > 1;

-- If no violations found, add new columns
ALTER TABLE bom.causes_of_death 
    ADD COLUMN source_name text,
    ADD COLUMN inserted_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP;

-- Add the unique constraint
ALTER TABLE bom.causes_of_death
    ADD CONSTRAINT causes_of_death_unique_record 
    UNIQUE (death, year, week_id);

COMMIT;

Update the bill of mortality table:

BEGIN;  -- Start a transaction so we can rollback if needed

  

-- First verify our counts before making changes. We do this to ensure no data issues are introduced. 

SELECT 
    COUNT(*) as total_rows,
    COUNT(CASE WHEN missing = 'FALSE' THEN 1 END) as missing_false_count,
    COUNT(CASE WHEN missing IS NULL THEN 1 END) as missing_null_count,
    COUNT(CASE WHEN illegible = 'FALSE' THEN 1 END) as illegible_false_count,
    COUNT(CASE WHEN illegible IS NULL THEN 1 END) as illegible_null_count
FROM bom.bill_of_mortality;

-- Rename year_id to year
ALTER TABLE bom.bill_of_mortality RENAME COLUMN year_id TO year;

-- Convert missing and illegible to boolean
-- Add new boolean columns

ALTER TABLE bom.bill_of_mortality 
    ADD COLUMN missing_bool boolean,
    ADD COLUMN illegible_bool boolean;

-- Convert the data, being explicit about the conversion

UPDATE bom.bill_of_mortality 

SET 
    missing_bool = CASE 
        WHEN missing = 'FALSE' THEN false
        WHEN missing IS NULL THEN NULL
    END,

    illegible_bool = CASE 
        WHEN illegible = 'FALSE' THEN false
        WHEN illegible IS NULL THEN NULL
    END;

-- Verify the conversion worked as expected

SELECT 
    COUNT(*) as total_rows,
    COUNT(CASE WHEN missing_bool = false THEN 1 END) as missing_false_count,
    COUNT(CASE WHEN missing_bool IS NULL THEN 1 END) as missing_null_count,
    COUNT(CASE WHEN illegible_bool = false THEN 1 END) as illegible_false_count,
    COUNT(CASE WHEN illegible_bool IS NULL THEN 1 END) as illegible_null_count
FROM bom.bill_of_mortality;

-- If the counts match, proceed with dropping old columns and renaming new ones

ALTER TABLE bom.bill_of_mortality 
    DROP COLUMN missing,
    DROP COLUMN illegible;

-- Then rename the new columns

ALTER TABLE bom.bill_of_mortality RENAME COLUMN missing_bool TO missing;
ALTER TABLE bom.bill_of_mortality RENAME COLUMN illegible_bool TO illegible;

-- Add new columns

ALTER TABLE bom.bill_of_mortality 
    ADD COLUMN source text,
    ADD COLUMN unique_identifier text,
    ADD COLUMN inserted_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP;

-- Before adding the unique constraint, check for any potential violations

SELECT parish_id, count_type, year, week_id, COUNT(*)
FROM bom.bill_of_mortality
GROUP BY parish_id, count_type, year, week_id
HAVING COUNT(*) > 1;

-- If no violations found, add the unique constraint

ALTER TABLE bom.bill_of_mortality 
    ADD CONSTRAINT bill_of_mortality_parish_id_count_type_year_week_id_key 
    UNIQUE (parish_id, count_type, year, week_id);

COMMIT;  -- If everything succeeded, commit the transaction

[qtrinh2]

After yesterday's meeting, we can go with option one.
Updates can be applied to the development database.
After verification, I can do a database dump of bom schema to load into production database.
The load to production database can be down outside of core hours since there will be minimal downtime.