Metadata-Version: 2.5
Name: pyoq-sql
Version: 1.0.2
Summary: A fully typed, database-first SQL toolkit for Python.
Project-URL: Homepage, https://teqpod.com/pyoq-sql/
Project-URL: Documentation, https://teqpod.com/pyoq-sql/overview/
Project-URL: Issues, https://github.com/Teqpod/pyoq-sql/issues
Project-URL: Security, https://github.com/Teqpod/pyoq-sql/security/advisories/new
License-Expression: MPL-2.0
License-File: LICENSE
Keywords: database,query-builder,sql,typing
Classifier: Development Status :: 5 - Production/Stable
Classifier: Framework :: AsyncIO
Classifier: Framework :: Django
Classifier: Framework :: FastAPI
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Typing :: Typed
Requires-Python: >=3.11
Provides-Extra: django
Requires-Dist: django<7,>=4.2; extra == 'django'
Provides-Extra: fastapi
Requires-Dist: fastapi<1,>=0.115; extra == 'fastapi'
Provides-Extra: mysql
Requires-Dist: pymysql<2,>=1.1; extra == 'mysql'
Provides-Extra: mysql-async
Requires-Dist: asyncmy<1,>=0.2.14; extra == 'mysql-async'
Requires-Dist: pymysql<2,>=1.1; extra == 'mysql-async'
Provides-Extra: postgres
Requires-Dist: psycopg<4,>=3.2; extra == 'postgres'
Provides-Extra: sanic
Requires-Dist: sanic<26,>=24.6; extra == 'sanic'
Provides-Extra: tracing
Requires-Dist: opentelemetry-api<2,>=1.25; extra == 'tracing'
Description-Content-Type: text/markdown

# PyOQ

PyOQ is a fully typed, database-first SQL toolkit for Python. It is designed to
provide explicit query construction, generated schema types, predictable
execution, and efficient result hydration without hiding SQL behavior.

SQLite, PostgreSQL and MySQL are supported, synchronously and asynchronously.
Schema reflection generates row, key, insert, update, table, column, relation,
enum, domain and routine types that hold under strict type checking. Relations
are fetched by a plan that keeps the query count independent of the result,
and Django, FastAPI and Sanic each run on the same core rather than on a copy
of it.

Every public behaviour is covered by tests at complete statement and branch
coverage, run against real PostgreSQL and MySQL servers rather than recorded
stand-ins.

## Installation

```console
python -m pip install pyoq-sql
```

The distribution name is `pyoq-sql`; the Python import package is `pyoq`.

Releases provide native platform wheels and a portable fallback wheel, so
installation does not require Rust. Building from the source archive does
require a supported Rust toolchain. When the portable wheel is used, select
its runtime explicitly:

```console
PYOQ_RUNTIME=python python -m your_application
```

The fallback preserves behavior and typing but is outside the native
performance contract. `PYOQ_RUNTIME=native` makes an unavailable native engine
a startup error. The default selects the native engine when present and emits
a runtime warning before using the fallback when it is absent.

## Package check

```python
from pyoq import __version__

print(__version__)
```

## Package architecture

Implementation code is grouped by capability. Each feature package owns its
public facade and private implementation details, while package-wide errors,
naming policy, typing metadata, and the native extension remain at the root.

```mermaid
flowchart TD
    Package[pyoq] --> CLI[cli]
    Package --> Config[config]
    Package --> Generation[generation]
    Package --> Query[query]
    Package --> Runtime[runtime]
    Package --> Schema[schema]
    Package --> Serving[serving]
    Package --> Errors[errors]
    Package --> Naming[naming]
    Runtime --> Native[private native extension]
```

Applications should import feature APIs from their stable facades, such as
`pyoq.config`, `pyoq.schema`, `pyoq.generation`, and `pyoq.runtime`. Nested
modules separate component responsibilities and are not compatibility
surfaces.

## Configuration

Configuration is read from `pyproject.toml`. Generated code always lives in a
dedicated directory below the project root. Use a complete environment
reference when a data source name contains credentials.

```toml
[tool.pyoq]
codegen-directory = "generated/database"
codegen-package = "application.database"
selected-profile = "default"

[tool.pyoq.profiles.default]
dialect = "postgres"
host = "localhost"
port = 5432
database = "${POSTGRES_DATABASE}"
username = "${POSTGRES_USERNAME}"
password = "${POSTGRES_PASSWORD}"
minimum-pool-size = 1
maximum-pool-size = 8
pool-checkout-timeout = 3.0

[tool.pyoq.profiles.testing]
dialect = "sqlite"
database-path = "database/testing.sqlite"
```

Open the selected profile through the fully typed lifecycle API:

```python
from pyoq import connect
from application.database.tables import ACCOUNT


with connect(profile="default") as database:
    rows = database.select_from(ACCOUNT).limit(10).fetch_all()
```

Omit `profile` to use `selected-profile`. The context creates one process-owned
pool from the profile limits and closes it on exit. A framework-free
application can instead pass a data source directly with
`connect(dsn, dialect=Dialect.MYSQL)`. An explicit `pool_policy` replaces the
complete profile pool policy for one call.

SQLite file paths use `database-path`. They are resolved relative to the project
root, must remain inside it, and are opened read-only during inspection. An
environment reference in the exact `${VARIABLE_NAME}` form may be used for any
structured server or pool value. Any other string is literal. Server profiles
may alternatively define one `dsn`, but cannot combine it with structured
connection fields.

`SecretValue` renders as `[REDACTED]`; access to its underlying value is always
explicit. An explicit `Configuration` object takes precedence over the file,
and the `selected_profile` argument can override profile selection. Unknown
settings, missing profiles, invalid dialects, unavailable environment values,
and paths outside the project root fail with exceptions from `pyoq.errors`.

## What each dialect can do

Every row here is one operation, not a family, because a family name hides
which parts of it exist. Every entry was taken from a running server rather
than from a specification.

**refused** means PyOQ raises before the statement is sent, naming what the
dialect has not got. It never emits SQL a database cannot run, and never
quietly does something narrower than what was asked. **written by PyOQ** means
the dialect has no such operation and PyOQ writes it out of what the dialect
does have, so one word means one thing on all three.

This table is generated from the capability matrix the source keeps. A
contract compiles every row against every dialect, checks that each answers
with the kind of value the row claims, and names the dialect-neutral contract
that runs it against a real server, so a claim here that is not true stops the
test suite.

<!-- capability matrix -->

| Operation | sqlite | postgres | mysql | reads back as | run by |
| --- | --- | --- | --- | --- | --- |
| window: row number | yes | yes | yes | numeric | `window_contract` |
| window: rank | yes | yes | yes | numeric | `window_contract` |
| window: dense rank | yes | yes | yes | numeric | `window_contract` |
| window: percent rank | yes | yes | yes | numeric | `window_contract` |
| window: cumulative distribution | yes | yes | yes | numeric | `window_contract` |
| window: ntile | yes | yes | yes | numeric | `window_contract` |
| window: lag | yes | yes | yes | numeric | `window_contract` |
| window: lead | yes | yes | yes | numeric | `window_contract` |
| window: first value | yes | yes | yes | numeric | `window_contract` |
| window: last value | yes | yes | yes | numeric | `window_contract` |
| window: nth value | yes | yes | yes | numeric | `window_contract` |
| window: an aggregate over a window | yes | yes | yes | numeric | `window_contract` |
| window: partition by | yes | yes | yes | numeric | `window_contract` |
| window: order by | yes | yes | yes | numeric | `window_contract` |
| window frame: rows | yes | yes | yes | numeric | `window_contract` |
| window frame: range | yes | yes | yes | numeric | `window_contract` |
| window frame: groups | yes | yes | refused | numeric | `window_contract` |
| window frame: exclude | yes | yes | refused | numeric | `window_contract` |
| window: declared by name | yes | yes | yes | numeric | `window_contract` |
| json: read a value | yes | yes | yes | json | `json_contract` |
| json: read text | yes | yes | yes | string | `json_contract` |
| json: how long an array is | yes | yes | yes | numeric | `json_contract` |
| json: whether a path is there | yes | yes | yes | boolean | `json_contract` |
| json: whether it holds a value | refused | yes | yes | boolean | `json_contract` |
| json: set | yes | yes | yes | json | `json_contract` |
| json: insert | yes | written by PyOQ | yes | json | `json_contract` |
| json: replace | yes | yes | yes | json | `json_contract` |
| json: merge all the way down | yes | refused | yes | json | `json_contract` |
| json: write over members, one level | refused | yes | refused | json | `json_contract` |
| json: remove | yes | yes | yes | json | `json_contract` |
| json: build an object | yes | yes | yes | json | `json_contract` |
| json: build an array | yes | yes | yes | json | `json_contract` |
| json: name the members | written by PyOQ | written by PyOQ | yes | json | `json_contract` |
| json: gather rows into an array | yes | yes | yes | json | `json_contract` |
| json: gather rows into an object | yes | yes | yes | json | `json_contract` |
| json: read an array as rows | yes | yes | yes | string | `json_contract` |
| array: holds a value | refused | yes | refused | boolean | `array_contract` |
| array: holds a value nowhere | refused | yes | refused | boolean | `array_contract` |
| array: holds every value | refused | yes | refused | boolean | `array_contract` |
| array: is among | refused | yes | refused | boolean | `array_contract` |
| array: shares a value | refused | yes | refused | boolean | `array_contract` |
| array: append | refused | yes | refused | other | `array_contract` |
| array: prepend | refused | yes | refused | other | `array_contract` |
| array: concatenate | refused | yes | refused | other | `array_contract` |
| array: without a value | refused | yes | refused | other | `array_contract` |
| array: replace a value | refused | yes | refused | other | `array_contract` |
| array: one element | refused | yes | refused | other | `array_contract` |
| array: how many elements | refused | yes | refused | numeric | `array_contract` |
| array: how many along a dimension | refused | yes | refused | numeric | `array_contract` |
| array: how many dimensions | refused | yes | refused | numeric | `array_contract` |
| array: built from values | refused | yes | refused | other | `array_contract` |
| lock: for update | refused | yes | yes | numeric | `locking_contract` |
| lock: for share | refused | yes | yes | numeric | `locking_contract` |
| lock: fail rather than wait | refused | yes | yes | numeric | `locking_contract` |
| lock: skip what is locked | refused | yes | yes | numeric | `locking_contract` |
| lock: named tables | refused | yes | yes | numeric | `locking_contract` |
| lock: named fields | refused | yes | yes | numeric | `locking_contract` |
| query: lateral source | refused | yes | yes | numeric | `lateral_contract` |
| query: recursive common table | yes | yes | yes | numeric | `recursion_contract` |
| query: row value | yes | yes | yes | boolean | `row_value_contract` |
| query: cast | yes | yes | yes | string | `cast_contract` |
| query: nulls first or last | yes | yes | written by PyOQ | numeric | `null_order_contract` |
| query: right join | refused | yes | yes | numeric | `joined_contract` |
| query: full join | refused | yes | refused | numeric | `joined_contract` |
| routine: run a stored procedure | refused | yes | yes | rows | `routine_contract` |
| routine: ask a function by name | yes | yes | yes | numeric | `routine_contract` |
| routine: more than one result set | refused | refused | refused | rows | `routine_contract` |

<!-- capability matrix -->

A JSON path is steps rather than text, because PostgreSQL reads another
dialect's written path as a member of that name and answers null without
complaining. PostgreSQL's own insert raises where a member already exists,
while the other two leave what is there, so PyOQ asks first on PostgreSQL.

Merging and concatenating are two operations, not one. `json_merge` is
merge-patch: a member given as null is removed, and an object inside an object
is merged rather than replaced. MySQL and SQLite do that themselves, and
PostgreSQL has no merge-patch at all. `json_concat` is one level deep: a member
is replaced whole and a null is stored as a null. PostgreSQL does that itself,
and the other two have nothing that means it. Each is refused where the
dialect does not have it, rather than quietly doing the other one.

Only PostgreSQL has an array type. SQLite will accept `TEXT[]` as a type name
with no array behind it, which is the kind of tolerance an emulation would be
built on, so the other two refuse instead.

SQLite locks a database rather than rows and keeps no stored procedures. A
timed lock wait is in no supported dialect: `FOR UPDATE WAIT` is a syntax error
on both PostgreSQL and MySQL. Null treatment on a window function is likewise
in none of them: `IGNORE NULLS` is refused by all three servers.

A procedure that answers with more than one result set is refused rather than
read down to its first, because a dropped result set is a silent one.

### Schema objects

| Object | SQLite | PostgreSQL | MySQL |
| --- | --- | --- | --- |
| tables, views, keys, relations, indexes, checks | yes | yes | yes |
| enums | none | reflected and generated | reflected per column |
| domains | none | reflected and generated | none |
| routines | none | reflected and generated | reflected with typed calls or typed rejection |

Only PostgreSQL keeps overloaded routines; MySQL refuses a second routine of
the same name, and SQLite keeps no routine catalog at all.

A procedure answers through the parameters it writes. A generated PostgreSQL
call asks the caller only for IN and INOUT values, supplies required OUT
placeholders internally, and reads the answer back with the catalog's types and
nullability. MySQL needs a session-variable adapter for OUT and INOUT values.
Those routines retain a generated, typed signature that raises
`UnsupportedQueryError` before any invalid SQL is sent.

