---
phase: 45-alter-comment-get-ddl
plan: 02
type: execute
wave: 2
depends_on: [45-01]
files_modified:
  - src/ddl/get_ddl.rs
  - src/ddl/mod.rs
  - src/lib.rs
  - test/sql/phase45_get_ddl.test
  - test/sql/TEST_LIST
autonomous: true
requirements: [SHOW-07, SHOW-08]

must_haves:
  truths:
    - "SELECT GET_DDL('SEMANTIC_VIEW', 'name') returns a valid CREATE OR REPLACE SEMANTIC VIEW statement"
    - "GET_DDL output includes all clauses: TABLES, RELATIONSHIPS, FACTS, DIMENSIONS, METRICS (when present)"
    - "GET_DDL output includes metadata annotations: COMMENT, WITH SYNONYMS, PRIVATE"
    - "GET_DDL output round-trips: executing the output DDL produces an equivalent definition"
    - "GET_DDL errors clearly for nonexistent views and unsupported object types"
  artifacts:
    - path: "src/ddl/get_ddl.rs"
      provides: "GetDdlScalar VScalar implementation and render_create_ddl function"
      contains: "GetDdlScalar"
      min_lines: 100
    - path: "src/ddl/mod.rs"
      provides: "Module declaration for get_ddl"
      contains: "pub mod get_ddl"
    - path: "src/lib.rs"
      provides: "GET_DDL scalar function registration"
      contains: "get_ddl"
    - path: "test/sql/phase45_get_ddl.test"
      provides: "sqllogictest integration tests for GET_DDL including round-trip"
    - path: "test/sql/TEST_LIST"
      provides: "Updated test list including phase45_get_ddl.test"
  key_links:
    - from: "src/ddl/get_ddl.rs"
      to: "src/catalog.rs"
      via: "CatalogState read for JSON lookup"
      pattern: "state.read()"
    - from: "src/ddl/get_ddl.rs"
      to: "src/model.rs"
      via: "SemanticViewDefinition deserialization"
      pattern: "serde_json::from_str"
    - from: "src/lib.rs"
      to: "src/ddl/get_ddl.rs"
      via: "register_scalar_function_with_state"
      pattern: "GetDdlScalar"
---

<objective>
Implement GET_DDL('SEMANTIC_VIEW', 'name') scalar function that reconstructs re-executable DDL from stored semantic view definitions.

Purpose: Users need to inspect, export, and replicate semantic view definitions. GET_DDL provides DDL round-trip capability matching Snowflake's GET_DDL function for semantic views.
Output: New scalar function module with DDL reconstruction logic, VScalar registration, and round-trip integration tests.
</objective>

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

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/45-alter-comment-get-ddl/45-RESEARCH.md
@.planning/phases/45-alter-comment-get-ddl/45-01-SUMMARY.md

@src/model.rs
@src/catalog.rs
@src/body_parser.rs
@src/ddl/mod.rs
@src/lib.rs

<interfaces>
<!-- Key types and contracts the executor needs. Extracted from codebase. -->

From src/model.rs:
```rust
pub struct SemanticViewDefinition {
    pub base_table: String,
    pub tables: Vec<TableRef>,
    pub dimensions: Vec<Dimension>,
    pub metrics: Vec<Metric>,
    pub joins: Vec<Join>,
    pub facts: Vec<Fact>,
    pub column_type_names: Vec<String>,     // runtime -- skip in DDL
    pub column_types_inferred: Vec<u32>,    // runtime -- skip in DDL
    pub created_on: Option<String>,         // runtime -- skip in DDL
    pub database_name: Option<String>,      // runtime -- skip in DDL
    pub schema_name: Option<String>,        // runtime -- skip in DDL
    pub comment: Option<String>,            // emit as COMMENT = '...' after view name
}

pub struct TableRef {
    pub alias: String,
    pub table: String,
    pub pk_columns: Vec<String>,
    pub unique_constraints: Vec<Vec<String>>,
    pub comment: Option<String>,
    pub synonyms: Vec<String>,
}

pub struct Dimension {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,        // runtime -- skip in DDL
    pub comment: Option<String>,
    pub synonyms: Vec<String>,
}

pub struct Metric {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,        // runtime -- skip in DDL
    pub using_relationships: Vec<String>,
    pub comment: Option<String>,
    pub synonyms: Vec<String>,
    pub access: AccessModifier,
}

pub struct Fact {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,        // runtime -- skip in DDL
    pub comment: Option<String>,
    pub synonyms: Vec<String>,
    pub access: AccessModifier,
}

pub struct Join {
    pub table: String,
    pub on: String,                         // legacy -- skip in DDL
    pub from_cols: Vec<String>,             // legacy -- skip in DDL
    pub join_columns: Vec<JoinColumn>,      // legacy -- skip in DDL
    pub from_alias: String,
    pub fk_columns: Vec<String>,
    pub ref_columns: Vec<String>,           // runtime -- skip in DDL
    pub name: Option<String>,
    pub cardinality: Cardinality,           // runtime -- skip in DDL
}

pub enum AccessModifier { Public, Private }
```

