---
phase: 28-integration-testing-documentation
plan: 02
type: execute
wave: 2
depends_on: ["28-01"]
files_modified:
  - test/sql/phase2_ddl.test
  - test/sql/semantic_views.test
  - test/sql/phase2_restart.test
  - test/sql/phase4_query.test
  - test/sql/phase20_extended_ddl.test
  - test/sql/TEST_LIST
  - test/integration/test_vtab_crash.py
  - test/integration/test_ducklake_ci.py
  - test/integration/test_ducklake.py
autonomous: true
requirements: []

must_haves:
  truths:
    - "No test file references create_semantic_view() or create_or_replace_semantic_view() or create_semantic_view_if_not_exists()"
    - "Restart persistence test still validates define-restart-verify cycle using native DDL"
    - "Query round-trip tests (WHERE, typed output, dimensions-only, metrics-only, error cases) all pass with native DDL"
    - "Python crash reproduction tests all pass with native DDL"
    - "DuckLake CI tests all pass with native DDL"
    - "just test-all passes green"
  artifacts:
    - path: "test/sql/TEST_LIST"
      provides: "Updated test file list without deleted files"
    - path: "test/sql/phase2_restart.test"
      provides: "Restart persistence test rewritten for AS-body syntax"
    - path: "test/sql/phase4_query.test"
      provides: "Query round-trip tests rewritten for AS-body syntax"
    - path: "test/sql/phase20_extended_ddl.test"
      provides: "Extended DDL test with backward-compat section removed"
    - path: "test/integration/test_vtab_crash.py"
      provides: "Crash reproduction tests using native DDL"
    - path: "test/integration/test_ducklake_ci.py"
      provides: "DuckLake CI test using native DDL"
    - path: "test/integration/test_ducklake.py"
      provides: "DuckLake integration test using native DDL"
  key_links:
    - from: "test/sql/phase2_restart.test"
      to: "native DDL path"
      via: "CREATE SEMANTIC VIEW ... AS ... (not create_semantic_view())"
      pattern: "CREATE SEMANTIC VIEW"
    - from: "test/integration/test_vtab_crash.py"
      to: "native DDL path"
      via: "con.execute(CREATE SEMANTIC VIEW ...)"
      pattern: "CREATE SEMANTIC VIEW"
---

<objective>
Rewrite or delete all test files that reference the removed function DDL interface.

Purpose: After Plan 01 removes the function DDL code, all tests using `create_semantic_view()` will fail. This plan converts them to native DDL syntax or deletes them if redundant.

Output: All test files use `CREATE SEMANTIC VIEW ... AS ...` syntax. `just test-all` passes green.
</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/phases/28-integration-testing-documentation/28-CONTEXT.md
@.planning/phases/28-integration-testing-documentation/28-RESEARCH.md
@.planning/phases/28-integration-testing-documentation/28-01-SUMMARY.md

<interfaces>
<!-- Native DDL syntax pattern (replacement for function DDL): -->

Before (function DDL -- being removed):
```sql
SELECT * FROM create_semantic_view('name',
    tables := [{'alias': 'o', 'table': 'orders'}],
    dimensions := [{'name': 'region', 'expr': 'region', 'source_table': 'o'}],
    metrics := [{'name': 'revenue', 'expr': 'sum(amount)', 'source_table': 'o'}]
);
```

After (native DDL -- replacement):
```sql
CREATE SEMANTIC VIEW name AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
    o.region AS region
)
METRICS (
    o.revenue AS sum(amount)
);
```

Key differences:
- Every table MUST have `PRIMARY KEY (col)` in native DDL
- Dimension/metric names use `alias.name` prefix
- Expressions are bare SQL (no quoting of expressions)
- `SELECT * FROM drop_semantic_view('x')` becomes `DROP SEMANTIC VIEW x`

