changeset 350:4d4575911060

sql (PostgreSQL): schema update: - added version check on top of update files, SQL will not be applied if the version is not the one expected - new db schema version (3) - added "presence" access_model in nodes - added "schema" column in nodes - converted items data to xml, this will allow the use of all XML functions PostgreSQL now needs to have been compiled with --with-libxml .
author Goffi <goffi@goffi.org>
date Fri, 08 Sep 2017 08:02:04 +0200
parents 20b82fb8de02
children 2098295747fd
files db/pubsub.sql db/sat_pubsub_update_0_1.sql db/sat_pubsub_update_0_2.sql db/sat_pubsub_update_1_2.sql db/sat_pubsub_update_2_3.sql
diffstat 5 files changed, 87 insertions(+), 32 deletions(-) [+]
line wrap: on
line diff
--- a/db/pubsub.sql	Sun Aug 27 20:33:39 2017 +0200
+++ b/db/pubsub.sql	Fri Sep 08 08:02:04 2017 +0200
@@ -10,13 +10,14 @@
     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', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')),
+        CHECK (access_model IN ('open', 'presence', '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'
         CHECK (send_last_published_item IN ('never', 'on_sub')),
     publish_model text NOT NULL DEFAULT 'publishers'
         CHECK (publish_model IN ('publishers', 'subscribers', 'open'))
+    schema xml;
 );
 
 /* we need 2 partial indexes to manage NULL value for PEP */
@@ -59,7 +60,7 @@
     node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
     item text NOT NULL,
     publisher text NOT NULL,
-    data text,
+    data xml,
     access_model text NOT NULL DEFAULT 'open'
         CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
     date timestamp with time zone NOT NULL DEFAULT now(),
@@ -99,4 +100,4 @@
 	value text
 );
 
-INSERT INTO metadata VALUES ('version', '2');
+INSERT INTO metadata VALUES ('version', '3');
--- a/db/sat_pubsub_update_0_1.sql	Sun Aug 27 20:33:39 2017 +0200
+++ b/db/sat_pubsub_update_0_1.sql	Fri Sep 08 08:02:04 2017 +0200
@@ -1,3 +1,18 @@
+-- we check version of the database before doing anything
+-- and stop execution if not good
+\set ON_ERROR_STOP
+DO $$
+DECLARE ver text;
+BEGIN
+    SELECT value INTO ver FROM metadata WHERE key='version';
+    IF FOUND THEN
+        RAISE EXCEPTION 'This update file need to be applied on older database, you don''t have to use it';
+    END IF;
+END$$;
+\unset ON_ERROR_STOP
+-- end of version check
+
+
 ALTER TABLE nodes ADD COLUMN pep text;
 
 ALTER TABLE nodes DROP CONSTRAINT nodes_node_key;
@@ -21,7 +36,7 @@
 
 UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%';
 
-/* This is to update namespaces, SàT was bugguy before 0.6 and didn't set the atom namespace in <entry/> */
+/* This is to update namespaces, SàT was buggy before 0.6 and didn't set the atom namespace in <entry/> */
 /* But yeah, this is a crazy query */
 UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id),
                         XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/preceding-sibling::*', data::xml)::text[],''),'')),
--- a/db/sat_pubsub_update_0_2.sql	Sun Aug 27 20:33:39 2017 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,28 +0,0 @@
-/* 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';
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/sat_pubsub_update_1_2.sql	Fri Sep 08 08:02:04 2017 +0200
@@ -0,0 +1,42 @@
+-- we check version of the database before doing anything
+-- and stop execution if not good
+\set ON_ERROR_STOP
+DO $$
+DECLARE ver smallint;
+BEGIN
+    SELECT value INTO ver FROM metadata WHERE key='version';
+    IF NOT FOUND OR ver!='1' THEN
+        RAISE EXCEPTION 'This update file need to be applied on database schema version 1, you use version %',ver;
+    END IF;
+END$$;
+\unset ON_ERROR_STOP
+-- end of version check
+
+/* 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';
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/sat_pubsub_update_2_3.sql	Fri Sep 08 08:02:04 2017 +0200
@@ -0,0 +1,25 @@
+-- we check version of the database before doing anything
+-- and stop execution if not good
+\set ON_ERROR_STOP
+DO $$
+DECLARE ver text;
+BEGIN
+    SELECT value INTO ver FROM metadata WHERE key='version';
+    IF NOT FOUND OR ver!='2' THEN
+        RAISE EXCEPTION 'This update file need to be applied on database schema version 2, you use version %',ver;
+    END IF;
+END$$;
+\unset ON_ERROR_STOP
+-- end of version check
+
+/* we add "presence" access model */
+ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check;
+ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher'));
+
+/* and schema column */
+ALTER TABLE nodes ADD COLUMN schema xml;
+
+/* we want xml types for items data too */
+ALTER TABLE items ALTER data TYPE xml using data::xml;
+
+UPDATE metadata SET value='3' WHERE key='version';