Mercurial > libervia-pubsub
annotate db/sat_pubsub_update_1_2.sql @ 441:9a5e66f8eeca
install: add `requriements.txt`
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Fri, 19 Mar 2021 12:12:17 +0100 |
| parents | 1dc606612405 |
| children |
| rev | line source |
|---|---|
| 350 | 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 smallint; | |
| 6 BEGIN | |
| 7 SELECT value INTO ver FROM metadata WHERE key='version'; | |
| 8 IF NOT FOUND OR ver!='1' THEN | |
|
403
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
350
diff
changeset
|
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 1, you use version %',ver; |
| 350 | 10 END IF; |
| 11 END$$; | |
| 12 \unset ON_ERROR_STOP | |
| 13 -- end of version check | |
| 14 | |
| 329 | 15 /* roster access model was badly used, we rename it to publisher-roster */ |
| 16 | |
| 17 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; | |
| 18 UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster'; | |
| 19 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); | |
| 20 | |
| 21 ALTER TABLE items DROP CONSTRAINT items_access_model_check; | |
| 22 UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster'; | |
| 23 ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')); | |
| 24 | |
| 25 ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check; | |
| 26 ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')); | |
| 27 | |
| 28 CREATE TABLE item_jids_authorized ( | |
| 29 item_jids_authorized_id serial PRIMARY KEY, | |
| 30 item_id integer NOT NULL references items ON DELETE CASCADE, | |
| 31 jid text NOT NULL, | |
| 32 UNIQUE (item_id,jid) | |
| 33 ); | |
| 34 | |
| 35 CREATE TABLE item_languages ( | |
| 36 item_languages_id serial PRIMARY KEY, | |
| 37 item_id integer NOT NULL references items ON DELETE CASCADE, | |
| 38 language text NOT NULL, | |
| 39 UNIQUE (item_id,language) | |
| 40 ); | |
| 41 | |
| 42 UPDATE metadata SET value='2' WHERE key='version'; |