libervia-pubsub: db/sat_pubsub_update_0

annotate db/sat_pubsub_update_0_1.sql @ 431:5e8b8ef5c862

implentation of XEP-0346 (Form Discovery and Publishing): The former non standard node schema has been replaced by XEP-0346 which uses 2 nodes (one from schema/template and one for submitted values). The implementation is an adapation of the former one, and data validation is still done even if this is not currently specified in any XEP. When the template node is modified, the change is reflected in the node schema.

author	Goffi <goffi@goffi.org>
date	Fri, 11 Dec 2020 17:19:00 +0100
parents	1dc606612405
children

rev	line source
350 4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	1 -- we check version of the database before doing anything
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	2 -- and stop execution if not good
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	3 \set ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	4 DO $$
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	5 DECLARE ver text;
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	6 BEGIN
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	7 SELECT value INTO ver FROM metadata WHERE key='version';
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	8 IF FOUND 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 older database, you don''t have to use it';
350 4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	10 END IF;
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	11 END$$;
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	12 \unset ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	13 -- end of version check
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	14
4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	15
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	16 ALTER TABLE nodes ADD COLUMN pep text;
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	17
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	18 ALTER TABLE nodes DROP CONSTRAINT nodes_node_key;
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	19 /* we need 2 partial indexes to manage NULL value for PEP */
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	20 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL;
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	21 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL;
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	22
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	23 CREATE TABLE metadata (
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	24 key text PRIMARY KEY,
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	25 value text
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	26 );
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	27
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	28 INSERT INTO metadata VALUES ('version', '1');
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	29
301 05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	30 CREATE TABLE item_categories (
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	31 item_categories_id serial PRIMARY KEY,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	32 item_id integer NOT NULL references items ON DELETE CASCADE,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	33 category text NOT NULL,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	34 UNIQUE (item_id,category)
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	35 );
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 294 diff changeset	36
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	37 UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%';
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	38
350 4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 303 diff changeset	39 /* This is to update namespaces, SàT was buggy before 0.6 and didn't set the atom namespace in <entry/> */
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	40 /* But yeah, this is a crazy query */
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	41 UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id),
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	42 XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/preceding-sibling::*', data::xml)::text[],''),'')),
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	43 xmlelement(name entry, xmlattributes('http://www.w3.org/2005/Atom' as xmlns), array_to_string(xpath('/item/entry/*', data::xml)::text[], '')::xml),
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	44 XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/following-sibling::*', data::xml)::text[],''),'')))
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	45 FROM nodes WHERE nodes.node_id = items.node_id
303 3c2705199108 sat_pubsub_update_0_1.sql now also updates comment nodes Goffi <goffi@goffi.org> parents: 301 diff changeset	46 AND (node = 'urn:xmpp:microblog:0' or node LIKE 'urn:xmpp:comments:%')
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: diff changeset	47 AND XMLEXISTS('/item/entry' PASSING (data::xml));

Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_0_1.sql @ 431:5e8b8ef5c862