From 18726624965b6d00faf816e5acb9fa7f5a124f04 Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Sun, 6 Jan 2013 00:21:29 +0100
Subject: o Moving data out of DDL files. o Making sure the dbdeltas work.

---
 src/main/sql/dbdelta/1-person_badge-add-timestamps.sql | 17 +++++++++++++----
 src/main/sql/dbdelta/2-jenkins_build_cleanup.sql       | 11 +++++++++++
 2 files changed, 24 insertions(+), 4 deletions(-)
 create mode 100644 src/main/sql/dbdelta/2-jenkins_build_cleanup.sql

(limited to 'src/main/sql/dbdelta')

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