---
phase: 53-yaml-file-loading
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/parse.rs
  - cpp/src/shim.cpp
  - test/sql/phase53_yaml_file.test
  - test/sql/TEST_LIST
autonomous: true
requirements: [YAML-02, YAML-07]

must_haves:
  truths:
    - "CREATE SEMANTIC VIEW name FROM YAML FILE '/path/to/file.yaml' creates a queryable semantic view"
    - "CREATE OR REPLACE SEMANTIC VIEW name FROM YAML FILE '/path' replaces an existing view"
    - "CREATE SEMANTIC VIEW IF NOT EXISTS name FROM YAML FILE '/path' is a no-op when view exists"
    - "SET enable_external_access = false blocks FROM YAML FILE with a security error"
    - "File-not-found produces an error mentioning FROM YAML FILE context"
    - "FROM YAML (inline) still works unchanged after FILE branch is added"
  artifacts:
    - path: "src/parse.rs"
      provides: "extract_single_quoted(), rewrite_ddl_yaml_file_body(), FROM YAML FILE detection in validate_create_body()"
      contains: "fn extract_single_quoted"
    - path: "cpp/src/shim.cpp"
      provides: "__SV_YAML_FILE__ sentinel interception, read_text() file loading, query reconstruction"
      contains: "__SV_YAML_FILE__"
    - path: "test/sql/phase53_yaml_file.test"
      provides: "Integration tests for FROM YAML FILE"
      contains: "FROM YAML FILE"
    - path: "test/sql/TEST_LIST"
      provides: "Test registry entry"
      contains: "phase53_yaml_file.test"
  key_links:
    - from: "src/parse.rs rewrite_ddl_yaml_file_body()"
      to: "cpp/src/shim.cpp sv_ddl_bind"
      via: "__SV_YAML_FILE__ sentinel protocol"
      pattern: "__SV_YAML_FILE__"
    - from: "cpp/src/shim.cpp sv_ddl_bind"
      to: "DuckDB read_text()"
      via: "SELECT content FROM read_text() on sv_ddl_conn"
      pattern: "read_text"
    - from: "cpp/src/shim.cpp sv_ddl_bind"
      to: "sv_rewrite_ddl_rust"
      via: "Re-invocation with reconstructed inline YAML query"
      pattern: "sv_rewrite_ddl_rust"
---

<objective>
Add `FROM YAML FILE '/path/to/file.yaml'` syntax to `CREATE SEMANTIC VIEW` DDL, enabling users to define semantic views from external YAML files with proper security boundaries.

Purpose: Users can version-control YAML semantic view definitions as separate files and load them directly. DuckDB's `enable_external_access` setting is enforced automatically via `read_text()`.
Output: Working file-based YAML DDL with unit tests (Rust) and integration tests (sqllogictest).
</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/53-yaml-file-loading/53-RESEARCH.md

# Prior phase context (Phase 52 established the FROM YAML inline path)
@.planning/phases/52-yaml-ddl-integration/52-01-SUMMARY.md

# Source files being modified
@src/parse.rs
@cpp/src/shim.cpp

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

From src/parse.rs (DdlKind enum, line 27):
```rust
pub enum DdlKind {
    Create,
    CreateOrReplace,
    CreateIfNotExists,
    Drop,
    DropIfExists,
    Describe,
    Show,
    ShowTerse,
    ShowColumns,
    Alter,
}
```

From src/parse.rs (ParseError struct):
```rust
pub struct ParseError {
    pub message: String,
    pub position: Option<usize>,
}
```

From src/parse.rs (existing FROM YAML detection, lines 1055-1065):
```rust
// --- FROM YAML body path (Phase 52) ---
let is_yaml_body = after_name_trimmed
    .get(..9)
    .is_some_and(|s| s.eq_ignore_ascii_case("FROM YAML"))
    && (after_name_trimmed.len() == 9
        || after_name_trimmed.as_bytes()[9].is_ascii_whitespace());
if is_yaml_body {
    let yaml_text = after_name_trimmed[9..].trim_start();
    return rewrite_ddl_yaml_body(kind, name, yaml_text, view_comment);
}
// --- End FROM YAML body path ---
```