A generated call names the schema the catalog said keeps the routine. Without
it a routine outside the search path resolves to nothing, and one whose name
another schema shares resolves to the wrong one.

## Supported dialects

Every configured dialect resolves to a schema source, so inspection and
generation work for all of them. SQLite is implemented end to end. PostgreSQL
has a compiler, type mapping, schema reflection, and both synchronous and
asynchronous drivers with pooling, transactions, savepoints, and streaming.
MySQL has all of that too, so all three dialects are now complete vertical
slices.

Database drivers are optional extras, so a SQLite installation stays dependency
free:

```console
python -m pip install "pyoq-sql[postgres]"
python -m pip install "pyoq-sql[mysql]"
python -m pip install "pyoq-sql[mysql-async]"
```

Without them, PostgreSQL and MySQL query construction and compilation still
work because they are pure Python. Only connecting requires a driver, and its
absence raises an explicit error naming the extra to install. PostgreSQL and
PostgreSQL and MySQL profiles supply their connection string through `dsn`, and
inspection opens the connection read-only.

A MySQL connection string names one database, which is reflected as the
snapshot's schema:

```console
mysql://user:password@host:3306/database
```

Query and fragment options are refused rather than silently ignored, so an
unsupported setting can never be mistaken for an applied one.

`schema_source_for()` resolves a dialect to its schema source, and
`supported_dialects()` reports every dialect that has one. Command services
route through the configured dialect at load time, so adding a dialect is a
registry entry rather than a change to the command layer.

The layers a dialect plugs into are deliberately neutral. `CompiledQuery`,
`StatementCompiler`, `SchemaSource`, `QueryOperations`, `BulkPlan`, and
`DatabaseCursor` carry no dialect assumptions, so a new database supplies a
compiler, a schema source, and a cursor-backed executor without reimplementing
result cardinality, bulk planning, or ordered multi-operation execution.

Execution is shared too. `ConnectionPool` owns the lease lifecycle, size and
timeout policy, idle reuse, invalidation, and close semantics; `RowStream` owns
bounded batch buffering, iteration ownership, and deterministic cleanup; and
`TransactionState` owns scope nesting, savepoint naming, owner-thread checks,
and stream interaction. A dialect supplies how it resets and validates a
connection, how it opens a cursor, and how it begins a transaction.

SQL rendering is shared as well. `ExpressionRenderer`, `QueryRenderer`, and
`WriteRenderer` own the standard SQL that every relational dialect writes the
same way: projections, sources, joins, common tables, ordering, set operations,
inserts, assignments, row scoping, conflict resolution, and returning. A dialect
declares only where it genuinely differs, such as how it spells distinctness,
how it extracts a date part, how it renders string predicates, which joins and
capabilities it allows, and how it paginates.

Parameter placeholders are part of that declaration. `ParameterStyle` covers
`?`, `$1`, and `%s`, and the compilation context escapes literal percent signs
in structural SQL when the driver style requires it.

`PostgresCompiler` shows the shape of a dialect built this way. It renders the
same query model as SQLite while spelling distinctness as `IS DISTINCT FROM`,
extracting date parts with `EXTRACT`, rendering string predicates with
`POSITION`, `LEFT`, and `RIGHT`, allowing RIGHT and FULL joins and a native
`NULLS` clause, emitting a bare `OFFSET` without a placeholder limit, and
budgeting at the 65535 parameter ceiling of the extended query protocol. It
rejects catalog-qualified identifiers and adding two temporal values, because
PostgreSQL has no operator for either.

MySQL has a compiler, type mapping, schema reflection, and synchronous and
asynchronous drivers. It shows how far a dialect can diverge while still using
the shared renderers: identifiers are quoted with backticks, string
concatenation renders as `CONCAT` because `||` means logical OR in MySQL,
distinctness uses the null-safe `<=>` operator, a bare `OFFSET` is given the
maximum row limit MySQL requires, explicit null placement is written by
ordering on whether a value is null because MySQL has no `NULLS` clause, and
upsert renders as a row alias with `ON DUPLICATE KEY UPDATE`. The row alias form requires
MySQL 8.0.19 or later; earlier servers and MariaDB use a different spelling
that PyOQ does not emit.

Where MySQL cannot express something, it says so instead of emulating it.
`RETURNING` is refused with a message pointing at a follow-up query. A conflict
target is refused because MySQL infers the key. A conflict condition is refused
because `ON DUPLICATE KEY UPDATE` has no `WHERE`. Do-nothing conflict
resolution is refused rather than emulated with `INSERT IGNORE`, which would
also swallow unrelated errors, or with a self-assignment, which would report a
different affected row count.

`postgres_type()` normalizes declared PostgreSQL type names into the shared
schema model, including serial types, spelled-out variants such as
`timestamp with time zone`, length and precision arguments, and array types
with their element type. A declaration it cannot parse stays opaque rather than
being guessed at.

MySQL executes through `MySQLExecutor` over a `MySQLPool`, with the same typed
operations SQLite and PostgreSQL use. Four dialect behaviors differ and are
documented rather than hidden.

An affected row count means matched rows. MySQL counts changed rows by default,
so an update writing a column's existing value would report nothing, and PyOQ
connects with the client flag that restores the meaning every other dialect
has.

An insert reports the first identifier it generated, not the last, because that
is what MySQL reports for a multi-row insert.

A statement timeout is enforced from the client. MySQL applies its own
`max_execution_time` only to read-only SELECT statements, so PyOQ sets that
and also runs a watchdog that asks the server to stop the statement once the
deadline passes. Cancellation works the same way, because MySQL has no
client-side cancel. Both open a short-lived second connection, and a statement
carrying neither a timeout nor a cancellation token starts no watchdog and pays
nothing.

A deadlock rolls the whole transaction back on the server while leaving the
connection healthy, and a later commit then succeeds having committed nothing.
PyOQ refuses to report that commit and raises instead, so work that was
discarded is never reported as saved. A failed statement that is not a deadlock
leaves a MySQL transaction usable, which is the opposite of PostgreSQL and
needs no savepoint to recover from.

Isolation is set before a transaction starts rather than as part of starting
it, and the default is MySQL's own repeatable read rather than a level PyOQ
imposes. Streaming reads through an unbuffered cursor, and a transaction allows
only one open stream at a time so that a second statement cannot silently
discard the rows the first has not read.

`MySQLAsyncExecutor` over a `MySQLAsyncPool` provides the same operations
without blocking, and behaves identically on every point above. Three things are
worth knowing about it.

It has its own extra, because its driver is a compiled one:

```console
python -m pip install "pyoq-sql[mysql-async]"
```

The synchronous extra stays pure Python. The asynchronous driver is compiled
because the read path is worth it: PyOQ adds only a few percent above its
driver on a large read, so the driver is the cost, and a pure-Python driver
measured roughly an order of magnitude slower end to end on the same query. A
throughput contract holds the read path to that standard so it cannot quietly
regress.

Its connections are encrypted. The synchronous driver negotiates TLS on its
own, and the asynchronous one is given the same settings so that choosing
`async` never quietly downgrades the transport. Encryption is what lets a
server's password check happen without a separate cryptography library, so a
server with TLS switched off needs `PyMySQL[rsa]` installed for either driver.

Its driver publishes only generated type stubs that leave most of its surface
unknown, and raises its own exception hierarchy rather than the synchronous
driver's. PyOQ therefore declares the shape it depends on and confines every
reference to the driver to one module. Failure classification is shared, since
every MySQL driver reports the same numeric codes while raising different
exception types.

`mysql_type()` normalizes declared MySQL type names, including width and
precision arguments, `unsigned`, `signed`, and `zerofill` modifiers, and the
inline `ENUM` and `SET` declarations that carry their values in the column type
rather than in a named type.

MySQL reflection reads the connected database through `information_schema`.
Auto-increment columns are reported as identity columns, stored and virtual
generated columns keep their generation expression, and column defaults are
normalized into valid SQL so that a literal default such as an empty string is
quoted while an expression default such as `json_object()` is not. Descending
and expression index terms, composite foreign keys with their update and delete
rules, and CHECK constraints are all carried across. The placeholder comment
MySQL stores on every view is discarded rather than reported as a comment.

PostgreSQL executes through `PostgresExecutor` over a `PostgresPool`, with the
same typed operations SQLite uses. Two dialect behaviors differ and are not
hidden. A statement timeout is enforced by the server through
`statement_timeout` rather than a client-side interrupt, and a cancellation
token is honored mid-statement by a watchdog that issues a server cancel.
Streaming uses a server-side cursor inside its own transaction, which is what
keeps memory bounded for large results.

PostgreSQL also executes asynchronously through `PostgresAsyncExecutor` over a
`PostgresAsyncPool`. The asynchronous surface mirrors the synchronous one
method for method, including pooling, transactions, savepoints, server-side
cursor streaming, server-enforced timeouts, and cancellation:

```python
async with PostgresAsyncPool(PostgresAsyncConnectionFactory(dsn)) as pool:
    database = PostgresAsyncExecutor(pool)
    async with database.transaction() as transaction:
        await transaction.execute(statement)
    async with database.stream(query) as rows:
        async for row in rows:
            ...
```

Bulk writes use PostgreSQL pipeline mode on both paths. The planner still
splits a large insert into chunks that fit the parameter budget, but the chunks
are sent without waiting for each result, which removes a round trip per chunk.
This changes one behavior for the better and it is worth knowing: a pipelined
batch is atomic, so a failure discards the whole batch rather than leaving
earlier chunks applied. Sequential chunking keeps the earlier chunks, as it
always did.

Pipelining is governed by the `pipeline` compiler capability and by whether the
installed libpq supports it. When either says no, chunk execution falls back to
the sequential path with identical results.

Ownership is per task rather than per thread, so a stream or transaction used
from a different task fails explicitly instead of corrupting its state. Both
paths are held to the same dialect-neutral execution contract, so behavior does
not drift between them.

The most important difference is transactional: in PostgreSQL a failed
statement aborts the whole transaction, and every later statement in that
transaction fails until it ends. Recovery is a savepoint taken before the
statement that might fail:

```python
with database.transaction() as transaction:
    with transaction.savepoint() as attempt:
        attempt.execute(statement_that_may_conflict)
    transaction.execute(next_statement)
```

PyOQ does not wrap every statement in an implicit savepoint to paper over this,
because that would add a hidden round trip to every write.

PostgreSQL reflection reads the system catalogs directly rather than
`information_schema`, so it recovers identity and generated columns, array
element types, per-column comments, foreign key referential actions, expression
and partial indexes with their sort direction, check constraints, materialized
views, and multiple schemas. Catalog rows are validated as they are read, so a
row of unexpected shape fails as a schema error instead of producing a silently
wrong model.

## Schema model

Database metadata is normalized into an immutable, dialect-neutral snapshot.
Source identifiers retain their exact case, spacing, and Unicode content. The
model does not apply Python naming rules or depend on database drivers and web
frameworks.

```mermaid
flowchart TD
    Snapshot[SchemaSnapshot] --> Catalog[Catalog]
    Catalog --> Schema[Schema]
    Schema --> Table[Table]
    Schema --> View[View]
    Schema --> Enum[EnumType]
    Table --> Column[Column]
    Table --> Key[Key]
    Table --> Relation[Relation]
    Table --> Index[Index]
    Table --> Check[CheckConstraint]
```

Construct snapshots directly when implementing metadata sources:

```python
from pyoq.config import DatabaseDialect
from pyoq.schema import (
    Catalog,
    Column,
    Identifier,
    Schema,
    SchemaSnapshot,
    SqlType,
    Table,
    TypeKind,
)

user_id = Column(
    name=Identifier("id"),
    data_type=SqlType(TypeKind.INTEGER, "INTEGER"),
    nullable=False,
)
users = Table(name=Identifier("users"), columns=(user_id,))
snapshot = SchemaSnapshot(
    dialect=DatabaseDialect.SQLITE,
    catalogs=(Catalog(None, (Schema(None, tables=(users,)),)),),
)
```

`SchemaSnapshot.to_json()` produces compact, versioned, deterministic JSON with
Unicode preserved. `SchemaSnapshot.from_json()` performs strict field and type
validation before constructing model objects. The round trip preserves ordered
columns, nullability, raw defaults, identity and computed values, SQL type
details, qualified references, comments, indexes, checks, relations, enums,
views, and dialect capabilities.

Every model uses frozen slots. Duplicate identifiers, invalid type dimensions,
misaligned foreign-key columns, unknown local key or index columns, conflicting
generated-value metadata, and unsupported snapshot versions fail with typed
schema exceptions. Qualified relation targets may remain outside a snapshot so
metadata sources can represent intentionally partial introspection safely.

## Relations

A foreign key is one fact that can be read from either side, so PyOQ derives
both. `derive_relations()` turns a schema snapshot into typed relations that
carry direction, cardinality, and whether a to-one relation may resolve to
nothing:

```python
from pyoq.relations import derive_relations

for relation in derive_relations(snapshot):
    print(relation.direction, relation.cardinality, relation.optional)
```

Cardinality and optionality are read from the schema rather than declared:

- A foreign key names at most one row of the table it points at, so following
  it forward is always to-one. It is optional when any of its columns is
  nullable, because a foreign key with a null part references nothing at all.
- Following it backwards is to-many, unless the referencing columns are
  themselves unique, in which case it is a to-one that may be absent. A unique
  key over part of the referencing columns is enough, since a subset that is
  already unique makes the wider set unique too. This is what recognizes a
  table whose primary key is also its foreign key as a one-to-one extension
  rather than a collection.
