def port_translate_filter_func(col: Column, operator: FilterOperator,
values: List[Any]) -> Any:
"""
Convert a passed in column, FilterOperator
and list of values into an sqlalchemy expression
"""
return_expression: Any
if operator in (FilterOperator.IN, FilterOperator.NOT_IN):
vals_list = itertools.chain.from_iterable(values)
if operator == FilterOperator.IN.value:
cond = col.in_(vals_list)
elif operator == FilterOperator.NOT_IN.value:
cond = ~(col.in_(vals_list))
return_expression = cond
if len(values) == 1:
value = values[0]
value.sort()
if operator == FilterOperator.EQUALS.value:
return_expression = col.in_(value)
if operator == FilterOperator.GREATER_THAN_OR_EQUALS.value:
return_expression = col >= value[0]
if operator == FilterOperator.GREATER_THAN.value:
return_expression = col > value[0]
if operator == FilterOperator.LESS_THAN.value:
return_expression = col < value[-1]
if operator == FilterOperator.LESS_THAN_OR_EQUALS.value:
return_expression = col <= value[-1]
if operator == FilterOperator.NOT_EQUALS.value:
return_expression = ~col.in_(value)
return return_expression
def default_filter_field(self, field: Column, op: str, value: Any):
"""
Applies a filter on a field.
Notes on 'ne' op:
Example data: [None, 'john', 'roger']
ne:john would return only roger (i.e. nulls excluded)
ne: would return john and roger
Notes on 'search' op:
For some reason, SQLAlchemy uses to_tsquery rather than
plainto_tsquery for the match operator
to_tsquery uses operators (&, |, ! etc.) while
plainto_tsquery tokenises the input string and uses AND between
tokens, hence plainto_tsquery is what we want here
For other database back ends, the behaviour of the match
operator is completely different - see:
http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html
:param field: field name
:param op: 'eq', 'ne', 'gt', 'lt', 'ge', 'le' or 'search'
:param value: comparison value, string or list/tuple
:return:
"""
multiple = isinstance(value, (list, tuple))
if value == "":
value = None
if multiple and op in ("eq", "ne"):
if op == "eq":
return field.in_(value)
elif op == "ne":
return ~field.in_(value)
else:
if multiple:
assert len(value) > 0
value = value[0]
if op == "eq":
return field == value
elif op == "ne":
return field != value
elif op == "gt":
return field > value
elif op == "ge":
return field >= value
elif op == "lt":
return field < value
elif op == "le":
return field <= value
def cidr_translate_filter_func(
col: Column, operator: FilterOperator, values: List[Any]
) -> Any:
"""
Convert a passed in column, FilterOperator and
list of values into an sqlalchemy expression
"""
return_expression: Any
if operator in (FilterOperator.IN, FilterOperator.NOT_IN):
dict_items = [val for val in values if isinstance(val, dict)]
single_values = [val for val in values if not isinstance(val, dict)]
if operator == FilterOperator.IN.value:
cond = col.in_(single_values)
for dictionary in dict_items:
cond = cond | (col <= dictionary["end"]) & (col >= dictionary["start"])
elif operator == FilterOperator.NOT_IN.value:
cond = ~(col.in_(single_values))
for dictionary in dict_items:
cond = cond & (col > dictionary["end"]) & (col < dictionary["start"])
return_expression = cond
if len(values) == 1:
value = values[0]
if operator == FilterOperator.EQUALS.value:
return_expression = (
col == value
if not isinstance(value, dict)
else (col <= value["end"]) & (col >= value["start"])
)
if operator == FilterOperator.GREATER_THAN_OR_EQUALS.value:
return_expression = (
col >= value if not isinstance(value, dict) else col >= value["end"]
)
if operator == FilterOperator.GREATER_THAN.value:
return_expression = (
col > value if not isinstance(value, dict) else col > value["end"]
)
if operator == FilterOperator.LESS_THAN.value:
return_expression = (
col < value if not isinstance(value, dict) else col < value["start"]
)
if operator == FilterOperator.LESS_THAN_OR_EQUALS.value:
return_expression = (
col <= value if not isinstance(value, dict) else col <= value["start"]
)
if operator == FilterOperator.NOT_EQUALS.value:
return_expression = (
col != value
if not isinstance(value, dict)
else (col > value["end"]) | (col < value["start"])
)
return return_expression
def query_items(self, node: colander.SchemaNode, dbsession: Session,
model: type, match_column: Column,
values: set) -> List[object]:
"""Query the actual model to get the concrete SQLAlchemy objects."""
if not values:
# Empty IN queries are not allowed
return []
return ModelSetResultList(
dbsession.query(model).filter(match_column.in_(values)).all())
def build_condition(filter_type: str, column: Column, value):
if filter_type == "like":
if not isinstance(value, list):
value = [value]
response = [column.like(f"%{single_value}%") for single_value in value]
else:
if not isinstance(value, list):
value = [value]
response = [column.in_(value)]
return response
def _get_expression(self, column: Column): # pylint: disable=too-many-return-statements
"""
Given a Column and ColumnFilter return an expression to use as a filter.
:param column: sqlalchemy Column object
:param column_filter: ColumnFilter object
:return: sqlalchemy expression object
"""
try:
self.column = column
except ValueError:
# Ignore bad filters.
return None
value = self.value
filter_type = self.filter_type
if filter_type == 'lt':
return column < value
elif filter_type == 'lte':
return column <= value
elif filter_type == 'eq':
return column == value
elif filter_type == 'neq':
return column != value
elif filter_type == 'gt':
return column > value
elif filter_type == 'gte':
return column >= value
elif filter_type == 'in':
return column.in_(value)
elif filter_type == 'not_in':
return not_(column.in_(value))
elif filter_type == 'bt':
return column.between(*value)
elif filter_type == 'nbt':
return not_(column.between(*value))
return None
def test_port_translate_filter_func_not_in_double():
"""Test to see if the port_translate_filter_func behaves as expected when the NOT_IN
operator is used with two ports"""
input_column = Column("user_ip", Integer)
input_operation = FilterOperator.NOT_IN
input_values = [[443, 80]]
port_translate_filter_response: sqlalchemy.sql.expression.BinaryExpression = ~(
input_column.in_(input_values[0]))
assert port.translate_filter(
input_column, input_operation,
input_values).compare(port_translate_filter_response)
def test_port_translate_filter_func_equals():
"""Test to see if the port_translate_filter_func behaves as expected when the EQUALS
operator is used"""
input_column = Column("user_ip", Integer)
input_operation = FilterOperator.EQUALS
input_values = [[443]]
port_translate_filter_response: sqlalchemy.sql.expression.BinaryExpression = (
input_column.in_(input_values[0]))
assert port.translate_filter(
input_column, input_operation,
input_values).compare(port_translate_filter_response)
def test_cidr_translate_filter_func_not_in_single():
"""Test to see if the cidr_translate_filter_func behaves as expected when the NOT_IN
operator is used with a single IP"""
input_column = Column("user_ip", Integer)
input_operation = FilterOperator.NOT_IN
input_values = [16843009]
cidr_translate_filter_response: sqlalchemy.sql.expression.BinaryExpression = ~(
input_column.in_(input_values))
assert internet_address.translate_filter(
input_column, input_operation,
input_values).compare(cidr_translate_filter_response)
def test_port_translate_filter_func_in_single(app_context: None):
"""Test to see if the port_translate_filter_func behaves as expected when the IN operator
is used with a single port"""
input_column = Column("user_ip", Integer)
input_operation = FilterOperator.IN
input_values = [[443]]
port_translate_filter_response: sqlalchemy.sql.expression.BinaryExpression = (
input_column.in_(input_values[0]))
assert port.translate_filter(
input_column, input_operation,
input_values).compare(port_translate_filter_response)
async def get_many(self,
search_field: Column,
search_value: List[str],
limit: Optional[int] = None,
offset: int = 0) -> List[BaseEntity]:
""" Return a collection of matched records up to the specified limit.
"""
query = (select(self.columns()).where(
search_field.in_(search_value)).offset(offset))
if limit:
query = query.limit(limit)
async with self.engine.acquire() as c:
csr = await c.execute(query)
result = await csr.fetchall()
entity = self.e
return [entity(**record) for record in result]
def test_cidr_translate_filter_func_not_in_double():
"""Test to see if the cidr_translate_filter_func behaves as expected when the NOT_IN
operator is used with two IP's"""
input_column = Column("user_ip", Integer)
input_operation = FilterOperator.NOT_IN
input_values = [{"start": 16843009, "end": 33686018}]
input_condition = ~(input_column.in_([]))
cidr_translate_filter_response: sqlalchemy.sql.expression.BinaryExpression = (
input_condition & (input_column > 33686018) &
(input_column < 16843009))
assert internet_address.translate_filter(
input_column, input_operation,
input_values).compare(cidr_translate_filter_response)
def get_bool_operation(model_property: Column, operator: str,
value: Any) -> Union[bool, ClauseElement]:
if operator == "_eq":
return model_property == value
if operator == "_in":
return model_property.in_(value)
if operator == "_is_null":
return model_property.is_(None)
if operator == "_like":
return model_property.like(value)
if operator == "_neq":
return model_property != value
if operator == "_nin":
return model_property.notin_(value)
if operator == "_nlike":
return model_property.notlike(value)
if operator == "_lt":
return model_property < value
if operator == "_gt":
return model_property > value
if operator == "_lte":
return model_property <= value
if operator == "_gte":
return model_property >= value
raise Exception("Invalid operator")
def query_items(self, node: colander.SchemaNode, dbsession: Session, model: type, match_column: Column, values: set) -> List[object]:
"""Query the actual model to get the concrete SQLAlchemy objects."""
if not values:
# Empty IN queries are not allowed
return []
return ModelSetResultList(dbsession.query(model).filter(match_column.in_(values)).all())
def assignFromYaml(self):
# Read in the YAML config file
with open(self.yaml, 'r') as yml_config_file:
# Load the YAML config file in order
config_yaml = yaml.load(yml_config_file, Loader=yaml.FullLoader)
# Assign the database information
self.type = config_yaml['sql']['type']
if self.type == 'sqlite':
self.filename = config_yaml['sql']['filename']
else:
self.host = config_yaml['sql']['host']
self.user = config_yaml['sql']['user']
if 'database' in config_yaml['sql']:
self.database = config_yaml['sql']['database']
if 'schema' in config_yaml['sql']:
self.schema = config_yaml['sql']['schema']
if config_yaml['sql']['passwd']:
self.passwd = config_yaml['sql']['passwd']
if 'backup' in config_yaml:
self.backup_dir = config_yaml['backup']['dir']
if 'log' in config_yaml['backup']:
self.log_file = config_yaml['backup']['log']
if 'max_days_old' in config_yaml['backup']:
self.max_days_old = int(
config_yaml['backup']['max_days_old'])
if 'max_files' in config_yaml['backup']:
self.max_files = int(config_yaml['backup']['max_files'])
# Create a metadata object
if self.schema: meta = MetaData(schema=self.schema)
else: meta = MetaData()
# Loop the config yaml
for table, column_yaml in config_yaml['database']['tables'].items(
):
# Create the table attribute dict
table_attr_list = []
# Loop the config yaml
for column, attribute_yaml in column_yaml.items():
# Create a column arg list and kwarg dict
col_arg_list = []
col_kwarg_dict = {}
# Assign a String-class arg, if found
if 'String' in attribute_yaml['type']:
if '(' in attribute_yaml[
'type'] and ')' in attribute_yaml['type']:
type_value = int(attribute_yaml['type'].split('(')
[1].split(')')[0])
col_arg_list.append(String(type_value))
else:
col_arg_list.append(String)
# Assign a Boolean-class arg, if found
elif 'Boolean' in attribute_yaml['type']:
col_arg_list.append(Boolean)
# Assign a String-class arg, if found
elif 'Text' in attribute_yaml['type']:
col_arg_list.append(Text)
# Assign a DateTime-class arg, if found
elif 'DateTime' in attribute_yaml['type']:
if 'default' in attribute_yaml or 'onupdate' in attribute_yaml:
raise Exception(
'DateTime does not support default or onupdate options'
)
if '(' in attribute_yaml[
'type'] and ')' in attribute_yaml['type']:
if '(record-created)':
col_arg_list.append(DateTime(timezone=True))
col_kwarg_dict['default'] = func.now()
elif '(record-updated)':
col_arg_list.append(DateTime(timezone=True))
col_kwarg_dict['onupdate'] = func.now()
elif '(record-both)':
col_arg_list.append(DateTime(timezone=True))
col_kwarg_dict['default'] = func.now()
col_kwarg_dict['onupdate'] = func.now()
else:
raise Exception(
'Unable to assign DateTime subtype (%s)' %
attribute_yaml['type'].split('(')[1].split(
')')[0])
else:
col_arg_list.append(DateTime(timezone=True))
# Assign a Date-class arg, if found
elif 'Date' in attribute_yaml['type']:
col_arg_list.append(Date)
# Assign a Integer-class arg, if found
elif 'Integer' in attribute_yaml['type']:
col_arg_list.append(Integer)
# Assign a Integer-class arg, if found
elif 'Numeric' in attribute_yaml['type']:
col_arg_list.append(Numeric)
# Assign a Integer-class arg, if found
elif 'Binary' in attribute_yaml['type']:
col_arg_list.append(LargeBinary)
else:
if 'type' not in attribute_yaml:
raise Exception('No type defined for column (%s)' %
column)
else:
raise Exception(
'Unable to define type (%s) for column (%s)' %
(attribute_yaml['type'], column))
# Loop the optional column attributes
for attribute_arg, attribute_value in attribute_yaml.items(
):
# Assign as a primary_key, if found
if attribute_arg == 'primary_key':
col_kwarg_dict['primary_key'] = True
# Assign as unique, if found
if attribute_arg == 'unique':
col_kwarg_dict['unique'] = True
# Assign as not null, if found
if attribute_arg == 'not_null':
col_kwarg_dict['nullable'] = not attribute_value
if attribute_arg == 'default':
col_kwarg_dict['default'] = attribute_value
if attribute_arg == 'onupdate':
col_kwarg_dict['onupdate'] = attribute_value
# Assign a ForeignKey arg, if found
if attribute_arg == 'foreign_key':
# Create foreign_key args
fk_arg_list = []
fk_kwarg_dict = {}
for fk_arg, fk_value in attribute_value.items():
if fk_arg == 'parent_key':
fk_arg_list.append(fk_value)
else:
fk_kwarg_dict[fk_arg] = fk_value
col_arg_list.append(
ForeignKey(*fk_arg_list, **fk_kwarg_dict))
# Add the column to the table attribute dict
sql_column = Column(column, *col_arg_list,
**col_kwarg_dict)
table_attr_list.append(sql_column)
# Assign a CheckConstraint, if found, return error if constraint is unknown
if 'constraint' in attribute_yaml:
if attribute_yaml['constraint']['check'] == 'EQ':
table_attr_list.append(
CheckConstraint(
sql_column == attribute_yaml['constraint']
['value'],
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'IN':
table_attr_list.append(
CheckConstraint(sql_column.in_(
attribute_yaml['constraint']['value']),
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'LT':
table_attr_list.append(
CheckConstraint(
sql_column <
attribute_yaml['constraint']['value'],
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'GT':
table_attr_list.append(
CheckConstraint(
sql_column >
attribute_yaml['constraint']['value'],
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'BOOL':
table_attr_list.append(
CheckConstraint(sql_column.in_([0, 1]),
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'TF_1':
table_attr_list.append(
CheckConstraint(sql_column.in_(['T', 'F']),
name='%s_check' % column))
elif attribute_yaml['constraint'][
'check'] == 'TF_Text':
table_attr_list.append(
CheckConstraint(sql_column.in_(
['True', 'False']),
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'YN':
table_attr_list.append(
CheckConstraint(sql_column.in_(['Y', 'N']),
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'YN_NS':
table_attr_list.append(
CheckConstraint(sql_column.in_(
['Y', 'N', 'NS']),
name='%s_check' % column))
elif attribute_yaml['constraint']['check'] == 'YesNo':
table_attr_list.append(
CheckConstraint(sql_column.in_(['Yes', 'No']),
name='%s_check' % column))
elif attribute_yaml['constraint'][
'check'] == 'YesNo_NS':
table_attr_list.append(
CheckConstraint(sql_column.in_(
['Yes', 'No', 'NS']),
name='%s_check' % column))
else:
raise Exception(
'Unknown constraint (%s)' %
attribute_yaml['constraint']['check'])
# Assign a column label, if found
if 'label' in attribute_yaml:
self._table_label_to_col[table][
attribute_yaml['label']] = column
self._table_col_to_label[table][
column] = attribute_yaml['label']
# Assign the table class
self._sql_tables[table] = Table(table, meta, *table_attr_list)
self.meta = meta
def _filter(query: Query, column: Column, value: Any) -> Query:
if not isinstance(value, list):
value = [value]
new_query = query.filter(column.in_(value))
return new_query