From src/parse.rs (rewrite_ddl_yaml_body signature, line 1176):
```rust
fn rewrite_ddl_yaml_body(
    kind: DdlKind,
    name: &str,
    yaml_text: &str,
    view_comment: Option<String>,
) -> Result<Option<String>, ParseError>
```

From src/parse.rs (extract_dollar_quoted, line 1147):
```rust
fn extract_dollar_quoted(input: &str) -> Result<(String, usize), ParseError>
```

From cpp/src/shim.cpp (sv_ddl_bind structure, lines 134-201):
```cpp
static unique_ptr<FunctionData> sv_ddl_bind(
    ClientContext &, TableFunctionBindInput &input,
    vector<LogicalType> &return_types, vector<string> &names) {
    auto query = StringValue::Get(input.inputs[0]);
    std::string sql_str(65536, '\0');
    char error_buf[1024];
    // ... sv_rewrite_ddl_rust call ...
    // ... duckdb_query(sv_ddl_conn, sql_str.c_str(), &result) ...
}
```

From cpp/src/shim.cpp (FFI declaration):
```cpp
extern "C" uint8_t sv_rewrite_ddl_rust(
    const char *input, size_t input_len,
    char *output, size_t output_len,
    char *error_buf, size_t error_len);
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Add FROM YAML FILE detection, path extraction, and sentinel generation to parse.rs</name>
  <files>src/parse.rs</files>
  <read_first>
    - src/parse.rs (full file -- understand validate_create_body structure, rewrite_ddl_yaml_body pattern, existing tests at bottom)
    - .planning/phases/53-yaml-file-loading/53-RESEARCH.md (Pattern 2, Pattern 3, Pattern 4 for implementation details)
  </read_first>
  <behavior>
    - Test: extract_single_quoted("'/path/to/file.yaml'") returns Ok(("path/to/file.yaml", 21))
    - Test: extract_single_quoted("'/file''s.yaml'") returns Ok(("file's.yaml", 15)) (escaped quotes)
    - Test: extract_single_quoted("''") returns Ok(("", 2)) -- empty string is valid extraction but rewrite_ddl_yaml_file_body rejects it
    - Test: extract_single_quoted("no quote") returns Err with "Expected single-quoted file path"
    - Test: extract_single_quoted("'unterminated") returns Err with "Unterminated file path string"
    - Test: rewrite_ddl_yaml_file_body(Create, "myview", "'/path/to/def.yaml'", None) returns Ok(Some("__SV_YAML_FILE__/path/to/def.yaml\0 0\0myview\0"))
    - Test: rewrite_ddl_yaml_file_body(CreateOrReplace, "v", "'/f.yaml'", Some("a comment".into())) returns sentinel with kind=1 and comment field
    - Test: rewrite_ddl_yaml_file_body(CreateIfNotExists, "v", "'/f.yaml'", None) returns sentinel with kind=2
    - Test: rewrite_ddl_yaml_file_body(Create, "v", "''", None) returns Err with "File path cannot be empty"
    - Test: rewrite_ddl_yaml_file_body(Create, "v", "'/f.yaml' extra stuff", None) returns Err with "Unexpected content after file path"
    - Test: validate_and_rewrite("CREATE SEMANTIC VIEW v FROM YAML FILE '/test.yaml'") returns sentinel starting with "__SV_YAML_FILE__"
    - Test: validate_and_rewrite("CREATE SEMANTIC VIEW v from yaml file '/test.yaml'") returns sentinel (case-insensitive)
    - Test: validate_and_rewrite with FROM YAML $$...$$ still works (regression check)
    - Test: error message for non-AS/FROM-YAML includes "FROM YAML FILE" usage hint
  </behavior>
  <action>
Add three items to src/parse.rs:

**1. `extract_single_quoted()` function** (add after `extract_dollar_quoted`, around line 1169):

```rust
/// Extract a single-quoted string literal from the input.
///
/// Returns `(unescaped_content, bytes_consumed)` on success.
/// Handles SQL-standard escaped single quotes (`''` -> `'`).
fn extract_single_quoted(input: &str) -> Result<(String, usize), ParseError> {
    if !input.starts_with('\'') {
        return Err(ParseError {
            message: "Expected single-quoted file path after FILE keyword. \
                      Use: FROM YAML FILE '/path/to/file.yaml'"
                .to_string(),
            position: None,
        });
    }
    let mut result = String::new();
    let mut i = 1; // skip opening quote
    let bytes = input.as_bytes();
    while i < bytes.len() {
        if bytes[i] == b'\'' {
            if i + 1 < bytes.len() && bytes[i + 1] == b'\'' {
                result.push('\'');
                i += 2;
            } else {
                return Ok((result, i + 1));
            }
        } else {
            result.push(bytes[i] as char);
            i += 1;
        }
    }
    Err(ParseError {
        message: "Unterminated file path string (missing closing single quote)".to_string(),
        position: None,
    })
}
```

**2. `rewrite_ddl_yaml_file_body()` function** (add after `extract_single_quoted`):

```rust
/// Generate a sentinel string for C++ shim to intercept and read the file.
///
/// Sentinel format: `__SV_YAML_FILE__<path>\x00<kind>\x00<name>\x00<comment>`
/// The C++ shim reads the file via `read_text()`, reconstructs as inline YAML,
/// and re-invokes `sv_rewrite_ddl_rust`.
fn rewrite_ddl_yaml_file_body(
    kind: DdlKind,
    name: &str,
    file_text: &str,
    view_comment: Option<String>,
) -> Result<Option<String>, ParseError> {
    let (file_path, consumed) = extract_single_quoted(file_text)?;

    let trailing = file_text[consumed..].trim();
    if !trailing.is_empty() {
        return Err(ParseError {
            message: format!("Unexpected content after file path: '{trailing}'"),
            position: None,
        });
    }

    if file_path.is_empty() {
        return Err(ParseError {
            message: "File path cannot be empty. \
                      Use: FROM YAML FILE '/path/to/file.yaml'"
                .to_string(),
            position: None,
        });
    }

    let kind_num = match kind {
        DdlKind::Create => 0,
        DdlKind::CreateOrReplace => 1,
        DdlKind::CreateIfNotExists => 2,
        _ => unreachable!("rewrite_ddl_yaml_file_body only called for CREATE forms"),
    };
    let comment = view_comment.as_deref().unwrap_or("");
    Ok(Some(format!(
        "__SV_YAML_FILE__{file_path}\x00{kind_num}\x00{name}\x00{comment}"
    )))
}
```

**3. Modify `validate_create_body()`** (lines 1055-1065) -- replace the existing FROM YAML block with a two-branch version:

Replace:
```rust
// --- FROM YAML body path (Phase 52) ---
let is_yaml_body = after_name_trimmed
    .get(..9)
    .is_some_and(|s| s.eq_ignore_ascii_case("FROM YAML"))
    && (after_name_trimmed.len() == 9
        || after_name_trimmed.as_bytes()[9].is_ascii_whitespace());
