def test_path_or(): hs_filter = 'siteRef->geoPostalCode or siteRef->geoCountry' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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' ) 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' ) LIMIT 1 """) _check_pg(sql_request)
def test_4path(): hs_filter = 'siteRef->ownerRef->a->b' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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' LIMIT 1 """) _check_pg(sql_request)
def test_and_or_path(): hs_filter = '(a->b or c->d) and (e->f or g->h)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") assert sql_request == textwrap.dedent(f"""\ -- (a->b or c->d) and (e->f or g->h) ( ( 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->'a' = t2.entity->'id' AND t2.entity ? 'b' ) 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->'c' = t4.entity->'id' AND t4.entity ? 'd' ) ) INTERSECT ( ( SELECT t5.entity FROM haystack as t5 INNER JOIN haystack AS t6 ON '{FAKE_NOW.isoformat()}' BETWEEN t5.start_datetime AND t5.end_datetime AND t5.customer_id='customer' AND '{FAKE_NOW.isoformat()}' BETWEEN t6.start_datetime AND t6.end_datetime AND t6.customer_id='customer' AND t5.entity->'e' = t6.entity->'id' AND t6.entity ? 'f' ) UNION ( SELECT t7.entity FROM haystack as t7 INNER JOIN haystack AS t8 ON '{FAKE_NOW.isoformat()}' BETWEEN t7.start_datetime AND t7.end_datetime AND t7.customer_id='customer' AND '{FAKE_NOW.isoformat()}' BETWEEN t8.start_datetime AND t8.end_datetime AND t8.customer_id='customer' AND t7.entity->'g' = t8.entity->'id' AND t8.entity ? 'h' ) ) LIMIT 1 """) _check_pg(sql_request)
def test_equal_uri(): hs_filter = 'a == `http://l`' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") assert sql_request == textwrap.dedent(f"""\ -- a == `http://l` 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 SUBSTRING(t1.entity->>'a',3) = 'http://l' LIMIT 1 """) _check_pg(sql_request)
def test_greater_or_equal_quantity(): hs_filter = 'temp >= 55400°' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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 SUBSTRING(t1.entity->>'temp',3)::float >= 55400.0 LIMIT 1 """) _check_pg(sql_request)
def test_lower_or_equal_number(): hs_filter = 'geoPostalCode <= 55400' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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 SUBSTRING(t1.entity->>'geoPostalCode',3)::float <= 55400.0 LIMIT 1 """) _check_pg(sql_request)
def test_not_tag(): hs_filter = 'not site' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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 NOT t1.entity ? 'site' LIMIT 1 """) _check_pg(sql_request)
def test_and_not_andtag_andtag(): hs_filter = '(not his and not point) and (not site and not ref)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") assert sql_request == textwrap.dedent(f"""\ -- (not his and not point) and (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 NOT t1.entity ?& array['his', 'point', 'site', 'ref'] LIMIT 1 """) _check_pg(sql_request)
def test_and_andtag_rtag(): hs_filter = '(site and ref) and his' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") assert sql_request == textwrap.dedent(f"""\ -- (site and ref) and his 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 ?& array['site', 'ref', 'his'] LIMIT 1 """) _check_pg(sql_request)
def test_equal_int(): hs_filter = 'a == 1' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") assert sql_request == textwrap.dedent(f"""\ -- a == 1 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' LIMIT 1 """) _check_pg(sql_request)
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") 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 SUBSTRING(t1.entity->>'a',3,25)::TIMESTAMP = TIMESTAMP '1977-04-22T01:00:00+00:00' LIMIT 1 """) _check_pg(sql_request)
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_pg(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_has_and_equal(): hs_filter = 'site and geoPostal==78000' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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') AND (t1.entity->>'geoPostal' = 'n:78000.000000') LIMIT 1 """) _check_pg(sql_request)
def test_or_and(): hs_filter = 'site or (elect and point)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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') OR (t1.entity ?& array['elect', 'point']) LIMIT 1 """) _check_pg(sql_request)
def test_and_or(): hs_filter = '(a or b) and (c or d)' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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 ?| array['a', 'b']) AND (t1.entity ?| array['c', 'd']) LIMIT 1 """) _check_pg(sql_request)
def test_and_with_not(): hs_filter = 'site and his and not geoPostal' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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 ?& array['site', 'his']) AND (NOT t1.entity ? 'geoPostal') LIMIT 1 """) _check_pg(sql_request)
def test_path_equal_quantity(): hs_filter = 'siteRef->temp == 55400deg' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") assert sql_request == textwrap.dedent(f"""\ -- siteRef->temp == 55400deg 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->>'temp' = 'n:55400.000000 deg' LIMIT 1 """) _check_pg(sql_request)
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") 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 """) _check_pg(sql_request)
def test_2path_greater_quantity(): hs_filter = 'siteRef->temp >= 55400°' sql_request = sql_filter('haystack', hs_filter, FAKE_NOW, 1, "customer") 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 SUBSTRING(t2.entity->>'temp',3)::float >= 55400.0 LIMIT 1 """) _check_pg(sql_request)