Mercurial > libervia-pubsub

diff db/sat_pubsub_update_5_6.sql @ 430:5a0ada3b61ca

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Full-Text Search implementation: /!\ pgsql schema needs to be updated /!\ /!\ Minimal PostgreSQL required version is now 12 /!\ A new options is available to specify main language of a node. By default a `generic` language is used (which uses the `simple` configuration in PostgreSQL). When a node owner changes the language, the index is rebuilt accordingly. It is possible to have item specific language for multilingual nodes (but for the moment the search is done with node language, so the results won't be good). If an item language is explicitely set in `item_languages`, the FTS configuration won't be affected by node FTS language setting. Search is parsed with `websearch_to_tsquery` for now, but this parser doesn't handle prefix matching, so it may be replaced in the future. SetConfiguration now only updates the modified values, this avoid triggering the FTS re-indexing on each config change. `_checkNodeExists` is not called anymore as we can check if a row has been modified to see if the node exists, this avoid a useless query. Item storing has been slighly improved with a useless SELECT and condition removed. To avoid 2 schema updates in a row, the `sat_pubsub_update_5_6.sql` file also prepares the implementation of XEP-0346 by updating nodes with a schema and creating the suitable template nodes.
author Goffi <goffi@goffi.org>
date Fri, 11 Dec 2020 17:18:52 +0100
parents
children
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/sat_pubsub_update_5_6.sql	Fri Dec 11 17:18:52 2020 +0100
@@ -0,0 +1,104 @@
+-- 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!='5' THEN
+        RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver;
+    END IF;
+END$$;
+\unset ON_ERROR_STOP
+-- end of version check
+
+/* NOT NULL constraint was not applied to items.data */
+ALTER TABLE items ALTER COLUMN data SET NOT NULL;
+
+/* Full Text Search */
+ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
+ALTER TABLE items ADD COLUMN data_fts_cfg regconfig NOT NULL DEFAULT 'simple';
+ALTER TABLE items ADD COLUMN data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED;
+CREATE INDEX items_data_fts ON items USING GIN (data_fts);
+
+CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS
+$$
+BEGIN
+    UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig
+        WHERE items.node_id=new.node_id
+            AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
+    RETURN new;
+END;
+$$
+language plpgsql;
+
+CREATE TRIGGER nodes_fts_language_update
+     AFTER UPDATE OF fts_language ON nodes
+     FOR EACH ROW
+     EXECUTE PROCEDURE update_data_fts();
+
+/* we update nodes with schema to prepare for XEP-0346 implementation */
+
+INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
+    SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL
+    ) AS s;
+
+INSERT INTO affiliations(entity_id, node_id, affiliation)
+    SELECT aff.entity_id, tpl.node_id, 'owner'
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NOT NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
+    LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
+
+/* we need to do a similar request for non PEP nodes */
+INSERT INTO affiliations(entity_id, node_id, affiliation)
+    SELECT aff.entity_id, tpl.node_id, 'owner'
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
+    LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
+
+INSERT INTO items(node_id, item, publisher, data)
+    SELECT
+        tpl.node_id,
+        'current',
+        e.jid||'/generated',
+        xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NOT NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
+    LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
+    LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
+
+/* once again for non PEP nodes */
+INSERT INTO items(node_id, item, publisher, data)
+    SELECT
+        tpl.node_id,
+        'current',
+        e.jid||'/generated',
+        xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
+    LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
+    LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
+
+UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL;
+
+UPDATE metadata SET value='6' WHERE key='version';