if is_yaml_body {
    let yaml_text = after_name_trimmed[9..].trim_start();
    return rewrite_ddl_yaml_body(kind, name, yaml_text, view_comment);
}
// --- End FROM YAML body path ---
```

With:
```rust
// --- FROM YAML body path (Phase 52 + Phase 53) ---
let is_yaml_body = after_name_trimmed
    .get(..9)
    .is_some_and(|s| s.eq_ignore_ascii_case("FROM YAML"))
    && (after_name_trimmed.len() == 9
        || after_name_trimmed.as_bytes()[9].is_ascii_whitespace());
if is_yaml_body {
    let yaml_text = after_name_trimmed[9..].trim_start();

    // Phase 53: FROM YAML FILE '/path' sub-branch
    let is_file = yaml_text
        .get(..4)
        .is_some_and(|s| s.eq_ignore_ascii_case("FILE"))
        && (yaml_text.len() == 4
            || yaml_text.as_bytes()[4].is_ascii_whitespace());
    if is_file {
        let file_text = yaml_text[4..].trim_start();
        return rewrite_ddl_yaml_file_body(kind, name, file_text, view_comment);
    }

    // Phase 52: FROM YAML $$...$$ inline sub-branch (existing)
    return rewrite_ddl_yaml_body(kind, name, yaml_text, view_comment);
}
// --- End FROM YAML body path ---
```

**4. Update error message** (line 1070) -- change:
```
"Expected 'AS' or 'FROM YAML' after view name. Use: CREATE SEMANTIC VIEW name AS TABLES (...) DIMENSIONS (...) METRICS (...) or: CREATE SEMANTIC VIEW name FROM YAML $$ ... $$"
```
to:
```
"Expected 'AS' or 'FROM YAML' after view name. Use: CREATE SEMANTIC VIEW name AS TABLES (...) DIMENSIONS (...) METRICS (...) or: CREATE SEMANTIC VIEW name FROM YAML $$ ... $$ or: CREATE SEMANTIC VIEW name FROM YAML FILE '/path/to/file.yaml'"
```

**5. Add unit tests** in the `#[cfg(test)] mod tests` block at the bottom of parse.rs. Add these tests near the existing yaml-related tests:

