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