---
phase: 11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views
plan: "05"
type: execute
wave: 4
depends_on: ["04"]
files_modified:
  - test/sql/phase2_ddl.test
  - test/sql/phase4_query.test
  - test/sql/semantic_views.test
  - test/sql/phase2_restart.test
autonomous: true
requirements:
  - DDL-03
  - DDL-04
  - DDL-05
  - DDL-06

must_haves:
  truths:
    - "make test passes — all sqllogictest files run cleanly"
    - "phase2_ddl.test uses new STRUCT/LIST DDL syntax for define_semantic_view"
    - "phase4_query.test uses semantic_view instead of semantic_query"
    - "semantic_views.test (end-to-end) uses new DDL and query syntax"
    - "Non-semantic SQL (CREATE TABLE, SELECT 1) executes identically — no regression"
    - "define_or_replace_semantic_view and drop variants work in tests"
  artifacts:
    - path: "test/sql/phase2_ddl.test"
      provides: "DDL tests with new STRUCT/LIST syntax"
    - path: "test/sql/phase4_query.test"
      provides: "Query tests with semantic_view function name"
    - path: "test/sql/semantic_views.test"
      provides: "End-to-end test with new DDL + query syntax"
    - path: "test/sql/phase2_restart.test"
      provides: "Persistence test updated to new DDL syntax"
  key_links:
    - from: "test/sql/phase2_ddl.test"
      to: "src/ddl/define.rs (Plan 04)"
      via: "Exercises define_semantic_view 6-arg call end-to-end"
    - from: "test/sql/phase4_query.test"
      to: "src/lib.rs (Plan 04)"
      via: "semantic_view function registration"
---

<objective>
Update all sqllogictest .test files to use the new DDL and query syntax, then run the full test suite to confirm end-to-end correctness.

Purpose: This is the integration validation plan. Plans 01-04 restructure Rust code; this plan updates the SQL-level tests that drive the extension and verifies the complete interface works end-to-end.

Output: All .test files updated to new syntax; `make test` passes.
</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/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/11.1-CONTEXT.md
@.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md
@.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/11.1-04-SUMMARY.md

<interfaces>
<!-- New DDL call pattern — positional 6-arg VScalar -->
-- Single-table view (no joins, no time dimensions):
SELECT define_semantic_view(
    'orders_view',
    [{'alias': 'o', 'table': 'orders'}],
    [],
    [{'name': 'region', 'expr': 'o.region', 'source_table': 'o'},
     {'name': 'status', 'expr': 'o.status', 'source_table': 'o'}],
    [],
    [{'name': 'revenue', 'expr': 'sum(o.amount)', 'source_table': 'o'},
     {'name': 'order_count', 'expr': 'count(*)', 'source_table': 'o'}]
);

-- Multi-table view (with relationships):
SELECT define_semantic_view(
    'sales_view',
    [{'alias': 'o', 'table': 'orders'}, {'alias': 'c', 'table': 'customers'}],
    [{'from_table': 'o', 'to_table': 'c',
      'join_columns': [{'from': 'customer_id', 'to': 'id'}]}],
    [{'name': 'region', 'expr': 'o.region', 'source_table': 'o'},
     {'name': 'customer_name', 'expr': 'c.name', 'source_table': 'c'}],
    [],
    [{'name': 'revenue', 'expr': 'sum(o.amount)', 'source_table': 'o'}]
);

-- With time dimensions:
SELECT define_semantic_view(
    'time_view',
    [{'alias': 'o', 'table': 'orders'}],
    [],
    [{'name': 'status', 'expr': 'o.status', 'source_table': 'o'}],
    [{'name': 'order_date', 'expr': 'o.created_at', 'granularity': 'month'}],
    [{'name': 'revenue', 'expr': 'sum(o.amount)', 'source_table': 'o'}]
);

-- define_or_replace:
SELECT define_or_replace_semantic_view(
    'orders_view',
    [{'alias': 'o', 'table': 'orders'}],
    [], [...], [], [...]
);

-- drop variants (UNCHANGED signature — still take name only):
SELECT drop_semantic_view('orders_view');
SELECT drop_semantic_view_if_exists('nonexistent_view');

<!-- New query syntax — only function name change -->
SELECT * FROM semantic_view('orders_view', dimensions := ['region'], metrics := ['revenue']);
SELECT * FROM semantic_view('sales_view', dimensions := ['o.region', 'c.customer_name'], metrics := ['revenue']);

