# OxygenHub on PostgreSQL

A hub keeps its records in SQLite (`<data>/hub.db`) unless it is built with
the `postgres` feature, when it keeps them in PostgreSQL 16 or later
(oxygenhub#211). The engine is chosen when the hub is built, not when it
starts: one binary speaks one engine.

## Build and configure

```sh
cargo build --release -p oxygenhub --features postgres
```

```toml
# <data>/hub.toml
[database]
url = "postgres://hub@10.20.0.3/hub"   # the password from PGPASSWORD or the URL
schema = ""                             # empty: public
```

`OXYHUB_DATABASE_URL` and `OXYHUB_DATABASE_SCHEMA` override the file. The
hub makes the `citext` extension (in `public`) and its schema if they are
missing, then applies `crates/hub/migrations-pg`. Repositories, keys and
uploads stay in the data directory as before.

`schema = "*"` gives each data directory a schema of its own (`hub_<dir>`),
so many hubs can share one database. The tests use it.

## Two schemas, one set of queries

The queries are the same on both engines: `$n` placeholders, `ON CONFLICT`,
`TRUE`/`FALSE`, `RETURNING`, `CASE` for counting flags, window functions,
`lower()` for comparing names. Postgres is stricter than SQLite, so:

- A flag column is `BOOLEAN` on Postgres and `INTEGER` on SQLite. Bind and
  read it as `bool`, and compare it with `TRUE`/`FALSE`, never `1`/`0`.
- `SUM` of a `BIGINT` is `NUMERIC` on Postgres: `CAST(SUM(…) AS BIGINT)`.
- Every column a `GROUP BY` query returns is grouped or aggregated.
- `HAVING` and `WHERE` cannot name a `SELECT` alias: repeat the expression or
  select from a subquery.

The few places that differ choose by `cfg(feature = "postgres")`:
connecting and migrating (`db::open`), the write lock (`db::begin_write`:
`BEGIN IMMEDIATE`, or an advisory lock), full-text search (FTS5, or a
`tsvector` with `ts_headline`), JSON (`db::json_text`, `json_each`), and the
catalog backups read (`backups::catalog`). A backup taken on either engine
restores into either: flags are written as 0 or 1, blobs as hex.

## Changing the schema

`migrations-pg/0001_schema.sql` is SQLite's 0001–0072 as one. Each later
change is two files with the same number, one in `migrations` (SQLite) and
one in `migrations-pg`. `tests/schema.rs` fails when the two disagree about a
table or column.

## Testing on Postgres

```sh
docker run -d --name oxyhub-pg-test -e POSTGRES_USER=hub -e POSTGRES_PASSWORD=test \
  -p 127.0.0.1:55432:5432 postgres:16-alpine
OXYHUB_DATABASE_URL=postgres://hub:test@127.0.0.1:55432/hub OXYHUB_DATABASE_SCHEMA='*' \
  cargo test -p oxygenhub --features postgres
```

Each test makes its own schema; drop the `hub_%` schemas between runs.