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/logback.xml37
-rw-r--r--src/main/sql/base-schema/posgresql-ddl.sql500
-rw-r--r--src/main/sql/dbdelta/1-person_badge-add-timestamps.sql27
3 files changed, 520 insertions, 44 deletions
diff --git a/src/main/sql/base-schema/logback.xml b/src/main/sql/base-schema/logback.xml
deleted file mode 100644
index 460df3a..0000000
--- a/src/main/sql/base-schema/logback.xml
+++ /dev/null
@@ -1,37 +0,0 @@
-<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/posgresql-ddl.sql b/src/main/sql/base-schema/posgresql-ddl.sql
new file mode 100644
index 0000000..4a274a8
--- /dev/null
+++ b/src/main/sql/base-schema/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
+--
+
diff --git a/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
index 5b53145..3c8162c 100644
--- a/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
+++ b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
@@ -1,21 +1,34 @@
-ALTER TABLE person_badge ADD timestamps TIMESTAMP[];
+ALTER TABLE person_badge ADD state VARCHAR(8000);
+
+ALTER TABLE person_badge DROP CONSTRAINT uq_person_badge__person__name__level;
DO $$
DECLARE r RECORD;
-DECLARE ts TIMESTAMP[];
BEGIN
- FOR r IN SELECT uuid, count FROM person_badge
+ FOR r IN SELECT uuid, created_date, person, 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;
+ FOR x IN SELECT x FROM generate_series(1, r.count)
+ LOOP
+ INSERT INTO person_badge(uuid, created_date, person, name, level, state) VALUES(
+ uuid_generate_v1(), r.created_date, r.person, r.name, r.level,
+ '{"type":"' || r.name || '","person":"' || r.person || '","builds":[]}');
+ END LOOP;
+
+ DELETE FROM person_badge WHERE uuid=r.uuid;
END LOOP;
END$$;
ALTER TABLE person_badge DROP count;
+ALTER TABLE person_badge ALTER state SET NOT NULL;
+
--//@UNDO
ALTER TABLE person_badge ADD count INT;
-UPDATE person_badge SET count=array_length(timestamps, 1);
+
+INSERT into person_badge(uuid, created_date, name, person, level, count)
+ SELECT uuid_generate_v1(), min(created_date) as "created_date", name, person, level, count(level) from person_badge group by name, person, level;
+
ALTER TABLE person_badge ALTER count SET NOT NULL;
-ALTER TABLE person_badge DROP timestamps;
+
+ALTER TABLE person_badge DROP state;