From 12606726c11efbbf7213b05284c94e28a1ae4b8e Mon Sep 17 00:00:00 2001 From: Trygve Laugstøl Date: Sat, 5 Jan 2013 22:39:18 +0100 Subject: o Including state in both badge and badge progress. o Removing count from badge, inserting one badge per badge received. --- .../sql/dbdelta/1-person_badge-add-timestamps.sql | 27 ++++++++++++++++------ 1 file changed, 20 insertions(+), 7 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 5b53145..3c8162c 100644 --- a/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql +++ b/src/main/sql/dbdelta/1-person_badge-add-timestamps.sql @@ -1,21 +1,34 @@ -ALTER TABLE person_badge ADD timestamps TIMESTAMP[]; +ALTER TABLE person_badge ADD state VARCHAR(8000); + +ALTER TABLE person_badge DROP CONSTRAINT uq_person_badge__person__name__level; DO $$ DECLARE r RECORD; -DECLARE ts TIMESTAMP[]; BEGIN - FOR r IN SELECT uuid, count FROM person_badge + FOR r IN SELECT uuid, created_date, person, count FROM person_badge LOOP - SELECT array_agg(x) FROM (SELECT current_timestamp FROM generate_series(1, r.count)) AS x INTO ts; - UPDATE person_badge SET timestamps=ts WHERE uuid=r.uuid; + FOR x IN SELECT x FROM generate_series(1, r.count) + 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; +ALTER TABLE person_badge ALTER state SET NOT NULL; + --//@UNDO ALTER TABLE person_badge ADD count INT; -UPDATE person_badge SET count=array_length(timestamps, 1); + +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; + ALTER TABLE person_badge ALTER count SET NOT NULL; -ALTER TABLE person_badge DROP timestamps; + +ALTER TABLE person_badge DROP state; -- cgit v1.2.3