Page History

-------------------------------------------------------
-- Sequences for creating new IDs (primary keys) for tables.
-------------------------------------------------------
CREATE SEQUENCE conceptrelationtype_seq;
CREATE SEQUENCE conceptrelation_seq;

--------------------------------------------------------------------------------------------------------------
-- ADVANCED AUTHORITY CONTROL ENTITIES
--------------------------------------------------------------------------------------------------------------
CREATE TABLE scheme
(
  uuid              UUID PRIMARY KEY DEFAULT gen_random_uuid() REFERENCES dspaceobject(uuid),
  created           TIMESTAMP WITH TIME ZONE,
  modified          TIMESTAMP WITH TIME ZONE,
  name              VARCHAR(256) UNIQUE,
  lang              VARCHAR(24)
);

CREATE TABLE term
(
  uuid              UUID PRIMARY KEY DEFAULT gen_random_uuid() REFERENCES dspaceobject(uuid),
  created           TIMESTAMP WITH TIME ZONE,
  modified          TIMESTAMP WITH TIME ZONE,
  source            VARCHAR(256),
  status            VARCHAR(256),
  literalForm       TEXT,
  lang              VARCHAR(24),
  hidden            BOOL,
  discoverable      BOOL
);

CREATE TABLE concept
(
  uuid              UUID PRIMARY KEY DEFAULT gen_random_uuid() REFERENCES dspaceobject(uuid),
  created           TIMESTAMP WITH TIME ZONE,
  modified          TIMESTAMP WITH TIME ZONE,
  status            VARCHAR(256),
  lang              VARCHAR(24),
  source            VARCHAR(256),
  hidden            BOOL,
  replaced_by       UUID REFERENCES concept(uuid),
  preferred_term    UUID REFERENCES term(uuid)
);

CREATE TABLE conceptrelationtype
(
  id                INT PRIMARY KEY DEFAULT NEXTVAL('conceptrelationtype_seq'),
  hierarchical      BOOL,
  incoming_label    VARCHAR(64) UNIQUE,
  outgoing_label    VARCHAR(64) UNIQUE
);

--------------------------------------------------------------------------------------------------------------
-- ADVANCED AUTHORITY CONTROL RELATIONS
--------------------------------------------------------------------------------------------------------------
CREATE TABLE scheme2concept
(
  scheme_id   	    UUID REFERENCES scheme(uuid),
  concept_id   	    UUID REFERENCES concept(uuid),
  PRIMARY KEY(scheme_id, concept_id)
);

CREATE TABLE concept2concept
(
  id                INT PRIMARY KEY DEFAULT NEXTVAL('conceptrelation_seq'),
  incoming_id       UUID REFERENCES concept(uuid),
  outgoing_id       UUID REFERENCES concept(uuid),
  relation_type     INT REFERENCES conceptrelationtype(id)
);

CREATE TABLE concept2term
(
  concept_id   	    UUID REFERENCES concept(uuid),
  term_id   	      UUID REFERENCES term(uuid),
  PRIMARY KEY(concept_id, term_id)
);