def test_4path(): hs_filter = 'siteRef->ownerRef->a->b' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- siteRef->ownerRef->a->b SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t2.start_datetime) AND datetime(t2.end_datetime) AND t2.customer_id='customer' AND json_object(json(t1.entity),'$.siteRef') = json_object(json(t2.entity),'$.id')) ) INNER JOIN haystack AS t3 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t3.start_datetime) AND datetime(t3.end_datetime) AND t3.customer_id='customer' AND json_object(json(t2.entity),'$.ownerRef') = json_object(json(t3.entity),'$.id')) ) INNER JOIN haystack AS t4 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t4.start_datetime) AND datetime(t4.end_datetime) AND t4.customer_id='customer' AND json_object(json(t3.entity),'$.a') = json_object(json(t4.entity),'$.id')) AND json_extract(json(t4.entity),'$.b') 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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- siteRef->geoPostalCode or siteRef->geoCountry SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t2.start_datetime) AND datetime(t2.end_datetime) AND t2.customer_id='customer' AND json_object(json(t1.entity),'$.siteRef') = json_object(json(t2.entity),'$.id')) AND json_extract(json(t2.entity),'$.geoPostalCode') IS NOT NULL ) UNION SELECT t3.entity FROM haystack as t3 INNER JOIN haystack AS t4 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t3.start_datetime) AND datetime(t3.end_datetime) AND t3.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t4.start_datetime) AND datetime(t4.end_datetime) AND t4.customer_id='customer' AND json_object(json(t3.entity),'$.siteRef') = json_object(json(t4.entity),'$.id')) AND json_extract(json(t4.entity),'$.geoCountry') IS NOT NULL ) LIMIT 1 """)
def test_complex(): hs_filter = '(a->b or c->d) and e or (f and g->h)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- (a->b or c->d) and e or (f and g->h) SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t2.start_datetime) AND datetime(t2.end_datetime) AND t2.customer_id='customer' AND json_object(json(t1.entity),'$.a') = json_object(json(t2.entity),'$.id')) AND json_extract(json(t2.entity),'$.b') IS NOT NULL ) UNION SELECT t3.entity FROM haystack as t3 INNER JOIN haystack AS t4 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t3.start_datetime) AND datetime(t3.end_datetime) AND t3.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t4.start_datetime) AND datetime(t4.end_datetime) AND t4.customer_id='customer' AND json_object(json(t3.entity),'$.c') = json_object(json(t4.entity),'$.id')) AND json_extract(json(t4.entity),'$.d') IS NOT NULL ) INTERSECT SELECT t5.entity FROM haystack as t5 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t5.start_datetime) AND datetime(t5.end_datetime) AND t5.customer_id='customer') AND json_extract(json(t5.entity),'$.e') IS NOT NULL ) UNION SELECT t6.entity FROM haystack as t6 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t6.start_datetime) AND datetime(t6.end_datetime) AND t6.customer_id='customer') AND json_extract(json(t6.entity),'$.f') IS NOT NULL ) INTERSECT SELECT t7.entity FROM haystack as t7 INNER JOIN haystack AS t8 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t7.start_datetime) AND datetime(t7.end_datetime) AND t7.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t8.start_datetime) AND datetime(t8.end_datetime) AND t8.customer_id='customer' AND json_object(json(t7.entity),'$.g') = json_object(json(t8.entity),'$.id')) AND json_extract(json(t8.entity),'$.h') IS NOT NULL ) LIMIT 1 """)
def test_equal_str(): hs_filter = 'a == "abc"' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- a == "abc" SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND json_extract(json(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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- temp >= 55400° SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND CAST(substr(json_extract(json(t1.entity),'$.temp'),3) AS REAL) >= 55400.0 ) LIMIT 1 """)
def test_equal_number(): hs_filter = 'geoPostalCode==1111' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- geoPostalCode==1111 SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND json_extract(json(t1.entity),'$.geoPostalCode') == 'n:1111.000000' ) LIMIT 1 """)
def test_lower_number(): hs_filter = 'geoPostalCode < 55400' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- geoPostalCode < 55400 SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND CAST(substr(json_extract(json(t1.entity),'$.geoPostalCode'),3) AS REAL) < 55400.0 ) LIMIT 1 """)
def test_not_tag(): hs_filter = 'not site' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- not site SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND json_extract(json(t1.entity),'$.site') IS NULL ) 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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- id==@p:demo:r:23a44701-3a62fd7a SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND json_extract(json(t1.entity),'$.id') LIKE 'r:p:demo:r:23a44701-3a62fd7a%' ) 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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- site and geoPostal==78000 SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (json_extract(json(t1.entity),'$.site') IS NOT NULL AND json_extract(json(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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- siteRef->temp >= 55400° SELECT t1.entity FROM haystack as t1 INNER JOIN haystack AS t2 ON ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t2.start_datetime) AND datetime(t2.end_datetime) AND t2.customer_id='customer' AND json_object(json(t1.entity),'$.siteRef') = json_object(json(t2.entity),'$.id')) AND CAST(substr(json_extract(json(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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- site or (elect and point) SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (json_extract(json(t1.entity),'$.site') IS NOT NULL OR (json_extract(json(t1.entity),'$.elect') IS NOT NULL AND json_extract(json(t1.entity),'$.point') IS NOT NULL ) ) ) LIMIT 1 """)
def test_and_ltag_andtag(): hs_filter = 'his and (site and ref)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") _check_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- his and (site and ref) SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND (json_extract(json(t1.entity),'$.his') IS NOT NULL AND (json_extract(json(t1.entity),'$.site') IS NOT NULL AND json_extract(json(t1.entity),'$.ref') IS NOT NULL ) ) ) 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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- (a or b) and (c or d) SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND ((json_extract(json(t1.entity),'$.a') IS NOT NULL OR json_extract(json(t1.entity),'$.b') IS NOT NULL ) AND (json_extract(json(t1.entity),'$.c') IS NOT NULL OR json_extract(json(t1.entity),'$.d') IS NOT 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_sqlite(sql_request) assert sql_request == textwrap.dedent("""\ -- (a==1 and b==1) or (c==2 and d==3) SELECT t1.entity FROM haystack as t1 WHERE ((datetime('2020-10-01T00:00:00+00:00') BETWEEN datetime(t1.start_datetime) AND datetime(t1.end_datetime) AND t1.customer_id='customer') AND ((json_extract(json(t1.entity),'$.a') == 'n:1.000000' AND json_extract(json(t1.entity),'$.b') == 'n:1.000000' ) OR (json_extract(json(t1.entity),'$.c') == 'n:2.000000' AND json_extract(json(t1.entity),'$.d') == 'n:3.000000' ) ) ) LIMIT 1 """)