---
phase: 02-storage-and-ddl
plan: "03"
type: execute
wave: 3
depends_on: ["02-02"]
files_modified:
  - test/phase2_ddl.sql
  - Justfile
autonomous: true
requirements:
  - DDL-01
  - DDL-02
  - DDL-03
  - DDL-04
  - DDL-05

must_haves:
  truths:
    - "SELECT define_semantic_view('orders', '{...json...}') returns a confirmation VARCHAR and the row appears in semantic_layer._definitions"
    - "SELECT define_semantic_view with a duplicate name returns an error containing 'already exists'"
    - "SELECT drop_semantic_view('orders') removes the view; subsequent describe_semantic_view('orders') returns an error"
    - "FROM list_semantic_views() returns one row per registered view with (name, base_table) columns"
    - "FROM describe_semantic_view('orders') returns one row with all six typed columns (name, base_table, dimensions, metrics, filters, joins)"
    - "Definitions survive a DuckDB restart: after closing and reopening a file-backed DB, registered views are still present"
  artifacts:
    - path: "test/phase2_ddl.sql"
      provides: "SQL logic test exercising the full DDL round-trip including persistence"
      contains: "define_semantic_view"
    - path: "Justfile"
      provides: "test-sql recipe that runs the SQL logic test via DuckDB CLI"
      contains: "test-sql"
  key_links:
    - from: "test/phase2_ddl.sql"
      to: "semantic_layer._definitions"
      via: "SQL SELECT after define_semantic_view"
      pattern: "semantic_layer\\._definitions"
    - from: "test/phase2_ddl.sql"
      to: "define_semantic_view"
      via: "DuckDB CLI LOAD + SELECT"
      pattern: "define_semantic_view"
---

<objective>
Verify the full DDL round-trip with SQL logic tests run via the DuckDB CLI.

Purpose: `cargo test` verifies Rust unit logic. This plan verifies the actual DuckDB extension loading and function behavior using the same LOAD smoke test approach established in Phase 1. It also explicitly tests catalog persistence across restarts (DDL-05) — the only requirement not covered by Rust unit tests alone.

Output: `test/phase2_ddl.sql` (SQL logic test file), `Justfile` updated with `test-sql` recipe.
</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-CONTEXT.md
@.planning/phases/02-storage-and-ddl/02-02-SUMMARY.md
@.planning/phases/01-scaffold/01-02-SUMMARY.md
@Justfile
</context>

<tasks>

<task type="auto">
  <name>Task 1: Write SQL logic test for DDL round-trip including persistence</name>
  <files>test/phase2_ddl.sql</files>
  <action>
Create `test/` directory if it does not exist. Create `test/phase2_ddl.sql`.

The SQL logic test is run via the DuckDB CLI (same approach as the Phase 1 LOAD smoke test). It:
1. Loads the extension
2. Exercises `define_semantic_view`, `list_semantic_views`, `describe_semantic_view`, `drop_semantic_view`
3. Verifies error cases
4. Verifies catalog persistence (by writing to a temp file-backed DB, closing, and re-opening)

The DuckDB CLI `.sqllogictest` format uses `query` and `statement` blocks with expected output. Alternatively, use a plain SQL file with `SELECT` assertions that raise errors on failure (simpler for DuckDB CLI `--bail` mode).

Use the plain SQL assertion approach with `SELECT assert(...)` or equivalent DuckDB boolean expressions that produce an error if false:

