Manual:Schema changes/ja

このページは開発作業の一部としてデータベースのレイアウトを変更する必要がある人々のために、MediaWiki コアとその拡張機能のためのスキーマ変更パッチを構築する方法を説明するヘルプ ページです.

用語集

 * スキーマ - MediaWiki の現在のデータベース レイアウト.
 * スキーマの変更 - スキーマ移行のアトミックな部分で、コミットによって追加されるもの. 例えば、「テーブル foo を追加」、「テーブル baz からカラム bar を削除」などです.
 * データベース管理システム (DBMS) - MediaWiki のデータベースを扱う基盤技術. MediaWiki コアでサポートされているものは以下の通りです: MySQL、SQLite、PostgreSQL.  拡張機能を使用するとより多くなる可能性があります.
 * データ定義言語 (DDL) - スキーマやスキーマ変更を定義する構文 (DBMS によって異なる場合がある). 例: 「ALTER TABLE」「DROP COLUMN」.  「.sql」ファイルとして保存されます.
 * データベース抽象レイヤー (DBAL) - DBMS に依存しないデータベース スキーマおよびスキーマ変更定義と実際の DDL との間の橋渡し.

概要
Each schema change needs to handle two parts. First, new installations need to have the new schema instead of the old one and second, old installation must be able to upgrade to the new one. For the first part, we fix the schema DDL files (saved as tables.sql) and for the second part, we provide "ALTER TABLE" patches and wire them into updater logic.

We are at middle of a migration from having one dedicated DDL per DBMS to only one abstracted schema. Depending on the table, you might change several raw SQL files or only change one json file and generate the SQL files using a maintenance script.

手動
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.

例

 * 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. This abstraction is using Doctrine DBAL library to generate DDL files.

If the table exists in "tables.json":


 * 1) Change the tables.json structure.
 * 2) Run maintenance script to generate the three DDL files:
 * 3) Create an abstract schema change .json file (see below) and put it in maintenance/abstractSchemaChanges/ directory
 * 4) Build the schema patches using the maintenance script, for example:
 * 5) Add them to MysqlUpdater, SqliteUpdater, PostgresUpdater
 * 6) Do not forget to checkout your changes and automatically generated DDL files in git when making the patch.

Example patches

 * Renaming four indexes in logging table

注記

 * 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.12.x/lib/Doctrine/DBAL/Types/Types.php

Abstract schema change
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 DDL 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 for most cases, meaning DBAL needs to know the schema to build a schema change DDL file using temporary tables.