Metadata-Version: 2.5
Name: dbmapper
Version: 0.1.0
Summary: Read-only relational database metadata extraction
Project-URL: Homepage, https://github.com/greenspangle/dbmapper
Project-URL: Source, https://github.com/greenspangle/dbmapper
Project-URL: Issues, https://github.com/greenspangle/dbmapper/issues
Author-email: Peter McGinty <you@example.com>
License-Expression: MIT
License-File: LICENSE
Keywords: database,metadata,oracle,postgresql,schema,sql-server,sqlite
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Operating System :: Microsoft :: Windows
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: Python :: 3.15
Classifier: Topic :: Database
Classifier: Typing :: Typed
Requires-Python: >=3.14
Requires-Dist: click<9,>=8.5.0
Requires-Dist: pydantic<3,>=2.14.1
Requires-Dist: tqdm<5,>=4.70.1
Provides-Extra: dev
Requires-Dist: build<2,>=1.6.1; extra == 'dev'
Requires-Dist: jsonschema<5,>=4.26.0; extra == 'dev'
Requires-Dist: keyrings-alt<6,>=5.0.2; extra == 'dev'
Requires-Dist: mypy<2,>=1.20.2; extra == 'dev'
Requires-Dist: pytest-cov<7,>=6.3.0; extra == 'dev'
Requires-Dist: pytest<9,>=8.4.2; extra == 'dev'
Requires-Dist: ruff<1,>=0.17.1; extra == 'dev'
Requires-Dist: types-tqdm<5,>=4.70.0.20260906; extra == 'dev'
Provides-Extra: mssql
Requires-Dist: keyring<26,>=25.7.0; extra == 'mssql'
Requires-Dist: pyodbc<6,>=5.3.0; extra == 'mssql'
Requires-Dist: sqlalchemy<3,>=2.1.6; extra == 'mssql'
Provides-Extra: oracle
Requires-Dist: keyring<26,>=25.7.0; extra == 'oracle'
Requires-Dist: oracledb<4,>=3.4.2; extra == 'oracle'
Requires-Dist: sqlalchemy<3,>=2.1.6; extra == 'oracle'
Provides-Extra: postgresql
Requires-Dist: keyring<26,>=25.7.0; extra == 'postgresql'
Requires-Dist: psycopg[binary]<4,>=3.3.6; extra == 'postgresql'
Requires-Dist: sqlalchemy<3,>=2.1.6; extra == 'postgresql'
Provides-Extra: sqlite
Requires-Dist: sqlalchemy<3,>=2.1.6; extra == 'sqlite'
Description-Content-Type: text/markdown

# README dbmapper

Read-only metadata extraction for Windows. `db-extract` reads the catalogues of
SQL Server, PostgreSQL, Oracle and SQLite databases and writes one versioned JSON
document per target in the Metadata Contract format: schemas, tables, columns
and types, keys, constraints, indexes, views, routines, triggers, sequences,
synonyms, user-defined types, partitioning, policies, dependencies, descriptions
and extended properties, and optionally privileges. Implementation and
verification status are recorded in `VALIDATION.md`. Metadata Contract 1.0 is the initial, unpublished
contract. Linux and macOS are future phases.

## Install

Python 3.14 or later is required. Install the extras for the databases you use:

```powershell
python -m pip install "dbmapper[sqlite,mssql,postgresql,oracle]"
```

Core installs Pydantic, Click and tqdm. Core, `dbmapper.contract`,
`dbmapper.extract` and `db-extract --help` work without database extras.
SQLAlchemy and keyring are loaded only for selected targets. SQLite has no
keyring dependency. The first release includes extraction and the contract only.

Native prerequisites:

| Extra | Native prerequisite | Connection method |
| --- | --- | --- |
| `sqlite` | Python's SQLite library, version 3.37+ | Existing database file |
| `mssql` | Microsoft ODBC Driver 17 or 18 for SQL Server, same architecture as Python | Host/port, host/instance, or configured ODBC DSN |
| `postgresql` | None beyond the binary psycopg wheel | Host and database |
| `oracle` | Thin: none; thick: compatible Oracle Client/Instant Client libraries discoverable through PATH or `oracle_client_lib_dir` | Host with service name or SID, or TNS alias |

When a matching binary wheel is unavailable, native driver builds also require
Visual C++ Build Tools and a full Python installation with development headers.
That build path was not available for the final Python 3.15 compatibility run.

