def do_operand(self,
input_value,
op_value,
type_value,
value1,
value2,
value3):
result1 = evaluate(
self.set_skel(input_value,
op_value.format(''),
type_value,
value1),
NodeEvaluation.EVAL_EXP,
{'variable': value2}
)
result2 = evaluate(
self.set_skel(input_value,
op_value.format(''),
type_value,
value1),
NodeEvaluation.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 = evaluate(
self.set_skel(input_value,
op_value.format('not_'),
type_value,
value1),
NodeEvaluation.EVAL_EXP,
{'variable': value2}
)
result2 = evaluate(
self.set_skel(input_value,
op_value.format('not_'),
type_value,
value1),
NodeEvaluation.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 get_formula_text(self):
"""
Return the content of the formula in a string that is human readable
:return: String
"""
return evaluate(self.formula, NodeEvaluation.EVAL_TXT)
def get_evaluation_context(self, row_values):
"""
Given an action and a set of row_values, prepare the dictionary with the
condition names, attribute names and column names and their
corresponding values.
:param row_values: Values to use in the evaluation of conditions.
:return: Context dictionary, or None if there has been some 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
# Step 1: Evaluate all the conditions
condition_eval = {}
for condition in self.conditions.filter(is_filter=False).values(
'name', 'is_filter', 'formula'):
# Evaluate the condition
try:
condition_eval[condition['name']] = evaluate(
condition['formula'], NodeEvaluation.EVAL_EXP, row_values)
except OnTaskException:
# Something went wrong evaluating a condition. Stop.
return None
# Step 2: Create the context with the attributes, the evaluation of the
# conditions and the values of the columns.
attributes = self.workflow.attributes
return dict(dict(row_values, **condition_eval), **attributes)
def get_table_cursor(pk, cond_filter, column_names):
"""
Execute a select query in the database with an optional filter obtained
from the jquery QueryBuilder.
:param pk: Primary key of the workflow storing the data
:param cond_filter: Condition object to filter the data (or None)
:param column_names: optional list of columns to select
:return: ([list of column names], QuerySet with the data rows)
"""
# Create the query
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}" from "{1}"'.format(
'", "'.join(safe_column_names),
create_table_name(pk)
)
# See if the action has a filter or not
fields = []
if cond_filter is not None:
cond_filter, fields = evaluate(cond_filter.formula,
NodeEvaluation.EVAL_SQL)
if cond_filter:
# The condition may be empty, in which case, nothing is needed.
query += ' WHERE ' + cond_filter
# Execute the query
cursor = connection.cursor()
cursor.execute(query, fields)
return cursor
def test_evaluate_formula(self):
self.assertTrue(
formula_evaluation.evaluate(self.formula1, NodeEvaluation.EVAL_EXP,
{
'Course_Code_a': 'df',
'ANOTHER': 'v2'
}))
def delete_filter(request, pk):
"""
Handle the AJAX request to delete a filter
:param request: AJAX request
:param pk: Filter ID
:return: AJAX response
"""
# Get the filter
try:
cond_filter = Condition.objects.filter(
Q(action__workflow__user=request.user)
| Q(action__workflow__shared=request.user),
is_filter=True).distinct().get(pk=pk)
except (KeyError, ObjectDoesNotExist):
return redirect('workflow:index')
data = dict()
data['form_is_valid'] = False
# Treat the two types of requests
if request.method == 'POST':
# If the request has 'action_content', update the action
action_content = request.POST.get('action_content', None)
if action_content:
cond_filter.action.set_content(action_content)
cond_filter.action.save()
# Log the event
formula, fields = evaluate(cond_filter.formula,
NodeEvaluation.EVAL_SQL)
Log.objects.register(
request.user, Log.FILTER_DELETE, cond_filter.action.workflow, {
'id': cond_filter.id,
'name': cond_filter.name,
'selected_rows': cond_filter.n_rows_selected,
'formula': formula,
'formula_fields': fields
})
# Get the action object for further processing
action = cond_filter.action
# Perform the delete operation
cond_filter.delete()
# Number of selected rows now needs to be updated in all remaining
# conditions
action.update_n_rows_selected()
return JsonResponse({'form_is_valid': True, 'html_redirect': ''})
data['html_form'] = \
render_to_string('action/includes/partial_filter_delete.html',
{'id': cond_filter.id},
request=request)
return JsonResponse(data)
def delete_condition(request, pk):
"""
Handle the AJAX request to delete a condition. The pk is the condition ID.
:param request: HTTP request
:param pk: condition or filter id
:return: AJAX response to render
"""
# AJAX result
data = {}
# Get the condition
try:
condition = Condition.objects.filter(
Q(action__workflow__user=request.user)
| Q(action__workflow__shared=request.user),
is_filter=False).distinct().get(pk=pk)
except (KeyError, ObjectDoesNotExist):
data['form_is_valid'] = True
data['html_redirect'] = reverse('workflow:index')
return JsonResponse(data)
data = {'form_is_valid': False}
# Treat the two types of requests
if request.method == 'POST':
# If the request has the 'action_content', update the action
action_content = request.POST.get('action_content', None)
if action_content:
condition.action.set_content(action_content)
condition.action.save()
formula, fields = evaluate(condition.formula, NodeEvaluation.EVAL_SQL)
Log.objects.register(
request.user, Log.CONDITION_DELETE, condition.action.workflow, {
'id': condition.id,
'name': condition.name,
'formula': formula,
'formula_fields': fields
})
# Perform the delete operation
condition.delete()
data['form_is_valid'] = True
data['html_redirect'] = ''
return JsonResponse(data)
data['html_form'] = \
render_to_string('action/includes/partial_condition_delete.html',
{'condition_id': condition.id},
request=request)
return JsonResponse(data)
def get_table_row_by_key(workflow, cond_filter, kv_pair, column_names):
"""
Select the set of elements after filtering and with the key=value pair
:param workflow: workflow object to get to the table
:param cond_filter: Condition object to filter the data (or None)
:param kv_pair: A key=value pair to identify the row. Key is suppose to
be unique.
:param column_names: Optional list of column names to select
:return: A dictionary with the (column_name, value) data or None if the
row has not been found
"""
# Create the query
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}" FROM "{1}" WHERE ("{2}" = %s)'.format(
'", "'.join(safe_column_names),
create_table_name(workflow.id),
fix_pctg_in_name(kv_pair[0])
)
fields = [kv_pair[1]]
# See if the action has a filter or not
if cond_filter is not None:
cond_filter, filter_fields = \
evaluate(cond_filter.formula, NodeEvaluation.EVAL_SQL)
query += ' AND (' + cond_filter + ')'
fields = fields + filter_fields
# Execute the query
cursor = connection.cursor()
cursor.execute(query, fields)
# Get the data
qs = cursor.fetchall()
# If there is anything different than one element, return None
if len(qs) != 1:
return None
# Get the only element
qs = qs[0]
# ZIP the values to create a dictionary
return OrderedDict(list(zip(workflow.get_column_names(), qs)))
def num_rows_by_name(table_name, cond_filter=None):
"""
Given a table name, get its number of rows
:param table_name: Table name
:param cond_filter: Condition element used to filter the query
:return: integer
"""
# Initial query with the table name
query = query_count_rows.format(table_name)
fields = []
if cond_filter is not None:
cond_filter, fields = evaluate(cond_filter, NodeEvaluation.EVAL_SQL)
query += ' WHERE ' + cond_filter
cursor = connection.cursor()
cursor.execute(query, fields)
return cursor.fetchone()[0]
def get_filter_query(table_name, column_names, filter_exp):
"""
Given a set of columns and a filter expression, return a pair of SQL query
and params to be executed
:param table_name: Table to query
:param column_names: list of columns to consider or None to consider all
:param filter_exp: Text filter expression
:return: (sql query, sql params)
"""
# Create the query
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}" FROM "{1}"'.format('", "'.join(safe_column_names),
table_name)
# Calculate the first suffix to add to the query
filter_txt = ''
filter_fields = []
if filter_exp:
filter_txt, filter_fields = evaluate(filter_exp,
NodeEvaluation.EVAL_SQL)
# Build the query so far appending the filter and/or the cv_tuples
if filter_txt:
query += ' WHERE '
fields = []
# If there has been a suffix from the filter, add it.
if filter_txt:
query += filter_txt
if filter_fields:
fields.extend(filter_fields)
return (query, fields)
def save_condition_form(request, form, template_name, action, condition,
is_filter):
"""
Function to process the AJAX form to create and update conditions and
filters.
:param request: HTTP request
:param form: Form being used to ask for the fields
:param template_name: Template being used to render the form
:param action: The action to which the condition is attached to
:param condition: Condition object being manipulated or None if creating
:param is_filter: The condition is a filter
:return:
"""
# Ajax response
data = dict()
# In principle we re-render until proven otherwise
data['form_is_valid'] = False
# The condition is new if no value is given
is_new = condition is None
if is_new:
condition_id = -1
else:
condition_id = condition.id
# Context for rendering
context = {
'form': form,
'action_id': action.id,
'condition_id': condition_id,
'add': is_new
}
# If the method is GET or the form is not valid, re-render the page.
if request.method == 'GET' or not form.is_valid():
data['html_form'] = render_to_string(template_name,
context,
request=request)
return JsonResponse(data)
# If the request has the 'action_content' field, update the action
action_content = request.POST.get('action_content', None)
if action_content:
action.set_content(action_content)
action.save()
if is_filter:
# Process the filter form
# If this is a create filter operation, but the action has one,
# flag the error
if is_new and action.get_filter():
# Should not happen. Go back to editing the action
data['form_is_valid'] = True
data['html_redirect'] = ''
return JsonResponse(data)
else:
# Verify that the condition name does not exist yet (Uniqueness FIX)
qs = Condition.objects.filter(name=form.cleaned_data['name'],
action=action,
is_filter=False)
if (is_new and qs.exists()) or \
(not is_new and qs.filter(~Q(id=condition_id)).exists()):
form.add_error(
'name',
_('A condition with that name already exists in this action'))
data['html_form'] = render_to_string(template_name,
context,
request=request)
return JsonResponse(data)
# Verify that the condition name does not collide with column names
workflow = get_workflow(request, action.workflow.id)
if not workflow:
# Workflow is not accessible. Go back to the index.
data['form_is_valid'] = True
data['html_redirect'] = reverse('workflow:index')
return JsonResponse(data)
# New condition name does not collide with column name
if form.cleaned_data['name'] in workflow.get_column_names():
form.add_error('name',
_('A column name with that name already exists.'))
context = {
'form': form,
'action_id': action.id,
'condition_id': condition_id,
'add': is_new
}
data['html_form'] = render_to_string(template_name,
context,
request=request)
return JsonResponse(data)
# New condition name does not collide with attribute names
if form.cleaned_data['name'] in list(workflow.attributes.keys()):
form.add_error('name',
_('The workflow has an attribute with this name.'))
context = {
'form': form,
'action_id': action.id,
'condition_id': condition_id,
'add': is_new
}
data['html_form'] = render_to_string(template_name,
context,
request=request)
return JsonResponse(data)
# If condition name has changed, rename appearances in the content
# field of the action.
if form.old_name and 'name' in form.changed_data:
# Performing string substitution in the content and saving
# TODO: Review!
replacing = '{{% if {0} %}}'
action.content = action.content.replace(
escape(replacing.format(form.old_name)),
escape(replacing.format(condition.name)))
action.save()
# Ok, here we can say that the data in the form is correct.
data['form_is_valid'] = True
# Proceed to update the DB
if is_new:
# Get the condition from the form, but don't commit as there are
# changes pending.
condition = form.save(commit=False)
condition.action = action
condition.is_filter = is_filter
condition.save()
else:
condition = form.save()
# Update the number of selected rows for the conditions
condition.update_n_rows_selected()
# Update the columns field
condition.columns.set(
action.workflow.columns.filter(
name__in=get_variables(condition.formula)))
# Update the condition
condition.save()
# Log the event
formula, _ = evaluate(condition.formula, NodeEvaluation.EVAL_SQL)
if is_new:
if is_filter:
log_type = Log.FILTER_CREATE
else:
log_type = Log.CONDITION_CREATE
else:
if is_filter:
log_type = Log.FILTER_UPDATE
else:
log_type = Log.CONDITION_UPDATE
# Log the event
Log.objects.register(
request.user, log_type, condition.action.workflow, {
'id': condition.id,
'name': condition.name,
'selected_rows': condition.n_rows_selected,
'formula': formula
})
data['html_redirect'] = ''
return JsonResponse(data)
def search_table_rows(workflow_id,
cv_tuples=None,
any_join=True,
order_col_name=None,
order_asc=True,
column_names=None,
pre_filter=None):
"""
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 workflow_id: workflow object to get to the table
:param cv_tuples: A column, value, type tuple to search the value in the
column
: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 column_names: Optional list of column names to select
:param pre_filter: Optional filter condition to pre filter the query set.
the query is built with these terms as requirement AND the cv_tuples.
:return: The resulting query set
"""
# Create the query
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}" FROM "{1}"'.format('", "'.join(safe_column_names),
create_table_name(workflow_id))
# Calculate the first suffix to add to the query
filter_txt = ''
filter_fields = []
if pre_filter:
filter_txt, filter_fields = evaluate(pre_filter,
NodeEvaluation.EVAL_SQL)
if cv_tuples:
likes = []
tuple_fields = []
for name, value, data_type in cv_tuples:
# Make sure we escape the name and search as text
name = fix_pctg_in_name(name)
mod_name = '(CAST("{0}" AS TEXT) LIKE %s)'.format(name)
# Create the second part of the query setting column LIKE '%value%'
likes.append(mod_name)
tuple_fields.append('%' + value + '%')
# Combine the search subqueries
if any_join:
tuple_txt = '(' + ' OR '.join(likes) + ')'
else:
tuple_txt = '(' + ' AND '.join(likes) + ')'
# Build the query so far appending the filter and/or the cv_tuples
if filter_txt or cv_tuples:
query += ' WHERE '
fields = []
# If there has been a suffix from the filter, add it.
if filter_txt:
query += filter_txt
fields.extend(filter_fields)
# If there is a pre-filter, the suffix needs to be "AND" with the ones
# just calculated
if filter_txt and cv_tuples:
query += ' AND '
if cv_tuples:
query += tuple_txt
fields.extend(tuple_fields)
# Add the order if needed
if order_col_name:
query += ' ORDER BY "{0}"'.format(fix_pctg_in_name(order_col_name))
if not order_asc:
query += ' DESC'
# Execute the query
cursor = connection.cursor()
result = cursor.execute(query, fields)
# Get the data
return cursor.fetchall()