def test_format_select_query():
"""_format should correctly format the entire query
"""
# Test simple selects
assert Query('*').from_('x')._format_select_query() == \
"SELECT * FROM x;"
assert Query('a', 'b').from_('x')._format_select_query() == \
"SELECT a, b FROM x;"
# Test limit
assert Query('*').from_('x').limit(100) \
._format_select_query() == "SELECT * FROM x LIMIT 100;"
# Test order
assert Query('*').from_('x').order('time', 'asc') \
._format_select_query() == "SELECT * FROM x ORDER BY time ASC;"
assert Query('*').from_('x').order('time', 'desc') \
._format_select_query() == "SELECT * FROM x ORDER BY time DESC;"
# Test functions
assert Query(Count('a')).from_('x') \
._format_select_query() == "SELECT COUNT(a) FROM x;"
assert Query(Sum(Count('a'))).from_('x') \
._format_select_query() == "SELECT SUM(COUNT(a)) FROM x;"
# Test where, comparators and value formatting
assert Query('*').from_('x').where(a='something') \
._format_select_query() == "SELECT * FROM x WHERE a = 'something';"
assert Query('*').from_('x').where(a='something', b=1) \
._format_select_query() == \
"SELECT * FROM x WHERE a = 'something' AND b = 1;"
assert Query('*').from_('x').where(a__ne='something') \
._format_select_query() == "SELECT * FROM x WHERE a != 'something';"
assert Query('*').from_('x').where(a=True, b=False) \
._format_select_query() == \
"SELECT * FROM x WHERE a = true AND b = false;"
assert Query('*').from_('x').where(a=True, b=False) \
._format_select_query() == \
"SELECT * FROM x WHERE a = true AND b = false;"
assert Query('*').from_('x').where(a__lt=4, b__gt=6.0) \
._format_select_query() == "SELECT * FROM x WHERE a < 4 AND b > 6.0;"
# Test group by
assert Query('*').from_('x').group_by('a') \
._format_select_query() == "SELECT * FROM x GROUP BY a;"
assert Query('*').from_('x').group_by('a', 'b') \
._format_select_query() == "SELECT * FROM x GROUP BY a, b;"
q = Query('*').from_('x') \
.group_by(time=timedelta(hours=1))
assert q._format_select_query() == "SELECT * FROM x GROUP BY time(1h);"
q = Query('*').from_('x') \
.group_by('a', 'b', time=timedelta(hours=1))
assert q._format_select_query() == "SELECT * FROM x GROUP BY time(1h), a, b;"
# Test something really crazy
fmt = "SELECT COUNT(a), SUM(b), PERCENTILE(d, 99) FROM x "
fmt += "WHERE e = false AND f != true AND g < 4 AND h > 5 "
fmt += "GROUP BY time(1h), a, b fill(0) "
fmt += "LIMIT 100 ORDER BY time ASC;"
q = Query(Count('a'), Sum('b'), Percentile('d', 99)) \
.from_('x') \
.where(e=False, f__ne=True, g__lt=4, h__gt=5) \
.group_by('a', 'b', time=timedelta(minutes=60), fill=True) \
.limit(100).order('time', 'asc')
assert q._format_select_query() == fmt
def test_format_select_query():
"""_format should correctly format the entire query
"""
# Test simple selects
assert Query('*').from_('x')._format_select_query() == \
"SELECT * FROM x;"
assert Query('a', 'b').from_('x')._format_select_query() == \
"SELECT a, b FROM x;"
# Test limit
assert Query('*').from_('x').limit(100) \
._format_select_query() == "SELECT * FROM x LIMIT 100;"
# Test order
assert Query('*').from_('x').order('time', 'asc') \
._format_select_query() == "SELECT * FROM x ORDER BY time ASC;"
assert Query('*').from_('x').order('time', 'desc') \
._format_select_query() == "SELECT * FROM x ORDER BY time DESC;"
# Test functions
assert Query(Count('a')).from_('x') \
._format_select_query() == "SELECT COUNT(a) FROM x;"
assert Query(Sum(Count('a'))).from_('x') \
._format_select_query() == "SELECT SUM(COUNT(a)) FROM x;"
# Test where, comparators and value formatting
assert Query('*').from_('x').where(a='something') \
._format_select_query() == "SELECT * FROM x WHERE a = 'something';"
assert Query('*').from_('x').where(a='something', b=1) \
._format_select_query() == \
"SELECT * FROM x WHERE a = 'something' AND b = 1;"
assert Query('*').from_('x').where(a__ne='something') \
._format_select_query() == "SELECT * FROM x WHERE a != 'something';"
assert Query('*').from_('x').where(a=True, b=False) \
._format_select_query() == \
"SELECT * FROM x WHERE a = true AND b = false;"
assert Query('*').from_('x').where(a=True, b=False) \
._format_select_query() == \
"SELECT * FROM x WHERE a = true AND b = false;"
assert Query('*').from_('x').where(a__lt=4, b__gt=6.0) \
._format_select_query() == "SELECT * FROM x WHERE a < 4 AND b > 6.0;"
# Test group by
assert Query('*').from_('x').group_by('a') \
._format_select_query() == "SELECT * FROM x GROUP BY a;"
assert Query('*').from_('x').group_by('a', 'b') \
._format_select_query() == "SELECT * FROM x GROUP BY a, b;"
q = Query('*').from_('x') \
.group_by(time=timedelta(hours=1))
assert q._format_select_query() == "SELECT * FROM x GROUP BY time(1h);"
q = Query('*').from_('x') \
.group_by('a', 'b', time=timedelta(hours=1))
assert q._format_select_query(
) == "SELECT * FROM x GROUP BY time(1h), a, b;"
# Test something really crazy
fmt = "SELECT COUNT(a), SUM(b), PERCENTILE(d, 99) FROM x "
fmt += "WHERE e = false AND f != true AND g < 4 AND h > 5 "
fmt += "GROUP BY time(1h), a, b fill(0) "
fmt += "LIMIT 100 ORDER BY time ASC;"
q = Query(Count('a'), Sum('b'), Percentile('d', 99)) \
.from_('x') \
.where(e=False, f__ne=True, g__lt=4, h__gt=5) \
.group_by('a', 'b', time=timedelta(minutes=60), fill=True) \
.limit(100).order('time', 'asc')
assert q._format_select_query() == fmt
