---
phase: 02-storage-and-ddl
plan: "04"
type: execute
wave: 1
depends_on: []
files_modified:
  - src/lib.rs
  - test/sql/phase2_ddl.test
autonomous: true
requirements:
  - DDL-05
gap_closure: true

must_haves:
  truths:
    - "define_semantic_view writes to the host DB's semantic_layer._definitions, not an ephemeral :memory: connection"
    - "After a DuckDB file-backed restart (LOAD + extension reload), registered semantic views are still returned by list_semantic_views()"
    - "In-memory DuckDB databases skip the catalog write path gracefully (no error, HashMap still updated)"
  artifacts:
    - path: "src/lib.rs"
      provides: "Host DB path resolved via PRAGMA database_list at entrypoint time; stored in DefineState and DropState"
      contains: "database_list"
    - path: "test/sql/phase2_ddl.test"
      provides: "SQLLogicTest restart section proving DDL-05 across a file-backed reload cycle"
      contains: "restart"
  key_links:
    - from: "src/lib.rs"
      to: "src/ddl/define.rs"
      via: "DefineState.db_path set to actual file path from PRAGMA database_list"
      pattern: "database_list"
    - from: "src/ddl/define.rs"
      to: "semantic_layer._definitions (host DB)"
      via: "Connection::open(state.db_path) opens the real file"
      pattern: "Connection::open"
    - from: "test/sql/phase2_ddl.test"
      to: "define_semantic_view + list_semantic_views"
      via: "restart statement closes/reopens file DB and re-runs LOAD"
      pattern: "restart"
---

<objective>
Fix DDL-05: make define_semantic_view and drop_semantic_view write catalog entries to the host DuckDB file, not an ephemeral :memory: connection, so registered views survive a restart.

Purpose: DDL-05 is the only unmet Phase 2 requirement. The mechanism (init_catalog reads semantic_layer._definitions on load, catalog_insert writes rows) is correct — the only broken link is that invoke always opens Connection::open(":memory:") because the entrypoint hardcoded ":memory:" as the db_path sentinel. Querying PRAGMA database_list at entrypoint time gives the real host DB file path.

Output: src/lib.rs with PRAGMA database_list path resolution; test/sql/phase2_ddl.test with a restart section proving views survive across a file-backed reload.
</objective>

<execution_context>
@/Users/paul/.claude/get-shit-done/workflows/execute-plan.md
@/Users/paul/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/02-storage-and-ddl/02-VERIFICATION.md
@.planning/phases/02-storage-and-ddl/02-02-SUMMARY.md
@.planning/phases/02-storage-and-ddl/02-03-SUMMARY.md
@src/lib.rs
@src/ddl/define.rs
@src/ddl/drop.rs
@test/sql/phase2_ddl.test
</context>

<tasks>

<task type="auto">
  <name>Task 1: Resolve host DB path via PRAGMA database_list in the extension entrypoint</name>
  <files>src/lib.rs</files>
  <action>
The problem: `src/lib.rs` hardcodes `let db_path: Arc<str> = Arc::from(":memory:");`. This causes `define_semantic_view` and `drop_semantic_view` to open `Connection::open(":memory:")` inside `invoke`, writing to an ephemeral DB separate from the host.

The fix: query `PRAGMA database_list` on the entrypoint connection (which IS the host connection) to recover the host DB file path, then pass the real path into DefineState and DropState.

In `src/lib.rs`, replace the hardcoded `":memory:"` line with:

```rust
// Resolve the host database file path by querying PRAGMA database_list.
// This returns rows: (seq INTEGER, name VARCHAR, file VARCHAR).
// The row where name = 'main' gives the host DB file path.
// For in-memory databases, file is an empty string — we map that to ":memory:"
// to preserve the existing sentinel behavior (writes to a second ":memory:"
// connection are ephemeral, but in-memory DBs cannot survive restart anyway).
let db_path: Arc<str> = {
    let path = con
        .query_row(
            "SELECT file FROM pragma_database_list() WHERE name = 'main'",
            [],
            |row| row.get::<_, String>(0),
        )
        .unwrap_or_default(); // returns "" for :memory: or on error
    if path.is_empty() {
        Arc::from(":memory:")
    } else {
        Arc::from(path.as_str())
    }
};
```

