From 24036ef94687300e98c742cfc268a2b5e9e87a88 Mon Sep 17 00:00:00 2001 From: Trygve Laugstøl Date: Sat, 5 Jan 2013 17:24:07 +0100 Subject: o Trying out dbdeploy. --- src/main/sql/base-schema/ddl-core.sql | 103 ++++++++++++++++++++++++++++++++++ 1 file changed, 103 insertions(+) create mode 100644 src/main/sql/base-schema/ddl-core.sql (limited to 'src/main/sql/base-schema/ddl-core.sql') 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; -- cgit v1.2.3