* (bug 650) Use a shorter cl_sortkey field to avoid breaking on MySQL 4.1

[lhc/web/wiklou.git] / maintenance / tables.sql

diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index 2bf8de4..c03a3b8 100644 (file)
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -104,7 +104,7 @@ CREATE TABLE /*$wgDBprefix*/user (
   user_email_token_expires CHAR(14) BINARY,
 
   PRIMARY KEY user_id (user_id),
-  INDEX user_name (user_name(10)),
+  UNIQUE INDEX user_name (user_name),
   INDEX (user_email_token)
 
 ) TYPE=InnoDB;
@@ -114,17 +114,25 @@ CREATE TABLE /*$wgDBprefix*/user (
 -- this allows sites with a shared user table to have different
 -- permissions assigned to a user in each project.
 --
--- TODO: de-blob this; it should be a property table
+-- This table replaces the old user_rights field which used a
+-- comma-separated blob.
 --
-CREATE TABLE /*$wgDBprefix*/user_rights (
+CREATE TABLE /*$wgDBprefix*/user_groups (
   -- Key to user_id
-  ur_user int(5) unsigned NOT NULL,
-  
-  -- Comma-separated list of permission keys
-  ur_rights tinyblob NOT NULL default '',
+  ug_user int(5) unsigned NOT NULL default '0',
   
-  UNIQUE KEY ur_user (ur_user)
-
+  -- Group names are short symbolic string keys.
+  -- The set of group names is open-ended, though in practice
+  -- only some predefined ones are likely to be used.
+  --
+  -- At runtime $wgGroupPermissions will associate group keys
+  -- with particular permissions. A user will have the combined
+  -- permissions of any group they're explicitly in, plus
+  -- the implicit '*' and 'user' groups.
+  ug_group char(16) NOT NULL default '',
+  
+  PRIMARY KEY (ug_user,ug_group),
+  KEY (ug_group)
 ) TYPE=InnoDB;
 
 -- The following table is no longer needed with Enotif >= 2.00
@@ -387,7 +395,12 @@ CREATE TABLE /*$wgDBprefix*/categorylinks (
   -- to determine sort order. Sorting is by binary order, which
   -- isn't always ideal, but collations seem to be an exciting
   -- and dangerous new world in MySQL...
-  cl_sortkey varchar(255) binary NOT NULL default '',
+  --
+  -- For MySQL 4.1+ with charset set to utf8, the sort key *index*
+  -- needs cut to be smaller than 1024 bytes (at 3 bytes per char).
+  -- To sort properly on the shorter key, this field needs to be
+  -- the same shortness.
+  cl_sortkey varchar(86) binary NOT NULL default '',
   
   -- This isn't really used at present. Provided for an optional
   -- sorting method by approximate addition time.
@@ -395,9 +408,8 @@ CREATE TABLE /*$wgDBprefix*/categorylinks (
   
   UNIQUE KEY cl_from(cl_from,cl_to),
   
-  -- This key is trouble. It's incomplete, AND it's too big
-  -- when collation is set to UTF-8. Bleeeacch!
-  KEY cl_sortkey(cl_to,cl_sortkey(128)),
+  -- We always sort within a given category...
+  KEY cl_sortkey(cl_to,cl_sortkey),
   
   -- Not really used?
   KEY cl_timestamp(cl_to,cl_timestamp)
@@ -795,19 +807,11 @@ CREATE TABLE /*$wgDBprefix*/logging (
 
 
 -- Hold group name and description
-CREATE TABLE /*$wgDBprefix*/groups (
-  gr_id int(5) unsigned NOT NULL auto_increment,
-  gr_name varchar(50) NOT NULL default '',
-  gr_description varchar(255) NOT NULL default '',
-  gr_rights tinyblob,
-  PRIMARY KEY  (gr_id)
-
-) TYPE=InnoDB;
-
--- Relation table between user and groups
-CREATE TABLE /*$wgDBprefix*/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)
-
-) TYPE=InnoDB;
+--CREATE TABLE /*$wgDBprefix*/groups (
+--  gr_id int(5) unsigned NOT NULL auto_increment,
+--  gr_name varchar(50) NOT NULL default '',
+--  gr_description varchar(255) NOT NULL default '',
+--  gr_rights tinyblob,
+--  PRIMARY KEY  (gr_id)
+--
+--) TYPE=InnoDB;