summaryrefslogtreecommitdiff
path: root/maintenance/sqlite
diff options
context:
space:
mode:
Diffstat (limited to 'maintenance/sqlite')
-rw-r--r--maintenance/sqlite/archives/initial-indexes.sql49
-rw-r--r--maintenance/sqlite/archives/patch-archive-ar_id.sql39
-rw-r--r--maintenance/sqlite/archives/patch-externallinks-el_id.sql19
-rw-r--r--maintenance/sqlite/archives/patch-kill-iwl_pft.sql7
-rw-r--r--maintenance/sqlite/archives/patch-rename-iwl_prefix.sql2
5 files changed, 108 insertions, 8 deletions
diff --git a/maintenance/sqlite/archives/initial-indexes.sql b/maintenance/sqlite/archives/initial-indexes.sql
index 73b008cc..1a59be5a 100644
--- a/maintenance/sqlite/archives/initial-indexes.sql
+++ b/maintenance/sqlite/archives/initial-indexes.sql
@@ -28,6 +28,8 @@ DROP TABLE IF EXISTS /*_*/interwiki_tmp;
DROP TABLE IF EXISTS /*_*/page_restrictions_tmp;
DROP TABLE IF EXISTS /*_*/protected_titles_tmp;
DROP TABLE IF EXISTS /*_*/page_props_tmp;
+DROP TABLE IF EXISTS /*_*/archive_tmp;
+DROP TABLE IF EXISTS /*_*/externallinks_tmp;
--------------------------------------------------------------------------------
-- Create new tables
@@ -268,6 +270,47 @@ CREATE TABLE /*_*/page_props_tmp (
);
CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props_tmp (pp_page,pp_propname);
+--
+-- Holding area for deleted articles, which may be viewed
+-- or restored by admins through the Special:Undelete interface.
+-- The fields generally correspond to the page, revision, and text
+-- fields, with several caveats.
+-- Cannot reasonably create views on this table, due to the presence of TEXT
+-- columns.
+CREATE TABLE /*$wgDBprefix*/archive_tmp (
+ ar_id NOT NULL PRIMARY KEY clustered IDENTITY,
+ ar_namespace SMALLINT NOT NULL DEFAULT 0,
+ ar_title NVARCHAR(255) NOT NULL DEFAULT '',
+ ar_text NVARCHAR(MAX) NOT NULL,
+ ar_comment NVARCHAR(255) NOT NULL,
+ ar_user INT NULL REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE SET NULL,
+ ar_user_text NVARCHAR(255) NOT NULL,
+ ar_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
+ ar_minor_edit BIT NOT NULL DEFAULT 0,
+ ar_flags NVARCHAR(255) NOT NULL,
+ ar_rev_id INT,
+ ar_text_id INT,
+ ar_deleted BIT NOT NULL DEFAULT 0,
+ ar_len INT DEFAULT NULL,
+ ar_page_id INT NULL,
+ ar_parent_id INT NULL
+);
+CREATE INDEX /*$wgDBprefix*/ar_name_title_timestamp ON /*$wgDBprefix*/archive_tmp(ar_namespace,ar_title,ar_timestamp);
+CREATE INDEX /*$wgDBprefix*/ar_usertext_timestamp ON /*$wgDBprefix*/archive_tmp(ar_user_text,ar_timestamp);
+CREATE INDEX /*$wgDBprefix*/ar_user_text ON /*$wgDBprefix*/archive_tmp(ar_user_text);
+
+--
+-- Track links to external URLs
+-- IE >= 4 supports no more than 2083 characters in a URL
+CREATE TABLE /*$wgDBprefix*/externallinks_tmp (
+ el_id INT NOT NULL PRIMARY KEY clustered IDENTITY,
+ el_from INT NOT NULL DEFAULT '0',
+ el_to VARCHAR(2083) NOT NULL,
+ el_index VARCHAR(896) NOT NULL,
+);
+-- Maximum key length ON SQL Server is 900 bytes
+CREATE INDEX /*$wgDBprefix*/externallinks_index ON /*$wgDBprefix*/externallinks_tmp(el_index);
+
--------------------------------------------------------------------------------
-- Populate the new tables using INSERT SELECT
--------------------------------------------------------------------------------
@@ -290,6 +333,8 @@ INSERT OR IGNORE INTO /*_*/interwiki_tmp SELECT * FROM /*_*/interwiki;
INSERT OR IGNORE INTO /*_*/page_restrictions_tmp SELECT * FROM /*_*/page_restrictions;
INSERT OR IGNORE INTO /*_*/protected_titles_tmp SELECT * FROM /*_*/protected_titles;
INSERT OR IGNORE INTO /*_*/page_props_tmp SELECT * FROM /*_*/page_props;
+INSERT OR IGNORE INTO /*_*/archive_tmp SELECT * FROM /*_*/archive;
+INSERT OR IGNORE INTO /*_*/externallinks_tmp SELECT * FROM /*_*/externallinks;
--------------------------------------------------------------------------------
-- Do the table renames
@@ -331,6 +376,10 @@ DROP TABLE /*_*/protected_titles;
ALTER TABLE /*_*/protected_titles_tmp RENAME TO /*_*/protected_titles;
DROP TABLE /*_*/page_props;
ALTER TABLE /*_*/page_props_tmp RENAME TO /*_*/page_props;
+DROP TABLE /*_*/archive;
+ALTER TABLE /*_*/archive_tmp RENAME TO /*_*/archive;
+DROP TABLE /*_*/externalllinks;
+ALTER TABLE /*_*/externallinks_tmp RENAME TO /*_*/externallinks;
--------------------------------------------------------------------------------
-- Drop and create tables with unique indexes but no valuable data
diff --git a/maintenance/sqlite/archives/patch-archive-ar_id.sql b/maintenance/sqlite/archives/patch-archive-ar_id.sql
new file mode 100644
index 00000000..00a9b071
--- /dev/null
+++ b/maintenance/sqlite/archives/patch-archive-ar_id.sql
@@ -0,0 +1,39 @@
+DROP TABLE IF EXISTS /*_*/archive_tmp;
+
+CREATE TABLE /*$wgDBprefix*/archive_tmp (
+ ar_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
+ ar_namespace int NOT NULL default 0,
+ ar_title varchar(255) binary NOT NULL default '',
+ ar_text mediumblob NOT NULL,
+ ar_comment tinyblob NOT NULL,
+ ar_user int unsigned NOT NULL default 0,
+ ar_user_text varchar(255) binary NOT NULL,
+ ar_timestamp binary(14) NOT NULL default '',
+ ar_minor_edit tinyint NOT NULL default 0,
+ ar_flags tinyblob NOT NULL,
+ ar_rev_id int unsigned,
+ ar_text_id int unsigned,
+ ar_deleted tinyint unsigned NOT NULL default 0,
+ ar_len int unsigned,
+ ar_page_id int unsigned,
+ ar_parent_id int unsigned default NULL,
+ ar_sha1 varbinary(32) NOT NULL default '',
+ ar_content_model varbinary(32) DEFAULT NULL,
+ ar_content_format varbinary(64) DEFAULT NULL
+);
+
+INSERT OR IGNORE INTO /*_*/archive_tmp (
+ ar_namespace, ar_title, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp,
+ ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id )
+ SELECT
+ ar_namespace, ar_title, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp,
+ ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id
+ FROM /*_*/archive;
+
+DROP TABLE /*_*/archive;
+
+ALTER TABLE /*_*/archive_tmp RENAME TO /*_*/archive;
+
+CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp);
+CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
+CREATE INDEX /*i*/ar_revid ON /*_*/archive (ar_rev_id);
diff --git a/maintenance/sqlite/archives/patch-externallinks-el_id.sql b/maintenance/sqlite/archives/patch-externallinks-el_id.sql
new file mode 100644
index 00000000..0aad4071
--- /dev/null
+++ b/maintenance/sqlite/archives/patch-externallinks-el_id.sql
@@ -0,0 +1,19 @@
+DROP TABLE IF EXISTS /*_*/externallinks_tmp;
+
+CREATE TABLE /*$wgDBprefix*/externallinks_tmp (
+ el_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
+ el_from int unsigned NOT NULL default 0,
+ el_to blob NOT NULL,
+ el_index blob NOT NULL
+);
+
+INSERT OR IGNORE INTO /*_*/externallinks_tmp (el_from, el_to, el_index) SELECT
+ el_from, el_to, el_index FROM /*_*/externallinks;
+
+DROP TABLE /*_*/externallinks;
+
+ALTER TABLE /*_*/externallinks_tmp RENAME TO /*_*/externallinks;
+
+CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from, el_to(40));
+CREATE INDEX /*i*/el_to ON /*_*/externallinks (el_to(60), el_from);
+CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index(60)); \ No newline at end of file
diff --git a/maintenance/sqlite/archives/patch-kill-iwl_pft.sql b/maintenance/sqlite/archives/patch-kill-iwl_pft.sql
deleted file mode 100644
index 8fc4b5cd..00000000
--- a/maintenance/sqlite/archives/patch-kill-iwl_pft.sql
+++ /dev/null
@@ -1,7 +0,0 @@
---
--- Kill the old iwl_prefix_from_title index, which may be present on some
--- installs if they ran update.php between it being added and being renamed
---
-
-DROP INDEX IF EXISTS /*i*/iwl_prefix;
-
diff --git a/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql b/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql
index fd4c9ec7..6d5b1bfa 100644
--- a/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql
+++ b/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql
@@ -2,4 +2,4 @@
-- Recreates the iwl_prefix for the iwlinks table
--
DROP INDEX IF EXISTS /*i*/iwl_prefix;
-CREATE INDEX IF NOT EXISTS /*i*/iwl_prefix_from_title ON /*_*/iwlinks (iwl_prefix, iwl_from, iwl_title);
+CREATE INDEX IF NOT EXISTS /*i*/iwl_prefix_title_from ON /*_*/iwlinks (iwl_prefix, iwl_title, iwl_from);