示例#1
0
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
示例#2
0
    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
示例#3
0
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
示例#4
0
 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
示例#6
0
    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
示例#7
0
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)
示例#8
0
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)
示例#9
0
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)
示例#10
0
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)
示例#11
0
 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]
示例#12
0
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)
示例#13
0
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")
示例#14
0
 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())
示例#15
0
    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
示例#16
0
 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