From 336ad6db0363ce61a62ca902f89694017fd610d3 Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Tue, 10 Sep 2024 19:16:03 +0100
Subject: [PATCH] Add creation_address column to users

This is a properly typed and indexed column that is intended
to replace creation_ip to allow fast queries.
---
 app/controllers/users_controller.rb                   |  1 +
 app/models/user.rb                                    |  2 ++
 .../20240910175616_add_user_creation_address.rb       |  8 ++++++++
 db/structure.sql                                      | 11 ++++++++++-
 4 files changed, 21 insertions(+), 1 deletion(-)
 create mode 100644 db/migrate/20240910175616_add_user_creation_address.rb

diff --git a/app/controllers/users_controller.rb b/app/controllers/users_controller.rb
index 4ebeb1ec3..3ba914210 100644
--- a/app/controllers/users_controller.rb
+++ b/app/controllers/users_controller.rb
@@ -267,6 +267,7 @@ class UsersController < ApplicationController
     current_user.data_public = true
     current_user.description = "" if current_user.description.nil?
     current_user.creation_ip = request.remote_ip
+    current_user.creation_address = request.remote_ip
     current_user.languages = http_accept_language.user_preferred_languages
     current_user.terms_agreed = Time.now.utc
     current_user.tou_agreed = Time.now.utc
diff --git a/app/models/user.rb b/app/models/user.rb
index ceeefd40f..441805f39 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -33,9 +33,11 @@
 #  tou_agreed           :datetime
 #  diary_comments_count :integer          default(0)
 #  note_comments_count  :integer          default(0)
+#  creation_address     :inet
 #
 # Indexes
 #
+#  index_users_on_creation_address   (creation_address) USING gist
 #  users_auth_idx                    (auth_provider,auth_uid) UNIQUE
 #  users_display_name_canonical_idx  (lower(NORMALIZE(display_name, NFKC)))
 #  users_display_name_idx            (display_name) UNIQUE
diff --git a/db/migrate/20240910175616_add_user_creation_address.rb b/db/migrate/20240910175616_add_user_creation_address.rb
new file mode 100644
index 000000000..4d9ba4094
--- /dev/null
+++ b/db/migrate/20240910175616_add_user_creation_address.rb
@@ -0,0 +1,8 @@
+class AddUserCreationAddress < ActiveRecord::Migration[7.1]
+  disable_ddl_transaction!
+
+  def change
+    add_column :users, :creation_address, :inet
+    add_index :users, :creation_address, :using => :gist, :opclass => :inet_ops, :algorithm => :concurrently
+  end
+end
diff --git a/db/structure.sql b/db/structure.sql
index f9e588b3c..9318185c6 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -1499,7 +1499,8 @@ CREATE TABLE public.users (
     home_tile bigint,
     tou_agreed timestamp without time zone,
     diary_comments_count integer DEFAULT 0,
-    note_comments_count integer DEFAULT 0
+    note_comments_count integer DEFAULT 0,
+    creation_address inet
 );
 
 
@@ -2603,6 +2604,13 @@ CREATE INDEX index_user_blocks_on_user_id ON public.user_blocks USING btree (use
 CREATE UNIQUE INDEX index_user_mutes_on_owner_id_and_subject_id ON public.user_mutes USING btree (owner_id, subject_id);
 
 
+--
+-- Name: index_users_on_creation_address; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_users_on_creation_address ON public.users USING gist (creation_address inet_ops);
+
+
 --
 -- Name: messages_from_user_id_idx; Type: INDEX; Schema: public; Owner: -
 --
@@ -3349,6 +3357,7 @@ INSERT INTO "schema_migrations" (version) VALUES
 ('23'),
 ('22'),
 ('21'),
+('20240910175616'),
 ('20240822121603'),
 ('20240813070506'),
 ('20240724194738'),
-- 
2.39.5