Mercurial > libervia-pubsub

annotate db/libervia_pubsub_update_5_7.sql @ 493:33222c54d2af

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
remove uses of twisted.internet.defer.returnValue This function has been deprecated in Twisted 24.7.0.
author Povilas Kanapickas <povilas@radix.lt>
date Wed, 11 Dec 2024 01:19:46 +0200
parents 9af0ef2c145c
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
1 -- we check version of the database before doing anything
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- and stop execution if not good
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
3 \set ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
4 DO $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
5 DECLARE ver text;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
6 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
8 IF NOT FOUND OR ver!='5' THEN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
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;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
10 END IF;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
11 END$$;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
12 \unset ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
13 -- end of version check
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
14
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* NOT NULL constraint was not applied to items.data */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
16 ALTER TABLE items ALTER COLUMN data SET NOT NULL;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
17
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
18 /* Full Text Search */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
19 ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
20 ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
21 ALTER TABLE items ADD COLUMN data_fts tsvector;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
22 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
23
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
24 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
25 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
26 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
27 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
28 WHERE items.node_id=new.node_id
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
29 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
30 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
31 END;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
32 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
33 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
34
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
35 CREATE TRIGGER nodes_fts_language_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
36 AFTER UPDATE OF fts_language ON nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
37 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
38 EXECUTE PROCEDURE update_data_fts_cfg();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
39
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
40 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
41 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
42 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
43 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
44 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
45 END
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
46 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
47 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
48
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
49 CREATE TRIGGER items_fts_tsvector_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
50 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
51 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
52 EXECUTE PROCEDURE update_data_fts();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
53
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
54 /* We do the update to trigger the data_fts generation */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
55 UPDATE items SET data_fts_cfg='simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
56
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
57 /* we update nodes with schema to prepare for XEP-0346 implementation */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
58
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
59 INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
60 SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
61 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
62 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
63 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
64 WHERE schema IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
65 ) AS s;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
66
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
67 INSERT INTO affiliations(entity_id, node_id, affiliation)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
68 SELECT aff.entity_id, tpl.node_id, 'owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
69 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
70 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
71 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
72 WHERE schema IS NOT NULL AND pep IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
73 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
74 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
75 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
76
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
77 /* we need to do a similar request for non PEP nodes */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
78 INSERT INTO affiliations(entity_id, node_id, affiliation)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
79 SELECT aff.entity_id, tpl.node_id, 'owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
80 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
81 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
82 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
83 WHERE schema IS NOT NULL AND pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
84 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
85 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
86 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
87
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
88 INSERT INTO items(node_id, item, publisher, data)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
89 SELECT
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
90 tpl.node_id,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
91 'current',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
92 e.jid||'/generated',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
93 xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
94 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
95 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
96 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
97 WHERE schema IS NOT NULL AND pep IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
98 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
99 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
100 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
101 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
102
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
103 /* once again for non PEP nodes */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
104 INSERT INTO items(node_id, item, publisher, data)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
105 SELECT
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
106 tpl.node_id,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
107 'current',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
108 e.jid||'/generated',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
109 xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
110 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
111 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
112 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
113 WHERE schema IS NOT NULL AND pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
114 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
115 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
116 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
117 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
118
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
119 UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
120
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
121 UPDATE metadata SET value='7' WHERE key='version';