diff options
author | Yorhel <git@yorhel.nl> | 2019-09-03 13:50:52 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-09-03 13:50:52 +0200 |
commit | 01f935095c90a4419fd9dfbb806fece94262cf89 (patch) | |
tree | c0a4a1e073bad6e91ae9a325deb1419dc6d8962e /util | |
parent | 409ea71adee83d05b53f14d2be10abc153905ca9 (diff) |
Add Pixiv ID to staff entries
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/schema.sql | 6 | ||||
-rw-r--r-- | util/updates/update_20190903.sql | 24 |
2 files changed, 28 insertions, 2 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql index bea433a0..5d4a801e 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -522,7 +522,8 @@ CREATE TABLE staff ( -- dbentry_type=s l_site varchar(250) NOT NULL DEFAULT '', -- [pub] l_twitter varchar(16) NOT NULL DEFAULT '', -- [pub] l_anidb integer, -- [pub] - l_wikidata integer -- [pub] + l_wikidata integer, -- [pub] + l_pixiv integer NOT NULL DEFAULT 0 -- [pub] ); -- staff_hist @@ -536,7 +537,8 @@ CREATE TABLE staff_hist ( l_site varchar(250) NOT NULL DEFAULT '', l_twitter varchar(16) NOT NULL DEFAULT '', l_anidb integer, - l_wikidata integer + l_wikidata integer, + l_pixiv integer NOT NULL DEFAULT 0 ); -- staff_alias diff --git a/util/updates/update_20190903.sql b/util/updates/update_20190903.sql index ef11efc1..813d26aa 100644 --- a/util/updates/update_20190903.sql +++ b/util/updates/update_20190903.sql @@ -5,3 +5,27 @@ ALTER TABLE wikidata ADD COLUMN pcgamingwiki text[]; ALTER TABLE wikidata ADD COLUMN steam integer[]; ALTER TABLE wikidata ADD COLUMN gog text[]; ALTER TABLE wikidata ADD COLUMN pixiv_user integer[]; + + +ALTER TABLE staff ADD COLUMN l_pixiv integer NOT NULL DEFAULT 0; +ALTER TABLE staff_hist ADD COLUMN l_pixiv integer NOT NULL DEFAULT 0; + +\i util/sql/editfunc.sql + + +-- C U R S E D -- + +CREATE OR REPLACE FUNCTION migrate_desc_to_pixiv(sid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_s_init(sid, (SELECT MAX(rev) FROM changes WHERE itemid = sid AND type = 's')); + UPDATE edit_staff SET + l_pixiv = regexp_replace("desc", '^.*www\.pixiv\.net/(?:member\.php\?id=|whitecube/user/)([0-9]+).*$', '\1', 'i')::integer, + "desc" = trim(both E' \t\r\n' from regexp_replace("desc", '(?<=^|\n)\s*(?:(?:His|her )?Pixiv(?: profile| account| link)?\s*:\s+|(?:His|Her) Pixiv (?:profile|account) (?:can be (?:viewed|visited|accessed|reached)|is) )?(?:\[URL=)?(?:https?://)?www\.pixiv\.net/(?:member\.php\?id=|whitecube/user/)([0-9]+)(?:\](?:here|pixiv|link|URL|pixiv link|pixiv profile|pixiv account)\.?\[/URL\])?(?:\n|\s*$|\.\s*)', '', 'i')); + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic extraction of Pixiv id from the notes.'; + PERFORM edit_s_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_desc_to_pixiv(id) FROM staff WHERE NOT hidden +--SELECT id, "desc" FROM staff WHERE NOT hidden AND "desc" ~ 'pixiv\.net' + AND "desc" ~* '(?<=^|\n)\s*(?:(?:His|her )?Pixiv(?: profile| account| link)?\s*:\s+|(?:His|Her) Pixiv (?:profile|account) (?:can be (?:viewed|visited|accessed|reached)|is) )?(?:\[URL=)?(?:https?://)?www\.pixiv\.net/(?:member\.php\?id=|whitecube/user/)([0-9]+)(?:\](?:here|pixiv|link|URL|pixiv link|pixiv profile|pixiv account)\.?\[/URL\])?(?:\n|\s*$|\.\s*)'; +DROP FUNCTION migrate_desc_to_pixiv(integer); |