expr.py

Python的一个ORM,现在很火

        self.assertEquals(parameters, [])

        expr = Func1() / "value"
        statement, parameters = compile(expr)
        self.assertEquals(statement, "func1()/?")
        self.assertEquals(parameters, [Variable("value")])

    def test_mod(self):
        expr = Mod(elem1, Mod(elem2, elem3))
        statement, parameters = compile(expr)
        self.assertEquals(statement, "elem1%(elem2%elem3)")
        self.assertEquals(parameters, [])

        expr = Mod(Mod(elem1, elem2), elem3)
        statement, parameters = compile(expr)
        self.assertEquals(statement, "elem1%elem2%elem3")
        self.assertEquals(parameters, [])

        expr = Func1() % "value"
        statement, parameters = compile(expr)
        self.assertEquals(statement, "func1()%?")
        self.assertEquals(parameters, [Variable("value")])

    def test_func(self):
        expr = Func("myfunc", elem1, Func1(elem2))
        statement, parameters = compile(expr)
        self.assertEquals(statement, "myfunc(elem1, func1(elem2))")
        self.assertEquals(parameters, [])

    def test_named_func(self):
        expr = Func1(elem1, Func2(elem2))
        statement, parameters = compile(expr)
        self.assertEquals(statement, "func1(elem1, func2(elem2))")
        self.assertEquals(parameters, [])

    def test_count(self):
        expr = Count(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "COUNT(func1())")
        self.assertEquals(parameters, [])

    def test_count_all(self):
        expr = Count()
        statement, parameters = compile(expr)
        self.assertEquals(statement, "COUNT(*)")
        self.assertEquals(parameters, [])

    def test_count_distinct(self):
        expr = Count(Func1(), distinct=True)
        statement, parameters = compile(expr)
        self.assertEquals(statement, "COUNT(DISTINCT func1())")
        self.assertEquals(parameters, [])

    def test_count_distinct_all(self):
        self.assertRaises(ValueError, Count, distinct=True)

    def test_max(self):
        expr = Max(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "MAX(func1())")
        self.assertEquals(parameters, [])

    def test_min(self):
        expr = Min(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "MIN(func1())")
        self.assertEquals(parameters, [])

    def test_avg(self):
        expr = Avg(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "AVG(func1())")
        self.assertEquals(parameters, [])

    def test_sum(self):
        expr = Sum(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "SUM(func1())")
        self.assertEquals(parameters, [])

    def test_lower(self):
        expr = Lower(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "LOWER(func1())")
        self.assertEquals(parameters, [])

        expr = Func1().lower()
        statement, parameters = compile(expr)
        self.assertEquals(statement, "LOWER(func1())")
        self.assertEquals(parameters, [])

    def test_upper(self):
        expr = Upper(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "UPPER(func1())")
        self.assertEquals(parameters, [])

        expr = Func1().upper()
        statement, parameters = compile(expr)
        self.assertEquals(statement, "UPPER(func1())")
        self.assertEquals(parameters, [])

    def test_not(self):
        expr = Not(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "NOT func1()")
        self.assertEquals(parameters, [])

    def test_exists(self):
        expr = Exists(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "EXISTS func1()")
        self.assertEquals(parameters, [])

    def test_asc(self):
        expr = Asc(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "func1() ASC")
        self.assertEquals(parameters, [])

    def test_desc(self):
        expr = Desc(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "func1() DESC")
        self.assertEquals(parameters, [])

    def test_asc_with_string(self):
        expr = Asc("column")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "column ASC")
        self.assertEquals(parameters, [])

    def test_desc_with_string(self):
        expr = Desc("column")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "column DESC")
        self.assertEquals(parameters, [])

    def test_sql(self):
        expr = SQL("expression")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "expression")
        self.assertEquals(parameters, [])

    def test_sql_params(self):
        expr = SQL("expression", ["params"])
        statement, parameters = compile(expr)
        self.assertEquals(statement, "expression")
        self.assertEquals(parameters, ["params"])

    def test_sql_invalid_params(self):
        expr = SQL("expression", "not a list or tuple")
        self.assertRaises(CompileError, compile, expr)

    def test_sql_tables(self):
        expr = Select([column1, Func1()], SQL("expression", [], Func2()))
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "SELECT column1, func1() FROM func2() "
                          "WHERE expression")
        self.assertEquals(parameters, [])

    def test_sql_tables_with_list_or_tuple(self):
        sql = SQL("expression", [], [Func1(), Func2()])
        expr = Select(column1, sql)
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "SELECT column1 FROM func1(), func2() "
                          "WHERE expression")
        self.assertEquals(parameters, [])

        sql = SQL("expression", [], (Func1(), Func2()))
        expr = Select(column1, sql)
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "SELECT column1 FROM func1(), func2() "
                          "WHERE expression")
        self.assertEquals(parameters, [])

    def test_sql_comparison(self):
        expr = SQL("expression1") & SQL("expression2")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "(expression1) AND (expression2)")
        self.assertEquals(parameters, [])

    def test_table(self):
        expr = Table(table1)
        statement, parameters = compile(expr)
        self.assertEquals(statement, table1)
        self.assertEquals(parameters, [])

    def test_alias(self):
        expr = Alias(Table(table1), "name")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "name")
        self.assertEquals(parameters, [])

    def test_alias_in_tables(self):
        expr = Select(column1, tables=Alias(Table(table1), "name"))
        statement, parameters = compile(expr)
        self.assertEquals(statement, "SELECT column1 FROM table1 AS name")
        self.assertEquals(parameters, [])

    def test_alias_in_tables_auto_name(self):
        expr = Select(column1, tables=Alias(Table(table1)))
        statement, parameters = compile(expr)
        self.assertEquals(statement[:-1], "SELECT column1 FROM table1 AS _")
        self.assertEquals(parameters, [])

    def test_alias_in_column_prefix(self):
        expr = Select(Column(column1, Alias(Table(table1), "alias")))
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "SELECT alias.column1 FROM table1 AS alias")
        self.assertEquals(parameters, [])

    def test_alias_for_column(self):
        expr = Select(Alias(Column(column1, table1), "name"))
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "SELECT table1.column1 AS name FROM table1")
        self.assertEquals(parameters, [])

    def test_alias_union(self):
        union = Union(Select(elem1), Select(elem2))
        expr = Select(elem3, tables=Alias(union, "alias"))
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "SELECT elem3 FROM "
                          "((SELECT elem1) UNION (SELECT elem2)) AS alias")
        self.assertEquals(parameters, [])

    def test_join(self):
        expr = Join(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "JOIN func1()")
        self.assertEquals(parameters, [])

    def test_join_on(self):
        expr = Join(Func1(), Func2() == "value")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "JOIN func1() ON func2() = ?")
        self.assertEquals(parameters, [Variable("value")])

    def test_join_on_with_string(self):
        expr = Join(Func1(), on="a = b")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "JOIN func1() ON a = b")
        self.assertEquals(parameters, [])

    def test_join_left_right(self):
        expr = Join(table1, table2)
        statement, parameters = compile(expr)
        self.assertEquals(statement, "table1 JOIN table2")
        self.assertEquals(parameters, [])

    def test_join_nested(self):
        expr = Join(table1, Join(table2, table3))
        statement, parameters = compile(expr)
        self.assertEquals(statement, "table1 JOIN (table2 JOIN table3)")
        self.assertEquals(parameters, [])

    def test_join_double_nested(self):
        expr = Join(Join(table1, table2), Join(table3, table4))
        statement, parameters = compile(expr)
        self.assertEquals(statement,
                          "(table1 JOIN table2) JOIN (table3 JOIN table4)")
        self.assertEquals(parameters, [])

    def test_join_table(self):
        expr = Join(Table(table1), Table(table2))
        statement, parameters = compile(expr)
        self.assertEquals(statement, "table1 JOIN table2")
        self.assertEquals(parameters, [])

    def test_join_contexts(self):
        table1, table2, on = track_contexts(3)
        expr = Join(table1, table2, on)
        compile(expr)
        self.assertEquals(table1.context, None)
        self.assertEquals(table2.context, None)
        self.assertEquals(on.context, EXPR)

    def test_left_join(self):
        expr = LeftJoin(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "LEFT JOIN func1()")
        self.assertEquals(parameters, [])

    def test_left_join_on(self):
        expr = LeftJoin(Func1(), Func2() == "value")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "LEFT JOIN func1() ON func2() = ?")
        self.assertEquals(parameters, [Variable("value")])

    def test_right_join(self):
        expr = RightJoin(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "RIGHT JOIN func1()")
        self.assertEquals(parameters, [])

    def test_right_join_on(self):
        expr = RightJoin(Func1(), Func2() == "value")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "RIGHT JOIN func1() ON func2() = ?")
        self.assertEquals(parameters, [Variable("value")])

    def test_natural_join(self):
        expr = NaturalJoin(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "NATURAL JOIN func1()")
        self.assertEquals(parameters, [])

    def test_natural_join_on(self):
        expr = NaturalJoin(Func1(), Func2() == "value")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "NATURAL JOIN func1() ON func2() = ?")
        self.assertEquals(parameters, [Variable("value")])

    def test_natural_left_join(self):
        expr = NaturalLeftJoin(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "NATURAL LEFT JOIN func1()")
        self.assertEquals(parameters, [])

    def test_natural_left_join_on(self):
        expr = NaturalLeftJoin(Func1(), Func2() == "value")
        statement, parameters = compile(expr)
        self.assertEquals(statement, "NATURAL LEFT JOIN func1() "
                                     "ON func2() = ?")
        self.assertEquals(parameters, [Variable("value")])

    def test_natural_right_join(self):
        expr = NaturalRightJoin(Func1())
        statement, parameters = compile(expr)
        self.assertEquals(statement, "NATURAL RIGHT JOIN func1()")
        self.assertEquals(parameters, [])

    def test_natural_right_join_on(self):
        expr = NaturalRightJoin(Func1(), Func2() == "value")
        statement, parameters = compile(expr)