diff options
author | Yorhel <git@yorhel.nl> | 2019-08-21 12:34:31 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-08-21 12:34:31 +0200 |
commit | 04d9ec043e1b74fe421a7a0a7d9e1822d21bd7ff (patch) | |
tree | 23b6c165088f7d66d25ee22c09e5b41f2b29ee64 /util/updates | |
parent | 03b52285a55e81b7c35d6fe59a3eca490b228bea (diff) |
Add Itch.io links to releases
Diffstat (limited to 'util/updates')
-rw-r--r-- | util/updates/update_20190821.sql | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/util/updates/update_20190821.sql b/util/updates/update_20190821.sql new file mode 100644 index 00000000..a4b657d7 --- /dev/null +++ b/util/updates/update_20190821.sql @@ -0,0 +1,31 @@ +ALTER TABLE releases ADD COLUMN l_itch text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist ADD COLUMN l_itch text NOT NULL DEFAULT ''; + +\i util/sql/editfunc.sql + +CREATE OR REPLACE FUNCTION migrate_website_to_itch(rid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid AND type = 'r')); + UPDATE edit_releases SET l_itch = regexp_replace(website, '^https?://([a-z0-9_-]+)\.itch\.io/([a-z0-9_-]+)(?:\?.+)?$', '\1.itch.io/\2'), website = ''; + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of website to Itch.io.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_itch(id) FROM releases WHERE NOT hidden AND website ~ '^https?://([a-z0-9_-]+)\.itch\.io/([a-z0-9_-]+)(?:\?.+)?$'; +DROP FUNCTION migrate_website_to_itch(integer); + + +CREATE OR REPLACE FUNCTION migrate_notes_to_itch(rid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid AND type = 'r')); + UPDATE edit_releases SET + l_itch = regexp_replace(notes, '^.*\s*(?:Also available|Available) (?:on|at|from) \[url=https?://([a-z0-9_-]+)\.itch\.io/([a-z0-9_-]+)\]\s*Itch(?:\.io)?\s*\.?\[/url\].*$', '\1.itch.io/\2', 'i'), + notes = regexp_replace(notes, '\s*(?:Also available|Available) (?:on|at|from) \[url=https?://([a-z0-9_-]+)\.itch\.io/([a-z0-9_-]+)\]\s*Itch(?:\.io)?\s*\.?\[/url\](?:\,?$|\.\s*)', '', 'i'); + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic extraction of Itch.io link from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_notes_to_itch(id) FROM releases WHERE NOT hidden AND l_itch = '' + AND notes ~* '\s*(?:Also available|Available) (?:on|at|from) \[url=https?://([a-z0-9_-]+)\.itch\.io/([a-z0-9_-]+)\]\s*Itch(?:\.io)?\s*\.?\[/url\](?:\,?$|\.\s*)'; + AND id NOT IN(59555, 65209, 60553); +DROP FUNCTION migrate_notes_to_itch(integer); |