- A to-many relation is never optional. The absence of children is an empty
  collection, not a missing value.

A self-referencing foreign key produces both directions on the same table.

`build_relation_graph()` indexes those relations by the table they start from,
so a schema can be navigated in either direction:

```python
from pyoq.relations import build_relation_graph

graph = build_relation_graph(snapshot)
for relation in graph.to_many_from(graph.resolve(team_reference)):
    print(relation.target.table.name.value)
```

Endpoints and lookups are resolved to the way the snapshot names a table, so a
foreign key written against a bare name reaches the qualified table it means,
and a caller need not know how the snapshot qualified anything. A bare name
that two schemas both carry is left alone, because it names neither.

A snapshot describes one database and a foreign key may point beyond it, so a
target the snapshot does not describe is reported through
`unresolved_targets` rather than refused. Such a table stays navigable, because
the table that declared the key still knows its side of it; only asking for the
missing table's definition fails, and it fails by name. A key naming a column
that a table it did resolve does not have is refused outright, because that is
corruption rather than partial coverage.

Generation emits both directions of every key as typed constants, each
carrying its cardinality and whether it may resolve to nothing:

```python
from generated.relations import (
    EMPLOYEE_TEAM_TEAM_ID,
    TEAM_EMPLOYEE_TEAM_ID_REVERSE,
)

EMPLOYEE_TEAM_TEAM_ID.to_one  # True: an employee has one team
TEAM_EMPLOYEE_TEAM_ID_REVERSE.to_one  # False: a team has many employees
```

A relation is named after the table across the key and the columns that carry
it, with the reverse direction saying so. Nothing in the name depends on where
a relation falls in a list, so adding a key to one table cannot rename the
relations already generated for another. A key that points at its own table
produces both directions on that table, which is why the direction is always
part of the name rather than only when it would otherwise collide. Where a
constraint has a name of its own, that name is used instead.

Two keys that no name can tell apart are reported as a naming collision rather
than resolved by guessing, and naming the constraint in the database is the fix.

### Fetch plans

A fetch plan says which relations a query brings back and how, and it is read
back before anything runs:

```python
from pyoq.relations import FetchPlan, FetchRequest, FetchStrategy

plan = FetchPlan(
    team_reference,
    (FetchRequest(employees, FetchStrategy.NESTED, (FetchRequest(badge),)),),
)
print("\n".join(plan.describe()))
```

```console
employee [] via nested
  badge via auto
```

Four strategies are available. `joined` brings a relation back in the same
query, `nested` brings a collection back as one correlated result, `select_in`
issues one further query per level, and `auto` leaves the choice to be made
against the dialect's capabilities when the query is planned, so one plan can
take the best route each database offers.

`resolve_fetch_plan()` turns that intent into a decision against what a dialect
can actually do, and records why:

```console
posts ordered by id via select-in: this dialect cannot order inside an
  aggregate, and this collection's order matters
  users via joined: a to-one relation is one row of the join
```

A fetch request says what a collection is ordered by with `by_column()` and
`by_expression()`, each of which takes a direction:

```python
from pyoq.relations import by_column, by_expression

FetchRequest(
    employees,
    order_by=(by_expression("lower(name)"), by_column("id", descending=True)),
)
```

A column is checked against the related table, because the plan can see the
schema. An expression is named rather than carried, because a plan is built
below the layer that writes queries and does not reach up into one. The name
is what the plan reports when it explains itself, and naming it is enough to
plan with: whether a collection is ordered at all is what decides how it can
be fetched, not what it is ordered by. The expression itself is given to
whatever carries the plan out.

A to-one relation rides the join, because it is one row the join already
carries. A collection is nested where the dialect can aggregate one, and falls
back to select-in where it cannot, or where the collection's order matters and
the dialect cannot order inside an aggregate. That last case is measured rather
than assumed: SQLite from 3.44 and PostgreSQL both accept an order inside their
aggregate, while MySQL rejects it outright, and MySQL discards an order given in
a derived table instead of honouring it. An aggregate in the wrong order is
worse than a second query, so PyOQ takes the second query.

A plan that names a strategy the dialect cannot carry out is refused rather than
quietly changed, because a caller who asked for an ordered nested collection and
received an unordered one has no way to notice.

`validate_fetch_plan()` checks a plan against the relation graph once, before
any query runs, so a mistake is reported against the schema rather than part-way
through a fetch. A relation the table does not have is refused, a nested
relation is checked against its own table rather than the root, and fetching one
relation twice at the same level is refused. The same relation may appear at
different levels, because a key pointing back is a different fetch rather than a
repeat, and that is also why plan depth is bounded.

### Relation loading state

A relation is loaded, known to be absent, or was never fetched. Rows are
detached values, so reading one never reaches a database, which is what makes
the third state necessary: without it a relation nobody asked for would be
indistinguishable from one that resolved to nothing.

```python
team.employees.value  # the fetched rows
person.manager.value  # None, when the key resolved to nothing
person.badge.value  # raises: this relation was not fetched
```

Reading a relation nobody fetched raises rather than answering, because any
answer would be a guess and no answer can be produced without I/O.

Run the model against a reflected database with:

```console
python examples/relation_model.py
```

## Row identity

A join repeats a parent row once per child, and an outer join invents a child
made entirely of nulls. Both are answered by asking what identifies a row, so
identity is decided before any row is built:

```python
from pyoq.hydration import IdentityMap, row_key_for

key = row_key_for(table, reference, projection.position_of)
identity = key.identify(result_row)  # None when the result holds no row here
```

A primary key identifies a table. Failing that, a unique key no part of which
is nullable. A table offering neither is identified by everything projected
from it, which is the only sound answer left: rows it cannot tell apart are
rows nobody can tell apart. A null among the identifying values means the
result holds no row there rather than a row whose identity happens to be null,
which is how an absent child is recognized.

Identity is by value however a driver spells it, so buffers and arrays compare
by their contents. A value no dictionary can hold, or a key column the query
does not select, is reported against the table it belongs to rather than
surfacing as a bare type or lookup error from inside a fetch.

`IdentityMap` builds each distinct row once. It lives for one fetch and is
discarded with it, so a row it returns can never be a stale row from an earlier
fetch.

## Hydration

A join hands back one row per combination, so the same parent arrives once per
child and the same child can arrive under several parents. `hydrate()` reads a
result back into rows that carry what the plan asked for:

```python
from pyoq.hydration import hydrate

teams = hydrate(node, result_rows)
```

Rows come back in the order the result first mentioned them, a row the result
mentions twice is built once, and children keep the order they first appeared
in. A null among a child's identifying values means the result holds no child
there, so an outer join yields an empty collection or an absent value rather
than a row made of nulls.

The result is read twice: once to group it and once to construct it. A frozen
row cannot be handed its children after it exists, so its children have to be
known before it is built.

One table read at two places in a plan yields separate rows, because each
carries different children and one row cannot hold both. Within one place, a
child several parents share is one object.

A to-one relation that returns several distinct rows for one parent is
reported. The schema said those columns were unique and the result disagreed,
and discarding rows would hide the disagreement.

Throughput and memory are held to a contract:

```console
python -m benchmarks.hydration
```

## Query diagnostics

A query issued once per row of a previous result is invisible from inside the
loop that causes it. Recording what a scope executed makes it visible from
outside:

```python
from pyoq.diagnostics import QueryObserver

observer = QueryObserver()
...
for repeated in observer.repeated():
    print(repeated.describe())
```

```console
4 executions of SELECT id FROM person WHERE team_id = ? from app/teams.py:31 in members
```

One query is recognized across the many times it is executed. Its bound values
are already placeholders, a list of any length collapses to one, a write chunked
to fit a parameter limit collapses however many chunks it took, and the three
placeholder styles reach the same shape, so one query compiled for three
dialects is one shape. Placeholders that are not a list stay apart, so a query
with two conditions never merges into one with a single condition.

A report carries the first place outside PyOQ that issued the query, which is
the caller's own code rather than the library's. An interpreter that offers no
frames reports no place rather than refusing to run.

Diagnostics are safe to log. A shape carries no bound value, so nothing a query
was asked about appears in a report, and that holds whether or not a parameter
was marked sensitive.

Diagnostics must not become the thing that exhausts a process, so the number of
distinct shapes and the number of places per shape are both capped. Executions
keep being counted after the caps are reached, and shapes seen beyond the cap
are counted through `unrecorded_shapes`.

An observer is safe to share. A pool hands connections to whichever thread asks,
so reading a report while several threads record must not fail.

### Query budgets

A repeated query that is only reported is a repeated query that still ships. A
budget turns the same observation into a refusal:

```python
from pyoq.diagnostics import QueryBudget, QueryScope

scope = QueryScope(QueryBudget(maximum_repeats=3))
```

```console
one statement ran 4 times in this scope, beyond the 3 it was allowed
from app/teams.py:31 in members: SELECT id FROM person WHERE team_id = ?
```

`maximum_repeats` is the one that catches an N+1 access, because the shape
executed once per row of an earlier result is the shape that repeats.
`maximum_queries` caps the scope as a whole. A refusal names the shape, the
count, and the place, and carries no bound value. The statement that caused it
is recorded before it is judged, so a report taken afterwards explains the
refusal.

Holding a budget costs almost nothing per statement, because only the shape
just recorded is judged rather than every shape a scope has seen.

### Select-in fetching

Select-in is the strategy that turns an N+1 access into a bounded number of
statements. `select_in_conditions()` produces one predicate per batch:

```python
from pyoq.fetching import select_in_conditions

for condition in select_in_conditions(
    relation, columns, parent_keys, maximum_parameters=999
):
    rows = database.many(select(...).from_(source).where(condition))
```

A key of one column becomes a membership test. A key of several becomes a
choice between equalities rather than a row value, because every dialect
renders the first alike and they do not all render the second alike.

The predicate is built from expression nodes rather than through the typed
query API. Keys come back from a database as values whose types the caller has
already checked, so routing them through a typed column would fight the column's
own type without protecting the query the caller wrote.

A contract proves the reduction on SQLite, PostgreSQL, and MySQL alike: the same
rows come back from far fewer statements than asking once per parent.

### Joined fetching

A to-one relation is one row the join already carries, so it costs no extra
statement. What it costs is knowing where each table's values land:

```python
from pyoq.fetching import join_condition, projection_layout

child_at, parent_at = projection_layout((2, 2))
query = (
    select(*child_columns, *parent_columns)
    .from_(table_source(children))
    .left_join(table_source(parents))
    .on(join_condition(child_key, parent_key))
)
```

Hydration reads a result by position, so the layout a projection is laid out
with and the layout it is read back by have to be the same one. Deriving both
from `projection_layout()` keeps them from drifting apart as a plan grows.

A left join leaves a to-one relation absent where nothing matched, which the
relation states already distinguish from a relation nobody fetched. This is
proved on SQLite, PostgreSQL, and MySQL alike: one query, hydrated back into
parents carrying their relation, with the unmatched half absent.

### Nested collections

A nested collection gathers a relation's rows into one value beside their
parent, so one statement returns the graph:

```console
SELECT "parent"."id",
       (SELECT COALESCE(json_group_array(json_object('id', "child"."id")
                        ORDER BY "child"."id"), json_array())
        FROM "child" WHERE ("child"."parent_id" = "parent"."id"))
FROM "parent"
```

Each dialect gathers rows with its own functions: `json_group_array` and
`json_object` on SQLite, `jsonb_agg` and `jsonb_build_object` on PostgreSQL,
`JSON_ARRAYAGG` and `JSON_OBJECT` on MySQL. An empty relation is coalesced to an
empty collection rather than left null, so a caller need not tell a relation
with no rows from a fetch that did not happen.

MySQL refuses an ordered collection rather than returning one in the wrong
order, and says to fetch it with select-in instead. Ordering inside a SQLite
aggregate needs SQLite 3.44 or later.

A collection reads one table, because a collection is the rows of one relation
and anything wider is a query rather than a relation.

`decode_collection()` reads the value back into rows. What a driver hands over
differs: SQLite and MySQL return JSON text while PostgreSQL returns a list it
has already decoded, and both arrive at the same rows. Values come back in the
order their names were given, so a caller reads them like any other row.

Nothing is trusted to hold what it promised. A collection that is not a list of
objects is refused, and a row missing a value it was asked for is reported by
name and position. A value that is null is a value, because a column that held
null is not a column that was missing. Values a query did not ask for are left
alone, so a table gaining a column does not break a fetch.

`hydrate_collection()` turns that into a loaded relation. An empty collection is
loaded and holds nothing, which is not the same as a relation nobody fetched.

### Relation batching

An N+1 access asks for one parent's children at a time. Gathering the keys first
turns that into one question, and a dialect's parameter limit turns it into a
bounded number of questions rather than one per parent:

```python
from pyoq.relations import RelationBatchLoader

loader = RelationBatchLoader()
for team in teams:
    loader.enqueue(members, team.key)
batches = loader.batches(maximum_parameters=999)
```

A key spanning several columns costs one bound value per column, so a limit
counts values rather than keys, and a key wider than the limit is refused rather
than split. Keys keep their order and never repeat. A key that is null reaches
nothing, so it is counted through `skipped_keys` rather than asked about, which
would cost a parameter to learn nothing.

A loader belongs to one scope, and each relation it holds is bounded on its own.

## One chain, from building to reading

