def test_percentage_where(self): "query a percentage of records w/ where condition" q = Query() q.select("total") q.tables("from orders") q.where("total", "total > 10") q.sortby('total') self.assertEqual(q({"limit":"10%"}), "with _l as (select total from orders where total > 10), _ll as (select total from _l order by total asc limit (select round(count(*)*0.1) from _l)) select total__into__ from _ll")
def test_where(self): "query: manages where clause" q = Query() q.select("column_1", None, "name") q.where("name", "name like 'this'") q.tables("from here") q.into(False) self.assertEqual("SELECT"+q({})[6:], "SELECT column_1 as name from here where name like 'this'")
def test_arguments(self): "query: inserts arguments" q = Query() q.select("col", "%(agg)s", "total") q.where("total", "total > 50") q.tables("from here") q.into(False) self.assertEqual("SELECT"+q(dict(agg="sum"))[6:], "SELECT sum(col) as total from here where total > 50")
def test_where_multi_same(self): "query: multiple where of same column" q = Query() q.select("column_1", None, "name") q.where("name", "name like 'this'") q.where("name", "name < 10") q.tables("from here") q.into(False) self.assertEqual("SELECT"+q({})[6:], "SELECT column_1 as name from here where name like 'this' and name < 10")
def test_agg_filter(self): q = Query() q.select("total") q.select("store") q.agg("total") q.agg("total") q.where("total", "total > 50") q.tables("from here") self.assertEqual("SELECT"+q({})[6:], "SELECT store, total__into__ from here where total > 50")
def test_where(self): "query: manages where clause" q = Query() q.select("column_1", None, "name") q.where("name", "name like 'this'") q.tables("from here") q.into(False) self.assertEqual( "SELECT" + q({})[6:], "SELECT column_1 as name from here where name like 'this'")
def test_arguments(self): "query: inserts arguments" q = Query() q.select("col", "%(agg)s", "total") q.where("total", "total > 50") q.tables("from here") q.into(False) self.assertEqual( "SELECT" + q(dict(agg="sum"))[6:], "SELECT sum(col) as total from here where total > 50")
def test_where_aggs(self): "query: manages where aggs" q = Query() q.select("col", "sum", "total") q.select("store") q.agg("total") q.groupby("store") q.where("total", "total > 50") q.tables("from here") q.into(False) self.assertEqual(q({}), "with _data as (select sum(col) as total, store from here group by store) select store, total__into__ from _data where total > 50")
def test_percentage_where(self): "query a percentage of records w/ where condition" q = Query() q.select("total") q.tables("from orders") q.where("total", "total > 10") q.sortby('total') self.assertEqual( q({"limit": "10%"}), "with _l as (select total from orders where total > 10), _ll as (select total from _l order by total asc limit (select round(count(*)*0.1) from _l)) select total__into__ from _ll" )
def test_agg_filter(self): q = Query() q.select("total") q.select("store") q.agg("total") q.agg("total") q.where("total", "total > 50") q.tables("from here") self.assertEqual( "SELECT" + q({})[6:], "SELECT store, total__into__ from here where total > 50")
def test_where_multi_same(self): "query: multiple where of same column" q = Query() q.select("column_1", None, "name") q.where("name", "name like 'this'") q.where("name", "name < 10") q.tables("from here") q.into(False) self.assertEqual( "SELECT" + q({})[6:], "SELECT column_1 as name from here where name like 'this' and name < 10" )
def test_where_multi_arg(self): q = Query() q.select("a") q.where("a", "a=1") q.where("b", "b=2") q.where("b", "b=3") q.tables("from t") q.into(False) # no column `col_1` self.assertRaises(valideer.ValidationError, q, valideer.parse({"where": "where"}).validate({"where": "a|b|c"})) self.assertEqual(q(valideer.parse({"where": "where"}).validate({"where": "a|b"})), "select a from t where (a=1 or (b=2 and b=3))")
def test_where_arrangement(self): q = Query() q.select("a") q.where("a", "a > 10") q.where("b", "b > 20") q.where("c", "c > 30") q.tables("from t") q.into(False) # no column `d` self.assertRaises(valideer.ValidationError, q, valideer.parse({"where": "where"}).validate({"where": "(d|b)&c"})) self.assertEqual(q(valideer.parse({"where": "where"}).validate({"where": "(a|b)&c"})), "select a from t where ((a > 10 or b > 20) and c > 30)")
def test_where_aggs(self): "query: manages where aggs" q = Query() q.select("col", "sum", "total") q.select("store") q.agg("total") q.groupby("store") q.where("total", "total > 50") q.tables("from here") q.into(False) self.assertEqual( q({}), "with _data as (select sum(col) as total, store from here group by store) select store, total__into__ from _data where total > 50" )
def test_where_multi_arg(self): q = Query() q.select("a") q.where("a", "a=1") q.where("b", "b=2") q.where("b", "b=3") q.tables("from t") q.into(False) # no column `col_1` self.assertRaises( valideer.ValidationError, q, valideer.parse({ "where": "where" }).validate({"where": "a|b|c"})) self.assertEqual( q(valideer.parse({ "where": "where" }).validate({"where": "a|b"})), "select a from t where (a=1 or (b=2 and b=3))")
def test_where_arrangement(self): q = Query() q.select("a") q.where("a", "a > 10") q.where("b", "b > 20") q.where("c", "c > 30") q.tables("from t") q.into(False) # no column `d` self.assertRaises( valideer.ValidationError, q, valideer.parse({ "where": "where" }).validate({"where": "(d|b)&c"})) self.assertEqual( q( valideer.parse({ "where": "where" }).validate({"where": "(a|b)&c"})), "select a from t where ((a > 10 or b > 20) and c > 30)")