o Trying out dbdeploy.

1 files changed, 103 insertions, 0 deletions

diff --git a/src/main/sql/base-schema/ddl-core.sql b/src/main/sql/base-schema/ddl-core.sql
new file mode 100644
index 0000000..6f1f101
--- /dev/null
+++ b/src/main/sql/base-schema/ddl-core.sql
@@ -0,0 +1,103 @@
+BEGIN;
+
+-- For dbdeploy
+CREATE TABLE changelog (
+  change_number INTEGER NOT NULL,
+  complete_dt TIMESTAMP NOT NULL,
+  applied_by VARCHAR(100) NOT NULL,
+  description VARCHAR(500) NOT NULL
+);
+
+ALTER TABLE changelog ADD CONSTRAINT pk_changelog PRIMARY KEY (change_number);
+
+DROP TABLE IF EXISTS build_participant;
+DROP TABLE IF EXISTS build;
+DROP SEQUENCE IF EXISTS build_seq;
+DROP TABLE IF EXISTS person_badge_progress;
+DROP TABLE IF EXISTS person_badge;
+DROP TABLE IF EXISTS person_jenkins_user;
+DROP TABLE IF EXISTS person;
+DROP TABLE IF EXISTS table_poller_status;
+
+CREATE TABLE table_poller_status (
+  poller_name VARCHAR(100) NOT NULL,
+  last_seq    INT          NOT NULL,
+  last_run    TIMESTAMP,
+  duration    INT,
+  status      VARCHAR(1000),
+  CONSTRAINT pk_job_status PRIMARY KEY (poller_name)
+);
+
+CREATE TABLE person (
+  uuid         CHAR(36)  NOT NULL,
+  created_date TIMESTAMP NOT NULL,
+  name         VARCHAR(100),
+  mail         VARCHAR(100),
+  CONSTRAINT pk_person PRIMARY KEY (uuid)
+);
+
+--The users from the different jenkins servers this user has claimed
+CREATE TABLE person_jenkins_user (
+  person       CHAR(36),
+  jenkins_user CHAR(36),
+  CONSTRAINT pk_person_jenkins_user PRIMARY KEY (person, jenkins_user),
+  CONSTRAINT fk_person_jenkins_user__person FOREIGN KEY (person) REFERENCES person (uuid),
+  CONSTRAINT fk_person_jenkins_user__jenkins_user FOREIGN KEY (jenkins_user) REFERENCES jenkins_user (uuid)
+);
+
+-- TODO: create a table with all badges?
+
+-- Badges received
+CREATE TABLE person_badge (
+  uuid         CHAR(36)     NOT NULL,
+  created_date TIMESTAMP    NOT NULL,
+
+  person       CHAR(36)     NOT NULL,
+  name         VARCHAR(100) NOT NULL,
+  level        INT          NOT NULL,
+  count        INT          NOT NULL,
+
+  CONSTRAINT pk_person_badge PRIMARY KEY (uuid),
+  CONSTRAINT uq_person_badge__person__name__level UNIQUE (person, name, level),
+  CONSTRAINT fk_person_badge__person FOREIGN KEY (person) REFERENCES person (uuid)
+);
+
+-- Badges the person is working on
+CREATE TABLE person_badge_progress (
+  uuid         CHAR(36)      NOT NULL,
+  created_date TIMESTAMP     NOT NULL,
+
+  person       CHAR(36)      NOT NULL,
+  badge        VARCHAR(100)  NOT NULL,
+  state        VARCHAR(8000) NOT NULL,
+
+  CONSTRAINT pk_person_badge_progress PRIMARY KEY (uuid),
+  CONSTRAINT fk_person_badge_progress__person FOREIGN KEY (person) REFERENCES person (uuid),
+  CONSTRAINT uq_person_badge_progress__person_badge UNIQUE (person, badge)
+);
+
+CREATE SEQUENCE build_seq;
+
+CREATE TABLE build (
+  uuid           CHAR(36)     NOT NULL,
+  created_date   TIMESTAMP    NOT NULL,
+
+  seq            INT          NOT NULL DEFAULT nextval('build_seq'),
+  timestamp      TIMESTAMP    NOT NULL,
+  success        BOOL         NOT NULL,
+
+  reference_type VARCHAR(100) NOT NULL,
+  reference_uuid CHAR(36)     NOT NULL,
+
+  CONSTRAINT pk_build PRIMARY KEY (uuid)
+);
+
+CREATE TABLE build_participant (
+  build  CHAR(36) NOT NULL,
+  person CHAR(36) NOT NULL,
+  CONSTRAINT pk_build_participant PRIMARY KEY (build, person),
+  CONSTRAINT fk_build_participant__build FOREIGN KEY (build) REFERENCES build (uuid),
+  CONSTRAINT fk_build_participant__person FOREIGN KEY (person) REFERENCES person (uuid)
+);
+
+COMMIT;