# 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.
`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.
A **filter** on a column the table does not have is an error, not something to drop. Ignoring a filter returns
*more* rows than were asked for, which is the wrong way to fail. So:
```php
$users->query()->listOnly()->getEntities(); //eNotFound: no such column `status`
```
`Query::listOnly()` filters on `status`, so it needs a table that has a `status` column. Add
`'status' => 'VARCHAR(20) NULL'` and it works — the inherited `cStatus` property stores as its case name.
## 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.
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
- **Nested-path filters are not supported.** `['author/name' => 'marc']` means "a property of a related
entity", which in SQL is a join, and nothing here knows which columns relate the two tables — that is what
`helpers\Liaison` was meant to describe, and it is not built. Join explicitly and filter on `alias.column`
(see [Joins](Joins.md)).
- **Full-text search** (`searchText()`) is not implemented.
- **No MySQL test coverage.** The MySQL dialect is exercised by rendering tests only.