Introduction to Localize Units SQL

localize_units_sql implements a set of functions to store and retrieve data structured as a Localize.Unit.t() type that represents a unit of measure and a value. See localize for details of using Localize.Unit. Note that localize_units_sql depends on localize.

Prerequisites

localize_units_sql is supported on Elixir 1.17 and later only.

Migrating from ex_cldr_units_sql
From version 2.0, the package has been renamed from ex_cldr_units_sql to localize_units_sql. The Cldr.Unit.* modules are now Localize.Unit.* (for example Cldr.Unit.Ecto.Composite.Type becomes Localize.Unit.Ecto.Composite.Type). Replace the ex_cldr family of dependencies with the unified localize package and remove any MyApp.Cldr backend module. Configure locales through config :localize. The Postgres composite type names (cldr_unit, cldr_unit_with_usage) are unchanged, so existing databases do not require a schema migration.

Postgrex JSON library
localize_units_sql no longer declares jason as a dependency. Postgrex defaults to Jason for encoding json/jsonb columns, so configure your preferred JSON library explicitly, for example config :postgrex, :json_library, JSON (requires Elixir 1.18+). Postgrex captures this setting at compile time, so after changing it run mix deps.compile postgrex --force once.

Make sure the Ecto type and the database type match!
It's important that the Ecto type Localize.Unit.Ecto.Composite.Type is matched with the correct database type in the migration: :cldr_unit or :cldr_unit_with_usage. Similarly Localize.Unit.Ecto.Map.Type must be matched with the database type :map in the migration.

Serializing to a Postgres database with Ecto

localize_units_sql provides custom Ecto data types and two custom Postgres data types to provide serialization of Localize.Unit.t() types without losing precision whilst also maintaining the integrity of the {unit, value} relationship. To serialize and retrieve unit types from a database the following steps should be followed:

First generate the migration to create the custom type:

mix units.gen.postgres.cldr_units_migration
* creating priv/repo/migrations
* creating priv/repo/migrations/20161007234652_add_cldr_unit_type_to_postgres.exs

Then migrate the database:

mix ecto.migrate
21:01:29.527 [info]  == Running 20200517121207 Localize.Unit.SQL.Repo.Migrations.AddCldrUnitTypeToPostgres.up/0 forward

21:01:29.529 [info]  execute "CREATE TYPE public.cldr_unit AS (unit varchar, value numeric);"

21:01:29.532 [info]  execute "CREATE TYPE public.cldr_unit_with_usage AS (unit varchar, value numeric, usage varchar);"

21:01:29.546 [info]  == Migrated 20200517121207 in 0.0s

Create your database migration with the new type (don't forget to mix ecto.migrate as well):

defmodule Localize.Unit.Repo.Migrations.CreateProduct do
  use Ecto.Migration

  def change do
    create table(:products) do
      add :weight, :cldr_unit
      add :length, :cldr_unit_with_usage
      timestamps()
    end
  end
end

Create your schema using the Localize.Unit.Ecto.Composite.Type ecto type:

defmodule Product do
  use Ecto.Schema

  schema "products" do
    field :weight, Localize.Unit.Ecto.Composite.Type
    field :length, Localize.UnitWithUsage.Ecto.Composite.Type

    timestamps()
  end
end

Insert into the database:

iex> Repo.insert %Product{weight: Localize.Unit.new!(Decimal.new(100), "kilogram")}

Retrieve from the database:

iex> Repo.all Product

Serializing to a MySQL (or other non-Postgres) database with Ecto

Since MySQL does not support composite types, the :map type is used which in MySQL is implemented as a JSON column. The unit name and amount are serialized into this column.

defmodule Localize.Unit.Repo.Migrations.CreateProduct do
  use Ecto.Migration

  def change do
    create table(:products) do
      add :weight_map, :map
      add :length_map, :map
      timestamps()
    end
  end
end

Create your schema using the Localize.Unit.Ecto.Map.Type ecto type:

defmodule Product do
  use Ecto.Schema

  schema "products" do
    field :weight_map, Localize.Unit.Ecto.Map.Type
    field :length_map, Localize.UnitWithUsage.Ecto.Map.Type

    timestamps()
  end
end

Insert into the database:

iex> Repo.insert %Product{weight_map: Localize.Unit.new!(100, "kilogram")}

Retrieve from the database:

iex> Repo.all Product

Notes

In order to preserve precision of the decimal amount, the amount part of the Localize.Unit.t() struct is serialized as a string. This is done because JSON serializes numeric values as either integer or float, neither of which would preserve precision of a decimal value.
The precision of the serialized string value is affected by the setting of Decimal.get_context/0. The default is 28 digits which should cater for your requirements.
Serializing the amount as a string means that SQL query arithmetic and equality operators will not work as expected. You may find that CASTing the string value will restore some of that functionality. For example:

CAST(JSON_EXTRACT(amount_map, &#39;$.value&#39;) AS DECIMAL(20, 8)) AS amount;

Postgres Database functions

Since the datatype used to store Localize.Unit in Postgres is a composite type (called :cldr_unit), the standard aggregation functions like sum and average are not supported and the order_by clause doesn't perform as expected. localize_units_sql provides mechanisms to provide these functions.

Aggregate functions: sum()

localize_units_sql provides a migration generator which, when migrated to the database with mix ecto.migrate, supports performing sum() aggregation on :cldr_unit types. The steps are:

Generate the migration by executing mix units.gen.postgres.aggregate_functions
Migrate the database by executing mix ecto.migrate
Formulate an Ecto query to use the aggregate function sum()

iex> q = Ecto.Query.select(Product, [p], type(sum(p.weight), p.weight))
iex> Repo.all(q)

The function Repo.aggregate/3 can also be used. However at least ecto version 3.2.4 is required for this to work correctly for custom ecto types such as :cldr_unit.

iex> Repo.aggregate(Product, :sum, :weight)

Note that to preserve the integrity of Localize.Unit it is not permissible to aggregate units that have different unit types. If you attempt to aggregate units with different unit types the query will abort and an exception will be raised:

** (Postgrex.Error) ERROR 22033 (): Incompatible units. Expected all unit names to be meter

Order_by with cldr_unit type

Since :cldr_unit is a composite type, the default order_by results may surprise since the ordering is based upon the type structure, not the unit value. Postgres defines a means to access the components of a composite type and therefore sorting can be done in a more predictable fashion. For example:

# In this example we are decomposing the composite column called
# `weight` and using the sub-field `value` to perform the ordering.
iex> q = from p in Product, select: p.weight, order_by: fragment("value(weight)")
iex> Repo.all(q)

Note that the results may still be unexpected. The example above shows the correct ascending ordering by value(weight) however the ordering is not unit aware and therefore mixed units will return a largely meaningless order.

Installation

localize_units_sql can be installed by adding localize_units_sql to your list of dependencies in mix.exs and then executing mix deps.get.

def deps do
  [
    {:localize_units_sql, "~> 2.0"},
    ...
  ]
end

Introduction to Localize Units SQL

Prerequisites

Migrating from ex_cldr_units_sql

Postgrex JSON library

Make sure the Ecto type and the database type match!