def get_projectable_attribute( self, alias, column_name, attrpath, cast=None, **kwargs ): """ :returns: An attribute store in a JSON field of the give column """ entity = _get_column(column_name, alias)[(attrpath)] if cast is None: entity = entity elif cast=='f': entity = entity.cast(Float) elif cast=='i': entity = entity.cast(Integer) elif cast=='b': entity = entity.cast(Boolean) elif cast=='t': entity = entity.astext elif cast=='j': entity = entity.cast(JSONB) elif cast=='d': entity = entity.cast(DateTime) else: raise InputValidationError( "Unkown casting key {}".format(cast) ) return entity
def get_filter_expr_from_attributes( self, operator, value, attr_key, column=None, column_name=None, alias=None): def cast_according_to_type(path_in_json, value): if isinstance(value, bool): type_filter = jsonb_typeof(path_in_json)=='boolean' casted_entity = path_in_json.cast(Boolean) elif isinstance(value, (int, float)): type_filter = jsonb_typeof(path_in_json)=='number' casted_entity = path_in_json.cast(Float) elif isinstance(value, dict) or value is None: type_filter = jsonb_typeof(path_in_json)=='object' casted_entity = path_in_json.cast(JSONB) # BOOLEANS? elif isinstance(value, dict): type_filter = jsonb_typeof(path_in_json)=='array' casted_entity = path_in_json.cast(JSONB) # BOOLEANS? elif isinstance(value, (str, unicode)): type_filter = jsonb_typeof(path_in_json)=='string' casted_entity = path_in_json.astext elif value is None: type_filter = jsonb_typeof(path_in_json)=='null' casted_entity = path_in_json.cast(JSONB) # BOOLEANS? elif isinstance(value, datetime): # type filter here is filter whether this attributes stores # a string and a filter whether this string # is compatible with a datetime (using a regex) # - What about historical values (BC, or before 1000AD)?? # - Different ways to represent the timezone type_filter = jsonb_typeof(path_in_json)=='string' regex_filter = path_in_json.astext.op( "SIMILAR TO" )("\d\d\d\d-[0-1]\d-[0-3]\dT[0-2]\d:[0-5]\d:\d\d\.\d+((\+|\-)\d\d:\d\d)?") type_filter = and_(type_filter, regex_filter) casted_entity = path_in_json.cast(DateTime) else: raise Exception('Unknown type {}'.format(type(value))) return type_filter, casted_entity if column is None: column = _get_column(column_name, alias) database_entity = column[tuple(attr_key)] if operator == '==': type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity == value) elif operator == '>': type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity > value) elif operator == '<': type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity < value) elif operator in ('>=', '=>'): type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity >= value) elif operator in ('<=', '=<'): type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity <= value) elif operator == 'of_type': # http://www.postgresql.org/docs/9.5/static/functions-json.html # Possible types are object, array, string, number, boolean, and null. valid_types = ('object', 'array', 'string', 'number', 'boolean', 'null') if value not in valid_types: raise InputValidationError( "value {} for of_type is not among valid types\n" "{}".format(value, valid_types) ) expr = jsonb_typeof(database_entity) == value elif operator == 'like': type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity.like(value)) elif operator == 'ilike': type_filter, casted_entity = cast_according_to_type(database_entity, value) expr = and_(type_filter, casted_entity.ilike(value)) elif operator == 'in': type_filter, casted_entity = cast_according_to_type(database_entity, value[0]) expr = and_(type_filter, casted_entity.in_(value)) elif operator == 'contains': expr = database_entity.cast(JSONB).contains(value) elif operator == 'has_key': expr = database_entity.cast(JSONB).has_key(value) elif operator == 'of_length': expr= and_( jsonb_typeof(database_entity) == 'array', jsonb_array_length(database_entity.cast(JSONB)) == value ) elif operator == 'longer': expr = and_( jsonb_typeof(database_entity) == 'array', jsonb_array_length(database_entity.cast(JSONB)) > value ) elif operator == 'shorter': expr = and_( jsonb_typeof(database_entity) == 'array', jsonb_array_length(database_entity.cast(JSONB)) < value ) else: raise InputValidationError( "Unknown operator {} for filters in JSON field".format(operator) ) return expr
def get_filter_expr( self, operator, value, attr_key, is_attribute, alias=None, column=None, column_name=None ): """ Applies a filter on the alias given. Expects the alias of the ORM-class on which to filter, and filter_spec. Filter_spec contains the specification on the filter. Expects: :param operator: The operator to apply, see below for further details :param value: The value for the right side of the expression, the value you want to compare with. :param path: The path leading to the value :param attr_key: Boolean, whether the value is in a json-column, or in an attribute like table. Implemented and valid operators: * for any type: * == (compare single value, eg: '==':5.0) * in (compare whether in list, eg: 'in':[5, 6, 34] * for floats and integers: * > * < * <= * >= * for strings: * like (case - sensitive), for example 'like':'node.calc.%' will match node.calc.relax and node.calc.RELAX and node.calc. but not node.CALC.relax * ilike (case - unsensitive) will also match node.CaLc.relax in the above example .. note:: The character % is a reserved special character in SQL, and acts as a wildcard. If you specifically want to capture a ``%`` in the string, use: ``_%`` * for arrays and dictionaries (only for the SQLAlchemy implementation): * contains: pass a list with all the items that the array should contain, or that should be among the keys, eg: 'contains': ['N', 'H']) * has_key: pass an element that the list has to contain or that has to be a key, eg: 'has_key':'N') * for arrays only (SQLAlchemy version): * of_length * longer * shorter All the above filters invoke a negation of the expression if preceded by **~**:: # first example: filter_spec = { 'name' : { '~in':[ 'halle', 'lujah' ] } # Name not 'halle' or 'lujah' } # second example: filter_spec = { 'id' : { '~==': 2 } } # id is not 2 """ expr = None if operator.startswith('~'): negation = True operator = operator.lstrip('~') elif operator.startswith('!'): negation = True operator = operator.lstrip('!') else: negation = False if operator in ('longer', 'shorter', 'of_length'): if not isinstance(value, int): raise InputValidationError( "You have to give an integer when comparing to a length" ) elif operator in ('like', 'ilike'): if not isinstance(value, basestring): raise InputValidationError( "Value for operator {} has to be a string (you gave {})" "".format(operator, value) ) elif operator == 'in': value_type_set = set([type(i) for i in value]) if len(value_type_set) > 1: raise InputValidationError( '{} contains more than one type'.format(value) ) elif len(value_type_set) == 0: raise InputValidationError( '{} contains is an empty list'.format(value) ) elif operator in ('and', 'or'): expressions_for_this_path = [] for filter_operation_dict in value: for newoperator, newvalue in filter_operation_dict.items(): expressions_for_this_path.append( self.get_filter_expr( newoperator, newvalue, attr_key=attr_key, is_attribute=is_attribute, alias=alias, column=column, column_name=column_name ) ) if operator == 'and': expr = and_(*expressions_for_this_path) elif operator == 'or': expr = or_(*expressions_for_this_path) if expr is None: if is_attribute: expr = self.get_filter_expr_from_attributes( operator, value, attr_key, column=column, column_name=column_name, alias=alias ) else: if column is None: if (alias is None) and (column_name is None): raise Exception( "I need to get the column but do not know \n" "the alias and the column name" ) column = _get_column(column_name, alias) expr = self._get_filter_expr_from_column(operator, value, column) if negation: return not_(expr) return expr