Manual:Database layout/diagram instructions

From mediawiki.org
Jump to navigation Jump to search
Database schema diagram of MediaWiki
Database schema diagram of MediaWiki

Wiki-based diagram[edit]

Since January 2020, there is a wiki-based workflow that automates most of the diagram-drawing process.

How it works[edit]

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.

Create updated diagram[edit]

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

Upload diagram to Wikimedia Commons[edit]

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.36.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.36.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[edit]

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[edit]

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[edit]

  1. Download and install MySQL Workbench.
  2. Download maintenance/tables.sql (raw) from the latest stable MediaWiki version (currently: 1.36.1). 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.36.1.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.36.1 database schema.svg (Do not overwrite any existing diagram on Commons).
  9. Update the commons: File:MediaWiki database schema latest.svg redirect.

Layout[edit]

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

Background: #C5E8B4

Background: #D8FFFC

Background: #FFF1C2

Background: #C5E8B4

Pages Revisions Link tables

Background: #FFF1C2

Background: #FFF1C2

Background: #FFF1C2

Statistics Search Maintenance Multimedia

Background: #D8FFFC

Background: #D8FFFC

Background: #E8D0C9

Background: #F7CCFF

Interwiki Caching tables ResourceLoader

Background: #E8D0C9

Background: #E8D0C9

Background: #E8D0C9

Credits[edit]

See also[edit]