Changeset View
Changeset View
Standalone View
Standalone View
src/docs/contributor/database.diviner
@title Database Schema | @title Database Schema | ||||
@group developer | @group developer | ||||
This document describes key components of the database schema and should answer | This document describes key components of the database schema and should answer | ||||
questions like how to store new types of data. | questions like how to store new types of data. | ||||
Database System | Database System | ||||
=============== | =============== | ||||
Phabricator uses MySQL or another MySQL-compatible database (like MariaDB | Phorge uses MySQL or another MySQL-compatible database (like MariaDB | ||||
or Amazon RDS). | or Amazon RDS). | ||||
Phabricator uses the InnoDB table engine. The only exception is the | Phorge uses the InnoDB table engine. The only exception is the | ||||
`search_documentfield` table which uses MyISAM because MySQL doesn't support | `search_documentfield` table which uses MyISAM because MySQL doesn't support | ||||
fulltext search in InnoDB (recent versions do, but we haven't added support | fulltext search in InnoDB (recent versions do, but we haven't added support | ||||
yet). | yet). | ||||
We are unlikely to ever support other incompatible databases like PostgreSQL or | We are unlikely to ever support other incompatible databases like PostgreSQL or | ||||
SQLite. | SQLite. | ||||
PHP Drivers | PHP Drivers | ||||
=========== | =========== | ||||
Phabricator supports [[ http://www.php.net/book.mysql | MySQL ]] and | Phorge supports [[ http://www.php.net/book.mysql | MySQL ]] and | ||||
[[ http://www.php.net/book.mysqli | MySQLi ]] PHP extensions. | [[ http://www.php.net/book.mysqli | MySQLi ]] PHP extensions. | ||||
Databases | Databases | ||||
========= | ========= | ||||
Each Phabricator application has its own database. The names are prefixed by | Each Phorge application has its own database. The names are prefixed by | ||||
`phabricator_` (this is configurable). | `phorge_` (this is configurable). | ||||
Phabricator uses a separate database for each application. To understand why, | Phorge uses a separate database for each application. To understand why, | ||||
see @{article:Why does Phabricator need so many databases?}. | see @{article:Why does Phorge need so many databases?}. | ||||
Connections | Connections | ||||
=========== | =========== | ||||
Phabricator specifies if it will use any opened connection just for reading or | Phorge specifies if it will use any opened connection just for reading or | ||||
also for writing. This allows opening write connections to a primary and read | also for writing. This allows opening write connections to a primary and read | ||||
connections to a replica in primary/replica setups (which are not actually | connections to a replica in primary/replica setups (which are not actually | ||||
supported yet). | supported yet). | ||||
Tables | Tables | ||||
====== | ====== | ||||
Most table names are prefixed by their application names. For example, | Most table names are prefixed by their application names. For example, | ||||
Differential revisions are stored in database `phabricator_differential` and | Differential revisions are stored in database `phorge_differential` and | ||||
table `differential_revision`. This generally makes queries easier to recognize | table `differential_revision`. This generally makes queries easier to recognize | ||||
and understand. | and understand. | ||||
The exception is a few tables which share the same schema over different | The exception is a few tables which share the same schema over different | ||||
databases such as `edge`. | databases such as `edge`. | ||||
We use lower-case table names with words separated by underscores. | We use lower-case table names with words separated by underscores. | ||||
Column Names | Column Names | ||||
============ | ============ | ||||
Phabricator uses `camelCase` names for columns. The main advantage is that they | Phorge uses `camelCase` names for columns. The main advantage is that they | ||||
directly map to properties in PHP classes. | directly map to properties in PHP classes. | ||||
Don't use MySQL reserved words (such as `order`) for column names. | Don't use MySQL reserved words (such as `order`) for column names. | ||||
Data Types | Data Types | ||||
========== | ========== | ||||
Phabricator defines a set of abstract data types (like `uint32`, `epoch`, and | Phorge defines a set of abstract data types (like `uint32`, `epoch`, and | ||||
`phid`) which map to MySQL column types. The mapping depends on the MySQL | `phid`) which map to MySQL column types. The mapping depends on the MySQL | ||||
version. | version. | ||||
Phabricator uses `utf8mb4` character sets where available (MySQL 5.5 or newer), | Phorge uses `utf8mb4` character sets where available (MySQL 5.5 or newer), | ||||
and `binary` character sets in most other cases. The primary motivation is to | and `binary` character sets in most other cases. The primary motivation is to | ||||
allow 4-byte unicode characters to be stored (the `utf8` character set, which | allow 4-byte unicode characters to be stored (the `utf8` character set, which | ||||
is more widely available, does not support them). On newer MySQL, we use | is more widely available, does not support them). On newer MySQL, we use | ||||
`utf8mb4` to take advantage of improved collation rules. | `utf8mb4` to take advantage of improved collation rules. | ||||
Phabricator stores dates with an `epoch` abstract data type, which maps to | Phorge stores dates with an `epoch` abstract data type, which maps to | ||||
`int unsigned`. Although this makes dates less readable when browsing the | `int unsigned`. Although this makes dates less readable when browsing the | ||||
database, it makes date and time manipulation more consistent and | database, it makes date and time manipulation more consistent and | ||||
straightforward in the application. | straightforward in the application. | ||||
We don't use the `enum` data type because each change to the list of possible | We don't use the `enum` data type because each change to the list of possible | ||||
values requires altering the table (which is slow with big tables). We use | values requires altering the table (which is slow with big tables). We use | ||||
numbers (or short strings in some cases) mapped to PHP constants instead. | numbers (or short strings in some cases) mapped to PHP constants instead. | ||||
▲ Show 20 Lines • Show All 42 Lines • ▼ Show 20 Lines | |||||
significant issues with data inconsistency that foreign keys could help prevent. | significant issues with data inconsistency that foreign keys could help prevent. | ||||
Empirically, we have witnessed first hand as `ON DELETE CASCADE` relationships | Empirically, we have witnessed first hand as `ON DELETE CASCADE` relationships | ||||
accidentally destroy huge amounts of data. We may pursue foreign keys | accidentally destroy huge amounts of data. We may pursue foreign keys | ||||
eventually, but there isn't a strong case for them at the present time. | eventually, but there isn't a strong case for them at the present time. | ||||
PHIDs | PHIDs | ||||
===== | ===== | ||||
Each globally referencable object in Phabricator has an associated PHID | Each globally referencable object in Phorge has an associated PHID | ||||
("Phabricator ID") which serves as a global identifier, similar to a GUID. | ("Phorge ID") which serves as a global identifier, similar to a GUID. | ||||
We use PHIDs for referencing data in different databases. | We use PHIDs for referencing data in different databases. | ||||
We use both auto-incrementing IDs and global PHIDs because each is useful in | We use both auto-incrementing IDs and global PHIDs because each is useful in | ||||
different contexts. Auto-incrementing IDs are meaningfully ordered and allow | different contexts. Auto-incrementing IDs are meaningfully ordered and allow | ||||
us to construct short, human-readable object names (like `D2258`) and URIs. | us to construct short, human-readable object names (like `D2258`) and URIs. | ||||
Global PHIDs allow us to represent relationships between different types of | Global PHIDs allow us to represent relationships between different types of | ||||
objects in a homogeneous way. | objects in a homogeneous way. | ||||
For example, infrastructure like "subscribers" can be implemented easily with | For example, infrastructure like "subscribers" can be implemented easily with | ||||
PHID relationships: different types of objects (users, projects, mailing lists) | PHID relationships: different types of objects (users, projects, mailing lists) | ||||
are permitted to subscribe to different types of objects (revisions, tasks, | are permitted to subscribe to different types of objects (revisions, tasks, | ||||
etc). Without PHIDs, we would need to add a "type" column to avoid ID collision; | etc). Without PHIDs, we would need to add a "type" column to avoid ID collision; | ||||
using PHIDs makes implementing features like this simpler. | using PHIDs makes implementing features like this simpler. | ||||
For more information, see @{article:Handles Technical Documentation} | |||||
Transactions | Transactions | ||||
============ | ============ | ||||
Transactional code should be written using transactions. Example of such code is | Transactional code should be written using transactions. Example of such code is | ||||
inserting multiple records where one doesn't make sense without the other, or | inserting multiple records where one doesn't make sense without the other, or | ||||
selecting data later used for update. See chapter in @{class:LiskDAO}. | selecting data later used for update. See chapter in @{class:LiskDAO}. | ||||
Advanced Features | Advanced Features | ||||
================= | ================= | ||||
We don't use MySQL advanced features such as triggers, stored procedures or | We don't use MySQL advanced features such as triggers, stored procedures or | ||||
events because we like expressing the application logic in PHP more than in SQL. | events because we like expressing the application logic in PHP more than in SQL. | ||||
Some of these features (especially triggers) can also cause a great deal of | Some of these features (especially triggers) can also cause a great deal of | ||||
confusion, and are generally more difficult to debug, profile, version control, | confusion, and are generally more difficult to debug, profile, version control, | ||||
update, and understand than application code. | update, and understand than application code. | ||||
Schema Denormalization | Schema Denormalization | ||||
====================== | ====================== | ||||
Phabricator uses schema denormalization sparingly. Avoid denormalization unless | Phorge uses schema denormalization sparingly. Avoid denormalization unless | ||||
there is a compelling reason (usually, performance) to denormalize. | there is a compelling reason (usually, performance) to denormalize. | ||||
Schema Changes and Migrations | Schema Changes and Migrations | ||||
============================= | ============================= | ||||
To create a new schema change or migration: | To create a new schema change or migration: | ||||
**Create a database patch**. Database patches go in | **Create a database patch**. Database patches go in | ||||
Show All 9 Lines | |||||
statement per patch). | statement per patch). | ||||
**Use namespace and character set variables**. When defining a `.sql` patch, | **Use namespace and character set variables**. When defining a `.sql` patch, | ||||
you should use these variables instead of hard-coding namespaces or character | you should use these variables instead of hard-coding namespaces or character | ||||
set names: | set names: | ||||
| Variable | Meaning | Notes | | | Variable | Meaning | Notes | | ||||
|---|---|---| | |---|---|---| | ||||
| `{$NAMESPACE}` | Storage Namespace | Defaults to `phabricator` | | | `{$NAMESPACE}` | Storage Namespace | Defaults to `phabricator` | | ||||
chris: Is there a task tracking this yet? Looks like an edit at… | |||||
Done Inline ActionsI've also reverted this change in the meantime. Matthew: T15016
I've also reverted this change in the meantime. | |||||
| `{$CHARSET}` | Default Charset | Mostly used to specify table charset | | | `{$CHARSET}` | Default Charset | Mostly used to specify table charset | | ||||
| `{$COLLATE_TEXT}` | Text Collation | For most text (case-sensitive) | | | `{$COLLATE_TEXT}` | Text Collation | For most text (case-sensitive) | | ||||
| `{$COLLATE_SORT}` | Sort Collation | For sortable text (case-insensitive) | | | `{$COLLATE_SORT}` | Sort Collation | For sortable text (case-insensitive) | | ||||
| `{$CHARSET_FULLTEXT}` | Fulltext Charset | Specify explicitly for fulltext | | | `{$CHARSET_FULLTEXT}` | Fulltext Charset | Specify explicitly for fulltext | | ||||
| `{$COLLATE_FULLTEXT}` | Fulltext Collate | Specify explicitly for fulltext | | | `{$COLLATE_FULLTEXT}` | Fulltext Collate | Specify explicitly for fulltext | | ||||
**Test your patch**. Run `bin/storage upgrade` to test your patch. | **Test your patch**. Run `bin/storage upgrade` to test your patch. | ||||
See Also | See Also | ||||
======== | ======== | ||||
- @{class:LiskDAO} | - @{class:LiskDAO} |
Content licensed under Creative Commons Attribution-ShareAlike 4.0 (CC-BY-SA) unless otherwise noted; code licensed under Apache 2.0 or other open source licenses. · CC BY-SA 4.0 · Apache 2.0
Is there a task tracking this yet? Looks like an edit at src/applications/config/option/PhabricatorMySQLConfigOptions.php:38, just want to make sure we don't forget down the road