- `test_extract_single_quoted_basic`: normal path extraction
- `test_extract_single_quoted_escaped`: `''` -> `'` handling
- `test_extract_single_quoted_empty`: empty string `''` returns empty content
- `test_extract_single_quoted_no_quote`: error when no opening quote
- `test_extract_single_quoted_unterminated`: error for unclosed quote
- `test_rewrite_ddl_yaml_file_body_create`: sentinel with kind=0
- `test_rewrite_ddl_yaml_file_body_replace`: sentinel with kind=1
- `test_rewrite_ddl_yaml_file_body_if_not_exists`: sentinel with kind=2
- `test_rewrite_ddl_yaml_file_body_with_comment`: comment field in sentinel
- `test_rewrite_ddl_yaml_file_body_empty_path`: rejected with error
- `test_rewrite_ddl_yaml_file_body_trailing_content`: rejected with error
- `test_validate_and_rewrite_yaml_file`: FROM YAML FILE produces sentinel
- `test_validate_and_rewrite_yaml_file_case_insensitive`: case-insensitive detection
- `test_validate_and_rewrite_yaml_inline_still_works`: FROM YAML $$ still works (regression)
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test parse::tests::yaml_file -- --nocapture 2>&1 | tail -20 && cargo test parse::tests::test_extract_single_quoted -- --nocapture 2>&1 | tail -10 && cargo test 2>&1 | tail -5</automated>
  </verify>
  <acceptance_criteria>
    - src/parse.rs contains `fn extract_single_quoted(input: &str) -> Result<(String, usize), ParseError>`
    - src/parse.rs contains `fn rewrite_ddl_yaml_file_body(`
    - src/parse.rs contains `"__SV_YAML_FILE__"` sentinel prefix in rewrite_ddl_yaml_file_body
    - src/parse.rs contains `s.eq_ignore_ascii_case("FILE")` in validate_create_body
    - src/parse.rs contains `return rewrite_ddl_yaml_file_body(kind, name, file_text, view_comment);`
    - src/parse.rs error message contains `FROM YAML FILE '/path/to/file.yaml'`
    - src/parse.rs contains `fn test_extract_single_quoted_basic` unit test
    - src/parse.rs contains `fn test_rewrite_ddl_yaml_file_body_create` unit test
    - src/parse.rs contains `fn test_validate_and_rewrite_yaml_file` unit test
    - `cargo test` exits 0 with all tests passing (including existing Phase 52 YAML tests)
  </acceptance_criteria>
  <done>
    - extract_single_quoted() extracts file paths from single-quoted strings with '' escape handling
    - rewrite_ddl_yaml_file_body() produces __SV_YAML_FILE__ sentinel encoding path, kind, name, comment
    - validate_create_body() detects FROM YAML FILE as a sub-branch of FROM YAML
    - Error message updated to include FROM YAML FILE usage hint
    - 14+ unit tests pass covering extraction, sentinel generation, and DDL detection
    - All existing tests (including Phase 52 YAML inline tests) continue to pass
  </done>
</task>

<task type="auto">
  <name>Task 2: Add C++ sentinel interception, file reading via read_text(), and sqllogictest integration tests</name>
  <files>cpp/src/shim.cpp, test/sql/phase53_yaml_file.test, test/sql/TEST_LIST</files>
  <read_first>
    - cpp/src/shim.cpp (full file -- understand sv_ddl_bind structure, sv_ddl_conn usage, sv_rewrite_ddl_rust FFI call, buffer sizes)
    - src/parse.rs (verify sentinel format from Task 1 is committed)
    - test/sql/phase52_yaml_ddl.test (pattern reference for sqllogictest structure)
    - test/sql/TEST_LIST (current entries to append to)
    - .planning/phases/53-yaml-file-loading/53-RESEARCH.md (Pattern 4, Pitfall 3, Pitfall 4, Pitfall 6 for C++ implementation details)
  </read_first>
  <action>