A builder describes a statement and never reaches a database. That is what makes
one safe to share, cache, and test, and it does not change. A context adds the
other half: it holds the database to run against and hands back builders that
carry it, so a chain can end in a fetch instead of being handed to an executor.

```python
from pyoq.dsl import using

dsl = using(database)

dsl.select(TITLE_ID, TITLE_NAME, TITLE_PRICE) \
   .from_(table_source(TITLES)) \
   .where(TITLE_PRICE.gt(Decimal("10.00"))) \
   .order_by(TITLE_ID) \
   .fetch_all() \
   .to_list()
```

A generated table names its own columns, so a query over all of them does not
have to list them:

```python
dsl.select_from(TITLES) \
   .where(TITLE_PRICE.gt(Decimal("10.00"))) \
   .order_by(TITLE_ID) \
   .fetch_all() \
   .into(TitleRow)
```

The SQL is `SELECT *`. PyOQ still carries the column list, so the row keeps
its names and each value is read back as the type the column holds, and that
order is the order the generated row type takes its arguments in. A package
that no longer matches its schema is refused rather than read by the wrong
columns.

The shape a caller wants is asked for at the end rather than assembled around
the call:

```python
rows = dsl.select(...).from_(...).fetch_all()

rows.to_list()          # [(1, "A Guide", Decimal("12.50")), ...]
rows.into(Title)        # [Title(1, "A Guide", Decimal("12.50")), ...]
rows.map(lambda r: r[1])
rows.to_dicts()         # [{"id": 1, "name": "A Guide", ...}, ...]
rows.to_dict()          # one row keyed, or an error saying which way it failed
rows.one()              # exactly one row, or an error saying which way it failed
rows.first()            # the row at the front, or None
```

A chain that ends in one row asks the same questions of it. The row is a
tuple, so it still unpacks, indexes, and compares the way it always did:

```python
row = dsl.select(TITLE_ID, TITLE_NAME).from_(...).fetch_one()

row.to_dict()           # {"id": 1, "name": "A Guide"}
row.to_list()           # [1, "A Guide"]
row.to_tuple()          # (1, "A Guide"), typed as it was selected
row.into(Title)         # Title(1, "A Guide")
identifier, name = row  # still a tuple
```

A row that may be missing is the one shape that had to be tested for before
it could be built, so the chain takes the type instead:

```python
dsl.select(...).where(...).fetch_optional_into(Title)   # Title | None
```

`into` spreads each row across a constructor in the order it was selected, so a
dataclass or a named tuple needs nothing else. `to_dicts` keys by the name each
column came back under, and refuses when a column has none rather than inventing
one.

A query can end in a single row or a single value instead:

```python
dsl.select(TITLE_NAME).from_(source).where(TITLE_ID.eq(1)).fetch_one()
dsl.select(TITLE_NAME).from_(source).where(TITLE_ID.eq(1)).fetch_optional()
dsl.select(TITLE_NAME).from_(source).where(TITLE_ID.eq(1)).fetch_value()
```

`fetch_value` is only offered to a query that selected one column. That is a
statement about the type rather than a check when it runs.

Writes chain the same way and end in the number of rows the database wrote:

```python
dsl.insert_into(TITLES, TITLE_ID, TITLE_NAME).values(1, "A Guide").execute()
dsl.update(TITLES).set(TITLE_PRICE, Decimal("20.00")).where(TITLE_ID.eq(1)).execute()
dsl.delete_from(TITLES).where(TITLE_ID.eq(1)).execute()

dsl.insert_into(TITLES, TITLE_ID, TITLE_NAME) \
   .values(1, "A Guide") \
   .on_conflict_do_update(TITLE_ID) \
   .set(TITLE_NAME, "A Guide") \
   .returning(TITLE_ID, TITLE_NAME) \
   .fetch_all() \
   .into(Title)
```

A set operation orders by a column it selected, because its operands may read
different tables and a table-qualified name means nothing once they are
combined. Naming anything else there is refused rather than sent to a server
that would reject it.

Types are carried the whole way. `select(TITLE_ID, TITLE_NAME, TITLE_PRICE)`
gives a chain over `tuple[int, str, Decimal]`, `fetch_all().to_list()` is a
`list[tuple[int, str, Decimal]]`, and `fetch_one()` is the tuple itself.

Unwrapping a bound query with `query`, or a bound write with `statement`, gives
back the ordinary builder, which cannot reach a database. Anything built that
way still runs through `dsl.fetch(...)` or `dsl.execute(...)`.

### An asynchronous database

`using` answers the same question either way, so an asynchronous database gives
an asynchronous context. Only the ending changes:

```python
rows = await (
    using(database)
    .select(TITLE_ID, TITLE_NAME)
    .from_(table_source(TITLES))
    .order_by(TITLE_ID)
    .fetch_all()
)
rows.into(Title)

await using(database).insert_into(TITLES, TITLE_ID).values(1).execute()
```

The chain is built the same way and describes the same statement. `fetch_all`,
`fetch_one`, `fetch_optional`, `fetch_value`, and `execute` are awaited because
the database is, and the types carry through exactly as they do synchronously.

## Django

Django 4.2 through 6.x, on any Python each of them supports.

[`docs/django.md`](docs/django.md) is a step by step guide from an empty project
to typed reads, writes, transactions, policies, and events. What follows is the
reference.

PyOQ runs on the connection Django already has open:

```python
from pyoq.django import DjangoOperations

database = DjangoOperations()  # or DjangoOperations("reporting")
rows = database.many(select(...).from_(source))
```

```console
python -m pip install "pyoq-sql[django]"
```

No pool is opened beside Django's. Two pools against one database is two views
of what has been committed, and the point of running inside Django is that there
is one. The connection is resolved for each statement rather than held, because
Django gives each thread a different one and closes them between requests.

The dialect comes from the connection rather than from the caller, since it is
the connection that is on the socket. Values are adapted the same way they are
on PyOQ's own connections, because a backend does not care whose connection it
is: a decimal or a date still has to reach it in the shape its driver accepts.
A backend PyOQ has no dialect for is refused by name.

A timeout is applied where the backend has a setting for one, and the session is
left exactly as it was found. Inside a transaction the setting is local, so the
database reverts it when the transaction ends; outside one, the previous value is
read first and put back after. A restore never replaces the failure that made it
necessary, because a statement that failed can leave a connection unable to
accept the next one. SQLite has no such setting, and MySQL's covers reads only,
so a caller is given what the backend can honour rather than a promise it
cannot keep. A caller's cancellation is honoured before and after a statement
regardless.

Routing is a question about a model, because that is the only thing a Django
router is given to decide on:

```python
database = DjangoOperations.for_model(Report)  # wherever this is routed
database = DjangoOperations.for_model(Report, write=True)
```

A caller without a model names the alias instead.

### Transactions

PyOQ takes part in the transaction Django opened and never opens one of its own.
There is no commit or rollback on `DjangoOperations`, because the block that
starts a transaction is the one that ends it:

```python
with database.atomic():
    database.execute(insert_into(REPORTS, name).values("quarterly"))
    database.on_commit(lambda: notify("saved"))
```

Ask the database for its own block rather than reaching for Django's. Django's
`transaction.atomic()` covers the default alias unless told which to use, so a
caller working against another database inside a bare block has no transaction
there at all and nothing reports it. `database.atomic()` always means this
database. The same holds for `database.on_commit()`, which waits for this
database to commit.

Nesting a block is a savepoint, and `atomic(savepoint=False)`,
`atomic(durable=True)`, and `on_commit(robust=True)` behave as Django defines
them. `database.in_transaction` reports whether a transaction is open.

Test isolation needs nothing extra. A Django test case wraps its test in a block
and rolls it back, and PyOQ writes through the connection that block belongs to.

An async view reaches a statement through Django's own bridging:

```python
await sync_to_async(read_reports, thread_sensitive=True)(database)
```

Django refuses a synchronous statement called directly from a coroutine, and
PyOQ inherits that guard. Nothing here shares transaction state across the
boundary, because a transaction belongs to the connection that a thread holds.

### Generating from migrations

Add PyOQ to the project and tell it where generated code belongs:

```python
INSTALLED_APPS = ["pyoq.django", ...]

PYOQ = {
    "codegen_directory": "app/_generated",
    "codegen_package": "app._generated",
}
```

```bash
python manage.py pyoq_codegen
python manage.py pyoq_codegen catalog --database reporting
python manage.py pyoq_codegen --dry-run
python manage.py pyoq_codegen --check
```

The schema comes from the migration files, not from a database. Migrations are
what the schema is going to be, and a developer generating types has usually
not applied them yet. Nothing connects: Django answers what column a field
takes on a backend without consulting a server, so the alias selects the
dialect rather than opening a socket.

What that yields is the schema the database will really have, which is not
always the one the models appear to describe:

- A Django default is applied in Python, so the column has no default. PyOQ
  says so rather than inviting an insert the database would refuse. The model's
  default is kept as column metadata.
- `on_delete` is carried out by Django, and the constraint it writes names no
  action at all. The relation records what the database will do, with the
  model's choice beside it.
- A unique constraint carrying a condition is not a key. It is unique among the
  rows it matches and not among the others, and treating it as a key would
  describe a relation reaching many rows as reaching one.
- A many-to-many field owns a table that migration state never lists, so PyOQ
  describes it from the field. A through model the project wrote is already a
  model and is left alone.

Generation itself is the same pipeline every other entry point uses, so
staging, validation, the manifest, drift detection, and the atomic replacement
behave identically here.

To generate after every migration, put PyOQ ahead of the app that supplies the
original command and ask for it:

```python
PYOQ = {..., "codegen_after_makemigrations": True}
```

Django's own command runs first and unchanged. A run that writes no migration,
such as `--dry-run` or `--check`, generates nothing: the files on disk still
describe the schema before the change, and generating from them would look like
it had worked.

### A project to read

[`examples/django_project`](examples/django_project) is a project laid out the
way a real one is: settings, a URLconf, WSGI and ASGI entry points, the admin,
two applications with models and migrations, a template, and views. It has two
databases and a router, and its models are chosen to show what reaches the
database and what stays in Python.

[`examples/django_integration.py`](examples/django_integration.py) applies its
migrations, generates from them, writes through the ORM and PyOQ on the same
connection, and then exercises the project through its own views, including an
async one, along with transactions, savepoints, and commit hooks:

```bash
python examples/django_integration.py
```

## FastAPI

The pool belongs to the application, so the lifespan opens it once and gives it
back once:

```python
from pyoq.fastapi import SyncDatabase, database_lifespan

def open_notes() -> SyncDatabase:
    pool = SQLitePool(SQLiteConnectionFactory(path))
    return SyncDatabase(SQLiteExecutor(pool), pool.close)

app = FastAPI(lifespan=database_lifespan(synchronous={"default": open_notes}))
```

A database that fails to open takes the application down, and the ones already
open are given back first. Starting up halfway and serving requests against a
partly opened application is worse than not starting.

Routes ask for what they need:

```python
import pyoq.fastapi as pyoq_fastapi

Database = Annotated[QueryOperations, Depends(pyoq_fastapi.database())]
Transaction = Annotated[QueryOperations, Depends(pyoq_fastapi.transaction())]

@app.get("/notes")
def list_notes(database: Database) -> dict[str, object]:
    return {"notes": using(database).select(...).fetch_all().to_dicts()}

@app.post("/notes")
def add_note(database: Transaction) -> dict[str, int]:
    return {"written": using(database).insert_into(...).values(...).execute()}
```

A request that returns commits. A request that raises rolls back, and so does
one the client gave up on: FastAPI tears the dependency down either way, and the
transaction is told what tore it down. An `HTTPException` counts as raising,
because a refused request is still a refused request.

Asking twice for the same dependency gives back the same object, so an override
matches:

```python
app.dependency_overrides[pyoq_fastapi.database()] = lambda: substitute
```

### Which database a route may use

`database()` and `transaction()` are synchronous. `async_database()` and
`async_transaction()` are their counterparts.

A synchronous transaction belongs to a synchronous route. FastAPI runs a
synchronous dependency in a worker thread and an async route body on the event
loop, and a transaction belongs to the thread that opened it. PyOQ refuses the
mismatch rather than running a statement somewhere it does not belong, so an
async route takes `async_transaction()`.

### Request budgets

A budget is per request, so one request cannot spend another's allowance:

```python
Budgeted = Annotated[
    QueryOperations,
    Depends(pyoq_fastapi.scoped(QueryBudget(maximum_queries=20))),
]
```

Every read and write is recorded before the driver sees it, so the statement
that goes beyond the allowance never reaches the database. `maximum_repeats` is
the one that catches a query running once per row of an earlier result.

`ScopedOperations` and `AsyncScopedOperations` do the counting and are not
specific to FastAPI. Either wraps any database:

```python
from pyoq.diagnostics import QueryScope, ScopedOperations

scope = QueryScope(QueryBudget(maximum_queries=20))
counted = ScopedOperations(database, scope)
```

See [`examples/fastapi_application.py`](examples/fastapi_application.py) for a
runnable application covering all of it.

## Sanic

Sanic starts its workers as separate processes. A connection made before they
exist would be shared by all of them, and two processes talking over one socket
corrupt each other's results. So every pool is opened from the listener that
runs inside each worker:

