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