db.migration.V20180818.2114__structure.sql Maven / Gradle / Ivy

Go to download
drop table if exists issue cascade;

CREATE TABLE issue (
  id                 serial NOT NULL,
  uid                serial NOT NULL,
  version            integer   DEFAULT 1,
  user_profile       varchar,
  deleted            bool      DEFAULT false,
  creation_timestamp timestamp DEFAULT now(),
  author_id          integer,
  number             varchar,
  subject            varchar,
  issue_date         timestamp DEFAULT now(),
  planned_date       timestamp,
  status_code        varchar,
  status_date        timestamp DEFAULT current_timestamp,
  priority_code      varchar,
  reference_id       integer,
  reference_type     varchar,
  CONSTRAINT pk_issue_id PRIMARY KEY (id)
);

CREATE INDEX issue_reference_id_idx
  ON issue (reference_id);

CREATE INDEX issue_reference_type_idx
  ON issue (reference_type);


drop table if exists issue_attributes cascade;

CREATE TABLE issue_attributes (
  uid                serial,
  id                 serial NOT NULL,
  version            integer   DEFAULT 1,
  user_profile       varchar,
  deleted            bool      DEFAULT false,
  creation_timestamp timestamp DEFAULT current_timestamp,
  issue_id           integer,
  code               varchar,
  type               varchar,
  string_value       varchar,
  long_value         integer,
  decimal_value      numeric,
  date_value         date,
  date_time_value    timestamp,
  CONSTRAINT pk_issue_attributes_id PRIMARY KEY (id)
);

CREATE INDEX idx_issue_attributes_issue_id
  ON issue_attributes (issue_id);

ALTER TABLE issue_attributes
  ADD CONSTRAINT fk_issue_attributes_issue FOREIGN KEY (issue_id) REFERENCES issue (id);


drop table if exists issue_watcher cascade;

CREATE TABLE issue_watcher (
  uid                serial,
  id                 serial NOT NULL,
  version            integer   DEFAULT 1,
  user_profile       varchar,
  deleted            bool      DEFAULT false,
  creation_timestamp timestamp DEFAULT current_timestamp,
  issue_id           integer,
  user_id            integer
);

CREATE INDEX idx_issue_watcher_issue_id
  ON issue_watcher (issue_id);

ALTER TABLE issue_watcher
  ADD CONSTRAINT fk_issue_watcher_issue FOREIGN KEY (issue_id) REFERENCES issue (id);


drop table if exists issue_comment cascade;


CREATE TABLE issue_comment (
  uid                serial,
  id                 serial NOT NULL,
  version            integer   DEFAULT 1,
  user_profile       varchar,
  deleted            bool      DEFAULT false,
  creation_timestamp timestamp DEFAULT current_timestamp,
  issue_id           integer,
  author_id          integer,
  comment_date       timestamp DEFAULT current_timestamp,
  body               varchar,
  CONSTRAINT pk_issue_comment_id PRIMARY KEY (id)
);

CREATE INDEX idx_issue_comment_issue_id
  ON issue_comment (issue_id);

ALTER TABLE issue_comment
  ADD CONSTRAINT fk_issue_comment_issue FOREIGN KEY (issue_id) REFERENCES issue (id);

drop table if exists issue_file cascade;

CREATE TABLE issue_file (
  uid                serial,
  id                 serial NOT NULL,
  version            integer   DEFAULT 1,
  user_profile       varchar,
  deleted            bool      DEFAULT false,
  creation_timestamp timestamp DEFAULT current_timestamp,
  issue_id           integer,
  comment_id         integer,
  file_hash          varchar
);

CREATE INDEX idx_issue_file_issue_id
  ON issue_file (issue_id);

CREATE INDEX idx_issue_file_comment_id
  ON issue_file (comment_id);

ALTER TABLE issue_file
  ADD CONSTRAINT fk_issue_file_issue FOREIGN KEY (issue_id) REFERENCES issue (id);

ALTER TABLE issue_file
  ADD CONSTRAINT fk_issue_file_issue_comment FOREIGN KEY (comment_id) REFERENCES issue_comment (id);


drop table if exists issue_assignee cascade;

CREATE TABLE issue_assignee (
  uid                serial NOT NULL,
  id                 serial NOT NULL,
  version            integer   DEFAULT 1,
  user_profile       varchar,
  deleted            bool      DEFAULT false,
  creation_timestamp timestamp DEFAULT now(),
  issue_id           integer,
  user_id            integer,
  CONSTRAINT pk_issue_assignee_id PRIMARY KEY (id)
);

ALTER TABLE issue_assignee
  ADD CONSTRAINT fk_issue_assignee_issue FOREIGN KEY (issue_id) REFERENCES issue (id);