summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-09-03 13:50:52 +0200
committerYorhel <git@yorhel.nl>2019-09-03 13:50:52 +0200
commit01f935095c90a4419fd9dfbb806fece94262cf89 (patch)
treec0a4a1e073bad6e91ae9a325deb1419dc6d8962e /util
parent409ea71adee83d05b53f14d2be10abc153905ca9 (diff)
Add Pixiv ID to staff entries
Diffstat (limited to 'util')
-rw-r--r--util/sql/schema.sql6
-rw-r--r--util/updates/update_20190903.sql24
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);