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. --- 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