Python equivalent:
```python
con.execute("CREATE SEMANTIC VIEW name AS TABLES (...) DIMENSIONS (...) METRICS (...)")
# instead of:
con.execute("SELECT * FROM create_semantic_view('name', tables := [...], ...)")

con.execute("DROP SEMANTIC VIEW name")
# instead of:
con.execute("SELECT * FROM drop_semantic_view('name')")
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Delete redundant SQL test files, rewrite valuable ones, update TEST_LIST</name>
  <files>test/sql/phase2_ddl.test, test/sql/semantic_views.test, test/sql/phase2_restart.test, test/sql/phase4_query.test, test/sql/phase20_extended_ddl.test, test/sql/TEST_LIST</files>
  <action>
**Delete these 2 files** (coverage exists in newer phase tests):
- `test/sql/phase2_ddl.test` -- 100% function DDL; phases 20+25 cover all DDL verbs via native syntax
- `test/sql/semantic_views.test` -- smoke test only (SELECT 42); phase25 already does `require semantic_views`

**Rewrite `test/sql/phase2_restart.test`:**
- Convert `SELECT * FROM create_semantic_view(...)` to `CREATE SEMANTIC VIEW ... AS TABLES (...) DIMENSIONS (...) METRICS (...)`. The view defines over `events` table with alias `e`, dimension `type` (expr: `type`), metric `n` (expr: `count(*)`). Native DDL requires a PRIMARY KEY -- use `PRIMARY KEY (type)` or an appropriate column.
- Convert `SELECT * FROM drop_semantic_view_if_exists('restart_test')` to `DROP SEMANTIC VIEW IF EXISTS restart_test`
- Convert `SELECT * FROM drop_semantic_view('restart_test')` to `DROP SEMANTIC VIEW restart_test`
- Keep the file EXCLUDED from TEST_LIST (restart limitation still applies with native DDL)
- Update the header comment to reflect AS-body syntax. Remove references to function DDL.

**Rewrite `test/sql/phase4_query.test`:**
- Section 2 (basic round-trip): Convert `create_semantic_view('simple_orders', ...)` to `CREATE SEMANTIC VIEW simple_orders AS TABLES (o AS test_orders PRIMARY KEY (id)) DIMENSIONS (o.region AS o.region, o.category AS o.category) METRICS (o.total_revenue AS sum(o.amount), o.order_count AS count(*))`.
- Section 3 (WHERE composition): Convert `create_semantic_view('filtered_orders', ...)` similarly.
- Section 4 (multi-table joins): Already uses native DDL (`CREATE SEMANTIC VIEW joined_orders AS ...`). No change needed.
- Section 8 (typed output): Convert `create_semantic_view('typed_output_test', ...)` and `create_semantic_view('typed_date_test', ...)` to native DDL.
- Section 9 (cleanup): Convert `SELECT * FROM drop_semantic_view(...)` calls to `DROP SEMANTIC VIEW ...`.
- Update the header comment to remove references to function DDL.

**Rewrite `test/sql/phase20_extended_ddl.test`:**
- Remove the "Backward compatibility" section (lines 181-201) which tests `create_semantic_view()` function DDL. This section includes a `SELECT 42` test (keep it if desired, but it's already tested elsewhere) and a function DDL create+drop pair (remove entirely).
- Update the header comment to remove backward compat references.

**Update `test/sql/TEST_LIST`:**
- Remove `test/sql/phase2_ddl.test`
- Remove `test/sql/semantic_views.test`
- Keep all other entries unchanged
- Note: `phase2_restart.test` was already NOT in TEST_LIST
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build && just test-sql 2>&1 | tail -20</automated>
  </verify>
  <done>2 test files deleted, 3 test files rewritten to native DDL, TEST_LIST updated, `just test-sql` passes</done>
</task>

<task type="auto">
  <name>Task 2: Rewrite Python integration tests to native DDL</name>
  <files>test/integration/test_vtab_crash.py, test/integration/test_ducklake_ci.py, test/integration/test_ducklake.py</files>
  <action>
**Rewrite `test/integration/test_vtab_crash.py`** (13 test functions):

For every `con.execute("SELECT * FROM create_semantic_view(...)")` call, convert to native DDL:
```python
# Before:
con.execute("""
    SELECT * FROM create_semantic_view('cv1_basic',
        tables := [{'alias': 't', 'table': 't1'}],
        dimensions := [{'name': 'name', 'expr': 'name', 'source_table': 't'}],
        metrics := [{'name': 'total', 'expr': 'sum(amount)', 'source_table': 't'},
                    {'name': 'cnt', 'expr': 'count(*)', 'source_table': 't'}]
    )
""")

