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 +++++++++++++----
 1 file changed, 13 insertions(+), 4 deletions(-)

(limited to 'src/main/sql/dbdelta/1-person_badge-add-timestamps.sql')

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);
-- 
cgit v1.2.3