The configured ODBC driver must be installed; options such as `Encrypt=strict`
also require an ODBC driver/server combination supporting that option.
Oracle Client mode and network configuration persist for the process lifetime.
Sequential API calls may reuse them but cannot switch to an incompatible mode.
The API does not support concurrent calls in one process.

## Configure

Copy `dbmapper.example.toml` to `dbmapper.toml` and replace the example targets.
Every target requires a dialect. Names are portable ASCII identifiers (1–64
characters, beginning with a letter or digit, followed by letters, digits,
underscores or hyphens), unique without regard to ASCII case, excluding Windows
device names. Output is `<name>.json`. No URL or password field is accepted.

Connection combinations:

* SQL Server: `host`, `database`, `odbc_driver`, optional `port` or `instance`;
  alternatively `dsn`, with optional `database`. Use `username` for password
  authentication or `trusted_connection=true` without a username for Windows
  authentication. DSN cannot be combined with host, port, instance or driver.
* PostgreSQL: `host`, `database`, `username`, optional `port`.
* Oracle: `username` and either `host` with exactly one of `service_name` or
  `sid` (optional port), or `tns_alias` alone. Top-level `oracle_config_dir`
  selects the directory containing `tnsnames.ora`. Easy Connect strings and full
  descriptors are not configuration inputs.
* SQLite: `path` to an existing regular database file. It accepts no credentials,
  connection timeout or driver options. Relative paths resolve against the TOML
  file; directly constructed `TargetConfig` paths resolve against cwd.

`schemas` is an optional exact, case-sensitive allowlist; `exclude_schemas`
subtracts from it or from all visible schemas. Empty `schemas=[]` selects none.
System schemas/objects are excluded. An unavailable requested schema is recorded
as an error. `include_row_counts=false` skips statistics retrieval entirely.
Counts are estimates from catalogue statistics; extraction never runs ANALYZE,
COUNT(*) or application-table scans. SQLite statistics can be absent.

`include_privileges` (default `false`) adds the `privileges` facet: object,
column and schema grants, including SQL Server DENY. On PostgreSQL an object
with no access list reports its effective default (`acldefault`), including the
owner's privileges. It is opt-in because it exposes role and user names, and
because complete privileges can need broader catalogue access (see Privileges
below).
`include_connected_user` (default `true`) records the session user as
`extraction.connected_user`; `false` records `null` and skips the query.
Neither option is accepted on SQLite targets.

`connect_timeout_seconds` defaults to 30, `query_timeout_seconds` to 120;
each is a positive integer no greater than 3600. Credentials have a separate
30-second lookup deadline. Booleans are not integers in configuration.

The optional `[targets.options]` table accepts only:

| Dialect | Options and values |
| --- | --- |
| SQL Server | `Encrypt`: yes/no/mandatory/optional/strict; `TrustServerCertificate`: yes/no; `HostNameInCertificate`: nonempty string; `ApplicationIntent`: ReadOnly/ReadWrite; `MultiSubnetFailover`: yes/no |
| PostgreSQL | `sslmode`: disable/allow/prefer/require/verify-ca/verify-full; `sslrootcert`: certificate path or system; `application_name`: nonempty string; `target_session_attrs`: any/read-write/read-only/primary/standby/prefer-standby |
| Oracle | `protocol`: tcp/tcps; `ssl_server_dn_match`: boolean; `ssl_server_cert_dn`: nonempty string |

SQL Server option names are case-insensitive, without duplicate aliases;
PostgreSQL and Oracle names are exact. Enumerated values are case-insensitive.
Authentication, secret-bearing, redirecting and timeout options are excluded.
Oracle wallets are excluded, including certificate wallets; `tcps` requires a
driver/client combination capable of using system certificate trust. TLS option
coverage remains a release gate in `VALIDATION.md`.

## Credentials and scheduling

Passwords come only from keyring. Store them separately, before running:

```powershell
keyring set "localhost:5432" "metadata_reader"
db-extract --config dbmapper.toml --target local_postgresql
```

The service key is lowercased `host:port`, or lowercased `host` when no port is
configured; no default port is substituted. SQL Server instances use
`host\instance`, DSNs use the DSN exactly, and Oracle aliases use the lowercase
alias. Username is the configured username. Database/service/SID do not affect
the lookup. `keyring_service` and `keyring_username` override either component.
SQL Server Windows authentication and SQLite never look up a password.

