annotate db/sat_pubsub_update_5_6.sql @ 430:5a0ada3b61ca

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