Differential D25007 Diff 21 src/docs/contributor/database.diviner

Changeset 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` \|
				chrisUnsubmitted Done Inline Actions 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 chris: Is there a task tracking this yet? Looks like an edit at…
				MatthewAuthorUnsubmitted Done Inline Actions T15016 I'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}