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