X-Git-Url: http://git.cyclocoop.org/?a=blobdiff_plain;f=maintenance%2Ftables.sql;h=db2c0704ce9f7f2028e1536146695ae58089b0dc;hb=22b1c2a2f8fb108bb902ac1b958e27b7bb610ba9;hp=340fb19f1bc8e32a0e14737b07e31c8c859d381e;hpb=c9dcdbc00710863c0258518ac8520d913deea62a;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 340fb19f1b..db2c0704ce 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -8,18 +8,28 @@ 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) -) PACK_KEYS=1; - + user_token char(32) binary NOT NULL default '', + PRIMARY KEY user_id (user_id), + INDEX user_name (user_name(10)) +); + +-- TODO: de-blob this; it should be a property table +CREATE TABLE user_rights ( + ur_user int(5) unsigned NOT NULL, + ur_rights tinyblob NOT NULL default '', + UNIQUE KEY ur_user (ur_user) +); + CREATE TABLE user_newtalk ( user_id int(5) NOT NULL default '0', - user_ip varchar(40) NOT NULL default '' + user_ip varchar(40) NOT NULL default '', + INDEX user_id (user_id), + INDEX user_ip (user_ip) ); CREATE TABLE cur ( @@ -39,8 +49,19 @@ CREATE TABLE cur ( cur_random real unsigned NOT NULL, cur_touched char(14) binary NOT NULL default '', inverse_timestamp char(14) binary NOT NULL default '', - UNIQUE KEY cur_id (cur_id) -) PACK_KEYS=1; + PRIMARY KEY cur_id (cur_id), + UNIQUE INDEX name_title (cur_namespace,cur_title), + + -- Is this one necessary? + INDEX cur_title (cur_title(20)), + + INDEX cur_timestamp (cur_timestamp), + INDEX (cur_random), + INDEX name_title_timestamp (cur_namespace,cur_title,inverse_timestamp), + INDEX user_timestamp (cur_user,inverse_timestamp), + INDEX usertext_timestamp (cur_user_text,inverse_timestamp), + INDEX namespace_redirect_timestamp(cur_namespace,cur_is_redirect,cur_timestamp) +); CREATE TABLE old ( old_id int(8) unsigned NOT NULL auto_increment, @@ -54,8 +75,13 @@ CREATE TABLE old ( old_minor_edit tinyint(1) NOT NULL default '0', old_flags tinyblob NOT NULL default '', inverse_timestamp char(14) binary NOT NULL default '', - UNIQUE KEY old_id (old_id) -) PACK_KEYS=1; + + PRIMARY KEY old_id (old_id), + INDEX old_timestamp (old_timestamp), + INDEX name_title_timestamp (old_namespace,old_title,inverse_timestamp), + INDEX user_timestamp (old_user,inverse_timestamp), + INDEX usertext_timestamp (old_user_text,inverse_timestamp) +); CREATE TABLE archive ( ar_namespace tinyint(2) unsigned NOT NULL default '0', @@ -66,8 +92,10 @@ CREATE TABLE archive ( ar_user_text varchar(255) binary NOT NULL, ar_timestamp char(14) binary NOT NULL default '', ar_minor_edit tinyint(1) NOT NULL default '0', - ar_flags tinyblob NOT NULL default '' -) PACK_KEYS=1; + ar_flags tinyblob NOT NULL default '', + + KEY name_title_timestamp (ar_namespace,ar_title,ar_timestamp) +); -- -- Track links that do exist @@ -151,8 +179,11 @@ CREATE TABLE ipblocks ( 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) -) PACK_KEYS=1; + + PRIMARY KEY ipb_id (ipb_id), + INDEX ipb_address (ipb_address), + INDEX ipb_user (ipb_user) +); CREATE TABLE image ( img_name varchar(255) binary NOT NULL default '', @@ -161,8 +192,10 @@ CREATE TABLE image ( 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 '', - UNIQUE KEY img_name (img_name) -) PACK_KEYS=1; + PRIMARY KEY img_name (img_name), + INDEX img_size (img_size), + INDEX img_timestamp (img_timestamp) +); CREATE TABLE oldimage ( oi_name varchar(255) binary NOT NULL default '', @@ -171,8 +204,9 @@ CREATE TABLE oldimage ( oi_description tinyblob NOT NULL default '', 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 '' -) PACK_KEYS=1; + oi_timestamp char(14) binary NOT NULL default '', + INDEX oi_name (oi_name(10)) +); CREATE TABLE recentchanges ( rc_id int(8) NOT NULL auto_increment, @@ -180,7 +214,7 @@ CREATE TABLE recentchanges ( rc_cur_time varchar(14) binary NOT NULL default '', rc_user int(10) unsigned NOT NULL default '0', rc_user_text varchar(255) binary NOT NULL default '', - rc_namespace tinyint(3) unsigned NOT NULL default '0', + rc_namespace tinyint(3) NOT NULL default '0', rc_title varchar(255) binary NOT NULL default '', rc_comment varchar(255) binary NOT NULL default '', rc_minor tinyint(3) unsigned NOT NULL default '0', @@ -192,16 +226,24 @@ 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 '', + rc_patrolled tinyint(3) unsigned NOT NULL default '0', rc_ip char(15) NOT NULL default '', - UNIQUE KEY rc_id (rc_id) -) PACK_KEYS=1; + + PRIMARY KEY rc_id (rc_id), + INDEX rc_timestamp (rc_timestamp), + INDEX rc_namespace_title (rc_namespace, rc_title), + INDEX rc_cur_id (rc_cur_id), + INDEX new_name_timestamp(rc_new,rc_namespace,rc_timestamp), + INDEX rc_ip (rc_ip) +); 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) -) PACK_KEYS=1; + UNIQUE KEY (wl_user, wl_namespace, wl_title), + KEY namespace_title (wl_namespace,wl_title) +); CREATE TABLE math ( math_inputhash varchar(16) NOT NULL, @@ -219,8 +261,11 @@ CREATE TABLE searchindex ( si_page int(8) unsigned NOT NULL, si_title varchar(255) NOT NULL default '', si_text mediumtext NOT NULL default '', - UNIQUE KEY (si_page) -) TYPE=MyISAM PACK_KEYS=1; + UNIQUE KEY (si_page), + FULLTEXT si_title (si_title), + FULLTEXT si_text (si_text) + +) TYPE=MyISAM; CREATE TABLE interwiki ( iw_prefix char(32) NOT NULL, @@ -264,4 +309,51 @@ CREATE TABLE `validate` ( `val_value` int(11) default '0', `val_comment` varchar(255) NOT NULL default '', KEY `val_user` (`val_user`,`val_title`,`val_timestamp`) -) TYPE=MyISAM; +); + + +CREATE TABLE logging ( + -- Symbolic keys for the general log type and the action type + -- within the log. The output format will be controlled by the + -- action field, but only the type controls categorization. + log_type char(10) NOT NULL default '', + log_action char(10) NOT NULL default '', + + -- Timestamp. Duh. + log_timestamp char(14) NOT NULL default '19700101000000', + + -- The user who performed this action; key to user_id + log_user int unsigned NOT NULL default 0, + + -- Key to the page affected. Where a user is the target, + -- this will point to the user page. + log_namespace tinyint unsigned NOT NULL default 0, + log_title varchar(255) NOT NULL default '', + + -- Freeform text. Interpreted as edit history comments. + log_comment varchar(255) NOT NULL default '', + + KEY type_time (log_type, log_timestamp), + KEY user_time (log_user, log_timestamp), + KEY page_time (log_namespace, log_title, log_timestamp) +); + + + + + +-- Hold group name and description +CREATE TABLE `group` ( + group_id int(5) unsigned NOT NULL auto_increment, + group_name varchar(50) NOT NULL default '', + group_description varchar(255) NOT NULL default '', + group_rights tinyblob, + PRIMARY KEY (group_id) +); + +-- Relation table between user and groups +CREATE TABLE user_groups ( + ug_user int(5) unsigned NOT NULL default '0', + ug_group int(5) unsigned NOT NULL default '0', + PRIMARY KEY (ug_user,ug_group) +);