Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_5_7.sql @ 454:7f1394bb96db

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
psql: fix ordering by using windowed function `row_number`
author Goffi <goffi@goffi.org>
date Mon, 02 Aug 2021 21:56:43 +0200
parents 920440200570
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';