defmodule ExSQL.Value do
@moduledoc """
SQLite's value semantics: storage classes, type affinity, comparison
ordering, arithmetic, and three-valued logic.
Values map onto Elixir terms directly:
| storage class | Elixir term |
|---------------|-------------------|
| `NULL` | `nil` |
| `INTEGER` | `integer()` |
| `REAL` | `float()` |
| `TEXT` | `binary()` |
| `BLOB` | `{:blob, binary}` |
Internally, JSON-producing functions may temporarily use `{:json, binary}`
to model SQLite's JSON subtype. It behaves as TEXT for ordinary SQL
semantics and is stripped before public result/storage boundaries.
Comparison follows SQLite's cross-class ordering: NULL < numeric values
(INTEGER and REAL compare numerically with each other) < TEXT < BLOB.
Boolean logic is three-valued — `nil` means "unknown" and propagates per
the SQL standard truth tables.
"""
@type t :: nil | integer() | float() | binary() | {:blob, binary()} | {:json, binary()}
# -- storage classes -----------------------------------------------------
@doc "Returns the storage class of a value, as `typeof()` reports it."
@spec type_of(t()) :: :null | :integer | :real | :text | :blob
def type_of(nil), do: :null
def type_of(v) when is_integer(v), do: :integer
def type_of(v) when is_float(v), do: :real
def type_of(v) when is_binary(v), do: :text
def type_of({:json, _}), do: :text
def type_of({:blob, _}), do: :blob
# -- type affinity ----------------------------------------------------------
@doc """
Coerces `value` per a column's affinity before storage, following
https://sqlite.org/datatype3.html §3: affinity is a preference, not a
constraint — values that can't be losslessly converted are stored as-is.
"""
@spec apply_affinity(t(), ExSQL.AST.ColumnDef.affinity()) :: t()
def apply_affinity({:json, text}, affinity), do: apply_affinity(text, affinity)
def apply_affinity(value, :any), do: value
def apply_affinity(nil, _affinity), do: nil
def apply_affinity({:blob, _} = blob, _affinity), do: blob
def apply_affinity(value, :text) when is_integer(value) or is_float(value),
do: to_text(value)
def apply_affinity(value, affinity) when affinity in [:integer, :numeric] do
case to_number(value) do
f when is_float(f) -> demote_to_integer(f)
other -> other
end
end
def apply_affinity(value, :real) do
case to_number(value) do
i when is_integer(i) -> i * 1.0
other -> other
end
end
def apply_affinity(value, _affinity), do: value
# A REAL that is exactly representable as an integer is stored as INTEGER
# under INTEGER/NUMERIC affinity.
defp demote_to_integer(f) do
truncated = trunc(f)
if truncated * 1.0 == f, do: truncated, else: f
end
# Lossless text→number conversion; non-numeric text is returned unchanged.
defp to_number(false), do: 0
defp to_number(true), do: 1
defp to_number(value) when is_integer(value) or is_float(value), do: value
defp to_number(value) when is_binary(value) do
trimmed = String.trim(value)
case Integer.parse(trimmed) do
{i, ""} ->
i
_ ->
case Float.parse(trimmed) do
{f, ""} -> f
_ -> value
end
end
end
# -- CAST --------------------------------------------------------------------
@doc """
The `CAST` operator (https://sqlite.org/lang_expr.html#castexpr). Unlike
affinity this is a forced conversion: non-numeric text becomes 0, and
numeric casts of text take the longest numeric prefix (`'123abc'` → 123).
"""
@spec cast(t(), ExSQL.AST.ColumnDef.affinity()) :: t()
def cast(nil, _affinity), do: nil
def cast({:json, text}, affinity), do: cast(text, affinity)
def cast({:blob, _} = blob, :blob), do: blob
def cast(value, :blob), do: {:blob, to_text(value)}
# Any other cast of a BLOB first interprets its bytes as text.
def cast({:blob, b}, affinity), do: cast(b, affinity)
def cast(value, :text), do: to_text(value)
def cast(value, :integer) do
case cast_numeric(value) do
f when is_float(f) -> f |> trunc() |> clamp_int64()
i -> i
end
end
def cast(value, :real), do: cast_numeric(value) * 1.0
# NUMERIC converts to INTEGER only when lossless: 123.456 stays REAL.
def cast(value, :numeric) do
case cast_numeric(value) do
f when is_float(f) -> demote_to_integer(f)
i -> i
end
end
# CAST to INTEGER saturates at the 64-bit range, as in SQLite.
defp clamp_int64(n) when n > 9_223_372_036_854_775_807, do: 9_223_372_036_854_775_807
defp clamp_int64(n) when n < -9_223_372_036_854_775_808, do: -9_223_372_036_854_775_808
defp clamp_int64(n), do: n
defp cast_numeric(v) when is_integer(v) or is_float(v), do: v
# Longest numeric prefix: prefer the float parse only when it consumes
# more input than the integer parse ('123abc' → 123, '123.5abc' → 123.5).
defp cast_numeric(v) when is_binary(v) do
trimmed = String.trim(v)
case {Integer.parse(trimmed), Float.parse(trimmed)} do
{{i, int_rest}, {f, float_rest}} ->
if byte_size(float_rest) < byte_size(int_rest), do: f, else: i
{:error, {f, _rest}} ->
f
{{i, _rest}, :error} ->
i
{:error, :error} ->
0
end
end
# -- bitwise operators ---------------------------------------------------------
@doc """
Bitwise `&`, `|`, `<<`, `>>` over 64-bit integers: NULL propagates,
operands convert with CAST-to-INTEGER semantics, a negative shift count
shifts the other way, and results wrap to signed 64 bits.
"""
@spec bitwise(:bitand | :bitor | :shl | :shr, t(), t()) :: t()
def bitwise(_op, nil, _b), do: nil
def bitwise(_op, _a, nil), do: nil
def bitwise(op, a, b) do
a = cast(a, :integer)
b = cast(b, :integer)
case op do
:bitand -> wrap64(Bitwise.band(a, b))
:bitor -> wrap64(Bitwise.bor(a, b))
:shl -> shift_left(a, b)
:shr -> shift_left(a, -b)
end
end
@doc "Bitwise NOT (`~`) over 64 bits; NULL propagates."
@spec bitnot(t()) :: t()
def bitnot(nil), do: nil
def bitnot(v), do: v |> cast(:integer) |> Bitwise.bnot() |> wrap64()
defp shift_left(_a, s) when s >= 64, do: 0
defp shift_left(a, s) when s >= 0, do: wrap64(Bitwise.bsl(a, s))
defp shift_left(a, s) when s <= -64, do: if(a < 0, do: -1, else: 0)
defp shift_left(a, s), do: Bitwise.bsr(a, -s)
defp wrap64(n) do
<<v::signed-64>> = <<n::signed-64>>
v
end
# -- comparison -----------------------------------------------------------------
@doc """
Total ordering across storage classes: NULL < numeric < TEXT < BLOB.
Used by `ORDER BY` and the comparison operators (which additionally return
NULL when either operand is NULL — see `compare_op/3`).
"""
@spec compare(t(), t(), atom()) :: :lt | :eq | :gt
def compare(a, b, collation \\ :binary)
# Fast path for two numbers (same storage-class rank), skipping the rank
# dispatch. Used heavily by ORDER BY sorting on numeric keys.
def compare(a, b, _collation) when is_number(a) and is_number(b) do
cond do
a < b -> :lt
a > b -> :gt
true -> :eq
end
end
def compare(a, b, collation) do
case {rank(a), rank(b)} do
{r, r} -> compare_same_class(a, b, collation)
{ra, rb} when ra < rb -> :lt
_ -> :gt
end
end
defp rank(nil), do: 0
defp rank(v) when is_integer(v) or is_float(v), do: 1
defp rank(v) when is_binary(v), do: 2
defp rank({:json, _}), do: 2
defp rank({:blob, _}), do: 3
defp compare_same_class(nil, nil, _collation), do: :eq
defp compare_same_class({:blob, a}, {:blob, b}, _collation), do: compare_terms(a, b)
defp compare_same_class({:json, a}, {:json, b}, collation), do: compare_text(a, b, collation)
defp compare_same_class({:json, a}, b, collation) when is_binary(b),
do: compare_text(a, b, collation)
defp compare_same_class(a, {:json, b}, collation) when is_binary(a),
do: compare_text(a, b, collation)
defp compare_same_class(a, b, collation) when is_binary(a) and is_binary(b),
do: compare_text(a, b, collation)
defp compare_same_class(a, b, _collation), do: compare_terms(a, b)
defp compare_text(a, b, :nocase), do: compare_terms(ascii_downcase(a), ascii_downcase(b))
defp compare_text(a, b, :rtrim),
do: compare_terms(String.trim_trailing(a, " "), String.trim_trailing(b, " "))
defp compare_text(a, b, {:custom, _name, comparator}), do: comparator.(a, b)
defp compare_text(a, b, _collation), do: compare_terms(a, b)
defp ascii_downcase(text) do
for <<c <- text>>, into: <<>> do
if c in ?A..?Z, do: <<c + 32>>, else: <<c>>
end
end
defp compare_terms(a, b) do
cond do
a < b -> :lt
a > b -> :gt
true -> :eq
end
end
@doc """
SQL comparison operator: returns `nil` (unknown) if either side is NULL,
otherwise a boolean per `compare/2`.
"""
@spec compare_op(:eq | :ne | :lt | :le | :gt | :ge, t(), t()) :: boolean() | nil
def compare_op(_op, nil, _b), do: nil
def compare_op(_op, _a, nil), do: nil
def compare_op(op, a, b) do
compare_op(op, a, b, :binary)
end
def compare_op(_op, nil, _b, _collation), do: nil
def compare_op(_op, _a, nil, _collation), do: nil
# Fast path: two numbers compare numerically, the same result
# `compare/3` (rank → compare_same_class → compare_terms) would give, but
# without the storage-class dispatch. This is the bulk of numeric scans.
def compare_op(op, a, b, _collation) when is_number(a) and is_number(b) do
case op do
:eq -> a == b
:ne -> a != b
:lt -> a < b
:le -> a <= b
:gt -> a > b
:ge -> a >= b
end
end
def compare_op(op, a, b, collation) do
result = compare(a, b, collation)
case op do
:eq -> result == :eq
:ne -> result != :eq
:lt -> result == :lt
:le -> result != :gt
:gt -> result == :gt
:ge -> result != :lt
end
end
@doc """
Applies comparison affinity to a pair of operands before comparing, per
https://sqlite.org/datatype3.html §4.2: if one side has numeric affinity
and the other does not, NUMERIC is applied to the other side; else if one
side has TEXT affinity and the other has none, TEXT is applied. BLOB
affinity counts as "no affinity". Literals compared to literals are left
untouched (ticket #805).
"""
@spec comparison_coerce(t(), atom(), t(), atom()) :: {t(), t()}
def comparison_coerce(a, affinity_a, b, affinity_b) do
cond do
numeric_affinity?(affinity_a) and not numeric_affinity?(affinity_b) ->
{a, apply_affinity(b, :numeric)}
numeric_affinity?(affinity_b) and not numeric_affinity?(affinity_a) ->
{apply_affinity(a, :numeric), b}
affinity_a == :text and affinity_b != :text ->
{a, apply_affinity(b, :text)}
affinity_b == :text and affinity_a != :text ->
{apply_affinity(a, :text), b}
true ->
{a, b}
end
end
defp numeric_affinity?(affinity), do: affinity in [:integer, :real, :numeric]
# -- three-valued logic -------------------------------------------------------------
@doc "Converts a value to SQL boolean: `nil`, `true`, or `false`."
@spec truthy(t()) :: boolean() | nil
def truthy(nil), do: nil
def truthy(false), do: false
def truthy(true), do: true
def truthy(value) do
case to_number(value) do
n when is_integer(n) or is_float(n) -> n != 0
# non-numeric text is false in a boolean context
_ -> false
end
end
@doc "Three-valued AND."
@spec sql_and(boolean() | nil, boolean() | nil) :: boolean() | nil
def sql_and(false, _), do: false
def sql_and(_, false), do: false
def sql_and(true, true), do: true
def sql_and(_, _), do: nil
@doc "Three-valued OR."
@spec sql_or(boolean() | nil, boolean() | nil) :: boolean() | nil
def sql_or(true, _), do: true
def sql_or(_, true), do: true
def sql_or(false, false), do: false
def sql_or(_, _), do: nil
@doc "Three-valued NOT."
@spec sql_not(boolean() | nil) :: boolean() | nil
def sql_not(nil), do: nil
def sql_not(b), do: not b
# -- arithmetic -------------------------------------------------------------------
@doc """
Arithmetic with SQLite semantics: NULL propagates; text operands get
numeric affinity applied (non-numeric text becomes 0); division and modulo
by zero yield NULL.
"""
@spec arithmetic(:add | :sub | :mul | :div | :mod, t(), t()) :: t()
def arithmetic(_op, nil, _b), do: nil
def arithmetic(_op, _a, nil), do: nil
def arithmetic(op, a, b) do
a = to_operand(a)
b = to_operand(b)
result =
case op do
:add -> a + b
:sub -> a - b
:mul -> a * b
:div -> divide(a, b)
:mod -> modulo(a, b)
end
# Integer results that overflow 64 bits are recomputed as REAL, as in
# SQLite (expr.test: integer overflow flips the operation to floating
# point rather than wrapping or erroring).
if is_integer(result) and out_of_int64_range?(result) do
case op do
:add -> a * 1.0 + b
:sub -> a * 1.0 - b
:mul -> a * 1.0 * b
:div -> a * 1.0 / b
:mod -> result
end
else
result
end
end
@int64_min -9_223_372_036_854_775_808
@int64_max 9_223_372_036_854_775_807
@doc "Returns true if an integer falls outside the 64-bit signed range."
@spec out_of_int64_range?(integer()) :: boolean()
def out_of_int64_range?(n), do: n < @int64_min or n > @int64_max
defp to_operand(v) do
case to_number(v) do
n when is_integer(n) or is_float(n) -> n
_ -> 0
end
end
defp divide(_a, b) when b == 0, do: nil
defp divide(a, b) when is_integer(a) and is_integer(b), do: div(a, b)
defp divide(a, b), do: a / b
defp modulo(_a, b) when b == 0, do: nil
defp modulo(a, b), do: rem(trunc(a), trunc(b))
# -- text ------------------------------------------------------------------------
@doc "String concatenation (`||`): NULL propagates, operands become text."
@spec concat(t(), t()) :: t()
def concat(nil, _b), do: nil
def concat(_a, nil), do: nil
def concat(a, b), do: to_text(a) <> to_text(b)
@doc "Renders a value as TEXT, the way `CAST(x AS TEXT)` would."
@spec to_text(t()) :: binary()
def to_text(v) when is_binary(v), do: v
def to_text({:json, text}), do: text
def to_text(v) when is_integer(v), do: Integer.to_string(v)
def to_text(v) when is_float(v), do: float_to_sql_text(v)
def to_text({:blob, b}), do: b
# SQLite renders a REAL with `%!.15g`: 15 significant digits, trailing zeros
# trimmed, always with a decimal point, switching to exponent form when the
# decimal exponent is < -4 or >= 15 (C `%g` rule). `scientific: 14` gives 15
# reliable significant digits (1 + 14) without log10 rounding hazards.
defp float_to_sql_text(f) when f == 0.0, do: "0.0"
defp float_to_sql_text(f) do
[mantissa, exp] = abs(f) |> :erlang.float_to_binary([{:scientific, 14}]) |> String.split("e")
digits = String.replace(mantissa, ".", "")
exp = String.to_integer(exp)
body =
if exp < -4 or exp >= 15 do
sci_float_text(digits, exp)
else
fixed_float_text(digits, exp)
end
if f < 0, do: "-" <> body, else: body
end
defp fixed_float_text(digits, exp) do
point = exp + 1
cond do
point <= 0 ->
"0." <> String.duplicate("0", -point) <> digits
point >= byte_size(digits) ->
digits <> String.duplicate("0", point - byte_size(digits)) <> ".0"
true ->
(fn {i, frac} -> i <> "." <> frac end).(String.split_at(digits, point))
end
|> trim_float_zeros()
end
defp sci_float_text(<<lead::binary-size(1), rest::binary>>, exp) do
sign = if exp < 0, do: "-", else: "+"
exp_digits = exp |> abs() |> Integer.to_string() |> String.pad_leading(2, "0")
trim_float_zeros(lead <> "." <> rest) <> "e" <> sign <> exp_digits
end
defp trim_float_zeros(text) do
if String.contains?(text, ".") do
trimmed = String.replace_trailing(text, "0", "")
if String.ends_with?(trimmed, "."), do: trimmed <> "0", else: trimmed
else
text <> ".0"
end
end
# -- pattern matching ---------------------------------------------------------------
@doc """
The `LIKE` operator: `%` matches any run, `_` any single character;
ASCII case-insensitive, as in SQLite's default (ICU-less) build.
"""
@spec like(t(), t()) :: boolean() | nil
@spec like(t(), t(), boolean()) :: boolean() | nil
def like(nil, _pattern), do: nil
def like(_value, nil), do: nil
def like(value, pattern), do: like(value, pattern, false)
def like(value, pattern, case_sensitive?) when is_boolean(case_sensitive?) do
regex = pattern_to_regex(to_text(pattern), "%", "_", nil, like_options(case_sensitive?))
Regex.match?(regex, to_text(value))
end
@spec like(t(), t(), binary(), boolean()) :: boolean() | nil
def like(nil, _pattern, _escape), do: nil
def like(_value, nil, _escape), do: nil
def like(value, pattern, escape), do: like(value, pattern, escape, false)
def like(value, pattern, escape, case_sensitive?) when is_boolean(case_sensitive?) do
regex = pattern_to_regex(to_text(pattern), "%", "_", escape, like_options(case_sensitive?))
Regex.match?(regex, to_text(value))
end
defp like_options(true), do: []
defp like_options(false), do: [:caseless]
@doc "The `GLOB` operator: `*`/`?` wildcards, case-sensitive."
@spec glob(t(), t()) :: boolean() | nil
def glob(nil, _pattern), do: nil
def glob(_value, nil), do: nil
def glob(value, pattern) do
regex = glob_to_regex(to_text(pattern))
Regex.match?(regex, to_text(value))
end
# GLOB supports `*`, `?`, and `[...]` character classes (with `^` negation and
# `a-c` ranges) — unlike LIKE, which treats `[` literally. An unterminated `[`
# is a literal bracket, matching SQLite.
defp glob_to_regex(pattern) do
inner = pattern |> String.to_charlist() |> glob_segments() |> IO.iodata_to_binary()
Regex.compile!("\\A" <> inner <> "\\z", [:dotall])
end
defp glob_segments([]), do: []
defp glob_segments([?* | rest]), do: [".*" | glob_segments(rest)]
defp glob_segments([?? | rest]), do: ["." | glob_segments(rest)]
defp glob_segments([?[ | rest]) do
case glob_bracket(rest) do
{:ok, class, rest} -> [class | glob_segments(rest)]
:error -> [Regex.escape("[") | glob_segments(rest)]
end
end
defp glob_segments([char | rest]), do: [Regex.escape(<<char::utf8>>) | glob_segments(rest)]
defp glob_bracket(chars) do
{neg, chars} = if match?([?^ | _], chars), do: {"^", tl(chars)}, else: {"", chars}
# A `]` right after `[`/`[^` is a literal member, not the terminator.
{lead, chars} = if match?([?] | _], chars), do: {"\\]", tl(chars)}, else: {"", chars}
case glob_class_body(chars, []) do
{:ok, body, rest} -> {:ok, "[" <> neg <> lead <> body <> "]", rest}
:error -> :error
end
end
defp glob_class_body([], _acc), do: :error
defp glob_class_body([?] | rest], acc), do: {:ok, IO.iodata_to_binary(Enum.reverse(acc)), rest}
defp glob_class_body([?\\ | rest], acc), do: glob_class_body(rest, ["\\\\" | acc])
defp glob_class_body([char | rest], acc), do: glob_class_body(rest, [<<char::utf8>> | acc])
defp pattern_to_regex(pattern, many, one, escape, options) do
inner =
pattern
|> String.graphemes()
|> escaped_pattern_parts(many, one, escape)
|> Enum.join()
Regex.compile!("\\A" <> inner <> "\\z", [:dotall | options])
end
defp escaped_pattern_parts([], _many, _one, _escape), do: []
defp escaped_pattern_parts([escape, char | rest], many, one, escape) when is_binary(escape) do
[Regex.escape(char) | escaped_pattern_parts(rest, many, one, escape)]
end
defp escaped_pattern_parts([char | rest], many, one, escape) do
part =
cond do
char == many -> ".*"
char == one -> "."
true -> Regex.escape(char)
end
[part | escaped_pattern_parts(rest, many, one, escape)]
end
end