Statement.Sql (etl_core v0.1.54)
Module for definition and construction of SQL statements
Link to this section 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
Construct an SELECT statement
Construct an UPDATE statement
Link to this section Functions
Link to this function
delete(table_id)
Construct an DELETE statement
parameters
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) > 1
return
Return:
- String
Link to this function
delete(table_id, conditions, operators \\ [])
Link to this function
generate_uuid()
Construct an instruction to generate a UUID
return
Return:
- String
Link to this function
get_function(atom)
Returns the known SQL function
parameter
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 string
return
Return:
- String
Link to this function
get_operator(atom)
Returns the concatenation operator. The BETWEEN operator is not expected.
parameter
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 table
return
Return:
- String
Link to this function
insert(table_id, values)
Construct an INSERT statement
parameters
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
Return:
- String
Link to this function
select(table_id, columns_to_return \\ :all, conditions \\ [], operators \\ [])
Construct an SELECT statement
parameters
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) > 1
return
Return:
- String
Link to this function
update(table_id, changes, conditions \\ [], operators \\ [])
Construct an UPDATE statement
parameters
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) > 1
return
Return:
- String