Statement.Sql (etl_core v0.1.55)
Module for definition and construction of SQL statements
Summary
Functions
Construct an DELETE statement
Construct an instruction to generate a UUID
Returns the known SQL function
Returns the concatenation operator. The BETWEEN operator is not expected.
Construct an INSERT statement
Combine multiple INSERT queries into a single one
Construct an SELECT statement
Construct an UPDATE statement
Functions
Link to this function
delete(table_id)
Construct an DELETE statement
Parameters:
- table_id: String. Name of the table.
- conditions. List of (list of tuple). If the value is a empty list, it will be assumed that the existence of the WHERE component is not desired. Otherwise, each tuple has the format {a, b, c} or {a, b, c, d} where
- a: Atom. Column name.
- b: t(). Value to compare.
- c: Atom. Operation to use. Expect :eq, :eq_nil, :neq, :neq_nil, :gt, :gte, :lt, :lte, :in, :like. You can query get_operator/1.
- d: Atom. Function to use.
Each list of this parameter, its elements are concatenated by the first value defined in `operators` and the lists are related to each other by the second value defined in `operators`.
- operators: List of size at most 2. List of connectors. if it has cardinality
- 0 => length(conditions) = 0
- 1 => length(conditions) = 1
- 2 => length(conditions) > 1Return:
- String
Link to this function
delete(table_id, conditions, operators \\ [])
Link to this function
generate_uuid()
Construct an instruction to generate a UUID
Return:
- String
Link to this function
get_function(atom)
Returns the known SQL function
Parameter:
- function_name: Atom. Name of SQL function to use. The possible values are:
:timestamp Function returns a datetime value based on a date or datetime value string, contains a time zone.
:datetime Function returns a datetime value based on a date or datetime value string
:time Function returns a time value based on a date or time value string
:date Function returns a time value based on a date or date value stringReturn:
- String
Link to this function
get_operator(atom)
Returns the concatenation operator. The BETWEEN operator is not expected.
Parameter:
- operator: Atom. Operator to convert. The possible values are:
:eq = equal
:neq <> not equal
:gt > greater than
:gte >= greater than or equal
:lt < less than
:lte <= less than or equal
:like LIKE search for a pattern
:in IN to specify multiple possible values for a column
:and AND and
:or OR or
:on ON to join
:inner_join INNER JOIN returns records that have matching values in both tables
:left_join LEFT OUTER JOIN returns all records from the left table, and the matched records from the right table
:right_join RIGHT OUTER JOIN returns all records from the right table, and the matched records from the left table
:full_join FULL OUTER JOIN returns all records when there is a match in either left or right tableReturn:
- String
Link to this function
insert(table_id, values)
Construct an INSERT statement
Parameters:
- table_id: String. Name of the table to insert the data into.
- values: List of tuples. List of values to insert. Each tuple is {column_name, associated_value_to_store}.Return:
- String
Link to this function
merge_inserts(queries)
Combine multiple INSERT queries into a single one
Parameters:
- `queries`: [String]. List of insert clauses. It is assumed that they all belong to the same tableReturn:
- String
Link to this function
select(table_id, columns_to_return \\ :all, conditions \\ [], operators \\ [])
Construct an SELECT statement
Parameters:
- table_id: String. Name of the table.
- columns_to_return: List of atoms | :all. Defines the columns to return. If the `all` atom is defined, all the columns of the table will be returned; otherwise, a list will be defined with those that interest
- conditions. List of (list of tuple). If the value is a empty list, it will be assumed that the existence of the WHERE component is not desired. Otherwise, each tuple has the format {a, b, c} or {a, b, c, d} where
- a: Atom. Column name.
- b: t(). Value to compare.
- c: Atom. Operation to use. Expect :eq, :eq_nil, :neq, :neq_nil, :gt, :gte, :lt, :lte, :in, :like. You can query get_operator/1.
- d: Atom. Function to use.
Each list of this parameter, its elements are concatenated by the first value defined in `operators` and the lists are related to each other by the second value defined in `operators`.
- operators: List of size at most 2. List of connectors. if it has cardinality
- 0 => length(conditions) = 0
- 1 => length(conditions) = 1
- 2 => length(conditions) > 1Return:
- String
Link to this function
update(table_id, changes, conditions \\ [], operators \\ [])
Construct an UPDATE statement
Parameters:
- table_id: String. Name of the table.
- changes: List of tuple of size 2 [{atom, t()}, ...]. Change list. Each tuple {A, B} indicates
- A field to modify
- B value to modify in field a
- conditions. List of (list of tuple). If the value is a empty list, it will be assumed that the existence of the WHERE component is not desired. Otherwise, each tuple has the format {a, b, c} or {a, b, c, d} where
- a: Atom. Column name.
- b: t(). Value to compare.
- c: Atom. Operation to use. Expect :eq, :eq_nil, :neq, :neq_nil, :gt, :gte, :lt, :lte, :in, :like. You can query get_operator/1.
- d: Atom. Function to use.
Each list of this parameter, its elements are concatenated by the first value defined in `operators` and the lists are related to each other by the second value defined in `operators`.
- operators: List of size at most 2. List of connectors. if it has cardinality
- 0 => length(conditions) = 0
- 1 => length(conditions) = 1
- 2 => length(conditions) > 1Return:
- String