maintenance/parserTests.sql

   1 -- HACK: this should go away when there is a better way
   2
   3 CREATE TEMPORARY TABLE parsertestuser (
   4   user_id int(5) unsigned NOT NULL auto_increment,
   5   user_name varchar(255) binary NOT NULL default '',
   6   user_real_name varchar(255) binary NOT NULL default '',
   7   user_password tinyblob NOT NULL default '',
   8   user_newpassword tinyblob NOT NULL default '',
   9   user_email tinytext NOT NULL default '',
  10   user_options blob NOT NULL default '',
  11   user_touched char(14) binary NOT NULL default '',
  12   PRIMARY KEY user_id (user_id),
  13   INDEX user_name (user_name(10))
  14 );
  15
  16 CREATE TEMPORARY TABLE parsertestcur (
  17   cur_id int(8) unsigned NOT NULL auto_increment,
  18   cur_namespace tinyint(2) unsigned NOT NULL default '0',
  19   cur_title varchar(255) binary NOT NULL default '',
  20   cur_text mediumtext NOT NULL default '',
  21   cur_comment tinyblob NOT NULL default '',
  22   cur_user int(5) unsigned NOT NULL default '0',
  23   cur_user_text varchar(255) binary NOT NULL default '',
  24   cur_timestamp char(14) binary NOT NULL default '',
  25   cur_restrictions tinyblob NOT NULL default '',
  26   cur_counter bigint(20) unsigned NOT NULL default '0',
  27   cur_is_redirect tinyint(1) unsigned NOT NULL default '0',
  28   cur_minor_edit tinyint(1) unsigned NOT NULL default '0',
  29   cur_is_new tinyint(1) unsigned NOT NULL default '0',
  30   cur_random real unsigned NOT NULL,
  31   cur_touched char(14) binary NOT NULL default '',
  32   inverse_timestamp char(14) binary NOT NULL default '',
  33   PRIMARY KEY cur_id (cur_id),
  34   UNIQUE INDEX name_title (cur_namespace,cur_title),
  35
  36   -- Is this one necessary?
  37   INDEX cur_title (cur_title(20)),
  38
  39   INDEX cur_timestamp (cur_timestamp),
  40   INDEX (cur_random),
  41   INDEX name_title_timestamp (cur_namespace,cur_title,inverse_timestamp),
  42   INDEX user_timestamp (cur_user,inverse_timestamp),
  43   INDEX usertext_timestamp (cur_user_text,inverse_timestamp),
  44   INDEX namespace_redirect_timestamp(cur_namespace,cur_is_redirect,cur_timestamp)
  45 );
  46
  47 CREATE TEMPORARY TABLE parsertestold (
  48   old_id int(8) unsigned NOT NULL auto_increment,
  49   old_namespace tinyint(2) unsigned NOT NULL default '0',
  50   old_title varchar(255) binary NOT NULL default '',
  51   old_text mediumtext NOT NULL default '',
  52   old_comment tinyblob NOT NULL default '',
  53   old_user int(5) unsigned NOT NULL default '0',
  54   old_user_text varchar(255) binary NOT NULL,
  55   old_timestamp char(14) binary NOT NULL default '',
  56   old_minor_edit tinyint(1) NOT NULL default '0',
  57   old_flags tinyblob NOT NULL default '',
  58   inverse_timestamp char(14) binary NOT NULL default '',
  59
  60   PRIMARY KEY old_id (old_id),
  61   INDEX old_timestamp (old_timestamp),
  62   INDEX name_title_timestamp (old_namespace,old_title,inverse_timestamp),
  63   INDEX user_timestamp (old_user,inverse_timestamp),
  64   INDEX usertext_timestamp (old_user_text,inverse_timestamp)
  65 );
  66
  67 CREATE TEMPORARY TABLE parsertestlinks (
  68   l_from int(8) unsigned NOT NULL default '0',
  69   l_to int(8) unsigned NOT NULL default '0',
  70   UNIQUE KEY l_from(l_from,l_to),
  71   KEY (l_to)
  72 );
  73
  74 CREATE TEMPORARY TABLE parsertestbrokenlinks (
  75   bl_from int(8) unsigned NOT NULL default '0',
  76   bl_to varchar(255) binary NOT NULL default '',
  77   UNIQUE KEY bl_from(bl_from,bl_to),
  78   KEY (bl_to)
  79 );
  80
  81 CREATE TEMPORARY TABLE parsertestimagelinks (
  82   il_from int(8) unsigned NOT NULL default '0',
  83   il_to varchar(255) binary NOT NULL default '',
  84   UNIQUE KEY il_from(il_from,il_to),
  85   KEY (il_to)
  86 );
  87
  88 CREATE TEMPORARY TABLE parsertestcategorylinks (
  89   cl_from int(8) unsigned NOT NULL default '0',
  90   cl_to varchar(255) binary NOT NULL default '',
  91   cl_sortkey varchar(255) binary NOT NULL default '',
  92   cl_timestamp timestamp NOT NULL,
  93   UNIQUE KEY cl_from(cl_from,cl_to),
  94   KEY cl_sortkey(cl_to,cl_sortkey(128)),
  95   KEY cl_timestamp(cl_to,cl_timestamp)
  96 );
  97
  98 --
  99 -- Stores (possibly gzipped) serialized objects with
 100 -- cache arrays to reduce database load slurping up
 101 -- from links and brokenlinks.
 102 --
 103 CREATE TEMPORARY TABLE parsertestlinkscc (
 104   lcc_pageid INT UNSIGNED NOT NULL UNIQUE KEY,
 105   lcc_cacheobj MEDIUMBLOB NOT NULL
 106 );
 107
 108 CREATE TEMPORARY TABLE parsertestsite_stats (
 109   ss_row_id int(8) unsigned NOT NULL,
 110   ss_total_views bigint(20) unsigned default '0',
 111   ss_total_edits bigint(20) unsigned default '0',
 112   ss_good_articles bigint(20) unsigned default '0',
 113   UNIQUE KEY ss_row_id (ss_row_id)
 114 );
 115
 116 CREATE TEMPORARY TABLE parsertesthitcounter (
 117   hc_id INTEGER UNSIGNED NOT NULL
 118 ) TYPE=HEAP MAX_ROWS=25000;
 119
 120 CREATE TEMPORARY TABLE parsertestipblocks (
 121   ipb_id int(8) NOT NULL auto_increment,
 122   ipb_address varchar(40) binary NOT NULL default '',
 123   ipb_user int(8) unsigned NOT NULL default '0',
 124   ipb_by int(8) unsigned NOT NULL default '0',
 125   ipb_reason tinyblob NOT NULL default '',
 126   ipb_timestamp char(14) binary NOT NULL default '',
 127   ipb_auto tinyint(1) NOT NULL default '0',
 128   ipb_expiry char(14) binary NOT NULL default '',
 129
 130   PRIMARY KEY ipb_id (ipb_id),
 131   INDEX ipb_address (ipb_address),
 132   INDEX ipb_user (ipb_user)
 133 );
 134
 135 CREATE TEMPORARY TABLE parsertestimage (
 136   img_name varchar(255) binary NOT NULL default '',
 137   img_size int(8) unsigned NOT NULL default '0',
 138   img_description tinyblob NOT NULL default '',
 139   img_user int(5) unsigned NOT NULL default '0',
 140   img_user_text varchar(255) binary NOT NULL default '',
 141   img_timestamp char(14) binary NOT NULL default '',
 142   PRIMARY KEY img_name (img_name),
 143   INDEX img_size (img_size),
 144   INDEX img_timestamp (img_timestamp)
 145 );
 146
 147 CREATE TEMPORARY TABLE parsertestoldimage (
 148   oi_name varchar(255) binary NOT NULL default '',
 149   oi_archive_name varchar(255) binary NOT NULL default '',
 150   oi_size int(8) unsigned NOT NULL default 0,
 151   oi_description tinyblob NOT NULL default '',
 152   oi_user int(5) unsigned NOT NULL default '0',
 153   oi_user_text varchar(255) binary NOT NULL default '',
 154   oi_timestamp char(14) binary NOT NULL default '',
 155   INDEX oi_name (oi_name(10))
 156 );
 157
 158 CREATE TEMPORARY TABLE parsertestrecentchanges (
 159   rc_id int(8) NOT NULL auto_increment,
 160   rc_timestamp varchar(14) binary NOT NULL default '',
 161   rc_cur_time varchar(14) binary NOT NULL default '',
 162   rc_user int(10) unsigned NOT NULL default '0',
 163   rc_user_text varchar(255) binary NOT NULL default '',
 164   rc_namespace tinyint(3) NOT NULL default '0',
 165   rc_title varchar(255) binary NOT NULL default '',
 166   rc_comment varchar(255) binary NOT NULL default '',
 167   rc_minor tinyint(3) unsigned NOT NULL default '0',
 168   rc_bot tinyint(3) unsigned NOT NULL default '0',
 169   rc_new tinyint(3) unsigned NOT NULL default '0',
 170   rc_cur_id int(10) unsigned NOT NULL default '0',
 171   rc_this_oldid int(10) unsigned NOT NULL default '0',
 172   rc_last_oldid int(10) unsigned NOT NULL default '0',
 173   rc_type tinyint(3) unsigned NOT NULL default '0',
 174   rc_moved_to_ns tinyint(3) unsigned NOT NULL default '0',
 175   rc_moved_to_title varchar(255) binary NOT NULL default '',
 176   rc_patrolled tinyint(3) unsigned NOT NULL default '0',
 177   rc_ip char(15) NOT NULL default '',
 178
 179   PRIMARY KEY rc_id (rc_id),
 180   INDEX rc_timestamp (rc_timestamp),
 181   INDEX rc_namespace_title (rc_namespace, rc_title),
 182   INDEX rc_cur_id (rc_cur_id),
 183   INDEX new_name_timestamp(rc_new,rc_namespace,rc_timestamp),
 184   INDEX rc_ip (rc_ip)
 185 );
 186
 187 CREATE TEMPORARY TABLE parsertestwatchlist (
 188   wl_user int(5) unsigned NOT NULL,
 189   wl_namespace tinyint(2) unsigned NOT NULL default '0',
 190   wl_title varchar(255) binary NOT NULL default '',
 191   UNIQUE KEY (wl_user, wl_namespace, wl_title),
 192   KEY namespace_title (wl_namespace,wl_title)
 193 );
 194
 195 CREATE TEMPORARY TABLE parsertestmath (
 196   math_inputhash varchar(16) NOT NULL,
 197   math_outputhash varchar(16) NOT NULL,
 198   math_html_conservativeness tinyint(1) NOT NULL,
 199   math_html text,
 200   math_mathml text,
 201   UNIQUE KEY math_inputhash (math_inputhash)
 202 );
 203
 204
 205 -- Table searchindex must be MyISAM for fulltext support
 206
 207 CREATE TEMPORARY TABLE parsertestsearchindex (
 208   si_page int(8) unsigned NOT NULL,
 209   si_title varchar(255) NOT NULL default '',
 210   si_text mediumtext NOT NULL default '',
 211   UNIQUE KEY (si_page),
 212   FULLTEXT si_title (si_title),
 213   FULLTEXT si_text (si_text)
 214
 215 ) TYPE=MyISAM;
 216
 217 CREATE TEMPORARY TABLE parsertestinterwiki (
 218   iw_prefix char(32) NOT NULL,
 219   iw_url char(127) NOT NULL,
 220   iw_local BOOL NOT NULL,
 221   UNIQUE KEY iw_prefix (iw_prefix)
 222 );
 223
 224 -- Used for caching expensive grouped queries
 225 CREATE TEMPORARY TABLE parsertestquerycache (
 226   qc_type char(32) NOT NULL,
 227   qc_value int(5) unsigned NOT NULL default '0',
 228   qc_namespace tinyint(2) unsigned NOT NULL default '0',
 229   qc_title char(255) binary NOT NULL default '',
 230   KEY (qc_type,qc_value)
 231 );
 232
 233 -- For a few generic cache operations if not using Memcached
 234 CREATE TEMPORARY TABLE parsertestobjectcache (
 235   keyname char(255) binary not null default '',
 236   value mediumblob,
 237   exptime datetime,
 238   unique key (keyname),
 239   key (exptime)
 240 );
 241
 242 -- For storing revision text
 243 CREATE TEMPORARY TABLE parsertestblobs (
 244   blob_index char(255) binary NOT NULL default '',
 245   blob_data longblob NOT NULL default '',
 246   UNIQUE key blob_index (blob_index)
 247 );
 248
 249 -- For article validation
 250
 251 CREATE TEMPORARY TABLE `parsertestvalidate` (
 252   `val_user` int(11) NOT NULL default '0',
 253   `val_title` varchar(255) binary NOT NULL default '',
 254   `val_timestamp` varchar(14) binary NOT NULL default '',
 255   `val_type` int(10) unsigned NOT NULL default '0',
 256   `val_value` int(11) default '0',
 257   `val_comment` varchar(255) NOT NULL default '',
 258   KEY `val_user` (`val_user`,`val_title`,`val_timestamp`)
 259 );
 260
 261
 262 CREATE TEMPORARY TABLE parsertestlogging (
 263   -- Symbolic keys for the general log type and the action type
 264   -- within the log. The output format will be controlled by the
 265   -- action field, but only the type controls categorization.
 266   log_type char(10) NOT NULL default '',
 267   log_action char(10) NOT NULL default '',
 268
 269   -- Timestamp. Duh.
 270   log_timestamp char(14) NOT NULL default '19700101000000',
 271
 272   -- The user who performed this action; key to user_id
 273   log_user int unsigned NOT NULL default 0,
 274
 275   -- Key to the page affected. Where a user is the target,
 276   -- this will point to the user page.
 277   log_namespace tinyint unsigned NOT NULL default 0,
 278   log_title varchar(255) NOT NULL default '',
 279
 280   -- Freeform text. Interpreted as edit history comments.
 281   log_comment varchar(255) NOT NULL default '',
 282
 283   KEY type_time (log_type, log_timestamp),
 284   KEY user_time (log_user, log_timestamp),
 285   KEY page_time (log_namespace, log_title, log_timestamp)
 286 );
 287
 288 INSERT INTO parsertestinterwiki (iw_prefix,iw_url,iw_local) VALUES
 289         ('MeatBall','http://www.usemod.com/cgi-bin/mb.pl?$1',0),
 290         ('es','http://es.wikipedia.org/wiki/$1',1),
 291         ('fr','http://fr.wikipedia.org/wiki/$1',1),
 292         ('zh','http://zh.wikipedia.org/wiki/$1',1);
 293