Mercurial > libervia-pubsub

comparison db/sat_pubsub_update_5_7.sql @ 433:920440200570

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore /!\ pgsql schema needs to be updated /!\ /!\ Minimal PostgreSQL required version is back to 9.5 /!\ `regconfig` is using system table, and `pg_upgrade` can't handle that, causing trouble when moving to a new major version of PostgreSQL. To work around this, the `data_fts_cfg` column type in `items` has been changed from `regconfig` to `text`. GENERATED column can't be used with type casting to `regconfig`, so the data_fts column is now generated with a trigger. As a result, the minimal requirement of PostgreSQL 12 is not necessary anymore.
author Goffi <goffi@goffi.org>
date Thu, 14 Jan 2021 17:59:23 +0100
parents
children
comparison
equal deleted inserted replaced
432:d9745fe5db46 433:920440200570
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 text NOT NULL DEFAULT 'simple';
21 ALTER TABLE items ADD COLUMN data_fts tsvector;
22 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
23
24 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
25 $$
26 BEGIN
27 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
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_cfg();
39
40 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
41 $$
42 BEGIN
43 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text);
44 RETURN new;
45 END
46 $$
47 language plpgsql;
48
49 CREATE TRIGGER items_fts_tsvector_update
50 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items
51 FOR EACH ROW
52 EXECUTE PROCEDURE update_data_fts();
53
54 /* We do the update to trigger the data_fts generation */
55 UPDATE items SET data_fts_cfg='simple';
56
57 /* we update nodes with schema to prepare for XEP-0346 implementation */
58
59 INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
60 SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1
61 FROM (
62 SELECT node_id, node, pep, publish_model, schema
63 FROM nodes
64 WHERE schema IS NOT NULL
65 ) AS s;
66
67 INSERT INTO affiliations(entity_id, node_id, affiliation)
68 SELECT aff.entity_id, tpl.node_id, 'owner'
69 FROM (
70 SELECT node_id, node, pep, publish_model, schema
71 FROM nodes
72 WHERE schema IS NOT NULL AND pep IS NOT NULL
73 ) AS s
74 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
75 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
76
77 /* we need to do a similar request for non PEP nodes */
78 INSERT INTO affiliations(entity_id, node_id, affiliation)
79 SELECT aff.entity_id, tpl.node_id, 'owner'
80 FROM (
81 SELECT node_id, node, pep, publish_model, schema
82 FROM nodes
83 WHERE schema IS NOT NULL AND pep IS NULL
84 ) AS s
85 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
86 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
87
88 INSERT INTO items(node_id, item, publisher, data)
89 SELECT
90 tpl.node_id,
91 'current',
92 e.jid||'/generated',
93 xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
94 FROM (
95 SELECT node_id, node, pep, publish_model, schema
96 FROM nodes
97 WHERE schema IS NOT NULL AND pep IS NOT NULL
98 ) AS s
99 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
100 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
101 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
102
103 /* once again for non PEP nodes */
104 INSERT INTO items(node_id, item, publisher, data)
105 SELECT
106 tpl.node_id,
107 'current',
108 e.jid||'/generated',
109 xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
110 FROM (
111 SELECT node_id, node, pep, publish_model, schema
112 FROM nodes
113 WHERE schema IS NOT NULL AND pep IS NULL
114 ) AS s
115 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
116 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
117 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
118
119 UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL;
120
121 UPDATE metadata SET value='7' WHERE key='version';