**1. Modify `sv_ddl_bind` in cpp/src/shim.cpp** -- insert sentinel interception between the `sv_rewrite_ddl_rust` call (line 146-149) and the existing `duckdb_query` execution (line 157). After `if (rc != 0) { throw ... }` at line 152, and before `duckdb_result result;` at line 156, insert:

```cpp
// Phase 53: Intercept YAML FILE sentinel and read file before final rewrite
string sql(sql_str.c_str());

if (sql.rfind("__SV_YAML_FILE__", 0) == 0) {
    // Parse sentinel: __SV_YAML_FILE__<path>\0<kind>\0<name>\0<comment>
    auto payload = sql.substr(16);
    vector<string> parts;
    size_t pos = 0;
    for (int i = 0; i < 3; i++) {
        auto nul = payload.find('\0', pos);
        if (nul == string::npos) {
            parts.push_back(payload.substr(pos));
            break;
        }
        parts.push_back(payload.substr(pos, nul - pos));
        pos = nul + 1;
    }
    if (pos < payload.size()) {
        parts.push_back(payload.substr(pos));
    }

    if (parts.size() < 3) {
        throw BinderException("Internal error: malformed YAML FILE sentinel");
    }

    auto &file_path = parts[0];
    auto &kind_str = parts[1];
    auto &view_name = parts[2];
    auto comment = parts.size() > 3 ? parts[3] : string();

    // Step 1: Read file via read_text() -- SQL-escape the file path
    string escaped_path;
    for (char c : file_path) {
        escaped_path += c;
        if (c == '\'') escaped_path += '\'';
    }
    string read_sql = "SELECT content FROM read_text('" + escaped_path + "')";

    duckdb_result file_result;
    if (duckdb_query(sv_ddl_conn, read_sql.c_str(), &file_result) != DuckDBSuccess) {
        auto err_ptr = duckdb_result_error(&file_result);
        string err_msg = err_ptr ? string(err_ptr) : "File read failed";
        duckdb_destroy_result(&file_result);
        throw BinderException("FROM YAML FILE failed: %s", err_msg);
    }

    auto row_count = duckdb_row_count(&file_result);
    if (row_count == 0) {
        duckdb_destroy_result(&file_result);
        throw BinderException("FROM YAML FILE: no content returned from '%s'",
                              file_path);
    }

    // SELECT content FROM read_text(...) projects content as column 0
    char *content_ptr = duckdb_value_varchar(&file_result, 0, 0);
    string yaml_content = content_ptr ? string(content_ptr) : "";
    if (content_ptr) duckdb_free(content_ptr);
    duckdb_destroy_result(&file_result);

    // Step 2: Reconstruct query as inline YAML with tagged dollar-quote
    // Tagged delimiter avoids collision with $$ in YAML content
    string kind_prefix;
    if (kind_str == "0") kind_prefix = "CREATE SEMANTIC VIEW ";
    else if (kind_str == "1") kind_prefix = "CREATE OR REPLACE SEMANTIC VIEW ";
    else kind_prefix = "CREATE SEMANTIC VIEW IF NOT EXISTS ";

    string reconstructed = kind_prefix + view_name;
    if (!comment.empty()) {
        string escaped_comment;
        for (char c : comment) {
            escaped_comment += c;
            if (c == '\'') escaped_comment += '\'';
        }
        reconstructed += " COMMENT = '" + escaped_comment + "'";
    }
    reconstructed += " FROM YAML $__sv_file$" + yaml_content + "$__sv_file$";

    // Step 3: Re-invoke Rust rewrite with the inline YAML query
    // Allocate buffer large enough for potentially large YAML content
    size_t rewrite_buf_size = std::max(size_t(65536), yaml_content.size() * 2 + 4096);
    std::string rewrite_sql(rewrite_buf_size, '\0');
    memset(error_buf, 0, sizeof(error_buf));

    rc = sv_rewrite_ddl_rust(
        reconstructed.c_str(), reconstructed.size(),
        rewrite_sql.data(), rewrite_sql.size(),
        error_buf, sizeof(error_buf));

    if (rc != 0) {
        throw BinderException("Semantic view DDL failed: %s", error_buf);
    }
    sql = string(rewrite_sql.c_str());
}
```

