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.

diff --git a/app/controllers/users_controller.rb b/app/controllers/users_controller.rb
index 4ebeb1ec3a271f9139fb6932b77d00d1a49cb514..3ba914210edd50fbbb4989ae24d65373f8f94804 100644 (file)
--- 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.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
     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 ceeefd40f4e1e746398fd5cba5c065af906db6f7..441805f390e4c4a206aff7bb5bf23ba9bed41173 100644 (file)
--- 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)
 #  tou_agreed           :datetime
 #  diary_comments_count :integer          default(0)
 #  note_comments_count  :integer          default(0)

+#  creation_address     :inet

 #
 # Indexes
 #
 #
 # 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
 #  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 (file)
index 0000000..4d9ba40
--- /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 f9e588b3c2a91d28bca92637e747686ab36c26c9..9318185c6a22980ea96fe6689addd10c7f492f2a 100644 (file)
--- 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,
     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);
 
 
 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: -
 --
 --
 -- Name: messages_from_user_id_idx; Type: INDEX; Schema: public; Owner: -
 --


@@ -3349,6 +3357,7 @@ INSERT INTO "schema_migrations" (version) VALUES
 ('23'),
 ('22'),
 ('21'),
 ('23'),
 ('22'),
 ('21'),

+('20240910175616'),

 ('20240822121603'),
 ('20240813070506'),
 ('20240724194738'),
 ('20240822121603'),
 ('20240813070506'),
 ('20240724194738'),