Mercurial > libervia-pubsub

comparison 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
comparison
equal deleted inserted replaced
429:0526073ff2ab 430:5a0ada3b61ca
1 -- we check version of the database before doing anything
2 -- and stop execution if not good
3 \set ON_ERROR_STOP
4 DO $$
5 DECLARE ver text;
6 BEGIN
7 SELECT value INTO ver FROM metadata WHERE key='version';
8 IF NOT FOUND OR ver!='5' THEN
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver;
10 END IF;
11 END$$;
12 \unset ON_ERROR_STOP
13 -- end of version check
14
15 /* NOT NULL constraint was not applied to items.data */
16 ALTER TABLE items ALTER COLUMN data SET NOT NULL;
17
18 /* Full Text Search */
19 ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
20 ALTER TABLE items ADD COLUMN data_fts_cfg regconfig NOT NULL DEFAULT 'simple';
21 ALTER TABLE items ADD COLUMN data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED;
22 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
23
24 CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS
25 $$
26 BEGIN
27 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig
28 WHERE items.node_id=new.node_id
29 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
30 RETURN new;
31 END;
32 $$
33 language plpgsql;
34
35 CREATE TRIGGER nodes_fts_language_update
36 AFTER UPDATE OF fts_language ON nodes
37 FOR EACH ROW
38 EXECUTE PROCEDURE update_data_fts();
39
40 /* we update nodes with schema to prepare for XEP-0346 implementation */
41
42 INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
43 SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1
44 FROM (
45 SELECT node_id, node, pep, publish_model, schema
46 FROM nodes
47 WHERE schema IS NOT NULL
48 ) AS s;
49
50 INSERT INTO affiliations(entity_id, node_id, affiliation)
51 SELECT aff.entity_id, tpl.node_id, 'owner'
52 FROM (
53 SELECT node_id, node, pep, publish_model, schema
54 FROM nodes
55 WHERE schema IS NOT NULL AND pep IS NOT NULL
56 ) AS s
57 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
58 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
59
60 /* we need to do a similar request for non PEP nodes */
61 INSERT INTO affiliations(entity_id, node_id, affiliation)
62 SELECT aff.entity_id, tpl.node_id, 'owner'
63 FROM (
64 SELECT node_id, node, pep, publish_model, schema
65 FROM nodes
66 WHERE schema IS NOT NULL AND pep IS NULL
67 ) AS s
68 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
69 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
70
71 INSERT INTO items(node_id, item, publisher, data)
72 SELECT
73 tpl.node_id,
74 'current',
75 e.jid||'/generated',
76 xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
77 FROM (
78 SELECT node_id, node, pep, publish_model, schema
79 FROM nodes
80 WHERE schema IS NOT NULL AND pep IS NOT NULL
81 ) AS s
82 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
83 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
84 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
85
86 /* once again for non PEP nodes */
87 INSERT INTO items(node_id, item, publisher, data)
88 SELECT
89 tpl.node_id,
90 'current',
91 e.jid||'/generated',
92 xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
93 FROM (
94 SELECT node_id, node, pep, publish_model, schema
95 FROM nodes
96 WHERE schema IS NOT NULL AND pep IS NULL
97 ) AS s
98 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
99 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
100 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
101
102 UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL;
103
104 UPDATE metadata SET value='6' WHERE key='version';