# HG changeset patch
# User Goffi <goffi@goffi.org>
# Date 1490553198 -7200
# Node ID 98409ef42c94de534f4436faf0c9a9aba61a615e
# Parent  ae37289007c35c01fde30fc6b17ea9090fb5688c
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

diff -r ae37289007c3 -r 98409ef42c94 db/pubsub.sql
--- a/db/pubsub.sql	Fri Mar 03 08:35:34 2017 +0100
+++ b/db/pubsub.sql	Sun Mar 26 20:33:18 2017 +0200
@@ -10,7 +10,7 @@
     node_type text NOT NULL DEFAULT 'leaf'
         CHECK (node_type IN ('leaf', 'collection')),
     access_model text NOT NULL DEFAULT 'open'
-        CHECK (access_model IN ('open', 'roster')),
+        CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')),
     persist_items boolean,
     deliver_payloads boolean NOT NULL DEFAULT TRUE,
     send_last_published_item text NOT NULL DEFAULT 'on_sub'
@@ -30,7 +30,7 @@
     entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
     node_id integer NOT NULL references nodes ON DELETE CASCADE,
     affiliation text NOT NULL
-        CHECK (affiliation IN ('outcast', 'publisher', 'owner')),
+        CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')),
     UNIQUE (entity_id, node_id)
 );
 
@@ -61,7 +61,7 @@
     publisher text NOT NULL,
     data text,
     access_model text NOT NULL DEFAULT 'open'
-        CHECK (access_model IN ('open', 'roster')),
+        CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
     date timestamp with time zone NOT NULL DEFAULT now(),
     UNIQUE (node_id, item)
 );
@@ -73,6 +73,20 @@
     UNIQUE (item_id,groupname)
 );
 
+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)
+);
+
 CREATE TABLE item_categories (
     item_categories_id serial PRIMARY KEY,
     item_id integer NOT NULL references items ON DELETE CASCADE,
@@ -85,4 +99,4 @@
 	value text
 );
 
-INSERT INTO metadata VALUES ('version', '1');
+INSERT INTO metadata VALUES ('version', '2');
diff -r ae37289007c3 -r 98409ef42c94 db/sat_pubsub_update_0_2.sql
--- /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';