Use libloc to add IP location information to the DB

This should save me considerable time in finding duplicate account
voters.

1 files changed, 17 insertions, 0 deletions

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;