```sql
-- test/phase2_ddl.sql
-- Phase 2 DDL round-trip test
-- Run with: duckdb -c ".read test/phase2_ddl.sql" (using the extension auto-loaded)
-- Or via: just test-sql

LOAD 'target/debug/libsemantic_views';

-- ============================================================
-- 1. Define a semantic view
-- ============================================================
SELECT define_semantic_view(
    'orders',
    '{"base_table":"orders","dimensions":[{"name":"region","expr":"region"}],"metrics":[{"name":"revenue","expr":"sum(amount)"}]}'
);

-- Row should exist in catalog
SELECT assert(count(*) = 1, 'Expected 1 row in catalog after define')
FROM semantic_layer._definitions
WHERE name = 'orders';

-- ============================================================
-- 2. Duplicate define is an error
-- ============================================================
-- DuckDB SQL logic: wrap in try/catch equivalent.
-- Use a CTE that calls the function and check for error in the result.
-- Since DuckDB does not have try/catch in SQL, use a .sqllogictest-style
-- "statement error" expectation, OR verify via the catalog count staying at 1.
-- For plain SQL mode, skip the error-path test here — it is covered by cargo tests.
-- Comment: duplicate define tested in Rust unit tests (catalog::tests::duplicate_insert_is_error)

-- ============================================================
-- 3. list_semantic_views returns the row
-- ============================================================
SELECT assert(count(*) = 1, 'Expected 1 row from list_semantic_views')
FROM list_semantic_views();

SELECT assert(
    count(*) = 1,
    'Expected orders row in list with correct base_table'
)
FROM list_semantic_views()
WHERE name = 'orders' AND base_table = 'orders';

-- ============================================================
-- 4. describe_semantic_view returns all fields
-- ============================================================
SELECT assert(name = 'orders',       'name mismatch')       FROM describe_semantic_view('orders');
SELECT assert(base_table = 'orders', 'base_table mismatch') FROM describe_semantic_view('orders');
-- dimensions should be non-empty JSON array string
SELECT assert(length(dimensions) > 2, 'dimensions should be non-empty') FROM describe_semantic_view('orders');

-- ============================================================
-- 5. Define a second view
-- ============================================================
SELECT define_semantic_view(
    'customers',
    '{"base_table":"customers","dimensions":[{"name":"country","expr":"country"}],"metrics":[{"name":"count","expr":"count(*)"}],"filters":["active = true"]}'
);

SELECT assert(count(*) = 2, 'Expected 2 rows after second define')
FROM list_semantic_views();

-- ============================================================
-- 6. drop_semantic_view removes the view
-- ============================================================
SELECT drop_semantic_view('orders');

SELECT assert(count(*) = 0, 'Expected 0 rows for orders after drop')
FROM semantic_layer._definitions
WHERE name = 'orders';

SELECT assert(count(*) = 1, 'Expected 1 row remaining after drop')
FROM list_semantic_views();

-- ============================================================
-- 7. Persistence test — write to file-backed DB, reload extension
-- ============================================================
-- Create a temp file DB, register a view, verify it survives "restart"
-- (simulated by using ATTACH/DETACH or by re-running the init path).
-- For DuckDB CLI, simulate restart by using a named file:

ATTACH '/tmp/semantic_views_test.duckdb' AS testdb;

-- Re-load extension in testdb context (the extension is already loaded globally)
-- Register a view in the testdb connection
-- Note: the extension functions operate on the current database file.
-- The persistence test is partially covered by catalog::tests::init_catalog_loads_existing_rows
-- (Rust unit test using in-memory sequential init calls).
-- Full file-backed persistence is verified here with ATTACH:

USE testdb;
-- define in testdb
SELECT define_semantic_view(
    'persist_test',
    '{"base_table":"t","dimensions":[{"name":"x","expr":"x"}],"metrics":[{"name":"n","expr":"count(*)"}]}'
);

SELECT assert(count(*) = 1, 'Persistence: expected row in testdb catalog')
FROM semantic_layer._definitions
WHERE name = 'persist_test';

USE memory;
DETACH testdb;

-- Re-attach to verify persistence
ATTACH '/tmp/semantic_views_test.duckdb' AS testdb2;
USE testdb2;

SELECT assert(count(*) = 1, 'Persistence: row survives DETACH/ATTACH cycle')
FROM semantic_layer._definitions
WHERE name = 'persist_test';

USE memory;
DETACH testdb2;

-- Cleanup
SELECT delete_file('/tmp/semantic_views_test.duckdb') WHERE file_exists('/tmp/semantic_views_test.duckdb');
```

**NOTE:** The exact DuckDB SQL API for `assert`, `delete_file`, `file_exists`, `USE`, and ATTACH behavior with extensions needs to be verified at execution time. The executor should:
1. Check DuckDB 1.4.4 SQL reference for assertion patterns (DuckDB has `SELECT 1 FROM (SELECT ...) WHERE condition` pattern that raises error via `LIMIT 1` + error on empty, or use `.sqllogictest` format)
2. If `assert()` is not a DuckDB built-in, rewrite assertions as: `SELECT CASE WHEN condition THEN 'ok' ELSE error('assertion failed: ...') END`
3. Adjust the persistence section based on how DuckDB CLI handles ATTACH with a loaded extension
4. Document all API resolutions in SUMMARY.md

