Recursive CTE Hierarchy Query

You are a SQL database engineer specializing in hierarchical data modeling.

Write a **recursive common table expression (CTE)** that traverses a parent-child hierarchy table and returns:

1. **All columns from the source table** plus computed columns for:
   - `depth` (integer, starting at 0 for roots)
   - `path` (string showing the full ancestry, e.g. "/root/parent/child" or delimited IDs)

2. **Cycle protection** via a depth limit (default MAX_DEPTH = 100) that prevents infinite loops if the data contains cycles.

3. **Two query patterns**:
   - Retrieve the **entire hierarchy** (all roots and their descendants)
   - Retrieve a **single subtree** starting from a given node ID

**Input schema:**

{{schema}}

**Requirements:**

- Use standard SQL (PostgreSQL, SQL Server, MySQL 8+, or SQLite 3.8.3+) recursive CTE syntax: `WITH RECURSIVE cte_name AS (anchor_query UNION ALL recursive_query)`
- The anchor member selects root nodes (WHERE parent_id IS NULL or equivalent)
- The recursive member joins the CTE to the table on parent-child relationship
- Increment depth in the recursive member; halt recursion when depth >= MAX_DEPTH
- Build the path by concatenating parent path + current node identifier (use appropriate delimiter or array type for the dialect)
- Include inline comments explaining the anchor, recursive step, cycle guard, and how to filter for a subtree
- Provide both queries: one `SELECT * FROM cte` for full tree, one `SELECT * FROM cte WHERE <starting condition>` for subtree
- If the dialect supports it, show cycle detection using the built-in CYCLE clause as an alternative

**Output only:**

- The complete, runnable CTE definition
- The two SELECT statements (full tree and subtree)
- A brief comment block at the top stating the SQL dialect and any version requirements

Do not include sample data or table creation DDL—focus solely on the recursive query logic.