Production backends are Windows Credential Locker, macOS Keychain and Linux
Secret Service; phase one runs on Windows only. Every member of a chained
backend must be supported. A locked, denied, missing or unsupported backend
fails pre-flight. No prompt, environment-variable password or file fallback is
used. Schedule the task as the Windows identity that owns the credentials (or
has SQL Server access for integrated authentication).

Credential lookups use daemon threads with a deadline. A lookup that times out
is abandoned and not joined. In an API host its thread may remain until the
backend returns, but it cannot keep the CLI process alive. Each invocation has
its own credential cache. Test-only `DBMAPPER_TEST_KEYRING=1` permits
`keyrings.alt` and logs that the store is not an operating-system store; this
setting is unsupported in production. Integration tests use temporary stores.

For the later Linux phase, a headless session needs an already unlocked Secret
Service provider. For macOS, Keychain access must be granted interactively once
before scheduling. These platform behaviours have not been verified here.

## CLI

```powershell
db-extract --help
db-extract --config dbmapper.toml --output-dir ./IO/json
db-extract --target local_sqlserver --database another_database --force
db-extract --config dbmapper.toml --compact
```

`--target` selects one exact configured name. `--database` requires `--target`
and supports SQL Server/PostgreSQL only. `--force` enables atomic replacement.
`--compact` writes the compact layout instead of the indented one; it changes
only whitespace, never content or order.
`--log-level` accepts DEBUG, INFO, WARNING, ERROR and CRITICAL; `--verbose` means
DEBUG and conflicts with an explicitly selected non-DEBUG level.

The CLI appends to `dbmapper.log` in cwd. Progress and errors go to stderr,
including plain completion lines when redirected. Stdout contains one final
summary. Raw driver exceptions, credentials, parameter values, retrieved source
and descriptions are not copied into diagnostics. DEBUG includes catalogue SQL
templates and timings. Logging initialization failure is a pre-flight error;
later logging failures mark the run unsuccessful without discarding output.

| Exit code | Meaning |
| --- | --- |
| 0 | All targets committed without metadata errors |
| 1 | Partial/failed target, logging failure or unexpected internal fault |
| 2 | Configuration or pre-flight failure; no connection opened or final output changed |
| 130 | Operator cancellation after cleanup; earlier commits remain |

All content-only configuration checks apply to every target. Environmental
checks apply only to selected targets. Independent pre-flight problems are
aggregated before any database connects.

## Python API and contract

```python
from dbmapper import extract
from dbmapper.contract import load_metadata

result = extract.run(
    "dbmapper.toml", target="local_sqlite", output_dir="IO/json", compact=False
)
metadata = extract.extract_target(extract.TargetConfig(
    name="local", dialect="sqlite", path="data/example.db"
))
compatible = load_metadata(metadata.model_dump_json())
```

`run` returns an immutable `RunResult` with ordered `TargetResult` records:
name, complete/partial/failed status, absolute output path or None, and safe
diagnostics. Its `exit_code` summarizes success. Expected operational failures
are results; `extract_target` raises `TargetError` for fatal target failures.
Both raise `PreflightError` for local problems. Exceptions expose respectively
`target_name`/`errors` and `problems`. Cancellation and unexpected programming
faults propagate after cleanup. Library calls add no logging handlers and
`extract_target` writes no files or progress output.

`configure_oracle(thick_mode=False, client_lib_dir=None, config_dir=None)` sets
process-wide Oracle configuration before Oracle use. Relative API paths resolve
against cwd. Configure it once; incompatible subsequent changes fail pre-flight.

Models live in `dbmapper.contract`. The generated exact-version schema is shipped
as `schemas/dbmapper-contract-1.0.schema.json` in that package and can be loaded
with `importlib.resources.files("dbmapper.contract")`. Producer models forbid
unknown fields and apply strict typing and cross-field validation.
`load_metadata` accepts JSON text/UTF-8 bytes/mappings, rejects duplicate JSON
members, validates known fields and ignores fields introduced by compatible 1.x
minor versions. It preserves the supplied minor version. Unsupported majors
raise `UnsupportedContractVersionError`; invalid content raises
`ContractValidationError`. Integers, including sequence limits, are exact and
may exceed 2^53; consumers must use arbitrary-precision parsing.

