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. --- pom.xml | 24 + src/main/resources/ddl-core.sql | 93 ---- src/main/resources/ddl-file.sql | 14 - src/main/resources/ddl-gitorious.sql | 40 -- src/main/resources/ddl-jenkins.sql | 79 ---- src/main/resources/ddl-nexus.sql | 67 --- src/main/resources/gitorious.org.sql | 11 - src/main/resources/logback.xml | 37 -- src/main/resources/queries.sql | 2 - src/main/sql/base-schema/ddl-core.sql | 103 +++++ src/main/sql/base-schema/ddl-file.sql | 14 + src/main/sql/base-schema/ddl-gitorious.sql | 40 ++ src/main/sql/base-schema/ddl-jenkins.sql | 79 ++++ src/main/sql/base-schema/ddl-nexus.sql | 67 +++ src/main/sql/base-schema/gitorious.org.sql | 11 + src/main/sql/base-schema/logback.xml | 37 ++ src/main/sql/base-schema/queries.sql | 2 + .../sql/dbdelta/1-person_badge-add-timestamps.sql | 21 + src/main/sql/dump_postgresql.sh | 3 + src/main/sql/posgresql-ddl.sql | 500 +++++++++++++++++++++ 20 files changed, 901 insertions(+), 343 deletions(-) delete mode 100644 src/main/resources/ddl-core.sql delete mode 100644 src/main/resources/ddl-file.sql delete mode 100644 src/main/resources/ddl-gitorious.sql delete mode 100755 src/main/resources/ddl-jenkins.sql delete mode 100644 src/main/resources/ddl-nexus.sql delete mode 100644 src/main/resources/gitorious.org.sql delete mode 100644 src/main/resources/logback.xml delete mode 100755 src/main/resources/queries.sql create mode 100644 src/main/sql/base-schema/ddl-core.sql create mode 100644 src/main/sql/base-schema/ddl-file.sql create mode 100644 src/main/sql/base-schema/ddl-gitorious.sql create mode 100755 src/main/sql/base-schema/ddl-jenkins.sql create mode 100644 src/main/sql/base-schema/ddl-nexus.sql create mode 100644 src/main/sql/base-schema/gitorious.org.sql create mode 100644 src/main/sql/base-schema/logback.xml create mode 100755 src/main/sql/base-schema/queries.sql create mode 100644 src/main/sql/dbdelta/1-person_badge-add-timestamps.sql create mode 100644 src/main/sql/dump_postgresql.sh create mode 100644 src/main/sql/posgresql-ddl.sql diff --git a/pom.xml b/pom.xml index 0224a0f..4406252 100755 --- a/pom.xml +++ b/pom.xml @@ -157,6 +157,30 @@ + + com.dbdeploy + maven-dbdeploy-plugin + 3.0M3 + + + postgresql + postgresql + 9.1-901-1.jdbc4 + + + + src/main/sql/dbdelta + org.postgresql.Driver + jdbc:postgresql://localhost/esper-test + esper + esper + pgsql + ; + row + dbdeploy-outputfile + dbdeploy-undoOutputfile + + org.codehaus.mojo appassembler-maven-plugin diff --git a/src/main/resources/ddl-core.sql b/src/main/resources/ddl-core.sql deleted file mode 100644 index b145438..0000000 --- a/src/main/resources/ddl-core.sql +++ /dev/null @@ -1,93 +0,0 @@ -BEGIN; - -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/resources/ddl-file.sql b/src/main/resources/ddl-file.sql deleted file mode 100644 index 99e8bf6..0000000 --- a/src/main/resources/ddl-file.sql +++ /dev/null @@ -1,14 +0,0 @@ -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/resources/ddl-gitorious.sql b/src/main/resources/ddl-gitorious.sql deleted file mode 100644 index 121a4ff..0000000 --- a/src/main/resources/ddl-gitorious.sql +++ /dev/null @@ -1,40 +0,0 @@ -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/resources/ddl-jenkins.sql b/src/main/resources/ddl-jenkins.sql deleted file mode 100755 index 3e420a2..0000000 --- a/src/main/resources/ddl-jenkins.sql +++ /dev/null @@ -1,79 +0,0 @@ -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/resources/ddl-nexus.sql b/src/main/resources/ddl-nexus.sql deleted file mode 100644 index 8acf741..0000000 --- a/src/main/resources/ddl-nexus.sql +++ /dev/null @@ -1,67 +0,0 @@ -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/resources/gitorious.org.sql b/src/main/resources/gitorious.org.sql deleted file mode 100644 index 51a015b..0000000 --- a/src/main/resources/gitorious.org.sql +++ /dev/null @@ -1,11 +0,0 @@ --- 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/resources/logback.xml b/src/main/resources/logback.xml deleted file mode 100644 index 460df3a..0000000 --- a/src/main/resources/logback.xml +++ /dev/null @@ -1,37 +0,0 @@ - - - - logs/${logging.app}.log - true - - logs/${logging.app}-%d.log - - - %d{HH:mm:ss.SSS} [%thread] %-5level %-30logger{1} - %msg%n - - - - - - INFO - - - %d{HH:mm:ss.SSS} [%thread] %-5level %-30logger{1} - %msg%n - - - - - - - - - - - - - - - - - - diff --git a/src/main/resources/queries.sql b/src/main/resources/queries.sql deleted file mode 100755 index 66bdb38..0000000 --- a/src/main/resources/queries.sql +++ /dev/null @@ -1,2 +0,0 @@ --- 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/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 @@ + + + + logs/${logging.app}.log + true + + logs/${logging.app}-%d.log + + + %d{HH:mm:ss.SSS} [%thread] %-5level %-30logger{1} - %msg%n + + + + + + INFO + + + %d{HH:mm:ss.SSS} [%thread] %-5level %-30logger{1} - %msg%n + + + + + + + + + + + + + + + + + + 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 +-- + -- cgit v1.2.3