```python
from pyoq.sanic import AsyncDatabase, attach_databases

async def open_notes() -> AsyncDatabase:
    pool = await PostgresAsyncPool(PostgresAsyncConnectionFactory(dsn)).open()
    return AsyncDatabase(PostgresAsyncExecutor(pool), pool.close)

attach_databases(app, asynchronous={"default": open_notes})
```

A database that fails to open takes the worker down, and the ones already open
are given back first. Every one a worker opened is closed when it stops.

### One transaction per request

A transaction belongs to the block the handler enters:

```python
import pyoq.sanic as pyoq_sanic

@app.post("/notes")
async def add_note(request):
    async with pyoq_sanic.transaction(request) as database:
        ...
```

Leaving the block normally commits. Leaving it any other way rolls back: a
handler that raised, and a request the server abandoned.

It is a block rather than a pair of middlewares for a reason. A server that is
told the client has gone cancels the task it called the application on, and
nothing after the handler runs, response middleware included. A transaction
ended there would keep its connection for as long as the worker lives, inside an
open transaction, holding whatever it locked. Python ends a block whatever
happens to the task running it, so the connection goes back to the pool.

A commit that fails raises on the way out of the block, which Sanic answers the
way it answers any other failure in a handler.

`synchronous_transaction` is the same for a database that is not awaited.

### Request budgets

```python
pyoq_sanic.attach_request_budget(app, QueryBudget(maximum_queries=20))

@app.get("/notes")
async def list_notes(request):
    database = pyoq_sanic.budgeted_of(request)
```

A budget belongs to the request, so one request cannot spend another's.
`attach_synchronous_request_budget` and `synchronous_budgeted_of` are the
counterparts.

See [`examples/sanic_application.py`](examples/sanic_application.py) for a
runnable application covering all of it.

## Policies

A rule written against SQL text can be walked around with an alias, a subquery,
or a common table. A policy is written against the statement's own nodes, before
any SQL exists, so every one of those routes carries it:

```python
from pyoq.policies import SoftDelete, TenantScope, governed

scoped = governed(database, [TenantScope("tenant_id", 7), SoftDelete("deleted_at")])
```

```sql
SELECT COUNT(*) FROM "notes" AS "n"
WHERE (("n"."tenant_id" = ?) AND ("n"."deleted_at" IS NULL))
```

The condition follows the name the statement gave the table, joins are scoped
beside it, a subquery and a common table are scoped inside themselves, and both
halves of a set operation are scoped. A write carries the scope as a value, so a
row cannot be created outside the scope that would then be unable to see it, and
a caller that names the column itself has its value replaced rather than
honoured. An update or a delete that said it meant every row now means every row
the policy admits.

### What is available

- `TenantScope(column, value)` puts every read and every write inside one scope
- `SoftDelete(column)` keeps a row marked deleted out of every read
- `AllowedTables(names)` refuses a statement that touches anything else
- `RowConstraint(table, build)` narrows one table by a condition of your own

### What cannot be governed

SQL that was compiled elsewhere has no statement left to read, so it is refused
rather than quietly allowed. So is raw SQL written into an expression. A project
that means to run them says so once:

```python
governed(database, [...], raw_sql=True)
```

### Transactions and streams

A governed database opens its own transactions and streams its own reads, both
held to the same rules:

```python
with scoped.transaction() as active:
    ...
with scoped.stream(query) as rows:
    ...
```

Without those a caller would reach past the policy to the database underneath to
open one, and everything inside it would be ungoverned. A dialect's own
streaming settings belong to the database underneath, which is where it was
given them.

### Going around a policy

```python
with administrative_bypass(scoped, "restoring a withdrawn note", audit) as free:
    ...
```

The ungoverned database is yielded rather than returned, so it belongs to the
block and cannot be kept past it. A reason is required, and entering and leaving
are both written to the audit.

See [`examples/policies.py`](examples/policies.py) for a runnable walkthrough.

## Reading a column as the type it was declared to hold

A driver answers with what its own protocol carries, which is not always what a
column was declared to be. SQLite hands back a float for a decimal and a string
for a date, and psycopg hands back a view of its buffer for bytes. A descriptor
that says `Decimal` and yields a float is a promise the runtime does not keep,
and money is the usual casualty.

A descriptor carries the type it was declared with, so a row is given back as
the row was described:

```python
PRICE = ColumnDescriptor[Decimal](..., value_type=Decimal)

row = using(database).select(PRICE).from_(source).fetch_one()
# Decimal('12.50'), not 12.5
```

Generated descriptors declare it for you. `Decimal`, `date`, `datetime`,
`time`, `timedelta`, `UUID`, `bytes`, `bool`, `int`, `float`, `str`, and JSON
are read from whatever a driver answered with. A generated enumeration reads
back as a member of itself rather than as the string stored for it, and an
array column reads back as the tuple its descriptor declares rather than as
the list a driver hands over.

This happens on every path that answers with rows: `fetch_one`, `fetch_all`,
scalars, bulk statements that return rows, streams, and the asynchronous
counterpart of each. A driver decides what it carries and decoding decides
what a caller is given, so two paths that decode differently would answer the
same query with different types.

A column that declared nothing is handed back exactly as it came, so a caller
that never said what it wanted pays nothing for the question.

A value that cannot be read as what it was declared to be raises, because
handing back the wrong type silently is the defect this exists to stop. What a
driver already lost is lost: PyOQ converts what it is given and cannot recover
precision a database did not keep.

A nested collection arrives as JSON, which spells a date as a string and a
decimal as a number, so it is told what its columns hold in the same way:

```python
decode_collection(payload, ("id", "released"), (int, date))
hydrate_collection(payload, ("id", "released"), construct, (int, date))
```

## Observability

Instrumentation is a database that wraps another one, so a project that wants
none holds the plain database and pays nothing at all:

```python
from pyoq.diagnostics import CollectingSink, InstrumentedOperations

watched = InstrumentedOperations(database, sink)
```

Every statement is reported before it reaches a driver and again when it is
answered, at the one place they all pass. An event carries the shape of the
statement, how many parameters it had, how long it took, and how many rows came
back.

### What an event may carry

Nothing a query was asked about, unless a policy says so:

```python
InstrumentedOperations(database, sink, EventPolicy(
    include_values=True,          # never the ones marked sensitive
    include_failure_detail=True,  # a driver names the offending value in it
    slow_after=0.5,
))
```

A failure is reported by the name of its type. PostgreSQL states the value that
caused it inside the message it raises, so the message is withheld until a
project decides otherwise. A value the compiler marked sensitive is left out
even when a policy asks for values, because marking it was the decision that it
must not be shown.

A shape has its literals taken out as well as its placeholders, so a statement
written by hand can be logged as safely as one PyOQ built. The statement is read
once, left to right, because what a character means depends on what it is
inside: a quote inside a comment starts nothing, and two dashes inside a string
are not a comment. Quoted strings, dollar quoted strings, and every kind of
comment go, nested ones whole; quoted names stay, because a name is not a value.
A number is taken out however it was written, and an opening that was never
closed takes the rest of the statement with it, because nothing after it can be
shown to be safe. What a shape carries is
bounded, while its digest is taken from the whole statement, so two that differ
only past the bound are still told apart.

### Tracing

```python
from pyoq.tracing import TracingSink

watched = InstrumentedOperations(database, TracingSink())
```

Each answered statement becomes a span carrying the same facts the event does.
A span is opened and closed when the statement is answered, because that is the
event that knows how long it took.

### Readings

```python
from pyoq.diagnostics import metrics_of, shape_cache_metrics

metrics_of(pool)          # open, idle, checked out, and how much is in use
shape_cache_metrics()     # how often a shape was already known
```

Events say what happened; a reading says what is happening now. The shape cache
is bounded, because the statements a process runs are not.

See [`examples/observability.py`](examples/observability.py) for a runnable
walkthrough, and `benchmarks/observability.py` for the cost it is held to.

## Generated names

`NamingPolicy` converts source identifiers into valid Python names as one
immutable batch. It supports snake case, Pascal case, and upper snake case.
Normalization uses Unicode NFKC and Python's locale-independent case rules.
Python keywords, soft keywords, and names reserved by a generated component
receive trailing underscores until the name is available.

Each request has a stable key chosen by the generation planner. Scopes isolate
namespaces such as table types, row fields, and enum members:

```python
from pyoq.naming import (
    NameRequest,
    NamingPolicy,
    NamingScope,
    PythonNameStyle,
)
from pyoq.schema import Identifier

fields = NamingScope(
    name="user-fields",
    style=PythonNameStyle.SNAKE_CASE,
    requests=(
        NameRequest("first-name", Identifier("First Name")),
        NameRequest("class", Identifier("class")),
    ),
    reserved_names=("builder", "build"),
)
names = NamingPolicy().resolve((fields,))

assert names.get("user-fields", "first-name") == "first_name"
assert names.get("user-fields", "class") == "class_"
```

Ordering requests or scopes differently does not change the result. Names that
become equal after case conversion, Unicode normalization, punctuation removal,
or reserved-name escaping are not renamed by sequence. Instead,
`NamingCollisionError` reports every conflict in the batch before a renderer
can receive a partial result. This keeps generated APIs stable when database
reflection order changes.

## Schema snapshots and drift

A snapshot is the one canonical record of what a database holds. Written to a
file it becomes reviewable in a diff and usable without a database, which is
what lets generation and staleness checks run where no credentials exist.

```toml
[tool.pyoq]
schema-snapshot = "schema.json"
```

With that set, generation reads the file instead of connecting. Nothing else
changes: the same pipeline serves both and does not know which it got.

```bash
pyoq snapshot   # record what the database holds
pyoq drift      # compare the record against the database
pyoq generate   # generate from the record, with no connection
```

Recording the same database twice writes the same bytes, so the file changes
only when the schema does. It is written beside its destination and moved into
place, so a reader never sees half a snapshot and a failed write leaves the
previous record intact.

`pyoq drift` names what moved in terms of the schema rather than of the file,
so the answer is the column that changed rather than the line that differs.

```text
1 schema difference(s): added column warehouse.public.parts.weight
```

Named things are matched by name and then by value, so a renamed table reads
as one removed and one added rather than as a change to something that is no
longer there.

### After a migration

A migration changes the schema, which makes the generated package stale and
the recorded snapshot wrong. One call puts both back in step, in that order,
because a package regenerated before the snapshot is written would be built
from the schema that has just been replaced.

```python
from pyoq.migrations import after_migration_at

after_migration_at("/path/to/project")
```

Nothing there imports a migration tool, and no migration tool is a dependency
of PyOQ.

It runs after the migration has been applied, which for Alembic means after
`alembic upgrade`. A post-write hook is not that moment: Alembic runs those
inside `alembic revision`, on the revision file it has just written, while
the database is still whatever it was. Wiring PyOQ there would record the
schema the migration is about to replace, so the installed command refuses a
revision file and says where the step belongs.

Call it from `env.py`, once the migrations have run:

```python
with connectable.connect() as connection:
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()
after_migration_at(PROJECT_ROOT)
```

Or run it as a step of its own:

```bash
alembic upgrade head && pyoq-after-migration
```

The command takes the project directory, or nothing and uses the working
directory, walking upwards to the first directory holding a `pyproject.toml`.
Django, a shell script, or a CI job call `after_migration_at` directly and get
the same two steps.

## Generation pipeline

Generation is a staged application service assembled from small typed
components. Schema loading, concern rendering, syntax validation, drift
inspection, locking, cleanup, manifest storage, and package replacement have
independent interfaces and one responsibility each.

```mermaid
flowchart LR
    Source[Schema source] --> Snapshot[Immutable snapshot]
    Snapshot --> Planner[Generation planner]
    Planner --> Renderers[Concern renderers]
    Renderers --> Plan[Canonical plan]
    Plan --> Validator[Python syntax validator]
    Validator --> Lock[Project lock]
    Lock --> Drift[Drift checker]
    Drift --> DryRun[Dry run report]
    Drift --> Check[Drift check]
    Drift --> Writer[Atomic writer]
    Writer --> Stage[Staged package]
    Stage --> Manifest[Ownership manifest]
    Manifest --> Swap[Package replacement]
```

Every rendered path is relative and portable. Plans sort paths before
validation, normalize line endings, reject duplicate outputs from any concern,
and hash UTF-8 bytes with SHA-256. Python and stub files are parsed before the
project lock or generated directory can be changed.

The ownership manifest is compact, versioned, and deterministic. A generated
file is replaceable or removable only when its path is recorded and its current
checksum still matches the manifest. Existing paths without ownership records,
modified generated files, symlinks, and unsafe parent paths fail closed.
Unowned files elsewhere in the generated directory are copied forward without
content changes. Cleanup considers only stale manifest entries.

Generation uses a fail-closed project lock at `.pyoq-generation-lock`. If a
process is interrupted without releasing it, verify that no generation command
is active before removing that directory.

Write mode copies the current package into a sibling staging directory, applies
the validated plan there, and replaces the destination package only after the
new manifest is complete. A failed replacement restores the previous package.
Check mode fails on creates, updates, removals, ownership conflicts, or manifest
drift. Dry-run mode returns the same categorized change counts without writing
the generated package.

The complete in-memory example assembles every production component and runs
dry-run, write, and check modes without a database or network connection:

```console
python examples/generation_pipeline.py
```

See [`examples/generation_pipeline.py`](examples/generation_pipeline.py) for
the typed schema source and pipeline assembly. Database reflection is
introduced in a later package phase. Until a built-in schema source is
available, the default command-line services continue to report generation as
unavailable.

