X-Git-Url: https://git.cyclocoop.org/%27.WWW_URL.%27admin/?a=blobdiff_plain;f=maintenance%2Ftables.sql;h=a4e107a17c68034a70bc8724f2a52c0d6546cf25;hb=27bfa2854fe6a4b4811b7b26c632eafa54f7cb0e;hp=0880a702055cac50a1606dcb59c061783ebdb70c;hpb=a4d029210aa2f5338519f7cc18a783cff4728049;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 0880a70205..a4e107a17c 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -86,12 +86,6 @@ CREATE TABLE /*_*/user ( -- Same with passwords. user_email tinytext NOT NULL, - -- Newline-separated list of name=value defining the user - -- preferences - -- Now obsolete in favour of user_properties table; - -- old values will be migrated from here transparently. - user_options blob NOT NULL, - -- This is a timestamp which is updated when a user -- logs in, logs out, changes preferences, or performs -- some other action requiring HTML cache invalidation @@ -135,7 +129,7 @@ CREATE TABLE /*_*/user ( CREATE UNIQUE INDEX /*i*/user_name ON /*_*/user (user_name); CREATE INDEX /*i*/user_email_token ON /*_*/user (user_email_token); -CREATE INDEX /*i*/user_email ON /*_*/user (user_email(50),user_name); +CREATE INDEX /*i*/user_email ON /*_*/user (user_email(50)); -- @@ -158,13 +152,13 @@ CREATE TABLE /*_*/user_groups ( -- 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 varbinary(16) NOT NULL default '' + ug_group varbinary(32) NOT NULL default '' ) /*$wgDBTableOptions*/; CREATE UNIQUE INDEX /*i*/ug_user_group ON /*_*/user_groups (ug_user,ug_group); CREATE INDEX /*i*/ug_group ON /*_*/user_groups (ug_group); --- Stores the groups the user has once belonged to. +-- Stores the groups the user has once belonged to. -- The user may still belong these groups. Check user_groups. CREATE TABLE /*_*/user_former_groups ( -- Key to user_id @@ -271,7 +265,7 @@ CREATE TABLE /*_*/page ( CREATE UNIQUE INDEX /*i*/name_title ON /*_*/page (page_namespace,page_title); CREATE INDEX /*i*/page_random ON /*_*/page (page_random); CREATE INDEX /*i*/page_len ON /*_*/page (page_len); - +CREATE INDEX /*i*/page_redirect_namespace_len ON /*_*/page (page_is_redirect, page_namespace, page_len); -- -- Every edit of a page creates also a revision row. @@ -279,6 +273,7 @@ CREATE INDEX /*i*/page_len ON /*_*/page (page_len); -- to the text storage backend. -- CREATE TABLE /*_*/revision ( + -- Unique ID to identify each revision rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Key to page_id. This should _never_ be invalid. @@ -302,14 +297,14 @@ CREATE TABLE /*_*/revision ( -- Text username or IP address of the editor. rev_user_text varchar(255) binary NOT NULL default '', - -- Timestamp + -- Timestamp of when revision was created rev_timestamp binary(14) NOT NULL default '', -- Records whether the user marked the 'minor edit' checkbox. -- Many automated edits are marked as minor. rev_minor_edit tinyint unsigned NOT NULL default 0, - -- Not yet used; reserved for future changes to the deletion system. + -- Restrictions on who can access this revision rev_deleted tinyint unsigned NOT NULL default 0, -- Length of this revision in bytes @@ -317,7 +312,10 @@ CREATE TABLE /*_*/revision ( -- Key to revision.rev_id -- This field is used to add support for a tree structure (The Adjacency List Model) - rev_parent_id int unsigned default NULL + rev_parent_id int unsigned default NULL, + + -- SHA-1 text content hash in base-36 + rev_sha1 varbinary(32) NOT NULL default '' ) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=1024; -- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit @@ -424,7 +422,10 @@ CREATE TABLE /*_*/archive ( ar_page_id int unsigned, -- Original previous revision - ar_parent_id int unsigned default NULL + ar_parent_id int unsigned default NULL, + + -- SHA-1 text content hash in base-36 + ar_sha1 varbinary(32) NOT NULL default '' ) /*$wgDBTableOptions*/; CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp); @@ -938,6 +939,57 @@ CREATE INDEX /*i*/fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp); +-- +-- Store information about newly uploaded files before they're +-- moved into the actual filestore +-- +CREATE TABLE /*_*/uploadstash ( + us_id int unsigned NOT NULL PRIMARY KEY auto_increment, + + -- the user who uploaded the file. + us_user int unsigned NOT NULL, + + -- file key. this is how applications actually search for the file. + -- this might go away, or become the primary key. + us_key varchar(255) NOT NULL, + + -- the original path + us_orig_path varchar(255) NOT NULL, + + -- the temporary path at which the file is actually stored + us_path varchar(255) NOT NULL, + + -- which type of upload the file came from (sometimes) + us_source_type varchar(50), + + -- the date/time on which the file was added + us_timestamp varbinary(14) not null, + + us_status varchar(50) not null, + + -- file properties from File::getPropsFromPath. these may prove unnecessary. + -- + us_size int unsigned NOT NULL, + -- this hash comes from File::sha1Base36(), and is 31 characters + us_sha1 varchar(31) NOT NULL, + us_mime varchar(255), + -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table + us_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL, + -- image-specific properties + us_image_width int unsigned, + us_image_height int unsigned, + us_image_bits smallint unsigned + +) /*$wgDBTableOptions*/; + +-- sometimes there's a delete for all of a user's stuff. +CREATE INDEX /*i*/us_user ON /*_*/uploadstash (us_user); +-- pick out files by key, enforce key uniqueness +CREATE UNIQUE INDEX /*i*/us_key ON /*_*/uploadstash (us_key); +-- the abandoned upload cleanup script needs this +CREATE INDEX /*i*/us_timestamp ON /*_*/uploadstash (us_timestamp); + + -- -- Primarily a summary table for Special:Recentchanges, -- this table contains some additional info on edits from @@ -946,6 +998,8 @@ CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timesta CREATE TABLE /*_*/recentchanges ( rc_id int NOT NULL PRIMARY KEY AUTO_INCREMENT, rc_timestamp varbinary(14) NOT NULL default '', + + -- This is no longer used rc_cur_time varbinary(14) NOT NULL default '', -- As in revision @@ -965,6 +1019,7 @@ CREATE TABLE /*_*/recentchanges ( -- default view. rc_bot tinyint unsigned NOT NULL default 0, + -- Set if this change corresponds to a page creation rc_new tinyint unsigned NOT NULL default 0, -- Key to page_id (was cur_id prior to 1.5). @@ -978,8 +1033,10 @@ CREATE TABLE /*_*/recentchanges ( -- rev_id of the prior revision, for generating diff links. rc_last_oldid int unsigned NOT NULL default 0, - -- These may no longer be used, with the new move log. + -- The type of change entry (RC_EDIT,RC_NEW,RC_LOG) rc_type tinyint unsigned NOT NULL default 0, + + -- These may no longer be used, with the new move log. rc_moved_to_ns tinyint unsigned NOT NULL default 0, rc_moved_to_title varchar(255) binary NOT NULL default '', @@ -1175,6 +1232,7 @@ CREATE INDEX /*i*/page_time ON /*_*/logging (log_namespace, log_title, log_times CREATE INDEX /*i*/times ON /*_*/logging (log_timestamp); CREATE INDEX /*i*/log_user_type_time ON /*_*/logging (log_user, log_type, log_timestamp); CREATE INDEX /*i*/log_page_id_time ON /*_*/logging (log_page,log_timestamp); +CREATE INDEX /*i*/type_action ON /*_*/logging(log_type, log_action, log_timestamp); CREATE TABLE /*_*/log_search ( @@ -1189,17 +1247,6 @@ CREATE UNIQUE INDEX /*i*/ls_field_val ON /*_*/log_search (ls_field,ls_value,ls_l CREATE INDEX /*i*/ls_log_id ON /*_*/log_search (ls_log_id); -CREATE TABLE /*_*/trackbacks ( - tb_id int PRIMARY KEY AUTO_INCREMENT, - tb_page int REFERENCES /*_*/page(page_id) ON DELETE CASCADE, - tb_title varchar(255) NOT NULL, - tb_url blob NOT NULL, - tb_ex text, - tb_name varchar(255) -) /*$wgDBTableOptions*/; -CREATE INDEX /*i*/tb_page ON /*_*/trackbacks (tb_page); - - -- Jobs performed by parallel apache threads or a command-line daemon CREATE TABLE /*_*/job ( job_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,