Spiral Database layer provides the ability to read and analyze basic properties of a given database or a given table. DBAL layer include set of "abstract" types assigned to each column based on DBMS specific mapping in order to unify different engines.
To check if database has table use hasTable:
if ($database->hasTable('users')) {
//...
}
Read how to get Database instances here.
Receive all database tables (array of Spiral\Database\Table):
foreach ($database->getTables() as $table) {
dump($table->getName());
}
Only tables specific to database prefix (if any) are resulted.
Schema Reader/Builder (AbstractTable) is available using getSchema method:
foreach ($database->getTables() as $table) {
dump($table->getSchema());
}
The AbstractTable provides low level access to table information such as column types (internal and abstract), indexes, foreign keys and etc. You can use this information to perform database export, build your own ORM or migration mechanism (see schema declaration).
Table primary keys:
dump($schema->getPrimaryKeys());
Table indexes:
foreach ($schema->getIndexes() as $index) {
dump($index->getName());
dump($index->getColumns());
dump($index->isUnique());
}
Table foreign keys (references):
foreach ($schema->getForeigns() as $foreign) {
dump($foreign->getColumn()); //Local column name
dump($foreign->getForeignTable()); //Global table name!
dump($foreign->getForeignKey());
dump($foreign->getDeleteRule()); //NO ACTION, CASCADE
dump($foreign->getUpdateRule()); //NO ACTION, CASCADE
}
Attention,
getForeignTablereturns full table name ignoring db prefix.
Table columns:
foreach ($schema->getColumns() as $column) {
dump($column->getName());
dump($column->getType()); //Internal database type
dump($column->abstractType()); //Abstract type like string, bigInt, enum, text and etc.
dump($column->phpType()); //PHP type: int, float, string, bool
dump($column->getDefaultValue()); //Can be instance of SqlFragment
dump($column->getSize()); //Only for strings and decimal values
dump($column->getPrecision()); //Decimals only
dump($column->getScale()); //Decimals only
dump($column->isNullable());
dump($column->getEnumValues()); //Only for enums
dump($column->getConstraints());
dump($column->sqlStatement()); //Column creation syntax
}
Some types can be mapped incorrectly if the table was created outside migrations or ORM.
You can find a complete list of available abstract types here.
You can also use console commands to get information about configured tables and their schemas:
| Command | Description |
|---|---|
| db:list | Get list of databases, their tables and records count. |
| db:describe | View table schema of default or specific database. |
> ./spiral.cli db:describe people --database=postgres
Columns of postgres.people:
+---------+-------------------------+----------------+-----------+------------------------------------+
| Column: | Database Type: | Abstract Type: | PHP Type: | Default Value: |
+---------+-------------------------+----------------+-----------+------------------------------------+
| id | bigserial | bigPrimary | int | nextval('people_id_seq'::regclass) |
| name | character varying (255) | string | string | --- |
| income | numeric (20,2) | decimal | float | --- |
| cityID | bigint | bigInteger | int | --- |
+---------+-------------------------+----------------+-----------+------------------------------------+
Indexes of postgres.people:
+-----------------------------------+-------+----------+
| Name: | Type: | Columns: |
+-----------------------------------+-------+----------+
| people_index_income_54ea144908c7c | INDEX | income |
+-----------------------------------+-------+----------+
Foreign keys of postgres.people:
+-------------------------------------+---------+----------------+-----------------+------------+------------+
| Name: | Column: | Foreign Table: | Foreign Column: | On Delete: | On Update: |
+-------------------------------------+---------+----------------+-----------------+------------+------------+
| people_foreign_cityID_550ef169f1818 | cityID | cities | id | CASCADE | NO ACTION |
+-------------------------------------+---------+----------------+-----------------+------------+------------+