## Generated database types

`GeneratedTypesRenderer` turns one immutable schema snapshot into a complete
Python package. The renderer resolves all names and type mappings once, then
passes that canonical model to focused file renderers.

```mermaid
flowchart LR
    Snapshot[Schema snapshot] --> Model[Canonical generation model]
    Model --> Enums[enums.py]
    Model --> Rows[rows.py]
    Model --> Writes[writes.py]
    Model --> Tables[tables.py]
    Model --> Relations[relations.py]
    Model --> Facade[package facade]
```

Add the renderer as one concern in the generation planner:

```python
from pyoq.generation import GeneratedTypesRenderer, GenerationPlanner

planner = GenerationPlanner((GeneratedTypesRenderer(),))
```

For a `user` table, generated names follow these roles:

| Generated name | Responsibility |
|---|---|
| `User` and `USER` | Typed table descriptor and its shared instance |
| `UserRow` | Frozen result value containing every readable column |
| `UserInsert` | Frozen insert value with required and omitted-field semantics |
| `UserUpdate` | Frozen update value where every writable field may be omitted |
| `UserInsertValues` | Required and optional dictionary shape for typed boundaries |
| `UserUpdateValues` | Optional dictionary shape for update boundaries |
| `UserPrimaryKey` | Frozen primary-key value |
| `UserBuilder` | Immutable insert builder without `build()` until complete |
| `UserUpdateBuilder` | Immutable update builder with concrete field setters |

Table and column descriptors support SQL-shaped discovery. Constants use the
source database name while carrying its exact value as metadata:

```python
from application.database import USER, User

identifier_column = USER.ID
new_values = User.builder().tenant_id(7).name("Ada").build()
```

Every writable column produces a concrete setter with its exact mapped Python
type. Nullable setters accept `None`. Generated, identity, computed, and other
read-only columns expose no insert or update setter. Calling a setter returns a
new frozen builder and never performs database I/O.

Required fields use `Missing` and `Present` type states. Each required setter
adds two overloads, so generated typing grows linearly with required-column
count. The final required setter returns a distinct complete-builder subtype.
Only that subtype defines `build()`. Mypy and Pyright therefore reject both an
empty build and a partially complete build, while editors can omit `build()`
from their completion lists. Runtime incomplete builder objects also lack that
attribute.

`UserInsert` is a reusable value object. The column-oriented insert statement
API accepts it through `values_many()` alongside positional multi-row values.
Constructing it has no connection, transaction, or persistence side effect.

Each generated column descriptor records both its database name and the Python
field name used by the generated row and write values. That pairing is what
lets a write statement map a generated value onto the columns it selected
without dynamic name guessing.

Primary and unique keys become frozen exact-type values. Relationships become
typed descriptors containing aligned source and target columns, referential
actions, and source names. Relationship descriptors describe metadata only;
they do not trigger lazy loading or hidden queries.

SQL scalars map to narrow Python types, including `Decimal`, `UUID`, date and
time values, immutable tuples for arrays, generated `StrEnum` classes, and a
recursive `JsonValue` alias. Unknown source types map to `object`, never
`Any`. Generated source is deterministic and passes Ruff formatting, Mypy
strict mode, and Pyright strict mode without suppressions.

A domain is a named type with rules attached, and it maps to whatever it is
written over: a domain over `text` generates `str`, and one over
`numeric(12, 2)` generates `Decimal`. The schema keeps the name as well as the
base, because a schema that forgot it would no longer describe the database it
was read from. A domain that forbids null makes every column of it not
nullable, whatever the column itself said, because the server refuses a null
there either way. PostgreSQL has domains; SQLite and MySQL have none.

Run the in-memory rendering example without a database or network connection:

```console
python examples/generated_types.py
```

## Typed expressions

Fields, bound values, computed expressions, and conditions form an immutable
typed expression tree. Named methods keep SQL semantics explicit and preserve
the result type in Mypy and Pyright strict modes.

```python
from decimal import Decimal

from pyoq.query import bind, field

USER_ID = field(int, "id", table_name="users")
USER_NAME = field(str, "name", table_name="users")
USER_BALANCE = field(Decimal, "balance", table_name="users")
USER_TENANT = field(int, "tenant_id", table_name="users")

predicate = USER_ID.gt(0) & USER_NAME.starts_with("A")
adjusted = USER_BALANCE.add(Decimal("5.00"))
selected = USER_ID.in_(bind(1), bind(2))
```

Generated column descriptors implement the same `Expression[T]` contract, so
the generated database package is the primary field source. Comparisons,
numeric arithmetic, string operations, temporal extraction and duration
arithmetic, null predicates, ranges, membership, and boolean composition are
available without converting descriptors or losing their value types.

```mermaid
flowchart LR
    Field[Typed field] --> Node[Immutable expression node]
    Value[Python value] --> Bind[Bound value node]
    Bind --> Node
    Node --> Computed[Typed computed expression]
    Node --> Condition[Boolean condition]
    Raw[Explicit typed raw template] --> Node
```

Python values supplied to expression methods always become bound-value nodes.
They cannot become field names, operators, clauses, or raw SQL text. Null
equality is normalized to `is_null()` or `is_not_null()` nodes. Explicit raw
expressions use named placeholders that accept expression objects only:

```python
from pyoq.query import raw

distance = raw(
    float,
    "distance({origin}, {target})",
    origin=USER_ID,
    target=bind(10),
)
```

Raw placeholders reject plain values, attribute access, indexing, conversion,
and format specifications. Use `raw_condition()` when the template produces a
boolean condition. SQL rendering and ordered parameter extraction are owned by
the compiler introduced with statement construction.

Run the expression example with:

```console
python examples/expressions.py
```

### Choosing one value out of several

`case()` reads its branches in order and stops at the first that holds. The
type of the whole expression is the type of its first result, so a later branch
that disagrees is refused where it is written.

```python
from pyoq.query import case, coalesce, greatest, least, nullif

tier = (
    case()
    .when(USER_BALANCE.gt(Decimal("500.00")), "premium")
    .when(USER_BALANCE.gt(Decimal("100.00")), "standard")
    .otherwise("basic")
)
```

`otherwise()` gives the value for rows no branch claimed, and the result is not
nullable. `end()` closes the case without one, and the result is `T | None`,
because SQL answers null for a row nothing matched:

```python
flagged = case().when(USER_BALANCE.lt(Decimal("0.00")), "overdrawn").end()
```

The other three answer the same question with fixed rules. `coalesce()` takes
the first argument that is not null, and it drops the `None` from the type when
the fallback cannot be null. `USER.NICKNAME` below is a generated descriptor for
a nullable column, typed `Expression[str | None]`:

```python
from generated.database.tables import USER

label = coalesce(USER.NICKNAME, "unknown")
```

`label` is `ComputedExpression[str]`, so a row read through it needs no null
check and no cast. `nullif()` answers null when its two arguments agree, `greatest()` takes the
widest of its arguments and `least()` the narrowest:

```python
blank_as_null = nullif(USER_NAME, "")
floor_price = greatest(USER_BALANCE, Decimal("0.00"))
capped = least(USER_BALANCE, Decimal("1000.00"))
```

Every one of these is an expression like any other, so it can be selected,
ordered by, grouped by, compared, or nested inside another. Values reach them
as bound parameters, never as SQL text. SQLite has no `GREATEST` or `LEAST` and
spells them `MAX` and `MIN` over several arguments, which the SQLite compiler
emits without changing what the expression means.

### Asking for a value as another type

`cast()` gives the conversion to the database and declares what it answers
with, so the value is read back as that type rather than as whatever the driver
carried:

```python
from pyoq.query import cast

as_number = cast(USER_NAME, Decimal)
as_text = cast(USER_ID, str)
```

`as_number` is `ComputedExpression[Decimal]`, and a row read through it holds a
`Decimal` on every dialect. A cast is the only computed expression that names
its own type, so it is the only one the row decoder can hold to a promise.

Each dialect names its own types, and the names were taken from running
servers rather than from a specification:

| Target | SQLite | PostgreSQL | MySQL |
| --- | --- | --- | --- |
| `bool` | `INTEGER` | `BOOLEAN` | refused |
| `int` | `INTEGER` | `INTEGER` | `SIGNED` |
| `float` | `REAL` | `DOUBLE PRECISION` | `DOUBLE` |
| `Decimal` | `NUMERIC` | `NUMERIC` | `DECIMAL(65, 30)` |
| `str` | `TEXT` | `TEXT` | `CHAR` |
| `bytes` | `BLOB` | `BYTEA` | `BINARY` |
| `date` | refused | `DATE` | `DATE` |
| `time` | refused | `TIME` | `TIME` |
| `datetime` | refused | `TIMESTAMP` | `DATETIME` |
| `timedelta` | refused | `INTERVAL` | refused |
| `UUID` | refused | `UUID` | refused |
| `dict`, `list` | refused | `JSONB` | `JSON` |

A dialect that has no such type refuses the cast. SQLite accepts
`CAST(x AS DATE)` and answers with an integer, because an unfamiliar type name
falls back to storage affinity there instead of being rejected, so PyOQ
refuses rather than emitting a name that would return the wrong kind of value
under a promise of the right one. MySQL is asked for its largest decimal
precision, because a bare `DECIMAL` means `DECIMAL(10, 0)` and would answer
`123` for `123.45`.

A target no database type answers to is refused where it is written, not when
the query runs.

### Measuring a row against the rows around it

An aggregate collapses a group into one row. A window leaves the rows alone
and gives each one an answer computed from its neighbours, so a rank, a
running total, or the previous row's value can be selected beside the row
itself.

The clauses read in the order SQL writes them: the function, then the window
it looks through, then how that window is divided and ordered.

```python
from pyoq.query import dense_rank, lag, ntile, rank, row_number, sum_

position = row_number().over().partition_by(USER_TENANT).order_by(
    USER_BALANCE.desc()
)
standing = rank().over().order_by(USER_BALANCE.desc())
running = sum_(USER_BALANCE).over().partition_by(USER_TENANT).order_by(USER_ID)
previous = lag(USER_BALANCE).over().order_by(USER_ID)
```

`row_number()` counts from one and breaks ties arbitrarily. `rank()` gives
tied rows the same position and leaves a gap after them; `dense_rank()` gives
them the same position and leaves no gap. `ntile(n)` says which of `n` equal
buckets a row falls in. `lag()` and `lead()` read the value that many rows
back or ahead, and answer null past the edge of the window, so they are typed
`T | None`.

Any aggregate can be taken over a window instead of over a group, through the
same `over()`. `count().over()` counts every row in the window without naming
a column.

A window with no `partition_by()` covers every row. A window orders rows the
way a query does, using the same terms and the same rules, so asking for nulls
last inside a window works on every dialect just as it does outside one.

Window functions require SQLite 3.25 or later. They are governed by the
`window_functions` capability, so a build without them refuses the query
rather than emitting SQL it cannot run.

### Comparing several values as one

A composite key is one key, and asking whether a row is among a set of them
should read that way. `row()` puts columns side by side and compares them
against tuples in one predicate, instead of an OR of ANDs a reader has to
reassemble:

```python
from pyoq.query import row

wanted = row(USER_TENANT, USER_NAME).in_((1, "Ada"), (2, "Grace"))
exact = row(USER_TENANT, USER_NAME).eq((1, "Ada"))
after = row(USER_TENANT, USER_ID).gt((1, 100))
```

The tuples are checked against the columns by position, so a value of the
wrong type, in the wrong order, or a tuple of the wrong width is refused where
it is written rather than when the query runs. `row(USER_TENANT,
USER_NAME).in_((1, 2))` does not type-check, because the second column is a
string.

`in_()`, `not_in()`, `eq()`, `ne()`, `gt()`, `ge()`, `lt()`, and `le()` are
available. The ordering comparisons compare left to right, the way SQL orders
a row value, so `(tenant, id) > (1, 100)` means every row of a later tenant
and the rows of tenant one after id 100. Matching an empty set of rows is the
same nothing that an empty `in_()` already means.

A row value is two columns or more. SQL reads a single bracketed value as that
value, so a row of one is refused. Every value travels bound, exactly as it
does in any other predicate.

Membership in the rows of another query is a `semi_join()` rather than a row
value, because that is where the join conditions and their typing already
live.

### Holding the rows a query read

A row read inside a transaction can be changed by somebody else before the
transaction acts on it. A lock holds it until the transaction ends, and the
clauses read in the order SQL writes them:

```python
job = (
    select(JOB_ID, JOB_PAYLOAD)
    .from_(JOBS)
    .where(JOB_STATE.eq("pending"))
    .limit(1)
    .for_update()
    .skip_locked()
)
```

That is a work queue: each worker takes a row nobody else holds, and passes
over the ones already taken instead of waiting behind them.

`for_update()` holds the whole row. `for_share()` holds it against change
while letting others read it. `for_no_key_update()` and `for_key_share()` are
the weaker PostgreSQL locks that leave a key referenceable.

`nowait()` fails instead of waiting for a row somebody else holds, and
`skip_locked()` passes over it. Waiting is what a lock does when told neither,
so nothing is written for it. `of()` narrows the lock to some of what the
query read, which is what keeps a join from holding rows it only looked at.

