Database PHP API#
Available since WoltLab Suite 5.2.
While the sql 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. We have added a new PHP-based API to manipulate the database scheme which can be used in combination with the script package installation plugin that skips parts that already exist:
1 2 3 4 5 6 7 8 9 10 |
|
All of the relevant components can be found in the wcf\system\database\table
namespace.
With WoltLab Suite 5.4, you should use the new database package installation plugin for which you only have to return the array of affected database tables:
1 2 3 |
|
Database Tables#
There are two classes representing database tables: DatabaseTable
and PartialDatabaseTable
.
If a new table should be created, use DatabaseTable
.
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:
If the tables does not already exist, a table represented by PartialDatabaseTable
will cause an exception (while a DatabaseTable
table will simply be created).
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:
1 2 3 4 5 6 7 8 9 10 |
|
To update a table, the same code as above can be used, except for PartialDatabaseTable
being used instead of DatabaseTable
.
To drop a table, only the drop()
method has to be called:
1 2 |
|
Columns#
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.
Such instances are created similarly to database table objects using the create()
factory method and passing the column name as the parameter.
Every column type supports the following methods:
defaultValue($defaultValue)
sets the default value of the column (default: none).drop()
to drop the column.notNull($notNull = true)
sets if the value of the column can beNULL
(default:false
).
Depending on the specific column class implementing additional interfaces, the following methods are also available:
IAutoIncrementDatabaseTableColumn::autoIncrement($autoIncrement = true)
sets if the value of the colum is auto-incremented.IDecimalsDatabaseTableColumn::decimals($decimals)
sets the number of decimals the column supports.IEnumDatabaseTableColumn::enumValues(array $values)
sets the predetermined set of valid values of the column.ILengthDatabaseTableColumn::length($length)
sets the (maximum) length of the column.
Additionally, there are some additionally classes of commonly used columns with specific properties:
DefaultFalseBooleanDatabaseTableColumn
(atinyint
column with length1
, default value0
and whose values cannot benull
)DefaultTrueBooleanDatabaseTableColumn
(atinyint
column with length1
, default value1
and whose values cannot benull
)NotNullInt10DatabaseTableColumn
(aint
column with length10
and whose values cannot benull
)NotNullVarchar191DatabaseTableColumn
(avarchar
column with length191
and whose values cannot benull
)NotNullVarchar255DatabaseTableColumn
(avarchar
column with length255
and whose values cannot benull
)ObjectIdDatabaseTableColumn
(aint
column with length10
, whose values cannot benull
, and whose values are auto-incremented)
Examples:
1 2 3 4 5 6 7 |
|
Foreign Keys#
Foreign keys are represented by DatabaseTableForeignKey
objects:
1 2 3 4 5 |
|
The supported actions for onDelete()
and onUpdate()
are CASCADE
, NO ACTION
, and SET NULL
.
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.
DatabaseTableForeignKey::create()
also supports the foreign key name as a parameter.
If it is not present, DatabaseTable::foreignKeys()
will automatically set one based on the foreign key's data.
Indices#
Indices are represented by DatabaseTableIndex
objects:
1 2 3 |
|
There are four different types: DatabaseTableIndex::DEFAULT_TYPE
(default), DatabaseTableIndex::PRIMARY_TYPE
, DatabaseTableIndex::UNIQUE_TYPE
, and DatabaseTableIndex::FULLTEXT_TYPE
.
For primary keys, there is also the DatabaseTablePrimaryIndex
class which automatically sets the type to DatabaseTableIndex::PRIMARY_TYPE
.
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.
The index name is specified as the parameter to DatabaseTableIndex::create()
.
It is strongly recommended to specify an explicit name (WoltLab/WCF#4505).
If no name is given, DatabaseTable::indices()
will automatically set one based on the index data.