# Repository
`PDORepository` is the bridge between a SQL database and core's entity layer. If you have used
`JsonRepository` or `MongoRepository`, this is the same contract: collections of entities, queries over them,
and `\MMO\commit()` to persist.
## Setting one up
```php
$handler = new MMO\pdo\SqliteHandler('data/app.sqlite');
$handler->connect();
$repository = new MMO\pdo\PDORepository($handler);
$repository->setTables([
'user' => [
'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
'name' => 'VARCHAR(100) NOT NULL UNIQUE',
'email' => 'VARCHAR(200) NULL',
],
]);
$repository->install(); //CREATE TABLE for anything missing
```
From configuration:
```php
\MMO\app([
'db' => ['$' => 'MMO\pdo\SqliteHandler', 'filename' => '{basepath}/../app.sqlite'],
'repo' => [
'$' => 'MMO\pdo\PDORepository',
'handler' => ['$' => '#db'],
'tables' => [
'user' => ['id' => 'INTEGER PRIMARY KEY AUTOINCREMENT', 'name' => 'VARCHAR(100) NOT NULL'],
],
],
'MMO\core\controllers\RepositoryController' => [
'repositories' => ['main' => ['$' => '#repo']],
],
]);
```
That last block is all it takes to serve the whole thing over HTTP —
`data/main/user.json`, with filtering, sorting, pagination, CSV and xlsx — because
`RepositoryController` works against any `iRepository`.
`addTable()` registers a `Table` you built yourself. A collection's name is the table's name.
`setEntities(['user' => User::class])` gives a table your own entity class, rather than one generated from its
columns -- before or after `setTables()`, alike (see [Tables](Tables.md#your-own-entity-class)).
## Worked example: an invoice and its lines
Two tables in SQLite, an entity class for each, a line referencing its invoice; created on first use, written in
one commit. Run as it is by `tests/repository/TestPDOInvoiceExample.php`.
```php
use MMO\core\attributes\ArrayProperty;
use MMO\pdo\helpers\PDOEntity;
class Invoice extends PDOEntity {
const ID_PROP = 'id';
#[ArrayProperty()] protected int $id = 0;
#[ArrayProperty()] protected string $number = '';
#[ArrayProperty()] protected string $customer = '';
#[ArrayProperty()] protected ?\DateTime $issued = null; // a DATE column: a DateTime both ways
public function getLabel(): string {return $this->number;}
}
class InvoiceLine extends PDOEntity {
const ID_PROP = 'id';
#[ArrayProperty()] protected int $id = 0;
#[ArrayProperty()] protected ?Invoice $invoice = null; // a reference: the invoice's id in the row
#[ArrayProperty()] protected string $label = '';
#[ArrayProperty()] protected float $quantity = 0;
#[ArrayProperty()] protected float $unitPrice = 0;
}
$handler = new MMO\pdo\SqliteHandler($dbFolder . '/invoices.sqlite'); // a folder the web server can write
$handler->connect();
$repository = new MMO\pdo\PDORepository($handler);
$repository->setTables([
'invoices' => [
'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
'number' => 'VARCHAR(20) NOT NULL UNIQUE',
'customer' => 'VARCHAR(200) NOT NULL',
'issued' => 'DATE NOT NULL',
],
'invoiceLines' => [
'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
'invoice' => 'INTEGER NOT NULL',
'label' => 'VARCHAR(200) NOT NULL',
'quantity' => 'DECIMAL(10,3) NOT NULL',
'unitPrice' => 'DECIMAL(10,2) NOT NULL',
],
]);
$repository->setEntities(['invoices' => Invoice::class, 'invoiceLines' => InvoiceLine::class]);
$repository->install(); // the tables the database does not have yet: the first request creates them
// writing: the invoice and its lines, one transaction
$invoice = $repository->getCollection('invoices')->createEntity(['number' => '2026-0001', 'customer' => 'Acme', 'issued' => new \DateTime()]);
$repository->getCollection('invoiceLines')->createEntity(['invoice' => $invoice, 'label' => 'Design', 'quantity' => 1.5, 'unitPrice' => 33.33]);
try {\MMO\commit($repository);}
catch (MMO\pdo\exceptions\ePDO $e) {/* 'UNIQUE constraint failed: invoices.number': nothing was written */}
$invoice->getId(); // '1' -- '-1' until the commit (a placeholder), the database's id after
// reading, in a later request
$invoices = $repository->getCollection('invoices');
$invoice = $invoices->getById('1'); // an id is a string: getById((string)$id)
$newest = $invoices->query([], sort: ['-issued', '-id'])->getEntities();
$lines = $repository->getCollection('invoiceLines')->query(['invoice' => $invoice->getId()])->getEntities();
$lines[0]->get('invoice') === $invoice; // true: the reference is the invoice itself
```
- **`query()` is a `Query`**, iterated by `foreach` or turned into a list by `->getEntities()` -- not an array.
- **A failed commit writes nothing**: the invoice and its lines are one transaction. Check what you can before
(the number is free: `$invoices->query(['number' => $number])->getEntities()` is empty), and catch `ePDO` for
what only the database knows.
- **Money**: a `DECIMAL` column reads back as a `float`; round each line to the cent (`round($quantity * $price, 2)`)
and add the rounded lines, so a total is what its lines show.
- **In an application**, a module owns the repository: built once, from configuration (`'entities' => [...]`
beside `'tables'`), its database file in a folder the web server's user can write.
`install()` is deliberately explicit and never runs on its own: creating tables as a side effect of connecting
is how a production schema gets silently rebuilt.
`unregister()` detaches the repository from the global `Commit`/`Fetch` events. A repository subscribes on
construction and otherwise stays subscribed for the life of the process — right for an application, wrong for
a test suite or a worker that swaps connections.
## Reading
```php
$users = $repository->getCollection('user');
$query = $users->query(['name' => 'marc'], sort: ['-id'], limit: 10, offset: 20);
foreach ($query as $user) {…}
$query->total; //how many rows MATCH, not how many were returned
```
Filters are core filters (see [Filters](Filters.md)). Sorting, pagination and field selection all become SQL.
`$total` is a real `SELECT COUNT(*)` whenever a limit or offset is in play, so paging can compute page counts.
Without pagination it is just the number of rows returned.
`$query->execute(['raw' => true])` returns plain rows instead of entities.
`['reload' => true]` refreshes an already-loaded entity from its row.
### Identity
A row always maps to the same entity instance: the repository looks each id up in the collection before
building anything. Querying twice gives you the same object, and an entity read back from the database is not
marked as created.
### Fields versus filters
A `Query`'s field list comes from the entity **schema**, which names properties no table has — `status`, `uid`
and `metadata` are on every entity. Those are dropped from the `SELECT`; the primary key is always added back.
### Filters
A filter is rewritten for the table before it becomes SQL, so that every `Query` a collection takes works on it:
- **A value is converted as it is stored**: a date in the column's format, an entity as its id, an enum as its case
name. A day alone (`'2026-10-09'`) against a `DATETIME` column is its midnight, or its last second for `$lte` and
`$gt`, as `FilterOperator::test` reads it.
- **`id` is the primary key**, whatever its name — and all of its columns for a composite key (an id folded by
`getId()`): `Collection::getById()` looks an entity up with `['id' => …]`.
- **A property with no column** holds its default on every row: the condition is true or false for the whole
table, and decided without asking the database. `Query::listOnly()` filters on `status != ARCHIVE`, which a
table without a `status` column matches entirely; `['status' => 'ARCHIVE']` matches nothing, and runs no query.
- **A reference**: `['author/name' => 'Hugo']`, where `author` is typed as the entity of another table of this
repository, is `` `author` IN (SELECT `id` FROM author WHERE `name` = ?) `` — and as deep as the references go. A
book with no author matches a condition a null matches (`['author/country' => null]`).
- **Any other nested path** reads a column holding JSON (see [Filters](Filters.md)).
A name that is none of these — neither a column nor a property — is an error, not something to drop. Ignoring a
filter returns *more* rows than were asked for, which is the wrong way to fail:
```php
$users->query(['nope' => 1])->getEntities(); //eNotFound: cannot filter [user] on [nope]: no such column
```
A sort on a name that is not a column is ignored (it cannot change which rows match).
## Writing
```php
$marc = $users->createEntity(['name' => 'marc']);
$marc->set('email', 'marc@example.com');
$old->removePermanently();
\MMO\commit($repository);
```
One commit is one transaction: if anything fails, nothing is written and the exception is rethrown.
Per entity, in one pass: removed → `DELETE`, created → `INSERT`, changed → `UPDATE` of just the changed
columns. New rows are committed first, so an entity referencing another gets a real foreign key. Entities in a
`readonly` collection are skipped with a warning. A `dryRun` commit writes nothing.
A removed entity leaves its collection once the commit saved it: `getById()` no longer finds it. One created and
removed before any commit has no row: nothing is deleted, and nothing is inserted later. Until 2026-09 a removed
entity stayed in its collection for the rest of the request, and one never saved was inserted by the next commit.
`LogQuery` logging (`logInsert`/`logUpdate`/`logDelete`/`logStatus`) happens only if a collection for
`LogQuery` entities is registered; without one there is nowhere to put a log.
### Values
`PDOExporter` converts each value for its column: a date to the column's own format (`Y-m-d` for `DATE`,
`Y-m-d H:i:s` for `DATETIME`), an enum to its case name, a referenced entity to its id, anything still
structured to JSON. A value with no column is dropped.
Read back, a referenced entity's id becomes that entity again: from its collection if it is loaded, queried
otherwise (null if no row has that id any longer). Until 2026-10 it was left `null`: a reference was written, and
never read.
A reference to an entity that has no id yet raises an error — commit it first, or let the same commit order it
(new entities go first).
## Ids
Three cases, handled for you:
- **`AUTO_INCREMENT`** — the database owns the id. The entity gets a negative *placeholder* id at creation and
the real one after its `INSERT`.
The placeholder is not cosmetic. A collection is keyed by entity id, and the framework assumes every entity
has one from birth (Mongo mints an ObjectId, the in-memory repository counts up). An entity with no id is
filed under a positional integer key, which the next entity's real id then collides with — and one entity
starts answering to another's id. Negative ids are type-compatible with an integer column and unreachable by
a sequence that counts up from 1.
On SQLite the key is created `id INTEGER PRIMARY KEY AUTOINCREMENT`, the only form in which SQLite never gives
the id of a deleted row again. Until 2026-09 mmo.pdo created it `id INTEGER, …, PRIMARY KEY(id)`: SQLite reads
that as its rowid, which gives the id of the highest row deleted to the next one. A table created then still
does, and SQLite cannot add `AUTOINCREMENT` to it: create another table and copy the rows over.
- **A single non-generated key** — `generateId()` assigns one, from the highest id in the table, among the
entities already created but not yet committed, and among those this repository removed.
**A later request only sees the table**: once the highest row is deleted, its id is given again there, and
whatever was keyed by it (a scope, a link, a cache) applies to the new row. Give a table whose ids must never
come back a key the database generates. A high-water mark kept by the repository would need a table of its
own, one the application never declared, and would still not make `MAX(id) + 1` safe for two requests at once;
the database's own sequence is declared with the table, and gives each insert its own id.
- **A composite key** — supplied by you. `getId()` folds it into one string, `getIdParts()` gives the parts
back (see [Tables](Tables.md)).
`PDORepository::IsPendingId($id)` tells whether an id is still a placeholder.
## Limitations
- **A reference is followed in a filter, and read back as its entity** (one query per entity not loaded yet).
A `SelectStatement` join is still written by hand
(see [Joins](Joins.md)); a reference to a table with a composite key cannot be filtered through.
- **Full-text search** (`searchText()`) is not implemented.
- **No MySQL test coverage.** The MySQL dialect is exercised by rendering tests only.