# `Localize.Unit.DDL`
[🔗](https://github.com/elixir-localize/localize_sql/blob/v1.0.1/lib/localize/unit/ddl.ex#L1)

Generates the SQL that stores `t:Localize.Unit.t/0` values in PostgreSQL.

A unit pairs a unit name with a decimal value — `2.5` metres, `100`
kilometres — and is stored as a tagged decimal composite type so that
the database can add and aggregate units while refusing to add metres
to feet. This module returns the SQL creating those types and their
aggregate functions; it is a thin wrapper over
`Localize.Ecto.TaggedDecimal.DDL`, supplying the unit specifications.

Two composite types are created. `cldr_unit(unit, value)` stores a unit
and its value; `cldr_unit_with_usage(unit, value, usage)` additionally
preserves the unit's usage (`:road` versus `:rainfall`, say) across the
round trip. Each has `sum`, `min`, `max` and `avg` aggregates that
raise on a unit-name mismatch.

### Migrations

The generated statements install into a migration with `execute/1` and
`execute_each/1`, which `mix localize.unit.gen.migration` does
automatically:

    defmodule MyApp.Repo.Migrations.AddCldrUnit do
      use Ecto.Migration

      def up do
        Localize.Unit.DDL.execute_each(Localize.Unit.DDL.create_cldr_unit())
        Localize.Unit.DDL.execute_each(Localize.Unit.DDL.define_aggregate_functions())
      end

      def down do
        Localize.Unit.DDL.execute_each(Localize.Unit.DDL.drop_aggregate_functions())
        Localize.Unit.DDL.execute_each(Localize.Unit.DDL.drop_cldr_unit())
      end
    end

# `db_type`

```elixir
@type db_type() :: :postgres
```

The target database. Only PostgreSQL is supported; the argument exists
so the API can grow without changing.

# `create_cldr_unit`

```elixir
@spec create_cldr_unit(db_type()) :: String.t()
```

Returns the SQL creating the `cldr_unit` and `cldr_unit_with_usage`
composite types.

### Arguments

* `db_type` is the target database. Only `:postgres` is supported. The
  default is `:postgres`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> Localize.Unit.DDL.create_cldr_unit() =~ "CREATE TYPE public.cldr_unit AS"
    true

# `define_aggregate_functions`

```elixir
@spec define_aggregate_functions(db_type()) :: String.t()
```

Returns the SQL defining the `sum`, `min`, `max` and `avg` aggregates
for both unit composite types.

### Arguments

* `db_type` is the target database. Only `:postgres` is supported. The
  default is `:postgres`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> Localize.Unit.DDL.define_aggregate_functions() =~ "CREATE OR REPLACE AGGREGATE sum(cldr_unit)"
    true

# `drop_aggregate_functions`

```elixir
@spec drop_aggregate_functions(db_type()) :: String.t()
```

Returns the SQL dropping the aggregates and their functions for both
unit composite types.

### Arguments

* `db_type` is the target database. Only `:postgres` is supported. The
  default is `:postgres`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> Localize.Unit.DDL.drop_aggregate_functions() =~ "DROP AGGREGATE IF EXISTS avg(cldr_unit);"
    true

# `drop_cldr_unit`

```elixir
@spec drop_cldr_unit(db_type()) :: String.t()
```

Returns the SQL dropping the `cldr_unit` and `cldr_unit_with_usage`
composite types.

### Arguments

* `db_type` is the target database. Only `:postgres` is supported. The
  default is `:postgres`.

### Returns

* The SQL statements as a string, separated by two blank lines.

### Examples

    iex> Localize.Unit.DDL.drop_cldr_unit() =~ "DROP TYPE public.cldr_unit_with_usage;"
    true

# `execute`

```elixir
@spec execute(String.t()) :: String.t()
```

Wraps a SQL string as a single `execute` call for a migration.

See `Localize.Ecto.TaggedDecimal.DDL.execute/2`.

### Arguments

* `sql` is a SQL string.

### Returns

* The `execute` call as a string.

### Examples

    iex> Localize.Unit.DDL.execute("DROP TYPE public.cldr_unit;")
    "execute \"DROP TYPE public.cldr_unit;\""

# `execute_each`

```elixir
@spec execute_each(String.t()) :: String.t()
```

Wraps each statement of a SQL string as its own `execute` call.

See `Localize.Ecto.TaggedDecimal.DDL.execute_each/2`.

### Arguments

* `sql` is a SQL string possibly containing several statements.

### Returns

* The `execute` calls as a string, one per statement.

### Examples

    iex> Localize.Unit.DDL.execute_each(Localize.Unit.DDL.drop_cldr_unit()) =~ "execute"
    true

---

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