summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-08-17 11:28:40 +0200
committerYorhel <git@yorhel.nl>2019-08-17 11:59:34 +0200
commitd61e691f31037afbe6c8e983c0318c66ad2a3e93 (patch)
treeb67a89441aa7c4696e85dafb18d6d2d189440700 /util
parent95bc983ab656f529a5f49655592cf5f95971de30 (diff)
Add Denpasoft links to releases
Diffstat (limited to 'util')
-rw-r--r--util/sql/schema.sql6
-rw-r--r--util/updates/update_20190816.sql16
2 files changed, 20 insertions, 2 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index b9ff6f05..0154e688 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -307,7 +307,8 @@ CREATE TABLE releases ( -- dbentry_type=r
l_steam integer NOT NULL DEFAULT 0, -- [pub]
l_dlsite text NOT NULL DEFAULT '', -- [pub]
l_dlsiteen text NOT NULL DEFAULT '', -- [pub]
- l_gog text NOT NULL DEFAULT '' -- [pub]
+ l_gog text NOT NULL DEFAULT '', -- [pub]
+ l_denpa text NOT NULL DEFAULT '' -- [pub]
);
-- releases_hist
@@ -334,7 +335,8 @@ CREATE TABLE releases_hist (
l_steam integer NOT NULL DEFAULT 0,
l_dlsite text NOT NULL DEFAULT '',
l_dlsiteen text NOT NULL DEFAULT '',
- l_gog text NOT NULL DEFAULT ''
+ l_gog text NOT NULL DEFAULT '',
+ l_denpa text NOT NULL DEFAULT ''
);
-- releases_lang
diff --git a/util/updates/update_20190816.sql b/util/updates/update_20190816.sql
index f61ccea8..769da299 100644
--- a/util/updates/update_20190816.sql
+++ b/util/updates/update_20190816.sql
@@ -4,10 +4,12 @@ ALTER TABLE releases ADD COLUMN l_steam integer NOT NULL DEFAULT 0;
ALTER TABLE releases ADD COLUMN l_dlsite text NOT NULL DEFAULT '';
ALTER TABLE releases ADD COLUMN l_dlsiteen text NOT NULL DEFAULT '';
ALTER TABLE releases ADD COLUMN l_gog text NOT NULL DEFAULT '';
+ALTER TABLE releases ADD COLUMN l_denpa text NOT NULL DEFAULT '';
ALTER TABLE releases_hist ADD COLUMN l_steam integer NOT NULL DEFAULT 0;
ALTER TABLE releases_hist ADD COLUMN l_dlsite text NOT NULL DEFAULT '';
ALTER TABLE releases_hist ADD COLUMN l_dlsiteen text NOT NULL DEFAULT '';
ALTER TABLE releases_hist ADD COLUMN l_gog text NOT NULL DEFAULT '';
+ALTER TABLE releases_hist ADD COLUMN l_denpa text NOT NULL DEFAULT '';
\i util/sql/editfunc.sql
@@ -80,3 +82,17 @@ SELECT migrate_notes_to_dlsite(id, notes) FROM releases WHERE NOT hidden
AND id <> 20242 -- odd special case
AND notes ~* '\s*(?:Also available|Available) (?:on|at|from) \[url=https?://[^\]]+/work/=/product_id/([RV][EJ][0-9]+)[^\]]*\]\s*DLsite\s*(?:english\s*)?\.?\[/url\](?:\,?$|\.\s*)';
DROP FUNCTION migrate_notes_to_dlsite(integer, text);
+
+
+
+CREATE OR REPLACE FUNCTION migrate_affiliates_to_denpa(rid integer, url text) 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_denpa = regexp_replace(url, '^.+/([^\/]+)/?$', '\1');
+ UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of affiliate link to Denpasoft link.';
+ PERFORM edit_r_commit();
+END;
+$$ LANGUAGE plpgsql;
+SELECT migrate_affiliates_to_denpa(rid, url) FROM affiliate_links a WHERE affiliate = 6 AND NOT hidden
+ AND NOT EXISTS(SELECT 1 FROM affiliate_links b WHERE b.id <> a.id AND a.rid = b.rid);
+DROP FUNCTION migrate_affiliates_to_denpa(integer, text);