Migration process

Owned by Pavlo Smahin
Last updated: Jul 05, 2023
9 min read

Proposed database structure

sft_headings, saft_headings, identifiers, notes - jsonb array of objects
sft_heading, saft_heading - objects are object of the same type that contains heading and heading_type (same as heading_type column)


Creation script 
BEGIN;   CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority
(
    id                   uuid                               not null
        primary key,
    natural_id           character varying default random() not null,
    source_file_id       uuid
        constraint fk_authority_source_file_id
            references diku_mod_entities_links.authority_source_file,
    source               character varying                  not null,
    heading              character varying                  not null,
    heading_type         character varying                  not null,
    _version             integer,
    subject_heading_code "char",
    sft_headings         jsonb,
    saft_headings        jsonb,
    identifiers          jsonb,
    notes                jsonb,
    PRIMARY KEY (id),
    CONSTRAINT unq_heading UNIQUE (heading, heading_type)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_file
(
    id       uuid                   NOT NULL,
    name     character varying(250) NOT NULL,
    type     character varying(25),
    base_url character varying(250),
    source   character varying(25)  NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_file_code
(
    authority_source_file_id uuid                  NOT NULL,
    code                     character varying(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_file_metadata
(
    id                       uuid NOT NULL,
    authority_source_file_id uuid NOT NULL,
    created_date             timestamp without time zone,
    updated_date             timestamp without time zone,
    created_by_user_id       uuid,
    updated_by_user_id       uuid,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_metadata
(
    id                 uuid NOT NULL,
    authority_id       uuid NOT NULL,
    created_date       timestamp without time zone,
    updated_date       timestamp without time zone,
    created_by_user_id uuid,
    updated_by_user_id uuid,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_note_type
(
    id     uuid                   NOT NULL,
    name   character varying(250) NOT NULL,
    source character varying(25)  NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_note_type_metadata
(
    id                     uuid NOT NULL,
    authority_note_type_id uuid NOT NULL,
    created_date           timestamp without time zone,
    updated_date           timestamp without time zone,
    created_by_user_id     uuid,
    updated_by_user_id     uuid,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS diku_mod_entities_links.authority
    ADD CONSTRAINT fk_authority_source_file_id FOREIGN KEY (source_file_id)
        REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID;


ALTER TABLE IF EXISTS diku_mod_entities_links.authority_source_file_code
    ADD CONSTRAINT fk_authority_source_file_id FOREIGN KEY (authority_source_file_id)
        REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID;


ALTER TABLE IF EXISTS diku_mod_entities_links.authority_source_file_metadata
    ADD CONSTRAINT fk_authority_source_file_id FOREIGN KEY (authority_source_file_id)
        REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID;


ALTER TABLE IF EXISTS diku_mod_entities_links.authority_metadata
    ADD CONSTRAINT fk_authority_id FOREIGN KEY (authority_id)
        REFERENCES diku_mod_entities_links.authority (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID;


ALTER TABLE IF EXISTS diku_mod_entities_links.authority_note_type_metadata
    ADD CONSTRAINT fk_authority_note_type_id FOREIGN KEY (authority_note_type_id)
        REFERENCES diku_mod_entities_links.authority_note_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID;  END;


Migration script 
CREATE FUNCTION DIKU_MOD_ENTITIES_LINKS.GETVALUEANDFIELDNAME4(JSON_DATA JSONB) 
RETURNS TABLE (FIELD_VALUE text, FIELD_NAME TEXT) LANGUAGE PLPGSQL AS $$
BEGIN
    IF (json_data ->> 'personalName') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'personalName' AS field_value, 'personalName' AS field_name;
    ELSIF (json_data ->> 'personalNameTitle') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'personalNameTitle' AS field_value, 'personalNameTitle' AS field_name;
    ELSIF (json_data ->> 'corporateName') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'corporateName' AS field_value, 'corporateName' AS field_name;
    ELSIF (json_data ->> 'corporateNameTitle') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'corporateNameTitle' AS field_value, 'corporateNameTitle' AS field_name;
            ELSIF (json_data ->> 'meetingName') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'meetingName' AS field_value, 'meetingName' AS field_name;
            ELSIF (json_data ->> 'meetingNameTitle') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'meetingNameTitle' AS field_value, 'meetingNameTitle' AS field_name;
            ELSIF (json_data ->> 'uniformTitle') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'uniformTitle' AS field_value, 'uniformTitle' AS field_name;
            ELSIF (json_data ->> 'topicalTerm') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'topicalTerm' AS field_value, 'topicalTerm' AS field_name;
            ELSIF (json_data ->> 'geographicName') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'geographicName' AS field_value, 'geographicName' AS field_name;
            ELSIF (json_data ->> 'genreTerm') IS NOT NULL THEN
        RETURN QUERY SELECT json_data ->> 'genreTerm' AS field_value, 'genreTerm' AS field_name;
    ELSE
        RETURN QUERY SELECT 'none' AS field_value, 'none' AS field_name;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION diku_mod_entities_links.get_jsonb_field_value2(column_name text)
RETURNS TABLE (id uuid, field_value text) AS $$
BEGIN
    RETURN QUERY EXECUTE 'SELECT id, jsonb_array_elements_text(jsonb->' || quote_literal(column_name) || ') AS field_value FROM diku_mod_inventory_storage.authority';
END;
$$ LANGUAGE plpgsql;


INSERT INTO diku_mod_entities_links.authority_note_type(id, name, source)
SELECT id, jsonb->>'name', jsonb->>'source' FROM diku_mod_inventory_storage.authority_note_type;

INSERT INTO diku_mod_entities_links.authority_note_type_metadata(
	authority_note_type_id, created_date, updated_date, created_by_user_id, updated_by_user_id)
SELECT id, to_timestamp(jsonb->'metadata'->>'createdDate', 'YYYY-MM-DDTHH24:MI:SS.MS'), to_timestamp(jsonb->'metadata'->>'updatedDate', 'YYYY-MM-DDTHH24:MI:SS.MS'), (jsonb->'metadata'->>'createdByUserId')::uuid, (jsonb->'metadata'->>'updatedByUserId')::uuid FROM diku_mod_inventory_storage.authority_note_type;

INSERT INTO diku_mod_entities_links.authority_source_file(
	id, name, type, base_url, source)
SELECT id, jsonb->>'name', jsonb->>'type',jsonb->>'baseUrl', jsonb->>'source' FROM diku_mod_inventory_storage.authority_source_file;

INSERT INTO diku_mod_entities_links.authority_source_file_code(
	authority_source_file_id, code)
SELECT id, jsonb_array_elements_text(jsonb->'codes') FROM diku_mod_inventory_storage.authority_source_file;

INSERT INTO diku_mod_entities_links.authority_source_file_metadata(
	authority_source_file_id, created_date, updated_date, created_by_user_id, updated_by_user_id)
SELECT id, to_timestamp(jsonb->'metadata'->>'createdDate', 'YYYY-MM-DDTHH24:MI:SS.MS'), to_timestamp(jsonb->'metadata'->>'updatedDate', 'YYYY-MM-DDTHH24:MI:SS.MS'), (jsonb->'metadata'->>'createdByUserId')::uuid, (jsonb->'metadata'->>'updatedByUserId')::uuid FROM diku_mod_inventory_storage.authority_source_file;

INSERT INTO diku_mod_entities_links.authority(
	id, natural_id, source_file_id, source, 
    heading, heading_type, _version, subject_heading_code)
SELECT t.id, t.jsonb->>'naturalId', (t.jsonb->>'sourceFileId')::uuid,'MARC', 
f.field_value, f.field_name, 0, t.jsonb->>'subjectHeadings'
FROM diku_mod_inventory_storage.authority t
CROSS JOIN LATERAL diku_mod_entities_links.GetValueAndFieldName4(t.jsonb) f(field_value, field_name)
ON CONFLICT (heading, heading_type) DO NOTHING;

DO $$
DECLARE
    row_data RECORD;
BEGIN
    FOR row_data IN
        SELECT id, to_timestamp(jsonb->'metadata'->>'createdDate', 'YYYY-MM-DDTHH24:MI:SS.MS') as created_date, to_timestamp(jsonb->'metadata'->>'updatedDate', 'YYYY-MM-DDTHH24:MI:SS.MS') as updated_date, (jsonb->'metadata'->>'createdByUserId')::uuid as created_by_user_id, (jsonb->'metadata'->>'updatedByUserId')::uuid as updated_by_user_id
        FROM diku_mod_inventory_storage.authority
    LOOP
        BEGIN
            INSERT INTO diku_mod_entities_links.authority_metadata (authority_id, created_date, updated_date, created_by_user_id, updated_by_user_id)
            VALUES (row_data.id, row_data.created_date, row_data.updated_date, row_data.created_by_user_id, row_data.updated_by_user_id);
        EXCEPTION
            WHEN foreign_key_violation THEN
                RAISE NOTICE 'Skipping insertion for authority_id: %', row_data.id;
        END;
    END LOOP;
END;
$$;

DO $$
DECLARE
    row_data RECORD;
BEGIN
    FOR row_data IN
      SELECT id, TRIM(identifier_obj->>'value') AS value, MIN(identifier_obj->>'identifierTypeId') AS identifierTypeId
      FROM diku_mod_inventory_storage.authority, jsonb_array_elements(jsonb->'identifiers') AS identifier_obj
      GROUP BY id, TRIM(identifier_obj->>'value')
    LOOP
        BEGIN
            INSERT INTO diku_mod_entities_links.authority_identifier(authority_id, value, identifier_type_id)
            VALUES (row_data.id, row_data.value, (row_data.identifierTypeId)::uuid);
        EXCEPTION
            WHEN foreign_key_violation THEN
                RAISE NOTICE 'Skipping insertion for authority_id: %', row_data.id;
        END;
    END LOOP;
END;
$$;

DO $$
DECLARE
    row_data RECORD;
BEGIN
    FOR row_data IN
      SELECT id, TRIM(note_obj->>'value') AS value, (note_obj->>'noteTypeId')::uuid AS noteTypeId, note_obj->>'note' as note, note_obj->>'staffOnly' as staffOnly
      FROM diku_mod_inventory_storage.authority, jsonb_array_elements(jsonb->'notes') AS note_obj
    LOOP
        BEGIN
            INSERT INTO diku_mod_entities_links.authority_note(authority_id, note_type_id, value, staff_only)
            VALUES (row_data.id, row_data.noteTypeId, row_data.note, row_data.staffOnly::boolean);
        EXCEPTION
            WHEN foreign_key_violation THEN
                RAISE NOTICE 'Skipping insertion for authority_id: %', row_data.id;
        END;
    END LOOP;
END;
$$;

INSERT INTO diku_mod_entities_links.authority_saft_heading(authority_id, saft_heading)
SELECT e.id, i.field_value
FROM diku_mod_entities_links.authority e
JOIN diku_mod_entities_links.get_jsonb_field_value2('saft' || UPPER(SUBSTRING(e.heading_type, 1, 1)) || SUBSTRING(e.heading_type, 2)) i on e.id = i.id;

INSERT INTO diku_mod_entities_links.authority_sft_heading(authority_id, sft_heading)
SELECT e.id, i.field_value
FROM diku_mod_entities_links.authority e
JOIN diku_mod_entities_links.get_jsonb_field_value2('sft' || UPPER(SUBSTRING(e.heading_type, 1, 1)) || SUBSTRING(e.heading_type, 2)) i on e.id = i.id;