Packages
vtc
0.13.12
0.17.5
0.17.4
0.17.3
0.17.2
0.17.1
0.17.0
0.16.9
0.16.8
0.16.7
0.16.6
0.16.5
0.16.4
0.16.2
0.16.1
0.16.0
0.15.4
0.15.3
0.15.2
0.15.1
0.15.0
0.14.5
0.14.4
0.14.3
0.14.2
0.14.1
0.14.0
0.13.15
0.13.14
0.13.13
0.13.12
0.13.11
0.13.10
0.13.9
0.13.8
0.13.7
0.13.6
0.13.5
0.13.4
0.13.3
0.13.2
0.13.1
0.13.0
0.12.1
0.12.0
0.11.1
0.11.0
0.10.10
0.10.9
0.10.8
0.10.7
0.10.6
0.10.5
0.10.4
0.10.3
0.10.2
0.10.1
0.10.0
0.9.2
0.9.1
0.9.0
0.8.4
0.8.3
0.8.2
0.8.1
0.8.0
0.7.3
0.7.2
0.7.1
0.7.0
0.6.1
0.6.0
0.5.3
0.5.2
0.5.1
0.4.0
0.3.9
0.3.8
0.3.7
0.3.6
0.3.5
0.3.4
0.3.3
0.3.2
0.3.1
0.3.0
0.2.6
0.2.5
0.2.4
0.2.3
0.2.2
0.2.1
0.2.0
0.1.9
0.1.8
0.1.7
0.1.6
0.1.5
0.1.4
0.1.3
0.1.2
0.1.1
A SMPTE timecode library for Elixir
Current section
Files
Jump to
Current section
Files
lib/ecto/postgres/pg_rational_migrations.ex
use Vtc.Ecto.Postgres.Utils
defpgmodule Vtc.Ecto.Postgres.PgRational.Migrations do
@moduledoc """
Migrations for adding rational types, casts, functions and constraints to a
Postgres database.
"""
alias Ecto.Migration
alias Ecto.Migration.Constraint
alias Vtc.Ecto.Postgres
require Ecto.Migration
@doc section: :migrations_full
@doc """
Adds raw SQL queries to a migration for creating the database types, associated
functions, casts, operators, and operator families.
This migration included all migraitons under the
[Pg Types](Vtc.Ecto.Postgres.PgRational.Migrations.html#pg-types),
[Pg Operators](Vtc.Ecto.Postgres.PgRational.Migrations.html#pg-operators),
[Pg Operator Classes](Vtc.Ecto.Postgres.PgRational.Migrations.html#pg-operator-classes),
[Pg Functions](Vtc.Ecto.Postgres.PgRational.Migrations.html#pg-functions), and
[Pg Private Functions](Vtc.Ecto.Postgres.PgRational.Migrations.html#pg-private-functions),
headings.
Safe to run multiple times when new functionality is added in updates to this library.
Existing values will be skipped.
## Types Created
Calling this macro creates the following type definitions:
```sql
CREATE TYPE public.rational AS (
numerator bigint,
denominator bigint
);
```
## Schemas Created
Up to two schemas are created as detailed by the
[Configuring Database Objects](Vtc.Ecto.Postgres.PgRational.Migrations.html#create_all/0-configuring-database-objects)
section below.
## Configuring Database Objects
To change where supporting functions are created, add the following to your
Repo confiugration:
```elixir
config :vtc, Vtc.Test.Support.Repo,
adapter: Ecto.Adapters.Postgres,
...
vtc: [
rational: [
functions_schema: :rational,
functions_prefix: "rational"
]
]
```
Option definitions are as follows:
- `functions_schema`: The postgres schema to store rational-related custom functions.
- `functions_prefix`: A prefix to add before all functions. Defaults to "rational"
for any function created in the `:public` schema, and "" otherwise.
## Private Functions
Some custom function names are prefaced with `__private__`. These functions should
not be called by end-users, as they are not subject to *any* API staility guarantees.
## Functions Created
See [PgFunctions](Vtc.Ecto.Postgres.PgRational.Migrations.html#pgfunctions)
section of these docs for details on native database functions
created.
## Operators Created
See [PgOperators](Vtc.Ecto.Postgres.PgRational.Migrations.html#pgoperators)
section of these docs for details on native database operators
created.
## Casts Created
See [PgCasts](Vtc.Ecto.Postgres.PgRational.Migrations.html#pgcasts)
section of these docs for details on native database operators
created.
## Examples
```elixir
defmodule MyMigration do
use Ecto.Migration
alias Vtc.Ecto.Postgres.PgRational
require PgRational.Migrations
def change do
PgRational.Migrations.create_all()
end
end
```
"""
@spec create_all() :: :ok
def create_all do
create_type()
create_function_schemas()
create_func_simplify()
create_func_minus()
create_func_abs()
create_func_round()
create_func_floor()
create_func_add()
create_func_sub()
create_func_mult()
create_func_div()
create_func_floor_div()
create_func_modulo()
create_op_add()
create_op_sub()
create_op_mult()
create_op_div()
create_op_modulo()
create_func_cmp()
create_func_eq()
create_func_neq()
create_func_lt()
create_func_lte()
create_func_gt()
create_func_gte()
create_op_eq()
create_op_neq()
create_op_neq2()
create_op_lt()
create_op_lte()
create_op_gt()
create_op_gte()
create_op_class_btree()
create_func_cast_to_double_precison()
create_func_cast_bigint_to_rational()
create_cast_double_precision()
create_cast_bigint_to_rational()
end
@doc section: :migrations_types
@doc """
Adds:
- `rational` composite type
- `rationals` schema
- `rationals_helpers` schema
"""
@spec create_type() :: :ok
def create_type do
Migration.execute("""
DO $$ BEGIN
CREATE TYPE rational AS (
numerator bigint,
denominator bigint
);
EXCEPTION WHEN duplicate_object
THEN null;
END $$;
""")
end
@doc section: :migrations_types
@doc """
Creates function schema as described by the
[Configuring Database Objects](Vtc.Ecto.Postgres.PgRational.Migrations.html#create_all/0-configuring-database-objects)
section above.
"""
@spec create_function_schemas() :: :ok
def create_function_schemas, do: Postgres.Utils.create_type_schemas(:rational)
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__simplify(rat)` function that simplifies a rational. Used at
the end of every rational operation to avoid overflows.
"""
@spec create_func_simplify() :: :ok
def create_func_simplify do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:simplify, Migration.repo()),
args: [input: :rational],
returns: :rational,
declares: [
greatest_denom: {:bigint, "gcd(input.numerator, input.denominator)"},
denominator: {:bigint, "ABS(input.denominator / greatest_denom)"},
numerator: {:bigint, "input.numerator / greatest_denom"}
],
body: """
IF (input).denominator < 0 THEN
RETURN (numerator * -1, denominator);
ELSE
RETURN (numerator, denominator);
END IF;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_functions
@doc """
Creates `rational.minus(rat)` function that flips the sign of the input value --
makes a positive value negative and a negative value positive.
"""
@spec create_func_minus() :: :ok
def create_func_minus do
create_func =
Postgres.Utils.create_plpgsql_function(
function(:minus, Migration.repo()),
args: [input: :rational],
returns: :rational,
body: """
RETURN ((input).numerator * -1, (input).denominator)::rational;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_functions
@doc """
Creates `ABS(rational)` function that returns the absolute value of the rational
value.
"""
@spec create_func_abs() :: :ok
def create_func_abs do
create_func =
Postgres.Utils.create_plpgsql_function(
"ABS",
args: [input: :rational],
returns: :rational,
body: """
RETURN (ABS((input).numerator), ABS((input).denominator))::rational;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_functions
@doc """
Creates `ROUND(rational)` function that returns the rational input, rounded to the
nearest :bigint.
"""
@spec create_func_round() :: :ok
def create_func_round do
create_func =
Postgres.Utils.create_plpgsql_function(
"ROUND",
args: [input: :rational],
returns: :bigint,
body: """
CASE
WHEN (input).numerator < 0 THEN
input := #{function(:minus, Migration.repo())}(input);
RETURN ROUND(input) * -1;
WHEN (((input).numerator % (input).denominator) * 2) < (input).denominator THEN
RETURN (input).numerator / (input).denominator;
ELSE
RETURN ((input).numerator / (input).denominator) + 1;
END CASE;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_functions
@doc """
Creates `FLOOR(rational)` function that returns the rational input as a `bigint`,
rounded towards zero, to match Postgres `FLOOR(real)` behavior.
"""
@spec create_func_floor() :: :ok
def create_func_floor do
create_func =
Postgres.Utils.create_plpgsql_function(
"FLOOR",
args: [input: :rational],
returns: :bigint,
body: """
RETURN ((input).numerator / (input).denominator);
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates a native CAST from `rational` to `double precision`.
"""
@spec create_func_cast_to_double_precison() :: :ok
def create_func_cast_to_double_precison do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:cast_to_double, Migration.repo()),
args: [value: :rational],
returns: :"double precision",
body: """
RETURN CAST ((value).numerator AS double precision) / CAST ((value).denominator AS double precision);
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates a native CAST from `bigint` to `rational`.
"""
@spec create_func_cast_bigint_to_rational() :: :ok
def create_func_cast_bigint_to_rational do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:cast_bigint_to_rational, Migration.repo()),
args: [value: :bigint],
returns: :rational,
body: """
RETURN (value, 1)::rational;
"""
)
Migration.execute(create_func)
end
## ARITHMATIC BACKING FUNCS
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__add(a, b)` backing function for the `+` operator
between two rationals.
"""
@spec create_func_add() :: :ok
def create_func_add do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:add, Migration.repo()),
args: [a: :rational, b: :rational],
declares: [
numerator: {:bigint, "((a).numerator * (b).denominator) + ((b).numerator * (a).denominator)"},
denominator: {:bigint, "(a).denominator * (b).denominator"}
],
returns: :rational,
body: """
RETURN #{private_function(:simplify, Migration.repo())}((numerator, denominator));
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__sub(a, b)` backing function for the `-` operator
between two rationals.
"""
@spec create_func_sub() :: :ok
def create_func_sub do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:sub, Migration.repo()),
args: [a: :rational, b: :rational],
returns: :rational,
body: """
RETURN #{private_function(:add, Migration.repo())}(a, b * -1::bigint);
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__mult(a, b)` backing function for the `*` operator
between two rationals.
"""
@spec create_func_mult() :: :ok
def create_func_mult do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:mult, Migration.repo()),
args: [a: :rational, b: :rational],
declares: [
numerator: {:bigint, "(a).numerator * (b).numerator"},
denominator: {:bigint, "(a).denominator * (b).denominator"}
],
returns: :rational,
body: """
RETURN #{private_function(:simplify, Migration.repo())}((numerator, denominator));
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__div(a, b)` backing function for the `/` operator
between two rationals.
"""
@spec create_func_div() :: :ok
def create_func_div do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:div, Migration.repo()),
args: [a: :rational, b: :rational],
declares: [
numerator: {:bigint, "(a).numerator * (b).denominator"},
denominator: {:bigint, "(a).denominator * (b).numerator"}
],
returns: :rational,
body: """
RETURN #{private_function(:simplify, Migration.repo())}((numerator, denominator));
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__div(a, b)` backing function for the `/` operator
between two rationals.
"""
@spec create_func_floor_div() :: :ok
def create_func_floor_div do
create_func =
Postgres.Utils.create_plpgsql_function(
"DIV",
args: [a: :rational, b: :rational],
declares: [result: {:rational, "a / b"}],
returns: :bigint,
body: """
RETURN FLOOR(result);
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__modulo(a, b)` backing function for the `%` operator
between two rationals.
"""
@spec create_func_modulo() :: :ok
def create_func_modulo do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:modulo, Migration.repo()),
args: [dividend: :rational, divisor: :rational],
declares: [
numerator:
{:bigint,
"""
((dividend).numerator * (divisor).denominator)
% ((divisor).numerator * (dividend).denominator)
"""},
denominator: {:bigint, "(dividend).denominator * (divisor).denominator"}
],
returns: :rational,
body: """
RETURN #{private_function(:simplify, Migration.repo())}((numerator, denominator));
"""
)
Migration.execute(create_func)
end
## COMPARISON BACKING FUNCS
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__cmp(a, b)` that returns:
- `1` if a > b
- `0` if a == b
- `-1` if a < b
Used to back equality operators.
"""
@spec create_func_cmp() :: :ok
def create_func_cmp do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:cmp, Migration.repo()),
args: [a: :rational, b: :rational],
declares: [
a_cmp: {:bigint, "((a).numerator * (b).denominator)"},
b_cmp: {:bigint, "((b).numerator * (a).denominator)"}
],
returns: :integer,
body: """
RETURN sign(a_cmp - b_cmp);
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__eq(a, b)` that backs the `=` operator.
"""
@spec create_func_eq() :: :ok
def create_func_eq do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:eq, Migration.repo()),
args: [a: :rational, b: :rational],
returns: :boolean,
body: """
RETURN #{private_function(:cmp, Migration.repo())}(a, b) = 0;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__neq(a, b)` that backs the `<>` operator.
"""
@spec create_func_neq() :: :ok
def create_func_neq do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:neq, Migration.repo()),
args: [a: :rational, b: :rational],
returns: :boolean,
body: """
RETURN #{private_function(:cmp, Migration.repo())}(a, b) != 0;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__lt(a, b)` that backs the `<` operator.
"""
@spec create_func_lt() :: :ok
def create_func_lt do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:lt, Migration.repo()),
args: [a: :rational, b: :rational],
returns: :boolean,
body: """
RETURN #{private_function(:cmp, Migration.repo())}(a, b) = -1;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__lte(a, b)` that backs the `<=` operator.
"""
@spec create_func_lte() :: :ok
def create_func_lte do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:lte, Migration.repo()),
args: [a: :rational, b: :rational],
declares: [
cmp: {:integer, "#{private_function(:cmp, Migration.repo())}(a, b)"}
],
returns: :boolean,
body: """
RETURN cmp = -1 OR cmp = 0;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__gt(a, b)` that backs the `>` operator.
"""
@spec create_func_gt() :: :ok
def create_func_gt do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:gt, Migration.repo()),
args: [a: :rational, b: :rational],
returns: :boolean,
body: """
RETURN #{private_function(:cmp, Migration.repo())}(a, b) = 1;
"""
)
Migration.execute(create_func)
end
@doc section: :migrations_private_functions
@doc """
Creates `rational.__private__gte(a, b)` that backs the `>=` operator.
"""
@spec create_func_gte() :: :ok
def create_func_gte do
create_func =
Postgres.Utils.create_plpgsql_function(
private_function(:gte, Migration.repo()),
args: [a: :rational, b: :rational],
declares: [
cmp: {:integer, "#{private_function(:cmp, Migration.repo())}(a, b)"}
],
returns: :boolean,
body: """
RETURN cmp = 1 OR cmp = 0;
"""
)
Migration.execute(create_func)
end
## ARITHMATIC OPS
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `+` operator.
"""
@spec create_op_add() :: :ok
def create_op_add do
create_op =
Postgres.Utils.create_operator(
:+,
:rational,
:rational,
private_function(:add, Migration.repo()),
commutator: :+
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `-` operator.
"""
@spec create_op_sub() :: :ok
def create_op_sub do
create_op =
Postgres.Utils.create_operator(
:-,
:rational,
:rational,
private_function(:sub, Migration.repo())
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `*` operator.
"""
@spec create_op_mult() :: :ok
def create_op_mult do
create_op =
Postgres.Utils.create_operator(
:*,
:rational,
:rational,
private_function(:mult, Migration.repo()),
commutator: :*
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `/` operator.
"""
@spec create_op_div() :: :ok
def create_op_div do
create_op =
Postgres.Utils.create_operator(
:/,
:rational,
:rational,
private_function(:div, Migration.repo())
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `%` operator.
"""
@spec create_op_modulo() :: :ok
def create_op_modulo do
create_op =
Postgres.Utils.create_operator(
:%,
:rational,
:rational,
private_function(:modulo, Migration.repo())
)
Migration.execute(create_op)
end
## COMPARISON OPS
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `=` operator.
"""
@spec create_op_eq() :: :ok
def create_op_eq do
create_op =
Postgres.Utils.create_operator(
:=,
:rational,
:rational,
private_function(:eq, Migration.repo()),
commutator: :=,
negator: :<>
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `<>` operator.
"""
@spec create_op_neq() :: :ok
def create_op_neq do
create_op =
Postgres.Utils.create_operator(
:<>,
:rational,
:rational,
private_function(:neq, Migration.repo()),
commutator: :<>,
negator: :=
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `!=` operator.
"""
@spec create_op_neq2() :: :ok
def create_op_neq2 do
create_op =
Postgres.Utils.create_operator(
:!=,
:rational,
:rational,
private_function(:neq, Migration.repo()),
commutator: :!=,
negator: :=
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `<` operator.
"""
@spec create_op_lt() :: :ok
def create_op_lt do
create_op =
Postgres.Utils.create_operator(
:<,
:rational,
:rational,
private_function(:lt, Migration.repo()),
commutator: :>,
negator: :>=
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `<` operator.
"""
@spec create_op_lte() :: :ok
def create_op_lte do
create_op =
Postgres.Utils.create_operator(
:<=,
:rational,
:rational,
private_function(:lte, Migration.repo()),
commutator: :>=,
negator: :>
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `<` operator.
"""
@spec create_op_gt() :: :ok
def create_op_gt do
create_op =
Postgres.Utils.create_operator(
:>,
:rational,
:rational,
private_function(:gt, Migration.repo()),
commutator: :<,
negator: :<=
)
Migration.execute(create_op)
end
@doc section: :migrations_operators
@doc """
Creates a custom :rational, :rational `<` operator.
"""
@spec create_op_gte() :: :ok
def create_op_gte do
create_op =
Postgres.Utils.create_operator(
:>=,
:rational,
:rational,
private_function(:gte, Migration.repo()),
commutator: :<=,
negator: :<
)
Migration.execute(create_op)
end
## OPERATOR CLASSES
@doc section: :migrations_operator_classes
@doc """
Creates a B-tree operator class to support indexing on comparison operations.
"""
@spec create_op_class_btree() :: :ok
def create_op_class_btree do
create_class =
Postgres.Utils.create_operator_class(
:rational_ops_btree,
:rational,
:btree,
[
<: 1,
<=: 2,
=: 3,
>=: 4,
>: 5
],
[
{private_function(:cmp, Migration.repo()), 1}
]
)
Migration.execute(create_class)
end
## CASTS
@doc section: :migrations_casts
@doc """
Creates a native cast for:
```sql
rational AS double precision
```
"""
@spec create_cast_double_precision() :: :ok
def create_cast_double_precision do
create_cast =
Postgres.Utils.create_cast(
:rational,
:"double precision",
private_function(:cast_to_double, Migration.repo())
)
Migration.execute(create_cast)
end
@doc section: :migrations_casts
@doc """
Creates a native cast for:
```sql
bigint AS rational
```
"""
@spec create_cast_bigint_to_rational() :: :ok
def create_cast_bigint_to_rational do
create_cast =
Postgres.Utils.create_cast(
:bigint,
:rational,
private_function(:cast_bigint_to_rational, Migration.repo()),
implicit: true
)
Migration.execute(create_cast)
end
@doc section: :migrations_constraints
@doc """
Creates basic constraints for a `PgRational` database field.
## Constraints created:
- `{field_name}_denominator_positive`: Checks that the denominator of the field is
positive.
## Examples
```elixir
create table("rationals", primary_key: false) do
add(:id, :uuid, primary_key: true, null: false)
add(:a, PgRational.type())
add(:b, PgRational.type())
end
PgRational.migration_add_field_constraints(:rationals, :a)
PgRational.migration_add_field_constraints(:rationals, :b)
```
"""
@spec create_field_constraints(atom(), atom()) :: :ok
def create_field_constraints(table, field_name) do
sql_field = "#{table}.#{field_name}"
constraint =
Migration.constraint(
table,
"#{field_name}_denominator_positive",
check: """
(#{sql_field}).denominator > 0
"""
)
%Constraint{} = Migration.create(constraint)
:ok
end
@doc """
Returns the config-qualified name of the function for this type.
"""
@spec function(atom(), Ecto.Repo.t()) :: String.t()
def function(name, repo) do
function_prefix = Postgres.Utils.type_function_prefix(repo, :rational)
"#{function_prefix}#{name}"
end
# Returns the config-qualified name of the function for this type.
@doc false
@spec private_function(atom(), Ecto.Repo.t()) :: String.t()
def private_function(name, repo) do
function_prefix = Postgres.Utils.type_private_function_prefix(repo, :rational)
"#{function_prefix}#{name}"
end
end