Skip to end of banner
Go to start of banner

Migration process

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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;


  • No labels