aboutsummaryrefslogtreecommitdiff
path: root/src/main/sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/sql')
-rw-r--r--src/main/sql/base-schema/ddl-core.sql103
-rw-r--r--src/main/sql/base-schema/ddl-file.sql14
-rw-r--r--src/main/sql/base-schema/ddl-gitorious.sql40
-rwxr-xr-xsrc/main/sql/base-schema/ddl-jenkins.sql79
-rw-r--r--src/main/sql/base-schema/ddl-nexus.sql67
-rw-r--r--src/main/sql/base-schema/gitorious.org.sql11
-rw-r--r--src/main/sql/base-schema/logback.xml37
-rwxr-xr-xsrc/main/sql/base-schema/queries.sql2
-rw-r--r--src/main/sql/dbdelta/1-person_badge-add-timestamps.sql21
-rw-r--r--src/main/sql/dump_postgresql.sh3
-rw-r--r--src/main/sql/posgresql-ddl.sql500
11 files changed, 877 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;
diff --git a/src/main/sql/base-schema/ddl-file.sql b/src/main/sql/base-schema/ddl-file.sql
new file mode 100644
index 0000000..99e8bf6
--- /dev/null
+++ b/src/main/sql/base-schema/ddl-file.sql
@@ -0,0 +1,14 @@
+BEGIN;
+
+DROP TABLE IF EXISTS file;
+
+CREATE TABLE file (
+ uuid CHAR(36) NOT NULL,
+ created_date TIMESTAMP NOT NULL,
+ url VARCHAR(1000) NOT NULL,
+ content_type VARCHAR(100) NOT NULL,
+ data BYTEA,
+ CONSTRAINT pk_file PRIMARY KEY (uuid)
+);
+
+COMMIT;
diff --git a/src/main/sql/base-schema/ddl-gitorious.sql b/src/main/sql/base-schema/ddl-gitorious.sql
new file mode 100644
index 0000000..121a4ff
--- /dev/null
+++ b/src/main/sql/base-schema/ddl-gitorious.sql
@@ -0,0 +1,40 @@
+BEGIN;
+
+DROP TABLE IF EXISTS gitorious_event;
+DROP TABLE IF EXISTS gitorious_repository;
+DROP TABLE IF EXISTS gitorious_project;
+
+CREATE TABLE gitorious_project (
+ slug VARCHAR(1000) PRIMARY KEY
+);
+
+CREATE TABLE gitorious_repository (
+ project_slug VARCHAR(1000) NOT NULL,
+ name VARCHAR(1000) NOT NULL,
+ atom_feed VARCHAR(1000) NOT NULL,
+ last_update TIMESTAMP,
+ last_successful_update TIMESTAMP,
+ CONSTRAINT gitorious_repository_pk PRIMARY KEY (project_slug, name),
+ CONSTRAINT gitorious_repository_2_gitorious_project FOREIGN KEY (project_slug) REFERENCES gitorious_project (slug)
+);
+
+CREATE TABLE gitorious_event (
+ project_slug VARCHAR(1000) NOT NULL,
+ name VARCHAR(1000) NOT NULL,
+
+-- The raw values for debugging
+ entry_id VARCHAR(1000) PRIMARY KEY,
+ published TIMESTAMP NOT NULL,
+ title VARCHAR(1000),
+ content VARCHAR(1000),
+
+ event_type VARCHAR(20),
+ who VARCHAR(100),
+-- Push
+ "from" CHAR(40),
+ "to" CHAR(40),
+ branch VARCHAR(100),
+ commit_count INTEGER
+);
+
+COMMIT;
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;
diff --git a/src/main/sql/base-schema/ddl-nexus.sql b/src/main/sql/base-schema/ddl-nexus.sql
new file mode 100644
index 0000000..8acf741
--- /dev/null
+++ b/src/main/sql/base-schema/ddl-nexus.sql
@@ -0,0 +1,67 @@
+BEGIN;
+
+DROP TABLE IF EXISTS nexus_event;
+DROP TABLE IF EXISTS nexus_artifact;
+DROP TABLE IF EXISTS nexus_repository;
+DROP TABLE IF EXISTS nexus_server;
+
+CREATE TABLE nexus_server (
+ uuid CHAR(36) NOT NULL,
+ url VARCHAR(1000) NOT NULL,
+ name VARCHAR(1000) NOT NULL,
+ CONSTRAINT pk_nexus_server PRIMARY KEY (uuid),
+ CONSTRAINT uq_nexus_server_url UNIQUE (url)
+);
+
+CREATE TABLE nexus_repository (
+ uuid CHAR(36) NOT NULL,
+ server CHAR(36) NOT NULL,
+ id VARCHAR(100),
+ group_ids VARCHAR(100) [],
+ CONSTRAINT pk_nexus_repository PRIMARY KEY (uuid),
+ CONSTRAINT fk_nexus_repository__nexus_server FOREIGN KEY (server) REFERENCES nexus_server (uuid),
+ CONSTRAINT uq_nexus_repository__id UNIQUE (server, id)
+);
+
+CREATE TABLE nexus_artifact (
+ uuid CHAR(36) NOT NULL,
+ repository CHAR(36) NOT NULL,
+ group_id VARCHAR(100) NOT NULL,
+ artifact_id VARCHAR(100) NOT NULL,
+ version VARCHAR(100) NOT NULL,
+ CONSTRAINT pk_nexus_artifact PRIMARY KEY (uuid),
+ CONSTRAINT uq_nexus_artifact__gid__aid__version UNIQUE (group_id, artifact_id, version),
+ CONSTRAINT fk_nexus_artifact__nexus_repository FOREIGN KEY (repository) REFERENCES nexus_repository (uuid)
+);
+
+CREATE TABLE nexus_event (
+ uuid CHAR(36) NOT NULL,
+ artifact CHAR(36) NOT NULL,
+ created TIMESTAMP NOT NULL,
+
+-- From the RSS
+ guid VARCHAR(1000) NOT NULL,
+ date TIMESTAMP NOT NULL,
+
+-- Our type flag
+ type VARCHAR(100) NOT NULL,
+
+-- new snapshot event
+ snapshot_timestamp VARCHAR(100),
+ build_number INT,
+ file VARCHAR(1000),
+ who VARCHAR(1000),
+ CONSTRAINT pk_nexus_event PRIMARY KEY (uuid),
+ CONSTRAINT fk_nexus_event__artifact FOREIGN KEY (artifact) REFERENCES nexus_artifact (uuid),
+ CONSTRAINT uq_nexus_event__guid UNIQUE (guid),
+ CONSTRAINT check_event_type CHECK (type IN ('new_snapshot', 'new_release'))
+-- CONSTRAINT pk_nexus_event PRIMARY KEY (timestamp, server_url, repository_id, group_id, artifact_id, version)
+);
+
+INSERT INTO nexus_server (uuid, url, name) VALUES ('4666dba4-3e2e-11e2-8a1b-0bd430e00b36', 'https://oss.sonatype.org', 'Central Repository');
+INSERT INTO nexus_repository (uuid, server, id, group_ids) VALUES ('4a2d7ab2-3e2f-11e2-af03-eb1ace2381bb', '4666dba4-3e2e-11e2-8a1b-0bd430e00b36', 'releases', ARRAY ['io.trygvis', 'no.arktekk', 'org.codehaus']);
+
+INSERT INTO nexus_server (uuid, url, name) VALUES ('91d942d8-3e2f-11e2-aaa0-a70628365abd', 'http://nexus.codehaus.org', 'Codehaus Snapshots');
+INSERT INTO nexus_repository (uuid, server, id, group_ids) VALUES ('a2415b88-3e2f-11e2-a2b8-2f066b90cf13', '91d942d8-3e2f-11e2-aaa0-a70628365abd', 'snapshots', ARRAY ['org.codehaus.mojo']);
+
+COMMIT;
diff --git a/src/main/sql/base-schema/gitorious.org.sql b/src/main/sql/base-schema/gitorious.org.sql
new file mode 100644
index 0000000..51a015b
--- /dev/null
+++ b/src/main/sql/base-schema/gitorious.org.sql
@@ -0,0 +1,11 @@
+-- Test data for my projects at gitorious.org
+BEGIN;
+
+INSERT INTO gitorious_project VALUES ('esper-test-project');
+INSERT INTO gitorious_repository VALUES ('esper-test-project', 'esper-test-project', 'https://gitorious.org/esper-test-project/esper-test-project.atom');
+
+INSERT INTO subscriber VALUES ('trygvis');
+INSERT INTO subscription_gitorious_repository VALUES ('trygvis', 'esper-test-project', 'esper-test-project');
+
+COMMIT;
+-
diff --git a/src/main/sql/base-schema/logback.xml b/src/main/sql/base-schema/logback.xml
new file mode 100644
index 0000000..460df3a
--- /dev/null
+++ b/src/main/sql/base-schema/logback.xml
@@ -0,0 +1,37 @@
+<configuration debug="false">
+
+ <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
+ <file>logs/${logging.app}.log</file>
+ <append>true</append>
+ <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
+ <fileNamePattern>logs/${logging.app}-%d.log</fileNamePattern>
+ </rollingPolicy>
+ <encoder>
+ <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %-30logger{1} - %msg%n</pattern>
+ </encoder>
+ </appender>
+
+ <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
+ <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
+ <level>INFO</level>
+ </filter>
+ <encoder>
+ <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %-30logger{1} - %msg%n</pattern>
+ </encoder>
+ </appender>
+
+ <logger name="org.apache.http.headers" level="INFO"/>
+ <logger name="org.apache.http.wire" level="INFO"/>
+ <logger name="org.apache.http" level="INFO"/>
+ <!-- Gitorious spews out a few invalid cookies -->
+ <logger name="org.apache.http.client.protocol.ResponseProcessCookies" level="ERROR"/>
+
+ <logger name="io.trygvis.esper.testing.util.HttpClient" level="INFO"/>
+
+ <logger name="org.apache.shiro" level="DEBUG"/>
+
+ <root>
+ <appender-ref ref="STDOUT"/>
+ <appender-ref ref="FILE"/>
+ </root>
+</configuration>
diff --git a/src/main/sql/base-schema/queries.sql b/src/main/sql/base-schema/queries.sql
new file mode 100755
index 0000000..66bdb38
--- /dev/null
+++ b/src/main/sql/base-schema/queries.sql
@@ -0,0 +1,2 @@
+-- All unclaimed jenkins users
+select absolute_url from jenkins_user where uuid not in (select jenkins_user from person_jenkins_user);
diff --git a/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
new file mode 100644
index 0000000..5b53145
--- /dev/null
+++ b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
@@ -0,0 +1,21 @@
+ALTER TABLE person_badge ADD timestamps TIMESTAMP[];
+
+DO $$
+DECLARE r RECORD;
+DECLARE ts TIMESTAMP[];
+BEGIN
+ FOR r IN SELECT uuid, count FROM person_badge
+ LOOP
+ SELECT array_agg(x) FROM (SELECT current_timestamp FROM generate_series(1, r.count)) AS x INTO ts;
+ UPDATE person_badge SET timestamps=ts WHERE uuid=r.uuid;
+ END LOOP;
+END$$;
+
+ALTER TABLE person_badge DROP count;
+
+--//@UNDO
+
+ALTER TABLE person_badge ADD count INT;
+UPDATE person_badge SET count=array_length(timestamps, 1);
+ALTER TABLE person_badge ALTER count SET NOT NULL;
+ALTER TABLE person_badge DROP timestamps;
diff --git a/src/main/sql/dump_postgresql.sh b/src/main/sql/dump_postgresql.sh
new file mode 100644
index 0000000..4253bd5
--- /dev/null
+++ b/src/main/sql/dump_postgresql.sh
@@ -0,0 +1,3 @@
+#!/bin/bash
+
+pg_dump --schema=public --schema-only -U esper -h localhost esper > src/main/resources/posgresql-ddl.sql
diff --git a/src/main/sql/posgresql-ddl.sql b/src/main/sql/posgresql-ddl.sql
new file mode 100644
index 0000000..4a274a8
--- /dev/null
+++ b/src/main/sql/posgresql-ddl.sql
@@ -0,0 +1,500 @@
+--
+-- PostgreSQL database dump
+--
+
+SET statement_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+
+SET search_path = public, pg_catalog;
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: apache_user; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE apache_user (
+ id character varying(100),
+ name character varying(100)
+);
+
+
+ALTER TABLE public.apache_user OWNER TO esper;
+
+--
+-- Name: build_seq; Type: SEQUENCE; Schema: public; Owner: esper
+--
+
+CREATE SEQUENCE build_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.build_seq OWNER TO esper;
+
+--
+-- Name: build; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE build (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ seq integer DEFAULT nextval('build_seq'::regclass) NOT NULL,
+ "timestamp" timestamp without time zone NOT NULL,
+ success boolean NOT NULL,
+ reference_type character varying(100) NOT NULL,
+ reference_uuid character(36) NOT NULL
+);
+
+
+ALTER TABLE public.build OWNER TO esper;
+
+--
+-- Name: build_participant; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE build_participant (
+ build character(36) NOT NULL,
+ person character(36) NOT NULL
+);
+
+
+ALTER TABLE public.build_participant OWNER TO esper;
+
+--
+-- Name: file; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE file (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ url character varying(1000) NOT NULL,
+ content_type character varying(100) NOT NULL,
+ data bytea
+);
+
+
+ALTER TABLE public.file OWNER TO esper;
+
+--
+-- Name: jenkins_build_seq; Type: SEQUENCE; Schema: public; Owner: esper
+--
+
+CREATE SEQUENCE jenkins_build_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.jenkins_build_seq OWNER TO esper;
+
+--
+-- Name: jenkins_build; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE jenkins_build (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ seq integer DEFAULT nextval('jenkins_build_seq'::regclass) NOT NULL,
+ job character(36) NOT NULL,
+ file character(36) NOT NULL,
+ entry_id character varying(1000) NOT NULL,
+ url character varying(1000) NOT NULL,
+ result character varying(100),
+ number integer,
+ duration integer,
+ "timestamp" timestamp without time zone,
+ users character(36)[]
+);
+
+
+ALTER TABLE public.jenkins_build OWNER TO esper;
+
+--
+-- Name: jenkins_job; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE jenkins_job (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ server character(36) NOT NULL,
+ file character(36) NOT NULL,
+ url character varying(1000) NOT NULL,
+ job_type character varying(100) NOT NULL,
+ display_name character varying(100)
+);
+
+
+ALTER TABLE public.jenkins_job OWNER TO esper;
+
+--
+-- Name: jenkins_server; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE jenkins_server (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ url character varying(1000) NOT NULL,
+ enabled boolean NOT NULL
+);
+
+
+ALTER TABLE public.jenkins_server OWNER TO esper;
+
+--
+-- Name: jenkins_user; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE jenkins_user (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ server character(36) NOT NULL,
+ absolute_url character varying(1000) NOT NULL
+);
+
+
+ALTER TABLE public.jenkins_user OWNER TO esper;
+
+--
+-- Name: person; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE person (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ name character varying(100),
+ mail character varying(100)
+);
+
+
+ALTER TABLE public.person OWNER TO esper;
+
+--
+-- Name: person_badge; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE person_badge (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ person character(36) NOT NULL,
+ name character varying(100) NOT NULL,
+ level integer NOT NULL,
+ count integer NOT NULL
+);
+
+
+ALTER TABLE public.person_badge OWNER TO esper;
+
+--
+-- Name: person_badge_progress; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE person_badge_progress (
+ uuid character(36) NOT NULL,
+ created_date timestamp without time zone NOT NULL,
+ person character(36) NOT NULL,
+ badge character varying(100) NOT NULL,
+ state character varying(8000) NOT NULL
+);
+
+
+ALTER TABLE public.person_badge_progress OWNER TO esper;
+
+--
+-- Name: person_jenkins_user; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE person_jenkins_user (
+ person character(36) NOT NULL,
+ jenkins_user character(36) NOT NULL
+);
+
+
+ALTER TABLE public.person_jenkins_user OWNER TO esper;
+
+--
+-- Name: table_poller_status; Type: TABLE; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE TABLE table_poller_status (
+ poller_name character varying(100) NOT NULL,
+ last_seq integer NOT NULL,
+ last_run timestamp without time zone,
+ duration integer,
+ status character varying(1000)
+);
+
+
+ALTER TABLE public.table_poller_status OWNER TO esper;
+
+--
+-- Name: pk_build; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY build
+ ADD CONSTRAINT pk_build PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_build_participant; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY build_participant
+ ADD CONSTRAINT pk_build_participant PRIMARY KEY (build, person);
+
+
+--
+-- Name: pk_file; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY file
+ ADD CONSTRAINT pk_file PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_jenkins_build; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_build
+ ADD CONSTRAINT pk_jenkins_build PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_jenkins_job; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_job
+ ADD CONSTRAINT pk_jenkins_job PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_jenkins_server; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_server
+ ADD CONSTRAINT pk_jenkins_server PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_jenkins_user; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_user
+ ADD CONSTRAINT pk_jenkins_user PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_job_status; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY table_poller_status
+ ADD CONSTRAINT pk_job_status PRIMARY KEY (poller_name);
+
+
+--
+-- Name: pk_person; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY person
+ ADD CONSTRAINT pk_person PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_person_badge; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY person_badge
+ ADD CONSTRAINT pk_person_badge PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_person_badge_progress; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY person_badge_progress
+ ADD CONSTRAINT pk_person_badge_progress PRIMARY KEY (uuid);
+
+
+--
+-- Name: pk_person_jenkins_user; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY person_jenkins_user
+ ADD CONSTRAINT pk_person_jenkins_user PRIMARY KEY (person, jenkins_user);
+
+
+--
+-- Name: uq_jenkins_build__id; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_build
+ ADD CONSTRAINT uq_jenkins_build__id UNIQUE (entry_id);
+
+
+--
+-- Name: uq_jenkins_build__seq; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_build
+ ADD CONSTRAINT uq_jenkins_build__seq UNIQUE (seq);
+
+
+--
+-- Name: uq_jenkins_job__url; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_job
+ ADD CONSTRAINT uq_jenkins_job__url UNIQUE (url);
+
+
+--
+-- Name: uq_jenkins_server__url; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_server
+ ADD CONSTRAINT uq_jenkins_server__url UNIQUE (url);
+
+
+--
+-- Name: uq_jenkins_user__absolute_url; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY jenkins_user
+ ADD CONSTRAINT uq_jenkins_user__absolute_url UNIQUE (absolute_url);
+
+
+--
+-- Name: uq_person_badge__person__name__level; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY person_badge
+ ADD CONSTRAINT uq_person_badge__person__name__level UNIQUE (person, name, level);
+
+
+--
+-- Name: uq_person_badge_progress__person_badge; Type: CONSTRAINT; Schema: public; Owner: esper; Tablespace:
+--
+
+ALTER TABLE ONLY person_badge_progress
+ ADD CONSTRAINT uq_person_badge_progress__person_badge UNIQUE (person, badge);
+
+
+--
+-- Name: ix_jenkins_build__created_date; Type: INDEX; Schema: public; Owner: esper; Tablespace:
+--
+
+CREATE INDEX ix_jenkins_build__created_date ON jenkins_build USING btree (created_date);
+
+
+--
+-- Name: fk_build_participant__build; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY build_participant
+ ADD CONSTRAINT fk_build_participant__build FOREIGN KEY (build) REFERENCES build(uuid);
+
+
+--
+-- Name: fk_build_participant__person; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY build_participant
+ ADD CONSTRAINT fk_build_participant__person FOREIGN KEY (person) REFERENCES person(uuid);
+
+
+--
+-- Name: fk_jenkins_build__file; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY jenkins_build
+ ADD CONSTRAINT fk_jenkins_build__file FOREIGN KEY (file) REFERENCES file(uuid);
+
+
+--
+-- Name: fk_jenkins_build__job; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY jenkins_build
+ ADD CONSTRAINT fk_jenkins_build__job FOREIGN KEY (job) REFERENCES jenkins_job(uuid);
+
+
+--
+-- Name: fk_jenkins_job__file; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY jenkins_job
+ ADD CONSTRAINT fk_jenkins_job__file FOREIGN KEY (file) REFERENCES file(uuid);
+
+
+--
+-- Name: fk_jenkins_job__server; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY jenkins_job
+ ADD CONSTRAINT fk_jenkins_job__server FOREIGN KEY (server) REFERENCES jenkins_server(uuid);
+
+
+--
+-- Name: fk_jenkins_user__server; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY jenkins_user
+ ADD CONSTRAINT fk_jenkins_user__server FOREIGN KEY (server) REFERENCES jenkins_server(uuid);
+
+
+--
+-- Name: fk_person_badge__person; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY person_badge
+ ADD CONSTRAINT fk_person_badge__person FOREIGN KEY (person) REFERENCES person(uuid);
+
+
+--
+-- Name: fk_person_badge_progress__person; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY person_badge_progress
+ ADD CONSTRAINT fk_person_badge_progress__person FOREIGN KEY (person) REFERENCES person(uuid);
+
+
+--
+-- Name: fk_person_jenkins_user__jenkins_user; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY person_jenkins_user
+ ADD CONSTRAINT fk_person_jenkins_user__jenkins_user FOREIGN KEY (jenkins_user) REFERENCES jenkins_user(uuid);
+
+
+--
+-- Name: fk_person_jenkins_user__person; Type: FK CONSTRAINT; Schema: public; Owner: esper
+--
+
+ALTER TABLE ONLY person_jenkins_user
+ ADD CONSTRAINT fk_person_jenkins_user__person FOREIGN KEY (person) REFERENCES person(uuid);
+
+
+--
+-- Name: public; Type: ACL; Schema: -; Owner: postgres
+--
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+
+--
+-- PostgreSQL database dump complete
+--
+