aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rwxr-xr-xpom.xml2
-rw-r--r--src/main/sql/base-schema/create_table.psql5
-rw-r--r--src/main/sql/base-schema/data-gitorious.sql (renamed from src/main/sql/base-schema/gitorious.org.sql)0
-rw-r--r--src/main/sql/base-schema/data-jenkins.sql15
-rw-r--r--src/main/sql/base-schema/data-nexus.sql9
-rw-r--r--src/main/sql/base-schema/data.nexus.sql9
-rwxr-xr-xsrc/main/sql/base-schema/ddl-jenkins.sql14
-rw-r--r--src/main/sql/base-schema/ddl-nexus.sql6
-rw-r--r--src/main/sql/dbdelta/1-person_badge-add-timestamps.sql17
-rw-r--r--src/main/sql/dbdelta/2-jenkins_build_cleanup.sql11
10 files changed, 67 insertions, 21 deletions
diff --git a/pom.xml b/pom.xml
index 7e4682b..ec3e57b 100755
--- a/pom.xml
+++ b/pom.xml
@@ -172,7 +172,7 @@
<configuration>
<scriptdirectory>src/main/sql/dbdelta</scriptdirectory>
<driver>org.postgresql.Driver</driver>
- <url>jdbc:postgresql://localhost/esper-test</url>
+ <url>jdbc:postgresql://localhost/esper</url>
<userid>esper</userid>
<password>esper</password>
<dbms>pgsql</dbms>
diff --git a/src/main/sql/base-schema/create_table.psql b/src/main/sql/base-schema/create_table.psql
new file mode 100644
index 0000000..c4eb909
--- /dev/null
+++ b/src/main/sql/base-schema/create_table.psql
@@ -0,0 +1,5 @@
+\i src/main/sql/base-schema/ddl-file.sql
+\i src/main/sql/base-schema/ddl-gitorious.sql
+\i src/main/sql/base-schema/ddl-jenkins.sql
+\i src/main/sql/base-schema/ddl-nexus.sql
+\i src/main/sql/base-schema/ddl-core.sql
diff --git a/src/main/sql/base-schema/gitorious.org.sql b/src/main/sql/base-schema/data-gitorious.sql
index 51a015b..51a015b 100644
--- a/src/main/sql/base-schema/gitorious.org.sql
+++ b/src/main/sql/base-schema/data-gitorious.sql
diff --git a/src/main/sql/base-schema/data-jenkins.sql b/src/main/sql/base-schema/data-jenkins.sql
new file mode 100644
index 0000000..31387f8
--- /dev/null
+++ b/src/main/sql/base-schema/data-jenkins.sql
@@ -0,0 +1,15 @@
+BEGIN;
+
+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);
+
+-- apache jenkins: '782a75f6-40a4-11e2-aca6-20cf30557fa0'
+-- olamy: '8588a612-4b5a-11e2-879d-20cf30557fa0'
+
+INSERT INTO person (uuid, created_date, name) VALUES ('8588a612-4b5a-11e2-879d-20cf30557fa0', CURRENT_TIMESTAMP, 'Olivier Lamy');
+INSERT INTO person_jenkins_user(person, jenkins_user) VALUES('8588a612-4b5a-11e2-879d-20cf30557fa0', 'e35f81ae-7589-4644-ad90-198a6bc582f8');
+
+COMMIT;
diff --git a/src/main/sql/base-schema/data-nexus.sql b/src/main/sql/base-schema/data-nexus.sql
new file mode 100644
index 0000000..2d2c0f4
--- /dev/null
+++ b/src/main/sql/base-schema/data-nexus.sql
@@ -0,0 +1,9 @@
+BEGIN;
+
+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/data.nexus.sql b/src/main/sql/base-schema/data.nexus.sql
new file mode 100644
index 0000000..2d2c0f4
--- /dev/null
+++ b/src/main/sql/base-schema/data.nexus.sql
@@ -0,0 +1,9 @@
+BEGIN;
+
+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/ddl-jenkins.sql b/src/main/sql/base-schema/ddl-jenkins.sql
index 3e420a2..6e28c85 100755
--- a/src/main/sql/base-schema/ddl-jenkins.sql
+++ b/src/main/sql/base-schema/ddl-jenkins.sql
@@ -44,10 +44,10 @@ CREATE TABLE jenkins_build (
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,
+ 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),
@@ -70,10 +70,4 @@ CREATE TABLE jenkins_user (
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
index 8acf741..5b9d09f 100644
--- a/src/main/sql/base-schema/ddl-nexus.sql
+++ b/src/main/sql/base-schema/ddl-nexus.sql
@@ -58,10 +58,4 @@ CREATE TABLE nexus_event (
-- 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/dbdelta/1-person_badge-add-timestamps.sql b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
index 3c8162c..15425bf 100644
--- a/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
+++ b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql
@@ -2,33 +2,42 @@ ALTER TABLE person_badge ADD state VARCHAR(8000);
ALTER TABLE person_badge DROP CONSTRAINT uq_person_badge__person__name__level;
+ALTER TABLE person_badge ALTER count DROP NOT NULL;
+
DO $$
DECLARE r RECORD;
+DECLARE zz RECORD;
BEGIN
- FOR r IN SELECT uuid, created_date, person, count FROM person_badge
+ FOR r IN SELECT uuid, created_date, person, name, level, count FROM person_badge
LOOP
- FOR x IN SELECT x FROM generate_series(1, r.count)
+ FOR zz IN SELECT x FROM generate_series(1, r.count) AS x
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;
+DELETE FROM person_badge WHERE state IS NULL;
+
ALTER TABLE person_badge ALTER state SET NOT NULL;
--//@UNDO
ALTER TABLE person_badge ADD count INT;
+ALTER TABLE person_badge ALTER state DROP NOT NULL;
+
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;
+DELETE FROM person_badge WHERE count IS NULL;
+
ALTER TABLE person_badge ALTER count SET NOT NULL;
ALTER TABLE person_badge DROP state;
+
+ALTER TABLE person_badge ADD CONSTRAINT uq_person_badge__person__name__level UNIQUE (person, name, level);
diff --git a/src/main/sql/dbdelta/2-jenkins_build_cleanup.sql b/src/main/sql/dbdelta/2-jenkins_build_cleanup.sql
new file mode 100644
index 0000000..c570c35
--- /dev/null
+++ b/src/main/sql/dbdelta/2-jenkins_build_cleanup.sql
@@ -0,0 +1,11 @@
+ALTER TABLE jenkins_build DROP result;
+ALTER TABLE jenkins_build DROP number;
+ALTER TABLE jenkins_build DROP duration;
+ALTER TABLE jenkins_build DROP timestamp;
+
+--//@UNDO
+
+ALTER TABLE jenkins_build ADD result VARCHAR(100);
+ALTER TABLE jenkins_build ADD number INT;
+ALTER TABLE jenkins_build ADD duration INT;
+ALTER TABLE jenkins_build ADD TIMESTAMP TIMESTAMP;
generated by cgit v1.2.3 (git 2.46.0) at 2024-12-22 21:54:46 +0000