def increase_row_integer(
table_name: str,
set_field: str,
where_field: str,
where_value,
):
"""Increase the integer in the row specified by the where fields.
Given a primary key, a field set_field, and a pair (where_field,
where_value), it increases the field in the appropriate row
:param table_name: Primary key to detect workflow
:param set_field: name of the field to be increased
:param where_field: Field used to filter the row in the table
:param where_value: Value of the previous field to filter the row
:return: The table in the workflow pointed by PK is modified.
"""
query = sql.SQL('UPDATE {0} SET {1} = {1} + 1 WHERE {2} = %s').format(
sql.Identifier(table_name),
OnTaskDBIdentifier(set_field),
OnTaskDBIdentifier(where_field),
sql.Literal(where_value))
# Execute the query
with connection.connection.cursor() as cursor:
cursor.execute(query, [where_value])
connection.commit()
def not_equal(node, eval_type, given_variables):
"""Process the not equal operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
constant = GET_CONSTANT.get(node['type'])(node['value'])
if eval_type == EVAL_EXP:
# Python evaluation
varvalue = get_value(node, given_variables)
return (not value_is_null(varvalue)) and varvalue != constant
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL('({0} != {1}) OR ({0} is null)').format(
OnTaskDBIdentifier(node['field']),
sql.Placeholder(),
)
fields = [str(constant)]
return query, fields
# Text evaluation
return '{0} ≠ {1} and not empty'.format(
node['field'], constant,
)
def is_not_null(node, eval_type, given_variables):
"""Process the is_not_null operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
if eval_type == EVAL_EXP:
# Python evaluation
return not value_is_null(get_value(node, given_variables))
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL('({0} is not null)').format(
OnTaskDBIdentifier(node['field']),
)
return query, []
# Text evaluation
return '{0} is not null'.format(node['field'])
def not_ends_with(node, eval_type, given_variables):
"""Process the not_ends_width operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
constant = GET_CONSTANT.get(node['type'])(node['value'])
if eval_type == EVAL_EXP:
# Python evaluation
varvalue = get_value(node, given_variables)
return (
(not value_is_null(varvalue))
and (not varvalue.endswith(constant))
)
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL('({0} NOT LIKE {1}) OR ({0} is null)').format(
OnTaskDBIdentifier(node['field']),
sql.Placeholder(),
)
fields = ['%' + node['value']]
return query, fields
# Text evaluation
return '{0} does not end with {1}'.format(node['field'], constant)
def contains(node, eval_type, given_variables):
"""Process the contains operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
constant = GET_CONSTANT.get(node['type'])(node['value'])
if eval_type == EVAL_EXP:
# Python evaluation
varvalue = get_value(node, given_variables)
return (
(not value_is_null(varvalue)) and varvalue.find(constant) != -1
)
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL('({0} LIKE {1}) AND ({0} is not null)').format(
OnTaskDBIdentifier(node['field']),
sql.Placeholder(),
)
fields = ['%' + node['value'] + '%']
return query, fields
# Text evaluation
return '{0} contains {1}'.format(node['field'], constant)
def insert_row(
table_name: str,
keys: List[str],
values: List,
):
"""Insert a row with a set of pairs.
Given a table and a list of (set_field, set_value) create a new row
:param table_name: Table name
:param keys: List of column names
:param values: List of column values
:return: Nothing. Effect reflected in the database.
"""
ncols = len(keys)
query = sql.SQL('INSERT INTO {0} ({1}) VALUES ({2})').format(
sql.Identifier(table_name),
sql.SQL(', ').join([
OnTaskDBIdentifier(key) for key in keys
]),
sql.SQL(', ').join([sql.Placeholder()] * ncols)
)
# Execute the query
with connection.connection.cursor() as cursor:
cursor.execute(query, values)
def less_or_equal(node, eval_type, given_variables):
"""Process the less_or_equal operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
constant = GET_CONSTANT.get(node['type'])(node['value'])
if eval_type == EVAL_EXP:
# Python evaluation
varvalue = get_value(node, given_variables)
if node['type'] in ('integer', 'double', 'datetime'):
return (not value_is_null(varvalue)) and varvalue <= constant
raise Exception(
ugettext(
'Evaluation error: Type {0} not allowed ' +
'with operator LESS OR EQUAL', ).format(node['type']), )
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL('({0} <= {1}) AND ({0} is not null)').format(
OnTaskDBIdentifier(node['field']),
sql.Placeholder(),
)
fields = [str(constant)]
return query, fields
# Text evaluation
return '{0} ⋜ {1} and not empty'.format(
node['field'],
constant,
)
def is_not_empty(node, eval_type, given_variables):
"""Process the is_empty operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
if eval_type == EVAL_EXP:
# Python evaluation
varvalue = get_value(node, given_variables)
return (not value_is_null(varvalue)) and varvalue != ''
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL('({0} != \'\') AND ({0} is not null)').format(
OnTaskDBIdentifier(node['field']), )
fields = [node['value']]
return query, fields
# Text evaluation
return '{0} is not empty'.format(node['field'])
def get_select_query(
table_name: str,
column_names: Optional[List[str]] = None,
filter_formula: Optional[Dict] = None,
filter_pairs: Optional[Mapping] = None,
) -> Tuple[sql.Composed, List[Any]]:
"""Calculate pair query, fields to execute a select statement.
:param table_name: Table to query
:param column_names: list of columns to consider or None to consider all
:param filter_formula: Text filter expression
:param filter_pairs: Dictionary of key/value pairs.
:return: (sql query, sql params)
"""
if column_names:
query = sql.SQL('SELECT {0} FROM {1}').format(
sql.SQL(', ').join(
[OnTaskDBIdentifier(cname) for cname in column_names]),
sql.Identifier(table_name),
)
else:
query = sql.SQL('SELECT * FROM {0}').format(sql.Identifier(table_name))
query_fields = []
if filter_formula or filter_pairs:
bool_clause, query_fields = get_boolean_clause(
filter_formula=filter_formula,
filter_pairs=filter_pairs,
)
if bool_clause:
query = query + sql.SQL(' WHERE ') + bool_clause
return query, query_fields
def update_row(
table_name: str,
keys: List[str],
values: List,
filter_dict: Optional[Mapping] = None,
):
"""Update table row with key,values in a row determined by a filter.
Given a table, a list of (set_field, set_value), and pairs (where_field,
where_value), it updates the row in the table selected with the list of (
where field = where value) with the values in the assignments in the list
of (set_fields, set_values)
:param table_name: Table name
:param keys: List of column names
:param values: List of column values
:param filter_dict: Dictionary of key, value to select the row
:return:
"""
query = sql.SQL('UPDATE {0} SET ').format(
sql.Identifier(table_name),
) + sql.SQL(', ').join([
sql.SQL('{0} = {1}').format(OnTaskDBIdentifier(key), sql.Placeholder())
for key in keys
])
query_fields = values
if filter_dict:
query = query + sql.SQL(' WHERE ')
query = query + sql.SQL(' AND ').join([
sql.SQL('{0} = {1}').format(
OnTaskDBIdentifier(key), sql.Placeholder())
for key in filter_dict.keys()
])
query_fields += [lit_val for lit_val in filter_dict.values()]
# Execute the query
with connection.connection.cursor() as cursor:
cursor.execute(query, query_fields)
def not_between(node, eval_type, given_variables):
"""Process the not_between operator.
:param node: Formula node
:param eval_type: Type of evaluation
:param given_variables: Dictionary of var/values
:return: Boolean result, SQL query, or text result
"""
if eval_type == EVAL_EXP:
# Python evaluation
varvalue = get_value(node, given_variables)
if value_is_null(varvalue):
return False
if node['type'] not in ('integer', 'double', 'datetime'):
raise Exception(
ugettext(
'Evaluation error: Type {0} not allowed '
+ 'with operator BETWEEN',
).format(node['type']),
)
left = GET_CONSTANT[node['type']](node['value'][0])
right = GET_CONSTANT[node['type']](node['value'][1])
return not left <= varvalue <= right
if eval_type == EVAL_SQL:
# SQL evaluation
query = sql.SQL(
'({0} NOT BETWEEN {1} AND {2}) AND ({0} is not null)',
).format(
OnTaskDBIdentifier(node['field']),
sql.Placeholder(),
sql.Placeholder(),
)
fields = [str(number) for number in node['value']]
return query, fields
# Text evaluation
return '{0} < {1} or {0} > {2} or {0} is empty'.format(
node['field'],
str(node['value'][0]),
str(node['value'][1]),
)
def is_column_unique(table_name: str, column_name: str) -> bool:
"""Return if a table column has all non-empty unique values.
:param table_name: table
:param column_name: column
:return: Boolean (is unique)
"""
query = sql.SQL('SELECT COUNT(DISTINCT {0}) = count(*) from {1}').format(
OnTaskDBIdentifier(column_name),
sql.Identifier(table_name),
)
# Get the result
with connection.connection.cursor() as cursor:
cursor.execute(query, [])
return cursor.fetchone()[0]
def get_text_column_hash(table_name: str, column_name: str) -> str:
"""Calculate and return the MD5 hash of a text column.
:param table_name: table to use
:param column_name: column to pull the values
:return: MD5 hash of the concatenation of the column values
"""
query = sql.SQL('SELECT MD5(STRING_AGG({0}, {1})) FROM {2}').format(
OnTaskDBIdentifier(column_name),
sql.Literal(''),
sql.Identifier(table_name),
)
with connection.connection.cursor() as cursor:
cursor.execute(query)
return cursor.fetchone()[0]
def get_boolean_clause(
filter_formula: Optional[Dict] = None,
filter_pairs: Optional[Mapping] = None,
conjunction: bool = True,
) -> Tuple[sql.Composed, List]:
"""Create the boolean clause based on a formula and a list of pairs.
Create the SQL boolean clause to be added to a query by combining a
formula and a dictionary with key:value pairs. Both of them are optional
and are combined through conjunction/disjunction depending on the
conjunction variable.
:param filter_formula: Boolean formula
:param filter_pairs: Dictionary of key/value pairs.
:param conjunction: Boolean stating if the clauses need to be in a
conjunction.
:return: SQL clause and list of fields.
"""
clause = None
clause_fields = []
if filter_formula:
# There is a filter
clause, clause_fields = evaluate_formula(filter_formula, EVAL_SQL)
if filter_pairs:
c_txt = ' AND ' if conjunction else ' OR '
pairs_clause = sql.SQL(c_txt).join([
sql.SQL('{0} = {1}').format(OnTaskDBIdentifier(key),
sql.Placeholder())
for key, __ in filter_pairs.items()
])
pairs_fields = [lit_val for __, lit_val in filter_pairs.items()]
if clause:
clause = clause + sql.SQL(' AND ') + pairs_clause
clause_fields += pairs_fields
else:
clause = pairs_clause
clause_fields = pairs_fields
return clause, clause_fields
def search_table(
table_name: str,
search_value: str,
columns_to_search: Optional[List] = None,
filter_formula: Optional[Dict] = None,
any_join: bool = True,
order_col_name: str = None,
order_asc: bool = True,
):
"""Search the content of all cells in the table.
Select rows where for every (column, value) pair, column contains value (
as in LIKE %value%, these are combined with OR if any is TRUE, or AND if
any is false, and the result is ordered by the given column and type (if
given)
:param table_name: table name
:param filter_formula: Optional filter condition to pre filter the query
:param columns_to_search: A column, value, type tuple to search the value
in the column set. the query is built with these terms as requirement AND
the cv_tuples.
:param any_join: Boolean encoding if values should be combined with OR (or
AND)
:param order_col_name: Order results by this column
:param order_asc: Order results in ascending values (or descending)
:param search_value: String to search
:return: The resulting query set
"""
# Create the query
if columns_to_search:
query = sql.SQL('SELECT {0} FROM {1}').format(
sql.SQL(', ').join([
OnTaskDBIdentifier(colname) for colname in columns_to_search
]),
sql.Identifier(table_name),
)
else:
query = sql.SQL('SELECT * from {1}').format(sql.Identifier(table_name))
query_fields = []
where_clause = sql.SQL('')
# Add filter part if present
if filter_formula:
filter_query, filter_fields = evaluate_formula(filter_formula,
EVAL_SQL)
if filter_query:
where_clause = filter_query
query_fields += filter_fields
# Add the CAST {0} AS TEXT LIKE ...
if search_value:
if where_clause != sql.SQL(''):
where_clause = where_clause + sql.SQL(' AND ')
# Combine the search subqueries
if any_join:
conn_txt = ' OR '
else:
conn_txt = ' AND '
where_clause = where_clause + sql.SQL(conn_txt).join([
sql.SQL('(CAST ({0} AS TEXT) LIKE %s)').format(
OnTaskDBIdentifier(cname), ) for cname in columns_to_search
])
query_fields += ['%' + search_value + '%'] * len(columns_to_search)
if where_clause != sql.SQL(''):
query = query + sql.SQL(' WHERE') + where_clause
# Add the order if needed
if order_col_name:
query = query + sql.SQL(' ORDER BY {0}').format(
OnTaskDBIdentifier(order_col_name))
if not order_asc:
query = query + sql.SQL(' DESC')
# Execute the query
with connection.connection.cursor() as cursor:
cursor.execute(query, query_fields)
search_result = cursor.fetchall()
return search_result
