Page Comparison

Proposed database structure

Image RemovedImage Added

Info
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)

Code Block

language	sql
title	Creation script
collapse	true

BEGIN; 
  CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority
(
    id  uuid NOT NULL,     natural_id character varying NOT NULL DEFAULT random(),    uuid source_file_id uuid,       source character varying NOT NULL,     heading character varying NOT NULL,     heading_type character varying NOT NULL, not null
  _version integer,     subject_heading_code "char"primary key,
    PRIMARY KEY (id),natural_id     CONSTRAINT unq_heading UNIQUE (heading, heading_type) ); character CREATEvarying TABLE IF NOT EXISTS diku_mod_entities_links.authority_default random() not null,
    source_file_id (     id uuid
 NOT NULL,     name character varying(250) NOT NULL,constraint fk_authority_source_file_id
      type character varying(25),     base_url character varying(250),
 references diku_mod_entities_links.authority_source_file,
    source   source character varying(25) NOT NULL,     PRIMARY KEY (id) );character varying CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_file_code (     authority_source_file_id uuid NOT NULL,   not null,
code character varying(20) NOT NULLheading );  CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_file_metadata (     idcharacter uuidvarying NOT NULL,     authority_source_file_id uuid NOT NULL,     created_date timestamp without timenot zonenull,
    updatedheading_datetype  timestamp without time zone,     created_by_user_id uuid,character varying      updated_by_user_id uuid,     PRIMARY KEY (id) );  CREATE TABLEnot IFnull,
NOT EXISTS diku_mod_entities_links.authority_metadata ( _version    id uuid NOT NULL,     authority_id uuidinteger,
NOT NULL,   subject_heading_code  created_date timestamp without time zone,"char",
    sft_headings     updated_date timestamp without time zonejsonb,
    created_by_user_id uuid,saft_headings       updated_by_user_id uuid jsonb,
    PRIMARYidentifiers   KEY (id) );  CREATE TABLE IF NOTjsonb,
EXISTS diku_mod_entities_links.authority_sft_heading (  notes   authority_id uuid,     sft_heading character varying );  CREATE TABLE IFjsonb,
NOT EXISTS diku_mod_entities_links.authority_saft_heading ( PRIMARY KEY   authority_id uuid,(id),
    CONSTRAINT saftunq_heading UNIQUE character varying(heading, heading_type)
);

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

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

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_notesource_typefile_metadatacode
(
    authority_source_file_id uuid NOT NULL,     authority_note_type_id uuid NOT NULL,     created_date timestamp without timeNOT zoneNULL,
    updated_date timestampcode without time zone,     created_by_user_id uuid,     updated_by_user_id uuid,     PRIMARY KEYcharacter varying(id20) NOT NULL
);

ALTERCREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority
    ADD CONSTRAINT fk_authority_source_file_idmetadata
FOREIGN KEY (source_file_id)
    REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLEid        ON UPDATE NO ACTION     ON DELETE NO ACTION    uuid NOT VALID;NULL,
  ALTER TABLE IF EXISTS diku_mod_entities_links.authority_ authority_source_file_codeid uuid NOT NULL,
  ADD CONSTRAINT fk_authority_source_file_id FOREIGN KEY (authority_source_file_id)created_date        REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLE timestamp without time zone,
ON UPDATE NO ACTION updated_date    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF          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_source_file_metadatanote_type
(
    id     uuid           ADD CONSTRAINT fk_authority_source_file_id FOREIGN KEY (authority_source_file_id)   NOT NULL,
REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLEname   character  ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IFvarying(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          ADD CONSTRAINT fk_authority_id FOREIGN KEY (authority_id)
           uuid NOT NULL,
   REFERENCES dikuauthority_modnote_entitiestype_links.authority (id) MATCHuuid SIMPLENOT NULL,
   ON UPDATEcreated_date NO ACTION
    ON DELETE NO ACTION   timestamp without NOTtime VALID;zone,
  ALTER TABLE IF EXISTS diku_mod_entities_links.authority_sft_heading updated_date        ADD CONSTRAINT fk_authority_id FOREIGNtimestamp KEY (authority_id)
without time zone,
   REFERENCES dikucreated_modby_entitiesuser_links.authority (id) MATCH SIMPLE   uuid,
 ON UPDATE NO ACTION updated_by_user_id     ONuuid,
DELETE NO ACTION  PRIMARY KEY  NOT VALID(id)
);


ALTER TABLE IF EXISTS diku_mod_entities_links.authority_saft_heading
    ADD CONSTRAINT fk_authority_source_file_id FOREIGN KEY (authoritysource_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_identifiersource_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_notefile_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_notemetadata
    ADD CONSTRAINT fk_authority_note_type_id FOREIGN KEY (noteauthority_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 END;

Code Block

language	sql
title	Migration script
collapse	true

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;

...

Versions Compared

Old Version 2

New Version Current

Key

Proposed database structure