Mercurial > libervia-pubsub
annotate db/libervia_pubsub_update_1_2.sql @ 478:b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
/!\ pgsql schema needs to be updated /!\
/!\ server conf needs to be updated for privileged entity: only the new
`urn:xmpp:privilege:2` namespace is handled now /!\
Privileged entity has been updated to hanlde the new namespace and IQ permission. Roster
pushes are not managed yet.
XEP-0376 (Pubsub Account Management) is partially implemented. The XEP is not fully
specified at the moment, and my messages on standard@ haven't seen any reply. Thus for now
only "Subscribing", "Unsubscribing" and "Listing Subscriptions" is implemented, "Auto
Subscriptions" and "Filtering" is not.
Public Pubsub Subscription
(https://xmpp.org/extensions/inbox/pubsub-public-subscriptions.html) is implemented;
the XEP has been accepted by council but is not yet published. It will be updated to use
subscription options instead of the <public> element actually specified, I'm waiting
for publication to update the XEP.
unsubscribe has been updated to return the `<subscription>` element as expected by
XEP-0060 (sat_tmp needs to be updated).
database schema has been updated to add columns necessary to keep track of subscriptions
to external nodes and to mark subscriptions as public.
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Wed, 11 May 2022 13:39:08 +0200 |
| parents | 9af0ef2c145c |
| 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'; |