The JSON Schema is the contract's normative form. The models implement it, plus
the cross-field invariants it cannot express, which its root `$comment` lists.
A conformance corpus of valid and invalid documents, each with the expected
outcome for the schema, the producer models and the loader, ships as the
`conformance/` directory of `dbmapper.contract`.

## Facets, unsupported fields and document size

`extraction.facets` lists the optional groups of fields that were extracted:

| Facet | Fields | Listed when |
| --- | --- | --- |
| `descriptions` | every `description` | always, for the built-in providers |
| `extended_properties` | every `properties` map | always, for the built-in providers |
| `dependencies` | every `references` list | always, for the built-in providers |
| `row_counts` | `row_count`, `row_count_kind`, `row_count_as_of` | `include_row_counts = true` (the default) |
| `privileges` | every `privileges` list | `include_privileges = true` |

When a facet is absent, its query is not issued and its fields hold their
defaults (`None`, `[]`, `{}`). Read them as "not extracted", never as facts.

`extraction.unsupported` lists the field paths the source DBMS cannot populate,
for example `schemas.synonyms` on PostgreSQL. It depends on the dialect and the
server version, and follows the capability matrix in
`requirements/01_extract/extraction_coverage.md` §15. Values at or below an
unsupported path also hold their defaults. An empty collection is therefore a
fact only when its facet is listed, its path is supported and no error covers
it.

Document size grows with the number of columns. Measured with SQLite; the
server dialects carry similar per-column facts, and routine and view source
adds its own length:

| Database | Indented | Compact |
| --- | --- | --- |
| 200 tables, 1,399 columns, with keys, checks, indexes and 20 views | 2.1 MB (about 10.5 KB per table) | 1.0 MB |
| 1,000 tables of 99 integer columns | 82 MB (about 830 bytes per column) | 39 MB |

The compact layout is about half the size of the indented one. Extraction holds
the whole document in memory before writing it.

## Partial output, safety and timeouts

Recoverable object failures retain accessible metadata and attach structured
errors. Missing required facts omit the affected item instead of inventing
defaults. Failed categories are empty with enclosing errors. An unusable session
or invalid root fails its target without output. Encrypted/wrapped source is
null without error; detectable privilege gaps are errors. Catalogue visibility
can hide objects silently, so an error-free document is not proof of sufficient
privileges.

Each target is validated/serialized fully, written to a unique same-directory
temporary file, flushed/closed, then committed atomically. Without force, Windows
rename atomically refuses an existing destination. Force uses atomic replacement.
Unsupported filesystem semantics fail the target. Cancellation removes temporary
files; forced process termination can leave temporary files but no partially
written final JSON. Earlier target commits remain, and failed targets do not
return a path to an old file. Power-loss durability is not promised.

SQLite is opened with `mode=ro`, `uri=true` and `PRAGMA query_only=ON`. No
attachments, extension loading or immutable mode are used. WAL-mode opening may
fail if the WAL/shared-memory state cannot be read or initialized with the
available permissions; that is a connection failure. The database and any
required `-wal`/`-shm` files must be accessible. Filesystem access controls govern
SQLite; SQLCipher and other encryption extensions are excluded.

SQL Server requests the ODBC read-only connection attribute; PostgreSQL sets
`default_transaction_read_only=on`; Oracle uses read-only transactions and
reestablishes that transaction mode after a recoverable rollback.

| Driver | Connection mechanism | Query mechanism and accepted gaps |
| --- | --- | --- |
| pyodbc | ODBC login timeout | DBAPI connection query timeout; post-execution fetching and some native cleanup/name-resolution phases may remain unbounded |
| psycopg | libpq connect_timeout | Server statement_timeout; DNS, native connection/cleanup work and multiple underlying address attempts may exceed a single timeout |
| oracledb | tcp_connect_timeout, retries disabled | call_timeout per round trip including fetch; this is not a whole-query or authentication-handshake deadline; name resolution, multiple addresses and native cleanup can add time |
| sqlite3 | No connect timeout | Progress handler plus lock-wait timeout; filesystem I/O and work between progress callbacks may block beyond the deadline |

There is no whole-run deadline or worker process/thread for database calls.
Cancellation takes effect when the current call returns or its driver deadline
ends it; cleanup has the same native-driver gaps. There are no automatic
application retries. The SQLite broken-view/virtual-table fallback is explicitly
allowed to retry individual objects after a failed bulk category.

