Mercurial > libervia-pubsub
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';