docs/package_database-php-api.md

   1 # Database PHP API
   2
   3 !!! info "Available since WoltLab Suite 5.2."
   4
   5 While the [sql](package_pip_sql.md) package installation plugin supports adding and removing tables, columns, and indices, it is not able to handle cases where the added table, column, or index already exist.
   6 We have added a new PHP-based API to manipulate the database scheme which can be used in combination with the [script](package_pip_script.md) package installation plugin that skips parts that already exist:
   7
   8 ```php
   9 $tables = [
  10         // TODO
  11 ];
  12
  13 (new DatabaseTableChangeProcessor(
  14         /** @var ScriptPackageInstallationPlugin $this */
  15         $this->installation->getPackage(),
  16         $tables,
  17         WCF::getDB()->getEditor())
  18 )->process();
  19 ```
  20
  21 All of the relevant components can be found in the `wcf\system\database\table` namespace.
  22
  23
  24 ## Database Tables
  25
  26 There are two classes representing database tables: `DatabaseTable` and `PartialDatabaseTable`.
  27 If a new table should be created, use `DatabaseTable`.
  28 In all other cases, `PartialDatabaseTable` should be used as it provides an additional save-guard against accidentally creating a new table by having a typo in the table name:
  29 If the tables does not already exist, a table represented by `PartialDatabaseTable` will cause an exception (while a `DatabaseTable` table will simply be created).
  30
  31 To create a table, a `DatabaseTable` object with the table's name as to be created and table's columns, foreign keys and indices have to be specified:
  32
  33 ```php
  34 DatabaseTable::create('foo1_bar')
  35     ->columns([
  36         // columns
  37     ])
  38     ->foreignKeys([
  39         // foreign keys
  40     ])
  41     ->indices([
  42         // indices
  43     ])
  44 ```
  45
  46 To update a table, the same code as above can be used, except for `PartialDatabaseTable` being used instead of `DatabaseTable`.
  47
  48 To drop a table, only the `drop()` method has to be called:
  49
  50 ```php
  51 PartialDatabaseTable::create('foo1_bar')
  52     ->drop()
  53 ```
  54
  55
  56 ## Columns
  57
  58 To represent a column of a database table, you have to create an instance of the relevant column class found in the `wcf\system\database\table\column` namespace.
  59 Such instances are created similarly to database table objects using the `create()` factory method and passing the column name as the parameter.
  60
  61 Every column type supports the following methods:
  62
  63 - `defaultValue($defaultValue)` sets the default value of the column (default: none).
  64 - `drop()` to drop the column.
  65 - `notNull($notNull = true)` sets if the value of the column can be `NULL` (default: `false`).
  66
  67 Depending on the specific column class implementing additional interfaces, the following methods are also available:
  68
  69 - `IAutoIncrementDatabaseTableColumn::autoIncrement($autoIncrement = true)` sets if the value of the colum is auto-incremented.
  70 - `IDecimalsDatabaseTableColumn::decimals($decimals)` sets the number of decimals the column supports.
  71 - `IEnumDatabaseTableColumn::enumValues(array $values)` sets the predetermined set of valid values of the column.
  72 - `ILengthDatabaseTableColumn::length($length)` sets the (maximum) length of the column.
  73
  74 Additionally, there are some additionally classes of commonly used columns with specific properties:
  75
  76 - `DefaultFalseBooleanDatabaseTableColumn` (a `tinyint` column with length `1`, default value `0` and whose values cannot be `null`)
  77 - `DefaultTrueBooleanDatabaseTableColumn` (a `tinyint` column with length `0`, default value `0` and whose values cannot be `null`)
  78 - `NotNullInt10DatabaseTableColumn` (a `int` column with length `10` and whose values cannot be `null`)
  79 - `NotNullVarchar191DatabaseTableColumn` (a `varchar` column with length `191` and whose values cannot be `null`)
  80 - `NotNullVarchar255DatabaseTableColumn` (a `varchar` column with length `255` and whose values cannot be `null`)
  81 - `ObjectIdDatabaseTableColumn` (a `int` column with length `10`, whose values cannot be `null`, and whose values are auto-incremented)
  82
  83 Examples:
  84
  85 ```php
  86 DefaultFalseBooleanDatabaseTableColumn::create('isDisabled')
  87
  88 NotNullInt10DatabaseTableColumn::create('fooTypeID')
  89
  90 SmallintDatabaseTableColumn::create('bar')
  91         ->length(5)
  92         ->notNull()
  93 ```
  94
  95
  96 ## Foreign Keys
  97
  98 Foreign keys are represented by `DatabaseTableForeignKey` objects:
  99
 100 ```php
 101 DatabaseTableForeignKey::create()
 102         ->columns(['fooID'])
 103         ->referencedTable('wcf1_foo')
 104         ->referencedColumns(['fooID'])
 105         ->onDelete('CASCADE')
 106 ```
 107
 108 The supported actions for `onDelete()` and `onUpdate()` are `CASCADE`, `NO ACTION`, and `SET NULL`.
 109 To drop a foreign key, all of the relevant data to create the foreign key has to be present and the `drop()` method has to be called.
 110
 111 `DatabaseTableForeignKey::create()` also supports the foreign key name as a parameter.
 112 If it is not present, `DatabaseTable::foreignKeys()` will automatically set one based on the foreign key's data.
 113
 114
 115 ## Indices
 116
 117 Indices are represented by `DatabaseTableIndex` objects:
 118
 119 ```php
 120 DatabaseTableIndex::create()
 121         ->type(DatabaseTableIndex::UNIQUE_TYPE)
 122         ->columns(['fooID'])
 123 ```
 124
 125 There are four different types: `DatabaseTableIndex::DEFAULT_TYPE` (default), `DatabaseTableIndex::PRIMARY_TYPE`, `DatabaseTableIndex::UNIQUE_TYPE`, and `DatabaseTableIndex::FULLTEXT_TYPE`.
 126 For primary keys, there is also the `DatabaseTablePrimaryIndex` class which automatically sets the type to `DatabaseTableIndex::PRIMARY_TYPE`.
 127 To drop a index, all of the relevant data to create the index has to be present and the `drop()` method has to be called.
 128
 129 `DatabaseTableIndex::create()` also supports the index name as a parameter.
 130 If it is not present, `DatabaseTable::indices()` will automatically set one based on the index data.