# Databases

> Use when querying or writing any RI database — Exportmaster (DBISAM sem01/sem04), PostgreSQL x3rocs, X3/Sage1000 SQL Server, keycloak MySQL, or Parquet. DuckDB is the primary read path.

- Skill: `lawless-m/databases` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add lawless-m/databases`
- Raw SKILL.md: https://api.skillmd.com/api/skills/lawless-m/databases/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: lawless-m (https://skillmd.com/u/lawless-m)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/lawless-m/databases

---


# Databases

Environment map, dialect quirks, and tools for RI's databases. Fuller schemas, worked queries, and PgQuery usage: see `reference-databases.md`. Working C# in this folder: `ODBC.cs` (ServiceLib ODBC wrapper), `PgQuery.cs`.

## Hosts and connections

| System | Host | Connection | Notes |
|---|---|---|---|
| Exportmaster (DBISAM) | RIVSEM01 (prod), RIVSEM04 (dev) | DuckDB `sem01.<table>` (reads), `DSN=Exportmaster` ODBC (writes) | Proprietary; DuckDB is the primary read path |
| PostgreSQL | rivsprod01 | `Host=rivsprod01;Database=x3rocs;Username=<from vault>` | Use Npgsql for new code; kdbx entry `Postgres/x3rocs` |
| X3 / Sage1000 (SQL Server) | OCS1 | `DSN=OCS1;UID=sa;PWD=<from vault>;` | Same DSN for both; server to be deprecated |
| MySQL (keycloak) | rocs-production-es.ramsden-international.com:6033 | `Server=...;Port=6033;Database=keycloak;Uid=crm;Pwd=<from vault>;` | Native MySql.Data |
| DuckDB | local | `DSN=DuckDB` (ODBC) or CLI below | Reads Parquet directly; no native C# driver |

Passwords marked `<from vault>` are not recorded here: get them from the RI KeePass vault (KdbxCredentials — see the ri-service-toolkit skill) or the deployed config of a service that already uses that connection; ask Matt if neither pans out.

ODBC uses `?` placeholders; native MySQL uses `@param`; Npgsql uses `$1, $2` positional.

## DuckDB (primary read path for Exportmaster)

The custom **dbisam extension** lets DuckDB ATTACH live Exportmaster directly; both platforms come pre-wired to attach `sem01` (rivsem01/NISAINT_CS, **prod**) and `sem04` (rivsem04/NISAINT_CS, **dev**) read-only — just reference `sem01.<table>`:

- **Linux (this box)**: plain `duckdb` — `~/.duckdbrc` does the `LOAD dbisam` + ATTACHes (credentials live there too). Extension installed under `~/.duckdb/extensions/v{version}/linux_amd64/`; source repo `~/Git/Dbisam_fdw` (also has `sem01_views.sql`, `init.sql`). After a DuckDB version bump the extension must be rebuilt — see the DuckDB-Extensions skill.
- **Windows**: launcher `Y:\Data Warehouse\duckdb\duckdb.bat` (`duckdb.exe -unsigned --init init.sql`).

```bash
duckdb -c "SELECT * FROM sem01.profile WHERE SppCode = 'WAYPROMO'"
duckdb -c "DESCRIBE sem01.profile"
duckdb -f query.sql
```

**Prefer the nightly Parquet snapshots for ad-hoc tests and exploration** — they are refreshed each morning and cost the live DBISAM box nothing:

| Set | Path | Contents |
|---|---|---|
| Exportmaster tables | `\\rivsprod02\RI Services\Outputs\Parquets\em\<table>.parquet` | Full nightly dump of every EM table — `orderh`, `orderi`, `quoteh`, `quotei`, `product`, `niingred`, `risupplier`, `country`, `customer`, `prices`, `pricdetl`, … (lower-case filenames) |
| Other extracts | `\\rivsprod02\RI Services\Outputs\Parquets\<area>\*.parquet` | `rocs`, `crm`, `mis`, `Ingreds`, `poller`, `quotes`, `cs-em`, … |

```bash
duckdb -c "SELECT * FROM read_parquet('\\rivsprod02\RI Services\Outputs\Parquets\em\orderi.parquet') WHERE REF = 'A-26-081849'"
```

Mapped as `R:\Outputs\Parquets\em` on hosts with the `R:` drive. Go to `sem01.<table>` only when you need data newer than last night's snapshot.

Read-only — for writes use the e3 application or ODBC. Full DuckDB SQL works on top (joins, aggregates, `read_parquet()` overlays). The Linux install also has `postgres_scanner`, `mysql_scanner`, and `httpfs` extensions, so DuckDB can front reads on the other databases too.

## Exportmaster / DBISAM

Key tables: `PRICES` (header), `PRICDETL` (detail; join `SphLink = SpdLink`), `STOCK`, `CUSTOMER` (incl. `Profile` + `UF_AltProf1..11`), `PROFILE` (`SppGroup`+`SppCode` key; `SppPrice1/2`, `SppDiscount1/2` = list types). Details and worked queries in `reference-databases.md`.

DBISAM SQL dialect quirks (it is not standard SQL):
- No `DELETE ... JOIN` — scope deletes with `DELETE FROM t WHERE key IN (SELECT ...)`
- `TOP` goes last, not after SELECT
- No `TRIM` function
- `SELECT` requires a `FROM` clause

To verify what the dialect actually accepts, use the DCG grammar companion and engine-verified corpus in `~/Git/Dibdog` (repo name is **Dibdog** — autocorrects to "Dingo"/"Dindog"); corpus at `dbisam-dcg-project/corpus/`. Official dialect docs: https://www.elevatesoft.com/manual?action=topics&id=dbisam4&product=rsdelphi&version=XE&section=sql_reference

## PostgreSQL gotcha: fixed-length VARCHAR params

Calling a PostgreSQL function whose signature has `VARCHAR(n)` parameters fails with "function does not exist" unless you cast explicitly in the SQL: `$1::VARCHAR(15)`. Also set `NpgsqlDbType.Varchar` on the C# parameter (including for `DBNull.Value`). INTEGER/NUMERIC/TEXT need no cast. Full example in `reference-databases.md`.

## PgQuery (ad-hoc PostgreSQL from the command line)

- Production: `Y:\CSharpDLLs\PgQuery\PgQuery.exe`; source: `C:\Users\matthew.heath\Git\PgQuery` (repo `gogs@dw.ramsden-international.com:matthew.heath/PgQuery.git`)
- `PgQuery --config <config.json> --sql "..."` or `--file script.sql`, optional `--output results.txt`
- Config JSON: `{"host": "rivsprod01", "database": "x3rocs", "username": "<from vault: Postgres/x3rocs>", "password": null, "port": 5432}` — examples live in `R:\JsonParams\` (e.g. `CRMPollerFixer.config.json`)

## X3 / Sage1000

`Person` / `Company` / `Account` tables (join `Pers_CompanyId`, `Pers_AccountId`). Web permission fields on `Person`: `pers_webaccesslevel`, `pers_WebAllowBannedProducts`, `pers_WebOrderBannedProducts`, `pers_WebExportProducts`, `pers_WebDownloadImages`. Active users: `Pers_Status = 1 AND Pers_Deleted IS NULL`.

## ExecuteAndMap (optional wrapper)

Existing services (CRMPollerFixer, JordanPrice) use the ServiceLib ODBC wrapper with lambda mappers — see `ODBC.cs` in this folder. Optional for new code; plain ADO.NET is fine. Set `CommandTimeout = 60` for long DuckDB queries, and check `IsDBNull()` before reading — Exportmaster data is full of nulls.