X-Git-Url: https://git.cyclocoop.org/?a=blobdiff_plain;f=maintenance%2Ftables.sql;h=b726f75794b1b9af3784b01aee676940693df7be;hb=0d2104a5e66a608368ff3e979d1bfa37122cc3e4;hp=8a9d79a729a8f9a84ace849eacdd7acf98773d2b;hpb=3c969496827a4ea71d87e4f535a664b15d68b84f;p=lhc%2Fweb%2Fwiklou.git

diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index 8a9d79a729..b726f75794 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -2,33 +2,26 @@
 -- This is read and executed by the install script; you should
 -- never have to run it by itself.
 --
--- Only UNIQUE keys are defined here; the rest are added by
--- indexes.sql.
---
--- If you change the main development branch version of this 
--- file, please add an appropriate ALTER TABLE to update.php, 
--- and increment the version number in Version.php.
+-- Indexes should be defined here; please import the rest from indexes.sql.
 
-DROP TABLE IF EXISTS user;
 CREATE TABLE user (
   user_id int(5) unsigned NOT NULL auto_increment,
   user_name varchar(255) binary NOT NULL default '',
+  user_real_name varchar(255) binary NOT NULL default '',
   user_rights tinyblob NOT NULL default '',
   user_password tinyblob NOT NULL default '',
   user_newpassword tinyblob NOT NULL default '',
   user_email tinytext NOT NULL default '',
-  user_options blob NOT NULL default '',  
+  user_options blob NOT NULL default '',
   user_touched char(14) binary NOT NULL default '',
   UNIQUE KEY user_id (user_id)
-) TYPE=MyISAM PACK_KEYS=1;
-	
-DROP TABLE IF EXISTS user_newtalk;
+) PACK_KEYS=1;
+
 CREATE TABLE user_newtalk (
   user_id int(5) NOT NULL default '0',
   user_ip varchar(40) NOT NULL default ''
-) TYPE=MyISAM;
+);
 
-DROP TABLE IF EXISTS cur;
 CREATE TABLE cur (
   cur_id int(8) unsigned NOT NULL auto_increment,
   cur_namespace tinyint(2) unsigned NOT NULL default '0',
@@ -47,9 +40,8 @@ CREATE TABLE cur (
   cur_touched char(14) binary NOT NULL default '',
   inverse_timestamp char(14) binary NOT NULL default '',
   UNIQUE KEY cur_id (cur_id)
-) TYPE=MyISAM PACK_KEYS=1;
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS old;
 CREATE TABLE old (
   old_id int(8) unsigned NOT NULL auto_increment,
   old_namespace tinyint(2) unsigned NOT NULL default '0',
@@ -63,9 +55,8 @@ CREATE TABLE old (
   old_flags tinyblob NOT NULL default '',
   inverse_timestamp char(14) binary NOT NULL default '',
   UNIQUE KEY old_id (old_id)
-) TYPE=MyISAM PACK_KEYS=1;
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS archive;
 CREATE TABLE archive (
   ar_namespace tinyint(2) unsigned NOT NULL default '0',
   ar_title varchar(255) binary NOT NULL default '',
@@ -76,48 +67,81 @@ CREATE TABLE archive (
   ar_timestamp char(14) binary NOT NULL default '',
   ar_minor_edit tinyint(1) NOT NULL default '0',
   ar_flags tinyblob NOT NULL default ''
-) TYPE=MyISAM PACK_KEYS=1;
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS links;
+--
+-- Track links that do exist
+-- l_from and l_to key to cur_id
+--
 CREATE TABLE links (
-  l_from varchar(255) binary NOT NULL default '',
-  l_to int(8) unsigned NOT NULL default '0'
-) TYPE=MyISAM;
+  l_from int(8) unsigned NOT NULL default '0',
+  l_to int(8) unsigned NOT NULL default '0',
+  UNIQUE KEY l_from(l_from,l_to),
+  KEY (l_to)
+);
 
-DROP TABLE IF EXISTS brokenlinks;
+--
+-- Track links to pages that don't yet exist.
+-- bl_from keys to cur_id
+-- bl_to is a text link (namespace:title)
+--
 CREATE TABLE brokenlinks (
   bl_from int(8) unsigned NOT NULL default '0',
-  bl_to varchar(255) binary NOT NULL default ''
-) TYPE=MyISAM;
+  bl_to varchar(255) binary NOT NULL default '',
+  UNIQUE KEY bl_from(bl_from,bl_to),
+  KEY (bl_to)
+);
+
+--
+-- Track links to images *used inline*
+-- il_from keys to cur_id, il_to keys to image_name.
+-- We don't distinguish live from broken links.
+--
+CREATE TABLE imagelinks (
+  il_from int(8) unsigned NOT NULL default '0',
+  il_to varchar(255) binary NOT NULL default '',
+  UNIQUE KEY il_from(il_from,il_to),
+  KEY (il_to)
+);
+
+--
+-- Track category inclusions *used inline*
+-- cl_from keys to cur_id, cl_to keys to cur_title of the category page.
+-- cl_sortkey is the title of the linking page or an optional override
+-- cl_timestamp marks when the link was last added
+--
+CREATE TABLE categorylinks (
+  cl_from int(8) unsigned NOT NULL default '0',
+  cl_to varchar(255) binary NOT NULL default '',
+  cl_sortkey varchar(255) binary NOT NULL default '',
+  cl_timestamp timestamp NOT NULL,
+  UNIQUE KEY cl_from(cl_from,cl_to),
+  KEY cl_sortkey(cl_to,cl_sortkey(128)),
+  KEY cl_timestamp(cl_to,cl_timestamp)
+);
 
-DROP TABLE IF EXISTS linkscc;
+--
+-- Stores (possibly gzipped) serialized objects with
+-- cache arrays to reduce database load slurping up
+-- from links and brokenlinks.
+--
 CREATE TABLE linkscc (
   lcc_pageid INT UNSIGNED NOT NULL UNIQUE KEY,
-  lcc_title VARCHAR(255) NOT NULL UNIQUE KEY,
   lcc_cacheobj MEDIUMBLOB NOT NULL
-) TYPE=MyISAM;
-
-DROP TABLE IF EXISTS imagelinks;
-CREATE TABLE imagelinks (
-  il_from varchar(255) binary NOT NULL default '',
-  il_to varchar(255) binary NOT NULL default ''
-) TYPE=MyISAM;
+);
 
