diff options
author | Yorhel <git@yorhel.nl> | 2019-08-06 17:27:13 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-08-16 12:24:15 +0200 |
commit | 7257061704af43976d8a18829475927ebaeae99d (patch) | |
tree | d69d53abcccd3fb2135a4ffc88c325630a94ce50 /util | |
parent | 64bd8cc0a1257443df5ca3a8a714fbd62e46021b (diff) |
Add Steam links to releases
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/schema.sql | 6 | ||||
-rw-r--r-- | util/updates/update_20190816.sql | 39 |
2 files changed, 43 insertions, 2 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 5eee0517..31b6a298 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -303,7 +303,8 @@ CREATE TABLE releases ( -- dbentry_type=r ani_story smallint NOT NULL DEFAULT 0, -- [pub] ani_ero smallint NOT NULL DEFAULT 0, -- [pub] uncensored boolean NOT NULL DEFAULT FALSE, -- [pub] - engine varchar(50) NOT NULL DEFAULT '' -- [pub] + engine varchar(50) NOT NULL DEFAULT '', -- [pub] + l_steam integer NOT NULL DEFAULT 0 -- [pub] ); -- releases_hist @@ -326,7 +327,8 @@ CREATE TABLE releases_hist ( ani_story smallint NOT NULL DEFAULT 0, ani_ero smallint NOT NULL DEFAULT 0, uncensored boolean NOT NULL DEFAULT FALSE, - engine varchar(50) NOT NULL DEFAULT '' + engine varchar(50) NOT NULL DEFAULT '', + l_steam integer NOT NULL DEFAULT 0 ); -- releases_lang diff --git a/util/updates/update_20190816.sql b/util/updates/update_20190816.sql new file mode 100644 index 00000000..287581cb --- /dev/null +++ b/util/updates/update_20190816.sql @@ -0,0 +1,39 @@ +-- Run 'make' before importing this script + +ALTER TABLE releases ADD COLUMN l_steam integer NOT NULL DEFAULT 0; +ALTER TABLE releases_hist ADD COLUMN l_steam integer NOT NULL DEFAULT 0; + +\i util/sql/editfunc.sql + +CREATE OR REPLACE FUNCTION migrate_website_to_steam(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_steam = regexp_replace(website, 'https?://store\.steampowered\.com/app/([0-9]+)(?:/.*)?', '\1')::integer, website = ''; + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of website to Steam AppID.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_steam(id) FROM releases WHERE NOT hidden AND website ~ 'https?://store\.steampowered\.com/app/([0-9]+)'; +DROP FUNCTION migrate_website_to_steam(integer); + + +CREATE OR REPLACE FUNCTION migrate_notes_to_steam(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_steam = regexp_replace(notes, '^.*(?:Also available|Available) on \[url=https?://store\.steampowered\.com/app/([0-9]+)[^\]]*\]\s*Steam\s*\.?\[/url\].*$', '\1')::integer, + notes = regexp_replace(notes, '\s*(?:Also available|Available) on \[url=https?://store\.steampowered\.com/app/([0-9]+)[^\]]*\]\s*Steam\s*\.?\[/url\](?:\,?$|\.\s*)', ''); + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic extraction of Steam AppID from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_notes_to_steam(id) FROM releases WHERE NOT hidden AND l_steam = 0 + AND notes ~ '\s*(?:Also available|Available) on \[url=https?://store\.steampowered\.com/app/([0-9]+)[^\]]*\]\s*Steam\s*\.?\[/url\](?:\,?$|\.\s*)'; +DROP FUNCTION migrate_notes_to_steam(integer); + + +-- https?://store.steampowered.com/app/729330/ +-- These two often don't link to the game directly, but rather info about community patches. +-- Using these in the conversion will cause too many incorrect links. +-- https?://steamcommunity.com/app/755970/ +-- https?://steamcommunity.com/games/323490/ |