summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-11-02 10:43:47 +0100
committerYorhel <git@yorhel.nl>2019-11-02 10:43:47 +0100
commit2cd005791529c6501901ac2bc0a7752f88fa7481 (patch)
treeaec3c0a19e08f9cf821c8b3a65bb95d5df12e723 /util
parent155f4d335a698f6cef6f0ddaf932d3302ebcc53d (diff)
Add character cup size field + conversion + filter
Diffstat (limited to 'util')
-rwxr-xr-xutil/jsgen.pl1
-rw-r--r--util/sql/schema.sql7
-rw-r--r--util/updates/update_20191102.sql51
3 files changed, 57 insertions, 2 deletions
diff --git a/util/jsgen.pl b/util/jsgen.pl
index bc9e75b9..ff5868d5 100755
--- a/util/jsgen.pl
+++ b/util/jsgen.pl
@@ -48,6 +48,7 @@ sub vars {
blood_types => [ map [ $_, $BLOOD_TYPE{$_} ], keys %BLOOD_TYPE ],
genders => [ map [ $_, $GENDER{$_} ], keys %GENDER ],
credit_type => [ map [ $_, $CREDIT_TYPE{$_} ], keys %CREDIT_TYPE ],
+ cup_size => [ grep $_, keys %CUP_SIZE ],
resolutions => scalar resolutions(),
);
JSON::XS->new->encode(\%vars);
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index a3c65c7b..ff753619 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -51,6 +51,7 @@ CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
+CREATE TYPE cup_size AS ENUM ('', 'AAA', 'AA', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
@@ -119,7 +120,8 @@ CREATE TABLE chars ( -- dbentry_type=c
weight smallint, -- [pub]
bloodt blood_type NOT NULL DEFAULT 'unknown', -- [pub]
main integer, -- [pub] chars.id
- main_spoil smallint NOT NULL DEFAULT 0 -- [pub]
+ main_spoil smallint NOT NULL DEFAULT 0, -- [pub]
+ cup_size cup_size NOT NULL DEFAULT '' -- [pub]
);
-- chars_hist
@@ -140,7 +142,8 @@ CREATE TABLE chars_hist (
weight smallint,
bloodt blood_type NOT NULL DEFAULT 'unknown',
main integer, -- chars.id
- main_spoil smallint NOT NULL DEFAULT 0
+ main_spoil smallint NOT NULL DEFAULT 0,
+ cup_size cup_size NOT NULL DEFAULT ''
);
-- chars_traits
diff --git a/util/updates/update_20191102.sql b/util/updates/update_20191102.sql
new file mode 100644
index 00000000..9afc4379
--- /dev/null
+++ b/util/updates/update_20191102.sql
@@ -0,0 +1,51 @@
+CREATE TYPE cup_size AS ENUM ('', 'AAA', 'AA', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
+
+ALTER TABLE chars ADD COLUMN cup_size cup_size NOT NULL DEFAULT '';
+ALTER TABLE chars_hist ADD COLUMN cup_size cup_size NOT NULL DEFAULT '';
+
+\i util/sql/editfunc.sql
+
+
+
+CREATE OR REPLACE FUNCTION migrate_trait_to_cup(cid integer, cup cup_size) RETURNS void AS $$
+BEGIN
+ PERFORM edit_c_init(cid, (SELECT MAX(rev) FROM changes WHERE itemid = cid AND type = 'c'));
+ UPDATE edit_chars SET cup_size = cup;
+ DELETE FROM edit_chars_traits WHERE tid IN(722, 1182, 1183, 1178, 1184, 723, 2129, 2115);
+ UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of breast size trait to cup size field.';
+ PERFORM edit_c_commit();
+END;
+$$ LANGUAGE plpgsql;
+
+UPDATE traits SET state = 1 WHERE id IN(722, 1182, 1183, 1178, 1184);
+
+-- This takes a while (slowness is likely due to traits_chars_calc(), can be temporarily disabled, but w/e)
+\timing
+SELECT migrate_trait_to_cup(c.id, 'AA') FROM chars c JOIN chars_traits ct ON ct.id = c.id WHERE NOT c.hidden AND c.cup_size = '' AND ct.tid = 722;
+SELECT migrate_trait_to_cup(c.id, 'A' ) FROM chars c JOIN chars_traits ct ON ct.id = c.id WHERE NOT c.hidden AND c.cup_size = '' AND ct.tid = 1182;
+SELECT migrate_trait_to_cup(c.id, 'B' ) FROM chars c JOIN chars_traits ct ON ct.id = c.id WHERE NOT c.hidden AND c.cup_size = '' AND ct.tid = 1183;
+SELECT migrate_trait_to_cup(c.id, 'C' ) FROM chars c JOIN chars_traits ct ON ct.id = c.id WHERE NOT c.hidden AND c.cup_size = '' AND ct.tid = 1178;
+SELECT migrate_trait_to_cup(c.id, 'D' ) FROM chars c JOIN chars_traits ct ON ct.id = c.id WHERE NOT c.hidden AND c.cup_size = '' AND ct.tid = 1184;
+\timing
+
+DROP FUNCTION migrate_trait_to_cup(integer, cup_size);
+
+
+-- Regex magic by skorpiondeath (with minor changes) - https://query.vndb.org/queries/kKFzwjqvAshONiaf
+CREATE OR REPLACE FUNCTION migrate_desc_to_cup(cid integer) RETURNS void AS $$
+BEGIN
+ PERFORM edit_c_init(cid, (SELECT MAX(rev) FROM changes WHERE itemid = cid AND type = 'c'));
+ UPDATE edit_chars
+ SET cup_size = substring( substring("desc" from '([c|C]up[\s]*(size|Size)?:[\s]*[A-Z][A]*)') from '[A]*.$')::cup_size
+ , "desc" = regexp_replace("desc", '[\s]*(-)?[\s]*?cup[\s]*(size)?:[\s]?[A-Z][A]*[.|\s-]?((cup)|([\s]*-->[\s]*[A-Z]))?[\n\r]*', '', 'gi');
+ DELETE FROM edit_chars_traits WHERE tid IN(722, 1182, 1183, 1178, 1184, 723, 2129, 2115);
+ UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic extraction of cup size field from the description.';
+ PERFORM edit_c_commit();
+END;
+$$ LANGUAGE plpgsql;
+
+\timing
+SELECT migrate_desc_to_cup(id) FROM chars WHERE NOT hidden AND cup_size = '' AND "desc" ~* '.*(cup[\s]*(size)?:[\s]*[A-Z]).*';
+\timing
+
+DROP FUNCTION migrate_desc_to_cup(integer);