**Why `pragma_database_list()` as a table function, not `PRAGMA database_list`:** DuckDB's `query_row` method on `Connection` takes a SQL string. DuckDB supports `FROM pragma_database_list()` as a table function syntax, which works in a SELECT. Use `SELECT file FROM pragma_database_list() WHERE name = 'main'` — this is the portable DuckDB syntax for reading a pragma in a SELECT context.

**Alternative if `pragma_database_list()` syntax fails:** Try `PRAGMA database_list` directly as a statement. If `query_row` does not accept a PRAGMA statement, use `prepare` + `query_map`:

```rust
let db_path: Arc<str> = {
    let mut stmt = con.prepare("SELECT file FROM pragma_database_list() WHERE name = 'main'")?;
    let path = stmt
        .query_row([], |row| row.get::<_, String>(0))
        .unwrap_or_default();
    if path.is_empty() {
        Arc::from(":memory:")
    } else {
        Arc::from(path.as_str())
    }
};
```

**The rest of `src/lib.rs` is UNCHANGED.** The `DefineState { catalog, db_path }` and `DropState { catalog, db_path }` structs and registrations already use `db_path` — once the path is correct, catalog writes in `invoke` will target the real file.

**Important: Connection::open on a file-backed DB from within invoke.** When `invoke` calls `Connection::open(state.db_path.as_ref())` where `db_path` is a real file path, it opens a second connection to the same DuckDB file. DuckDB supports multiple connections to the same file (it uses WAL mode). This is safe for v0.1 — the extension tests will verify it end-to-end. If DuckDB raises a locking error (unlikely for a single-user file), the invoke will return an error to the user, which is acceptable behavior.

After the change:
1. Run `cargo build --no-default-features --features extension` — must exit 0.
2. Run `cargo clippy --no-default-features --features extension -- -D warnings` — must exit 0.
3. Run `cargo test` — must exit 0 (11 unit tests; these use bundled feature and Connection::open_in_memory(), unaffected by this change).

**Clippy consideration:** `unwrap_or_default()` on the `query_row` result is intentional — if the pragma query fails for any reason, fall back to `:memory:` rather than failing the extension load. Add `#[allow(clippy::...)]` only if clippy complains; otherwise leave as-is.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo build --no-default-features --features extension 2>&1 | tail -5 && cargo test 2>&1 | tail -10</automated>
    <manual>Confirm cargo build exits 0. Confirm the db_path line in src/lib.rs no longer reads Arc::from(":memory:") as a hardcoded literal — it now queries pragma_database_list(). Confirm cargo test shows 11 tests passing.</manual>
  </verify>
  <done>
    - `cargo build --no-default-features --features extension` exits 0
    - `cargo clippy --no-default-features --features extension -- -D warnings` exits 0
    - `cargo test` exits 0 with 11 tests passing
    - `src/lib.rs` contains `pragma_database_list` (the hardcoded `:memory:` sentinel is gone)
    - `db_path` is now resolved at entrypoint time from the host connection's PRAGMA, not hardcoded
  </done>
</task>

<task type="auto">
  <name>Task 2: Add restart section to SQLLogicTest proving DDL-05 file-backed persistence</name>
  <files>test/sql/phase2_ddl.test</files>
  <action>
DDL-05 requires that definitions survive a DuckDB restart. The SQLLogicTest `restart` statement closes the current database file and reopens it, then re-runs `LOAD`. This is the correct mechanism to prove cross-restart persistence.

Append a new section to `test/sql/phase2_ddl.test` after the existing section 9 (cleanup). The new section 10 defines a view, triggers a restart, and verifies the view is still present.

**How `restart` works in SQLLogicTest:**
- The SQLLogicTest runner creates a temporary file-backed DB for each test file (not `:memory:`).
- The `restart` statement is a special keyword that causes the runner to close the current DuckDB connection and reopen the test database file.
- After `restart`, the extension must be re-loaded with `require semantic_views` or a `LOAD` statement.
- Any rows persisted to `semantic_layer._definitions` before the `restart` must be present after.

