Mercurial > libervia-pubsub
diff db/sat_pubsub_update_0_2.sql @ 329:98409ef42c94
PostGreSQL: schema update:
access models changes:
- renamed roster to publisher-roster, as roster is in fact the pubsub service's roster, so it was badly used
- added whitelist, publish-only
- new non standard (yet?) self-publisher. It is planed to allow publisher to publish, update and delete only its own items
- roster => publisher-roster renaming, and whitelist has been added to item access model too. publish-only and self-publisher don't make sense for items
added "member" to affiliations (will be used for whitelist)
news tables item_jids_authorized and item_languages.
updated to version 2
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Sun, 26 Mar 2017 20:33:18 +0200 |
| parents | |
| children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/sat_pubsub_update_0_2.sql Sun Mar 26 20:33:18 2017 +0200 @@ -0,0 +1,28 @@ +/* roster access model was badly used, we rename it to publisher-roster */ + +ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; +UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster'; +ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); + +ALTER TABLE items DROP CONSTRAINT items_access_model_check; +UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster'; +ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')); + +ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check; +ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')); + +CREATE TABLE item_jids_authorized ( + item_jids_authorized_id serial PRIMARY KEY, + item_id integer NOT NULL references items ON DELETE CASCADE, + jid text NOT NULL, + UNIQUE (item_id,jid) +); + +CREATE TABLE item_languages ( + item_languages_id serial PRIMARY KEY, + item_id integer NOT NULL references items ON DELETE CASCADE, + language text NOT NULL, + UNIQUE (item_id,language) +); + +UPDATE metadata SET value='2' WHERE key='version';