diff options
Diffstat (limited to 'maintenance/sqlite')
14 files changed, 277 insertions, 18 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-drop-ss_admins.sql b/maintenance/sqlite/archives/patch-drop-ss_admins.sql new file mode 100644 index 00000000..9951e17e --- /dev/null +++ b/maintenance/sqlite/archives/patch-drop-ss_admins.sql @@ -0,0 +1,22 @@ +-- field is deprecated and no longer updated as of 1.5 +CREATE TABLE /*_*/site_stats_tmp ( + ss_row_id int unsigned NOT NULL, + ss_total_views bigint unsigned default 0, + ss_total_edits bigint unsigned default 0, + ss_good_articles bigint unsigned default 0, + ss_total_pages bigint default '-1', + ss_users bigint default '-1', + ss_active_users bigint default '-1', + ss_images int default 0 +) /*$wgDBTableOptions*/; + +INSERT INTO /*_*/site_stats_tmp + SELECT ss_row_id, ss_total_views, ss_total_edits, ss_good_articles, + ss_total_pages, ss_users, ss_active_users, ss_images + FROM /*_*/site_stats; + +DROP TABLE /*_*/site_stats; + +ALTER TABLE /*_*/site_stats_tmp RENAME TO /*_*/site_stats; + +CREATE UNIQUE INDEX /*i*/ss_row_id ON /*_*/site_stats (ss_row_id);
\ No newline at end of file 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-job_token.sql b/maintenance/sqlite/archives/patch-job_token.sql new file mode 100644 index 00000000..4e4d28fd --- /dev/null +++ b/maintenance/sqlite/archives/patch-job_token.sql @@ -0,0 +1,8 @@ +ALTER TABLE /*_*/job ADD COLUMN job_random integer unsigned NOT NULL default 0; +ALTER TABLE /*_*/job ADD COLUMN job_token varbinary(32) NOT NULL default ''; +ALTER TABLE /*_*/job ADD COLUMN job_sha1 varbinary(32) NOT NULL default ''; +ALTER TABLE /*_*/job ADD COLUMN job_token_timestamp varbinary(14) NULL default NULL; + +CREATE INDEX /*i*/job_sha1 ON /*_*/job (job_sha1); +CREATE INDEX /*i*/job_cmd_token ON /*_*/job (job_cmd,job_token,job_random); + 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-profiling.sql b/maintenance/sqlite/archives/patch-profiling.sql new file mode 100644 index 00000000..4a07283c --- /dev/null +++ b/maintenance/sqlite/archives/patch-profiling.sql @@ -0,0 +1,12 @@ +-- profiling table +-- This is optional + +CREATE TABLE /*_*/profiling ( + pf_count int NOT NULL default 0, + pf_time float NOT NULL default 0, + pf_memory float NOT NULL default 0, + pf_name varchar(255) NOT NULL default '', + pf_server varchar(30) NOT NULL default '' +); + +CREATE UNIQUE INDEX /*i*/pf_name_server ON /*_*/profiling (pf_name, pf_server); diff --git a/maintenance/sqlite/archives/patch-rc_moved.sql b/maintenance/sqlite/archives/patch-rc_moved.sql new file mode 100644 index 00000000..70248d54 --- /dev/null +++ b/maintenance/sqlite/archives/patch-rc_moved.sql @@ -0,0 +1,46 @@ +-- rc_moved_to_ns and rc_moved_to_title is no longer used, delete the fields + +CREATE TABLE /*_*/recentchanges_tmp ( + rc_id int NOT NULL PRIMARY KEY AUTO_INCREMENT, + rc_timestamp varbinary(14) NOT NULL default '', + rc_cur_time varbinary(14) NOT NULL default '', + rc_user int unsigned NOT NULL default 0, + rc_user_text varchar(255) binary NOT NULL, + rc_namespace int NOT NULL default 0, + rc_title varchar(255) binary NOT NULL default '', + rc_comment varchar(255) binary NOT NULL default '', + rc_minor tinyint unsigned NOT NULL default 0, + rc_bot tinyint unsigned NOT NULL default 0, + rc_new tinyint unsigned NOT NULL default 0, + rc_cur_id int unsigned NOT NULL default 0, + rc_this_oldid int unsigned NOT NULL default 0, + rc_last_oldid int unsigned NOT NULL default 0, + rc_type tinyint unsigned NOT NULL default 0, + rc_patrolled tinyint unsigned NOT NULL default 0, + rc_ip varbinary(40) NOT NULL default '', + rc_old_len int, + rc_new_len int, + rc_deleted tinyint unsigned NOT NULL default 0, + rc_logid int unsigned NOT NULL default 0, + rc_log_type varbinary(255) NULL default NULL, + rc_log_action varbinary(255) NULL default NULL, + rc_params blob NULL +) /*$wgDBTableOptions*/; + +INSERT INTO /*_*/recentchanges_tmp + SELECT rc_id, rc_timestamp, rc_cur_time, rc_user, rc_user_text, rc_namespace, rc_title, rc_comment, + rc_minor, rc_bot, rc_new, rc_cur_id, rc_this_oldid, rc_last_oldid, rc_type, rc_patrolled, rc_ip, + rc_old_len, rc_new_len, rc_deleted, rc_logid, rc_log_type, rc_log_action, rc_params + FROM /*_*/recentchanges; + +DROP TABLE /*_*/recentchanges; + +ALTER TABLE /*_*/recentchanges_tmp RENAME TO /*_*/recentchanges; + +CREATE INDEX /*i*/rc_timestamp ON /*_*/recentchanges (rc_timestamp); +CREATE INDEX /*i*/rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title); +CREATE INDEX /*i*/rc_cur_id ON /*_*/recentchanges (rc_cur_id); +CREATE INDEX /*i*/new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp); +CREATE INDEX /*i*/rc_ip ON /*_*/recentchanges (rc_ip); +CREATE INDEX /*i*/rc_ns_usertext ON /*_*/recentchanges (rc_namespace, rc_user_text); +CREATE INDEX /*i*/rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp); diff --git a/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql b/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql index 851a6b37..6d5b1bfa 100644 --- a/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql +++ b/maintenance/sqlite/archives/patch-rename-iwl_prefix.sql @@ -1,5 +1,5 @@ --- +-- -- 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); diff --git a/maintenance/sqlite/archives/patch-sites.sql b/maintenance/sqlite/archives/patch-sites.sql new file mode 100644 index 00000000..88392748 --- /dev/null +++ b/maintenance/sqlite/archives/patch-sites.sql @@ -0,0 +1,71 @@ +-- Patch to add the sites and site_identifiers tables. +-- Licence: GNU GPL v2+ +-- Author: Jeroen De Dauw < jeroendedauw@gmail.com > + + +-- Holds all the sites known to the wiki. +CREATE TABLE IF NOT EXISTS /*_*/sites ( +-- Numeric id of the site + site_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, + + -- Global identifier for the site, ie 'enwiktionary' + site_global_key varbinary(32) NOT NULL, + + -- Type of the site, ie 'mediawiki' + site_type varbinary(32) NOT NULL, + + -- Group of the site, ie 'wikipedia' + site_group varbinary(32) NOT NULL, + + -- Source of the site data, ie 'local', 'wikidata', 'my-magical-repo' + site_source varbinary(32) NOT NULL, + + -- Language code of the sites primary language. + site_language varbinary(32) NOT NULL, + + -- Protocol of the site, ie 'http://', 'irc://', '//' + -- This field is an index for lookups and is build from type specific data in site_data. + site_protocol varbinary(32) NOT NULL, + + -- Domain of the site in reverse order, ie 'org.mediawiki.www.' + -- This field is an index for lookups and is build from type specific data in site_data. + site_domain VARCHAR(255) NOT NULL, + + -- Type dependent site data. + site_data BLOB NOT NULL, + + -- If site.tld/path/key:pageTitle should forward users to the page on + -- the actual site, where "key" is the local identifier. + site_forward bool NOT NULL, + + -- Type dependent site config. + -- For instance if template transclusion should be allowed if it's a MediaWiki. + site_config BLOB NOT NULL +) /*$wgDBTableOptions*/; + +CREATE UNIQUE INDEX /*i*/sites_global_key ON /*_*/sites (site_global_key); +CREATE INDEX /*i*/sites_type ON /*_*/sites (site_type); +CREATE INDEX /*i*/sites_group ON /*_*/sites (site_group); +CREATE INDEX /*i*/sites_source ON /*_*/sites (site_source); +CREATE INDEX /*i*/sites_language ON /*_*/sites (site_language); +CREATE INDEX /*i*/sites_protocol ON /*_*/sites (site_protocol); +CREATE INDEX /*i*/sites_domain ON /*_*/sites (site_domain); +CREATE INDEX /*i*/sites_forward ON /*_*/sites (site_forward); + + + +-- Links local site identifiers to their corresponding site. +CREATE TABLE IF NOT EXISTS /*_*/site_identifiers ( + -- Key on site.site_id + si_site INT UNSIGNED NOT NULL, + + -- local key type, ie 'interwiki' or 'langlink' + si_type varbinary(32) NOT NULL, + + -- local key value, ie 'en' or 'wiktionary' + si_key varbinary(32) NOT NULL +) /*$wgDBTableOptions*/; + +CREATE UNIQUE INDEX /*i*/site_ids_type ON /*_*/site_identifiers (si_type, si_key); +CREATE INDEX /*i*/site_ids_site ON /*_*/site_identifiers (si_site); +CREATE INDEX /*i*/site_ids_key ON /*_*/site_identifiers (si_key);
\ No newline at end of file diff --git a/maintenance/sqlite/archives/patch-ufg_group-length-increase.sql b/maintenance/sqlite/archives/patch-ufg_group-length-increase-255.sql index c6dcea5e..edd0a3dc 100644 --- a/maintenance/sqlite/archives/patch-ufg_group-length-increase.sql +++ b/maintenance/sqlite/archives/patch-ufg_group-length-increase-255.sql @@ -1,11 +1,11 @@ -CREATE TABLE /*_*/user_former_groups_tmp ( - ug_user int unsigned NOT NULL default 0, - ug_group varbinary(32) NOT NULL default '' + CREATE TABLE /*_*/user_former_groups_tmp ( + ufg_user int unsigned NOT NULL default 0, + ufg_group varbinary(255) NOT NULL default '' ) /*$wgDBTableOptions*/; INSERT INTO /*_*/user_former_groups_tmp - SELECT ug_user, ug_group - FROM /*_*/user_groups; + SELECT ufg_user, ufg_group + FROM /*_*/user_former_groups; DROP TABLE /*_*/user_former_groups; diff --git a/maintenance/sqlite/archives/patch-ug_group-length-increase.sql b/maintenance/sqlite/archives/patch-ug_group-length-increase-255.sql index 5e810937..3daeb7c6 100644 --- a/maintenance/sqlite/archives/patch-ug_group-length-increase.sql +++ b/maintenance/sqlite/archives/patch-ug_group-length-increase-255.sql @@ -1,6 +1,6 @@ CREATE TABLE /*_*/user_groups_tmp ( ug_user int unsigned NOT NULL default 0, - ug_group varbinary(32) NOT NULL default '' + ug_group varbinary(255) NOT NULL default '' ) /*$wgDBTableOptions*/; INSERT INTO /*_*/user_groups_tmp diff --git a/maintenance/sqlite/archives/searchindex-fts3.sql b/maintenance/sqlite/archives/searchindex-fts3.sql index 28554c02..2a370940 100644 --- a/maintenance/sqlite/archives/searchindex-fts3.sql +++ b/maintenance/sqlite/archives/searchindex-fts3.sql @@ -1,7 +1,7 @@ -- Patch that introduces fulltext search capabilities to SQLite schema -- Requires that SQLite must be compiled with FTS3 module (comes with core amalgamation). -- See http://sqlite.org/fts3.html for details of syntax. --- Will fail if FTS3 is not present, +-- Will fail if FTS3 is not present, DROP TABLE IF EXISTS /*_*/searchindex; CREATE VIRTUAL TABLE /*_*/searchindex USING FTS3( -- Key to page_id @@ -10,7 +10,7 @@ CREATE VIRTUAL TABLE /*_*/searchindex USING FTS3( -- Munged version of title si_title, - + -- Munged version of body text si_text ); diff --git a/maintenance/sqlite/archives/searchindex-no-fts.sql b/maintenance/sqlite/archives/searchindex-no-fts.sql index bc014b3d..16247ffe 100644 --- a/maintenance/sqlite/archives/searchindex-no-fts.sql +++ b/maintenance/sqlite/archives/searchindex-no-fts.sql @@ -17,7 +17,7 @@ CREATE TABLE /*_*/searchindex ( -- Munged version of title si_title TEXT, - + -- Munged version of body text si_text TEXT ); |