Manual:Schema changes

This is a help page to build schema change patches for mediawiki core and its extensions:

Manual
In this method which is used until 2020, when making a schema change:
 * You need to change tables.sql in two different places (maintenance/tables.sql for mysql and maintenance/postgres/tables.sql for postgres)
 * You need to make a sql patch as the upgrade path of current installations for mysql and put it in maintenance/archives/)
 * If other RDBMS types don't work with that patch, you need to make a dedicated patch for them. For example, sqlite doesn't 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
 * Then you need to add these patch sql files into MysqlUpdater, SqliteUpdater and PostgresUpdater.

Examples

 * Dropping a column (Note that back then we had five DBMS systems 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. It doesn't 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:
 * Change the tables.json structure.
 * Run maintenance script to generate the three DBMS .sql files:
 * Build the schema patches the same way you build it manually (See above). This will change soon.
 * Do not forget to checkout your changes and automatically generated .sql 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
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 doesn't have ALTER TABLE, meaning it needs to know the schema to build a schema change .sql file using temporary tables.