Manual:Database layout/diagram instructions

Database schema diagram of MediaWiki

Explore database schema →

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 Lua module called Module:SchemaDiagram 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.

Previous versions

• Database layout/diagram/1.34.0
• Database layout/diagram/1.35.0
• Database layout/diagram/1.36.0
• Database layout/diagram/1.37.0
• Database layout/diagram/1.38.0
• Database layout/diagram/1.39.0
• Database layout/diagram/1.40.0
• Database layout/diagram/1.41.0
• Database layout/diagram/1.42.1

• SchemaDiagram/sql/mediawiki-1.34.0.txt
• SchemaDiagram/sql/mediawiki-1.35.0.txt
• SchemaDiagram/sql/mediawiki-1.36.0.txt
• SchemaDiagram/sql/mediawiki-1.37.0.json
• SchemaDiagram/sql/mediawiki-1.38.0.json
• SchemaDiagram/sql/mediawiki-1.39.0.json
• SchemaDiagram/sql/mediawiki-1.40.0.json
• SchemaDiagram/sql/mediawiki-1.41.0.json
• SchemaDiagram/sql/mediawiki-1.42.1.json

Create updated diagram

Use the below instructions to create a diagram for MediaWiki core (latest stable is currently: 1.42.3)

• Create Template:SchemaDiagram/sql/mediawiki-1.42.3.json, and fill it with the contents of mediawiki/core.git@1.42.3:/maintenance/tables.json.
• Go to "Page information" for Template:SchemaDiagram/sql/mediawiki-1.42.3.json, and change the content model from "wikitext" to "JSON".
• Create Manual:Database layout/diagram/1.42.3, using the contents of Manual:Database layout/diagram/1.42.1 as starting point and save it as-is first for easier review and diffing.
• Edit Manual:Database layout/diagram/1.42.3
  1. Make sure the caption parameters referes to the correct MediaWiki version and release month (see MediaWiki version information).
  2. Update sql_page to point to SchemaDiagram/sql/mediawiki-1.42.3.json.
  3. 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 layout structure.
  4. 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 chat on IRC.
  5. Feel free to add yourself to the credit line.
  6. Once it looks done, 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/1.42.1 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 UploadWizard.
  • File name should be "MediaWiki 1.42.1 database schema.png".
  • Make sure the right license and attribution are set (see bottom-right corner of the diagram).
  • Add category Category:MediaWiki database schemas.
• After upload:
  • Edit the description page and add {{Other versions/MediaWiki database}} to |other_versions= in the Information template.
  • Update this redirect: 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 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 maintenance/tables.sql 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 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

1. Download and install MySQL Workbench.
2. Download maintenance/tables.sql (raw) from the latest stable MediaWiki version (currently: 1.42.3). Checkout the tag using Git, or download the tarball. If downloading from the web viewer, be sure to save as ".sql" instead of ".txt".
3. Create new MySQL Workbench Model document.
   1. 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)
4. Use "File / Import / Reverse Engineer SQL Script" to import MediaWiki's tables.sql. Be sure to use UTF-8 encoding and tick "Place imported objects on a diagram".

   Import of SQL script file '~/Downloads/1.42.3.tables.sql' has finished.
   40 tables, 0 views and 0 stored procedures were imported in 0 schemas.

5. 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.
6. Export to SVG.
7. Edit SVG and trim the excessive space from the <svg>'s width, height, and viewBox attributes. Easiest is to open it in a browser, lower the viewBox 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 width="1676.976378pt" height="1565.801575pt" viewBox="0 0 1676.976378 1565.801575" was the generated element, and trimmed to width="1400pt" height="1200pt" viewBox="0 0 1400 1200".
8. Upload to Commons as File:MediaWiki 1.42.3 database schema.svg (Do not overwrite any existing diagram on Commons).
   • Add |other_versions={{Other versions/MediaWiki database}} to the Information template.
   • Add the image to Category:MediaWiki database schemas.
9. 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.

Database schema of MediaWiki X.YY.Z (MONTH_NAME YEAR)
Refer to https://www.mediawiki.org/wiki/DB for more details.

User	Permissions	Logging	Tags	Recent changes
Background: #C5E8B4 user ± 15 fields user_properties user_newtalk actor bot_passwords	Background: #C5E8B4 user_groups user_former_groups ipblocks ipblocks_restrictions page_restrictions protected_titles	Background: #D8FFFC logging log_search	Background: #FFF1C2 change_tag change_tag_def	Background: #C5E8B4 recentchanges watchlist watchlist_expiry
Pages		Revisions		Link tables
Background: #FFF1C2 page ± 13 fields page_props archive ± 16 fields redirect category		Background: #FFF1C2 revision ± 14 fields slots slot_roles content content_models text		Background: #FFF1C2 pagelinks iwlinks externallinks langlinks imagelinks templatelinks categorylinks
Statistics	Search	Maintenance	Multimedia
Background: #D8FFFC site_stats	Background: #D8FFFC searchindex	Background: #E8D0C9 job ± 11 fields updatelog	Background: #F7CCFF image ± 13 fields oldimage ± 15 fields filearchive ± 21 fields uploadstash ± 17 fields
Interwiki	Caching tables	ResourceLoader
Background: #E8D0C9 sites ± 11 fields site_identifiers interwiki	Background: #E8D0C9 objectcache l10n_cache querycache querycache_info querycachetwo	Background: #E8D0C9 module_deps

Credits

• Dprutean, started this in 2009 with the MediaWiki 1.10 schema diagram, using the DbSchema software.
• Nickj, re-created the MediaWiki 1.10 schema diagram using MySQL Workbench and started documenting how to update it.
• Krinkle, updating the schema diagrams and documentation 2011–2019.

See also

• "MediaWiki database" on Wikimedia Commons