Manual:Coding conventions/Database

Jump to navigation Jump to search


Use UPPERCASE for MySQL/MariaDB keywords, and lowercase for things like types. Do not specify the lengths of numeric types, but do for varchar() and varbinary() types. Use varbinary(14) for all timestamps, and parse them to the standard format using $dbw->timestamp( $ts ); do not use the timestamp field type.

Make sure to include the /*_*/ comment immediately before any table name; this will be replaced with the wiki's database prefix if necessary, and omitting it will cause breakage. Similarly, include the /*$wgDBTableOptions*/ comment after any table declaration, and /*i*/ immediately before any index names.

Create indices as separate statements, do not include them in the table creation query; the separate syntax is clearer and makes it easier to see the difference between unique and non-unique indices. Don't create indices with ALTER TABLE ... ADD INDEX ..., always use CREATE INDEX ... ON ... instead.

-- Track page-to-page hyperlinks within the wiki.
CREATE TABLE /*_*/pagelinks (
  -- Key to the page_id of the page containing the link.
  pl_from int 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 ''
) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
CREATE UNIQUE INDEX /*i*/pl_namespace ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);


SQLite uses the same SQL as MySQL/MariaDB, so the SQL must be compatible with both. Usually, this can be accomplished with a few simple rules, given at Manual:Database access#SQLite compatibility.

Table naming[edit]

  • Table names should be singular nouns: user, page, revision, etc. There are some historical exceptions: pagelinks, categorylinks
  • Column names are given a prefix derived from the table name: the name itself if it's short, or an abbreviation:
    • pagepage_id, page_namespace, page_title
    • categorylinkscl_from, cl_namespace

Changing the schema[edit]

See Development policy#Database_patches, especially .

When updating the schema for an extension, it is advisable to both update the original schema file and create a patch file for those updating from a previous version.

Coding conventionsManual:Coding conventions
General All languagesManual:Coding conventions#Code structure · Development policyDevelopment policy · Security for developersSecurity for developers · Pre-commit checklistManual:Pre-commit checklist · Performance guidelinesPerformance guidelines (draft) · Design Style guideDesign/Living style guide · Documentation Style guideDocumentation/Style guide (draft) · Accessibility guide for developersAccessibility guide for developers (draft) · Best practices for extensionsBest practices for extensions (draft)
PHP Code conventionsManual:Coding conventions/PHP · PHPUnit test conventionsManual:PHP unit testing/Writing unit tests#Test_conventions · Security checklist for developersSecurity checklist for developers
JavaScript Code conventionsManual:Coding conventions/JavaScript · Learning JavaScriptLearning JavaScript
CSS Code conventionsManual:Coding conventions/CSS
Database Code conventionsManual:Coding conventions/Database · Database policyDevelopment policy#Database policy
Python Code conventionsManual:Coding conventions/Python
Ruby Code conventionsManual:Coding conventions/Ruby
Selenium/Cucumber Code conventionsManual:Coding conventions/Selenium
Java Code conventionsManual:Coding conventions/Java
API client code Standards for API client librariesAPI:Client code/Gold standard