o Trying out dbdeploy.

12 files changed, 558 insertions, 0 deletions

diff --git a/pom.xml b/pom.xml
index 0224a0f..4406252 100755
--- a/pom.xml
+++ b/pom.xml
@@ -158,6 +158,30 @@
         </executions>
       </plugin>
       <plugin>
+        <groupId>com.dbdeploy</groupId>
+        <artifactId>maven-dbdeploy-plugin</artifactId>
+        <version>3.0M3</version>
+        <dependencies>
+          <dependency>
+            <groupId>postgresql</groupId>
+            <artifactId>postgresql</artifactId>
+            <version>9.1-901-1.jdbc4</version>
+          </dependency>
+        </dependencies>
+        <configuration>
+          <scriptdirectory>src/main/sql/dbdelta</scriptdirectory>
+          <driver>org.postgresql.Driver</driver>
+          <url>jdbc:postgresql://localhost/esper-test</url>
+          <userid>esper</userid>
+          <password>esper</password>
+          <dbms>pgsql</dbms>
+          <delimiter>;</delimiter>
+          <delimiterType>row</delimiterType>
+          <outputfile>dbdeploy-outputfile</outputfile>
+          <undoOutputfile>dbdeploy-undoOutputfile</undoOutputfile>
+        </configuration>
+      </plugin>
+      <plugin>
         <groupId>org.codehaus.mojo</groupId>
         <artifactId>appassembler-maven-plugin</artifactId>
         <version>1.1.1</version>
diff --git a/src/main/resources/ddl-core.sql b/src/main/sql/base-schema/ddl-core.sql
index b145438..6f1f101 100644
--- a/src/main/resources/ddl-core.sql
+++ b/src/main/sql/base-schema/ddl-core.sql
@@ -1,5 +1,15 @@
 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;
diff --git a/src/main/resources/ddl-file.sql b/src/main/sql/base-schema/ddl-file.sql
index 99e8bf6..99e8bf6 100644
--- a/src/main/resources/ddl-file.sql
+++ b/src/main/sql/base-schema/ddl-file.sql
diff --git a/src/main/resources/ddl-gitorious.sql b/src/main/sql/base-schema/ddl-gitorious.sql
index 121a4ff..121a4ff 100644
--- a/src/main/resources/ddl-gitorious.sql
+++ b/src/main/sql/base-schema/ddl-gitorious.sql
diff --git a/src/main/resources/ddl-jenkins.sql b/src/main/sql/base-schema/ddl-jenkins.sql
index 3e420a2..3e420a2 100755
--- a/src/main/resources/ddl-jenkins.sql
+++ b/src/main/sql/base-schema/ddl-jenkins.sql
diff --git a/src/main/resources/ddl-nexus.sql b/src/main/sql/base-schema/ddl-nexus.sql
index 8acf741..8acf741 100644
--- a/src/main/resources/ddl-nexus.sql
+++ b/src/main/sql/base-schema/ddl-nexus.sql
diff --git a/src/main/resources/gitorious.org.sql b/src/main/sql/base-schema/gitorious.org.sql
index 51a015b..51a015b 100644
--- a/src/main/resources/gitorious.org.sql
+++ b/src/main/sql/base-schema/gitorious.org.sql
diff --git a/src/main/resources/logback.xml b/src/main/sql/base-schema/logback.xml
index 460df3a..460df3a 100644
--- a/src/main/resources/logback.xml
+++ b/src/main/sql/base-schema/logback.xml
diff --git a/src/main/resources/queries.sql b/src/main/sql/base-schema/queries.sql
index 66bdb38..66bdb38 100755
--- a/src/main/resources/queries.sql
+++ b/src/main/sql/base-schema/queries.sql
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
+--
+