diff options
author | Yorhel <git@yorhel.nl> | 2019-08-17 11:28:40 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-08-17 11:59:34 +0200 |
commit | d61e691f31037afbe6c8e983c0318c66ad2a3e93 (patch) | |
tree | b67a89441aa7c4696e85dafb18d6d2d189440700 /util | |
parent | 95bc983ab656f529a5f49655592cf5f95971de30 (diff) |
Add Denpasoft links to releases
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/schema.sql | 6 | ||||
-rw-r--r-- | util/updates/update_20190816.sql | 16 |
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); |