Manual:Schema changes

This is a help page describing how to build schema change patches for MediaWiki core and its extensions for people who need to change the database layout as part of their development work.

Glossary

 * Schema: Current database layout of mediawiki.
 * Schema change: An atomic part of schema migration that is being added through a commit. For example "Adding table foo", "Dropping column bar from table baz" and so on.
 * Database management system (DBMS): The underlying technology handling the mediawiki database. The supported ones in mediawiki core are: MySQL, Sqlite and Postgres. It can be more using extensions.
 * Data definition language (DDL): Syntax that defines schema and schema changes (It can differ in different DBMSes). For example "ALTER TABLE", "DROP COLUMN". They are saved as ".sql" files.
 * Database Abstraction Layer (DBAL): The bridge between DBMS-independent database schema and schema change definitions and the actual DDLs.

Manual
In this method which is used until 2020, when making a schema change:
 * 1) Change tables.sql in two different places (maintenance/tables.sql for MySQL and maintenance/postgres/tables.sql for PostgresQL)
 * 2) Make a schema change DDL file as the upgrade path of current installations for MySQL and put the file in maintenance/archives/)
 * 3) * If other DBMS types don't work with that patch, you need to make a dedicated patch for them. For example, Sqlite does not have ALTER TABLE, meaning you need to make a temporary table, copy the data, drop the old table and rename the new table to the old name. here's an example
 * 4) Wire these DDL files (from step 2) into MysqlUpdater, SqliteUpdater and PostgresUpdater.

Examples

 * Dropping a column (Note that we used to support five DBMSes instead of three)
 * Changing indexes
 * Adding a new table

Automatically generated
We are working to improve this. First step is to overhaul schemas. You can find the abstract schema in "maintenance/tables.json". This file does not contain all tables yet and for the tables that are not abstracted you need to follow the old way. But if the table exists in "tables.json":
 * 1) Change the tables.json structure.
 * 2) Run maintenance script to generate the three DDL files:
 * 3) Build the schema patches the same way you build it manually (See above). This will change soon.
 * 4) Do not forget to checkout your changes and automatically generated DDL files in git when making the patch.

Future changes
In future, for making a schema change, you will make a json file with snapshot of before and after abstract schemas for the table (one schema change per table please). Then you will run a maintenance script in a similar manner and it will diff between two tables and then automatically generates the schema change .sql files.

Example abstract schema change
The two tables are the same but type of "actor_user" has changed from "integer" to "bigint". The reason for diffing instead of abstracting the change itself is that SQlite does not have ALTER TABLE, meaning DBAL needs to know the schema to build a schema change DDL file using temporary tables.