def test_svl_to_sql_xy_sort_split_by():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a SORT and a SPLIT BY.
"""
svl_plot = {
"data": "bigfoot",
"type": "line",
"x": {
"field": "latitude",
"sort": "ASC"
},
"y": {
"field": "temperature_mid",
"agg": "AVG"
},
"split_by": {
"field": "classification"
},
}
truth_query = ("SELECT latitude AS x, AVG(temperature_mid) AS y, "
"classification AS split_by "
"FROM bigfoot "
"GROUP BY latitude, classification "
"ORDER BY split_by, x ASC")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_color_by():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a COLOR BY.
"""
svl_plot = {
"data": "bigfoot",
"type": "scatter",
"x": {
"field": "latitude"
},
"y": {
"field": "temperature_mid"
},
"color_by": {
"field": "humidity"
},
}
truth_query = (
"SELECT latitude AS x, temperature_mid AS y, humidity AS color_by "
"FROM bigfoot")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_sort_x():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a sort clause on x.
"""
svl_plot = {
"data": "bigfoot",
"type": "line",
"x": {
"field": "latitude",
"sort": "ASC"
},
"y": {
"field": "temperature_mid",
"agg": "AVG"
},
}
truth_query = ("SELECT latitude AS x, AVG(temperature_mid) AS y "
"FROM bigfoot "
"GROUP BY latitude "
"ORDER BY x ASC")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_sort_y():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a sort clause on y.
"""
svl_plot = {
"data": "bigfoot",
"type": "bar",
"x": {
"field": "classification"
},
"y": {
"agg": "COUNT",
"sort": "DESC",
"field": "classification"
},
}
truth_query = ("SELECT classification AS x, COUNT(classification) AS y "
"FROM bigfoot "
"GROUP BY classification "
"ORDER BY y DESC")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_split_by_agg():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a split by field and an aggregation.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "date",
"temporal": "YEAR"
},
"y": {
"field": "temperature",
"agg": "MAX"
},
"split_by": {
"field": "classification"
},
}
truth_query = ("SELECT STRFTIME('%Y', date) AS x, MAX(temperature) AS y, "
"classification AS split_by FROM bigfoot "
"GROUP BY STRFTIME('%Y', date), classification")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_split_by_transform():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a split by field with a transform modifier.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "date",
"temporal": "YEAR"
},
"y": {
"field": "latitude",
"agg": "MAX"
},
"split_by": {
"transform":
"CASE WHEN temperature > 90 THEN 'hot' "
"ELSE 'not_hot' END"
},
}
truth_query = (
"SELECT STRFTIME('%Y', date) AS x, MAX(latitude) AS y, "
"CASE WHEN temperature > 90 THEN 'hot' ELSE 'not_hot' END AS split_by "
"FROM bigfoot GROUP BY STRFTIME('%Y', date), "
"CASE WHEN temperature > 90 THEN 'hot' ELSE 'not_hot' END")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy():
""" Tests that the svl_to_sql_xy function returns the correct value when
there are no aggregations or split_bys.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "latitude"
},
"y": {
"field": "temperature_mid"
},
}
truth_query = "SELECT latitude AS x, temperature_mid AS y FROM bigfoot"
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_count():
""" Tests that the svl_to_sql_xy function returns the correct value when
one of the aggregations is a count.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "classification"
},
"y": {
"agg": "COUNT"
},
}
truth_query = ("SELECT classification AS x, COUNT(*) AS y "
"FROM bigfoot GROUP BY classification")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_filter():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a filter.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "latitude"
},
"y": {
"field": "temperature_mid"
},
"filter": "latitude < 84",
}
truth_query = ("SELECT latitude AS x, temperature_mid AS y FROM bigfoot "
"WHERE latitude < 84")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_temporal():
""" Tests that the svl_to_sql_xy function returns the correct value when one
of the fields has a temporal transformation.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "date",
"temporal": "YEAR"
},
"y": {
"field": "temperature"
},
}
truth_query = ("SELECT STRFTIME('%Y', date) AS x, temperature AS y "
"FROM bigfoot")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_agg_y_sum():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a SUM aggregation on y.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"agg": "SUM",
"field": "temperature"
},
"y": {
"field": "classification"
},
}
truth_query = ("SELECT SUM(temperature) AS x, classification AS y "
"FROM bigfoot GROUP BY classification")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_agg_x():
""" Tests that the svl_to_sql_xy function returns the correct value when x is
aggregated.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "classification"
},
"y": {
"agg": "MAX",
"field": "temperature"
},
}
truth_query = ("SELECT classification AS x, MAX(temperature) AS y "
"FROM bigfoot GROUP BY classification")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_temporal_agg():
""" Tests that the svl_to_sql_xy function returns the correct value when one
field is a temporal transformation and the other is an aggregation.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "date",
"temporal": "YEAR"
},
"y": {
"agg": "COUNT"
},
}
truth_query = (
"SELECT STRFTIME('%Y', date) AS x, COUNT(*) AS y FROM bigfoot "
"GROUP BY STRFTIME('%Y', date)")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_split_by():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a split by field.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "date"
},
"y": {
"field": "temperature"
},
"split_by": {
"field": "classification"
},
}
truth_query = ("SELECT date AS x, temperature AS y, "
"classification AS split_by FROM bigfoot")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
def test_svl_to_sql_xy_split_by_temporal():
""" Tests that the svl_to_sql_xy function returns the correct value when
there's a split by field with a temporal modifier.
"""
svl_plot = {
"data": "bigfoot",
"x": {
"field": "latitude"
},
"y": {
"field": "temperature",
"agg": "MAX"
},
"split_by": {
"field": "date",
"temporal": "YEAR"
},
}
truth_query = ("SELECT latitude AS x, MAX(temperature) AS y, "
"STRFTIME('%Y', date) AS split_by FROM bigfoot "
"GROUP BY latitude, STRFTIME('%Y', date)")
answer_query = svl_to_sql_xy(svl_plot)
assert truth_query == answer_query
