|
430
|
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'; |
