tables.sql

来自「php 开发的内容管理系统」· SQL 代码 · 共 1,009 行 · 第 1/3 页

  
  -- See ar_text note.
  ar_flags tinyblob NOT NULL default '',
  
  -- When revisions are deleted, their unique rev_id is stored
  -- here so it can be retained after undeletion. This is necessary
  -- to retain permalinks to given revisions after accidental delete
  -- cycles or messy operations like history merges.
  -- 
  -- Old entries from 1.4 will be NULL here, and a new rev_id will
  -- be created on undeletion for those revisions.
  ar_rev_id int(8) unsigned,
  
  -- For newly deleted revisions, this is the text.old_id key to the
  -- actual stored text. To avoid breaking the block-compression scheme
  -- and otherwise making storage changes harder, the actual text is
  -- *not* deleted from the text table, merely hidden by removal of the
  -- page and revision entries.
  --
  -- Old entries deleted under 1.2-1.4 will have NULL here, and their
  -- ar_text and ar_flags fields will be used to create a new text
  -- row upon undeletion.
  ar_text_id int(8) unsigned,
  
  KEY name_title_timestamp (ar_namespace,ar_title,ar_timestamp)

) TYPE=InnoDB, DEFAULT CHARSET=utf8;


--
-- Track page-to-page hyperlinks within the wiki.
--
CREATE TABLE /*$wgDBprefix*/pagelinks (
  -- Key to the page_id of the page containing the link.
  pl_from int(8) unsigned NOT NULL default '0',
  
  -- Key to page_namespace/page_title of the target page.
  -- The target page may or may not exist, and due to renames
  -- and deletions may refer to different page records as time
  -- goes by.
  pl_namespace int NOT NULL default '0',
  pl_title varchar(255) binary NOT NULL default '',
  
  UNIQUE KEY pl_from(pl_from,pl_namespace,pl_title),
  KEY (pl_namespace,pl_title)

) TYPE=InnoDB, DEFAULT CHARSET=utf8;


--
-- Track template inclusions.
--
CREATE TABLE /*$wgDBprefix*/templatelinks (
  -- Key to the page_id of the page containing the link.
  tl_from int(8) unsigned NOT NULL default '0',
  
  -- Key to page_namespace/page_title of the target page.
  -- The target page may or may not exist, and due to renames
  -- and deletions may refer to different page records as time
  -- goes by.
  tl_namespace int NOT NULL default '0',
  tl_title varchar(255) binary NOT NULL default '',
  
  UNIQUE KEY tl_from(tl_from,tl_namespace,tl_title),
  KEY (tl_namespace,tl_title)

) TYPE=InnoDB, DEFAULT CHARSET=utf8;


--
-- Track links to images *used inline*
-- We don't distinguish live from broken links here, so
-- they do not need to be changed on upload/removal.
--
CREATE TABLE /*$wgDBprefix*/imagelinks (
  -- Key to page_id of the page containing the image / media link.
  il_from int(8) unsigned NOT NULL default '0',
  
  -- Filename of target image.
  -- This is also the page_title of the file's description page;
  -- all such pages are in namespace 6 (NS_IMAGE).
  il_to varchar(255) binary NOT NULL default '',
  
  UNIQUE KEY il_from(il_from,il_to),
  KEY (il_to)

) TYPE=InnoDB, DEFAULT CHARSET=utf8;

--
-- Track category inclusions *used inline*
-- This tracks a single level of category membership
-- (folksonomic tagging, really).
--
CREATE TABLE /*$wgDBprefix*/categorylinks (
  -- Key to page_id of the page defined as a category member.
  cl_from int(8) unsigned NOT NULL default '0',
  
  -- Name of the category.
  -- This is also the page_title of the category's description page;
  -- all such pages are in namespace 14 (NS_CATEGORY).
  cl_to varchar(255) binary NOT NULL default '',
  
  -- The title of the linking page, or an optional override
  -- 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...
  --
  -- 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.
  cl_timestamp timestamp NOT NULL,
  
  UNIQUE KEY cl_from(cl_from,cl_to),
  
  -- We always sort within a given category...
  KEY cl_sortkey(cl_to,cl_sortkey),
  
  -- Not really used?
  KEY cl_timestamp(cl_to,cl_timestamp)

) TYPE=InnoDB, DEFAULT CHARSET=utf8;