From src/catalog.rs:
```rust
pub type CatalogState = Arc<RwLock<HashMap<String, String>>>;
```

From duckdb-1.10500.0 VScalar trait:
```rust
pub trait VScalar: Sized {
    type State: Sized;
    unsafe fn invoke(
        state: &Self::State,
        input: &mut DataChunkHandle,
        output: &mut dyn WritableVector,
    ) -> Result<(), Box<dyn std::error::Error>>;
    fn signatures() -> Vec<ScalarFunctionSignature>;
}
```

Body parser clause ordering (MUST follow in DDL output):
TABLES -> RELATIONSHIPS -> FACTS -> DIMENSIONS -> METRICS
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: DDL reconstruction module with render_create_ddl and unit tests</name>
  <files>src/ddl/get_ddl.rs, src/ddl/mod.rs</files>
  <read_first>
    - src/model.rs (SemanticViewDefinition, TableRef, Join, Dimension, Metric, Fact structs -- all fields)
    - src/body_parser.rs (clause ordering: TABLES, RELATIONSHIPS, FACTS, DIMENSIONS, METRICS -- see CLAUSE_ORDER constant around line 54)
    - src/ddl/mod.rs (existing module declarations)
  </read_first>
  <behavior>
    - Test: render_create_ddl for a minimal view (1 table, 1 dimension, 1 metric) produces valid DDL starting with "CREATE OR REPLACE SEMANTIC VIEW"
    - Test: render_create_ddl for a view with COMMENT produces "COMMENT = 'the comment'" after the view name and before AS
    - Test: render_create_ddl for a view with comment containing single quote produces "COMMENT = 'it''s a test'"
    - Test: render_create_ddl for a view with RELATIONSHIPS produces a RELATIONSHIPS clause with correct syntax
    - Test: render_create_ddl for a view with FACTS produces a FACTS clause
    - Test: render_create_ddl for a view with PRIVATE metrics produces "PRIVATE" prefix before the metric
    - Test: render_create_ddl for a view with USING relationships produces "USING (rel1)" before AS
    - Test: render_create_ddl for a view with SYNONYMS produces "WITH SYNONYMS = ('syn1', 'syn2')"
    - Test: render_create_ddl for a view with COMMENT on dimensions/metrics/facts/tables produces correct annotations
    - Test: render_create_ddl for a view with UNIQUE constraints produces "UNIQUE (col1, col2)"
    - Test: render_create_ddl for a view with empty tables vec returns error about legacy format
    - Test: render_create_ddl omits RELATIONSHIPS when joins is empty
    - Test: render_create_ddl omits FACTS when facts is empty
    - Test: emit_comment and emit_synonyms helpers produce correct output
  </behavior>
  <action>
    **Step 1: Add module declaration to src/ddl/mod.rs**:
    Add `pub mod get_ddl;` to the module list.

    **Step 2: Create src/ddl/get_ddl.rs** with the following structure:

    ```rust
    //! GET_DDL scalar function: reconstructs CREATE OR REPLACE SEMANTIC VIEW DDL
    //! from a stored SemanticViewDefinition JSON.
    //!
    //! The DDL reconstruction follows the body parser clause ordering:
    //! TABLES -> RELATIONSHIPS -> FACTS -> DIMENSIONS -> METRICS
    //! with optional clauses omitted when empty.

    use std::fmt::Write;

    use crate::model::{AccessModifier, SemanticViewDefinition};
    ```

    **Helper functions** (not gated on `extension` feature -- usable in unit tests):

    `fn escape_single_quote(s: &str) -> String`:
    Returns `s.replace('\'', "''")` -- SQL single-quote doubling.

    `fn emit_comment(out: &mut String, comment: &Option<String>)`:
    If Some, appends ` COMMENT = '<escaped>'`.

    `fn emit_synonyms(out: &mut String, synonyms: &[String])`:
    If non-empty, appends ` WITH SYNONYMS = ('<escaped1>', '<escaped2>')`.

    `pub fn render_create_ddl(name: &str, def: &SemanticViewDefinition) -> Result<String, String>`:
    Main reconstruction function. Returns Err for legacy definitions (empty tables vec).

    The function builds the DDL string using `write!()` / `writeln!()`:

    1. **Header**: `CREATE OR REPLACE SEMANTIC VIEW {name}`
    2. **View-level comment**: if `def.comment` is Some, append ` COMMENT = '<escaped>'`
    3. **AS keyword**: ` AS\n`
    4. **TABLES clause** (required):
       - If `def.tables.is_empty()`: return Err("Legacy definition format; please re-create using CREATE OR REPLACE SEMANTIC VIEW")
       - For each table in def.tables (comma-separated):
         - `    {alias} AS {table}`
         - If pk_columns non-empty: ` PRIMARY KEY ({col1}, {col2})`
         - For each unique_constraint: ` UNIQUE ({col1}, {col2})`
         - `emit_comment` and `emit_synonyms`
    5. **RELATIONSHIPS clause** (optional, only if `def.joins` non-empty):
       - For each join (comma-separated):
         - `    {join.name.unwrap_or_default()} AS {join.from_alias}({fk_col1}, {fk_col2}) REFERENCES {join.table}`
         - Note: If join.name is None, this is a legacy relationship format -- skip it or use a synthetic name
    6. **FACTS clause** (optional, only if `def.facts` non-empty):
       - For each fact (comma-separated):
         - If access is Private: `    PRIVATE `
         - `{source_table}.{name} AS {expr}`
         - `emit_comment` and `emit_synonyms`
    7. **DIMENSIONS clause** (optional, only if `def.dimensions` non-empty):
       - For each dimension (comma-separated):
         - `{source_table}.{name} AS {expr}`
         - `emit_comment` and `emit_synonyms`
    8. **METRICS clause** (optional, only if `def.metrics` non-empty):
       - For each metric (comma-separated):
         - If access is Private: `    PRIVATE `
         - If source_table is Some: `{source_table}.{name}` else just `{name}` (derived metrics)
         - If using_relationships non-empty: ` USING ({rel1}, {rel2})`
         - ` AS {expr}`
         - `emit_comment` and `emit_synonyms`

    **Important field mapping rules** (from research):
    - Expressions (expr) are emitted verbatim -- they are NOT inside single quotes, they go between AS and trailing annotations
    - Only COMMENT values and SYNONYM values need single-quote escaping
    - Identifiers (table names, column names, aliases) are NOT quoted
    - Clause body is indented with 4 spaces per entry
    - Clauses are separated by newlines
    - Each clause is wrapped in `CLAUSE_NAME (\n...\n)`
    - At least one of DIMENSIONS or METRICS must be present (enforced by parser, not by GET_DDL)

    **Step 3: Write unit tests** in a `#[cfg(test)] mod tests` block at the bottom of get_ddl.rs:
    - Test all behaviors listed in the behavior section above
    - Build SemanticViewDefinition structs directly (using Default::default() for runtime fields)
    - Verify output strings contain expected patterns
    - Verify error case for empty tables vec

    These tests run under `cargo test` (no extension feature needed since render_create_ddl uses only model types).
  </action>
  <verify>
    <automated>cargo test -- --test-threads=1 get_ddl 2>&1 | tail -20</automated>
  </verify>
  <acceptance_criteria>
    - src/ddl/get_ddl.rs exists with `pub fn render_create_ddl` function
    - src/ddl/get_ddl.rs contains `fn escape_single_quote`
    - src/ddl/get_ddl.rs contains `fn emit_comment`
    - src/ddl/get_ddl.rs contains `fn emit_synonyms`
    - src/ddl/get_ddl.rs contains `CREATE OR REPLACE SEMANTIC VIEW` in output
    - src/ddl/get_ddl.rs contains clause names: `TABLES`, `RELATIONSHIPS`, `FACTS`, `DIMENSIONS`, `METRICS`
    - src/ddl/get_ddl.rs returns Err for empty tables vec (legacy definitions)
    - src/ddl/mod.rs contains `pub mod get_ddl`
    - Unit tests cover: minimal view, view with comment, comment with quotes, relationships, facts, PRIVATE metrics, USING, SYNONYMS, UNIQUE constraints, empty tables error
    - `cargo test` exits 0
  </acceptance_criteria>
  <done>render_create_ddl reconstructs all DDL clauses with correct escaping, annotations, and clause ordering; unit tests verify all model field combinations; module integrated into ddl/mod.rs</done>
