libervia-pubsub: db/pubsub.sql annotate

annotate db/pubsub.sql @ 451:0e6e176cb572

pgsql: fix items order: Due to a misinterpretation, items where returned in wrong order when RSM was used. Pubsub vanilla `max_items` is reversing the items order to get last items, but when this attribute is not used (RSM being used or not), the items must be in chronological order. This patch fixes it, so RSM returns oldest items by defaut, and empty `<before/>` must be used to get last page (and thus most recent items). Items are always finally ordered chronologically inside the returned page (default by `update` time, but this can be modified when a specific order is requested). Reference field is also fixed to use `updated` instead of `item_id` when item are ordered by `update`.

author	Goffi <goffi@goffi.org>
date	Sun, 06 Jun 2021 19:42:57 +0200
parents	c57b9ede1381
children	0d38c3529972

rev	line source
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	1 CREATE TABLE entities (
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	2 entity_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	3 jid text NOT NULL UNIQUE
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	4 );
f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	5
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	6 CREATE TABLE nodes (
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	7 node_id serial PRIMARY KEY,
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	8 node text NOT NULL,
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	9 pep text,
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	10 node_type text NOT NULL DEFAULT 'leaf'
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	11 CHECK (node_type IN ('leaf', 'collection')),
240 70c8bb90d75f added access_model to config, default to 'open' Goffi <goffi@goffi.org> parents: 206 diff changeset	12 access_model text NOT NULL DEFAULT 'open'
350 4d4575911060 sql (PostgreSQL): schema update: Goffi <goffi@goffi.org> parents: 329 diff changeset	13 CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	14 persist_items boolean,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	15 deliver_payloads boolean NOT NULL DEFAULT TRUE,
403 1dc606612405 implemented experimental "consistent_publisher" option: Goffi <goffi@goffi.org> parents: 367 diff changeset	16 max_items integer NOT NULL DEFAULT 0
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	17 CHECK (max_items >= 0),
438 b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting: Goffi <goffi@goffi.org> parents: 433 diff changeset	18 overwrite_policy text NOT NULL DEFAULT 'original_publisher'
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting: Goffi <goffi@goffi.org> parents: 433 diff changeset	19 CHECK (overwrite_policy IN ('original_publisher', 'any_publisher')),
367 a772f7dac930 backend, storage(pgsql): creation/update date + serial ids: Goffi <goffi@goffi.org> parents: 360 diff changeset	20 serial_ids boolean NOT NULL DEFAULT FALSE,
403 1dc606612405 implemented experimental "consistent_publisher" option: Goffi <goffi@goffi.org> parents: 367 diff changeset	21 consistent_publisher boolean NOT NULL DEFAULT FALSE,
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	22 fts_language text NOT NULL DEFAULT 'generic',
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	23 send_last_published_item text NOT NULL DEFAULT 'on_sub'
260 f0cd02c032b3 publish model management Goffi <goffi@goffi.org> parents: 248 diff changeset	24 CHECK (send_last_published_item IN ('never', 'on_sub')),
f0cd02c032b3 publish model management Goffi <goffi@goffi.org> parents: 248 diff changeset	25 publish_model text NOT NULL DEFAULT 'publishers'
359 1d93f94feee3 psql (schema): fixed forgotten comma Goffi <goffi@goffi.org> parents: 350 diff changeset	26 CHECK (publish_model IN ('publishers', 'subscribers', 'open')),
360 6350622d9eb9 psql (schema): fixed bad semi-colon Goffi <goffi@goffi.org> parents: 359 diff changeset	27 schema xml
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	28 );
f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	29
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	30 /* we need 2 partial indexes to manage NULL value for PEP */
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	31 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL;
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	32 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL;
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	33
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	34 INSERT INTO nodes (node, node_type) values ('', 'collection');
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	35
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	36 CREATE TABLE affiliations (
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	37 affiliation_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	38 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	39 node_id integer NOT NULL references nodes ON DELETE CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	40 affiliation text NOT NULL
329 98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	41 CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	42 UNIQUE (entity_id, node_id)
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	43 );
f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	44
243 42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	45 CREATE TABLE node_groups_authorized (
42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	46 node_groups_authorized_id serial PRIMARY KEY,
42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	47 node_id integer NOT NULL references nodes ON DELETE CASCADE,
42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	48 groupname text NOT NULL,
42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	49 UNIQUE (node_id,groupname)
42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	50 );
42048e37699e added experimental roster access_model (use remote_roster) Goffi <goffi@goffi.org> parents: 240 diff changeset	51
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	52 CREATE TABLE subscriptions (
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	53 subscription_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	54 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	55 resource text,
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	56 node_id integer NOT NULL REFERENCES nodes ON delete CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	57 state text NOT NULL DEFAULT 'subscribed'
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	58 CHECK (state IN ('subscribed', 'pending', 'unconfigured')),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	59 subscription_type text
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	60 CHECK (subscription_type IN (NULL, 'items', 'nodes')),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	61 subscription_depth text
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	62 CHECK (subscription_depth IN (NULL, '1', 'all')),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	63 UNIQUE (entity_id, resource, node_id));
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	64
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	65 CREATE TABLE items (
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	66 item_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	67 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	68 item text NOT NULL,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	69 publisher text NOT NULL,
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	70 data xml NOT NULL,
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	71 data_fts tsvector,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	72 data_fts_cfg text NOT NULL DEFAULT 'simple',
248 50f6ee966da8 item are gotten according to item's access model in getItems Goffi <goffi@goffi.org> parents: 244 diff changeset	73 access_model text NOT NULL DEFAULT 'open'
329 98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	74 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
367 a772f7dac930 backend, storage(pgsql): creation/update date + serial ids: Goffi <goffi@goffi.org> parents: 360 diff changeset	75 created timestamp with time zone NOT NULL DEFAULT now(),
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids: Goffi <goffi@goffi.org> parents: 360 diff changeset	76 updated timestamp with time zone NOT NULL DEFAULT now(),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	77 UNIQUE (node_id, item)
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	78 );
244 3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	79
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	80 CREATE TABLE item_groups_authorized (
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	81 item_groups_authorized_id serial PRIMARY KEY,
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	82 item_id integer NOT NULL references items ON DELETE CASCADE,
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	83 groupname text NOT NULL,
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	84 UNIQUE (item_id,groupname)
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	85 );
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	86
329 98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	87 CREATE TABLE item_jids_authorized (
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	88 item_jids_authorized_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	89 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	90 jid text NOT NULL,
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	91 UNIQUE (item_id,jid)
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	92 );
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	93
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	94 CREATE TABLE item_languages (
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	95 item_languages_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	96 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	97 language text NOT NULL,
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	98 UNIQUE (item_id,language)
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	99 );
98409ef42c94 PostGreSQL: schema update: Goffi <goffi@goffi.org> parents: 301 diff changeset	100
301 05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	101 CREATE TABLE item_categories (
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	102 item_categories_id serial PRIMARY KEY,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	103 item_id integer NOT NULL references items ON DELETE CASCADE,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	104 category text NOT NULL,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	105 UNIQUE (item_id,category)
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	106 );
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry: Goffi <goffi@goffi.org> parents: 295 diff changeset	107
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	108 /* Full Text Search */
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	109 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	110
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	111 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	112 $$
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	113 BEGIN
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	114 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	115 WHERE items.node_id=new.node_id
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	116 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	117 RETURN new;
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	118 END;
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	119 $$
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	120 language plpgsql;
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	121
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	122 CREATE TRIGGER nodes_fts_language_update
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	123 AFTER UPDATE OF fts_language ON nodes
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	124 FOR EACH ROW
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	125 EXECUTE PROCEDURE update_data_fts_cfg();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	126
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	127 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	128 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	129 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	130 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: 430 diff changeset	131 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	132 END
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	133 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	134 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	135
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	136 CREATE TRIGGER items_fts_tsvector_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	137 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: 430 diff changeset	138 FOR EACH ROW
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	139 EXECUTE PROCEDURE update_data_fts();
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	140
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: 430 diff changeset	141
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	142 CREATE TABLE metadata (
430 5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	143 key text PRIMARY KEY,
5a0ada3b61ca Full-Text Search implementation: Goffi <goffi@goffi.org> parents: 403 diff changeset	144 value text
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	145 );
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	146
448 c57b9ede1381 pgsql: fix wrong version in schema Goffi <goffi@goffi.org> parents: 438 diff changeset	147 INSERT INTO metadata VALUES ('version', '8');

Mercurial > libervia-pubsub

annotate db/pubsub.sql @ 451:0e6e176cb572