Verbatim catalogue source and comments may contain sensitive database-authored
text. They are preserved in JSON; control access to the output directory.

## Privileges and verification

The project requires least-privilege evidence on minimum server versions before
release. That matrix is not yet complete. Current controlled fixtures establish:

* SQL Server: Windows administrator access in an isolated test database. Start
  with CONNECT and database/schema/object `VIEW DEFINITION` for a reader; the
  minimum restricted-account grants are not yet verified. With
  `include_privileges`, `sys.database_permissions` shows only the permissions
  the connecting principal can see.
* PostgreSQL: a login with CONNECT (public default) and USAGE on the fixture
  schema can read the tested metadata, including privileges and policies,
  without application-table SELECT grants.
  Exact fixture grants are in `tests/test_local_postgresql.py`.
* Oracle: the isolated fixture owner has CREATE SESSION and owns its objects.
  Complete privileges need SELECT on `SYS.DBA_TAB_PRIVS`, `SYS.DBA_COL_PRIVS`
  and, on 23ai, `SYS.DBA_SCHEMA_PRIVS`. Without them extraction uses the `ALL_`
  views, which show only grants involving the connecting user or its roles,
  records a `PRIVILEGE_GAP` on every schema and exits with code 1.
  Cross-schema least-privilege grants/source visibility are not established;
  `SELECT_CATALOG_ROLE`/`SELECT ANY DICTIONARY` are candidates, not verified
  minimum grants.
* SQLite: read access to the existing file and relevant WAL sidecars.

See `VALIDATION.md` for exact versions, acceptance coverage and remaining gates.

The recorded Windows/Python 3.14.7 server tests used SQL Server 17.0.1145.4
(pyodbc 5.3.0, ODBC Driver 18 DLL version 2018.0186.0001.01), PostgreSQL 18.3
(psycopg 3.3.6), and Oracle AI Database 26ai Free 23.26.1.0.0 (oracledb 3.4.2,
thin and thick mode with Oracle Client 23.26.1.0.0). SQLite 3.37.0 and 3.53.1
passed the default suite on Python 3.14.7; final Python 3.15.0 passed with
SQLite 3.53.4. SQLAlchemy was 2.1.6 and Pydantic 2.14.1. These results establish
the tested fixtures; the complete supported-version/privilege matrix remains
outstanding, including SQL Server/Oracle native drivers on Python 3.15.

## Development

```powershell
uv sync --all-extras
uv run ruff check src tests tools
uv run ruff format --check src tests tools
uv run mypy src
uv run pytest
uv run python tools/generate_schema.py --check
uv run python tools/build_conformance_corpus.py --check
uv run python tools/build_requirements_index.py --check
uv build
```

The default suite includes real SQLite fixtures and a 100,000-object correctness
and query-count check. Canned mapping tests complement controlled server tests;
they do not replace live SQL evidence. Local server fixtures are opt-in because
they create isolated databases/accounts and require installed Windows services:

```powershell
$env:DBMAPPER_LOCAL_MSSQL="1"
$env:DBMAPPER_LOCAL_POSTGRESQL="1"
$env:DBMAPPER_LOCAL_ORACLE="1"
uv run pytest -m local_db
```

SQL Server uses localhost Windows authentication; Oracle uses local OS SYSDBA
only to create/drop a temporary user in FREEPDB1, while extraction itself uses
the generated test password through a temporary keyring. PostgreSQL starts and
stops a disposable localhost cluster using the installed server binaries
(`DBMAPPER_POSTGRES_BIN` overrides their directory). Set
`DBMAPPER_ORACLE_THICK=1` in a separate test process to test Oracle thick mode.
Fixtures do not inspect application databases or the operator's stored passwords.
Containerized minimum-version CI and the full release matrix remain outstanding.

Bulk access uses fixed catalogue facets. SQL Server and SQLite issue no
per-object catalogue statements during successful extraction. With SQLAlchemy
2.1.6, PostgreSQL constraints/indexes use batches of 3,000 and Oracle reflection
uses batches of 500. Each such facet has one catalogue execution per batch plus
fixed enumeration/setup queries. The SQLite fallback above is the only
per-object-query exception. Test and record batch boundaries when changing
SQLAlchemy versions; the complete live boundary matrix is still a release gate.
