view release on metacpan or  search on metacpan

docs/adr/0005-native-rdb-introspection-and-model-shape.md  view on Meta::CPAN

The normalized generation contract (`table_keys`, `table_columns_info`,
`table_pk_info`, `table_uniq_info`, `table_fk_info`, `table_is_view`, ...) is
inherited unchanged from `DBIO::Introspect::Base`, because the assembled model
follows the base's canonical single-schema shape — no Firebird-specific
override of the contract methods is needed (recorded in karr #2).

### Sub-decision A — `FetchHashKeyName = 'NAME_lc'`

`_build_model` forces `local $dbh->{FetchHashKeyName} = 'NAME_lc'`
(`Introspect.pm:38-40`) for the whole build. Firebird returns result-column
names **upper-cased**, but the fetch helpers read **lower-case** `rdb$` keys
from `fetchrow_hashref` (e.g. `$row->{'rdb$relation_name'}`). Forcing
lower-cased hash keys reconciles the two so the helpers' hash lookups hit.
(Statement handles inherit this at prepare time.)

### Sub-decision B — UNIQUE constraints vs CREATE UNIQUE INDEX are split

A Firebird `UNIQUE` *constraint* and a standalone `CREATE UNIQUE INDEX` are
**two different catalog objects**. The two are introspected by two different
helpers:

- `Introspect::Uniques` reads `UNIQUE` *constraints* via
  `rdb$relation_constraints` (`rdb$constraint_type = 'UNIQUE'`) and feeds
  `unique_constraints` — the authoritative source for `table_uniq_info`.
- `Introspect::Indexes` **deliberately excludes** constraint-backed indexes:
  after fetching from `rdb$indices`, it queries `rdb$relation_constraints` for
  `PRIMARY KEY`/`UNIQUE` index names and skips them
  (`Introspect/Indexes.pm:61-83`), so only explicit `CREATE INDEX` objects land
  in `indexes`.

Only the constraints feed `table_uniq_info`; the standalone unique indexes stay
in `indexes` (recorded in karr #4, the `table_uniq_info` gap).

## Rationale

The `rdb$` tables are the only source with the constraint-level, generator-level
and referential-action detail the diff and generation contracts need; the DBI
metadata layer would lose information and make the introspect/diff round-trip
unreliable. Pinning the model to one documented five-section shape lets Diff and
`DBIO::Generate` depend on a single contract rather than each re-deriving
Firebird's catalog. The `NAME_lc` reconciliation is the small but essential
glue that lets the helpers use ordinary lower-case `rdb$` key lookups despite
Firebird's upper-casing. Keeping constraint-uniques and index-uniques separate
matters because they are deployed and dropped differently: folding a
`CREATE UNIQUE INDEX` into `table_uniq_info` would make the diff propose
constraint operations for a plain index (and vice versa).

## Consequences

- All Firebird introspection reads `rdb$*` directly; do not reach for DBI
  metadata methods to "simplify" a helper — it would lose the detail the diff
  depends on.
- The per-helper subdir layout matches every other DBIO driver
  (`Introspect/{Tables,Columns,Indexes,Uniques,ForeignKeys}`); new
  introspected facts go in the matching helper.
- `THE INTROSPECTED MODEL` POD is the contract; any change to a section's shape
  must be made there and in Diff/Generate consumers together.
- `NAME_lc` is set for the whole `_build_model`; helpers may rely on lower-case
  `rdb$` keys and must not also case-fold result-column names themselves.
- The constraint/index split must be preserved: `Uniques` owns constraints,
  `Indexes` excludes constraint-backed indexes.