-DROP TABLE IF EXISTS site_stats;
 CREATE TABLE site_stats (
   ss_row_id int(8) unsigned NOT NULL,
   ss_total_views bigint(20) unsigned default '0',
   ss_total_edits bigint(20) unsigned default '0',
   ss_good_articles bigint(20) unsigned default '0',
   UNIQUE KEY ss_row_id (ss_row_id)
-) TYPE=MyISAM;
+);
 
-DROP TABLE IF EXISTS hitcounter;
 CREATE TABLE hitcounter (
   hc_id INTEGER UNSIGNED NOT NULL
 ) TYPE=HEAP MAX_ROWS=25000;
 
-DROP TABLE IF EXISTS ipblocks;
 CREATE TABLE ipblocks (
   ipb_id int(8) NOT NULL auto_increment,
   ipb_address varchar(40) binary NOT NULL default '',
@@ -126,20 +150,20 @@ CREATE TABLE ipblocks (
   ipb_reason tinyblob NOT NULL default '',
   ipb_timestamp char(14) binary NOT NULL default '',
   ipb_auto tinyint(1) NOT NULL default '0',
+  ipb_expiry char(14) binary NOT NULL default '',
   UNIQUE KEY ipb_id (ipb_id)
-) TYPE=MyISAM PACK_KEYS=1;
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS image;
 CREATE TABLE image (
   img_name varchar(255) binary NOT NULL default '',
   img_size int(8) unsigned NOT NULL default '0',
   img_description tinyblob NOT NULL default '',
   img_user int(5) unsigned NOT NULL default '0',
   img_user_text varchar(255) binary NOT NULL default '',
-  img_timestamp char(14) binary NOT NULL default ''
-) TYPE=MyISAM PACK_KEYS=1;
+  img_timestamp char(14) binary NOT NULL default '',
+  UNIQUE KEY img_name (img_name)
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS oldimage;
 CREATE TABLE oldimage (
   oi_name varchar(255) binary NOT NULL default '',
   oi_archive_name varchar(255) binary NOT NULL default '',
@@ -148,10 +172,10 @@ CREATE TABLE oldimage (
   oi_user int(5) unsigned NOT NULL default '0',
   oi_user_text varchar(255) binary NOT NULL default '',
   oi_timestamp char(14) binary NOT NULL default ''
-) TYPE=MyISAM PACK_KEYS=1;
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS recentchanges;
 CREATE TABLE recentchanges (
+  rc_id int(8) NOT NULL auto_increment,
   rc_timestamp varchar(14) binary NOT NULL default '',
   rc_cur_time varchar(14) binary NOT NULL default '',
   rc_user int(10) unsigned NOT NULL default '0',
@@ -168,17 +192,18 @@ CREATE TABLE recentchanges (
   rc_type tinyint(3) unsigned NOT NULL default '0',
   rc_moved_to_ns tinyint(3) unsigned NOT NULL default '0',
   rc_moved_to_title varchar(255) binary NOT NULL default '',
-) TYPE=MyISAM PACK_KEYS=1;
+  rc_patrolled tinyint(3) unsigned NOT NULL default '0',
+  rc_ip char(15) NOT NULL default '',
+  PRIMARY KEY rc_id (rc_id)
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS watchlist;
 CREATE TABLE watchlist (
   wl_user int(5) unsigned NOT NULL,
   wl_namespace tinyint(2) unsigned NOT NULL default '0',
   wl_title varchar(255) binary NOT NULL default '',
   UNIQUE KEY (wl_user, wl_namespace, wl_title)
-) TYPE=MyISAM PACK_KEYS=1;
+) PACK_KEYS=1;
 
-DROP TABLE IF EXISTS math;
 CREATE TABLE math (
   math_inputhash varchar(16) NOT NULL,
   math_outputhash varchar(16) NOT NULL,
@@ -186,12 +211,11 @@ CREATE TABLE math (
   math_html text,
   math_mathml text,
   UNIQUE KEY math_inputhash (math_inputhash)
-) TYPE=MyISAM;
+);
 
 
 -- Table searchindex must be MyISAM for fulltext support
 
-DROP TABLE IF EXISTS searchindex;
 CREATE TABLE searchindex (
   si_page int(8) unsigned NOT NULL,
   si_title varchar(255) NOT NULL default '',
@@ -199,7 +223,6 @@ CREATE TABLE searchindex (
   UNIQUE KEY (si_page)
 ) TYPE=MyISAM PACK_KEYS=1;
 
-DROP TABLE IF EXISTS interwiki;
 CREATE TABLE interwiki (
   iw_prefix char(32) NOT NULL,
   iw_url char(127) NOT NULL,
@@ -207,3 +230,39 @@ CREATE TABLE interwiki (
   UNIQUE KEY iw_prefix (iw_prefix)
 );
 
+-- Used for caching expensive grouped queries
+CREATE TABLE querycache (
+  qc_type char(32) NOT NULL,
+  qc_value int(5) unsigned NOT NULL default '0',
+  qc_namespace tinyint(2) unsigned NOT NULL default '0',
+  qc_title char(255) binary NOT NULL default '',
+  KEY (qc_type,qc_value)
+);
+
+-- For a few generic cache operations if not using Memcached
+CREATE TABLE objectcache (
+  keyname char(255) binary not null default '',
+  value mediumblob,
+  exptime datetime,
+  unique key (keyname),
+  key (exptime)
+);
+
+-- For storing revision text
+CREATE TABLE blobs (
+  blob_index char(255) binary NOT NULL default '',
+  blob_data longblob NOT NULL default '',
+  UNIQUE key blob_index (blob_index)
+);
+
+-- For article validation
+
+CREATE TABLE `validate` (
+  `val_user` int(11) NOT NULL default '0',
+  `val_title` varchar(255) binary NOT NULL default '',
+  `val_timestamp` varchar(14) binary NOT NULL default '',
+  `val_type` int(10) unsigned NOT NULL default '0',
+  `val_value` int(11) default '0',
+  `val_comment` varchar(255) NOT NULL default '',
+  KEY `val_user` (`val_user`,`val_title`,`val_timestamp`)
+) TYPE=MyISAM;