Schema_data_migration.txt

-- --------------------------------------------------------------------------------
-- About
--     migration script
--     postgresql 9.6.7
-- Content
--     structures
--     name
--     data structures level 1
--     data structures level 2
--     data structures level 3
--     data name level 1
--     data name level 2
--     data name level 3
--     index
--     latest
--     foreign key
--     index
--     sequence
--     primary key
--     function
-- Note
--     order of items is important
-- --------------------------------------------------------------------------------

-- --------------------------------------------------------------------------------
-- structures
-- --------------------------------------------------------------------------------
CREATE TABLE systemgroup (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    name character varying(255),
    mnemonic character varying(255),
    mnemonic_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);
CREATE TABLE system (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    parent_uuid character varying(255),
    name character varying(255),
    mnemonic character varying(255),
    mnemonic_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);

CREATE TABLE subsystem (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    parent_uuid character varying(255),
    name character varying(255),
    mnemonic character varying(255),
    mnemonic_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);
-- --------------------------------------------------------------------------------
CREATE TABLE discipline (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    name character varying(255),
    mnemonic character varying(255),
    mnemonic_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);

CREATE TABLE devicegroup (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    parent_uuid character varying(255),
    name character varying(255),
    mnemonic character varying(255),
    mnemonic_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);

CREATE TABLE devicetype (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    parent_uuid character varying(255),
    name character varying(255),
    mnemonic character varying(255),
    mnemonic_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);

-- --------------------------------------------------------------------------------
-- name
-- --------------------------------------------------------------------------------
CREATE TABLE name (
    id bigint NOT NULL,
    version integer,
    uuid character varying(255),
    systemgroup_uuid character varying(255),
    system_uuid character varying(255),
    subsystem_uuid character varying(255),
    devicetype_uuid character varying(255),
    instance_index character varying(255),
    convention_name character varying(255),
    convention_name_equivalence character varying(255),
    description character varying(255),
    status character varying(255),
    latest boolean NOT NULL,
    deleted boolean NOT NULL,
    requested timestamp without time zone,
    requested_by character varying(255),
    requested_comment character varying(255),
    processed timestamp without time zone,
    processed_by character varying(255),
    processed_comment character varying(255)
);