# After:
con.execute("""
    CREATE SEMANTIC VIEW cv1_basic AS
    TABLES (t AS t1 PRIMARY KEY (id))
    DIMENSIONS (t.name AS name)
    METRICS (t.total AS sum(amount), t.cnt AS count(*))
""")
```

Key considerations for each test:
- **cv1_basic**: table `t1` has columns `id, name, amount`. PK: `(id)`.
- **cv1_decimal**: table `t2` has columns `id, amount`. PK: `(id)`.
- **cv1_datetrunc**: table `t3` has columns `id, dt, val`. PK: `(id)`.
- **cv1_mixed**: table `t4` has columns `id, name, amount, dt, quantity`. PK: `(id)`.
- **cv1_many**: table `t5` has many columns. PK: use a unique column like `ti` (TINYINT) or add an `id`. Actually, there's no natural PK here. Use `(ti)` if unique values, or just use a column with distinct values in the test data. The 3 rows have `ti` values 1, 2, 3 -- use `PRIMARY KEY (ti)`.
- **cv2_close**: table `tc1` has `id, val`. PK: `(id)`.
- **cv2_gc**: table `tc2` has `id, val`. PK: `(id)`.
- **cv2_multi**: table `tc3` has `id, val`. PK: `(id)`.
- **cv3_inmem**: table `tb1` has `id, val`. PK: `(id)`.
- **cv3_file**: table `tb2` has `id, val`. PK: `(id)`.
- **cv4_large**: table `t_large` has `id, category, val`. PK: `(id)`.
- **cv4_empty**: table `t_empty` has `id, val`. PK: `(id)`.
- **cv5_a/b**: tables `ta`/`tb` have `id, val`. PK: `(id)`.

Also update the docstring at the top to remove references to function-based DDL.
Do NOT change the `semantic_view()` or `explain_semantic_view()` query calls -- those stay the same.

**Rewrite `test/integration/test_ducklake_ci.py`:**

Convert the `create_semantic_view` call (Test 1) to native DDL:
```python
# Before:
con.execute("""
    SELECT * FROM create_semantic_view('ci_orders',
        tables := [{'alias': 'o', 'table': 'jaffle.raw_orders'}],
        ...)
""")

# After:
con.execute("""
    CREATE SEMANTIC VIEW ci_orders AS
    TABLES (o AS jaffle.raw_orders PRIMARY KEY (id))
    DIMENSIONS (
        o.store_id AS store_id,
        o.customer AS customer,
        o.ordered_at AS date_trunc('day', ordered_at)
    )
    METRICS (
        o.order_count AS count(*),
        o.total_revenue AS sum(order_total)
    )
""")
```

Convert cleanup `drop_semantic_view` call to `DROP SEMANTIC VIEW`:
```python
# Before:
con.execute("SELECT * FROM drop_semantic_view('ci_orders')")
# After:
con.execute("DROP SEMANTIC VIEW ci_orders")
```

Update docstring to reference native DDL.

**Rewrite `test/integration/test_ducklake.py`:**
Same pattern as `test_ducklake_ci.py`:
- Convert `create_semantic_view` to `CREATE SEMANTIC VIEW ... AS ...`
- Convert `drop_semantic_view` to `DROP SEMANTIC VIEW`
- Use `jaffle.raw_orders` as the table name in TABLES clause (DuckLake-qualified)
- PK: `PRIMARY KEY (id)` (raw_orders has an `id` column)
- Update docstring
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just test-all 2>&1 | tail -20</automated>
  </verify>
  <done>All 3 Python integration tests rewritten to native DDL, `just test-all` passes green (Rust tests + SQL logic tests + Python integration tests)</done>
</task>

</tasks>

<verification>
After both tasks complete:
- `grep -r "create_semantic_view\b" test/ --include="*.test" --include="*.py"` returns zero matches (excluding `_from_json` references if any, and `semantic_view()` query function calls which are different)
- `just test-all` passes green
- `just test-sql` passes
- `just test-ducklake-ci` passes
- Python vtab crash tests pass
</verification>

<success_criteria>
- Zero references to `create_semantic_view()`, `create_or_replace_semantic_view()`, or `create_semantic_view_if_not_exists()` in any test file
- `just test-all` passes (the full quality gate from CLAUDE.md)
- Restart persistence test still validates the define-restart-verify cycle
- Query round-trip scenarios (WHERE, typed output, dimensions-only, metrics-only, error cases) all pass
</success_criteria>

<output>
After completion, create `.planning/phases/28-integration-testing-documentation/28-02-SUMMARY.md`
</output>