Then change the existing execution code (line 156-162) from:
```cpp
duckdb_result result;
if (duckdb_query(sv_ddl_conn, sql_str.c_str(), &result) != DuckDBSuccess) {
```
to:
```cpp
duckdb_result result;
if (duckdb_query(sv_ddl_conn, sql.c_str(), &result) != DuckDBSuccess) {
```

Note the change from `sql_str.c_str()` to `sql.c_str()` -- this ensures the sentinel-rewritten SQL is used when applicable, and the original rewritten SQL is used otherwise (since `sql` was initialized from `sql_str.c_str()` at the start of the sentinel block, or falls through with the original value if the sentinel check does not match).

**IMPORTANT:** The `string sql(sql_str.c_str());` declaration and the sentinel `if` block must be inserted BEFORE the `duckdb_result result;` line. If the sentinel prefix is NOT detected, `sql` still contains the original `sql_str` content, so the `duckdb_query` call works correctly for both paths.

**2. Create test/sql/phase53_yaml_file.test:**

```
# Phase 53: YAML file loading integration tests
# Tests for CREATE SEMANTIC VIEW ... FROM YAML FILE '/path'

require semantic_views

# Setup: create backing tables
statement ok
CREATE TABLE p53_orders (id INTEGER PRIMARY KEY, amount DOUBLE, region VARCHAR);

statement ok
INSERT INTO p53_orders VALUES (1, 100.0, 'East'), (2, 200.0, 'West');

# Write a YAML definition file to __TEST_DIR__
statement ok
COPY (SELECT 'base_table: p53_orders
tables:
  - alias: o
    table: p53_orders
    pk_columns:
      - id
dimensions:
  - name: region
    expr: o.region
    source_table: o
metrics:
  - name: total_amount
    expr: SUM(o.amount)
    source_table: o' AS content)
TO '__TEST_DIR__/p53_test.yaml' (FORMAT CSV, HEADER FALSE, QUOTE '');

# ---------------------------------------------------------------
# YAML-02: CREATE SEMANTIC VIEW from YAML file
# ---------------------------------------------------------------

statement ok
CREATE SEMANTIC VIEW p53_from_file FROM YAML FILE '__TEST_DIR__/p53_test.yaml'

# Verify the view is queryable
query TR rowsort
SELECT region, total_amount FROM semantic_view('p53_from_file', dimensions := ['region'], metrics := ['total_amount'])
----
East	100.0
West	200.0

# Verify metrics-only query works
query R
SELECT total_amount FROM semantic_view('p53_from_file', metrics := ['total_amount'])
----
300.0

# ---------------------------------------------------------------
# YAML-02: CREATE OR REPLACE from YAML file
# ---------------------------------------------------------------

# Write a different YAML definition
statement ok
COPY (SELECT 'base_table: p53_orders
tables:
  - alias: o
    table: p53_orders
    pk_columns:
      - id
dimensions:
  - name: region
    expr: o.region
    source_table: o
metrics:
  - name: order_count
    expr: COUNT(o.id)
    source_table: o' AS content)
TO '__TEST_DIR__/p53_test_v2.yaml' (FORMAT CSV, HEADER FALSE, QUOTE '');

statement ok
CREATE OR REPLACE SEMANTIC VIEW p53_from_file FROM YAML FILE '__TEST_DIR__/p53_test_v2.yaml'

# Verify the view was replaced (new metric name)
query TR rowsort
SELECT region, order_count FROM semantic_view('p53_from_file', dimensions := ['region'], metrics := ['order_count'])
----
East	1
West	1

# ---------------------------------------------------------------
# YAML-02: CREATE IF NOT EXISTS from YAML file (no-op)
# ---------------------------------------------------------------

statement ok
CREATE SEMANTIC VIEW IF NOT EXISTS p53_from_file FROM YAML FILE '__TEST_DIR__/p53_test.yaml'

# Verify original view (with order_count) is still in place
query TR rowsort
SELECT region, order_count FROM semantic_view('p53_from_file', dimensions := ['region'], metrics := ['order_count'])
----
East	1
West	1

# ---------------------------------------------------------------
# YAML-02: COMMENT = '...' with FROM YAML FILE
# ---------------------------------------------------------------

statement ok
CREATE SEMANTIC VIEW p53_commented COMMENT = 'Loaded from file' FROM YAML FILE '__TEST_DIR__/p53_test.yaml'

query R
SELECT total_amount FROM semantic_view('p53_commented', metrics := ['total_amount'])
----
300.0

# ---------------------------------------------------------------
# YAML-02: Case-insensitive FROM YAML FILE detection
# ---------------------------------------------------------------

statement ok
CREATE SEMANTIC VIEW p53_lowercase from yaml file '__TEST_DIR__/p53_test.yaml'

query R
SELECT total_amount FROM semantic_view('p53_lowercase', metrics := ['total_amount'])
----
300.0

# ---------------------------------------------------------------
# YAML-07: Security enforcement -- enable_external_access=false
# ---------------------------------------------------------------

statement ok
SET enable_external_access = false

statement error
CREATE SEMANTIC VIEW p53_blocked FROM YAML FILE '__TEST_DIR__/p53_test.yaml'
----
FROM YAML FILE failed

# Re-enable for remaining tests
statement ok
SET enable_external_access = true

# ---------------------------------------------------------------
# Error cases: file not found
# ---------------------------------------------------------------

statement error
CREATE SEMANTIC VIEW p53_missing FROM YAML FILE '__TEST_DIR__/nonexistent_file.yaml'
----
FROM YAML FILE failed

# ---------------------------------------------------------------
# Error cases: missing file path quotes
# ---------------------------------------------------------------

statement error
CREATE SEMANTIC VIEW p53_no_quotes FROM YAML FILE /path/to/file.yaml
----
Expected single-quoted file path

# ---------------------------------------------------------------
# Regression: inline YAML still works after FILE branch added
# ---------------------------------------------------------------

statement ok
CREATE SEMANTIC VIEW p53_inline_regression FROM YAML $$
base_table: p53_orders
tables:
  - alias: o
    table: p53_orders
    pk_columns:
      - id
dimensions:
  - name: region
    expr: o.region
    source_table: o
metrics:
  - name: total_amount
    expr: SUM(o.amount)
    source_table: o
$$

query R
SELECT total_amount FROM semantic_view('p53_inline_regression', metrics := ['total_amount'])
----
300.0

# ---------------------------------------------------------------
# Cleanup
# ---------------------------------------------------------------

statement ok
DROP SEMANTIC VIEW p53_from_file

statement ok
DROP SEMANTIC VIEW p53_commented

statement ok
DROP SEMANTIC VIEW p53_lowercase

statement ok
DROP SEMANTIC VIEW p53_inline_regression

statement ok
DROP TABLE p53_orders
```

