diff options
| -rwxr-xr-x | pom.xml | 24 | ||||
| -rw-r--r-- | src/main/sql/base-schema/ddl-core.sql (renamed from src/main/resources/ddl-core.sql) | 10 | ||||
| -rw-r--r-- | src/main/sql/base-schema/ddl-file.sql (renamed from src/main/resources/ddl-file.sql) | 0 | ||||
| -rw-r--r-- | src/main/sql/base-schema/ddl-gitorious.sql (renamed from src/main/resources/ddl-gitorious.sql) | 0 | ||||
| -rwxr-xr-x | src/main/sql/base-schema/ddl-jenkins.sql (renamed from src/main/resources/ddl-jenkins.sql) | 0 | ||||
| -rw-r--r-- | src/main/sql/base-schema/ddl-nexus.sql (renamed from src/main/resources/ddl-nexus.sql) | 0 | ||||
| -rw-r--r-- | src/main/sql/base-schema/gitorious.org.sql (renamed from src/main/resources/gitorious.org.sql) | 0 | ||||
| -rw-r--r-- | src/main/sql/base-schema/logback.xml (renamed from src/main/resources/logback.xml) | 0 | ||||
| -rwxr-xr-x | src/main/sql/base-schema/queries.sql (renamed from src/main/resources/queries.sql) | 0 | ||||
| -rw-r--r-- | src/main/sql/dbdelta/1-person_badge-add-timestamps.sql | 21 | ||||
| -rw-r--r-- | src/main/sql/dump_postgresql.sh | 3 | ||||
| -rw-r--r-- | src/main/sql/posgresql-ddl.sql | 500 |
12 files changed, 558 insertions, 0 deletions
@@ -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 +-- + |