defmodule Selecto.Advanced.ValuesClause do
@moduledoc """
VALUES clause support for inline table generation and data transformations.
VALUES clauses allow creating inline tables from literal data, useful for
lookup tables, data transformations, and testing scenarios.
## Examples
# Basic VALUES table
selecto
|> Selecto.with_values([
["PG", "Family Friendly", 1],
["PG-13", "Teen", 2],
["R", "Adult", 3]
],
columns: ["rating_code", "description", "sort_order"],
as: "rating_lookup"
)
# Map-based VALUES
selecto
|> Selecto.with_values([
%{month: 1, name: "January", days: 31},
%{month: 2, name: "February", days: 28},
%{month: 3, name: "March", days: 31}
], as: "months")
"""
defmodule Spec do
@moduledoc """
Specification for a VALUES clause operation.
"""
defstruct [
# Unique identifier for the VALUES clause
:id,
# List of data rows (lists or maps)
:data,
# Column names (inferred from data or explicit)
:columns,
# Alias for the VALUES table
:alias,
# :list_of_lists, :list_of_maps
:data_type,
# Inferred data types for each column
:column_types,
# Boolean indicating if data has been validated
:validated
]
@type data_row :: [term()] | map()
@type column_spec :: String.t() | atom()
@type t :: %__MODULE__{
id: String.t(),
data: [data_row()],
columns: [column_spec()],
alias: String.t(),
data_type: :list_of_lists | :list_of_maps,
column_types: map(),
validated: boolean()
}
end
defmodule ValidationError do
@moduledoc """
Error raised when VALUES clause data is invalid.
"""
defexception [:type, :message, :details]
@type t :: %__MODULE__{
type: :inconsistent_columns | :empty_data | :type_mismatch | :validation_error,
message: String.t(),
details: map()
}
end
@doc """
Create a VALUES clause specification.
## Parameters
- `data` - List of data rows (lists or maps)
- `opts` - Options including :columns, :as (alias)
## Examples
# List of lists format
ValuesClause.create_values_clause([
["PG", "Family", 1],
["R", "Adult", 3]
], columns: ["code", "desc", "order"], as: "ratings")
# Map format (columns inferred)
ValuesClause.create_values_clause([
%{id: 1, name: "Alice"},
%{id: 2, name: "Bob"}
], as: "users")
"""
def create_values_clause(data, opts \\ []) do
alias_name = Keyword.get(opts, :as, "values_table")
explicit_columns = Keyword.get(opts, :columns, [])
spec = %Spec{
id: generate_values_id(alias_name),
data: data,
columns: [],
alias: alias_name,
data_type: detect_data_type(data),
column_types: %{},
validated: false
}
# Validate and process the data
case validate_and_process_data(spec, explicit_columns) do
{:ok, processed_spec} -> processed_spec
{:error, validation_error} -> raise validation_error
end
end
@doc """
Validate VALUES clause data and infer column information.
Ensures data consistency and infers column names and types.
"""
def validate_and_process_data(%Spec{} = spec, explicit_columns) do
with :ok <- validate_data_not_empty(spec.data),
{:ok, data_type} <- validate_data_consistency(spec.data),
{:ok, columns} <- determine_columns(spec.data, data_type, explicit_columns),
{:ok, column_types} <- infer_column_types(spec.data, data_type, columns),
:ok <- validate_data_completeness(spec.data, data_type, columns) do
processed_spec = %{
spec
| data_type: data_type,
columns: columns,
column_types: column_types,
validated: true
}
{:ok, processed_spec}
else
{:error, reason} -> {:error, reason}
end
end
# Validate that data is not empty
defp validate_data_not_empty([]),
do:
{:error,
%ValidationError{
type: :empty_data,
message: "VALUES clause cannot have empty data",
details: %{}
}}
defp validate_data_not_empty(_data), do: :ok
# Validate that all data rows have consistent structure
defp validate_data_consistency([first_row | rest]) do
data_type = detect_data_type([first_row])
case data_type do
:list_of_lists ->
validate_list_consistency([first_row | rest])
:list_of_maps ->
validate_map_consistency([first_row | rest])
_ ->
{:error,
%ValidationError{
type: :validation_error,
message: "Unsupported data format for VALUES clause",
details: %{data_type: data_type}
}}
end
end
# Validate that all rows are lists with same length
defp validate_list_consistency([first_row | rest]) when is_list(first_row) do
expected_length = length(first_row)
invalid_rows =
rest
|> Enum.with_index(1)
|> Enum.filter(fn {row, _index} ->
not is_list(row) or (is_list(row) and length(row) != expected_length)
end)
if Enum.empty?(invalid_rows) do
{:ok, :list_of_lists}
else
[{invalid_row, index}] = Enum.take(invalid_rows, 1)
actual_length = if is_list(invalid_row), do: length(invalid_row), else: 0
{:error,
%ValidationError{
type: :inconsistent_columns,
message: "Row #{index + 1} has #{actual_length} columns, expected #{expected_length}",
details: %{
expected_length: expected_length,
actual_length: actual_length,
row_index: index + 1
}
}}
end
end
# Validate that all rows are maps with consistent keys
defp validate_map_consistency([first_row | rest]) when is_map(first_row) do
expected_keys = Map.keys(first_row) |> Enum.map(&to_string/1) |> Enum.sort()
invalid_rows =
rest
|> Enum.with_index(1)
|> Enum.filter(fn {row, _index} ->
not is_map(row) or Map.keys(row) |> Enum.map(&to_string/1) |> Enum.sort() != expected_keys
end)
if Enum.empty?(invalid_rows) do
{:ok, :list_of_maps}
else
[{invalid_row, index}] = Enum.take(invalid_rows, 1)
actual_keys =
if is_map(invalid_row),
do: Map.keys(invalid_row) |> Enum.map(&to_string/1) |> Enum.sort(),
else: []
{:error,
%ValidationError{
type: :inconsistent_columns,
message: "Row #{index + 1} has different keys than first row",
details: %{
expected_keys: expected_keys,
actual_keys: actual_keys,
row_index: index + 1
}
}}
end
end
# Determine column names from data and explicit columns
defp determine_columns(data, :list_of_lists, explicit_columns) do
case explicit_columns do
[] ->
# Generate default column names
[first_row | _] = data
column_count = length(first_row)
columns = Enum.map(1..column_count, &"column#{&1}")
{:ok, columns}
columns when is_list(columns) ->
[first_row | _] = data
if length(columns) == length(first_row) do
{:ok, Enum.map(columns, &to_string/1)}
else
{:error,
%ValidationError{
type: :inconsistent_columns,
message:
"Number of explicit columns (#{length(columns)}) doesn't match data columns (#{length(first_row)})",
details: %{explicit_count: length(columns), data_count: length(first_row)}
}}
end
end
end
defp determine_columns(data, :list_of_maps, explicit_columns) do
[first_row | _] = data
inferred_columns = Map.keys(first_row) |> Enum.map(&to_string/1) |> Enum.sort()
case explicit_columns do
[] ->
{:ok, inferred_columns}
columns when is_list(columns) ->
explicit_strings = Enum.map(columns, &to_string/1) |> Enum.sort()
if explicit_strings == inferred_columns do
{:ok, explicit_strings}
else
{:error,
%ValidationError{
type: :inconsistent_columns,
message: "Explicit columns don't match map keys",
details: %{explicit_columns: explicit_strings, map_keys: inferred_columns}
}}
end
end
end
# Infer column types from data
defp infer_column_types(data, data_type, columns) do
column_types =
case data_type do
:list_of_lists ->
infer_types_from_lists(data, columns)
:list_of_maps ->
infer_types_from_maps(data, columns)
end
{:ok, column_types}
end
# Infer types from list of lists format
defp infer_types_from_lists(data, columns) do
columns
|> Enum.with_index()
|> Enum.into(%{}, fn {column, index} ->
# Sample values from this column position
sample_values =
data
# Sample first 10 rows
|> Enum.take(10)
|> Enum.map(&Enum.at(&1, index))
|> Enum.reject(&is_nil/1)
inferred_type = infer_type_from_values(sample_values)
{column, inferred_type}
end)
end
# Infer types from list of maps format
defp infer_types_from_maps(data, columns) do
columns
|> Enum.into(%{}, fn column ->
column_key = String.to_existing_atom(column)
# Sample values from this column
sample_values =
data
# Sample first 10 rows
|> Enum.take(10)
|> Enum.map(&Map.get(&1, column_key))
|> Enum.reject(&is_nil/1)
inferred_type = infer_type_from_values(sample_values)
{column, inferred_type}
end)
rescue
ArgumentError ->
# Fallback if string to atom conversion fails
columns
|> Enum.into(%{}, fn column ->
sample_values =
data
|> Enum.take(10)
|> Enum.map(&Map.get(&1, column))
|> Enum.reject(&is_nil/1)
inferred_type = infer_type_from_values(sample_values)
{column, inferred_type}
end)
end
# Infer data type from sample values
defp infer_type_from_values([]), do: :unknown
defp infer_type_from_values(values) do
# Check the first non-nil value's type
case Enum.find(values, &(not is_nil(&1))) do
val when is_integer(val) -> :integer
val when is_float(val) -> :decimal
val when is_boolean(val) -> :boolean
val when is_binary(val) -> :string
%Date{} -> :date
%DateTime{} -> :utc_datetime
%NaiveDateTime{} -> :naive_datetime
_ -> :unknown
end
end
# Validate that all data rows have complete data for all columns
defp validate_data_completeness(data, :list_of_lists, columns) do
expected_length = length(columns)
incomplete_rows =
data
|> Enum.with_index()
|> Enum.filter(fn {row, _index} -> length(row) != expected_length end)
if Enum.empty?(incomplete_rows) do
:ok
else
[{row, index}] = Enum.take(incomplete_rows, 1)
{:error,
%ValidationError{
type: :inconsistent_columns,
message: "Row #{index + 1} has #{length(row)} values, expected #{expected_length}",
details: %{expected: expected_length, actual: length(row), row_index: index + 1}
}}
end
end
defp validate_data_completeness(data, :list_of_maps, columns) do
required_keys =
Enum.map(columns, fn col ->
try do
String.to_existing_atom(col)
rescue
ArgumentError -> col
end
end)
incomplete_rows =
data
|> Enum.with_index()
|> Enum.filter(fn {row, _index} ->
row_keys = Map.keys(row) |> Enum.sort()
Enum.sort(required_keys) != row_keys
end)
if Enum.empty?(incomplete_rows) do
:ok
else
[{row, index}] = Enum.take(incomplete_rows, 1)
{:error,
%ValidationError{
type: :inconsistent_columns,
message: "Row #{index + 1} missing or has extra keys",
details: %{required_keys: required_keys, row_keys: Map.keys(row), row_index: index + 1}
}}
end
end
# Detect data type from first element
defp detect_data_type([]), do: :empty
defp detect_data_type([first | _]) when is_list(first), do: :list_of_lists
defp detect_data_type([first | _]) when is_map(first), do: :list_of_maps
defp detect_data_type(_), do: :unknown
# Generate unique ID for VALUES clause
defp generate_values_id(alias_name) do
unique = :erlang.unique_integer([:positive])
"values_#{alias_name}_#{unique}"
end
end