-- --------------------------------------------------------------------------------
-- data structures level 1
-- --------------------------------------------------------------------------------
insert into systemgroup (
    id,
    version,
    uuid,
    name,
    mnemonic,
    mnemonic_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select npr1.id, npr1."version", np1.uuid, npr1."name", npr1.mnemonic, npr1.mnemoniceqclass, npr1.description, npr1.status, false, npr1.deleted,
npr1.requestdate, ua_r.username as requestedBy, npr1.requestercomment, npr1.processdate, ua_p.username as processedBy, npr1.processorcomment
from namepartrevision npr1
inner join namepart np1 on npr1.namepart_id = np1.id
left join useraccount ua_r on npr1.requestedby_id = ua_r.id
left join useraccount ua_p on npr1.processedby_id = ua_p.id
where np1.nameparttype = 'SECTION' and npr1.parent_id is null;

insert into discipline (
    id,
    version,
    uuid,
    name,
    mnemonic,
    mnemonic_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select npr1.id, npr1."version", np1.uuid, npr1."name", npr1.mnemonic, npr1.mnemoniceqclass, npr1.description, npr1.status, false, npr1.deleted,
npr1.requestdate, ua_r.username as requestedBy, npr1.requestercomment, npr1.processdate, ua_p.username as processedBy, npr1.processorcomment
from namepartrevision npr1
inner join namepart np1 on npr1.namepart_id = np1.id
left join useraccount ua_r on npr1.requestedby_id = ua_r.id
left join useraccount ua_p on npr1.processedby_id = ua_p.id
where np1.nameparttype = 'DEVICE_TYPE' and npr1.parent_id is null;

-- --------------------------------------------------------------------------------
-- data structures level 2
-- --------------------------------------------------------------------------------
insert into system (
    id,
    version,
    uuid,
    parent_uuid,
    name,
    mnemonic,
    mnemonic_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select npr2.id, npr2."version", np2.uuid, np22.uuid,
npr2."name", npr2.mnemonic, npr2.mnemoniceqclass, npr2.description,
npr2.status, false, npr2.deleted, npr2.requestdate, ua_r.username as requestedBy, npr2.requestercomment, npr2.processdate, ua_p.username as processedBy, npr2.processorcomment
from namepartrevision npr2
inner join namepart np2  on npr2.namepart_id = np2.id
inner join namepart np22 on npr2.parent_id   = np22.id
left join useraccount ua_r on npr2.requestedby_id = ua_r.id
left join useraccount ua_p on npr2.processedby_id = ua_p.id
where np2.nameparttype = 'SECTION' and npr2.parent_id in
(select npr1.namepart_id from namepartrevision npr1, namepart np1 where npr1.namepart_id = np1.id and np1.nameparttype = 'SECTION' and npr1.parent_id is null);

insert into devicegroup (
    id,
    version,
    uuid,
    parent_uuid,
    name,
    mnemonic,
    mnemonic_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select npr2.id, npr2."version", np2.uuid, np22.uuid,
npr2."name", npr2.mnemonic, npr2.mnemoniceqclass, npr2.description,
npr2.status, false, npr2.deleted, npr2.requestdate, ua_r.username as requestedBy, npr2.requestercomment, npr2.processdate, ua_p.username as processedBy, npr2.processorcomment
from namepartrevision npr2
inner join namepart np2  on npr2.namepart_id = np2.id
inner join namepart np22 on npr2.parent_id   = np22.id
left join useraccount ua_r on npr2.requestedby_id = ua_r.id
left join useraccount ua_p on npr2.processedby_id = ua_p.id
where np2.nameparttype = 'DEVICE_TYPE' and npr2.parent_id in
(select npr1.namepart_id from namepartrevision npr1, namepart np1 where npr1.namepart_id = np1.id and np1.nameparttype = 'DEVICE_TYPE' and npr1.parent_id is null);

-- --------------------------------------------------------------------------------
-- data structures level 3
-- --------------------------------------------------------------------------------
insert into subsystem (
    id,
    version,
    uuid,
    parent_uuid,
    name,
    mnemonic,
    mnemonic_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select npr3.id, npr3."version", np3.uuid, np32.uuid,
npr3."name", npr3.mnemonic, npr3.mnemoniceqclass, npr3.description,
npr3.status, false, npr3.deleted, npr3.requestdate, ua_r.username as requestedBy, npr3.requestercomment, npr3.processdate, ua_p.username as processedBy, npr3.processorcomment
from namepartrevision npr3
inner join namepart np3  on npr3.namepart_id = np3.id
inner join namepart np32 on npr3.parent_id   = np32.id
left join useraccount ua_r on npr3.requestedby_id = ua_r.id
left join useraccount ua_p on npr3.processedby_id = ua_p.id
where np3.nameparttype = 'SECTION' and npr3.parent_id in
(
select npr2.namepart_id from namepartrevision npr2, namepart np2 where npr2.namepart_id = np2.id and np2.nameparttype = 'SECTION' and npr2.parent_id in
(select npr1.namepart_id from namepartrevision npr1, namepart np1 where npr1.namepart_id = np1.id and np1.nameparttype = 'SECTION' and npr1.parent_id is null)
);

insert into devicetype (
    id,
    version,
    uuid,
    parent_uuid,
    name,
    mnemonic,
    mnemonic_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select npr3.id, npr3."version", np3.uuid, np32.uuid,
npr3."name", npr3.mnemonic, npr3.mnemoniceqclass, npr3.description,
npr3.status, false, npr3.deleted, npr3.requestdate, ua_r.username as requestedBy, npr3.requestercomment, npr3.processdate, ua_p.username as processedBy, npr3.processorcomment
from namepartrevision npr3
inner join namepart np3  on npr3.namepart_id = np3.id
inner join namepart np32 on npr3.parent_id   = np32.id
left join useraccount ua_r on npr3.requestedby_id = ua_r.id
left join useraccount ua_p on npr3.processedby_id = ua_p.id
where np3.nameparttype = 'DEVICE_TYPE' and npr3.parent_id in
(
select npr2.namepart_id from namepartrevision npr2, namepart np2 where npr2.namepart_id = np2.id and np2.nameparttype = 'DEVICE_TYPE' and npr2.parent_id in
(select npr1.namepart_id from namepartrevision npr1, namepart np1 where npr1.namepart_id = np1.id and np1.nameparttype = 'DEVICE_TYPE' and npr1.parent_id is null)
);

-- --------------------------------------------------------------------------------
-- data name level 1
-- --------------------------------------------------------------------------------
insert into name (
    id,
    version,
    uuid,
    systemgroup_uuid,
    system_uuid,
    subsystem_uuid,
    devicetype_uuid,
    instance_index,
    convention_name,
    convention_name_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select dr.id, dr.version, d.uuid,
np_s.uuid,
null,
null,
np_d.uuid,
dr.instanceindex, dr.conventionname, dr.conventionnameeqclass, dr.additionalinfo, null, false, dr.deleted,
dr.requestdate, ua_r.username as requestedBy, null, null, null, dr.processorComment
from devicerevision dr
inner join device d on dr.device_id = d.id
inner join namepart np_s on dr.section_id    = np_s.id
left outer join namepart np_d on dr.devicetype_id = np_d.id
left join useraccount ua_r on dr.requestedby_id = ua_r.id
where dr.section_id in
(
select np.id from namepart np, namepartrevision npr where np.id = npr.namepart_id and npr.parent_id is null
);
-- --------------------------------------------------------------------------------
-- data name level 2
-- --------------------------------------------------------------------------------
insert into name (
    id,
    version,
    uuid,
    systemgroup_uuid,
    system_uuid,
    subsystem_uuid,
    devicetype_uuid,
    instance_index,
    convention_name,
    convention_name_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select dr.id, dr.version, d.uuid,
null,
np_s.uuid,
null,
np_d.uuid,
dr.instanceindex, dr.conventionname, dr.conventionnameeqclass, dr.additionalinfo, null, false, dr.deleted,
dr.requestdate, ua_r.username as requestedBy, null, null, null, dr.processorComment
from devicerevision dr
inner join device d on dr.device_id = d.id
inner join namepart np_s on dr.section_id    = np_s.id
left outer join namepart np_d on dr.devicetype_id = np_d.id
left join useraccount ua_r on dr.requestedby_id = ua_r.id
where dr.section_id in
(
select np.id from namepart np, namepartrevision npr where np.id = npr.namepart_id and npr.parent_id in
    (
    select np2.id from namepart np2, namepartrevision npr2 where np2.id = npr2.namepart_id and npr2.parent_id is null
    )
);

-- --------------------------------------------------------------------------------
-- data name level 3
-- --------------------------------------------------------------------------------
insert into name (
    id,
    version,
    uuid,
    systemgroup_uuid,
    system_uuid,
    subsystem_uuid,
    devicetype_uuid,
    instance_index,
    convention_name,
    convention_name_equivalence,
    description,
    status,
    latest,
    deleted,
    requested,
    requested_by,
    requested_comment,
    processed,
    processed_by,
    processed_comment
)
select dr.id, dr.version, d.uuid,
null,
null,
np_s.uuid,
np_d.uuid,
dr.instanceindex, dr.conventionname, dr.conventionnameeqclass, dr.additionalinfo, null, false, dr.deleted,
dr.requestdate, ua_r.username as requestedBy, null, null, null, dr.processorComment
from devicerevision dr
inner join device d on dr.device_id = d.id
inner join namepart np_s on dr.section_id    = np_s.id
left outer join namepart np_d on dr.devicetype_id = np_d.id
left join useraccount ua_r on dr.requestedby_id = ua_r.id
where dr.section_id in
(
select np.id from namepart np, namepartrevision npr where np.id = npr.namepart_id and npr.parent_id in
    (
    select np2.id from namepart np2, namepartrevision npr2 where np2.id = npr2.namepart_id and npr2.parent_id in
        (
        select np3.id from namepart np3, namepartrevision npr3 where np3.id = npr3.namepart_id and npr3.parent_id is null
        )
    )
);

-- --------------------------------------------------------------------------------
-- index
-- --------------------------------------------------------------------------------
CREATE INDEX systemgroup_id_idx ON public.systemgroup (id);
CREATE INDEX systemgroup_uuid_idx ON public.systemgroup (uuid);
CREATE INDEX systemgroup_mnemonic_idx ON public.systemgroup (mnemonic);
CREATE INDEX systemgroup_status_idx ON public.systemgroup (status);
CREATE INDEX systemgroup_deleted_idx ON public.systemgroup (deleted);

CREATE INDEX system_id_idx ON public.system (id);
CREATE INDEX system_uuid_idx ON public.system (uuid);
CREATE INDEX system_parent_uuid_idx ON public.system (parent_uuid);
CREATE INDEX system_mnemonic_idx ON public.system (mnemonic);
CREATE INDEX system_status_idx ON public.system (status);
CREATE INDEX system_deleted_idx ON public.system (deleted);

CREATE INDEX subsystem_id_idx ON public.subsystem (id);
CREATE INDEX subsystem_uuid_idx ON public.subsystem (uuid);
CREATE INDEX subsystem_parent_uuid_idx ON public.subsystem (parent_uuid);
CREATE INDEX subsystem_mnemonic_idx ON public.subsystem (mnemonic);
CREATE INDEX subsystem_status_idx ON public.subsystem (status);
CREATE INDEX subsystem_deleted_idx ON public.subsystem (deleted);

CREATE INDEX discipline_id_idx ON public.discipline (id);
CREATE INDEX discipline_uuid_idx ON public.discipline (uuid);
CREATE INDEX discipline_mnemonic_idx ON public.discipline (mnemonic);
CREATE INDEX discipline_status_idx ON public.discipline (status);
CREATE INDEX discipline_deleted_idx ON public.discipline (deleted);

CREATE INDEX devicegroup_id_idx ON public.devicegroup (id);
CREATE INDEX devicegroup_uuid_idx ON public.devicegroup (uuid);
CREATE INDEX devicegroup_parent_uuid_idx ON public.devicegroup (parent_uuid);
CREATE INDEX devicegroup_mnemonic_idx ON public.devicegroup (mnemonic);
CREATE INDEX devicegroup_status_idx ON public.devicegroup (status);
CREATE INDEX devicegroup_deleted_idx ON public.devicegroup (deleted);

CREATE INDEX devicetype_id_idx ON public.devicetype (id);
CREATE INDEX devicetype_uuid_idx ON public.devicetype (uuid);
CREATE INDEX devicetype_parent_uuid_idx ON public.devicetype (parent_uuid);
CREATE INDEX devicetype_mnemonic_idx ON public.devicetype (mnemonic);
CREATE INDEX devicetype_status_idx ON public.devicetype (status);
CREATE INDEX devicetype_deleted_idx ON public.devicetype (deleted);

CREATE INDEX name_uuid_idx ON public.name (uuid);

-- --------------------------------------------------------------------------------
-- latest
-- --------------------------------------------------------------------------------
update systemgroup sg set latest = true where sg.id = (
  select max(sg2.id) from systemgroup sg2 where sg2.uuid = sg.uuid and sg2.status = 'APPROVED'
);
update system sys set latest = true where sys.id = (
  select max(sys2.id) from system sys2 where sys2.uuid = sys.uuid and sys2.status = 'APPROVED'
);
update subsystem sub set latest = true where sub.id = (
  select max(sub2.id) from subsystem sub2 where sub2.uuid = sub.uuid and sub2.status = 'APPROVED'
);

update discipline di set latest = true where di.id = (
  select max(di2.id) from discipline di2 where di2.uuid = di.uuid and di2.status = 'APPROVED'
);
update devicegroup dg set latest = true where dg.id = (
  select max(dg2.id) from devicegroup dg2 where dg2.uuid = dg.uuid and dg2.status = 'APPROVED'
);
update devicetype dt set latest = true where dt.id = (
  select max(dt2.id) from devicetype dt2 where dt2.uuid = dt.uuid and dt2.status = 'APPROVED'
);

update name en set latest = true where en.id = (
  select max(en2.id) from name en2 where en2.uuid = en.uuid
);

-- --------------------------------------------------------------------------------
-- foreign key
-- --------------------------------------------------------------------------------

-- --------------------------------------------------------------------------------
-- index
-- --------------------------------------------------------------------------------
CREATE INDEX name_id_idx ON public.name (id);
CREATE INDEX name_namepartrevision_systemgroup_uuid_idx ON public.name (systemgroup_uuid);
CREATE INDEX name_namepartrevision_system_uuid_idx ON public.name (system_uuid);
CREATE INDEX name_namepartrevision_subsystem_uuid_idx ON public.name (subsystem_uuid);
CREATE INDEX name_namepartrevision_devicetype_uuid_idx ON public.name (devicetype_uuid);
CREATE INDEX name_convention_name_idx ON public.name (convention_name);
CREATE INDEX name_status_idx ON public.name (status);
CREATE INDEX name_deleted_idx ON public.name (deleted);

CREATE INDEX systemgroup_latest_idx ON public.systemgroup (latest);
CREATE INDEX system_latest_idx ON public.system (latest);
CREATE INDEX subsystem_latest_idx ON public.subsystem (latest);

CREATE INDEX discipline_latest_idx ON public.discipline (latest);
CREATE INDEX devicegroup_latest_idx ON public.devicegroup (latest);
CREATE INDEX devicetype_latest_idx ON public.devicetype (latest);

CREATE INDEX name_latest_idx ON public.name (latest);

-- --------------------------------------------------------------------------------
-- sequence
-- --------------------------------------------------------------------------------
CREATE SEQUENCE systemgroup_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE SEQUENCE system_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE SEQUENCE subsystem_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE SEQUENCE discipline_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE SEQUENCE devicegroup_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE SEQUENCE devicetype_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE SEQUENCE name_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

SELECT setval('systemgroup_id_seq', (select max(id) from systemgroup));
SELECT setval('system_id_seq', (select max(id) from "system"));
SELECT setval('subsystem_id_seq', (select max(id) from "subsystem"));
SELECT setval('discipline_id_seq', (select max(id) from discipline));
SELECT setval('devicegroup_id_seq', (select max(id) from devicegroup));
SELECT setval('devicetype_id_seq', (select max(id) from devicetype));
SELECT setval('name_id_seq', (select max(id) from "name"));

ALTER SEQUENCE systemgroup_id_seq OWNED BY systemgroup.id;
ALTER SEQUENCE system_id_seq OWNED BY system.id;
ALTER SEQUENCE subsystem_id_seq OWNED BY subsystem.id;
ALTER SEQUENCE discipline_id_seq OWNED BY discipline.id;
ALTER SEQUENCE devicegroup_id_seq OWNED BY devicegroup.id;
ALTER SEQUENCE devicetype_id_seq OWNED BY devicetype.id;
ALTER SEQUENCE name_id_seq OWNED BY name.id;

ALTER TABLE ONLY systemgroup ALTER COLUMN id SET DEFAULT nextval('systemgroup_id_seq'::regclass);
ALTER TABLE ONLY system ALTER COLUMN id SET DEFAULT nextval('system_id_seq'::regclass);
ALTER TABLE ONLY subsystem ALTER COLUMN id SET DEFAULT nextval('subsystem_id_seq'::regclass);
ALTER TABLE ONLY discipline ALTER COLUMN id SET DEFAULT nextval('discipline_id_seq'::regclass);
ALTER TABLE ONLY devicegroup ALTER COLUMN id SET DEFAULT nextval('devicegroup_id_seq'::regclass);
ALTER TABLE ONLY devicetype ALTER COLUMN id SET DEFAULT nextval('devicetype_id_seq'::regclass);
ALTER TABLE ONLY name ALTER COLUMN id SET DEFAULT nextval('name_id_seq'::regclass);

-- --------------------------------------------------------------------------------
-- primary key
-- --------------------------------------------------------------------------------
ALTER TABLE public.systemgroup ADD CONSTRAINT systemgroup_pk PRIMARY KEY (id);
ALTER TABLE public.system ADD CONSTRAINT system_pk PRIMARY KEY (id);
ALTER TABLE public.subsystem ADD CONSTRAINT subsystem_pk PRIMARY KEY (id);

ALTER TABLE public.discipline ADD CONSTRAINT discipline_pk PRIMARY KEY (id);
ALTER TABLE public.devicegroup ADD CONSTRAINT devicegroup_pk PRIMARY KEY (id);
ALTER TABLE public.devicetype ADD CONSTRAINT devicetype_pk PRIMARY KEY (id);

ALTER TABLE public.name ADD CONSTRAINT name_pk PRIMARY KEY (id);
-- --------------------------------------------------------------------------------

-- --------------------------------------------------------------------------------
-- function
-- --------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_mnemonic_path_system_structure(convention_name text)
    RETURNS text
    LANGUAGE plpgsql
AS
$$
DECLARE
    pos int;
BEGIN
    pos = strpos(convention_name, ':');
    IF pos > 0  THEN
        RETURN substr(convention_name, 1, pos-1);
    END IF;
    RETURN convention_name;
END;
$$;

CREATE OR REPLACE FUNCTION get_mnemonic_path_device_structure(convention_name text)
    RETURNS text
    LANGUAGE plpgsql
AS
$$
DECLARE
    pos int;
    mnemonic_path text;
    nbr_delimiters int;
BEGIN
    pos = strpos(convention_name, ':');
    IF pos > 0  THEN
        mnemonic_path = substr(convention_name, pos+1);
        nbr_delimiters = array_length(string_to_array(mnemonic_path, '-'), 1) - 1;
        IF nbr_delimiters = 2 then
            mnemonic_path = reverse(mnemonic_path);
            mnemonic_path = substr(mnemonic_path, strpos(mnemonic_path, '-')+1);
            RETURN reverse(mnemonic_path);
        ELSIF nbr_delimiters = 1 then
            return mnemonic_path;
        ELSE
            RETURN null;
        END IF;
    END IF;
    RETURN null;
END;
$$;

CREATE OR REPLACE FUNCTION public.get_instance_index(convention_name text)
 RETURNS text
 LANGUAGE plpgsql
AS
$$
DECLARE
    len int;
    pos int;
    mnemonic_path text;
    nbr_delimiters int;
BEGIN
    pos = strpos(convention_name, ':');
    IF pos > 0  THEN
        mnemonic_path = substr(convention_name, pos+1);
        nbr_delimiters = array_length(string_to_array(mnemonic_path, '-'), 1) - 1;
        IF nbr_delimiters = 2 then
            mnemonic_path = reverse(mnemonic_path);
            len = length(mnemonic_path);
            pos = strpos(mnemonic_path, '-');
            mnemonic_path = reverse(mnemonic_path);
            RETURN substr(mnemonic_path, len - pos + 2);
        ELSE
            RETURN null;
        END IF;
    END IF;
    RETURN null;
END;
$$;

CREATE OR REPLACE FUNCTION get_mnemonic_path_system(system_uuid text)
    RETURNS text
    LANGUAGE plpgsql
AS
$$
DECLARE
    system_mnemonic text;
BEGIN
    select mnemonic into system_mnemonic from "system" where uuid = system_uuid and latest = true;

    if system_mnemonic is not null then
        return system_mnemonic;
    else
        return null;
    end if;
END;
$$;

CREATE OR REPLACE FUNCTION get_mnemonic_path_subsystem(subsystem_uuid text)
    RETURNS text
    LANGUAGE plpgsql
AS
$$
DECLARE
    subsystem_mnemonic text;
    system_uuid text;
    system_mnemonic text;
BEGIN
    select parent_uuid, mnemonic into system_uuid, subsystem_mnemonic from subsystem where uuid = subsystem_uuid and latest = true;
    select mnemonic into system_mnemonic from "system" where uuid = system_uuid and latest = true;

    if system_mnemonic is not null and subsystem_mnemonic is not null then
        return concat(system_mnemonic, '-', subsystem_mnemonic);
    else
        return null;
    end if;
END;
$$;

CREATE OR REPLACE FUNCTION get_mnemonic_path_devicegroup(devicegroup_uuid text)
    RETURNS text
    LANGUAGE plpgsql
AS
$$
DECLARE
    discipline_uuid text;
    discipline_mnemonic text;
BEGIN
    select parent_uuid into discipline_uuid from devicegroup where uuid = devicegroup_uuid and latest = true;
    select mnemonic into discipline_mnemonic from discipline where uuid = discipline_uuid and latest = true;

    if discipline_mnemonic is not null then
        return discipline_mnemonic;
    else
        return null;
    end if;
END;
$$;

CREATE OR REPLACE FUNCTION get_mnemonic_path_devicetype(devicetype_uuid text)
    RETURNS text
    LANGUAGE plpgsql
AS
$$
DECLARE
    devicetype_mnemonic text;
    devicegroup_uuid text;
    discipline_uuid text;
    discipline_mnemonic text;
BEGIN
    select parent_uuid, mnemonic into devicegroup_uuid, devicetype_mnemonic from devicetype where uuid = devicetype_uuid and latest = true;
    select parent_uuid, mnemonic into discipline_uuid from devicegroup where uuid = devicegroup_uuid and latest = true;
    select mnemonic into discipline_mnemonic from discipline where uuid = discipline_uuid and latest = true;

    if discipline_mnemonic is not null and devicetype_mnemonic is not null then
        return concat(discipline_mnemonic, '-', devicetype_mnemonic);
    elsif devicetype_mnemonic is not null then
        return devicetype_mnemonic;
    else
        return null;
    end if;
END;
$$;