Manual:generateSchemaSql.php


DetailsEdit

This maintenance script build SQL files from abstract JSON files. The feature to generate SQL files from abstract JSON file was introduced in MediaWiki 1.35 by RFC: T191231. MediaWiki is using Doctrine DBAL library to generate DDL files from the abstractions. Please read more here.

OptionsEdit

generateSchemaSql.php accepts following three options.

Option Default value
--json Path to the json file tables.json
--sql Path to output tables-generated.sql
--type Outout database type
Can be either 'mysql', 'sqlite', or 'postgres'
mysql

UsageEdit

Example actorTable.json
[
	{
		"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" ]
	}
]


Generating MySQL file from actorTable.json
Terminal


Output:-

-- This file is automatically generated using maintenance/generateSchemaSql.php.
-- Source: actorTable.json
-- Do not modify this file directly.
-- See https://www.mediawiki.org/wiki/Manual:Schema_changes
CREATE TABLE /*_*/actor (
  actor_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  actor_user INT UNSIGNED DEFAULT NULL,
  actor_name VARBINARY(255) NOT NULL,
  UNIQUE INDEX actor_user (actor_user),
  UNIQUE INDEX actor_name (actor_name),
  PRIMARY KEY(actor_id)
) /*$wgDBTableOptions*/;


Generating SQLite file from actorTable.json
Terminal


Output:-

-- This file is automatically generated using maintenance/generateSchemaSql.php.
-- Source: a.json
-- Do not modify this file directly.
-- See https://www.mediawiki.org/wiki/Manual:Schema_changes
CREATE TABLE /*_*/actor (
  actor_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  actor_user INTEGER UNSIGNED DEFAULT NULL,
  actor_name BLOB NOT NULL
);

CREATE UNIQUE INDEX actor_user ON /*_*/actor (actor_user);

CREATE UNIQUE INDEX actor_name ON /*_*/actor (actor_name);