annotate db/sat_pubsub_update_1_2.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: 329
diff changeset
1 -- we check version of the database before doing anything
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
2 -- and stop execution if not good
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
3 \set ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
4 DO $$
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
5 DECLARE ver smallint;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
6 BEGIN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
8 IF NOT FOUND OR ver!='1' 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 1, you use version %',ver;
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
10 END IF;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
11 END$$;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
12 \unset ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
13 -- end of version check
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
14
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* roster access model was badly used, we rename it to publisher-roster */
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
16
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
17 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check;
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
18 UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster';
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
19 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher'));
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
20
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
21 ALTER TABLE items DROP CONSTRAINT items_access_model_check;
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
22 UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster';
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
23 ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist'));
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
24
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
25 ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check;
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
26 ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner'));
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
27
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
28 CREATE TABLE item_jids_authorized (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
29 item_jids_authorized_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
30 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
31 jid text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
32 UNIQUE (item_id,jid)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
33 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
34
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
35 CREATE TABLE item_languages (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
36 item_languages_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
37 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
38 language text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
39 UNIQUE (item_id,language)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
40 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
41
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
42 UPDATE metadata SET value='2' WHERE key='version';