</task>

<task type="auto">
  <name>Task 2: GetDdlScalar VScalar registration and round-trip integration tests</name>
  <files>src/ddl/get_ddl.rs, src/lib.rs, test/sql/phase45_get_ddl.test, test/sql/TEST_LIST</files>
  <read_first>
    - src/ddl/get_ddl.rs (render_create_ddl from Task 1)
    - src/lib.rs (init_extension function, existing VTab registration pattern, imports)
    - src/catalog.rs (CatalogState type)
    - .planning/phases/45-alter-comment-get-ddl/45-RESEARCH.md (VScalar trait pattern, code examples)
    - test/sql/phase43_metadata.test (example of complex semantic view DDL for round-trip testing)
  </read_first>
  <action>
    **Step 1: Add GetDdlScalar to src/ddl/get_ddl.rs**:

    The VScalar implementation must be feature-gated on `extension` (same as all DDL VTabs). Add at the top of the file, after the non-gated render functions:

    ```rust
    #[cfg(feature = "extension")]
    mod scalar {
        use duckdb::core::{DataChunkHandle, LogicalTypeHandle, LogicalTypeId};
        use duckdb::vscalar::{ScalarFunctionSignature, VScalar};
        use duckdb::vtab::arrow::WritableVector;
        use libduckdb_sys::duckdb_string_t;
        use duckdb::ffi::DuckString;

        use crate::catalog::CatalogState;
        use crate::model::SemanticViewDefinition;
        use super::render_create_ddl;

        pub struct GetDdlScalar;

        impl VScalar for GetDdlScalar {
            type State = CatalogState;

            unsafe fn invoke(
                state: &Self::State,
                input: &mut DataChunkHandle,
                output: &mut dyn WritableVector,
            ) -> Result<(), Box<dyn std::error::Error>> {
                let len = input.len();
                let type_vec = input.flat_vector(0);
                let name_vec = input.flat_vector(1);
                let types = type_vec.as_slice_with_len::<duckdb_string_t>(len);
                let names = name_vec.as_slice_with_len::<duckdb_string_t>(len);
                let out_vec = output.flat_vector();

                for i in 0..len {
                    let obj_type = DuckString::new(&mut { types[i] }).as_str().to_string();
                    let name = DuckString::new(&mut { names[i] }).as_str().to_string();

                    if !obj_type.eq_ignore_ascii_case("SEMANTIC_VIEW") {
                        return Err(format!(
                            "GET_DDL: unsupported object type '{}'. Only 'SEMANTIC_VIEW' is supported.",
                            obj_type
                        ).into());
                    }

                    let guard = state.read().unwrap();
                    let json = guard.get(&name).ok_or_else(|| {
                        format!("semantic view '{}' does not exist", name)
                    })?;
                    let def: SemanticViewDefinition = serde_json::from_str(json)?;
                    let ddl = render_create_ddl(&name, &def)?;
                    out_vec.insert(i, ddl.as_str());
                }
                Ok(())
            }

            fn signatures() -> Vec<ScalarFunctionSignature> {
                vec![ScalarFunctionSignature::exact(
                    vec![
                        LogicalTypeHandle::from(LogicalTypeId::Varchar),  // object_type
                        LogicalTypeHandle::from(LogicalTypeId::Varchar),  // name
                    ],
                    LogicalTypeHandle::from(LogicalTypeId::Varchar),      // return: DDL string
                )]
            }
        }
    }

    #[cfg(feature = "extension")]
    pub use scalar::GetDdlScalar;
    ```

    **IMPORTANT**: The exact VScalar API (method signatures, DuckString usage, WritableVector, flat_vector) must be verified against the actual duckdb crate source at the version used in this project (duckdb = "=1.10500.0"). The research document verified the API from `duckdb-1.10500.0/src/vscalar/mod.rs`. If the API is slightly different (e.g., different method names on WritableVector or DataChunkHandle), adapt accordingly. Check the duckdb crate's test examples for correct usage patterns.

    **Step 2: Register GET_DDL in src/lib.rs**:

    In `init_extension`, after all table function registrations (before the query connection block), add:

    ```rust
    // Register GET_DDL scalar function
    con.register_scalar_function_with_state::<ddl::get_ddl::GetDdlScalar, _>(
        "get_ddl",
        &catalog_state,
    )?;
    ```

    Add the import:
    ```rust
    use crate::ddl::get_ddl::GetDdlScalar;  // or reference via path
    ```

    Note: `register_scalar_function_with_state` is the method on Connection for registering VScalar functions. Verify the exact method name from the duckdb crate -- it may be `register_scalar_function` or similar.

    **Step 3: Create sqllogictest at test/sql/phase45_get_ddl.test**:

    ```
    # Phase 45: GET_DDL('SEMANTIC_VIEW', 'name')
    #
    # Verifies GET_DDL scalar function for DDL reconstruction:
    #   SHOW-07: Returns a valid CREATE OR REPLACE SEMANTIC VIEW statement
    #   SHOW-08: Output round-trips correctly
    #
    # Requirements covered: SHOW-07, SHOW-08

    require semantic_views
    ```

    Test cases:

    **Case 1: Minimal view GET_DDL**
    - Create a simple semantic view with 1 table, 1 dimension, 1 metric
    - SELECT GET_DDL('SEMANTIC_VIEW', 'simple_view')
    - Verify output contains `CREATE OR REPLACE SEMANTIC VIEW simple_view`
    - Verify output contains `TABLES`, `DIMENSIONS`, `METRICS`

    **Case 2: View with all annotations (COMMENT, SYNONYMS, PRIVATE)**
    - Create a view with view-level COMMENT, table COMMENT, dimension COMMENT, PRIVATE metric, SYNONYMS on metric
    - SELECT GET_DDL('SEMANTIC_VIEW', 'annotated_view')
    - Verify output contains COMMENT, SYNONYMS, PRIVATE keywords

    **Case 3: View with RELATIONSHIPS**
    - Create a multi-table view with relationships
    - SELECT GET_DDL('SEMANTIC_VIEW', 'joined_view')
    - Verify output contains RELATIONSHIPS clause

    **Case 4: View with FACTS**
    - Create a view with FACTS clause
    - SELECT GET_DDL('SEMANTIC_VIEW', 'facts_view')
    - Verify output contains FACTS clause

    **Case 5: View with USING RELATIONSHIPS on metrics**
    - Create a view with metrics using USING clause
    - Verify GET_DDL output contains USING keyword

    **Case 6: Round-trip test (SHOW-08)**
    - Create a complex view with all features (COMMENT, SYNONYMS, PRIVATE, RELATIONSHIPS, FACTS, USING)
    - Call GET_DDL to get the DDL
    - DROP the original view
    - Execute the GET_DDL output (it should be a valid CREATE OR REPLACE statement)
    - Call GET_DDL again on the re-created view
    - Verify the second GET_DDL output matches the first (or is functionally equivalent)
    - Verify querying the re-created view produces correct results

    **Case 7: Error - nonexistent view**
    - SELECT GET_DDL('SEMANTIC_VIEW', 'nonexistent') should error with "does not exist"

    **Case 8: Error - unsupported object type**
    - SELECT GET_DDL('TABLE', 'something') should error with "unsupported object type"

    **Case 9: Comment with single quotes**
    - Create a view with COMMENT = 'it''s a test'
    - GET_DDL should produce COMMENT = 'it''s a test' in output
    - Round-trip: execute the output, verify comment preserved

    **Case 10: Case-insensitive object type**
    - SELECT GET_DDL('semantic_view', 'name') should work (lowercase)

    **Step 4: Update test/sql/TEST_LIST**:
    Add `test/sql/phase45_get_ddl.test` as a new line.

    **Step 5: Build and run full test suite**:
    Run `just build && just test-sql` to verify sqllogictest passes.
    Run `cargo test` to verify all Rust tests pass.
    Run `just test-all` for the complete quality gate.
  </action>
  <verify>
    <automated>just test-all</automated>
  </verify>
  <acceptance_criteria>
    - src/ddl/get_ddl.rs contains `pub struct GetDdlScalar`
    - src/ddl/get_ddl.rs contains `impl VScalar for GetDdlScalar`
    - src/ddl/get_ddl.rs contains `SEMANTIC_VIEW` string check (case-insensitive)
    - src/ddl/get_ddl.rs contains `render_create_ddl` call within invoke
    - src/lib.rs contains `get_ddl` scalar function registration
    - test/sql/phase45_get_ddl.test exists with at least 8 test cases
    - test/sql/phase45_get_ddl.test contains a round-trip test (create -> GET_DDL -> drop -> execute output -> GET_DDL again)
    - test/sql/TEST_LIST contains `phase45_get_ddl.test`
    - `just test-all` exits 0
  </acceptance_criteria>
  <done>GET_DDL scalar function registered and working end-to-end: returns valid CREATE OR REPLACE DDL for all view features, round-trip test proves DDL can be re-executed to produce equivalent definitions, error cases handled, full test suite passes</done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| User SQL -> GET_DDL function | User provides object_type and name as VARCHAR args |
