Something went wrong. Try again.
@recaptime-dev's working patches + fork for Phorge, a community fork of Phabricator. (Upstream dev and stable branches are at upstream/main and upstream/stable respectively.) hq.recaptime.dev/wiki/Phorge
phorge phabricator
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176@title Database Schema@group developerThis document describes key components of the database schema and should answerquestions like how to store new types of data.= Database System =Phabricator uses MySQL with InnoDB engine. The only exception is the`search_documentfield` table which uses MyISAM because MySQL doesn't supportfulltext search in InnoDB.Let us know if you need to use other database system: @{article:Give Feedback!Get Support!}.= PHP Drivers =Phabricator supports [[ http://www.php.net/book.mysql | MySQL ]] and[[ http://www.php.net/book.mysqli | MySQLi ]] PHP extensions. Most installationsuse MySQL but MySQLi should work equally well.= Databases =Each Phabricator application has its own database. The names are prefixed by`phabricator_`. This design has two advantages:* Each database is easier to comprehend and to maintain.* We don't do cross-database joins so each database can live on its own machine which is useful for load-balancing.= Connections =Phabricator specifies if it will use any opened connection just for reading oralso for writing. This allows opening write connections to master and readconnections to slave in master/slave replication. It is useful forload-balancing.= Tables =Each table name is prefixed by its application. For example, Differentialrevisions are stored in database `phabricator_differential` and table`differential_revision`. This duplicity allows easy recognition of the table inDarkConsole (see @{article:Using DarkConsole}) and other places.The exception is tables which share the same schema over different databasessuch as `edge`.We use lower-case table names with words separated by underscores. The reason isthat MySQL can be configured (with `lower_case_table_names`) to lower-case thetable names anyway.= Column Names =Phabricator uses camelCase names for columns. The main advantage is that theydirectly map to properties in PHP classes.Don't use MySQL reserved words (such as `order`) for column names.= Data Types =Phabricator uses `int unsigned` columns for storing dates instead of `date` or`datetime`. We don't need to care about time-zones in both MySQL and PHP becauseof it. The other reason is that PHP internally uses numbers for storing dates.Phabricator uses UTF-8 encoding for storing all text data. We use`utf8_general_ci` collation for free-text and `utf8_bin` for identifiers.We don't use the `enum` data type because each change to the list of possiblevalues requires altering the table (which is slow with big tables). We usenumbers (or short strings in some cases) mapped to PHP constants instead.= JSON =Some data don't require structured access - you don't need to filter or order bythem. We store these data as text fields in JSON format. This approach hasseveral advantages:* If we decide to add another unstructured field then we don't need to alter the table (which is slow for big tables in MySQL).* Table structure is not cluttered by fields which could be unused most of the time.An example of such usage can be found in column`differential_diffproperty.data`.= Primary Keys =Most tables have auto-increment column named `id`. However creating such columnis not required for tables which are not usually directly referenced (such astables expressing M:N relations). Example of such table is`differential_relationship`.= Indexes =Create all indexes necessary for fast query execution in most cases. Don'tcreate indexes which are not used. You can analyze queries @{article:UsingDarkConsole}.Older MySQL versions are not able to use indexes for tuple search:`(a, b) IN ((%s, %d), (%s, %d))`. Use `AND` and `OR` instead:`((a = %s AND b = %d) OR (a = %s AND b = %d))`.= Foreign Keys =We don't use InnoDB's foreign keys because our application is so great thatno inconsistencies can arise. It will just slow us down.= PHIDs =Each globally referencable object in Phabricator has its associated PHID(Phabricator ID) which serves as a global identifier. We use PHIDs forreferencing data in different databases.We use both autoincrementing IDs and global PHIDs because each is useful indifferent contexts. Autoincrementing IDs are chronologically ordered and allowus to construct short, human-readable object names (like D2258) and URIs. GlobalPHIDs allow us to represent relationships between different types of objects ina homogenous way.For example, the concept of "subscribers" is more powerfully done with PHIDsbecause we could theoretically have users, projects, teams, and more allas "subscribers" of other objects. Using an ID column we would need to add a"type" column to avoid ID collision; using PHIDs does not require thisadditional column.= Transactions =Transactional code should be written using transactions. Example of such code isinserting multiple records where one doesn't make sense without the other orselecting data later used for update. See chapter in @{class:LiskDAO}.= Advanced Features =We don't use MySQL advanced features such as triggers, stored procedures orevents because we like expressing the application logic in PHP more than in SQL.Some of these features (especially triggers) can also cause big confusion.Avoiding these advanced features is also good for supporting other databasesystems (which we don't support anyway).= Schema Denormalization =Phabricator uses schema denormalization for performance reasons sparingly. Tryto avoid it if possible.= Changing the Schema =There are three simple steps to update the schema:# Create a `.sql` file in `resources/sql/patches/`. This file should: - Contain the approprate MySQL commands to update the schema. - Be named as `YYYYMMDD.patchname.ext`. For example, `20130217.example.sql`. - Use `${NAMESPACE}` rather than `phabricator` for database names. - Use `COLLATE utf8_bin` for any columns that are to be used as identifiers,such as PHID columns. Otherwise, use `COLLATE utf8_general_ci`. - Name all indexes so it is possible to delete them later.# Edit `src/infrastructure/storage/patch/PhabricatorBuiltinPatchList.php` andadd your patch to @{method@phabricator:PhabricatorBuiltinPatchList::getPatches}.# Run `bin/storage upgrade`.It is also possible to create more complex patches in PHP for data migration(due to schema changes or otherwise.) However, the schema changes themselvesshould be done in separate `.sql` files. Order can be guaranteed by editing`src/infrastructure/storage/patch/PhabricatorBuiltinPatchList.php`appropriately.See the[[https://secure.phabricator.com/rPb39175342dc5bee0c2246b05fa277e76a7e96ed3| commit adding policy storage for Paste ]] for a reasonable example of the codechanges.= See Also =* @{class:LiskDAO}* @{class:PhabricatorPHID}