def test_sql_validation():
"""
Test that sql gets validated.
"""
with pytest.raises(pglast.parser.ParseError):
sql = "elect foo from mooses"
pretty_sql(sql)
def test_versioned_site_sql(diff_reporter):
"""
Verify the SQL for a versioned-site spatial unit.
"""
su = make_spatial_unit("versioned-site")
sql = pretty_sql(su.get_query())
verify(sql, diff_reporter)
def test_daily_location_1_sql(diff_reporter):
"""
Simple daily location query returns the expected SQL string.
"""
dl = daily_location("2016-01-01", "2016-01-02")
sql = pretty_sql(dl.get_query())
verify(sql, diff_reporter)
def test_sql_prettified():
"""
Test that sql is prettified as expected.
"""
sql = "select foo, beta, frog from (select * from octagon where mooses in ('bees')) z limit 9"
prettied = "SELECT foo,\n beta,\n frog\nFROM (SELECT *\n FROM octagon\n WHERE mooses IN ('bees')) AS z\nLIMIT 9"
assert pretty_sql(sql) == prettied
def test_event_count_5_sql(diff_reporter):
"""
Event count query with hours spanning midnight returns the expected sql string.
"""
ec = EventCount(start="2016-01-03", stop="2016-01-06", hours=(22, 4))
sql = pretty_sql(ec.get_query())
diff_reporter(sql)
def __init__(self, sql: str, column_names: Union[List[str], Set[str]]):
self.sql = pretty_sql(sql)
seen = {} # Dedupe the column names but preserve order
self._column_names = [
seen.setdefault(x, x) for x in column_names if x not in seen
]
super().__init__()
def test_event_count_4_sql(diff_reporter):
"""
Event count query with restricted hours returns the expected sql string.
"""
ec = EventCount(start="2016-01-02", stop="2016-01-04", hours=(12, 18))
sql = pretty_sql(ec.get_query())
diff_reporter(sql)
def test_event_count_3_sql(diff_reporter):
"""
Event count query with implicit start date returns the expected sql string.
"""
ec = EventCount(start=None, stop="2016-01-02")
sql = pretty_sql(ec.get_query())
diff_reporter(sql)
def test_daily_location_4_sql(diff_reporter):
"""
Regression test; this verifies the SQL statement for the test below (which checks the resulting dataframe)
"""
subset_query = CustomQuery(
"SELECT * FROM (VALUES ('dr9xNYK006wykgXj')) as tmp (subscriber)",
column_names=["subscriber"],
)
dl = daily_location(
"2016-01-05",
table="events.calls",
hours=(22, 6),
subscriber_subset=subset_query,
)
sql = pretty_sql(dl.get_query())
verify(sql, diff_reporter)
def test_daily_location_6_sql(diff_reporter):
"""
Regression test; this verifies the SQL statement for the test below (which checks the resulting dataframe)
"""
subset_query = CustomQuery(
"""
SELECT outgoing, datetime, duration, msisdn AS subscriber
FROM events.calls
WHERE datetime::date = '2016-01-01' AND duration > 2000
""",
["subscriber"],
)
dl = daily_location("2016-01-03",
table="events.calls",
subscriber_subset=subset_query)
sql = pretty_sql(dl.get_query())
verify(sql, diff_reporter)
def test_daily_location_1_sql(diff_reporter):
"""
Daily location query with non-default parameters returns the expected data.
"""
subset_query = CustomQuery(
"SELECT DISTINCT msisdn AS subscriber FROM events.calls WHERE msisdn in ('GNLM7eW5J5wmlwRa', 'e6BxY8mAP38GyAQz', '1vGR8kp342yxEpwY')",
column_names=["subscriber"],
)
dl = daily_location(
"2016-01-05",
spatial_unit=make_spatial_unit("cell"),
hours=(23, 5),
method="last",
subscriber_subset=subset_query,
)
sql = pretty_sql(dl.get_query())
diff_reporter(sql)
def test_daily_location_3_sql(diff_reporter):
"""
Daily location query with non-default parameters returns the expected data.
"""
subset_query = CustomQuery(
"SELECT DISTINCT msisdn AS subscriber FROM events.calls WHERE msisdn in ('GNLM7eW5J5wmlwRa', 'e6BxY8mAP38GyAQz', '1vGR8kp342yxEpwY')",
column_names=["subscriber"],
)
dl = daily_location(
"2016-01-05",
level="cell",
hours=(23, 5),
method="last",
# subscriber_identifier="imei",
# column_name="admin2pcod",
# ignore_nulls=False,
subscriber_subset=subset_query,
)
sql = pretty_sql(dl.get_query())
verify(sql, diff_reporter)
def test_events_tables_union_1_sql(diff_reporter):
"""
EventsTablesUnion returns the expected sql string.
"""
etu = EventsTablesUnion(
start="2016-01-02",
stop="2016-01-03",
tables=["events.calls"],
columns=[
"datetime",
"duration",
"id",
"location_id",
"msisdn",
"msisdn_counterpart",
"outgoing",
"tac",
],
)
sql = pretty_sql(etu.get_query())
diff_reporter(sql)
def test_daily_location_2_sql(diff_reporter):
"""
Daily location query with non-default parameters returns the expected SQL string.
"""
dl = daily_location(
"2016-01-04",
level="admin2",
hours=(3, 9),
method="most-common",
subscriber_identifier="imei",
column_name="admin2pcod",
ignore_nulls=False,
subscriber_subset=[
"2GJxeNazvlgZbqj6",
"7qKmzkeMbmk5nOa0",
"8dpPLR15XwR7jQyN",
"1NqnrAB9bRd597x2",
],
)
sql = pretty_sql(dl.get_query())
verify(sql, diff_reporter)
