Mercurial > libervia-pubsub
annotate db/sat_pubsub_update_1_2.sql @ 403:1dc606612405
implemented experimental "consistent_publisher" option:
/!\ pgsql schema needs to be updated /!\
New "consistent_publisher" option has been implemented to allow node owners + admins to
modify an item while preserving the original publisher. This way, original publisher can
still edit the item.
In addition to `consistent_publisher`, `max_items` has been added to PGQSL schema to
prepare for future implementation.
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Wed, 12 Jun 2019 21:51:50 +0200 |
| parents | 4d4575911060 |
| 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'; |