# Requirements Archive: v0.9.0 Read-Only Database LOAD Support + Quoted Identifier Bugfix

**Archived:** 2026-05-17
**Status:** SHIPPED

For current requirements, see `.planning/REQUIREMENTS.md`.

---

# Requirements: DuckDB Semantic Views — v0.9.0

**Defined:** 2026-05-15
**Core Value:** A DuckDB user can define a semantic view once and query it with any combination of dimensions and metrics, without writing GROUP BY or JOIN logic by hand — the extension handles expansion, DuckDB handles execution.

## Milestone Goal

Allow `LOAD semantic_views` to succeed on a read-only DuckDB database so that previously-defined semantic views can be queried. DDL (CREATE / DROP / ALTER) on a read-only database surfaces DuckDB's standard "cannot write to read-only database" error rather than failing at LOAD with a confusing schema-create error.

## v1 Requirements (v0.9.0)

### Read-Only LOAD

- [x] **RO-01**: `LOAD semantic_views` succeeds on a read-only DuckDB database (both a fresh read-only file that was never opened writable, and a previously-bootstrapped file reopened read-only). No catalog mutation is attempted at LOAD time on read-only.
- [x] **RO-02**: `list_semantic_views()`, `describe_semantic_view('name')`, and `FROM semantic_view('name', dimensions := [...], metrics := [...])` return correct results on a read-only database that was previously bootstrapped with one or more semantic views.
- [x] **RO-03**: `list_semantic_views()` returns zero rows (not a catalog error) on a read-only database that was never bootstrapped — the missing `semantic_layer._definitions` table is treated as "no views defined."
- [x] **RO-04**: `describe_semantic_view('name')` and `FROM semantic_view('name', ...)` on a read-only database with no `_definitions` table surface a clean "semantic view 'name' does not exist" error (not a raw catalog error about a missing table).
- [x] **RO-05**: `CREATE SEMANTIC VIEW`, `DROP SEMANTIC VIEW`, and `ALTER SEMANTIC VIEW` on a read-only database fail with DuckDB's standard "cannot write to read-only database" error (or the closest equivalent surfaced by the planner). The extension does not wrap or rephrase this error.

### Documentation & Examples

- [x] **DOC-01**: `CHANGELOG.md` has a `[0.9.0]` section under the standard Keep-a-Changelog headings (`Added` for the read-only support; `Known limitations` if any) describing the user-visible change. The bottom-of-file compare link is updated.
- [x] **DOC-02**: `docs/explanation/transactional-ddl-and-limitations.rst` gains a "Read-only databases" section explaining: LOAD behavior, query behavior on bootstrapped vs fresh read-only DBs, and the bootstrap-then-reopen workflow.
- [x] **DOC-03**: `docs/reference/create-semantic-view.rst`, `docs/reference/drop-semantic-view.rst`, and `docs/reference/alter-semantic-view.rst` each carry a one-line note that the operation requires a writable database.
- [x] **DOC-04**: `README.md` mentions in its LOAD section that read-only databases are supported for queries.
- [x] **DOC-05**: `examples/readonly_load.py` demonstrates the end-to-end flow: open writable → define a view → close → reopen read-only → query the view → attempt a DDL → catch the read-only error. Matches the style of `examples/transactional_ddl.py`.

### Quality Gate

- [x] **TEST-01**: sqllogictest fixture (`test/sql/readonly_load.test`) covers: (a) load on a fresh read-only file → `list_semantic_views()` returns empty; (b) load on a bootstrap-then-reopen read-only file → reads return the pre-existing view; (c) `CREATE` / `DROP` / `ALTER` on a read-only file fails with a read-only error.
- [x] **TEST-02**: Python integration test (`test/integration/test_readonly_load.py`) covers the same three scenarios end-to-end using `duckdb.connect(path, read_only=True)`.
- [x] **TEST-03**: `just test-all` and `just ci` pass with the new fixtures.

### Release

- [x] **REL-01**: `Cargo.toml` and `description.yml` bumped to `0.9.0`. Tag `v0.9.0` cut after squash-merge to `main`.

### Quoted Identifier Handling

