Lotus.Storage.VariableResolver (Lotus v1.2.0)

Extracts variable-to-column bindings from SQL queries.

Works out which table column each {{variable}} is compared against so Lotus.Storage.Query.compile/2 can cast the value to the column's type. It scans Lotus.Query.Tokenizer tokens with a few heuristics; it is not a SQL parser. A variable it cannot bind falls back to the variable's declared type, so a miss costs nothing but a cast.

Supported Patterns

Explicit: WHERE users.id = {{user_id}}
Implicit: WHERE id = {{user_id}} (table from the first FROM)
Aliased tables: WHERE u.id = {{id}} (resolves alias u to users)
Schema-qualified tables: FROM public.users carries schema: "public"
Quoted identifiers: "Users"."Id" keeps its case; unquoted names are folded to lowercase

A name introduced by a WITH clause is not a base table: a column of a CTE, or of an alias of one, binds with table: nil. So does a column of a subquery in FROM.

Usage

sql = "SELECT * FROM users WHERE users.id = {{user_id}}"
VariableResolver.resolve_variables(sql)
# => [%{variable: "user_id", schema: nil, table: "users", column: "id"}]

Summary

Types

variable_binding()

Functions

resolve_variables(sql, profile \\ Profile.for_language("sql"))

Extract variable bindings from a SQL statement.

Types

variable_binding()

@type variable_binding() :: %{
  variable: String.t(),
  schema: String.t() | nil,
  table: String.t() | nil,
  column: String.t() | nil
}

Functions

resolve_variables(sql, profile \\ Profile.for_language("sql"))

@spec resolve_variables(String.t(), Lotus.Query.Tokenizer.Profile.t()) :: [
  variable_binding()
]

Extract variable bindings from a SQL statement.

Returns one binding per variable, deduplicated by name. An explicit binding wins over an implicit one, and both win over an unbound occurrence. The profile selects the quoting and comment rules; it defaults to ANSI SQL.

Examples

resolve_variables("SELECT * FROM users WHERE users.id = {{user_id}}")
# => [%{variable: "user_id", schema: nil, table: "users", column: "id"}]

resolve_variables("SELECT * FROM users u WHERE u.id = {{user_id}}")
# => [%{variable: "user_id", schema: nil, table: "users", column: "id"}]

resolve_variables("SELECT * FROM public.users WHERE id = {{user_id}}")
# => [%{variable: "user_id", schema: "public", table: "users", column: "id"}]