Page Comparison

Proposed database structure

Image RemovedImage Added

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(),     source_file_id uuid,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 (natural_id),     CONSTRAINT unq_heading UNIQUE (heading, heading_type)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_file
(      character varying 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 character varying(25) NOT NULL,     PRIMARY KEY (id) );  CREATE TABLEcharacter IFvarying 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
( not null,
    heading              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 TABLE IFnot NOTnull,
EXISTS diku_mod_entities_links.authority_metadata (  _version   id uuid NOT NULL,     authority_id uuid NOT NULLinteger,
    createdsubject_heading_date timestamp without time zone,code "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 NOT EXISTS diku_mod_entities_links.authority_sft_heading
(jsonb,
    notes  authority_id uuid,     sft_heading character varying );  CREATE TABLE IF NOTjsonb,
EXISTS diku_mod_entities_links.authority_saft_heading
(   PRIMARY KEY authority_(id),
uuid,    CONSTRAINT saftunq_heading character varyingUNIQUE (heading, heading_type)
);

CREATE TABLE IF NOT EXISTS diku_mod_entities_links.authority_source_identifierfile
(
    authority_id uuid,     identifier_type_id uuid,     value character varying );  CREATE TABLE IF        NOT EXISTS diku_mod_entities_links.authority_note
(NULL,
    name     character  authority_id uuidvarying(250) NOT NULL,
    note_type_id uuid,
    value character varying(25),
    staffbase_onlyurl booleancharacter );varying(250),
   CREATE TABLEsource IF NOT EXISTS diku_mod_entities_links.authority_note_type
(
    id uuid NOT NULL,
    name character varying(250) NOT NULL,
    source character varying(25) 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 time zoneNOT NULL,
    updated_datecode timestamp 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_source_file_metadata
(
   ADD id CONSTRAINT fk_authority_source_file_id FOREIGN KEY (source_file_id)           REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLE     ON UPDATE NOuuid ACTIONNOT NULL,
   ON DELETE NO ACTION
 authority_source_file_id uuid NOT NULL,
   NOT VALID;created_date   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)timestamp without time zone,
    updated_date REFERENCES diku_mod_entities_links.authority_source_file (id) MATCH SIMPLE     ON UPDATE NO ACTIONtimestamp without time zone,
 ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF    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 3

New Version 4

Key

Proposed database structure