From 18726624965b6d00faf816e5acb9fa7f5a124f04 Mon Sep 17 00:00:00 2001 From: Trygve Laugstøl Date: Sun, 6 Jan 2013 00:21:29 +0100 Subject: o Moving data out of DDL files. o Making sure the dbdeltas work. --- pom.xml | 2 +- src/main/sql/base-schema/create_table.psql | 5 +++++ src/main/sql/base-schema/data-gitorious.sql | 11 +++++++++++ src/main/sql/base-schema/data-jenkins.sql | 15 +++++++++++++++ src/main/sql/base-schema/data-nexus.sql | 9 +++++++++ src/main/sql/base-schema/data.nexus.sql | 9 +++++++++ src/main/sql/base-schema/ddl-jenkins.sql | 14 ++++---------- src/main/sql/base-schema/ddl-nexus.sql | 6 ------ src/main/sql/base-schema/gitorious.org.sql | 11 ----------- src/main/sql/dbdelta/1-person_badge-add-timestamps.sql | 17 +++++++++++++---- src/main/sql/dbdelta/2-jenkins_build_cleanup.sql | 11 +++++++++++ 11 files changed, 78 insertions(+), 32 deletions(-) create mode 100644 src/main/sql/base-schema/create_table.psql create mode 100644 src/main/sql/base-schema/data-gitorious.sql create mode 100644 src/main/sql/base-schema/data-jenkins.sql create mode 100644 src/main/sql/base-schema/data-nexus.sql create mode 100644 src/main/sql/base-schema/data.nexus.sql delete mode 100644 src/main/sql/base-schema/gitorious.org.sql create mode 100644 src/main/sql/dbdelta/2-jenkins_build_cleanup.sql diff --git a/pom.xml b/pom.xml index 7e4682b..ec3e57b 100755 --- a/pom.xml +++ b/pom.xml @@ -172,7 +172,7 @@ src/main/sql/dbdelta org.postgresql.Driver - jdbc:postgresql://localhost/esper-test + jdbc:postgresql://localhost/esper esper esper pgsql 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/data-gitorious.sql b/src/main/sql/base-schema/data-gitorious.sql new file mode 100644 index 0000000..51a015b --- /dev/null +++ b/src/main/sql/base-schema/data-gitorious.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/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/base-schema/gitorious.org.sql b/src/main/sql/base-schema/gitorious.org.sql deleted file mode 100644 index 51a015b..0000000 --- a/src/main/sql/base-schema/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/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; -- cgit v1.2.3