def execute_select_on_table(pk, fields, values, 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 fields: List of fields to add to the WHERE clause
:param values: parameters to match the previous fields
:param column_names: optional list of columns to select
:return: QuerySet with the data rows
"""
# Create the query
safe_column_names = ['"' + fix_pctg_in_name(x) + '"'
for x in column_names]
query = 'SELECT {0}'.format(','.join(safe_column_names))
# Add the table
query += ' FROM "{0}"'.format(create_table_name(pk))
# See if the action has a filter or not
cursor = connection.cursor()
if fields:
query += ' WHERE ' + \
' AND '.join(['"{0}" = %s'.format(fix_pctg_in_name(x))
for x in fields])
cursor.execute(query, values)
else:
# Execute the query
cursor.execute(query)
# Get the data
return cursor.fetchall()
def update_row(pk, set_fields, set_values, where_fields, where_values):
"""
Given a primary key, pairs (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 pk: Primary key to detect workflow
:param set_fields: List of field names to be updated
:param set_values: List of values to update the fields of the previous list
:param where_fields: List of fields used to filter the row in the table
:param where_values: List of values of the previous fields to filter the row
:return: The table in the workflow pointed by PK is modified.
"""
# First part of the query with the table name
query = 'UPDATE "{0}"'.format(create_table_name(pk))
# Add the SET field = value clauses
query += ' SET ' + ', '.join(
['"{0}" = %s'.format(fix_pctg_in_name(x)) for x in set_fields])
# And finally add the WHERE clause
query += ' WHERE ' + ' AND '.join(
['"{0}" = %s'.format(fix_pctg_in_name(x)) for x in where_fields])
# Concatenate the values as parameters to the query
parameters = set_values + where_values
# Execute the query
cursor = connection.cursor()
cursor.execute(query, parameters)
connection.commit()
def get_table_row_by_key(workflow, cond_filter, kv_pair, column_names=None):
"""
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
if column_names:
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}"'.format('", "'.join(safe_column_names))
else:
query = 'SELECT *'
# Add the table
query += ' FROM "{0}"'.format(create_table_name(workflow.id))
# Create the second part of the query setting key=value
query += ' WHERE ("{0}" = %s)'.format(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_node_sql(cond_filter.formula)
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(zip(workflow.get_column_names(), qs))
def get_table_cursor(pk, cond_filter, column_names=None):
"""
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
if column_names:
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))
else:
query = 'SELECT * from "{0}"'.format(create_table_name(pk))
# See if the action has a filter or not
fields = []
if cond_filter is not None:
cond_filter, fields = evaluate_node_sql(cond_filter.formula)
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 _op_not_ends_with(self, eval_type):
"""
Process the not_ends_width operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
constant = self.GET_CONSTANT_FN.get(self.node['type'])(
self.node['value'])
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
return (varvalue is not None) and (not varvalue.endswith(constant))
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '("{0}"'.format(varname) + \
' NOT LIKE %s) OR ("{0}" is null)'.format(varname)
result_fields = ["%" + self.node['value']]
return result, result_fields
# Text evaluation
return '{0} does not end with {1}'.format(self.node['field'], constant)
def _op_equal(self, eval_type):
"""
Process the equal operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
constant = self.GET_CONSTANT_FN.get(self.node['type'])(
self.node['value'])
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
return (varvalue is not None) and varvalue == constant
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '("{0}"'.format(varname) + \
' = %s) AND ("{0}" is not null)'.format(varname)
result_fields = [str(constant)]
return result, result_fields
# Text evaluation
return '{0} equal to {1}'.format(self.node['field'], constant)
def _op_greater_or_equal(self, eval_type):
"""
Process the greater_or_equal operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
constant = self.GET_CONSTANT_FN.get(self.node['type'])(
self.node['value'])
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
if self.node['type'] in ('integer', 'double', 'datetime'):
return (varvalue is not None) and varvalue >= constant
raise Exception(
ugettext('Evaluation error: Type {0} not allowed '
'with operator GREATER OR EQUAL').format(
self.node['type']))
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '"{0}"'.format(varname) + ' >= %s'
result_fields = [str(constant)]
return result, result_fields
# Text evaluation
return '{0} is greater than or equal to {1}'.format(
self.node['field'], constant)
def _op_contains(self, eval_type):
"""
Process the contains operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
constant = self.GET_CONSTANT_FN.get(self.node['type'])(
self.node['value'])
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
return (varvalue is not None) and varvalue.find(constant) != -1
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '("{0}"'.format(varname) + \
' LIKE %s) AND ("{0}" is not null)'.format(varname)
result_fields = ["%" + self.node['value'] + "%"]
return result, result_fields
# Text evaluation
return '{0} contains {1}'.format(self.node['field'], constant)
def is_column_table_unique(pk, column_name):
"""
Given a PK, see if the given column has unique values
:param pk:
:param column_name:
:return: Boolean
"""
query = 'SELECT COUNT(DISTINCT "{0}") = count(*) from "{1}"'.format(
fix_pctg_in_name(column_name), create_table_name(pk))
# Get the result
cursor = connection.cursor()
cursor.execute(query, [])
return cursor.fetchone()[0]
def delete_table_row_by_key(workflow_id, kv_pair):
"""
Delete the row in the table attached to a workflow with the given key,
value pairs
:param workflow_id: workflow object to get to the table
:param kv_pair: A key=value pair to identify the row. Key is suppose to
be unique.
:return: Drops that row from the table in the DB
"""
# Create the query
query = 'DELETE FROM "{0}"'.format(create_table_name(workflow_id))
# Create the second part of the query setting key=value
query += ' WHERE ("{0}" = %s)'.format(fix_pctg_in_name(kv_pair[0]))
fields = [kv_pair[1]]
# Execute the query
cursor = connection.cursor()
cursor.execute(query, fields)
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
if column_names:
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}"'.format('", "'.join(safe_column_names))
else:
query = 'SELECT *'
# Add the table
query += ' FROM "{0}"'.format(table_name)
# Calculate the first suffix to add to the query
filter_txt = ''
filter_fields = []
if filter_exp:
filter_txt, filter_fields = evaluate_node_sql(filter_exp)
# 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 _op_is_not_null(self, eval_type):
"""
Process the is_not_null operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
return varvalue is not None
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '(\"{0}\" is not null)'.format(varname)
return result, []
# Text evaluation
return '{0} is not null'.format(self.node['field'])
def _op_not_between(self, eval_type):
"""
Process the not_between operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
if self.node['type'] not in ('integer', 'double', 'datetime'):
raise Exception(
ugettext('Evaluation error: Type {0} not allowed '
'with operator BETWEEN').format(
self.node['type']))
left = self.GET_CONSTANT_FN[self.node['type']](
self.node['value'][0])
right = self.GET_CONSTANT_FN[self.node['type']](
self.node['value'][1])
return (varvalue is not None) and not left <= varvalue <= right
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '"{0}"'.format(varname) + ' NOT BETWEEN %s AND %s'
result_fields = [
str(self.node['value'][0]),
str(self.node['value'][1])
]
return result, result_fields
# Text evaluation
return '{0} is not between {1} and {2}'.format(
self.node['field'], str(self.node['value'][0]),
str(self.node['value'][1]))
def _op_is_empty(self, eval_type):
"""
Process the is_empty operator
:param eval_type: Type of evaluation
:return: Boolean result, SQL query, or text result
"""
if eval_type == self.EVAL_EXP:
# Python evaluation
varvalue = self.get_value()
return (varvalue is not None) and varvalue == ''
if eval_type == self.EVAL_SQL:
# SQL evaluation
varname = fix_pctg_in_name(self.node['field'])
result = '("{0}"'.format(varname) + \
" = '') OR (\"{0}\" is null)".format(varname)
return result, []
# Text evaluation
return '{0} is empty'.format(self.node['field'])
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
if column_names:
safe_column_names = [fix_pctg_in_name(x) for x in column_names]
query = 'SELECT "{0}"'.format('", "'.join(safe_column_names))
else:
query = 'SELECT *'
# Add the table
query += ' FROM "{0}"'.format(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_node_sql(pre_filter)
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 and filter_fields:
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()
cursor.execute(query, fields)
# Get the data
return cursor.fetchall()
def evaluate_node_sql(node):
"""
Given a node representing a query filter
translates the expression into a SQL filter expression.
:param node: Node representing the expression
:return: String with the filter and list of fields to replace
WARNING:
select * from table where variable <> 'value'
does not return records where variable is different from value. It ignores
those that are NULL
Instead the query should be:
select * from table where (variable <> 'value') or (variable is null)
"""
if 'condition' in node:
# Node is a condition, get the values of the sub-clauses
sub_pairs = \
[evaluate_node_sql(x) for x in node['rules']]
if not sub_pairs:
# Nothing has been returned, so it is an empty query
return '', []
# Now combine
if node['condition'] == 'AND':
result = '((' + \
') AND ('.join([x for x, __ in sub_pairs]) + '))'
else:
result = '((' + \
') OR ('.join([x for x, __ in sub_pairs]) + '))'
result_fields = \
list(itertools.chain.from_iterable([x for __, x in sub_pairs]))
if node.get('not', False):
result = '(NOT (' + result + '))'
return result, result_fields
# Get the variable name and duplicate the symbol % in case it is part of
# the variable name (escape needed for SQL processing)
varname = fix_pctg_in_name(node['field'])
# Get the operator
operator = node['operator']
# If the operator is between or not_between, there is a special case,
# the constant cannot be computed because the node['value'] is a pair
constant = None
if node['value'] is not None:
# Calculate the constant value depending on the type
if node['type'] == 'integer':
constant = int(node['value'])
elif node['type'] == 'double':
constant = float(node['value'])
elif node['type'] == 'boolean':
constant = int(node['value'] == '1')
elif node['type'] == 'string':
constant = node['value']
elif node['type'] == 'datetime':
constant = node['value']
else:
raise Exception(
_('No function to translate type {0}').format(node['type']))
# Terminal Node
result_fields = []
if operator == 'equal':
result = '("{0}"'.format(varname) + \
' = %s) AND ("{0}" is not null)'.format(varname)
result_fields = [str(constant)]
elif operator == 'not_equal':
result = '("{0}"'.format(varname) + \
'!= %s) OR ("{0}" is null)'.format(varname)
result_fields = [str(constant)]
elif operator == 'begins_with' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
' LIKE %s) AND ("{0}" is not null)'.format(varname)
result_fields = [node['value'] + "%"]
elif operator == 'not_begins_with' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
' NOT LIKE %s) OR ("{0}" is null)'.format(varname)
result_fields = [node['value'] + "%"]
elif operator == 'contains' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
' LIKE %s) AND ("{0}" is not null)'.format(varname)
result_fields = ["%" + node['value'] + "%"]
elif operator == 'not_contains' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
' NOT LIKE %s) OR ("{0}" is null)'.format(varname)
result_fields = ["%" + node['value'] + "%"]
elif operator == 'ends_with' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
' LIKE %s) AND ("{0}" is not null)'.format(varname)
result_fields = ["%" + node['value']]
elif operator == 'not_ends_width' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
' NOT LIKE %s) OR ("{0}" is null)'.format(varname)
result_fields = ["%" + node['value']]
elif operator == 'is_empty' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
" = '') OR (\"{0}\" is null)".format(varname)
elif operator == 'is_not_empty' and node['type'] == 'string':
result = '("{0}"'.format(varname) + \
" != '') AND (\"{0}\" is not null)".format(varname)
elif operator == 'less' and \
(node['type'] == 'integer' or node['type'] == 'double'
or node['type'] == 'datetime'):
result = '"{0}"'.format(varname) + ' < %s'
result_fields = [str(constant)]
elif operator == 'less_or_equal' and \
(node['type'] == 'integer' or node['type'] == 'double'
or node['type'] == 'datetime'):
result = '"{0}"'.format(varname) + ' <= %s'
result_fields = [str(constant)]
elif operator == 'greater' and \
(node['type'] == 'integer' or node['type'] == 'double'
or node['type'] == 'datetime'):
result = '"{0}"'.format(varname) + ' > %s'
result_fields = [str(constant)]
elif operator == 'greater_or_equal' and \
(node['type'] == 'integer' or node['type'] == 'double'
or node['type'] == 'datetime'):
result = '"{0}"'.format(varname) + ' >= %s'
result_fields = [str(constant)]
elif operator == 'between' and \
(node['type'] == 'integer' or node['type'] == 'double'
or node['type'] == 'datetime'):
result = '"{0}"'.format(varname) + ' BETWEEN %s AND %s'
result_fields = [str(node['value'][0]), str(node['value'][1])]
elif operator == 'not_between' and \
(node['type'] == 'integer' or node['type'] == 'double'
or node['type'] == 'datetime'):
result = '"{0}"'.format(varname) + ' NOT BETWEEN %s AND %s'
result_fields = [str(node['value'][0]), str(node['value'][1])]
else:
raise Exception(
_('Type, operator, field {0}, {1}, {2} not supported yet').format(
node['type'], operator, varname))
if node.get('not', False):
raise Exception(_('Negation found in unexpected location'))
return result, result_fields
