def test_others(db):
# Create records
all_records = [
Record(integer=1),
Record(float=1.0),
]
add_records(db, all_records)
# Test integer
expression = parse_boolean_search('integer==1')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.integer == 1
# Test float with float
expression = parse_boolean_search('float==1.0')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.float == 1.0
# Test float with integer
expression = parse_boolean_search('float==1')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.float == 1.0
# Delete records
delete_records(db, all_records)
def test_field_names(db):
# Create records
grandparent = GrandParent(name='GrandParent')
parent = Parent(name='Parent', grandparent=grandparent)
record = Record(string='Record', parent=parent)
db.session.add(record)
db.session.commit()
# Test non-hierarchical name
expression = parse_boolean_search('string==Record')
record = Record.query.filter(expression.filter(Record)).first()
assert record is not None
assert record.string=='Record'
# Test level-1 hierarchy name
expression = parse_boolean_search('parent.name==Parent')
record = Record.query.filter(expression.filter(Record)).first()
assert record is not None
assert record.string=='Record'
# Test level-2 hierarchy name
expression = parse_boolean_search('parent.grandparent.name==GrandParent')
record = Record.query.filter(expression.filter(Record)).first()
assert record is not None
assert record.string=='Record'
# Delete records
db.session.delete(record)
db.session.commit()
def test_boolean_expressions():
# Test precedence
expression = parse_boolean_search('a=1 or b=2 or not c=3 and d=4 and e=5')
assert repr(expression) == 'or_(a=1, b=2, and_(not_(c=3), d=4, e=5))'
# Test parenthesis
expression = parse_boolean_search('a=1 or not b=2 and c=3')
assert repr(expression) == 'or_(a=1, and_(not_(b=2), c=3))'
expression = parse_boolean_search('a=1 or not (b=2 and c=3)')
assert repr(expression) == 'or_(a=1, not_(and_(b=2, c=3)))'
expression = parse_boolean_search('(a=1 or not b=2) and c=3')
assert repr(expression) == 'and_(or_(a=1, not_(b=2)), c=3)'
# Test all operators
expression = parse_boolean_search('a < 1 or a <= 1 or a = 1 or a == 1 or a >= 1 or a > 1 or a != 1')
assert repr(expression) == 'or_(a<1, a<=1, a=1, a==1, a>=1, a>1, a!=1)'
# Test example
expression = parse_boolean_search('field1=*something* and not (field2==1 or field3<=10.0)')
assert repr(expression) == 'and_(field1=*something*, not_(or_(field2==1, field3<=10.0)))'
# Test range
expr = parse_boolean_search('a >= 4 and a < 6')
assert repr(expr) == 'and_(a>=4, a<6)'
# Test between
expr = parse_boolean_search('a between 1 and 2')
assert repr(expr) == 'abetween1and2'
expr = parse_boolean_search('a between 1 and 2 or c > 10')
assert repr(expr) == 'or_(abetween1and2, c>10)'
def test_boolean_functions():
# Test function expression
expr = parse_boolean_search('func(a > 5) < 40')
assert repr(expr) == 'func(a>5)<40'
assert hasattr(expr, 'fxn_name')
assert expr.fxn_name == 'func'
assert repr(expr.condition) == 'a>5'
assert expr.value == '40'
assert not hasattr(expr, 'conditions')
# Test function cone
expr = parse_boolean_search('cone(3.4, 5.6, 1)')
assert repr(expr) == 'cone(3.4,5.6,1)'
assert expr.fxn_name == 'cone'
assert expr.value == '1'
assert expr.coords == ['3.4', '5.6']
assert not hasattr(expr, 'conditions')
def test_boolean_functions():
# Test function expression
expr = parse_boolean_search('func(a > 5) < 40')
assert repr(expr) == 'func(a>5)<40'
assert hasattr(expr, 'fxn_name')
assert expr.fxn_name == 'func'
assert repr(expr.condition) == 'a>5'
assert expr.value == '40'
assert not hasattr(expr, 'conditions')
# Test function cone
expr = parse_boolean_search('cone(3.4, 5.6, 1)')
assert repr(expr) == 'cone(3.4,5.6,1)'
assert expr.fxn_name == 'cone'
assert expr.value == '1'
assert expr.coords == ['3.4', '5.6']
assert not hasattr(expr, 'conditions')
def test_exceptions(db):
# Create records
all_records = [
Record(integer=1),
Record(float=1.0),
]
add_records(db, all_records)
# Test invalid field name
try:
expression = parse_boolean_search('XYZ==text')
records = Record.query.filter(expression.filter(Record)).all()
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Test invalid operator
try:
expression = parse_boolean_search('a:1')
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Test invalid integer
try:
expression = parse_boolean_search('integer==text')
records = Record.query.filter(expression.filter(Record)).all()
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Test invalid float
try:
expression = parse_boolean_search('float==text')
records = Record.query.filter(expression.filter(Record)).all()
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Delete records
delete_records(db, all_records)
def test_condition_nobase():
expr = parse_boolean_search('a < 1 and b > 2')
cond = expr.conditions[0]
assert repr(cond) == 'a<1'
assert cond.name == 'a'
assert cond.basename is None
assert cond.fullname == 'a'
assert cond.op == '<'
assert cond.value == '1'
def test_boolean_fxn_andor(expression):
expr = parse_boolean_search(expression)
assert hasattr(expr, 'conditions')
assert hasattr(expr, 'functions')
assert expr.conditions
assert expr.functions
assert len(expr.conditions) == 1
assert len(expr.functions) == 1
assert repr(expr.conditions[0]) == 'b<2'
def test_boolean_fxn_andor(expression):
expr = parse_boolean_search(expression)
assert hasattr(expr, 'conditions')
assert hasattr(expr, 'functions')
assert expr.conditions
assert expr.functions
assert len(expr.conditions) == 1
assert len(expr.functions) == 1
assert repr(expr.conditions[0]) == 'b<2'
def test_condition_base():
expr = parse_boolean_search('table.a < 1 and b > 2')
cond = expr.conditions[0]
assert repr(cond) == 'table.a<1'
assert cond.name == 'a'
assert cond.basename == 'table'
assert cond.fullname == 'table.a'
assert cond.op == '<'
assert cond.value == '1'
def test_exceptions(db):
# Create records
all_records = [Record(integer=1), Record(float=1.0)]
add_records(db, all_records)
# Test invalid field name
try:
expression = parse_boolean_search("XYZ==text")
records = Record.query.filter(expression.filter(Record)).all()
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Test invalid operator
try:
expression = parse_boolean_search("a:1")
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Test invalid integer
try:
expression = parse_boolean_search("integer==text")
records = Record.query.filter(expression.filter(Record)).all()
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Test invalid float
try:
expression = parse_boolean_search("float==text")
records = Record.query.filter(expression.filter(Record)).all()
assert False # An exception should have skipped this statement
except BooleanSearchException as e:
# print(e)
pass
# Delete records
delete_records(db, all_records)
def test_field_names(db):
# Create records
grandparent = GrandParent(name='GrandParent')
parent = Parent(name='Parent', grandparent=grandparent)
record = Record(string='Record', parent=parent)
db.session.add(record)
db.session.commit()
# Test non-hierarchical name
expression = parse_boolean_search('string==Record')
record = Record.query.filter(expression.filter(Record)).first()
assert record is not None
assert record.string == 'Record'
def test_fields_across_relationships(db):
# Create records
grandparent = GrandParent(name='GrandParent')
parent = Parent(name='Parent', grandparent=grandparent)
record = Record(string='Record', parent=parent)
db.session.add(record)
db.session.commit()
# Test level-1 hierarchy name
expression = parse_boolean_search('parent.name==Parent')
record = Record.query.filter(expression.filter(Record)).first()
assert record is not None
assert record.string == 'Record'
# Test level-2 hierarchy name
expression = parse_boolean_search('parent.grandparent.name==GrandParent')
record = Record.query.filter(expression.filter(Record)).first()
assert record is not None
assert record.string == 'Record'
# Delete records
db.session.delete(record)
db.session.commit()
def test_boolean_expressions():
# Test precedence
expression = parse_boolean_search('a=1 or b=2 or not c=3 and d=4 and e=5')
assert repr(expression) == 'or_(a=1, b=2, and_(not_(c=3), d=4, e=5))'
# Test parenthesis
expression = parse_boolean_search('a=1 or not b=2 and c=3')
assert repr(expression) == 'or_(a=1, and_(not_(b=2), c=3))'
expression = parse_boolean_search('a=1 or not (b=2 and c=3)')
assert repr(expression) == 'or_(a=1, not_(and_(b=2, c=3)))'
expression = parse_boolean_search('(a=1 or not b=2) and c=3')
assert repr(expression) == 'and_(or_(a=1, not_(b=2)), c=3)'
# Test all operators
expression = parse_boolean_search(
'a < 1 or a <= 1 or a = 1 or a == 1 or a >= 1 or a > 1 or a != 1')
assert repr(expression) == 'or_(a<1, a<=1, a=1, a==1, a>=1, a>1, a!=1)'
# Test example
expression = parse_boolean_search(
'field1=*something* and not (field2==1 or field3<=10.0)')
assert repr(
expression
) == 'and_(field1=*something*, not_(or_(field2==1, field3<=10.0)))'
# Test range
expr = parse_boolean_search('a >= 4 and a < 6')
assert repr(expr) == 'and_(a>=4, a<6)'
# Test between
expr = parse_boolean_search('a between 1 and 2')
assert repr(expr) == 'abetween1and2'
expr = parse_boolean_search('a between 1 and 2 or c > 10')
assert repr(expr) == 'or_(abetween1and2, c>10)'
def test_boolean_expressions():
# Test precedence
expression = parse_boolean_search("a=1 or b=2 or not c=3 and d=4 and e=5")
assert repr(expression) == "or_(a=1, b=2, and_(not_(c=3), d=4, e=5))"
# Test parenthesis
expression = parse_boolean_search("a=1 or not b=2 and c=3")
assert repr(expression) == "or_(a=1, and_(not_(b=2), c=3))"
expression = parse_boolean_search("a=1 or not (b=2 and c=3)")
assert repr(expression) == "or_(a=1, not_(and_(b=2, c=3)))"
expression = parse_boolean_search("(a=1 or not b=2) and c=3")
assert repr(expression) == "and_(or_(a=1, not_(b=2)), c=3)"
# Test all operators
expression = parse_boolean_search("a < 1 or a <= 1 or a = 1 or a == 1 or a >= 1 or a > 1 or a != 1")
assert repr(expression) == "or_(a<1, a<=1, a=1, a==1, a>=1, a>1, a!=1)"
# Test example
expression = parse_boolean_search("field1=*something* and not (field2==1 or field3<=10.0)")
assert repr(expression) == "and_(field1=*something*, not_(or_(field2==1, field3<=10.0)))"
def test_boolean_params():
expr = parse_boolean_search('a < 1 and b > 2')
assert 'a' in expr.params
assert 'b' in expr.params
assert sorted(['a', 'b']) == sorted(expr.uniqueparams)
assert expr.conditions is not []
def test_boolean_params():
expr = parse_boolean_search('a < 1 and b > 2')
assert 'a' in expr.params
assert 'b' in expr.params
assert sorted(['a', 'b']) == sorted(expr.uniqueparams)
assert expr.conditions is not []
def test_condition_bitwise(op, value, exp):
par = 'table.a {0} {1}'.format(op, value)
expr = parse_boolean_search(par)
assert expr.value == exp
def set_filter(self, searchfilter=None):
''' Parses a filter string and adds it into the query.
Parses a natural language string filter into the appropriate SQL
filter syntax. String is a boolean join of one or more conditons
of the form "PARAMETER_NAME OPERAND VALUE"
Parameter names must be uniquely specified. For example, nsa.z is
a unique parameter name in the database and can be specified thusly.
On the other hand, name is not a unique parameter name in the database,
and must be clarified with the desired table.
Parameter Naming Convention:
NSA redshift == nsa.z
IFU name == ifu.name
Pipeline name == pipeline_info.name
Allowed Joins:
AND | OR | NOT
In the absence of parantheses, the precedence of
joins follow: NOT > AND > OR
Allowed Operands:
== | != | <= | >= | < | > | =
Notes:
Operand == maps to a strict equality (x == 5 --> x is equal to 5)
Operand = maps to SQL LIKE
(x = 5 --> x contains the string 5; x = '%5%')
(x = 5* --> x starts with the string 5; x = '5%')
(x = *5 --> x ends with the string 5; x = '%5')
Parameters:
searchfilter (str):
A (natural language) string containing the filter conditions
in the query; written as you would say it.
Example:
>>> # Filter string
>>> filter = "nsa.z < 0.012 and ifu.name = 19*"
>>> # Converts to
>>> and_(nsa.z<0.012, ifu.name=19*)
>>> # SQL syntax
>>> mangasampledb.nsa.z < 0.012 AND lower(mangadatadb.ifudesign.name) LIKE lower('19%')
>>> # Filter string
>>> filter = 'cube.plate < 8000 and ifu.name = 19 or not (nsa.z > 0.1 or not cube.ra > 225.)'
>>> # Converts to
>>> or_(and_(cube.plate<8000, ifu.name=19), not_(or_(nsa.z>0.1, not_(cube.ra>225.))))
>>> # SQL syntax
>>> mangadatadb.cube.plate < 8000 AND lower(mangadatadb.ifudesign.name) LIKE lower(('%' || '19' || '%'))
>>> OR NOT (mangasampledb.nsa.z > 0.1 OR mangadatadb.cube.ra <= 225.0)
'''
if searchfilter:
# if params is a string, then parse and filter
if type(searchfilter) == str or type(searchfilter) == unicode:
searchfilter = self._check_shortcuts_in_filter(searchfilter)
try:
parsed = parse_boolean_search(searchfilter)
except BooleanSearchException as e:
raise MarvinError('Your boolean expression contained a syntax error: {0}'.format(e))
else:
raise MarvinError('Input parameters must be a natural language string!')
# update the parameters dictionary
self.searchfilter = searchfilter
self._parsed = parsed
self._checkParsed()
self.strfilter = str(parsed)
self.filterparams.update(parsed.params)
filterkeys = [key for key in parsed.uniqueparams if key not in self.params]
self.params.extend(filterkeys)
# print filter
if not self.quiet:
print('Your parsed filter is: ')
print(parsed)
# Perform local vs remote modes
if self.mode == 'local':
# Pass into Marvin Forms
try:
self._setForms()
except KeyError as e:
self.reset()
raise MarvinError('Could not set parameters. Multiple entries found for key. Be more specific: {0}'.format(e))
elif self.mode == 'remote':
# Is it possible to build a query remotely but still allow for user manipulation?
pass
def test_strings(db):
all_records = [Record(string="abc"), Record(string="abcx"), Record(string="xabc"), Record(string="xabcx")]
add_records(db, all_records)
expression = parse_boolean_search("string==abc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.string == "abc"
expression = parse_boolean_search("string!=abc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string != "abc"
expression = parse_boolean_search("not string==abc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string != "abc"
expression = parse_boolean_search("string<xabc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 2
for record in records:
assert record.string < "xabc"
expression = parse_boolean_search("string<=xabc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string <= "xabc"
expression = parse_boolean_search("string>xabc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.string > "xabc"
expression = parse_boolean_search("string>=xabc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 2
for record in records:
assert record.string >= "xabc"
expression = parse_boolean_search("string=abc")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 4
for record in records:
assert "abc" in record.string
expression = parse_boolean_search("string=*x and string=x*")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.string[0:1] == "x" and record.string[-1:] == "x"
expression = parse_boolean_search("string=*x or string=x*")
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string[0:1] == "x" or record.string[-1:] == "x"
delete_records(db, all_records)
def test_strings(db):
all_records = [
Record(string='abc'),
Record(string='abcx'),
Record(string='xabc'),
Record(string='xabcx'),
]
add_records(db, all_records)
expression = parse_boolean_search('string==abc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.string == 'abc'
expression = parse_boolean_search('string!=abc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string != 'abc'
expression = parse_boolean_search('not string==abc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string != 'abc'
expression = parse_boolean_search('string<xabc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 2
for record in records:
assert record.string < 'xabc'
expression = parse_boolean_search('string<=xabc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string <= 'xabc'
expression = parse_boolean_search('string>xabc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.string > 'xabc'
expression = parse_boolean_search('string>=xabc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 2
for record in records:
assert record.string >= 'xabc'
expression = parse_boolean_search('string=abc')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 4
for record in records:
assert 'abc' in record.string
expression = parse_boolean_search('string=*x and string=x*')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 1
for record in records:
assert record.string[0:1] == 'x' and record.string[-1:] == 'x'
expression = parse_boolean_search('string=*x or string=x*')
records = Record.query.filter(expression.filter(Record)).all()
assert len(records) == 3
for record in records:
assert record.string[0:1] == 'x' or record.string[-1:] == 'x'
delete_records(db, all_records)
def test_condition_bitwise(op, value, exp):
par = 'table.a {0} {1}'.format(op, value)
expr = parse_boolean_search(par)
assert expr.value == exp
