diff options
author | Trygve Laugstøl <trygvis@inamo.no> | 2013-01-05 22:39:18 +0100 |
---|---|---|
committer | Trygve Laugstøl <trygvis@inamo.no> | 2013-01-05 22:39:18 +0100 |
commit | 12606726c11efbbf7213b05284c94e28a1ae4b8e (patch) | |
tree | 415a400218f3bbaac4f13a43461446f64f74028c /src/main/sql/dbdelta | |
parent | abf160e56be50781b0f78f3f938513ab6102809b (diff) | |
download | esper-testing-12606726c11efbbf7213b05284c94e28a1ae4b8e.tar.gz esper-testing-12606726c11efbbf7213b05284c94e28a1ae4b8e.tar.bz2 esper-testing-12606726c11efbbf7213b05284c94e28a1ae4b8e.tar.xz esper-testing-12606726c11efbbf7213b05284c94e28a1ae4b8e.zip |
o Including state in both badge and badge progress.
o Removing count from badge, inserting one badge per badge received.
Diffstat (limited to 'src/main/sql/dbdelta')
-rw-r--r-- | src/main/sql/dbdelta/1-person_badge-add-timestamps.sql | 27 |
1 files changed, 20 insertions, 7 deletions
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; |