def test_percentage_agg(self):
"query a percentage of records"
q = Query()
q.select("total", "sum", "total")
q.tables("from orders")
q.sortby('total')
self.assertEqual(q({"limit":"50%"}), "with _l as (select total as total from orders), _ll as (select total from _l order by total asc limit (select round(count(*)*0.5) from _l)) select sum(total) as total__into__ from _ll")
def test_with(self):
"query: with magic"
q = Query()
q.select("column_1")
q.with_("select this from that", "_this")
q.tables("from table")
self.assertEqual(q({}), "with _this as (select this from that) select column_1__into__ from table")
def test_resort_tables(self):
"query: re-organizes tables"
q = Query()
q.select("column_1")
q.select("column_2")
q.tables("left join this using (that)", "from here")
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1, column_2__into__ from here left join this using (that)")
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_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_filters_selects_2(self):
q = Query()
q.select("column_1")
q.select("column_2")
q.select(False)
q.select("column_3")
q.tables("from here")
self.assertEqual("SELECT"+q({})[6:], "SELECT column_3__into__ from here")
def test_filters_selects(self):
"query: filters select columns"
q = Query()
q.select("column_1")
q.select("column_2")
q.select("column_1")
q.select("column_1")
q.tables("from here")
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1, column_2__into__ from here")
def test_with_false(self):
"query: with reduced"
q = Query()
q.select("column_1")
q.with_("with _that ()")
q.with_(False)
q.with_("select this from that", "_that")
q.tables("from table")
self.assertEqual(q({}), "with _that as (select this from that) select column_1__into__ from table")
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_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_filters_selects_2(self):
q = Query()
q.select("column_1")
q.select("column_2")
q.select(False)
q.select("column_3")
q.tables("from here")
self.assertEqual("SELECT" + q({})[6:],
"SELECT column_3__into__ from here")
def test_resort_tables(self):
"query: re-organizes tables"
q = Query()
q.select("column_1")
q.select("column_2")
q.tables("left join this using (that)", "from here")
self.assertEqual(
"SELECT" + q({})[6:],
"SELECT column_1, column_2__into__ from here left join this using (that)"
)
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(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_with(self):
"query: with magic"
q = Query()
q.select("column_1")
q.with_("select this from that", "_this")
q.tables("from table")
self.assertEqual(
q({}),
"with _this as (select this from that) select column_1__into__ from table"
)
def test_filter_tables(self):
q = Query()
q.select("column_1")
q.select("column_2")
q.tables("from here", False, "inner join this using (that)",
"from there")
self.assertEqual(
"SELECT" + q({})[6:],
"SELECT column_1, column_2__into__ from there inner join this using (that)"
)
def test_filters_selects(self):
"query: filters select columns"
q = Query()
q.select("column_1")
q.select("column_2")
q.select("column_1")
q.select("column_1")
q.tables("from here")
self.assertEqual("SELECT" + q({})[6:],
"SELECT column_1, column_2__into__ from here")
def test_resort_tables_2(self):
q = Query()
q.select("column_1")
q.select("column_2")
q.tables("left join this using (that)", "from here",
"inner join a using (b)")
self.assertEqual(
"SELECT" + q({})[6:],
"SELECT column_1, column_2__into__ from here inner join a using (b) left join this using (that)"
)
def test_percentage_agg(self):
"query a percentage of records"
q = Query()
q.select("total", "sum", "total")
q.tables("from orders")
q.sortby('total')
self.assertEqual(
q({"limit": "50%"}),
"with _l as (select total as total from orders), _ll as (select total from _l order by total asc limit (select round(count(*)*0.5) from _l)) select sum(total) as 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_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_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_with_false(self):
"query: with reduced"
q = Query()
q.select("column_1")
q.with_("with _that ()")
q.with_(False)
q.with_("select this from that", "_that")
q.tables("from table")
self.assertEqual(
q({}),
"with _that as (select this from that) select column_1__into__ from table"
)
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_sortby(self):
"query: sort by must be seleted"
q = Query()
q.select("column_1")
q.sortby("column_2")
q.tables("from table")
self.assertEqual(
"SELECT" + q(dict(dir="asc"))[6:],
"SELECT column_1, column_2__into__ from table order by column_2 asc"
)
q = Query()
q.select("column_1")
q.sortby("column_2")
q.tables("from table")
self.assertEqual(
"SELECT" + q(dict(dir="asc"))[6:],
"SELECT column_1, column_2__into__ from table order by column_2 asc"
)
q = Query()
q.select("column_1")
q.select("column_2")
q.sortby("column_2")
q.tables("from table")
self.assertEqual(
"SELECT" + q(dict(dir="desc"))[6:],
"SELECT column_1, column_2__into__ from table order by column_2 desc"
)
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_sortby(self):
"query: sort by must be seleted"
q = Query()
q.select("column_1")
q.sortby("column_2")
q.tables("from table")
self.assertEqual("SELECT"+q(dict(dir="asc"))[6:], "SELECT column_1, column_2__into__ from table order by column_2 asc")
q = Query()
q.select("column_1")
q.sortby("column_2")
q.tables("from table")
self.assertEqual("SELECT"+q(dict(dir="asc"))[6:], "SELECT column_1, column_2__into__ from table order by column_2 asc")
q = Query()
q.select("column_1")
q.select("column_2")
q.sortby("column_2")
q.tables("from table")
self.assertEqual("SELECT"+q(dict(dir="desc"))[6:], "SELECT column_1, column_2__into__ from table order by column_2 desc")
def test_filter_tables(self):
q = Query()
q.select("column_1")
q.select("column_2")
q.tables("from here", False, "inner join this using (that)", "from there")
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1, column_2__into__ from there inner join this using (that)")
def test_resort_tables_2(self):
q = Query()
q.select("column_1")
q.select("column_2")
q.tables("left join this using (that)", "from here", "inner join a using (b)")
self.assertEqual("SELECT"+q({})[6:], "SELECT column_1, column_2__into__ from here inner join a using (b) left join this using (that)")