| Catalog JSON -> DDL output | Stored JSON deserialized and emitted as DDL text |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-45-04 | Information Disclosure | GET_DDL output | accept | GET_DDL intentionally exposes full view definition including PRIVATE items (matches Snowflake behavior -- PRIVATE controls query access, not definition visibility) |
| T-45-05 | Tampering | GET_DDL object_type input | mitigate | Validate object_type is exactly "SEMANTIC_VIEW" (case-insensitive); reject all other types with clear error |
| T-45-06 | Tampering | Stored JSON deserialization | mitigate | serde_json::from_str with typed target (SemanticViewDefinition) -- invalid JSON produces error, not code execution |
| T-45-07 | Injection | DDL output with user-controlled strings | mitigate | Comments and synonyms use single-quote escaping (' -> ''); expressions emitted verbatim (not inside quotes); identifiers unquoted (safe by construction from parser validation at create time) |
</threat_model>

<verification>
1. `cargo test` passes (all Rust unit tests including new render_create_ddl tests)
2. `just build && just test-sql` passes (sqllogictest including phase45_get_ddl.test)
3. `just test-all` passes (full suite)
4. GET_DDL produces valid DDL for all clause combinations
5. Round-trip: create -> GET_DDL -> drop -> execute output -> GET_DDL produces same output
6. Error cases: nonexistent view, unsupported object type
7. Single-quote escaping in comments and synonyms works correctly
</verification>

<success_criteria>
- SHOW-07: GET_DDL('SEMANTIC_VIEW', 'name') returns a syntactically valid CREATE OR REPLACE SEMANTIC VIEW statement for all supported features
- SHOW-08: Executing GET_DDL output and calling GET_DDL again produces functionally equivalent DDL
- First scalar function (VScalar) in the extension works correctly alongside existing table functions
- All existing tests continue to pass
</success_criteria>

<output>
After completion, create `.planning/phases/45-alter-comment-get-ddl/45-02-SUMMARY.md`
</output>
