Migration process

Proposed database structure

Creation script

BEGIN;


CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority
(
    id uuid NOT NULL,
    natural_id character varying NOT NULL DEFAULT random(),
    source_file_id uuid,
    source character varying NOT NULL,
    heading character varying NOT NULL,
    heading_type character varying NOT NULL,
    _version integer,
    subject_heading_code "char",
    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_sft_heading
(
    authority_id uuid,
    sft_heading character varying
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_saft_heading
(
    authority_id uuid,
    saft_heading character varying
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_identifier
(
    authority_id uuid,
    identifier_type_id uuid,
    value character varying
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_note
(
    authority_id uuid,
    note_type_id uuid,
    value character varying,
    staff_only boolean
);

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_sft_heading
    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_saft_heading
    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_identifier
    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
    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
    ADD CONSTRAINT fk_authority_note_type_id FOREIGN KEY (note_type_id)
    REFERENCES diku_mod_entities_links.authority_note_type (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;