--
-- Track links to external URLs
--
CREATE TABLE /*$wgDBprefix*/externallinks (
  -- page_id of the referring page
  el_from int(8) unsigned NOT NULL default '0',

  -- The URL
  el_to blob NOT NULL default '',

  -- In the case of HTTP URLs, this is the URL with any username or password
  -- removed, and with the labels in the hostname reversed and converted to 
  -- lower case. An extra dot is added to allow for matching of either
  -- example.com or *.example.com in a single scan.
  -- Example: 
  --      http://user:[e-mail]/page.html
  --   becomes
  --      http://com.example.sub./page.html
  -- which allows for fast searching for all pages under example.com with the
  -- clause: 
  --      WHERE el_index LIKE 'http://com.example.%'
  el_index blob NOT NULL default '',
  
  KEY (el_from, el_to(40)),
  KEY (el_to(60), el_from),
  KEY (el_index(60))
) TYPE=InnoDB, DEFAULT CHARSET=utf8;

-- 
-- Track interlanguage links
--
CREATE TABLE /*$wgDBprefix*/langlinks (
  -- page_id of the referring page
  ll_from int(8) unsigned NOT NULL default '0',
  
  -- Language code of the target
  ll_lang varchar(10) binary NOT NULL default '',

  -- Title of the target, including namespace
  ll_title varchar(255) binary NOT NULL default '',

  UNIQUE KEY (ll_from, ll_lang),
  KEY (ll_lang, ll_title)
) ENGINE=InnoDB, DEFAULT CHARSET=utf8;

--
-- Contains a single row with some aggregate info
-- on the state of the site.
--
CREATE TABLE /*$wgDBprefix*/site_stats (
  -- The single row should contain 1 here.
  ss_row_id int(8) unsigned NOT NULL,
  
  -- Total number of page views, if hit counters are enabled.
  ss_total_views bigint(20) unsigned default '0',
  
  -- Total number of edits performed.
  ss_total_edits bigint(20) unsigned default '0',
  
  -- An approximate count of pages matching the following criteria:
  -- * in namespace 0
  -- * not a redirect
  -- * contains the text '[['
  -- See Article::isCountable() in includes/Article.php
  ss_good_articles bigint(20) unsigned default '0',
  
  -- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except faster
  ss_total_pages bigint(20) default '-1',

  -- Number of users, theoretically equal to SELECT COUNT(*) FROM user;
  ss_users bigint(20) default '-1',

  -- Deprecated, no longer updated as of 1.5
  ss_admins int(10) default '-1',

  -- Number of images, equivalent to SELECT COUNT(*) FROM image
  ss_images int(10) default '0',

  UNIQUE KEY ss_row_id (ss_row_id)

) TYPE=InnoDB;

--
-- Stores an ID for every time any article is visited;
-- depending on $wgHitcounterUpdateFreq, it is
-- periodically cleared and the page_counter column
-- in the page table updated for the all articles
-- that have been visited.)
--
CREATE TABLE /*$wgDBprefix*/hitcounter (
  hc_id INTEGER UNSIGNED NOT NULL
) TYPE=HEAP MAX_ROWS=25000;


