# SQL Mirror

`SqlMirror` pushes storh collections into SQLite or MySQL tables so you can
run joins, cross-table ordering, substring search, and reporting in SQL while
the files stay canonical. Think of it as a secondary index that happens to be
a database: the mirror is derived, disposable, and rebuildable at any time.
`ext-pdo` with the `sqlite` or `mysql` driver is required only when you use
it; the core package stays dependency-free.

## Setup

```php
use Storh\DocStore;
use Storh\Schema;
use Storh\SqlMirror;

$schema = Schema::collection('pages')
    ->string('slug')->unique()
    ->string('kind')->index()
    ->int('publishedAt')->range();

$pages  = new DocStore($root, 'pages', schema: $schema);
$events = new Storh\SegmentedLog($root, 'events');

$mirror = new SqlMirror(new PDO('sqlite:' . $root . '/mirror.db'), 'app_');
$mirror->collection($pages, 'pages', $schema);
$mirror->collection($events, 'events');

$mirror->install();
$mirror->push();
```

Both engines can be mirrored: `DocStore` collections and `SegmentedLog`
streams (only live records are pushed). Passing a `Schema` is optional and
independent of the store: declared `string`/`int`/`float`/`bool` fields
become typed, indexed columns; everything else stays queryable in the `data`
JSON column. `mixed` fields cannot be flagged for indexing.

## Connections

The constructor accepts a `PDO` handle (`sqlite` or `mysql` driver, detected
automatically), a `mysqli` handle, or any `SqlMirrorConnection`
implementation:

```php
$mirror = new SqlMirror(new PDO('sqlite:' . $root . '/mirror.db'));
$mirror = new SqlMirror(new PDO('mysql:host=127.0.0.1;dbname=app;charset=utf8mb4', $user, $password));
$mirror = new SqlMirror(new mysqli('127.0.0.1', $user, $password, 'app'), 'wp_storh_');
```

The mysqli path works with either mysqli error-reporting mode, so an existing
handle from a host application (for example WordPress's `$wpdb->dbh`) can be
reused as is. Behavior is identical across drivers; push failures roll back
the collection's transaction on all of them.

Every table gets `id` (primary key), `hash` (drives incremental pushes), the
typed columns, and `data` (the full record JSON).

## Syncing

```php
$mirror->push();                    // reconcile: insert, update, delete as needed
$mirror->push('pages');             // reconcile a single collection
$mirror->flush('pages', $ids);      // push or remove just these ids
$mirror->pull();                    // write mirror rows back into the store
$mirror->verify();                  // report missing, stale, and orphaned rows
$mirror->rebuild();                 // clear all rows and push from scratch
$mirror->uninstall();               // drop the mirror tables
```

`push()` diffs record hashes against the mirror and writes each collection in
one transaction, so a crashed or failed push never leaves a half-applied
batch. Unchanged records cost one `SELECT` plus a record sweep. A periodic
`push()` (for example from a cron tick) keeps the mirror converged; call
`flush()` with the ids you wrote during a request when readers need
read-your-writes against the mirror.

## Querying

Query the mirror with plain SQL. `table()` returns the mirrored table name:

```php
$pdo->query(
    'SELECT p.slug, COUNT(*) AS sales
     FROM ' . $mirror->table('purchases') . ' o
     INNER JOIN ' . $mirror->table('products') . ' p ON p.id = o.product_id
     GROUP BY p.slug
     ORDER BY sales DESC'
);
```

## Pulling back into files

`pull()` reads mirror rows and writes them into the registered store, in id
order. It covers two flows:

- Restore: the files are gone but the mirror survives. Register a fresh store
  under the same collection name and pull.
- Seeding: rows loaded into the mirror table by external tooling become storh
  records. Rows must carry valid UUIDv7 ids and an object in the `data`
  column; typed columns are ignored on pull because `data` is the whole
  record.

```php
$restored = new Storh\DocStore($root, 'pages');
$mirror   = new SqlMirror($pdo);
$mirror->collection($restored, 'pages');
$mirror->pull();          // ['written' => n, 'unchanged' => m]
```

Pulled writes go through the normal store machinery, so schema validation and
unique indexes apply, and every record is written durably. `pull()` never
deletes local records, and records whose data already matches the mirror row
are skipped. After seeding hand-inserted rows, run `push()` once: it rewrites
those rows in canonical form so their hashes reconcile.

## Contract and limits

- Files are canonical. The mirror is read-only output; anything written to it
  by hand is overwritten or deleted by the next `push()`.
- Unique columns in the mirror are a backstop, not write-time enforcement: a
  duplicate that files allowed surfaces as a failed (and fully rolled back)
  push. Use storh unique indexes for write-time enforcement.
- The mirror lags files by up to one push interval, except for ids you
  `flush()` explicitly.
- Mirroring does not add transactions to storh itself. Multi-record atomic
  writes remain a job for a real database as the system of record.
- Schema changes are applied by dropping and reinstalling the mirror:
  `uninstall()`, `install()`, `push()`.