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 b4ae1dd..58728a3 100644 (file)
--- 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 46e3e7e..1e43d3e 100644 (file)
--- 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 (file)
index 0000000..5f05672
--- /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 (file)
index 0000000..5f05672
--- /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 14f6932..1497d6f 100644 (file)
--- 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.
 --