<!-- Verify reserved keyword 'table' in STRUCT literal works -->
-- IMPORTANT: Test {'alias': 'o', 'table': 'orders'} in DuckDB BEFORE writing tests.
-- If 'table' causes a parse error as a STRUCT key, rename to 'tbl' in BOTH:
--   1. The test files here
--   2. The signatures() in define.rs (Plan 04) and parse_args.rs (Plan 02)
--   3. The DESIGN.md
-- This is Pitfall 4 from RESEARCH.md. Verify empirically first.
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Verify 'table' keyword safety and update all .test files to new DDL + query syntax</name>
  <files>test/sql/phase2_ddl.test, test/sql/phase4_query.test, test/sql/semantic_views.test, test/sql/phase2_restart.test</files>
  <action>
**Step 0: Verify 'table' keyword in STRUCT literals**

Before editing test files, run a quick sanity check using the DuckDB binary or `make test` with a single test to verify that `{'alias': 'x', 'table': 'y'}` is valid DuckDB STRUCT literal syntax.

Build the extension first: `make build` or `cargo build --features extension`.

If 'table' is rejected as a STRUCT key (SQL parse error), rename:
- The STRUCT field from `'table'` to `'tbl'` in all test files
- Also update signatures() in define.rs: the field name in `LogicalTypeHandle::struct_type(&[("table", ...)])` → `("tbl", ...)`
- Also update parse_args.rs to read field index correctly
- Also update DESIGN.md

If 'table' works fine, proceed with 'table' as-is.

**Step 1: Read each test file in full, then rewrite**

Read test/sql/phase2_ddl.test, phase4_query.test, semantic_views.test, phase2_restart.test before editing.

**Step 2: Update phase2_ddl.test**

Replace every `define_semantic_view('name', '{"base_table":...json...}')` call with the new positional 6-arg syntax. Key patterns to convert:

- Simple single-table view: `define_semantic_view('v', '{"base_table":"t","dimensions":[...],"metrics":[...]}')`
  becomes:
  ```sql
  SELECT define_semantic_view('v', [{'alias':'t','table':'t'}], [], [{...dims...}], [], [{...metrics...}]);
  ```
- Join-based view: add the relationship in arg 2
- Time dimension view: move time dims to arg 4

Also replace any `define_or_replace_semantic_view('name', '...')` with new 6-arg form.

Keep drop_semantic_view and drop_semantic_view_if_exists calls unchanged (they still take just name).

Keep list_semantic_views and describe_semantic_view calls unchanged.

**Step 3: Update phase4_query.test**

Replace all `semantic_query(` with `semantic_view(`. No other changes needed for basic queries. Add one new test case for table-qualified dimension names:
```sql
query I rowsort
SELECT * FROM semantic_view('sales_view', dimensions := ['o.region'], metrics := ['revenue']);
----
...expected results...
```

**Step 4: Update semantic_views.test (end-to-end)**

This is the primary smoke test. Update all DDL calls to new syntax and all `semantic_query` → `semantic_view`. Ensure at least one multi-table test with `join_columns` relationship is present.

**Step 5: Update phase2_restart.test**

This test verifies persistence across DB restart. Update the DDL call at the beginning to new syntax. The query at the end uses `semantic_query` — rename to `semantic_view`.

**Step 6: Run make test**

```bash
make test
```

If tests fail:
1. Read the error output carefully
2. Most likely causes:
   - 'table' keyword conflict → rename to 'tbl' (see Step 0 resolution)
   - STRUCT field order mismatch in parse_args.rs → verify field indices
   - Nested LIST-in-STRUCT read bug in parse_args.rs → check inner list len()
3. Fix the underlying Rust code (parse_args.rs or define.rs) if needed, then re-run
4. Iterate until `make test` passes

**Anti-patterns to avoid:**
- Do NOT call SQL from VScalar invoke() — all STRUCT reading is pure Rust memory access
- Do NOT skip running `make test` — the test run is the final integration gate
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && make test 2>&1 | tail -30</automated>
  </verify>
  <done>make test passes with exit code 0; all .test files use new DDL syntax with STRUCT/LIST args; all semantic_query references replaced with semantic_view</done>
</task>

</tasks>

<verification>
`make test` passes (exit code 0). All four .test files updated. No `semantic_query` string remains in test files. No raw JSON string DDL calls remain.
</verification>

<success_criteria>
- `make test` exits 0
- `grep -r "semantic_query" test/sql/` returns no results
- `grep -r "\"base_table\"" test/sql/` returns no results (no more raw JSON DDL)
- phase2_ddl.test covers: define, define_or_replace, drop, drop_if_exists with new syntax
- phase4_query.test covers: basic query + table-qualified dim names
- phase2_restart.test demonstrates persistence with new DDL syntax
</success_criteria>

<output>
After completion, create `.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/11.1-05-SUMMARY.md`
</output>
