# `QuackDB.DDL`
[🔗](https://github.com/elixir-vibe/quackdb/blob/main/lib/quack_db/ddl.ex#L1)

Small DuckDB DDL SQL builders.

These helpers return SQL iodata for common analytical setup tasks such as
temporary tables in tests or notebooks. They are not an Ecto migration layer;
execute the generated SQL with `QuackDB.query/4` or `Repo.query/3`.

    QuackDB.DDL.create_table("events",
      [
        id: :integer,
        name: :varchar,
        payload: :json,
        occurred_at: :timestamp
      ],
      temporary: true,
      if_not_exists: true
    )

# `column`

```elixir
@type column() ::
  {atom() | String.t(), column_type() | keyword()}
  | {atom() | String.t(), column_type(), keyword()}
```

# `column_type`

```elixir
@type column_type() :: QuackDB.Type.spec()
```

# `create_table_option`

```elixir
@type create_table_option() ::
  {:temporary, boolean()}
  | {:if_not_exists, boolean()}
  | {:or_replace, boolean()}
  | {:as, iodata()}
  | QuackDB.DDL.Check.t()
```

# `attach`

```elixir
@spec attach(String.t(), atom() | String.t(), keyword()) :: iodata()
```

Builds an `ATTACH` statement that attaches `path` as the database `name`.

Options become DuckDB attach options, such as `:type`, `:read_only`, or a
DuckLake `:data_path`. Atoms are written as keywords, other values as
literals:

    alias QuackDB.DDL

    DDL.attach("ducklake:metadata.ducklake", :lake, data_path: "s3://bucket/lake/")
    DDL.attach("s3://bucket/events", :events, type: :delta)
    DDL.attach("s3://bucket/events", :events_v5, type: :delta, version: 5)

Supports `if_not_exists: true`.

# `check`
*macro* 

Builds one inline CHECK constraint from an expression.

Bare identifiers refer to columns. Pin runtime values with `^`; use
`field("column name")` or `field(^name)` for other column names. Supports
comparisons (`==`, `!=`, `>`, `>=`, `<`, `<=`), `and`, `or`, `not`, and
`is_nil/1`. Arithmetic, arbitrary function calls, and raw SQL are rejected.

    import QuackDB.DDL, only: [create_table: 3, check: 1]

    maximum = 100
    create_table(:events, [score: :integer],
      check(score >= 0 and score < ^maximum)
    )

Return values are composable: pass a single check or a list of checks as
`create_table/3`'s third argument. Table options can follow checks in that list:
`[check(score >= 0), temporary: true]`.

# `create_schema`

```elixir
@spec create_schema(
  QuackDB.SQL.Fragment.schema(),
  keyword()
) :: iodata()
```

Builds a `CREATE SCHEMA` statement.

A list names a nested schema, which DuckDB 2.0 creates inside its parent.
In an Ecto migration:

    alias QuackDB.DDL

    def up do
      execute DDL.create_schema(:finance)
      execute DDL.create_schema([:finance, :reports])
      execute DDL.create_table({[:finance, :reports], :ledger}, id: :integer)
    end

Supports `if_not_exists: true`.

# `create_sequence`

```elixir
@spec create_sequence(
  QuackDB.SQL.Fragment.table(),
  keyword()
) :: iodata()
```

Builds a standalone `CREATE SEQUENCE` statement.

Names may be atoms, strings, or `{prefix, name}` tuples. Supported options are
`:start` (integer), `:increment` (nonzero integer), and `:if_not_exists` (boolean).
Other options raise rather than being silently ignored. Sequences can have
gaps; do not use them when a gapless transactional counter is required.

    alias QuackDB.{DDL, Sequence}

    QuackDB.query!(conn, DDL.create_sequence(:task_keys, start: 1))
    Sequence.next_values(conn, :task_keys, 3)
    #=> [1, 2, 3]

# `create_table`

```elixir
@spec create_table(module()) :: iodata()
```

Builds a `CREATE TABLE` statement from an Ecto schema module.

# `create_table`

```elixir
@spec create_table(
  module() | String.t() | atom(),
  QuackDB.DDL.Check.t() | [create_table_option()] | [column()]
) :: iodata()
```

# `create_table`

```elixir
@spec create_table(
  String.t() | atom(),
  module() | [column()],
  QuackDB.DDL.Check.t() | [create_table_option()]
) :: iodata()
```

Builds a `CREATE TABLE` statement.

    QuackDB.DDL.create_table("events", id: :integer, name: :varchar)
    QuackDB.DDL.create_table("events", [id: :integer], temporary: true)
    QuackDB.DDL.create_table("temp_events", EventSchema, temporary: true)

Pass a single `check/1` value or a list for inline constraints:

    import QuackDB.DDL, only: [create_table: 3, check: 1]

    create_table(:events, [{:score, :integer, null: false}],
      [check(score >= 0), check(score < 100)]
    )

Checks also work with schema-derived columns. Column names are quoted and
values use the SQL literal codec, not raw predicate strings. CHECK permits
NULL when its expression is unknown; use `null: false` to require a value.
Checks cannot be combined with `:as`. DuckDB does not preserve supplied CHECK
names reliably, so this API does not name checks or promise Ecto changeset
constraint mapping. ALTER ADD/DROP CONSTRAINT remains unsupported.

Pass `:as` to build `CREATE TABLE AS` from iodata or an Ecto query without pinned params:

    QuackDB.DDL.create_table("docs", as: query, temporary: true)
    QuackDB.DDL.create_table("docs", as: query, or_replace: true)

# `create_trigger`

```elixir
@spec create_trigger(
  QuackDB.SQL.Fragment.identifier_name(),
  QuackDB.SQL.Fragment.table(),
  iodata(),
  keyword()
) :: iodata()
```

Builds a DuckDB 2.0 `CREATE TRIGGER` statement that runs `statement`, a SQL
statement such as an `INSERT` or `UPDATE`, when `event` changes `table`.

Triggers are schema, so they usually live in an Ecto migration. The body is
DuckDB SQL because it reads the transition table:

    alias QuackDB.DDL

    def up do
      execute DDL.create_trigger(:events_count, :events,
        "UPDATE event_stats SET total = total + (SELECT count(*) FROM inserted)",
        event: :insert,
        referencing: [new_table: :inserted]
      )
    end

    def down, do: execute(DDL.drop_trigger(:events_count, :events))

Options:

  * `:event` (required) - `:insert`, `:delete`, `:update`, or
    `{:update, columns}` for `UPDATE OF columns`.
  * `:timing` - `:after` (default) or `:before`.
  * `:for_each` - `:statement` (default) or `:row`. Row triggers refer to the
    changed row as `NEW` and `OLD`.
  * `:referencing` - transition tables as `[old_table: alias, new_table: alias]`.
  * `:or_replace` and `:if_not_exists` - booleans.

DuckDB 2.0 does not run `BEFORE ... FOR EACH ROW` or `UPDATE ... FOR EACH ROW`
triggers yet, and a table cannot mix row and statement triggers; the server
rejects those.

# `detach`

```elixir
@spec detach(
  atom() | String.t(),
  keyword()
) :: iodata()
```

Builds a `DETACH` statement. Supports `if_exists: true`.

# `drop_schema`

```elixir
@spec drop_schema(
  QuackDB.SQL.Fragment.schema(),
  keyword()
) :: iodata()
```

Builds a `DROP SCHEMA` statement. Supports `if_exists: true` and `cascade: true`.

# `drop_sequence`

```elixir
@spec drop_sequence(
  QuackDB.SQL.Fragment.table(),
  keyword()
) :: iodata()
```

Builds a `DROP SEQUENCE` statement, optionally with `if_exists: true`.

# `drop_table`

```elixir
@spec drop_table(
  String.t() | atom(),
  keyword()
) :: iodata()
```

Builds a `DROP TABLE` statement.

# `drop_trigger`

```elixir
@spec drop_trigger(
  QuackDB.SQL.Fragment.identifier_name(),
  QuackDB.SQL.Fragment.table(),
  keyword()
) :: iodata()
```

Builds a `DROP TRIGGER name ON table` statement, optionally with `if_exists: true`.

---

*Consult [api-reference.md](api-reference.md) for complete listing*