Mercurial > libervia-pubsub
changeset 466:0d38c3529972
psql: schema update (9) to add `roster` table
author | Goffi <goffi@goffi.org> |
---|---|
date | Fri, 15 Oct 2021 13:40:59 +0200 |
parents | 9af0ef2c145c |
children | d86e0f8a1405 |
files | db/libervia_pubsub_update_8_9.sql db/pubsub.sql sat_pubsub/pgsql_storage.py |
diffstat | 3 files changed, 36 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_8_9.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,24 @@ +-- we check version of the database before doing anything +-- and stop execution if not good +\set ON_ERROR_STOP +DO $$ +DECLARE ver text; +BEGIN + SELECT value INTO ver FROM metadata WHERE key='version'; + IF NOT FOUND OR ver!='8' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 8, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* new "roster" table */ +CREATE TABLE roster ( + roster_id serial PRIMARY KEY, + jid text NOT NULL UNIQUE, + version text, + updated timestamp with time zone NOT NULL, + roster xml NOT NULL +); + +UPDATE metadata SET value='9' WHERE key='version';
--- a/db/pubsub.sql Fri Oct 15 13:40:59 2021 +0200 +++ b/db/pubsub.sql Fri Oct 15 13:40:59 2021 +0200 @@ -138,10 +138,20 @@ FOR EACH ROW EXECUTE PROCEDURE update_data_fts(); +/* Roster */ + +CREATE TABLE roster ( + roster_id serial PRIMARY KEY, + jid text NOT NULL UNIQUE, + version text, + updated timestamp with time zone NOT NULL, + roster xml NOT NULL +); + CREATE TABLE metadata ( key text PRIMARY KEY, value text ); -INSERT INTO metadata VALUES ('version', '8'); +INSERT INTO metadata VALUES ('version', '9');
--- a/sat_pubsub/pgsql_storage.py Fri Oct 15 13:40:59 2021 +0200 +++ b/sat_pubsub/pgsql_storage.py Fri Oct 15 13:40:59 2021 +0200 @@ -78,7 +78,7 @@ parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8')) ITEMS_SEQ_NAME = 'node_{node_id}_seq' PEP_COL_NAME = 'pep' -CURRENT_VERSION = '8' +CURRENT_VERSION = '9' # retrieve the maximum integer item id + 1 NEXT_ITEM_ID_QUERY = r"SELECT COALESCE(max(item::integer)+1,1) as val from items where node_id={node_id} and item ~ E'^\\d+$'"