--
-- The internet is full of jerks, alas. Sometimes it's handy
-- to block a vandal or troll account.
--
CREATE TABLE /*$wgDBprefix*/ipblocks (
  -- Primary key, introduced for privacy.
  ipb_id int(8) NOT NULL auto_increment,
  
  -- Blocked IP address in dotted-quad form or user name.
  ipb_address varchar(40) binary NOT NULL default '',
  
  -- Blocked user ID or 0 for IP blocks.
  ipb_user int(8) unsigned NOT NULL default '0',
  
  -- User ID who made the block.
  ipb_by int(8) unsigned NOT NULL default '0',
  
  -- Text comment made by blocker.
  ipb_reason tinyblob NOT NULL default '',
  
  -- Creation (or refresh) date in standard YMDHMS form.
  -- IP blocks expire automatically.
  ipb_timestamp char(14) binary NOT NULL default '',
  
  -- Indicates that the IP address was banned because a banned
  -- user accessed a page through it. If this is 1, ipb_address
  -- will be hidden, and the block identified by block ID number.
  ipb_auto tinyint(1) NOT NULL default '0',
  
  -- Time at which the block will expire.
  ipb_expiry char(14) binary NOT NULL default '',
  
  -- Start and end of an address range, in hexadecimal
  -- Size chosen to allow IPv6
  ipb_range_start varchar(32) NOT NULL default '',
  ipb_range_end varchar(32) NOT NULL default '',

  PRIMARY KEY ipb_id (ipb_id),
  INDEX ipb_address (ipb_address),
  INDEX ipb_user (ipb_user),
  INDEX ipb_range (ipb_range_start(8), ipb_range_end(8))

) TYPE=InnoDB, DEFAULT CHARSET=utf8;


--
-- Uploaded images and other files.
--
CREATE TABLE /*$wgDBprefix*/image (
  -- Filename.
  -- This is also the title of the associated description page,
  -- which will be in namespace 6 (NS_IMAGE).
  img_name varchar(255) binary NOT NULL default '',
  
  -- File size in bytes.
  img_size int(8) unsigned NOT NULL default '0',
  
  -- For images, size in pixels.
  img_width int(5)  NOT NULL default '0',
  img_height int(5)  NOT NULL default '0',
  
  -- Extracted EXIF metadata stored as a serialized PHP array.
  img_metadata mediumblob NOT NULL,
  
  -- For images, bits per pixel if known.
  img_bits int(3)  NOT NULL default '0',
  
  -- Media type as defined by the MEDIATYPE_xxx constants
  img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
  
  -- major part of a MIME media type as defined by IANA
  -- see http://www.iana.org/assignments/media-types/
  img_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart") NOT NULL default "unknown",
  
  -- minor part of a MIME media type as defined by IANA
  -- the minor parts are not required to adher to any standard
  -- but should be consistent throughout the database
  -- see http://www.iana.org/assignments/media-types/
  img_minor_mime varchar(32) NOT NULL default "unknown",
  
  -- Description field as entered by the uploader.
  -- This is displayed in image upload history and logs.
  img_description tinyblob NOT NULL default '',
  
  -- user_id and user_name of uploader.
  img_user int(5) unsigned NOT NULL default '0',
  img_user_text varchar(255) binary NOT NULL default '',
  
  -- Time of the upload.
  img_timestamp char(14) binary NOT NULL default '',
  
  PRIMARY KEY img_name (img_name),
  
  -- Used by Special:Imagelist for sort-by-size
  INDEX img_size (img_size),

  -- Used by Special:Newimages and Special:Imagelist
  INDEX img_timestamp (img_timestamp)

) TYPE=InnoDB, DEFAULT CHARSET=utf8;

--
-- Previous revisions of uploaded files.
-- Awkwardly, image rows have to be moved into
-- this table at re-upload time.
--
CREATE TABLE /*$wgDBprefix*/oldimage (
  -- Base filename: key to image.img_name
  oi_name varchar(255) binary NOT NULL default '',
  
  -- Filename of the archived file.
  -- This is generally a timestamp and '!' prepended to the base name.
  oi_archive_name varchar(255) binary NOT NULL default '',
  
  -- Other fields as in image...
  oi_size int(8) unsigned NOT NULL default 0,