# Handlers
A handler is one database connection, plus the SQL dialect that goes with it. Everything else in the package
goes through it.
## Connecting
```php
$sqlite = new MMO\pdo\SqliteHandler('data/app.sqlite');
$sqlite->connect();
$mysql = new MMO\pdo\MysqlHandler('localhost', 'mydb', 'user', 'password');
$mysql->connect();
```
A relative SQLite filename is resolved against the project root (`\MMO\filename()`). Two values are passed
through untouched, because they are DSNs and not paths:
- `SqliteHandler::MEMORY` (`':memory:'`) — a fresh, empty database per connection, discarded when it closes.
The right choice for tests and demos.
- anything starting with `file:` — SQLite's URI form.
### From configuration
```php
\MMO\app([
'db' => [
'$' => 'MMO\pdo\SqliteHandler',
'filename' => '{basepath}/../app.sqlite',
],
]);
```
Put the database **outside** the document root, or the web server will happily serve it.
For MySQL, read the password from the environment rather than committing it:
```php
'db' => [
'$' => 'MMO\pdo\MysqlHandler',
'host' => 'localhost',
'database' => 'mydb',
'user' => 'app',
'password' => getenv('MMO_PDO_PASSWORD'),
],
```
`MysqlHandler::CreateDatabase()` creates a database and its user from an admin account, for a first install.
## Transactions
```php
$handler->do(function($handler) {
//…several statements…
});//commits, or rolls everything back and rethrows
```
`do()` is the one to use. It rolls back on **any** `\Throwable`, which matters because a failing statement
throws `ePDO` (an `eRuntime`), not a `\PDOException`.
The manual form nests: `startTransaction()` can be called any number of times, and only the outermost
`commit()` actually commits.
```php
$handler->startTransaction();
$handler->do(fn() => …); //returns false: nested, nothing committed yet
$handler->commit(); //returns true: this is the one that commits
```
`rollBack()` aborts every level at once — SQL has no partial rollback without savepoints — and resets the
nesting counter. `inTransaction()` reports whether anything is open.
`disconnect()` rolls back an unfinished transaction and warns, rather than letting the driver decide.
## Statements
The handler is the factory for every statement (see [Statements](Statements.md)):
```php
$handler->statement('SELECT * FROM user WHERE id = {id}', ['id' => 3]);
$handler->selectStatement('user', '*', ['name' => 'marc']);
$handler->insertStatement('user', ['name' => 'marc']);
$handler->updateStatement('user', ['name' => 'marc'], ['id' => 3]);
$handler->deleteStatement('user', ['id' => 3]);
$handler->createStatement('user', ['id' => 'INT PRIMARY KEY']);
$handler->dropStatement('user', ifExists: true);
```
Going through the handler is what stamps each statement with the right driver, so `ON DUPLICATE KEY UPDATE`
versus `ON CONFLICT … DO UPDATE` (and the column type mapping) come out right without being asked for.
## Tables and introspection
```php
$handler->addTable($table); //register a declared table
$handler->table('user'); //fetch it -- reading it FROM the database if it was never declared
$handler->tables(); //every table the database has
$handler->tableExists('user');
```
`table()` on an undeclared name introspects the live schema (`pragma table_info` / `SHOW COLUMNS`) and builds a
`Table` from it, so you can work against a database you did not declare.
Introspection recovers types, NOT NULL and the primary key. It does not recover everything a hand-written
declaration carries — notably SQLite's `AUTOINCREMENT` keyword is not reported by `pragma table_info` at all.
Declare your tables if you need that.
## Logging
A handler is an `iLogEmitter`. At `info` verbosity every executed statement is logged with its SQL and its
bound values, which is usually the fastest way to see what a query really became.