Add Itch.io links to releases

2 files changed, 35 insertions, 2 deletions

diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 0265673f..62da4f90 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -316,7 +316,8 @@ CREATE TABLE releases ( -- dbentry_type=r
   l_mg       integer NOT NULL DEFAULT 0, -- [pub]
   l_getchu   integer NOT NULL DEFAULT 0, -- [pub]
   l_getchudl integer NOT NULL DEFAULT 0, -- [pub]
-  l_dmm      text NOT NULL DEFAULT '' -- [pub]
+  l_dmm      text NOT NULL DEFAULT '', -- [pub]
+  l_itch     text NOT NULL DEFAULT '' -- [pub]
 );
 
 -- releases_hist
@@ -352,7 +353,8 @@ CREATE TABLE releases_hist (
   l_mg       integer NOT NULL DEFAULT 0,
   l_getchu   integer NOT NULL DEFAULT 0,
   l_getchudl integer NOT NULL DEFAULT 0,
-  l_dmm      text NOT NULL DEFAULT ''
+  l_dmm      text NOT NULL DEFAULT '',
+  l_itch     text NOT NULL DEFAULT ''
 );
 
 -- releases_lang
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);