Each dialect refuses what it has not got, measured against running servers.
SQLite locks the whole database rather than rows, so it refuses locking
outright and points at a transaction. MySQL has `FOR UPDATE` and `FOR SHARE`
with `NOWAIT`, `SKIP LOCKED`, and `OF`, but no weaker lock, so it refuses
`for_no_key_update()` and `for_key_share()`. PostgreSQL has all four.

A lock belongs to the query that read the rows, so a set operation carries
none.

### Reading inside a JSON value

A path is steps rather than text, because the three dialects do not agree on
how a path is written, and PostgreSQL reads another's spelling as a member
that is simply absent and answers null without complaining. A string step is a
member and an integer step is an element:

```python
country = EVENT_BODY.json_text("actor", "country")
first_tag = EVENT_BODY.json_text("tags", 0)
tag_count = EVENT_BODY.json_length("tags")
```

`json_get()` answers with JSON and `json_text()` with what that JSON says, so
`json_text()` is typed `str | None` and gives you text on every dialect even
where the driver would have handed back the number it looked like.

`json_has()` asks whether anything is at a path at all, and `json_contains()`
asks whether one JSON value holds another:

```python
verified = EVENT_BODY.json_has("actor", "verified")
from_london = EVENT_BODY.json_contains({"actor": {"city": "London"}})
```

The path is a value, so it is bound like any other and never enters the SQL.
Each dialect writes what it has:

| Asked | SQLite | PostgreSQL | MySQL |
| --- | --- | --- | --- |
| `json_get` | `-> '$.a.b'` | `#> '{a,b}'` | `JSON_EXTRACT` |
| `json_text` | `JSON_EXTRACT` | `#>> '{a,b}'` | `JSON_UNQUOTE(JSON_EXTRACT(...))` |
| `json_length` | `JSON_ARRAY_LENGTH` | `JSONB_ARRAY_LENGTH` | `JSON_LENGTH` |
| `json_has` | `JSON_TYPE(...) IS NOT NULL` | `#> ... IS NOT NULL` | `JSON_CONTAINS_PATH` |
| `json_contains` | refused | `@>` | `JSON_CONTAINS` |

SQLite has no containment operator, so it refuses rather than emulating one.
Asking any of this of a column that is not JSON is refused where it is
written.

### Asking about an array

An array column is generated as `tuple[T, ...]`, and these questions are typed
by that element type, so a value of the wrong kind is refused where it is
written:

```python
tagged = POST_TAGS.has("python")
untagged = POST_TAGS.lacks("draft")
both = POST_TAGS.contains_all(("python", "sql"))
any_of = POST_TAGS.overlaps(("python", "rust"))
inside = POST_TAGS.contained_by(("python", "sql", "rust"))
first = POST_TAGS.element(0)
count = POST_TAGS.length()
```

**Elements are counted from zero.** The column reads back as a tuple and a
tuple counts from zero, so `POST_TAGS.element(0)` is the same element as
`row.tags[0]`. SQL counts an array from one, and PyOQ writes that difference
out rather than leaving a caller to remember it. Past the end there is no
element, so the answer is null and the type is `T | None`.

`length()` is the same method that measures text, because how long a thing is
is one question whichever kind of thing it is.

Only PostgreSQL has an array type. SQLite and MySQL refuse these and say to
hold a collection as JSON or in a table of its own, rather than emulating an
array they have not got.

### Calling a function this database has

Every database grows functions the others have not got, and a toolkit that
offered only what all three share would be smaller than any of them. A vendor
function is declared once with the type it answers with, and called like any
other expression:

```python
from pyoq.config import DatabaseDialect
from pyoq.query import vendor_function, vendor_predicate

similarity = vendor_function(
    float, "similarity", dialects=(DatabaseDialect.POSTGRES,)
)
starts_with = vendor_predicate(
    "starts_with", dialects=(DatabaseDialect.POSTGRES,)
)

close = similarity(USER_NAME, bind("Ada")).gt(0.3)
prefixed = starts_with(USER_NAME, bind("Ad"))
```

`dialects` says which databases the function exists on, and a compiler for any
other refuses the query rather than sending SQL that cannot run. A declaration
that names no dialect is written wherever it is asked for, because saying
nothing means the caller did not say.

`vendor_function()` answers with the type given; `vendor_predicate()` answers
with a `Condition`, so it composes with `and_()`, `or_()`, and `where()` like
any other predicate.

Arguments are expressions and travel bound, exactly as they do everywhere
else. The **name** is not a value, because no database takes a function name
as a parameter, so it is written into the SQL and held to being a name: it
must be an identifier, optionally qualified by the schema that holds it.
Anything else is refused where it is declared.

Use `raw()` instead when what you need is not a call at all but a fragment of
SQL with a shape of its own.

### Running a stored procedure

A procedure is invoked rather than selected from, so it is a statement rather
than an expression:

```python
from pyoq.query import call

database.execute(call("record_one", bind(7)))
```

Arguments travel bound like any other value. The name is written into the SQL,
because no database takes a routine name as a parameter, so it is held to
being an identifier optionally qualified by the schema that holds it.

A procedure can answer with rows, and a schema does not describe what they
hold, so nothing can be inferred. Naming the columns is what lets the rows be
read back as the types they were said to be:

```python
rows = database.many(call("read_one", bind(0)).returning(RECORDED_VALUE))
```

PostgreSQL has no result set from a procedure and passes a value back through
an `INOUT` parameter, which `CALL` answers with as one row. MySQL answers with
whatever the procedure selected. SQLite keeps no stored procedures at all and
refuses, because there is nothing for it to run and nothing to fall back on.

A stored **function** is a function, not a statement, so it is declared with
`vendor_function()` and called wherever an expression goes.

## SELECT queries

Every join type is its own method and the clause qualifying it comes after,
so a chain reads in the order SQL is written:

```python
select(TITLE_NAME, PUBLISHER_NAME).from_(TITLES).left_join(PUBLISHERS).on(
    TITLE_PUBLISHER_ID.eq(PUBLISHER_ID)
)
```

| SQL | PyOQ |
| --- | --- |
| `INNER JOIN t ON c` | `.join(t).on(c)`, or `.inner_join(t).on(c)` |
| `LEFT JOIN t ON c` | `.left_join(t).on(c)` |
| `RIGHT JOIN t ON c` | `.right_join(t).on(c)` |
| `FULL JOIN t ON c` | `.full_join(t).on(c)` |
| `CROSS JOIN t` | `.cross_join(t)` |
| `NATURAL JOIN t` | `.natural_join(t)` |
| `NATURAL LEFT JOIN t` | `.natural_left_join(t)` |
| `NATURAL RIGHT JOIN t` | `.natural_right_join(t)` |
| `NATURAL FULL JOIN t` | `.natural_full_join(t)` |
| `JOIN t USING (a, b)` | `.join(t).using("a", "b")` |
| `WHERE EXISTS (...)` | `.semi_join(t).on(c)`, or `where(exists(q))` |
| `WHERE NOT EXISTS (...)` | `.anti_join(t).on(c)`, or `where(not_exists(q))` |
| `JOIN LATERAL (...)` | `.cross_join(q.as_lateral("name"))` |

A join is qualified once, by `ON`, `USING`, or `NATURAL`. `on_key()` takes a
generated relationship descriptor and builds the `ON` from the foreign key the
schema already declares, including one equality per column of a composite key.

A cross join and a natural join finish the chain on their own. Every other
kind is not a query until it is qualified, which both type checkers enforce.

A semi join keeps rows that have a match without bringing the match back, and
an anti join keeps rows that have none. No database writes either as a join,
so neither does the SQL: they become `EXISTS` and `NOT EXISTS`, which every
supported dialect understands. They neither collide with `where` nor depend
on being written after it.

A lateral source may read the rows to its left, one row at a time. SQLite has
none, so it refuses the query rather than evaluating the subquery once and
quietly meaning something else.

A recursive table declares typed columns before either term is built. The
column object is reused when reading from the recursive source, so autocomplete
and static checking cannot replace its type with a caller assertion:

```python
from pyoq.query import bind, column, recursive_table, select

number = column("n", int)
walk = recursive_table("walk", number)
walk_definition = walk.define(
    select(bind(1)),
    select(walk.field(number).add(1))
    .from_(walk)
    .where(walk.field(number).lt(5)),
)
numbers = select(walk.field(number)).with_(walk_definition).from_(walk_definition)
```

A write returns every column its table declares with `returning_all()`, which
reads the list generation put on the table.

`select()` retains the exact ordered projection tuple through eight fields.
Every clause returns a new query and leaves its input reusable. Fields and
generated column descriptors are the only normal structural inputs, while
Python values remain bound expression values.

```python
from pyoq.descriptors import TableDescriptor
from pyoq.query import count, field, select

USERS = TableDescriptor[object, object, object]("users")
USER_ID = field(int, "id", table_name="users")
USER_NAME = field(str, "name", table_name="users")

active_users = (
    select(USER_ID.as_("user_id"), USER_NAME, count())
    .from_(USERS)
    .where(USER_ID.gt(0))
    .group_by(USER_ID, USER_NAME)
    .having(count().gt(0))
    .order_by(USER_NAME.asc())
    .limit(20)
)
```

A query source is a table descriptor instance, never a generated table class.
The generated class is the typed column and builder namespace, and the
generated constant beside it is the value that `from_()`, `join()`,
`cross_join()`, and `table_source()` accept. Passing the class is rejected by
both strict type checkers and, for untyped callers, by a `QueryValidationError`
that states the instance requirement.

The immutable model covers projection aliases, distinct selection, table and
aliased-table sources, inner and outer joins, cross joins, predicates,
grouping, aggregate filters, ordering with explicit null placement,
pagination, derived-table subqueries, common table expressions, and set
operations. Aggregates preserve numeric and scalar result types while marking
empty-set results nullable where required.

```mermaid
flowchart LR
    Projection[Typed projections] --> Select[Immutable SELECT node]
    Source[Table or derived source] --> Select
    Join[Typed joins] --> Select
    Predicate[Boolean conditions] --> Select
    Select --> Derived[Subquery or common table]
    Select --> Set[Typed set operation]
```

Set operations require the same projection type and validate projection counts
again at runtime. Common table column lists must match the selected arity.
Structural identifiers reject empty strings and null characters. The model
does not render or execute SQL; compilation and execution own those separate
responsibilities.

Run the SELECT construction example with:

```console
python examples/select_queries.py
```

## Typed INSERT statements

`insert_into(table, *columns)` mirrors SQL and types the values by the selected
columns. Each `values()` call adds one row, and repeated calls compile into a
single multi-row statement rather than separate statements. Statement
construction is immutable and performs no I/O.

```python
from pyoq.query import insert_into

created = (
    insert_into(USERS, USER_ID, USER_NAME).values(100, "Hermann").values(101, "Alfred")
)
```

The selected columns determine the exact positional types and arity that
`values()` accepts, so a wrong order, type, or count is a static error in both
strict type checkers. Values become bound parameters; typed expressions may be
passed where a computed value is required.

Generated immutable insert values feed the same statement through
`values_many()`:

```python
statement = insert_into(USERS, USER_ID, USER_NAME).values_many(
    (User.builder().id(100).name("Hermann").build(),)
)
```

`values_many()` maps each generated value onto the selected columns by the
field name recorded on the generated column descriptor. A value that leaves a
selected column unset is rejected, because a selected column always requires a
value. Handwritten `field()` columns carry no generated field name and are
therefore positional only.

Generated columns cannot be written. Selecting one, or selecting a column the
schema marks read-only, fails with a `QueryValidationError` before any SQL is
produced.

`returning()` changes the terminal result type and produces a statement that
result operations can read:

```python
row = database.one(
    insert_into(USERS, USER_NAME).values("Hermann").returning(USER_ID, USER_NAME)
)
```

RETURNING requires SQLite 3.35 or later. It is governed by the `returning`
compiler capability, and disabling that capability makes the clause fail closed
with `UnsupportedQueryError`.

Statements without a returning clause execute through `execute()`, which
reports the affected row count and the last inserted row identifier:

```python
result = database.execute(insert_into(USERS, USER_ID, USER_NAME).values(100, "Hermann"))
```

Run the typed INSERT example with:

```console
python examples/typed_inserts.py
```

## Typed UPDATE and DELETE

`update(table)` and `delete_from(table)` build the same kind of immutable
statement. Assignments are typed by their column, and values become bound
parameters.

```python
from pyoq.query import delete_from, update

renamed = update(USERS).set(USER_NAME, "Ada Lovelace").where(USER_ID.eq(1))
removed = delete_from(USERS).where(USER_ID.eq(1))
```

A statement that would touch every row must say so. An UPDATE or DELETE with no
WHERE condition fails with a `CompilationError` before any SQL reaches the
database, so a forgotten condition cannot rewrite or empty a table:

```python
update(USERS).set(USER_ACTIVE, False).all_rows()
delete_from(USERS).all_rows()
```

`all_rows()` and `where()` exclude each other. Defining both, or defining either
twice, raises `QueryStateError`.

Generated immutable update values assign only the fields they actually set:

```python
statement = update(USERS).set_values(
    User.update_builder().name("Ada").build(),
    USER_NAME,
    USER_ACTIVE,
)
```

Unset fields are skipped, which is what makes an update value a partial update.
A value that leaves every named column unset is rejected, because that statement
would assign nothing. As with inserts, generated columns and read-only columns
cannot be assigned.

`returning()` is available on every write form and produces the same typed
terminal statement:

```python
row = database.one(
    delete_from(USERS).where(USER_ID.eq(1)).returning(USER_ID, USER_NAME)
)
```

