Mercurial > libervia-pubsub
annotate db/sat_pubsub_update_4_5.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 |
rev | line source |
---|---|
403
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
1 -- we check version of the database before doing anything |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
2 -- and stop execution if not good |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
3 \set ON_ERROR_STOP |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
4 DO $$ |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
5 DECLARE ver text; |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
6 BEGIN |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
7 SELECT value INTO ver FROM metadata WHERE key='version'; |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
8 IF NOT FOUND OR ver!='4' THEN |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 4, you use version %',ver; |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
10 END IF; |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
11 END$$; |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
12 \unset ON_ERROR_STOP |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
13 -- end of version check |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
14 |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
15 /* new "max_items" and "consistent publisher" options */ |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
16 ALTER TABLE nodes ADD COLUMN max_items integer NOT NULL DEFAULT 0 |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
17 CHECK (max_items >= 0); |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
18 ALTER TABLE nodes ADD COLUMN consistent_publisher boolean NOT NULL DEFAULT FALSE; |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
19 |
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
20 UPDATE metadata SET value='5' WHERE key='version'; |