From 9b2b027ba7bc922bb9150cad19c49feb0c892f9f Mon Sep 17 00:00:00 2001 From: tisane Date: Sat, 27 Oct 2012 22:23:50 -0400 Subject: [PATCH] Add archive, externallinks PK * New fields: ar_id, el_id. el_id is presently not used for anything, but will help with online schema changes. Bug: 15441 Bug: 39675 Change-Id: Ib6b0fc3736d173fa4ba7b786ecfc710b2f4711bb --- RELEASE-NOTES-1.22 | 2 + includes/installer/MysqlUpdater.php | 2 + includes/installer/OracleUpdater.php | 4 +- includes/installer/PostgresUpdater.php | 7 ++- includes/installer/SqliteUpdater.php | 3 +- maintenance/archives/patch-archive-ar_id.sql | 8 +++ .../archives/patch-externallinks-el_id.sql | 8 +++ maintenance/mssql/tables.sql | 2 + .../oracle/archives/patch-archive-ar_id.sql | 6 +++ .../archives/patch-externallinks-el_id.sql | 4 ++ maintenance/oracle/tables.sql | 6 +++ maintenance/postgres/tables.sql | 6 +++ .../sqlite/archives/initial-indexes.sql | 49 +++++++++++++++++++ .../sqlite/archives/patch-archive-ar_id.sql | 39 +++++++++++++++ .../archives/patch-externallinks-el_id.sql | 19 +++++++ maintenance/tables.sql | 8 ++- 16 files changed, 167 insertions(+), 6 deletions(-) create mode 100644 maintenance/archives/patch-archive-ar_id.sql create mode 100644 maintenance/archives/patch-externallinks-el_id.sql create mode 100644 maintenance/oracle/archives/patch-archive-ar_id.sql create mode 100644 maintenance/oracle/archives/patch-externallinks-el_id.sql create mode 100644 maintenance/sqlite/archives/patch-archive-ar_id.sql create mode 100644 maintenance/sqlite/archives/patch-externallinks-el_id.sql diff --git a/RELEASE-NOTES-1.22 b/RELEASE-NOTES-1.22 index afe0270daf..289f008723 100644 --- a/RELEASE-NOTES-1.22 +++ b/RELEASE-NOTES-1.22 @@ -242,6 +242,8 @@ production. * Added $wgExtensionEntryPointListFiles for use in mergeMessageFileList.php. * Added a hook, APIQuerySiteInfoStatisticsInfo, to allow extensions to modify the output of the API query meta=siteinfo&siprop=statistics +* Primary keys have been added to both the archive table and the externallinks + tables. === Bug fixes in 1.22 === * Disable Special:PasswordReset when $wgEnableEmail is false. Previously one diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 2842bba592..b0092be361 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -231,6 +231,8 @@ class MysqlUpdater extends DatabaseUpdater { // 1.22 array( 'doIwlinksIndexNonUnique' ), array( 'addIndex', 'iwlinks', 'iwl_prefix_from_title', 'patch-iwlinks-from-title-index.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), ); } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index f3f86ebcf3..8484189f46 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -73,7 +73,9 @@ class OracleUpdater extends DatabaseUpdater { array( 'addField', 'revision', 'rev_content_format', 'patch-revision-rev_content_format.sql' ), array( 'addField', 'revision', 'rev_content_model', 'patch-revision-rev_content_model.sql' ), array( 'addField', 'archive', 'ar_content_format', 'patch-archive-ar_content_format.sql' ), - array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), + array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), array( 'addField', 'page', 'page_content_model', 'patch-page-page_content_model.sql' ), array( 'dropField', 'site_stats', 'ss_admins', 'patch-ss_admins.sql' ), array( 'dropField', 'recentchanges', 'rc_moved_to_title', 'patch-rc_moved.sql' ), diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 79183da3ca..f0e4aece8c 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -103,8 +103,8 @@ class PostgresUpdater extends DatabaseUpdater { array( 'addPgField', 'archive', 'ar_content_model', 'TEXT' ), array( 'addPgField', 'archive', 'ar_content_format', 'TEXT' ), array( 'addPgField', 'categorylinks', 'cl_sortkey_prefix', "TEXT NOT NULL DEFAULT ''"), - array( 'addPgField', 'categorylinks', 'cl_collation', "TEXT NOT NULL DEFAULT 0"), - array( 'addPgField', 'categorylinks', 'cl_type', "TEXT NOT NULL DEFAULT 'page'"), + array( 'addPgField', 'categorylinks', 'cl_collation', "TEXT NOT NULL DEFAULT 0" ), + array( 'addPgField', 'categorylinks', 'cl_type', "TEXT NOT NULL DEFAULT 'page'" ), array( 'addPgField', 'image', 'img_sha1', "TEXT NOT NULL DEFAULT ''" ), array( 'addPgField', 'ipblocks', 'ipb_allow_usertalk', 'SMALLINT NOT NULL DEFAULT 0' ), array( 'addPgField', 'ipblocks', 'ipb_anon_only', 'SMALLINT NOT NULL DEFAULT 0' ), @@ -159,6 +159,9 @@ class PostgresUpdater extends DatabaseUpdater { array( 'addPgField', 'job', 'job_token', "TEXT NOT NULL DEFAULT ''" ), array( 'addPgField', 'job', 'job_token_timestamp', "TIMESTAMPTZ" ), array( 'addPgField', 'job', 'job_sha1', "TEXT NOT NULL DEFAULT ''" ), + array( 'addPgField', 'archive', 'ar_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq')" ), + array( 'addPgField', 'externallinks', 'el_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq')" ), + # type changes array( 'changeField', 'archive', 'ar_deleted', 'smallint', '' ), diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index df69c0e051..cfefe099ae 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -95,7 +95,6 @@ class SqliteUpdater extends DatabaseUpdater { array( 'addField', 'archive', 'ar_content_format', 'patch-archive-ar_content_format.sql' ), array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), array( 'addField', 'page', 'page_content_model', 'patch-page-page_content_model.sql' ), - array( 'dropField', 'site_stats', 'ss_admins', 'patch-drop-ss_admins.sql' ), array( 'dropField', 'recentchanges', 'rc_moved_to_title', 'patch-rc_moved.sql' ), array( 'addTable', 'sites', 'patch-sites.sql' ), @@ -109,6 +108,8 @@ class SqliteUpdater extends DatabaseUpdater { array( 'addIndex', 'page_props', 'pp_propname_page', 'patch-page_props-propname-page-index.sql' ), array( 'addIndex', 'image', 'img_media_mime', 'patch-img_media_mime-index.sql' ), array( 'addIndex', 'iwlinks', 'iwl_prefix_from_title', 'patch-iwlinks-from-title-index.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), ); } diff --git a/maintenance/archives/patch-archive-ar_id.sql b/maintenance/archives/patch-archive-ar_id.sql new file mode 100644 index 0000000000..ddd1d7b482 --- /dev/null +++ b/maintenance/archives/patch-archive-ar_id.sql @@ -0,0 +1,8 @@ +-- +-- patch-archive-ar_id.sql +-- +-- Bug 39675. Add archive.ar_id. + +ALTER TABLE /*$wgDBprefix*/archive + ADD COLUMN ar_id int unsigned NOT NULL AUTO_INCREMENT FIRST, + ADD PRIMARY KEY (ar_id); diff --git a/maintenance/archives/patch-externallinks-el_id.sql b/maintenance/archives/patch-externallinks-el_id.sql new file mode 100644 index 0000000000..d4b51b5175 --- /dev/null +++ b/maintenance/archives/patch-externallinks-el_id.sql @@ -0,0 +1,8 @@ +-- +-- patch-extenallinks-el_id.sql +-- +-- Bug 15441. Add externallinks.el_id. + +ALTER TABLE /*$wgDBprefix*/externallinks + ADD COLUMN el_id int unsigned NOT NULL AUTO_INCREMENT FIRST, + ADD PRIMARY KEY (el_id); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index c474f004e5..7356c38f75 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -159,6 +159,7 @@ CREATE TABLE /*$wgDBprefix*/text ( -- Cannot reasonably create views on this table, due to the presence of TEXT -- columns. CREATE TABLE /*$wgDBprefix*/archive ( + 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, @@ -298,6 +299,7 @@ CREATE INDEX /*$wgDBprefix*/lc_lang_key ON /*$wgDBprefix*/l10n_cache (lc_lang, l -- Track links to external URLs -- IE >= 4 supports no more than 2083 characters in a URL CREATE TABLE /*$wgDBprefix*/externallinks ( + 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, diff --git a/maintenance/oracle/archives/patch-archive-ar_id.sql b/maintenance/oracle/archives/patch-archive-ar_id.sql new file mode 100644 index 0000000000..a43f76024b --- /dev/null +++ b/maintenance/oracle/archives/patch-archive-ar_id.sql @@ -0,0 +1,6 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.archive ADD ( +ar_id NUMBER NOT NULL, +); +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_pk PRIMARY KEY (ar_id); diff --git a/maintenance/oracle/archives/patch-externallinks-el_id.sql b/maintenance/oracle/archives/patch-externallinks-el_id.sql new file mode 100644 index 0000000000..a8c443f42f --- /dev/null +++ b/maintenance/oracle/archives/patch-externallinks-el_id.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.externallinks ADD el_id NUMBER NOT NULL; +ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id); \ No newline at end of file diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 57b6e7edab..acfabc339a 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -129,7 +129,9 @@ CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text' ); ALTER TABLE &mw_prefix.pagecontent ADD CONSTRAINT &mw_prefix.pagecontent_pk PRIMARY KEY (old_id); +CREATE SEQUENCE archive_ar_id_seq; CREATE TABLE &mw_prefix.archive ( + ar_id NUMBER NOT NULL, ar_namespace NUMBER DEFAULT 0 NOT NULL, ar_title VARCHAR2(255) NOT NULL, ar_text CLOB, @@ -149,6 +151,7 @@ CREATE TABLE &mw_prefix.archive ( ar_content_model VARCHAR2(32), ar_content_format VARCHAR2(64) ); +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_pk PRIMARY KEY (ar_id); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk1 FOREIGN KEY (ar_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX &mw_prefix.archive_i02 ON &mw_prefix.archive (ar_user_text,ar_timestamp); @@ -208,11 +211,14 @@ ALTER TABLE &mw_prefix.category ADD CONSTRAINT &mw_prefix.category_pk PRIMARY KE CREATE UNIQUE INDEX &mw_prefix.category_u01 ON &mw_prefix.category (cat_title); CREATE INDEX &mw_prefix.category_i01 ON &mw_prefix.category (cat_pages); +CREATE SEQUENCE externallinks_el_id_seq; CREATE TABLE &mw_prefix.externallinks ( + el_id NUMBER NOT NULL, el_from NUMBER NOT NULL, el_to VARCHAR2(2048) NOT NULL, el_index VARCHAR2(2048) NOT NULL ); +ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id); ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_fk1 FOREIGN KEY (el_from) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.externallinks_i01 ON &mw_prefix.externallinks (el_from, el_to); CREATE INDEX &mw_prefix.externallinks_i02 ON &mw_prefix.externallinks (el_to, el_from); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 766fc1f9ef..bc2428e4da 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -18,6 +18,8 @@ DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE; DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE; DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE; DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE; +DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE; +DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE; DROP FUNCTION IF EXISTS page_deleted() CASCADE; DROP FUNCTION IF EXISTS ts2_page_title() CASCADE; DROP FUNCTION IF EXISTS ts2_page_text() CASCADE; @@ -156,7 +158,9 @@ ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_prop CREATE INDEX page_props_propname ON page_props (pp_propname); CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page); +CREATE SEQUENCE archive_ar_id_seq; CREATE TABLE archive ( + ar_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'), ar_namespace SMALLINT NOT NULL, ar_title TEXT NOT NULL, ar_text TEXT, -- technically should be bytea, but not used anymore @@ -224,7 +228,9 @@ CREATE TABLE categorylinks ( CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from); +CREATE SEQUENCE externallinks_id_seq; CREATE TABLE externallinks ( + el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_id_seq'), el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, el_to TEXT NOT NULL, el_index TEXT NOT NULL diff --git a/maintenance/sqlite/archives/initial-indexes.sql b/maintenance/sqlite/archives/initial-indexes.sql index 73b008cce8..1a59be5aeb 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 0000000000..00a9b071e8 --- /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 0000000000..0aad40715a --- /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/tables.sql b/maintenance/tables.sql index d37ca47b66..de92ef53e9 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -380,6 +380,8 @@ CREATE TABLE /*_*/text ( -- fields, with several caveats. -- CREATE TABLE /*_*/archive ( + -- Primary key + 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 '', @@ -445,7 +447,6 @@ CREATE TABLE /*_*/archive ( -- content format, see CONTENT_FORMAT_XXX constants ar_content_format varbinary(64) DEFAULT NULL - ) /*$wgDBTableOptions*/; CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp); @@ -602,6 +603,9 @@ CREATE INDEX /*i*/cat_pages ON /*_*/category (cat_pages); -- Track links to external URLs -- CREATE TABLE /*_*/externallinks ( + -- Primary key + el_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + -- page_id of the referring page el_from int unsigned NOT NULL default 0, @@ -1076,7 +1080,7 @@ CREATE TABLE /*_*/recentchanges ( -- Visibility of recent changes items, bitfield rc_deleted tinyint unsigned NOT NULL default 0, - -- Value corresonding to log_id, specific log entries + -- Value corresponding to log_id, specific log entries rc_logid int unsigned NOT NULL default 0, -- Store log type info here, or null rc_log_type varbinary(255) NULL default NULL, -- 2.20.1