- [x] **QID-01**: `CREATE [OR REPLACE | IF NOT EXISTS] SEMANTIC VIEW` accepts a fully-quoted fully-qualified name (e.g. `"memory"."main"."orders_sv"`). The catalog row is stored under the bare unquoted last part.
- [x] **QID-02**: The same DDL forms accept partial-quoting variants (`db.schema."view"`, `"db".schema.view`, `"view"`); all normalise to the bare last-part key.
- [x] **QID-03**: `DROP`, `ALTER`, `DESCRIBE`, and `SHOW COLUMNS IN SEMANTIC VIEW` accept the same quoting forms; the runtime `semantic_view('"name"', ...)` positional argument is also normalised. `ALTER ... RENAME TO` normalises BOTH the source and the new-name target.
- [x] **QID-04**: The expansion pipeline emits exactly one pair of quotes per identifier part for `TABLES (...)` source-table references, regardless of whether the source-table reference is quoted, partially quoted, or bare. No `"""triple"""` strings appear in expanded SQL.
- [x] **QID-05**: `GET_DDL('SEMANTIC_VIEW', 'name')` round-trip is stable: a copy-paste of the output is an executable `CREATE OR REPLACE SEMANTIC VIEW <bare_name> AS ...` that produces an equivalent view.
- [x] **QID-06**: Error messages for "view does not exist" and "view already exists" reference the unquoted bare name, never the quoted source string.
- [x] **QID-07**: `src/ident.rs` ships with unit-test coverage of bare / quoted / mixed / `""`-escaped / dot-inside-quotes / whitespace-inside-quotes / error inputs, plus a round-trip proptest (`parse(emit(parse(x))) == parse(x)`). `test/sql/phase64_quoted_idents.test` integration fixture covers QID-01..06 end-to-end.

## v2 / Future Requirements

### External catalog DB (Option 2, deferred)

- **CAT-01**: Allow `_definitions` to live in a separate attached database (DuckLake-style), so semantic views can be defined over a host DB the user has no write access to. Deferred — requires a UX redesign (catalog discovery, ATTACH ordering, cross-DB name resolution) and is not blocking the read-only LOAD scenario.

## Out of Scope (v0.9.0)

| Feature | Reason |
|---------|--------|
| Custom "semantic views require writable database" wrapper around DuckDB's DDL error | DuckDB's native message is clear; wrapping would require threading the read-only flag through the parser-override path end-to-end for marginal UX gain. |
| External catalog DB (Option 2) | Larger scope; deferred to v2 as **CAT-01**. v0.9.0 keeps `_definitions` in the host DB. |
| Read-only support for in-memory databases | `:memory:` databases are always writable; the case doesn't arise. |
| Auto-recovery / silent re-bootstrap when `_definitions` is missing on a writable DB | A missing table on a writable DB is a real anomaly — `init_catalog` still issues `CREATE TABLE IF NOT EXISTS` in that path. The graceful "missing → empty" fallback applies only when LOAD detected read-only. |

## Traceability

Updated during roadmap creation.

| Requirement | Phase | Status |
|-------------|-------|--------|
| RO-01 | Phase 63 | Complete |
| RO-02 | Phase 63 | Complete |
| RO-03 | Phase 63 | Complete |
| RO-04 | Phase 63 | Complete |
| RO-05 | Phase 63 | Complete |
| DOC-01 | Phase 63 | Complete |
| DOC-02 | Phase 63 | Complete |
| DOC-03 | Phase 63 | Complete |
| DOC-04 | Phase 63 | Complete |
| DOC-05 | Phase 63 | Complete |
| TEST-01 | Phase 63 | Complete |
| TEST-02 | Phase 63 | Complete |
| TEST-03 | Phase 63 | Complete |
| REL-01 | Phase 63 | Complete |
| QID-01 | Phase 64 | Complete |
| QID-02 | Phase 64 | Complete |
| QID-03 | Phase 64 | Complete |
| QID-04 | Phase 64 | Complete |
| QID-05 | Phase 64 | Complete |
| QID-06 | Phase 64 | Complete |
| QID-07 | Phase 64 | Complete |

**Coverage:**
- v1 requirements: 21 total
- Mapped to phases: 21
- Unmapped: 0 ✓

---
*Requirements defined: 2026-05-15*
*Last updated: 2026-05-15 after initial definition*
