From b3daafde4a7a1cb4076b9a218e3258d34db313dd Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 24 Aug 2022 14:06:00 +0200 Subject: Use libloc to add IP location information to the DB This should save me considerable time in finding duplicate account voters. --- util/updates/2022-08-24-ipinfo.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 util/updates/2022-08-24-ipinfo.sql (limited to 'util') diff --git a/util/updates/2022-08-24-ipinfo.sql b/util/updates/2022-08-24-ipinfo.sql new file mode 100644 index 00000000..ffa00708 --- /dev/null +++ b/util/updates/2022-08-24-ipinfo.sql @@ -0,0 +1,17 @@ +CREATE TYPE ipinfo AS ( + ip inet, + country text, + asn integer, + as_name text, + anonymous_proxy boolean, + sattelite_provider boolean, + anycast boolean, + drop boolean +); + +ALTER TABLE audit_log ALTER COLUMN by_ip TYPE ipinfo USING ROW(by_ip,null,null,null,null,null,null,null); +ALTER TABLE reports ALTER COLUMN ip TYPE ipinfo USING CASE WHEN ip IS NULL THEN NULL ELSE ROW(ip,null,null,null,null,null,null,null)::ipinfo END; + +ALTER TABLE users_shadow ALTER COLUMN ip DROP DEFAULT; +ALTER TABLE users_shadow ALTER COLUMN ip DROP NOT NULL; +ALTER TABLE users_shadow ALTER COLUMN ip TYPE ipinfo USING CASE WHEN ip = '0.0.0.0' THEN NULL ELSE ROW(ip,null,null,null,null,null,null,null)::ipinfo END; -- cgit v1.2.3