def test_column_as(self):
"query: select as name"
q = Query()
q.select("column_1", None, "name")
self.assertItemsEqual(q._selects.keys(), ["name"])
q.tables("from here")
q.into(False)
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1 as name from here")
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_column_as(self):
"query: select as name"
q = Query()
q.select("column_1", None, "name")
self.assertItemsEqual(q._selects.keys(), ["name"])
q.tables("from here")
q.into(False)
self.assertEqual("SELECT" + q({})[6:],
"SELECT column_1 as name from here")
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_into_toggle(self):
"query: can toggle __into__"
q = Query()
q.select("column_1")
q.tables("from here")
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1__into__ from here")
q = Query()
q.select("column_1")
q.tables("from here")
q.into(False)
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1 from here")
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_into_toggle(self):
"query: can toggle __into__"
q = Query()
q.select("column_1")
q.tables("from here")
self.assertEqual("SELECT" + q({})[6:],
"SELECT column_1__into__ from here")
q = Query()
q.select("column_1")
q.tables("from here")
q.into(False)
self.assertEqual("SELECT" + q({})[6:], "SELECT column_1 from here")
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)")