Ejemplo n.º 1
0
 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")
Ejemplo n.º 2
0
 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")
Ejemplo n.º 3
0
 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)")
Ejemplo n.º 4
0
 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")
Ejemplo n.º 5
0
 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")
Ejemplo n.º 6
0
 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'")
Ejemplo n.º 7
0
 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")
Ejemplo n.º 8
0
 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")
Ejemplo n.º 9
0
 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")
Ejemplo n.º 10
0
 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")
Ejemplo n.º 11
0
 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")
Ejemplo n.º 12
0
 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")
Ejemplo n.º 13
0
 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")
Ejemplo n.º 14
0
 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")
Ejemplo n.º 15
0
 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)"
     )
Ejemplo n.º 16
0
 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")
Ejemplo n.º 17
0
 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'")
Ejemplo n.º 18
0
 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"
     )
Ejemplo n.º 19
0
 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)"
     )
Ejemplo n.º 20
0
 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")
Ejemplo n.º 21
0
 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)"
     )
Ejemplo n.º 22
0
 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"
     )
Ejemplo n.º 23
0
 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")
Ejemplo n.º 24
0
 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"
     )
Ejemplo n.º 25
0
 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")
Ejemplo n.º 26
0
    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")
Ejemplo n.º 27
0
 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"
     )
Ejemplo n.º 28
0
 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"
     )
Ejemplo n.º 29
0
    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")
Ejemplo n.º 30
0
    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))")
Ejemplo n.º 31
0
    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)")
Ejemplo n.º 32
0
 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"
     )
Ejemplo n.º 33
0
    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))")
Ejemplo n.º 34
0
    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"
        )
Ejemplo n.º 35
0
    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)")
Ejemplo n.º 36
0
    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")
Ejemplo n.º 37
0
 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)")
Ejemplo n.º 38
0
 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)")