# HG changeset patch # User Goffi # Date 1634298059 -7200 # Node ID 9af0ef2c145c8086ca6a6052ffe3e8896accb239 # Parent 391aa65f72b27852d0ce0ea7e1eac4c0aba87cc7 db: renamed update files following global renaming diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_0_1.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_0_1.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,47 @@ +-- 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 FOUND THEN + RAISE EXCEPTION 'This update file needs to be applied on older database, you don''t have to use it'; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + + +ALTER TABLE nodes ADD COLUMN pep text; + +ALTER TABLE nodes DROP CONSTRAINT nodes_node_key; +/* we need 2 partial indexes to manage NULL value for PEP */ +CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL; +CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL; + +CREATE TABLE metadata ( + key text PRIMARY KEY, + value text +); + +INSERT INTO metadata VALUES ('version', '1'); + +CREATE TABLE item_categories ( + item_categories_id serial PRIMARY KEY, + item_id integer NOT NULL references items ON DELETE CASCADE, + category text NOT NULL, + UNIQUE (item_id,category) +); + +UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%'; + +/* This is to update namespaces, SàT was buggy before 0.6 and didn't set the atom namespace in */ +/* But yeah, this is a crazy query */ +UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id), + XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/preceding-sibling::*', data::xml)::text[],''),'')), + xmlelement(name entry, xmlattributes('http://www.w3.org/2005/Atom' as xmlns), array_to_string(xpath('/item/entry/*', data::xml)::text[], '')::xml), + XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/following-sibling::*', data::xml)::text[],''),''))) + FROM nodes WHERE nodes.node_id = items.node_id + AND (node = 'urn:xmpp:microblog:0' or node LIKE 'urn:xmpp:comments:%') + AND XMLEXISTS('/item/entry' PASSING (data::xml)); diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_1_2.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_1_2.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,42 @@ +-- we check version of the database before doing anything +-- and stop execution if not good +\set ON_ERROR_STOP +DO $$ +DECLARE ver smallint; +BEGIN + SELECT value INTO ver FROM metadata WHERE key='version'; + IF NOT FOUND OR ver!='1' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 1, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* roster access model was badly used, we rename it to publisher-roster */ + +ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; +UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster'; +ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); + +ALTER TABLE items DROP CONSTRAINT items_access_model_check; +UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster'; +ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')); + +ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check; +ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')); + +CREATE TABLE item_jids_authorized ( + item_jids_authorized_id serial PRIMARY KEY, + item_id integer NOT NULL references items ON DELETE CASCADE, + jid text NOT NULL, + UNIQUE (item_id,jid) +); + +CREATE TABLE item_languages ( + item_languages_id serial PRIMARY KEY, + item_id integer NOT NULL references items ON DELETE CASCADE, + language text NOT NULL, + UNIQUE (item_id,language) +); + +UPDATE metadata SET value='2' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_2_3.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_2_3.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,25 @@ +-- 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!='2' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 2, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* we add "presence" access model */ +ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; +ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); + +/* and schema column */ +ALTER TABLE nodes ADD COLUMN schema xml; + +/* we want xml types for items data too */ +ALTER TABLE items ALTER data TYPE xml using data::xml; + +UPDATE metadata SET value='3' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_3_4.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_3_4.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,22 @@ +-- 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!='3' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 3, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* new "serial ids" option */ +ALTER TABLE nodes ADD COLUMN serial_ids boolean NOT NULL DEFAULT FALSE; + +/* we want to keep creation and update times */ +ALTER TABLE items RENAME COLUMN date TO created; +ALTER TABLE items ADD COLUMN updated timestamp with time zone NOT NULL DEFAULT now(); + +UPDATE metadata SET value='4' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_4_5.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_4_5.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,20 @@ +-- 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!='4' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 4, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* new "max_items" and "consistent publisher" options */ +ALTER TABLE nodes ADD COLUMN max_items integer NOT NULL DEFAULT 0 + CHECK (max_items >= 0); +ALTER TABLE nodes ADD COLUMN consistent_publisher boolean NOT NULL DEFAULT FALSE; + +UPDATE metadata SET value='5' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_5_7.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_5_7.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,121 @@ +-- 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!='5' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* NOT NULL constraint was not applied to items.data */ +ALTER TABLE items ALTER COLUMN data SET NOT NULL; + +/* Full Text Search */ +ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic'; +ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple'; +ALTER TABLE items ADD COLUMN data_fts tsvector; +CREATE INDEX items_data_fts ON items USING GIN (data_fts); + +CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS +$$ +BEGIN + UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') + WHERE items.node_id=new.node_id + AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); + RETURN new; +END; +$$ +language plpgsql; + +CREATE TRIGGER nodes_fts_language_update + AFTER UPDATE OF fts_language ON nodes + FOR EACH ROW + EXECUTE PROCEDURE update_data_fts_cfg(); + +CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS +$$ +BEGIN + new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); + RETURN new; +END +$$ +language plpgsql; + +CREATE TRIGGER items_fts_tsvector_update + BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items + FOR EACH ROW + EXECUTE PROCEDURE update_data_fts(); + +/* We do the update to trigger the data_fts generation */ +UPDATE items SET data_fts_cfg='simple'; + +/* we update nodes with schema to prepare for XEP-0346 implementation */ + +INSERT INTO nodes(node, pep, persist_items, publish_model, max_items) + SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1 + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL + ) AS s; + +INSERT INTO affiliations(entity_id, node_id, affiliation) + SELECT aff.entity_id, tpl.node_id, 'owner' + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NOT NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep + LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; + +/* we need to do a similar request for non PEP nodes */ +INSERT INTO affiliations(entity_id, node_id, affiliation) + SELECT aff.entity_id, tpl.node_id, 'owner' + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL + LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; + +INSERT INTO items(node_id, item, publisher, data) + SELECT + tpl.node_id, + 'current', + e.jid||'/generated', + xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NOT NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep + LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' + LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; + +/* once again for non PEP nodes */ +INSERT INTO items(node_id, item, publisher, data) + SELECT + tpl.node_id, + 'current', + e.jid||'/generated', + xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL + LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' + LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; + +UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL; + +UPDATE metadata SET value='7' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_6_7.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_6_7.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,58 @@ +-- NOTE: this update is to be used only by people which have been installing the +-- 6th version of the schema. It is has been replaced because regconfig prevent +-- proper update (the GENERATED column with regconfig has been replaced by a +-- trigger). People which haven't installed 6th version can directly use the +-- sat_pubsub_update_5_7.sql file. The sat_pubsub_update_5_6.sql has been +-- deleted has it's not needed anymore and can lead to troubles. + +-- 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!='6' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 6, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* regconfig type is not usable when doing database upgrade (for new PostgreSQL major version) */ +ALTER TABLE items DROP COLUMN data_fts; +ALTER TABLE items ALTER COLUMN data_fts_cfg TYPE text; +ALTER TABLE items ADD COLUMN data_fts tsvector; +CREATE INDEX items_data_fts ON items USING GIN (data_fts); + +ALTER FUNCTION update_data_fts() RENAME TO update_data_fts_cfg; +/* We don't use regconfig anymore in this method */ +CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS +$$ +BEGIN + UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') + WHERE items.node_id=new.node_id + AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); + RETURN new; +END; +$$ +language plpgsql; + +CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS +$$ +BEGIN + new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); + RETURN new; +END +$$ +language plpgsql; + +CREATE TRIGGER items_fts_tsvector_update + BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items + FOR EACH ROW + EXECUTE PROCEDURE update_data_fts(); + +/* We do the update to trigger the data_fts generation */ +UPDATE items SET data_fts_cfg='simple'; + +UPDATE metadata SET value='7' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/libervia_pubsub_update_7_8.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/libervia_pubsub_update_7_8.sql Fri Oct 15 13:40:59 2021 +0200 @@ -0,0 +1,19 @@ +-- 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!='7' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 7, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* new "overwrite_policy" option */ +ALTER TABLE nodes ADD COLUMN overwrite_policy text NOT NULL DEFAULT 'original_publisher' + CHECK (overwrite_policy IN ('original_publisher', 'any_publisher')); + +UPDATE metadata SET value='8' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_0_1.sql --- a/db/sat_pubsub_update_0_1.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,47 +0,0 @@ --- 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 FOUND THEN - RAISE EXCEPTION 'This update file needs to be applied on older database, you don''t have to use it'; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - - -ALTER TABLE nodes ADD COLUMN pep text; - -ALTER TABLE nodes DROP CONSTRAINT nodes_node_key; -/* we need 2 partial indexes to manage NULL value for PEP */ -CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL; -CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL; - -CREATE TABLE metadata ( - key text PRIMARY KEY, - value text -); - -INSERT INTO metadata VALUES ('version', '1'); - -CREATE TABLE item_categories ( - item_categories_id serial PRIMARY KEY, - item_id integer NOT NULL references items ON DELETE CASCADE, - category text NOT NULL, - UNIQUE (item_id,category) -); - -UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%'; - -/* This is to update namespaces, SàT was buggy before 0.6 and didn't set the atom namespace in */ -/* But yeah, this is a crazy query */ -UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id), - XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/preceding-sibling::*', data::xml)::text[],''),'')), - xmlelement(name entry, xmlattributes('http://www.w3.org/2005/Atom' as xmlns), array_to_string(xpath('/item/entry/*', data::xml)::text[], '')::xml), - XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/following-sibling::*', data::xml)::text[],''),''))) - FROM nodes WHERE nodes.node_id = items.node_id - AND (node = 'urn:xmpp:microblog:0' or node LIKE 'urn:xmpp:comments:%') - AND XMLEXISTS('/item/entry' PASSING (data::xml)); diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_1_2.sql --- a/db/sat_pubsub_update_1_2.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,42 +0,0 @@ --- we check version of the database before doing anything --- and stop execution if not good -\set ON_ERROR_STOP -DO $$ -DECLARE ver smallint; -BEGIN - SELECT value INTO ver FROM metadata WHERE key='version'; - IF NOT FOUND OR ver!='1' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 1, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* roster access model was badly used, we rename it to publisher-roster */ - -ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; -UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster'; -ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); - -ALTER TABLE items DROP CONSTRAINT items_access_model_check; -UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster'; -ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')); - -ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check; -ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')); - -CREATE TABLE item_jids_authorized ( - item_jids_authorized_id serial PRIMARY KEY, - item_id integer NOT NULL references items ON DELETE CASCADE, - jid text NOT NULL, - UNIQUE (item_id,jid) -); - -CREATE TABLE item_languages ( - item_languages_id serial PRIMARY KEY, - item_id integer NOT NULL references items ON DELETE CASCADE, - language text NOT NULL, - UNIQUE (item_id,language) -); - -UPDATE metadata SET value='2' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_2_3.sql --- a/db/sat_pubsub_update_2_3.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,25 +0,0 @@ --- 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!='2' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 2, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* we add "presence" access model */ -ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; -ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); - -/* and schema column */ -ALTER TABLE nodes ADD COLUMN schema xml; - -/* we want xml types for items data too */ -ALTER TABLE items ALTER data TYPE xml using data::xml; - -UPDATE metadata SET value='3' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_3_4.sql --- a/db/sat_pubsub_update_3_4.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,22 +0,0 @@ --- 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!='3' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 3, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* new "serial ids" option */ -ALTER TABLE nodes ADD COLUMN serial_ids boolean NOT NULL DEFAULT FALSE; - -/* we want to keep creation and update times */ -ALTER TABLE items RENAME COLUMN date TO created; -ALTER TABLE items ADD COLUMN updated timestamp with time zone NOT NULL DEFAULT now(); - -UPDATE metadata SET value='4' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_4_5.sql --- a/db/sat_pubsub_update_4_5.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,20 +0,0 @@ --- 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!='4' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 4, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* new "max_items" and "consistent publisher" options */ -ALTER TABLE nodes ADD COLUMN max_items integer NOT NULL DEFAULT 0 - CHECK (max_items >= 0); -ALTER TABLE nodes ADD COLUMN consistent_publisher boolean NOT NULL DEFAULT FALSE; - -UPDATE metadata SET value='5' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_5_7.sql --- a/db/sat_pubsub_update_5_7.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,121 +0,0 @@ --- 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!='5' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* NOT NULL constraint was not applied to items.data */ -ALTER TABLE items ALTER COLUMN data SET NOT NULL; - -/* Full Text Search */ -ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic'; -ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple'; -ALTER TABLE items ADD COLUMN data_fts tsvector; -CREATE INDEX items_data_fts ON items USING GIN (data_fts); - -CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS -$$ -BEGIN - UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') - WHERE items.node_id=new.node_id - AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); - RETURN new; -END; -$$ -language plpgsql; - -CREATE TRIGGER nodes_fts_language_update - AFTER UPDATE OF fts_language ON nodes - FOR EACH ROW - EXECUTE PROCEDURE update_data_fts_cfg(); - -CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS -$$ -BEGIN - new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); - RETURN new; -END -$$ -language plpgsql; - -CREATE TRIGGER items_fts_tsvector_update - BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items - FOR EACH ROW - EXECUTE PROCEDURE update_data_fts(); - -/* We do the update to trigger the data_fts generation */ -UPDATE items SET data_fts_cfg='simple'; - -/* we update nodes with schema to prepare for XEP-0346 implementation */ - -INSERT INTO nodes(node, pep, persist_items, publish_model, max_items) - SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1 - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL - ) AS s; - -INSERT INTO affiliations(entity_id, node_id, affiliation) - SELECT aff.entity_id, tpl.node_id, 'owner' - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NOT NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep - LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; - -/* we need to do a similar request for non PEP nodes */ -INSERT INTO affiliations(entity_id, node_id, affiliation) - SELECT aff.entity_id, tpl.node_id, 'owner' - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL - LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; - -INSERT INTO items(node_id, item, publisher, data) - SELECT - tpl.node_id, - 'current', - e.jid||'/generated', - xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NOT NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep - LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' - LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; - -/* once again for non PEP nodes */ -INSERT INTO items(node_id, item, publisher, data) - SELECT - tpl.node_id, - 'current', - e.jid||'/generated', - xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL - LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' - LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; - -UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL; - -UPDATE metadata SET value='7' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_6_7.sql --- a/db/sat_pubsub_update_6_7.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,58 +0,0 @@ --- NOTE: this update is to be used only by people which have been installing the --- 6th version of the schema. It is has been replaced because regconfig prevent --- proper update (the GENERATED column with regconfig has been replaced by a --- trigger). People which haven't installed 6th version can directly use the --- sat_pubsub_update_5_7.sql file. The sat_pubsub_update_5_6.sql has been --- deleted has it's not needed anymore and can lead to troubles. - --- 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!='6' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 6, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* regconfig type is not usable when doing database upgrade (for new PostgreSQL major version) */ -ALTER TABLE items DROP COLUMN data_fts; -ALTER TABLE items ALTER COLUMN data_fts_cfg TYPE text; -ALTER TABLE items ADD COLUMN data_fts tsvector; -CREATE INDEX items_data_fts ON items USING GIN (data_fts); - -ALTER FUNCTION update_data_fts() RENAME TO update_data_fts_cfg; -/* We don't use regconfig anymore in this method */ -CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS -$$ -BEGIN - UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') - WHERE items.node_id=new.node_id - AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); - RETURN new; -END; -$$ -language plpgsql; - -CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS -$$ -BEGIN - new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); - RETURN new; -END -$$ -language plpgsql; - -CREATE TRIGGER items_fts_tsvector_update - BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items - FOR EACH ROW - EXECUTE PROCEDURE update_data_fts(); - -/* We do the update to trigger the data_fts generation */ -UPDATE items SET data_fts_cfg='simple'; - -UPDATE metadata SET value='7' WHERE key='version'; diff -r 391aa65f72b2 -r 9af0ef2c145c db/sat_pubsub_update_7_8.sql --- a/db/sat_pubsub_update_7_8.sql Fri Oct 15 13:40:59 2021 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,19 +0,0 @@ --- 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!='7' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 7, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* new "overwrite_policy" option */ -ALTER TABLE nodes ADD COLUMN overwrite_policy text NOT NULL DEFAULT 'original_publisher' - CHECK (overwrite_policy IN ('original_publisher', 'any_publisher')); - -UPDATE metadata SET value='8' WHERE key='version';