annotate db/sat_pubsub_update_2_3.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 1dc606612405
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
1 -- we check version of the database before doing anything
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- and stop execution if not good
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
3 \set ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
4 DO $$
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
5 DECLARE ver text;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
6 BEGIN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
8 IF NOT FOUND OR ver!='2' THEN
403
1dc606612405 implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents: 350
diff changeset
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 2, you use version %',ver;
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
10 END IF;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
11 END$$;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
12 \unset ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
13 -- end of version check
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
14
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* we add "presence" access model */
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
16 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
17 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher'));
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
18
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
19 /* and schema column */
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
20 ALTER TABLE nodes ADD COLUMN schema xml;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
21
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
22 /* we want xml types for items data too */
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
23 ALTER TABLE items ALTER data TYPE xml using data::xml;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
24
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
25 UPDATE metadata SET value='3' WHERE key='version';