Run the typed UPDATE and DELETE example with:

```console
python examples/typed_updates.py
```

## Conflict resolution

An INSERT can resolve a uniqueness conflict instead of failing. The conflict
target names the columns whose constraint is being resolved:

```python
from pyoq.query import excluded, insert_into

ignored = (
    insert_into(USERS, USER_ID, USER_NAME)
    .values(1, "Ada")
    .on_conflict_do_nothing(USER_ID)
)

merged = (
    insert_into(USERS, USER_ID, USER_NAME)
    .values(1, "Ada Lovelace")
    .on_conflict_do_update(USER_ID)
    .set(USER_NAME, excluded(USER_NAME))
)
```

`excluded()` refers to the row the statement tried to insert, keeping the value
type of the column it names. It is how a conflicting update reads the new value
rather than the stored one.

A conflict target must be one of the columns the statement inserts, so a typo or
a column from another table fails with a `QueryValidationError` rather than
producing a statement that never matches. `on_conflict_do_nothing()` may omit the
target, which resolves a conflict on any constraint. Resolution that updates
always requires a target, because SQLite cannot infer one.

A conflicting update can also filter which rows it touches:

```python
merged = (
    insert_into(USERS, USER_ID, USER_NAME)
    .values(1, "Ada Lovelace")
    .on_conflict_do_update(USER_ID)
    .set(USER_NAME, excluded(USER_NAME))
    .where(USER_NAME.ne("locked"))
)
```

Conflict resolution can be defined once per statement, and an update resolution
that assigns nothing fails at compilation. The `upsert` compiler capability
disables the whole feature and makes it fail closed with
`UnsupportedQueryError`.

Run the conflict resolution example with:

```console
python examples/typed_upserts.py
```

## Bulk and multi-operation writes

SQLite binds a bounded number of parameters per statement, so a large multi-row
insert cannot be one statement. The bulk planner splits it into the fewest
statements that each stay inside the budget, and it does so without executing
anything:

```python
plan = database.plan_bulk(statement)

plan.statement_count
plan.input_rows
plan.row_counts
```

Chunks are contiguous and cover every input row exactly once, so each planned
statement maps back to a known range of input rows through `start` and
`length`. That is the correlation the plan guarantees. SQLite does not define
the row order a `RETURNING` clause produces, so returned rows correlate to a
chunk rather than to an individual input row.

Planning accounts for the parameters a statement spends outside its rows.
Conflict assignments, conflict conditions, and returning projections reserve
their share of the budget before rows are packed, and rows whose values are not
plain bound parameters are measured individually rather than assumed.

```python
result = database.execute_bulk(statement)

result.rows_affected
result.statement_count
result.row_counts
```

A bulk write is only atomic inside a transaction. Executed directly, each
planned statement commits on its own, so a failure in a later chunk leaves the
earlier chunks applied. Wrap the call in a transaction when the whole batch must
succeed or fail together:

```python
with database.transaction() as transaction:
    transaction.execute_bulk(statement)
```

`many_bulk()` runs the same plan and returns the returned rows from every chunk.

Ordered multi-operation execution runs a sequence of write statements in order
under an explicit budget, so an unbounded batch cannot be submitted by accident:

```python
from pyoq.query.execution import OperationBudget

results = database.execute_all(
    (first_statement, second_statement),
    budget=OperationBudget(maximum_operations=8),
)
```

An empty sequence executes nothing and returns an empty result tuple. Execution
stops at the first failure, and the same atomicity rule applies: use a
transaction when the earlier operations must not survive a later failure.

Run the bulk write example with:

```console
python examples/bulk_writes.py
```

## SQLite compilation

`SQLiteCompiler` renders a query into immutable SQL text and a separate ordered
parameter tuple. Python values never enter the SQL string. Pagination values
use the same parameter path, and sensitive bound values are recorded by their
zero-based parameter positions.

```python
from pyoq.query import bind, select
from pyoq.query.sqlite import SQLiteCompiler

compiled = SQLiteCompiler().compile(
    select(USER_ID, USER_NAME)
    .from_(USERS)
    .where(USER_ID.gt(bind(100, sensitive=True)))
    .limit(20)
)

assert compiled.parameters == (100, 20)
assert compiled.sensitive_parameter_indexes == frozenset({0})
```

Identifiers are quoted component by component. Operator expressions are
parenthesized deterministically, null equality is rendered with `IS NULL`, and
empty membership predicates become constant boolean expressions. Derived
tables, common tables, joins, aggregates, and compound queries preserve the
textual order of their parameters.

```mermaid
flowchart LR
    Query[Immutable query tree] --> Capability[SQLite capability checks]
    Capability --> Compiler[SQLite compiler]
    Compiler --> SQL[Quoted SQL structure]
    Compiler --> Parameters[Ordered parameter tuple]
    Compiler --> Sensitive[Sensitive parameter positions]
```

Compiler capabilities are explicit and immutable. RIGHT JOIN and FULL JOIN are
disabled by default because support depends on the SQLite runtime. Recursive
common tables, explicit null ordering, raw expressions, write returning,
conflict resolution, and the maximum parameter budget can also be restricted. A query requiring a
disabled or unrepresentable feature fails with an exception from `pyoq.errors`.

The compiler also renders write statements. An INSERT compiles into one
statement whose rows share the same quoted column list, an UPDATE renders its
assignments in the order they were added, and every value in either form is a
bound parameter. An UPDATE or DELETE that has neither a WHERE condition nor an
explicit full-table opt-in fails here rather than reaching the database.

Temporal arithmetic is not translated into SQLite numeric operators. Use an
explicit typed raw expression when the intended SQLite date function is known.
Raw expression text cannot introduce bind markers, statement separators, or
SQL comment syntax. Catalog-qualified identifiers also fail because SQLite has
no matching catalog level.

Run the compiler example with:

```console
python examples/sqlite_compilation.py
```

## Synchronous SQLite execution

`SQLitePool` provides bounded, exclusive connection leases. One checked-out
connection belongs to one caller until the lease exits. Application exceptions
return healthy connections, while failures that indicate a broken connection
discard only that connection. Checkout waits are bounded by policy.

```python
from pyoq.query import select
from pyoq.query.sqlite import (
    SQLiteConnectionFactory,
    SQLiteExecutor,
    SQLitePool,
    SQLitePoolPolicy,
)

pool = SQLitePool(
    SQLiteConnectionFactory("application.sqlite"),
    SQLitePoolPolicy(maximum_size=10, checkout_timeout=5.0),
)
db = SQLiteExecutor(pool)

rows: list[tuple[int, str]] = db.many(
    select(USER_ID, USER_NAME).from_(USERS).order_by(USER_ID)
)
selected: tuple[int, str] = db.one(
    select(USER_ID, USER_NAME).from_(USERS).where(USER_ID.eq(1))
)
name: str = db.scalar(select(USER_NAME).from_(USERS).where(USER_ID.eq(1)))

pool.close()
```

`one` requires exactly one row, `one_or_none` accepts zero or one, `many`
returns all rows, and `scalar` requires a single projected value. Their return
types follow the query projection. `execute` accepts an immutable compiled
statement and returns affected-row metadata. Insert identifiers are populated
only for statements carrying explicit insert classification, which prevents
stale driver metadata from escaping.

SQLite-compatible scalar parameters are adapted before a connection is
checked out. Text, numbers, bytes, temporal values, decimal values, UUIDs,
enums, memory views, and JSON containers have deterministic representations.
Unsupported values fail with `ParameterBindingError` before pool capacity is
used.

```mermaid
flowchart LR
    Query[Typed query] --> Compiler[SQLite compiler]
    Compiler --> Adapter[Parameter adapter]
    Adapter --> Pool[Exclusive pool lease]
    Pool --> Driver[SQLite driver]
    Driver --> Cardinality[Typed result policy]
    Cardinality --> Result[Row, scalar, or execute result]
```

Run the execution example with:

```console
python examples/sqlite_execution.py
```

## Transactions and bounded streaming

`SQLiteTransaction` owns one pool lease for its complete context. Normal exit
commits, while every exception derived from `BaseException` rolls back. A
transaction can only be used by its owner thread, and only its innermost active
scope may issue work. Nested scopes use generated savepoint identifiers.

```python
from pyoq.query.sqlite import TransactionMode

with db.transaction(TransactionMode.IMMEDIATE) as transaction:
    transaction.execute(FIRST_INSERT)

    try:
        with transaction.savepoint() as nested:
            nested.execute(OPTIONAL_INSERT)
            raise ValueError("discard optional work")
    except ValueError:
        pass

    transaction.execute(FINAL_INSERT)
```

Statement failures that leave the SQLite connection healthy can be handled
inside the transaction. Connection failures make the complete transaction
unusable. A commit or savepoint failure discards the connection because its
outcome cannot be assumed safely.

`stream` returns a typed iterator that fetches at most the configured batch
size. It checks out no connection until it is entered or iterated, returns the
lease automatically on exhaustion, and closes on iteration failure. Use its
context manager whenever iteration may stop early.

```python
from pyoq.query.execution import CancellationToken, ExecutionControl
from pyoq.query.sqlite import SQLiteStreamPolicy

token = CancellationToken()
policy = SQLiteStreamPolicy(
    batch_size=128,
    control=ExecutionControl(
        timeout=2.5,
        cancellation_token=token,
        progress_steps=500,
    ),
)

with db.stream(
    select(USER_ID, USER_NAME).from_(USERS).order_by(USER_ID),
    policy=policy,
) as rows:
    for user_id, user_name in rows:
        consume(user_id, user_name)
```

Timeout and cancellation checks run before execution, through SQLite progress
callbacks, and between streamed rows. `QueryTimeoutError` and
`QueryCancelledError` are distinct typed failures. Cancellation is cooperative:
another thread may call `token.cancel()`, but it must not use the owned
connection or consume the row stream.

```mermaid
flowchart TD
    Executor[SQLiteExecutor] --> Lease[Exclusive pool lease]
    Lease --> Transaction[Owned transaction]
    Transaction --> Savepoint[Nested savepoint]
    Transaction --> Stream[Bounded row stream]
    Stream --> Batch[At most batch_size buffered rows]
    Control[Timeout or cancellation] --> Progress[SQLite progress callback]
    Progress --> Cleanup[Cursor, handler, transaction, and lease cleanup]
    Batch --> Cleanup
    Savepoint --> Cleanup
```

Run the transaction and streaming example with:

```console
python examples/sqlite_transactions.py
```

## Command line

The package installs the `pyoq` command and also supports `python -m pyoq`.

```console
pyoq --version
pyoq generate --project-root .
pyoq generate --project-root . --check
pyoq generate --project-root . --dry-run
pyoq inspect --project-root .
```

Both database commands accept `--config` for a configuration file below the
project root and `--profile` for explicit profile selection. `--check` and
`--dry-run` are mutually exclusive.

Command exit codes are stable:

- `0`: success
- `1`: command service failure
- `2`: invalid command usage
- `3`: configuration failure
- `4`: requested operation is unavailable

SQLite generation and inspection are available through the default command
services. Inspection reads tables, views, columns, defaults, generated values,
primary and unique keys, indexes, CHECK constraints, and foreign-key
relationships. Metadata is normalized before rendering, and the connection is
closed before generated files are checked or written.

Generation writes six deterministic, fully typed modules and an ownership
manifest into `codegen-directory`. Repeating a write against an unchanged
database leaves the generated directory untouched. `--check` fails on drift,
while `--dry-run` reports planned creation, update, removal, and conflict counts
without changing files. Generation runs against SQLite, PostgreSQL, and
MySQL.

Run the complete SQLite reflection and generation example with:

```console
python examples/sqlite_generation.py
```

## Performance contract

Run the native and portable kernel comparison from an editable native build:

```console
python -m benchmarks.performance_contract
```

The command checks relative throughput, traced peak memory, retained
allocations, and native artifact size. It exits unsuccessfully when a measured
kernel exceeds its calibrated budget.

## Distribution checks

Build the native wheel with the release backend:

```console
maturin build --release --locked --out wheelhouse
```

Build the universal portable wheel without compiling the native extension:

```console
hatchling build -t wheel -d wheelhouse
```

When both artifact kinds are present, validate their tags, native contents,
license, type information, and shared package metadata:

```console
python -m scripts.verify_wheels wheelhouse
```

Continuous integration builds native wheels for glibc and musl Linux on x86-64
and ARM64, macOS on Apple Silicon and x86-64, and Windows on x86-64 and ARM64.
The stable native ABI is installed and imported on every supported Python
version. The universal wheel is also installed through an incompatible-platform
resolver test to prove that it does not require Rust.

## Design goals

- Fully typed public APIs compatible with mypy and Pyright strict modes.
- Database-first schema generation with deterministic output.
- Explicit, immutable SQL query models and bound parameters.
- PostgreSQL, MySQL, and SQLite support.
- Django, FastAPI, and Sanic integrations.
- Connection pooling, transaction safety, and observable query execution.
- Fetch planning that prevents hidden I/O and N+1 query behavior.
- Native hot paths with measured throughput, allocation, and peak-memory gates.

## Supported Python

PyOQ requires Python 3.11 or newer.

## License

PyOQ is licensed under the Mozilla Public License 2.0. Applications may use it
commercially, including as part of larger proprietary products. Changes to
covered PyOQ source files remain subject to the MPL terms. See `LICENSE` for the
complete terms.
