View source for Manual:Database layout/diagram instructions

{{Manual:Database layout/viewbox}}
__TOC__
== Wiki-based diagram ==
Since January 2020, there is a wiki-based workflow that automates most of the diagram-drawing process.

=== How it works ===
There is a [[Extension:Scribunto/Lua reference manual|Lua module]] called <code>[[Module:SchemaDiagram]]</code> which is capable of parsing the SQL file that is part of the MediaWiki software. This file is used by MediaWiki to define the real tables and their structure, and the same file is used to visualise the diagram.

Pages such as [[Manual:Database layout/diagram]] invoke this Lua module with a reference to the SQL source, and a JSON structure that controls how to visualise it.

=== Create updated diagram ===
Use the below instructions to create a diagram for MediaWiki core (latest stable is currently: <code>{{MW stable release number}}</code>)

* Create [[Template:SchemaDiagram/sql/mediawiki-{{MW stable release number}}.json]], and fill it with the contents of [https://gerrit.wikimedia.org/g/mediawiki/core/+/{{MW stable release number}}/maintenance/tables.json mediawiki/core.git@{{MW stable release number}}:/maintenance/tables.json].
* Go to "Page information" for [[Template:SchemaDiagram/sql/mediawiki-{{MW stable release number}}.json]], and change the content model from "wikitext" to "JSON". 
* Create [[Manual:Database layout/diagram/{{MW stable release number}}]], using the contents of [[{{#invoke:Resolve|resolve|title=Manual:Database layout/diagram}}]] as starting point and save it as-is first for easier review and diffing.
* Edit [[Manual:Database layout/diagram/{{MW stable release number}}]]
*# Make sure the <code>caption</code> parameters referes to the correct MediaWiki version and release month (see [[:Category:MediaWiki version information templates|MediaWiki version information]]).
*# Update <code>sql_page</code> to point to <code>SchemaDiagram/sql/mediawiki-{{MW stable release number}}.json</code>.
*# Use "Show preview" and look for any errors from Lua and address them before saving. The most common error is that the layout specifies a table that was removed in the latest version. Simply remove it from the <code>layout</code> structure.
*#Also look out for Lua warnings of the form "''Database table … not in the current layout''". This indicates a new table was introduced, for which we will need to find a suitable place in the layout. If you are unfamiliar with this database table, there is likely a documentation page about it at [[Manual:Database layout]] which might explain more. If you're still unsure or if you want help deciding which group the new table fits in then ask MediaWiki developers via [[Wikitech-l]] or in the [[MediaWiki on IRC|#mediawiki chat on IRC]].
*#Feel free to add yourself to the <code>credit</code> line.
*#Once it looks done, [https://www.mediawiki.org/w/index.php?title=Manual:Database_layout/diagram&action=edit update the "Manual:Database layout/diagram" redirect] to this new version.

=== Upload diagram to Wikimedia Commons ===
For archival reasons and for display on other wikis, you may want to ocasionally also capture a screenshot and upload it to Commons:

*Go to [[Manual:Database layout/diagram/{{MW stable release number}}]] and activate the "Full screen" button.
*Use the browser's developer tools to capture a screenshot of the entire page
**Firefox: Open the three-dots "Page action" menu, choose "Take a Screenshot". Then "Save full page".
**Chrome: Menu bar "View" and choose "Developer" and "Developer Tools". Then open the command menu via ctrl-shift-P (Linux/Windows) or cmd-shift-P (macOS), and type "full size screen", and press Enter.
*Upload to Commons via [[commons:Special:UploadWizard|UploadWizard]].
**File name should be "MediaWiki {{MW stable release number}} database schema.png".
**Make sure the right license and attribution are set (see  bottom-right corner of the diagram).
**Add category [[c:Category:MediaWiki_database_schemas|Category:MediaWiki database schemas]].
*After upload:
**Edit the description page and add <code><nowiki>{{Other versions/MediaWiki database}}</nowiki></code> to <code>|other_versions=</code> in the Information template.
**Update this redirect: [[commons: File:MediaWiki database schema latest.svg|File:MediaWiki database schema latest.svg]] (Yes, from svg to png. It's okay!)

== Workbench diagram ==
From 2011 to 2019, schema diagrams were made using ''MySQL Workbench''. The resulting workbench files have been published on GitHub at [https://github.com/krinkle/ts-krinkle-misc/tree/gh-pages/mwDatabaseSchema/ Krinkle/mwDatabaseSchema]

=== Update the layout ===
Given that the workbench are hard-exported to Commons and can take a while to work through, it is recommended to figure out the layout before starting.

* Compare <code>[https://github.com/wikimedia/mediawiki/blob/{{MW stable release number}}/maintenance/tables.sql maintenance/tables.sql]</code> to the [[#Layout]] below.
* Remove any tables from the layout that no longer exist.
* Any new tables need to be given a place in the layout. If the table has more than 10 fields, also create a "N fields" placeholder block. This is to help figure out which groups are very tall, which helps determine where to put the smaller tables.
* If you're not sure what a table is about and in which group it belongs, reach out to other MediaWiki developers via [[Wikitech-l]] or in the [[MediaWiki on IRC|#mediawiki chat on IRC]].
* Keep groups on the same row roughly the same height to avoid wasted space, for example by moving tables next to each other as needed.

=== Create the diagram ===

# Download and install [https://www.mysql.com/downloads/workbench/ MySQL Workbench].
# Download <code>[https://github.com/wikimedia/mediawiki/blob/{{MW stable release number}}/maintenance/tables.sql maintenance/tables.sql]</code> ([https://github.com/wikimedia/mediawiki/raw/{{MW stable release number}}/maintenance/tables.sql raw]) from the latest stable MediaWiki version (currently: <code>{{MW stable release number}}</code>). Checkout the tag using Git, or [[Download|download the tarball]]. If downloading from the web viewer, be sure to save as ".sql" instead of ".txt".
# Create new MySQL Workbench Model document.
## Look out for a "Templates" section that may contain an example "user" or "category" table by default. Delete these examples before the next step. Due to a bug, importing the MediaWiki schema will fail partially if these example templates are not deleted. (Workbench Version 6.3)
# Use "File / Import / Reverse Engineer SQL Script" to import MediaWiki's <code>tables.sql</code>. Be sure to use UTF-8 encoding and tick "Place imported objects on a diagram".<blockquote class="toccolours">Import of SQL script file '~/Downloads/{{MW stable release number}}.tables.sql' has finished.<br>40 tables, 0 views and 0 stored procedures were imported in 0 schemas.</blockquote>
# The tables are now drawn on the canvas. However, they are not yet organised or grouped. Use the [[#Layout]] section as your guide, and create the necessary "Layer" objects.
# Export to SVG.
# Edit SVG and trim the excessive space from the <code><nowiki><svg></nowiki></code>'s <code>width</code>, <code>height</code>, and <code>viewBox</code>  attributes. Easiest is to open it in a browser, lower the <code>viewBox</code> values until there is little space left on the right and bottom (but not cropped). Then update the width and height attributes as well. For example, for mediawiki-schema-1.24.1 <code>width="1676.976378pt" height="1565.801575pt" viewBox="0 0 1676.976378 1565.801575"</code> was the generated element, and trimmed to <code>width="1400pt" height="1200pt" viewBox="0 0 1400 1200"</code>.
# Upload to Commons as <code>File:MediaWiki {{MW stable release number}} database schema.svg</code> ('''Do not overwrite''' any existing diagram on Commons).
#*Add <code><nowiki>|other_versions={{Other versions/MediaWiki database}}</nowiki></code> to the Information template.
#*Add the image to [[c:Category:MediaWiki_database_schemas|Category:MediaWiki database schemas]].
# Update the [[commons: File:MediaWiki database schema latest.svg]] redirect.

=== Layout ===
This is a description of the annotations and composition we use to visualise the relationships between tables.

<!-- Temporary tables that relate to the Actor, Comment and MCR migrations have been intentionally left out. -- Krinkle 16 July 2019. -->
{| class="wikitable"
|+ Database schema of MediaWiki X.YY.Z (MONTH_NAME YEAR)<br><span style="font-weight: normal;">Refer to https://www.mediawiki.org/wiki/DB for more details.</span>
|-
! User
! Permissions
! Logging
! Tags
! Recent changes
|- valign="top"
| style="background: #C5E8B4;"|
Background: <code>#C5E8B4</code>

{|
|- style="vertical-align: top;"
|
* [[Manual: user table|user]]<div class="wikitable" style="height: 15em;">± 15 fields</div>
|
* [[Manual: user_properties table|user_properties]]
* [[Manual: user_newtalk table|user_newtalk]]
* [[Manual:actor table|actor]]
* [[Manual:Bot passwords table|bot_passwords]]
|}
| style="background: #C5E8B4;"|
Background: <code>#C5E8B4</code>

* [[Manual: user_groups table|user_groups]]
* [[Manual: user_former_groups table|user_former_groups]]
* [[Manual: ipblocks table|ipblocks]]
* [[Manual:ipblocks_restrictions table|ipblocks_restrictions]]
* [[Manual: page_restrictions table|page_restrictions]]
* [[Manual: protected_titles table|protected_titles]]
| style="background: #D8FFFC;"|
Background: <code>#D8FFFC</code>

* [[Manual: logging table|logging]]
* [[Manual: log_search table|log_search]]

| style="background: #FFF1C2;"|
Background: <code>#FFF1C2</code>

* [[Manual: change_tag table|change_tag]]
* [[Manual:change_tag_def table|change_tag_def]]

| style="background: #C5E8B4;"|
Background: <code>#C5E8B4</code>
* [[Manual: recentchanges table|recentchanges]]
* [[Manual: watchlist table|watchlist]]
* [[Manual: watchlist_expiry table|watchlist_expiry]]

|-
! colspan="2" | Pages
! colspan="2" | Revisions
! Link tables
|- valign="top"
| colspan="2" style="background: #FFF1C2;" |
Background: <code>#FFF1C2</code>
{|
|- style="vertical-align: top;"
|
* [[Manual: page table|page]]<div class="wikitable" style="height: 13em;">± 13 fields</div>
* [[Manual: page_props table|page_props]]
|
* [[Manual: archive table|archive]]<div class="wikitable" style="height: 16em;">± 16 fields</div>
|
* [[Manual: redirect table|redirect]]
* [[Manual: category table|category]]
|}
| colspan="2" style="background: #FFF1C2;" |
Background: <code>#FFF1C2</code>

{|
|- style="vertical-align: top;"
|
* [[Manual: revision table|revision]]<div class="wikitable" style="height: 14em;">± 14 fields</div>
|
* [[Manual:slots table|slots]]
* [[Manual:slot_roles table|slot_roles]]
|
* [[Manual:content table|content]]
* [[Manual:content_models table|content_models]]
* [[Manual: text table|text]]
|}

| style="background: #FFF1C2;"|
Background: <code>#FFF1C2</code>
{|
|- style="vertical-align: top;"
|
* [[Manual: pagelinks table|pagelinks]]
* [[Manual: iwlinks table|iwlinks]]
* [[Manual: externallinks table|externallinks]]
* [[Manual: langlinks table|langlinks]]
|
* [[Manual: imagelinks table|imagelinks]]
* [[Manual: templatelinks table|templatelinks]]
* [[Manual: categorylinks table|categorylinks]]
|}

|-
! Statistics
! Search
! Maintenance
! colspan="2" | Multimedia
|- valign="top"
| style="background: #D8FFFC;" |
Background: <code>#D8FFFC</code>
* [[Manual: site_stats table|site_stats]]

| style="background: #D8FFFC;"|
Background: <code>#D8FFFC</code>
* [[Manual: searchindex table|searchindex]]

| style="background: #E8D0C9;"|
Background: <code>#E8D0C9</code>
* [[Manual: job table|job]]<div class="wikitable" style="height: 11em;">± 11 fields</div>
* [[Manual: updatelog table|updatelog]]

| colspan="2" rowspan="3" style="background: #F7CCFF;"|
Background: <code>#F7CCFF</code>
{|
|- style="vertical-align: top;"
|
* [[Manual: image table|image]]<div class="wikitable" style="height: 13em;">± 13 fields</div>
* [[Manual: oldimage table|oldimage]]<div class="wikitable" style="height: 15em;">± 15 fields</div>
|
* [[Manual: filearchive table|filearchive]]<div class="wikitable" style="height: 21em;">± 21 fields</div>
|
* [[Manual: uploadstash table|uploadstash]]<div class="wikitable" style="height: 17em;">± 17 fields</div>
|}

|-
! Interwiki
! Caching tables
! ResourceLoader
|- valign="top"
| style="background: #E8D0C9;"|
Background: <code>#E8D0C9</code>
{|
|- style="vertical-align: top;"
|
* [[Manual: sites table|sites]]<div class="wikitable" style="height: 11em;">± 11 fields</div>
|
* [[Manual: site_identifiers table|site_identifiers]]
* [[Manual: interwiki table|interwiki]]
|}
| style="background: #E8D0C9;"|
Background: <code>#E8D0C9</code>
{|
|- style="vertical-align: top;"
|
* [[Manual: objectcache table|objectcache]]
* [[Manual: l10n_cache table|l10n_cache]]
|
* [[Manual: querycache table|querycache]]
* [[Manual: querycache_info table|querycache_info]]
|
* [[Manual: querycachetwo table|querycachetwo]]
|}

| style="background: #E8D0C9;"|
Background: <code>#E8D0C9</code>
* [[Manual: module_deps table|module_deps]]

|}

== Credits ==

* [[Commons:Special:Contributions/Dprutean|Dprutean]], started this in 2009 with the [[Commons:File:Mediawiki dbschema.svg|MediaWiki 1.10 schema diagram]], using the [http://www.dbschema.com/ DbSchema] software.
* [[User:Nickj|Nickj]], re-created the [[commons:File:MediaWiki_1.10_database_schema.png|MediaWiki 1.10 schema diagram]] using MySQL Workbench and started documenting how to update it.
* [[User:Krinkle|Krinkle]], updating the schema diagrams and documentation 2011–2019.

== See also ==
* [[commons:MediaWiki#MediaWiki database schemata|"MediaWiki database" on Wikimedia Commons]]