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:

$ php maintenance/generateSchemaSql.php --json maintenance/tables.json --sql maintenance/tables-generated.sql --type=mysql
$ php maintenance/generateSchemaSql.php --json maintenance/tables.json --sql maintenance/sqlite/tables-generated.sql --type=sqlite
$ php maintenance/generateSchemaSql.php --json maintenance/tables.json --sql maintenance/postgres/tables-generated.sql --type=postgres

• 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.

Example abstract schema

[
	{
		"name": "actor",
		"comment": "The \"actor\" table associates user names or IP addresses with integers for the benefit of other tables that need to refer to either logged-in or logged-out users. If something can only ever be done by logged-in users, it can refer to the user table directly.",
		"columns": [
			{
				"name": "actor_id",
				"comment": "Unique ID to identify each actor",
				"type": "bigint",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "actor_user",
				"comment": "Key to user.user_id, or NULL for anonymous edits",
				"type": "integer",
				"options": { "unsigned": true, "notnull": false }
			},
			{
				"name": "actor_name",
				"comment": "Text username or IP address",
				"type": "binary",
				"options": { "length": 255, "notnull": true }
			}
		],
		"indexes": [
			{ "name": "actor_user", "columns": [ "actor_user" ], "unique": true },
			{ "name": "actor_name", "columns": [ "actor_name" ], "unique": true }
		],
		"pk": [ "actor_id" ]
	}
]

Notes

• The default in Doctrine DBAL is "notnull": true. If you want your column to be nullable, make it explicit by "notnull": false.
• List of column types of Doctrine DBAL can be found in: https://github.com/doctrine/dbal/blob/2.10.x/lib/Doctrine/DBAL/Types/Types.php

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

{
	"before": {
		"name": "actor",
		"columns": [
			{
				"name": "actor_id",
				"type": "bigint",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "actor_user",
				"type": "integer",
				"options": { "unsigned": true, "notnull": false }
			},
			{
				"name": "actor_name",
				"type": "binary",
				"options": { "length": 255, "notnull": true }
			}
		],
		"indexes": [
			{ "name": "actor_user", "columns": [ "actor_user" ], "unique": true },
			{ "name": "actor_name", "columns": [ "actor_name" ], "unique": true }
		],
		"pk": [ "actor_id" ]
	},
	"after": {
		"name": "actor",
		"columns": [
			{
				"name": "actor_id",
				"type": "bigint",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "actor_user",
				"type": "bigint",
				"options": { "unsigned": true, "notnull": false }
			},
			{
				"name": "actor_name",
				"type": "binary",
				"options": { "length": 255, "notnull": true }
			}
		],
		"indexes": [
			{ "name": "actor_user", "columns": [ "actor_user" ], "unique": true },
			{ "name": "actor_name", "columns": [ "actor_name" ], "unique": true }
		],
		"pk": [ "actor_id" ]
	}
}

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.