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_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_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_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)