# Tables
A `Table` is a table's columns, and what those columns mean as an entity. It is the bridge between SQL and
core's schema layer.
## Declaring one
Inline:
```php
$users = new MMO\pdo\helpers\Table($handler, 'user');
$users->addFields([
'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
'name' => 'VARCHAR(100) NOT NULL UNIQUE',
'email' => 'VARCHAR(200) NULL',
'born' => 'DATE NULL',
]);
```
As a class, when the table is part of your code:
```php
class UserTable extends MMO\pdo\helpers\Table {
protected string $name = 'user';
protected array $fields = [
'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
'name' => 'VARCHAR(100) NOT NULL UNIQUE',
];
}
```
Or as configuration, through the repository (see [Repository](Repository.md)).
The third constructor argument registers the table on the handler; pass `false` to keep it private.
## The column mini-language
A column is its SQL definition, read left to right. Recognised keywords become flags and are removed from the
type; anything else is the type.
| keyword | |
|---|---|
| `PRIMARY` | part of the primary key |
| `KEY`, `INDEX` | indexed |
| `UNIQUE` | unique |
| `NOT`, `NULL` | nullability (`NOT NULL`, or `NULL`) |
| `AUTO_INCREMENT`, `AUTOINCREMENT` | the database generates the value |
| `VISIBLE`, `INVISIBLE`, `FULLTEXT` | passed through to MySQL |
| `DEFAULT x` | the default; a quoted literal keeps its quotes |
| `FOREIGN KEY ref(col)` | an inline single-column reference |
Quoted strings and parenthesised groups are kept whole, so `ENUM('a)b','c')` and `DEFAULT 'o''brien'` parse
correctly.
## Columns become entity properties
`Table::getEntityClass()` generates a `PDOEntity` subclass from the columns, once, and caches it. The mapping:
| SQL | PHP | annotations |
|---|---|---|
| `VARCHAR(n)`, `TEXT`, `BLOB` | `string` | `@maxLength n` |
| `CHAR(n)` | `string` | `@minLength n`, `@maxLength n` |
| `INT`, `BIGINT`, `BIT`, `YEAR` | `int` | `@minimum 0` when `UNSIGNED` |
| `BOOLEAN` | `bool` | |
| `FLOAT`, `DOUBLE`, `DECIMAL`, `NUMERIC`, `REAL` | `float` | `@minimum 0` when `UNSIGNED` |
| `DATE` | `\DateTime` | `@format date` |
| `DATETIME`, `TIMESTAMP` | `\DateTime` | `@format mysql-datetime` |
| `TIME` | `string` | `@format time` |
| `ENUM('a','b')` | `string` | `@var 'a'\|'b'` — a real constraint, validated |
| `SET('a','b')` | `array` | `@var ['a'\|'b']` |
`NULL` makes the property nullable and defaults it to `null`. A `DEFAULT` becomes a PHP default when it can be
expressed as one; an expression such as `CURRENT_TIMESTAMP` cannot, so it is left to the database. A type that
cannot be translated raises an error rather than producing an untyped property.
The generated class is named `MMO\pdo\entities\{table}_{hash}`, where the hash covers the column shape. That
is stable across machines — the name ends up inside serialized entity references — and still distinct for two
same-named tables with different columns.
### Reserved column names
`PDOEntity` already declares some properties. A column named after one of them is **not** redeclared:
- `status` — reuses the inherited `cStatus` property, which stores as its case name. Give the table a
`VARCHAR` column and it round-trips.
- an internal (`flags`, `changes`, `errors`, `links`, `collection`) — reported as an error. Rename the column,
or supply your own entity class.
### Your own entity class
As soon as an entity needs behaviour, write it:
```php
class User extends MMO\pdo\helpers\PDOEntity {
const ID_PROP = 'id';
#[MMO\core\attributes\ArrayProperty()]
protected int $id = 0;
#[MMO\core\attributes\ArrayProperty()]
protected string $name = '';
public function getLabel(): string {return $this->name;}
}
$users->setEntityClass(User::class);
```
`PDOEntity` extends `aEntity`, not `Entity`, on purpose: `Entity` mandates `id`/`label`/`title`/`ctime`/`mtime`
with fixed types, and an arbitrary SQL table has no reason to carry any of them.
## Primary keys
`getIds()` lists the primary columns; `getAutoId()` names the `AUTO_INCREMENT` one, if there is one.
A composite key is supported. Because the framework keys a collection by a single string id, `getId()` folds
the parts with `PDOEntity::ID_SEPARATOR` (ASCII 0x1D); `getIdParts()` gives them back as `column => value`,
which is what a WHERE clause needs, and `debugId()` renders the readable `[1,2]` form for logs.
A table with no primary key produces entities with `ID_PROP = null` and an empty id. They can be read and
inserted, but not looked up or updated by id.
## Queries
```php
$users->create(ifNotExists: true);
$users->drop();
$users->exists();
$users->select('*', ['name' => 'marc']);
$users->selectFirst('*', ['name' => 'marc']);
$users->selectValue('email', ['name' => 'marc']);
$users->selectValues('name');
$users->count(['name' => 'marc']);
$users->search(['name' => 'marc']); //the primary key of each match
$users->insert(['name' => 'marc'], ['name' => 'bob']); //returns the last generated id
$users->store(['name' => 'marc', 'email' => 'x']); //insert, or update on key conflict
$users->update(['email' => 'x'], ['name' => 'marc']); //returns rows changed
$users->updateById(['id' => 3, 'email' => 'x']);
$users->delete(['name' => 'marc']);
```
Each has a `…Statement()` sibling returning the builder instead of running it.
`selectValue()` returns `false` when no row matches and `null` when the column holds NULL: test for `=== false`,
not a plain truth test, which a NULL, a 0 or an empty string would fail as well.
`delete()` with no filter would empty the table, so it refuses unless you pass `$allowAll`.
`store()` picks the conflict target itself: the primary key when the rows carry it, otherwise the first unique
column they do. A table with several independent unique keys cannot be targeted automatically — use
`insertStatement()->duplicate()` and say which.
For anything entity-shaped, go through the [repository](Repository.md) instead of these.