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-jenkins.sql | 79 ++++++++++++++++++++++++++++++++ 1 file changed, 79 insertions(+) create mode 100755 src/main/sql/base-schema/ddl-jenkins.sql (limited to 'src/main/sql/base-schema/ddl-jenkins.sql') diff --git a/src/main/sql/base-schema/ddl-jenkins.sql b/src/main/sql/base-schema/ddl-jenkins.sql new file mode 100755 index 0000000..3e420a2 --- /dev/null +++ b/src/main/sql/base-schema/ddl-jenkins.sql @@ -0,0 +1,79 @@ +BEGIN; + +DROP TABLE IF EXISTS jenkins_build; +DROP TABLE IF EXISTS jenkins_job; +DROP TABLE IF EXISTS jenkins_server; + +CREATE TABLE jenkins_server ( + uuid CHAR(36) NOT NULL, + created_date TIMESTAMP NOT NULL, + + url VARCHAR(1000) NOT NULL, + enabled BOOLEAN NOT NULL, + + CONSTRAINT pk_jenkins_server PRIMARY KEY (uuid), + CONSTRAINT uq_jenkins_server__url UNIQUE (url) +); + +CREATE TABLE jenkins_job ( + uuid CHAR(36) NOT NULL, + created_date TIMESTAMP NOT NULL, + + server CHAR(36) NOT NULL, + + file CHAR(36) NOT NULL, + url VARCHAR(1000) NOT NULL, + job_type VARCHAR(100) NOT NULL, + display_name VARCHAR(100), + + CONSTRAINT pk_jenkins_job PRIMARY KEY (UUID), + CONSTRAINT fk_jenkins_job__server FOREIGN KEY (server) REFERENCES jenkins_server (uuid), + CONSTRAINT fk_jenkins_job__file FOREIGN KEY (file) REFERENCES file (uuid), + CONSTRAINT uq_jenkins_job__url UNIQUE (url) +); + +CREATE SEQUENCE jenkins_build_seq; + +CREATE TABLE jenkins_build ( + uuid CHAR(36) NOT NULL, + created_date TIMESTAMP NOT NULL, + seq INT NOT NULL DEFAULT nextval('jenkins_build_seq'), + + job CHAR(36) NOT NULL, + + file CHAR(36) NOT NULL, + entry_id VARCHAR(1000) NOT NULL, + url VARCHAR(1000) NOT NULL, +-- result VARCHAR(100) NOT NULL, +-- number INT NOT NULL, +-- duration INT NOT NULL, +-- timestamp TIMESTAMP NOT NULL, + users CHAR(36) [], + + CONSTRAINT pk_jenkins_build PRIMARY KEY (UUID), + CONSTRAINT fk_jenkins_build__job FOREIGN KEY (job) REFERENCES jenkins_job (uuid), + CONSTRAINT fk_jenkins_build__file FOREIGN KEY (file) REFERENCES file (uuid), + CONSTRAINT uq_jenkins_build__id UNIQUE (entry_id), + CONSTRAINT uq_jenkins_build__seq UNIQUE (seq) +); + +CREATE INDEX ix_jenkins_build__created_date ON jenkins_build (created_date); + +CREATE TABLE jenkins_user ( + uuid CHAR(36) NOT NULL, + created_date TIMESTAMP NOT NULL, + + server CHAR(36) NOT NULL, + absolute_url VARCHAR(1000) NOT NULL, + CONSTRAINT pk_jenkins_user PRIMARY KEY (uuid), + CONSTRAINT fk_jenkins_user__server FOREIGN KEY (server) REFERENCES jenkins_server (uuid), + CONSTRAINT uq_jenkins_user__absolute_url UNIQUE (absolute_url) +); + +INSERT INTO jenkins_server (uuid, created_date, url, enabled) VALUES +('782a75f6-40a4-11e2-aca6-20cf30557fa0', CURRENT_TIMESTAMP, 'https://builds.apache.org', FALSE), +('4c473c86-40ad-11e2-ae61-20cf30557fa0', CURRENT_TIMESTAMP, 'http://ci.jruby.org', FALSE), +('518c6162-411b-11e2-b63c-20cf30557fa0', CURRENT_TIMESTAMP, 'http://www.simantics.org/jenkins', FALSE), +('3c1a1448-422c-11e2-a7b3-20cf30557fa0', CURRENT_TIMESTAMP, 'https://jenkins.puppetlabs.com', FALSE); + +COMMIT; -- cgit v1.2.3