**3. Update test/sql/TEST_LIST** -- append `test/sql/phase53_yaml_file.test` as a new line at the end.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build 2>&1 | tail -5 && just test-sql 2>&1 | tail -10 && just test-all 2>&1 | tail -10</automated>
  </verify>
  <acceptance_criteria>
    - cpp/src/shim.cpp contains `__SV_YAML_FILE__` string literal in sv_ddl_bind function
    - cpp/src/shim.cpp contains `read_text(` for file reading
    - cpp/src/shim.cpp contains `FROM YAML FILE failed` error message wrapping
    - cpp/src/shim.cpp contains `$__sv_file$` tagged dollar-quote delimiter
    - cpp/src/shim.cpp contains `std::max(size_t(65536), yaml_content.size() * 2 + 4096)` for dynamic buffer sizing
    - cpp/src/shim.cpp contains `sv_rewrite_ddl_rust` re-invocation call inside the sentinel block
    - cpp/src/shim.cpp uses `sql.c_str()` (not `sql_str.c_str()`) for the final duckdb_query execution
    - test/sql/phase53_yaml_file.test contains `FROM YAML FILE '__TEST_DIR__/p53_test.yaml'`
    - test/sql/phase53_yaml_file.test contains `SET enable_external_access = false`
    - test/sql/phase53_yaml_file.test contains `FROM YAML FILE failed` expected error
    - test/sql/phase53_yaml_file.test contains `CREATE OR REPLACE SEMANTIC VIEW p53_from_file FROM YAML FILE`
    - test/sql/phase53_yaml_file.test contains `CREATE SEMANTIC VIEW IF NOT EXISTS p53_from_file FROM YAML FILE`
    - test/sql/TEST_LIST contains `test/sql/phase53_yaml_file.test`
    - `just build` exits 0 (C++ compiles without errors)
    - `just test-sql` exits 0 (all sqllogictest files pass including new phase53 tests)
    - `just test-all` exits 0 (full quality gate passes)
  </acceptance_criteria>
  <done>
    - C++ shim intercepts __SV_YAML_FILE__ sentinel, reads file via read_text() on sv_ddl_conn, reconstructs as inline YAML with tagged dollar-quote, and re-invokes sv_rewrite_ddl_rust
    - enable_external_access=false blocks FROM YAML FILE via read_text() error propagation
    - Dynamic buffer sizing handles large YAML files (up to 1MB size cap)
    - File path SQL-escaped to prevent injection
    - 13+ sqllogictest integration tests cover: basic file load, CREATE OR REPLACE, IF NOT EXISTS, COMMENT, case-insensitive detection, security enforcement, file-not-found, missing quotes, inline YAML regression
    - Full quality gate (just test-all) passes
  </done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| User DDL -> Rust parse | Untrusted file paths in FROM YAML FILE enter the parser |
