ExSQL.
Copy Markdown
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.
Summary
Functions
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.
Arithmetic with SQLite semantics: NULL propagates; text operands get numeric affinity applied (non-numeric text becomes 0); division and modulo by zero yield NULL.
Bitwise NOT (~) over 64 bits; NULL propagates.
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.
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).
Total ordering across storage classes: NULL < numeric < TEXT < BLOB.
SQL comparison operator: returns nil (unknown) if either side is NULL,
otherwise a boolean per compare/2.
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).
String concatenation (||): NULL propagates, operands become text.
The GLOB operator: */? wildcards, case-sensitive.
The LIKE operator: % matches any run, _ any single character;
ASCII case-insensitive, as in SQLite's default (ICU-less) build.
Returns true if an integer falls outside the 64-bit signed range.
Three-valued AND.
Three-valued NOT.
Three-valued OR.
Renders a value as TEXT, the way CAST(x AS TEXT) would.
Converts a value to SQL boolean: nil, true, or false.
Returns the storage class of a value, as typeof() reports it.
Types
Functions
@spec apply_affinity(t(), ExSQL.AST.ColumnDef.affinity()) :: t()
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.
Arithmetic with SQLite semantics: NULL propagates; text operands get numeric affinity applied (non-numeric text becomes 0); division and modulo by zero yield NULL.
Bitwise NOT (~) over 64 bits; NULL propagates.
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 cast(t(), ExSQL.AST.ColumnDef.affinity()) :: t()
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).
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).
SQL comparison operator: returns nil (unknown) if either side is NULL,
otherwise a boolean per compare/2.
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).
String concatenation (||): NULL propagates, operands become text.
The GLOB operator: */? wildcards, case-sensitive.
The LIKE operator: % matches any run, _ any single character;
ASCII case-insensitive, as in SQLite's default (ICU-less) build.
Returns true if an integer falls outside the 64-bit signed range.
Three-valued AND.
Three-valued NOT.
Three-valued OR.
Renders a value as TEXT, the way CAST(x AS TEXT) would.
Converts a value to SQL boolean: nil, true, or false.
@spec type_of(t()) :: :null | :integer | :real | :text | :blob
Returns the storage class of a value, as typeof() reports it.