summaryrefslogtreecommitdiff
path: root/util/updates
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-08-21 12:34:31 +0200
committerYorhel <git@yorhel.nl>2019-08-21 12:34:31 +0200
commit04d9ec043e1b74fe421a7a0a7d9e1822d21bd7ff (patch)
tree23b6c165088f7d66d25ee22c09e5b41f2b29ee64 /util/updates
parent03b52285a55e81b7c35d6fe59a3eca490b228bea (diff)
Add Itch.io links to releases
Diffstat (limited to 'util/updates')
-rw-r--r--util/updates/update_20190821.sql31
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);