Add character cup size field + conversion + filter

1 files changed, 51 insertions, 0 deletions

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);