def do_operand(self, input_value, op_value, type_value, value1, value2,
value3):
result1 = formula.evaluate(
self.set_skel(input_value, op_value.format(''), type_value,
value1), formula.EVAL_EXP, {'variable': value2})
result2 = formula.evaluate(
self.set_skel(input_value, op_value.format(''), type_value,
value1), formula.EVAL_EXP, {'variable': value3})
if op_value.endswith('null') or value2 is not None:
# If value2 is not None, expect regular results
self.assertTrue(result1)
else:
# If value2 is None, then all formulas should be false
self.assertFalse(result1)
self.assertFalse(result2)
if op_value.find('{0}') != -1:
result1 = formula.evaluate(
self.set_skel(input_value, op_value.format('not_'), type_value,
value1), formula.EVAL_EXP, {'variable': value2})
result2 = formula.evaluate(
self.set_skel(input_value, op_value.format('not_'), type_value,
value1), formula.EVAL_EXP, {'variable': value3})
self.assertFalse(result1)
if op_value.endswith('null') or value3 is not None:
# If value2 is not None, expect regular results
self.assertTrue(result2)
else:
# If value2 is None, then all formulas should be false
self.assertFalse(result2)
def test_eval_conditions(self):
# Get the action first
self.action = models.Action.objects.get(name=self.action_name)
# Get wflow table, filter and column names
wflow_table = self.action.workflow.get_data_frame_table_name()
filter_formula = self.action.get_filter_formula()
column_names = self.action.workflow.get_column_names()
conditions = self.action.conditions.filter(is_filter=False)
# Get dataframe
df = pandas.get_subframe(wflow_table, filter_formula, column_names)
# Get the query set
qs = sql.get_rows(wflow_table,
column_names=column_names,
filter_formula=filter_formula)
# Iterate over the rows in the dataframe and compare
for idx, row in enumerate(qs):
row_value_df = dict(list(zip(column_names, df.loc[idx, :])))
row_value_qs = dict(list(zip(column_names, row)))
cond_eval1 = [
formula.evaluate(x.formula, formula.EVAL_EXP, row_value_df)
for x in conditions
]
cond_eval2 = [
formula.evaluate(x.formula, formula.EVAL_EXP, row_value_qs)
for x in conditions
]
assert cond_eval1 == cond_eval2
def test_evaluate_formula(self):
self.assertTrue(
formula.evaluate(self.formula1, formula.EVAL_EXP, {
'Course_Code_a': 'df',
'ANOTHER': 'v2'
}))
def action_condition_evaluation(
action: models.Action,
row_values: Dict,
) -> Optional[Dict[str, bool]]:
"""Calculate dictionary with column_name: Boolean evaluations.
:param action: Action objects to obtain the columns
:param row_values: dictionary with (name: value) pairs for one row
:return: Dictionary condition_name: True/False or None if anomaly
"""
condition_eval = {}
conditions = action.conditions.filter(is_filter=False).values(
'name',
'is_filter',
'formula',
)
for condition in conditions:
# Evaluate the condition
try:
condition_eval[condition['name']] = formula.evaluate(
condition['formula'], formula.EVAL_EXP, row_values)
except ontask.OnTaskException:
# Something went wrong evaluating a condition. Stop.
return None
return condition_eval
def get_formula_text(self):
"""Translate the formula to plain text.
Return the content of the formula in a string that is human readable
:return: String
"""
if not self.formula_text:
self.formula_text = dataops_formula.evaluate(
self.formula, dataops_formula.EVAL_TXT)
self.save()
return self.formula_text
def select_ids_all_false(
table_name: str,
filter_formula: Optional[Dict],
cond_formula_list: List[Dict],
) -> List[int]:
"""Create query to select rows with all conditions equal to false.
:param table_name: Table in the DB
:param filter_formula: Filter formula for the WHERE clause (if any)
:param cond_formula_list: Non-empty list of condition formulas
:return: List of indeces for which all conditions (and filter) are false
"""
# Prelude for the query
query = sql.SQL(
'SELECT t.position from (SELECT *, ROW_NUMBER() OVER () ' +
'AS position FROM {0}) AS t', ).format(sql.Identifier(table_name))
cond_sql, cond_fields = zip(*[
formula.evaluate(c_formula, formula.EVAL_SQL)
for c_formula in cond_formula_list
])
# WHERE clause for the conditions
query += sql.SQL(' WHERE ') + sql.SQL(' AND ').join(
[sql.SQL('(NOT ({0}))').format(cond) for cond in cond_sql], )
query_fields = sum(cond_fields, [])
# Query clause for the filter
if filter_formula:
filter_query, filter_fields = formula.evaluate(
filter_formula,
formula.EVAL_SQL,
)
query = query + sql.SQL(' AND ({0})').format(filter_query)
query_fields += filter_fields
# Run the query and return the list
cursor = connection.connection.cursor()
cursor.execute(query, query_fields)
return [id_tuple[0] for id_tuple in cursor.fetchall()]
def do_sql_txt_operand(self,
input_value,
op_value,
type_value,
value,
row_yes=1,
row_no=1):
self.set_skel(input_value, op_value.format(''), type_value, value,
'v_' + type_value)
data_frame = pandas.load_table(self.test_table, self.test_columns,
self.skel)
self.assertEqual(data_frame.shape[0], row_yes)
formula.evaluate(self.skel, formula.EVAL_TXT)
if op_value.find('{0}') != -1:
self.set_skel(input_value, op_value.format('not_'), type_value,
value, 'v_' + type_value)
data_frame = pandas.load_table(self.test_table, self.test_columns,
self.skel)
self.assertEqual(data_frame.shape[0], row_no)
formula.evaluate(self.skel, formula.EVAL_TXT)
def log(self, user, operation_type: str, **kwargs):
"""Log the operation with the object."""
payload = {
'id': self.id,
'name': self.name,
'columns': [col.name for col in self.columns.all()],
'formula': formula.evaluate(self.formula, formula.EVAL_TXT),
'nrows': self.nrows
}
payload.update(kwargs)
return Log.objects.register(user, operation_type, self.workflow,
payload)
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 = formula.evaluate(filter_formula,
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 get_action_evaluation_context(
action: models.Action,
row_values: Dict,
condition_eval: Mapping = None,
) -> Optional[Dict]:
"""Create a dictionary with name:value to evaluate action content.
:param action: Action object for which the dictionary is needed
:param row_values: Dictionary with col_name, col_value
:param condition_eval: Dictionary with the condition evaluations
:return: Dictionary with context values or None if there is an anomaly
"""
# If no row values are given, there is nothing to do here.
if row_values is None:
# No rows satisfy the given condition
return None
if not condition_eval:
# Step 1: Evaluate all the conditions
condition_eval = {}
conditions = action.conditions.filter(is_filter=False).values(
'name',
'is_filter',
'formula',
)
for condition in conditions:
# Evaluate the condition
try:
condition_eval[condition['name']] = formula.evaluate(
condition['formula'], formula.EVAL_EXP, row_values)
except ontask.OnTaskException:
# Something went wrong evaluating a condition. Stop.
return None
# Create the context with the attributes, the evaluation of the
# conditions and the values of the columns.
return dict(
dict(row_values, **condition_eval),
**action.workflow.attributes,
)
def get_num_rows(table_name, cond_filter=None):
"""Get the number of rows in the table that satisfy the condition.
:param table_name: Table name
:param cond_filter: Formula
:return: integer
"""
query = sql.SQL('SELECT count (*) FROM {0}').format(
sql.Identifier(table_name))
cond_fields = []
if cond_filter is not None:
cond_filter, cond_fields = formula.evaluate(
cond_filter,
formula.EVAL_SQL,
)
query = sql.SQL('{0} WHERE {1}').format(query, cond_filter)
with connection.connection.cursor() as cursor:
cursor.execute(query, cond_fields)
num_rows = cursor.fetchone()[0]
return num_rows
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 = formula.evaluate(filter_formula,
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
