diff options
Diffstat (limited to 'src/main/sql')
-rw-r--r-- | src/main/sql/base-schema/create_table.psql | 5 | ||||
-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.sql | 15 | ||||
-rw-r--r-- | src/main/sql/base-schema/data-nexus.sql | 9 | ||||
-rw-r--r-- | src/main/sql/base-schema/data.nexus.sql | 9 | ||||
-rwxr-xr-x | src/main/sql/base-schema/ddl-jenkins.sql | 14 | ||||
-rw-r--r-- | src/main/sql/base-schema/ddl-nexus.sql | 6 | ||||
-rw-r--r-- | src/main/sql/dbdelta/1-person_badge-add-timestamps.sql | 17 | ||||
-rw-r--r-- | src/main/sql/dbdelta/2-jenkins_build_cleanup.sql | 11 |
9 files changed, 66 insertions, 20 deletions
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; |