The test file should be runnable with `duckdb < test/phase2_ddl.sql` and exit 0 on success, non-0 on failure.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo build --release 2>&1 | tail -5 && echo "BUILD_OK"</automated>
    <manual>The SQL test file exists. Confirm the assertion syntax used is valid for DuckDB 1.4.4. The persistence test verifies DDL-05 (definitions survive restart).</manual>
  </verify>
  <done>
    - `test/phase2_ddl.sql` exists with tests for: define, list, describe, drop, persistence
    - File uses valid DuckDB 1.4.4 SQL (no invalid functions or syntax)
    - Assertions raise an error on failure (non-zero exit)
  </done>
</task>

<task type="auto">
  <name>Task 2: Add test-sql recipe to Justfile and run the full SQL integration test</name>
  <files>Justfile</files>
  <action>
Read the existing `Justfile` to understand current recipes (build, test, lint, fmt, etc.).

Add a `test-sql` recipe that:
1. Builds the extension in release mode (so the `.so`/`.dylib` path is consistent)
2. Runs the SQL logic test via the DuckDB CLI

```just
# Run SQL logic tests for Phase 2 DDL via DuckDB CLI
test-sql: build-release
    duckdb < test/phase2_ddl.sql
```

If the DuckDB CLI is not in PATH on the CI runners but is available locally, add a comment noting this. The CI workflow already handles LOAD smoke tests via the DuckDB CLI (from Phase 1 plan 02); check `01-02-SUMMARY.md` for how the DuckDB CLI was installed in CI.

Also add a `test-all` recipe that runs both Rust tests and SQL logic tests:

```just
# Run all tests: Rust unit tests + SQL logic tests
test-all: test test-sql
```

After adding the recipe, run `just test-sql` to execute the full SQL round-trip:

```bash
just test-sql
```

This command should:
- Build the release extension
- Load it in DuckDB CLI
- Execute all DDL assertions
- Exit 0

Fix any SQL syntax issues in `test/phase2_ddl.sql` until `just test-sql` exits 0.

**If `just test-sql` fails due to DuckDB CLI not being in PATH locally:** Document the issue in SUMMARY.md and verify the test file is syntactically correct by reviewing DuckDB docs. The CI workflow will catch real failures on Linux runners where the DuckDB CLI is installed.

Run `cargo clippy -- -D warnings` after Justfile changes (the Justfile itself does not affect Rust linting, but confirm the full test suite still passes).
  </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. All SQL assertions passed. No error messages from define_semantic_view, list_semantic_views, describe_semantic_view, drop_semantic_view, or the persistence section.</manual>
  </verify>
  <done>
    - `just test-sql` exits 0 — all SQL assertions pass
    - `Justfile` has `test-sql` and `test-all` recipes
    - `cargo test` still exits 0 — no regressions in Rust unit tests
    - The persistence test (ATTACH/DETACH cycle) passes — DDL-05 verified
  </done>
</task>

</tasks>

<verification>
Full phase 2 verification:
1. `cargo test` exits 0 — all model and catalog unit tests pass
2. `cargo clippy -- -D warnings` exits 0 — zero lint violations
3. `cargo build` exits 0 — extension cdylib compiles
4. `just test-sql` exits 0 — all SQL logic assertions pass including persistence round-trip
5. Phase 2 success criteria from ROADMAP.md:
   - `SELECT define_semantic_view('orders', '{...}')` returns confirmation; row in `semantic_layer._definitions`
   - `SELECT drop_semantic_view('orders')` removes the definition; `describe_semantic_view('orders')` errors
   - `FROM list_semantic_views()` returns one row per registered view
   - `FROM describe_semantic_view('orders')` returns all definition fields
   - Definitions survive a DuckDB restart (DDL-05)
</verification>

<success_criteria>
- `test/phase2_ddl.sql` exercises all five Phase 2 success criteria from ROADMAP.md
- `just test-sql` exits 0 — full DDL round-trip verified via DuckDB CLI
- All requirements covered: DDL-01 (define), DDL-02 (drop), DDL-03 (list), DDL-04 (describe), DDL-05 (persistence)
- No regressions: `cargo test` still exits 0
</success_criteria>

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