Mercurial > libervia-pubsub
comparison db/sat_pubsub_update_2_3.sql @ 350:4d4575911060
sql (PostgreSQL): schema update:
- added version check on top of update files, SQL will not be applied if the version is not the one expected
- new db schema version (3)
- added "presence" access_model in nodes
- added "schema" column in nodes
- converted items data to xml, this will allow the use of all XML functions
PostgreSQL now needs to have been compiled with --with-libxml .
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Fri, 08 Sep 2017 08:02:04 +0200 |
| parents | |
| children | 1dc606612405 |
comparison
equal
deleted
inserted
replaced
| 349:20b82fb8de02 | 350:4d4575911060 |
|---|---|
| 1 -- we check version of the database before doing anything | |
| 2 -- and stop execution if not good | |
| 3 \set ON_ERROR_STOP | |
| 4 DO $$ | |
| 5 DECLARE ver text; | |
| 6 BEGIN | |
| 7 SELECT value INTO ver FROM metadata WHERE key='version'; | |
| 8 IF NOT FOUND OR ver!='2' THEN | |
| 9 RAISE EXCEPTION 'This update file need to be applied on database schema version 2, you use version %',ver; | |
| 10 END IF; | |
| 11 END$$; | |
| 12 \unset ON_ERROR_STOP | |
| 13 -- end of version check | |
| 14 | |
| 15 /* we add "presence" access model */ | |
| 16 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; | |
| 17 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); | |
| 18 | |
| 19 /* and schema column */ | |
| 20 ALTER TABLE nodes ADD COLUMN schema xml; | |
| 21 | |
| 22 /* we want xml types for items data too */ | |
| 23 ALTER TABLE items ALTER data TYPE xml using data::xml; | |
| 24 | |
| 25 UPDATE metadata SET value='3' WHERE key='version'; |