Append to the end of `test/sql/phase2_ddl.test`:

```
# ============================================================
# 10. DDL-05: persistence across restart — define_semantic_view
#     writes to the host DB file; views survive extension reload
# ============================================================

# Define a view before restart
statement ok
SELECT define_semantic_view(
    'restart_test',
    '{"base_table":"events","dimensions":[{"name":"type","expr":"type"}],"metrics":[{"name":"n","expr":"count(*)"}]}'
);

# Verify it is present before restart
query TT rowsort
SELECT name, base_table FROM list_semantic_views();
----
restart_test	events

restart

# After restart the extension is reloaded; init_catalog reads semantic_layer._definitions
# The view defined before restart must be present in the catalog
query TT rowsort
SELECT name, base_table FROM list_semantic_views();
----
restart_test	events
```

**Note on `restart` and extension loading:** The SQLLogicTest runner (Python `duckdb_sqllogictest`) handles `restart` by closing and reopening the DB file. The `require semantic_views` at the top of the file applies to the initial load only. After `restart`, check whether the runner automatically re-runs `require` or whether an explicit `LOAD` is needed.

If the runner does NOT automatically reload after `restart`, add:
```
restart

require semantic_views
```

or equivalently:
```
restart

statement ok
LOAD 'target/debug/libsemantic_views';
```

**Determine the correct form at execution time** by reading the existing `test/sql/semantic_views.test` file (from Phase 1) to see if it uses `restart` and how it handles reloading. If no prior example exists, try with `require semantic_views` after `restart` first (that is the standard SQLLogicTest pattern).

After appending the section, run `just test-sql` to execute all tests:

```bash
just test-sql
```

This builds the debug extension and runs the SQLLogicTest runner against `test/sql/*.test`. Both existing tests and the new restart section must pass.

If the restart section fails:
1. Check the runner output — it will show which assertion failed
2. If `list_semantic_views()` returns 0 rows after restart, the PRAGMA fix in Task 1 may not be working — debug by adding a diagnostic query before the restart that shows `SELECT file FROM pragma_database_list() WHERE name = 'main'` and checking the value
3. If the runner errors on `restart` syntax, check the SQLLogicTest runner version installed by `make configure` and its restart support

Also run `cargo test` to confirm the 11 unit tests still pass after any changes.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just test-sql 2>&1 | tail -20</automated>
    <manual>Confirm just test-sql exits 0. Confirm the output shows both phase2_ddl.test and semantic_views.test as SUCCESS. Confirm the restart section in phase2_ddl.test passes — list_semantic_views() returns restart_test after the restart statement.</manual>
  </verify>
  <done>
    - `just test-sql` exits 0 — all SQLLogicTest files pass including the new restart section
    - `test/sql/phase2_ddl.test` contains a `restart` statement followed by a query verifying `restart_test` view is present
    - `cargo test` still exits 0 — 11 unit tests unaffected
    - DDL-05 is verified: a view defined before restart is recoverable after restart in a file-backed DuckDB
  </done>
</task>

</tasks>

<verification>
Full DDL-05 gap closure verification:
1. `cargo build --no-default-features --features extension` exits 0
2. `cargo clippy --no-default-features --features extension -- -D warnings` exits 0
3. `cargo test` exits 0 — 11 unit tests pass (model + catalog)
4. `just test-sql` exits 0 — both test files pass, including the new `restart` section
5. `src/lib.rs` no longer hardcodes `":memory:"` as db_path — it queries `pragma_database_list()` on the host connection
6. A view defined with `define_semantic_view` before a DuckDB file-backed restart is still returned by `list_semantic_views()` after restart
</verification>

<success_criteria>
- DDL-05 is satisfied: definitions survive a real file-backed DuckDB restart, proven by the SQLLogicTest restart section
- Existing behavior unchanged: in-memory databases fall back to ":memory:" sentinel (no-op catalog write, HashMap-only state) — no regression
- All 11 Rust unit tests pass
- Both SQLLogicTest files pass (including the new restart section)
- Zero clippy violations
</success_criteria>

<output>
After completion, create `.planning/phases/02-storage-and-ddl/02-04-SUMMARY.md`
</output>