| Rust sentinel -> C++ shim | File path crosses FFI boundary via sentinel protocol |
| C++ shim -> DuckDB read_text() | File path embedded in SQL query for file reading |
| File content -> YAML parser | Untrusted YAML content from external file enters deserializer |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-53-01 | Tampering | cpp/src/shim.cpp read_text query | mitigate | SQL-escape file path (double single quotes) before embedding in `SELECT content FROM read_text('...')` query. extract_single_quoted handles '' unescaping on input side; C++ re-escapes for read_text query. |
| T-53-02 | Information Disclosure | cpp/src/shim.cpp read_text | mitigate | DuckDB's `read_text()` enforces `enable_external_access` GLOBAL setting. When false, all file reads blocked. Integration test verifies: `SET enable_external_access = false` then `FROM YAML FILE` fails with error. |
| T-53-03 | Information Disclosure | File path traversal | accept | DuckDB's file access controls (enable_external_access, allowed_directories) apply to read_text(). Extension does not add custom path validation -- DuckDB is the security boundary. Path traversal to /etc/passwd etc. is blocked when enable_external_access=false. When true, user has opted into file access. |
| T-53-04 | Denial of Service | Large YAML file | mitigate | from_yaml_with_size_cap() enforces 1MB size limit before YAML parsing begins. read_text() reads the full file, but DuckDB's built-in limits apply. The 1MB YAML cap prevents anchor/alias bomb expansion. |
| T-53-05 | Tampering | Dollar-quote collision | mitigate | Use tagged dollar-quote `$__sv_file$...$__sv_file$` when reconstructing inline YAML query. Double-underscore prefix is extremely unlikely in user YAML content. extract_dollar_quoted() already supports tagged delimiters. |
| T-53-06 | Tampering | Sentinel prefix collision | accept | Sentinel prefix `__SV_YAML_FILE__` cannot be produced by any other code path -- inline YAML rewrite always produces `SELECT * FROM create_semantic_view_from_json(...)`. Low risk of false positive. |
</threat_model>

<verification>
1. `cargo test` -- all Rust unit tests pass including new yaml_file tests and existing yaml tests
2. `just build && just test-sql` -- all sqllogictest integration tests pass including new phase53_yaml_file.test
3. `just test-all` -- full quality gate (Rust tests + proptests + sqllogictest + DuckLake CI)
4. Manual spot check: `CREATE SEMANTIC VIEW v FROM YAML FILE '/path/to/valid.yaml'` creates queryable view
5. Security check: `SET enable_external_access = false; CREATE SEMANTIC VIEW v FROM YAML FILE '/path'` is rejected
</verification>

<success_criteria>
- YAML-02: `CREATE SEMANTIC VIEW name FROM YAML FILE '/path/to/file.yaml'` creates a queryable semantic view from the file contents
- YAML-02: CREATE OR REPLACE and IF NOT EXISTS modifiers work with FROM YAML FILE
- YAML-07: `SET enable_external_access = false` blocks FROM YAML FILE with a security error
- All existing tests pass (zero regressions)
- `just test-all` exits 0
</success_criteria>

<output>
After completion, create `.planning/phases/53-yaml-file-loading/53-01-SUMMARY.md`
</output>
