def test_4path(): hs_filter = 'siteRef->ownerRef->a->b' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- siteRef->ownerRef->a->b SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t2.start_datetime AND t2.end_datetime AND t2.customer_id='customer' AND t1.entity->'$.siteRef' = t2.entity->'$.id') ) INNER JOIN haystack AS t3 ON (('{FAKE_NOW.isoformat()}' BETWEEN t3.start_datetime AND t3.end_datetime AND t3.customer_id='customer' AND t2.entity->'$.ownerRef' = t3.entity->'$.id') ) INNER JOIN haystack AS t4 ON (('{FAKE_NOW.isoformat()}' BETWEEN t4.start_datetime AND t4.end_datetime AND t4.customer_id='customer' AND t3.entity->'$.a' = t4.entity->'$.id') AND t4.entity->'$.b' IS NOT NULL ) LIMIT 1 """)
def test_path_and(): hs_filter = 'siteRef->geoPostalCode and siteRef->geoCountry' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- siteRef->geoPostalCode and siteRef->geoCountry SELECT t6.entity FROM haystack as t6 WHERE entity->'$.id' in ( SELECT t2.entity FROM haystack as t2 INNER JOIN haystack AS t3 ON (('{FAKE_NOW.isoformat()}' BETWEEN t2.start_datetime AND t2.end_datetime AND t2.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t3.start_datetime AND t3.end_datetime AND t3.customer_id='customer' AND t2.entity->'$.siteRef' = t3.entity->'$.id') AND t3.entity->'$.geoPostalCode' IS NOT NULL ) ) AND entity->'$.id' in ( SELECT t4.entity FROM haystack as t4 INNER JOIN haystack AS t5 ON (('{FAKE_NOW.isoformat()}' BETWEEN t4.start_datetime AND t4.end_datetime AND t4.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t5.start_datetime AND t5.end_datetime AND t5.customer_id='customer' AND t4.entity->'$.siteRef' = t5.entity->'$.id') AND t5.entity->'$.geoCountry' IS NOT NULL ) ) LIMIT 1 """)
def test_path_or(): hs_filter = 'siteRef->geoPostalCode or siteRef->geoCountry' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- siteRef->geoPostalCode or siteRef->geoCountry SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t2.start_datetime AND t2.end_datetime AND t2.customer_id='customer' AND t1.entity->'$.siteRef' = t2.entity->'$.id') AND t2.entity->'$.geoPostalCode' IS NOT NULL ) UNION SELECT t3.entity FROM haystack as t3 INNER JOIN haystack AS t4 ON (('{FAKE_NOW.isoformat()}' BETWEEN t3.start_datetime AND t3.end_datetime AND t3.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t4.start_datetime AND t4.end_datetime AND t4.customer_id='customer' AND t3.entity->'$.siteRef' = t4.entity->'$.id') AND t4.entity->'$.geoCountry' IS NOT NULL ) LIMIT 1 """)
def test_path_and_or(): hs_filter = '(a->b or c->d) and (e->f or g->h)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- (a->b or c->d) and (e->f or g->h) SELECT t10.entity FROM haystack as t10 WHERE entity->'$.id' in ( SELECT t2.entity FROM haystack as t2 INNER JOIN haystack AS t3 ON (('{FAKE_NOW.isoformat()}' BETWEEN t2.start_datetime AND t2.end_datetime AND t2.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t3.start_datetime AND t3.end_datetime AND t3.customer_id='customer' AND t2.entity->'$.a' = t3.entity->'$.id') AND t3.entity->'$.b' IS NOT NULL ) UNION SELECT t4.entity FROM haystack as t4 INNER JOIN haystack AS t5 ON (('{FAKE_NOW.isoformat()}' BETWEEN t4.start_datetime AND t4.end_datetime AND t4.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t5.start_datetime AND t5.end_datetime AND t5.customer_id='customer' AND t4.entity->'$.c' = t5.entity->'$.id') AND t5.entity->'$.d' IS NOT NULL ) ) AND entity->'$.id' in ( SELECT t6.entity FROM haystack as t6 INNER JOIN haystack AS t7 ON (('{FAKE_NOW.isoformat()}' BETWEEN t6.start_datetime AND t6.end_datetime AND t6.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t7.start_datetime AND t7.end_datetime AND t7.customer_id='customer' AND t6.entity->'$.e' = t7.entity->'$.id') AND t7.entity->'$.f' IS NOT NULL ) UNION SELECT t8.entity FROM haystack as t8 INNER JOIN haystack AS t9 ON (('{FAKE_NOW.isoformat()}' BETWEEN t8.start_datetime AND t8.end_datetime AND t8.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t9.start_datetime AND t9.end_datetime AND t9.customer_id='customer' AND t8.entity->'$.g' = t9.entity->'$.id') AND t9.entity->'$.h' IS NOT NULL ) ) LIMIT 1 """)
def test_equal(): hs_filter = 'geoPostal==78000' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- geoPostal==78000 SELECT t1.entity FROM haystack as t1 WHERE ((\'{FAKE_NOW.isoformat()}\' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id=\'customer\') AND t1.entity->\'$.geoPostal\' = \'n:78000.000000\' ) LIMIT 1 """)
def test_equal_xstr(): hs_filter = 'a == hex("deadbeef")' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- a == hex("deadbeef") SELECT t1.entity FROM haystack as t1 WHERE ((\'{FAKE_NOW.isoformat()}\' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id=\'customer\') AND t1.entity->\'$.a\' = \'x:hex:deadbeef\' ) LIMIT 1 """)
def test_equal_datetime(): hs_filter = 'a == 1977-04-22T01:00:00-00:00' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- a == 1977-04-22T01:00:00-00:00 SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND t1.entity->'$.a' = 't:1977-04-22T01:00:00+00:00 UTC' ) LIMIT 1 """)
def test_not_tag(): hs_filter = 'not site' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- not site SELECT t1.entity FROM haystack as t1 WHERE ((\'{FAKE_NOW.isoformat()}\' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id=\'customer\') AND t1.entity->\'$.site\' IS NULL ) LIMIT 1 """)
def test_equal_number(): hs_filter = 'geoPostalCode==1111' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- geoPostalCode==1111 SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND t1.entity->'$.geoPostalCode' = 'n:1111.000000' ) LIMIT 1 """)
def test_equal_str(): hs_filter = 'a == "abc"' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- a == "abc" SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND t1.entity->'$.a' = 's:abc' ) LIMIT 1 """)
def test_greater_or_equal_quantity(): hs_filter = 'temp >= 55400°' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- temp >= 55400° SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND CAST(SUBSTR(t1.entity->'$.temp',3) AS REAL) >= 55400.0 ) LIMIT 1 """)
def test_lower_or_equal_number(): hs_filter = 'geoPostalCode <= 55400' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- geoPostalCode <= 55400 SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND CAST(SUBSTR(t1.entity->'$.geoPostalCode',3) AS REAL) <= 55400.0 ) LIMIT 1 """)
def test_select_with_id(): hs_filter = 'id==@p:demo:r:23a44701-3a62fd7a' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- id==@p:demo:r:23a44701-3a62fd7a SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND t1.entity->'$.id' LIKE '"r:p:demo:r:23a44701-3a62fd7a%"' ) LIMIT 1 """)
def test_and_not_ltag_rtag(): hs_filter = 'not site and not ref' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- not site and not ref SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND (t1.entity->'$.site' IS NULL AND t1.entity->'$.ref' IS NULL ) ) LIMIT 1 """)
def test_has_and_equal(): hs_filter = 'site and geoPostal==78000' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- site and geoPostal==78000 SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND (t1.entity->'$.site' IS NOT NULL AND t1.entity->'$.geoPostal' = 'n:78000.000000' ) ) LIMIT 1 """)
def test_2path_greater_quantity(): hs_filter = 'siteRef->temp >= 55400°' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- siteRef->temp >= 55400° SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ('{FAKE_NOW.isoformat()}' BETWEEN t2.start_datetime AND t2.end_datetime AND t2.customer_id='customer' AND t1.entity->'$.siteRef' = t2.entity->'$.id') AND CAST(SUBSTR(t2.entity->'$.temp',3) AS REAL) >= 55400.0 ) LIMIT 1 """)
def test_or_and(): hs_filter = 'site or (elect and point)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- site or (elect and point) SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND (t1.entity->'$.site' IS NOT NULL OR (t1.entity->'$.elect' IS NOT NULL AND t1.entity->'$.point' IS NOT NULL ) ) ) LIMIT 1 """)
def test_and_with_not(): hs_filter = 'site and his and not geoPostal' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- site and his and not geoPostal SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ((t1.entity->'$.site' IS NOT NULL AND t1.entity->'$.his' IS NOT NULL ) AND t1.entity->'$.geoPostal' IS NULL ) ) LIMIT 1 """)
def test_combine_and(): hs_filter = '(a==1 and b==1) or (c==2 and d==3)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- (a==1 and b==1) or (c==2 and d==3) SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ((t1.entity->'$.a' = 'n:1.000000' AND t1.entity->'$.b' = 'n:1.000000' ) OR (t1.entity->'$.c' = 'n:2.000000' AND t1.entity->'$.d' = 'n:3.000000' ) ) ) LIMIT 1 """)
def test_and_or(): hs_filter = '(a or b) and (c or d)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- (a or b) and (c or d) SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ((t1.entity->'$.a' IS NOT NULL OR t1.entity->'$.b' IS NOT NULL ) AND (t1.entity->'$.c' IS NOT NULL OR t1.entity->'$.d' IS NOT NULL ) ) ) LIMIT 1 """)
def test_and_andtag_andtag(): hs_filter = '(his and point) and (site and ref)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_mysql(sql_request) assert sql_request == textwrap.dedent(f"""\ -- (his and point) and (site and ref) SELECT t1.entity FROM haystack as t1 WHERE (('{FAKE_NOW.isoformat()}' BETWEEN t1.start_datetime AND t1.end_datetime AND t1.customer_id='customer') AND ((t1.entity->'$.his' IS NOT NULL AND t1.entity->'$.point' IS NOT NULL ) AND (t1.entity->'$.site' IS NOT NULL AND t1.entity->'$.ref' IS NOT NULL ) ) ) LIMIT 1 """)