Localize.Ecto.TaggedDecimal.DDL (Localize SQL v1.0.0)

Copy Markdown View Source

Generates the SQL that installs a tagged decimal type in PostgreSQL.

A tagged decimal — described by a Localize.Ecto.TaggedDecimal.t/0 specification — is stored as a composite type pairing a tag with a decimal, such as money_with_currency(currency_code, amount) or cldr_unit(unit, value). This module returns the SQL creating that type together with the aggregate functions and operators that make it usable in queries.

Aggregates over a tagged decimal are tag-guarded: summing a column whose rows carry different tags raises rather than returning a meaningless number. The generated functions compare the tag of each incoming row against the tag accumulated so far and raise SQLSTATE 22033 (invalid_parameter_value) on a mismatch, so a query that adds euros to yen fails in the database rather than silently succeeding.

The SQL is generated from the specification rather than kept as static files so that one implementation serves every domain. The names of the composite type, its fields and the generated functions come from the specification, because a deployed database already contains them and an aggregate must name the same fields as the type it was created against.

Statements

Each function returns one or more SQL statements in a single string, separated by two blank lines. execute/1,2 and execute_each/1,2 wrap them for use in an Ecto migration — execute_each/1,2 emits one execute per statement, which is what a migration needs when the statements must run in order.

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.create_type(spec)
"CREATE TYPE public.cldr_unit AS (unit varchar, value numeric);"

Summary

Functions

create_type(spec)

Returns the SQL creating the composite type.

define_avg_function(spec)

Returns the SQL defining a tag-guarded avg aggregate.

define_minmax_functions(spec)

Returns the SQL defining tag-guarded min and max aggregates.

define_operator(spec, operator)

Returns the SQL defining a tag-guarded operator over the type.

define_sum_function(spec)

Returns the SQL defining a tag-guarded sum aggregate.

drop_avg_function(spec)

Returns the SQL dropping the avg aggregate, its functions and its intermediate state type.

drop_minmax_functions(spec)

Returns the SQL dropping the min and max aggregates and their functions.

drop_operator(spec, operator)

Returns the SQL dropping an operator over the type and its function.

drop_sum_function(spec)

Returns the SQL dropping the sum aggregate and its functions.

drop_type(spec)

Returns the SQL dropping the composite type.

execute(sql, append \\ "")

Wraps SQL as a single execute call for an Ecto migration.

execute_each(sql, append \\ "")

Wraps each statement of sql as its own execute call.

Functions

create_type(spec)

@spec create_type(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL creating the composite type.

Arguments

Returns

  • The SQL statement as a string.

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "money_with_currency", tag_field: "currency_code",
...>   value_field: "amount", function_prefix: "money",
...>   tag_description: "currency code"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.create_type(spec)
"CREATE TYPE public.money_with_currency AS (currency_code varchar, amount numeric);"

define_avg_function(spec)

@spec define_avg_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL defining a tag-guarded avg aggregate.

The aggregate accumulates into an intermediate composite type holding a running sum and count, which the final function divides. That type is created by this SQL and removed by drop_avg_function/1.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.define_avg_function(spec) =~ "CREATE TYPE public.unit_avg_state"
true

define_minmax_functions(spec)

@spec define_minmax_functions(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL defining tag-guarded min and max aggregates.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.define_minmax_functions(spec) =~ "AGGREGATE min(cldr_unit)"
true

define_operator(spec, operator)

@spec define_operator(Localize.Ecto.TaggedDecimal.t(), :add | :subtract | :negate) ::
  String.t()

Returns the SQL defining a tag-guarded operator over the type.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.define_operator(spec, :add) =~ "CREATE OPERATOR + ("
true

define_sum_function(spec)

@spec define_sum_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL defining a tag-guarded sum aggregate.

Three statements: the state function, the combine function that makes the aggregate safe under parallel query, and the aggregate itself.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.define_sum_function(spec) =~ "CREATE OR REPLACE AGGREGATE sum(cldr_unit)"
true

drop_avg_function(spec)

@spec drop_avg_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL dropping the avg aggregate, its functions and its intermediate state type.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.drop_avg_function(spec) =~ "DROP TYPE IF EXISTS public.unit_avg_state;"
true

drop_minmax_functions(spec)

@spec drop_minmax_functions(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL dropping the min and max aggregates and their functions.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.drop_minmax_functions(spec) =~ "DROP AGGREGATE IF EXISTS max(cldr_unit);"
true

drop_operator(spec, operator)

@spec drop_operator(Localize.Ecto.TaggedDecimal.t(), :add | :subtract | :negate) ::
  String.t()

Returns the SQL dropping an operator over the type and its function.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.drop_operator(spec, :negate)
"DROP OPERATOR IF EXISTS - (NONE, cldr_unit);\n\n\nDROP FUNCTION IF EXISTS unit_negate(cldr_unit);"

drop_sum_function(spec)

@spec drop_sum_function(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL dropping the sum aggregate and its functions.

Arguments

Returns

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

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.drop_sum_function(spec) =~ "DROP AGGREGATE IF EXISTS sum(cldr_unit);"
true

drop_type(spec)

@spec drop_type(Localize.Ecto.TaggedDecimal.t()) :: String.t()

Returns the SQL dropping the composite type.

Arguments

Returns

  • The SQL statement as a string.

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> Localize.Ecto.TaggedDecimal.DDL.drop_type(spec)
"DROP TYPE public.cldr_unit;"

execute(sql, append \\ "")

@spec execute(String.t(), String.t()) :: String.t()

Wraps SQL as a single execute call for an Ecto migration.

Arguments

  • sql is a SQL string.

  • append is a string appended inside the generated execute call, such as a pipe into a migration-time adjustment. The default is "".

Returns

  • The execute call as a string.

Examples

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

execute_each(sql, append \\ "")

@spec execute_each(String.t(), String.t()) :: String.t()

Wraps each statement of sql as its own execute call.

Statements are separated by two blank lines, as the generating functions of this module emit them.

Arguments

  • sql is a SQL string possibly containing several statements.

  • append is a string appended inside each generated execute call. The default is "".

Returns

  • The execute calls as a string, one per statement.

Examples

iex> spec = Localize.Ecto.TaggedDecimal.new!(
...>   type_name: "cldr_unit", tag_field: "unit", value_field: "value",
...>   function_prefix: "unit", tag_description: "unit name"
...> )
iex> sql = Localize.Ecto.TaggedDecimal.DDL.drop_sum_function(spec)
iex> Localize.Ecto.TaggedDecimal.DDL.execute_each(sql) |> String.split("\n") |> length()
3