From: MusikAnimal <musikanimal@gmail.com>
Date: Wed, 9 Aug 2017 21:34:09 +0000 (-0400)
Subject: Create new table to be used for querying IP ranges.
X-Git-Tag: 1.31.0-rc.0~2418^2
X-Git-Url: https://git.heureux-cyclage.org/?p=lhc%2Fweb%2Fwiklou.git;a=commitdiff_plain;h=d779a09035c43785e544279b93242a01960f6c72

Create new table to be used for querying IP ranges.

When revisions by logged out users are created, they will get copied
to this table. We can then JOIN it with revision when querying for
ranges at Special:Contributions.

DBA approval for this table can be found at T156318

Bug: T163562

Change-Id: I6593eb13701128faa782691a6b25ec01869c827d
---

diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php
index b4ae1dd940..58728a3755 100644
--- a/includes/installer/MysqlUpdater.php
+++ b/includes/installer/MysqlUpdater.php
@@ -304,6 +304,7 @@ class MysqlUpdater extends DatabaseUpdater {
 
 			// 1.30
 			[ 'modifyField', 'image', 'img_media_type', 'patch-add-3d.sql' ],
+			[ 'addTable', 'ip_changes', 'patch-ip_changes.sql' ],
 		];
 	}
 
diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php
index 46e3e7e2fa..1e43d3e7a1 100644
--- a/includes/installer/SqliteUpdater.php
+++ b/includes/installer/SqliteUpdater.php
@@ -168,6 +168,7 @@ class SqliteUpdater extends DatabaseUpdater {
 
 			// 1.30
 			[ 'modifyField', 'image', 'img_media_type', 'patch-add-3d.sql' ],
+			[ 'addTable', 'ip_changes', 'patch-ip_changes.sql' ],
 		];
 	}
 
diff --git a/maintenance/archives/patch-ip_changes.sql b/maintenance/archives/patch-ip_changes.sql
new file mode 100644
index 0000000000..5f05672e84
--- /dev/null
+++ b/maintenance/archives/patch-ip_changes.sql
@@ -0,0 +1,23 @@
+--
+-- Every time an edit by a logged out user is saved,
+-- a row is created in ip_changes. This stores
+-- the IP as a hex representation so that we can more
+-- easily find edits within an IP range.
+--
+CREATE TABLE /*_*/ip_changes (
+  -- Foreign key to the revision table, also serves as the unique primary key
+  ipc_rev_id int unsigned NOT NULL PRIMARY KEY DEFAULT '0',
+
+  -- The timestamp of the revision
+  ipc_rev_timestamp binary(14) NOT NULL DEFAULT '',
+
+  -- Hex representation of the IP address, as returned by IP::toHex()
+  -- For IPv4 it will resemble: ABCD1234
+  -- For IPv6: v6-ABCD1234000000000000000000000000
+  -- BETWEEN is then used to identify revisions within a given range
+  ipc_hex varbinary(35) NOT NULL DEFAULT ''
+
+) /*$wgDBTableOptions*/;
+
+CREATE INDEX /*i*/ipc_rev_timestamp ON /*_*/ip_changes (ipc_rev_timestamp);
+CREATE INDEX /*i*/ipc_hex_time ON /*_*/ip_changes (ipc_hex,ipc_rev_timestamp);
diff --git a/maintenance/sqlite/archives/patch-ip_changes.sql b/maintenance/sqlite/archives/patch-ip_changes.sql
new file mode 100644
index 0000000000..5f05672e84
--- /dev/null
+++ b/maintenance/sqlite/archives/patch-ip_changes.sql
@@ -0,0 +1,23 @@
+--
+-- Every time an edit by a logged out user is saved,
+-- a row is created in ip_changes. This stores
+-- the IP as a hex representation so that we can more
+-- easily find edits within an IP range.
+--
+CREATE TABLE /*_*/ip_changes (
+  -- Foreign key to the revision table, also serves as the unique primary key
+  ipc_rev_id int unsigned NOT NULL PRIMARY KEY DEFAULT '0',
+
+  -- The timestamp of the revision
+  ipc_rev_timestamp binary(14) NOT NULL DEFAULT '',
+
+  -- Hex representation of the IP address, as returned by IP::toHex()
+  -- For IPv4 it will resemble: ABCD1234
+  -- For IPv6: v6-ABCD1234000000000000000000000000
+  -- BETWEEN is then used to identify revisions within a given range
+  ipc_hex varbinary(35) NOT NULL DEFAULT ''
+
+) /*$wgDBTableOptions*/;
+
+CREATE INDEX /*i*/ipc_rev_timestamp ON /*_*/ip_changes (ipc_rev_timestamp);
+CREATE INDEX /*i*/ipc_hex_time ON /*_*/ip_changes (ipc_hex,ipc_rev_timestamp);
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index 14f69324f7..1497d6fc23 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -410,6 +410,30 @@ CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timesta
 -- and is a logged-in user.
 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
 
+--
+-- Every time an edit by a logged out user is saved,
+-- a row is created in ip_changes. This stores
+-- the IP as a hex representation so that we can more
+-- easily find edits within an IP range.
+--
+CREATE TABLE /*_*/ip_changes (
+  -- Foreign key to the revision table, also serves as the unique primary key
+  ipc_rev_id int unsigned NOT NULL PRIMARY KEY DEFAULT '0',
+
+  -- The timestamp of the revision
+  ipc_rev_timestamp binary(14) NOT NULL DEFAULT '',
+
+  -- Hex representation of the IP address, as returned by IP::toHex()
+  -- For IPv4 it will resemble: ABCD1234
+  -- For IPv6: v6-ABCD1234000000000000000000000000
+  -- BETWEEN is then used to identify revisions within a given range
+  ipc_hex varbinary(35) NOT NULL DEFAULT ''
+
+) /*$wgDBTableOptions*/;
+
+CREATE INDEX /*i*/ipc_rev_timestamp ON /*_*/ip_changes (ipc_rev_timestamp);
+CREATE INDEX /*i*/ipc_hex_time ON /*_*/ip_changes (ipc_hex,ipc_rev_timestamp);
+
 --
 -- Holds text of individual page revisions.
 --