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