def test_datetime():
    from datetime import datetime, date, time
    assert sqlrepr(datetime(2005, 7, 14, 13, 31, 2)) == "'2005-07-14 13:31:02'"
    assert sqlrepr(date(2005, 7, 14)) == "'2005-07-14'"
    assert sqlrepr(time(13, 31, 2)) == "'13:31:02'"
    # now dates before 1900
    assert sqlrepr(datetime(1428, 7, 14, 13, 31, 2)) == "'1428-07-14 13:31:02'"
    assert sqlrepr(date(1428, 7, 14)) == "'1428-07-14'"

def test_empty_AND():
    assert AND() is None
    assert AND(True) is True

    # sqlrepr() is needed because AND() returns an SQLExpression that overrides
    # comparison. The following
    #     AND('x', 'y') == "foo bar"
    # is True! (-: Eeek!
    assert sqlrepr(AND(1, 2)) == sqlrepr(SQLOp("AND", 1, 2)) == "((1) AND (2))"
    assert sqlrepr(AND(1, 2, '3'), "sqlite") == \
        sqlrepr(SQLOp("AND", 1, SQLOp("AND", 2, '3')), "sqlite") == \
        "((1) AND ((2) AND ('3')))"

def test_sets():
    try:
        set
    except NameError:
        pass
    else:
        assert sqlrepr(set([1])) == "(1)"
    try:
        from sets import Set
    except ImportError:
        pass
    else:
        assert sqlrepr(Set([1])) == "(1)"

def test_insert():
    # Single column, no keyword arguments.
    instance = Insert('test', [('test',)])
    assert sqlrepr(instance, 'mysql') == "INSERT INTO test VALUES ('test')"

    # Multiple columns, no keyword arguments.
    instance2 = Insert('test', [('1st', '2nd', '3th', '4th')])
    assert sqlrepr(instance2, 'postgres') == "INSERT INTO test VALUES ('1st', '2nd', '3th', '4th')"

    # Multiple rows, multiple columns, "valueList" keyword argument.
    instance3 = Insert('test', valueList=[('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3')])
    assert sqlrepr(instance3, 'sqlite') == "INSERT INTO test VALUES ('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3')"

    # Multiple columns, "values" keyword argument.
    instance4 = Insert('test', values=('v1', 'v2', 'v3'))
    assert sqlrepr(instance4, 'mysql') == "INSERT INTO test VALUES ('v1', 'v2', 'v3')"

    # Single column, "valueList" keyword argument.
    instance5 = Insert('test', valueList=[('v1',)])
    assert sqlrepr(instance5, 'mysql') == "INSERT INTO test VALUES ('v1')"

    # Multiple rows, Multiple columns, template.
    instance6 = Insert('test', valueList=[('a1', 'b1'), ('a2', 'b2')], template=['col1', 'col2'])
    assert sqlrepr(instance6, 'mysql') == "INSERT INTO test (col1, col2) VALUES ('a1', 'b1'), ('a2', 'b2')"

    # Multiple columns, implicit template (dictionary value).
    instance7 = Insert('test', valueList=[{'col1': 'a1', 'col2': 'b1'}])
    assert sqlrepr(instance7, 'mysql') == "INSERT INTO test (col2, col1) VALUES ('b1', 'a1')"

    # Multiple rows, Multiple columns, implicit template.
    instance8 = Insert('test', valueList=[{'col1': 'a1', 'col2': 'b1'},
                                        {'col1': 'a2', 'col2': 'b2'}])
    assert sqlrepr(instance8, 'mysql') == "INSERT INTO test (col2, col1) VALUES ('b1', 'a1'), ('b2', 'a2')"

def test_sets():
    try:
        set
    except NameError:
        pass
    else:
        assert sqlrepr(set([1])) == "(1)"
    if sys.version_info[:3] < (2, 6, 0): # Module sets was deprecated in Python 2.6
        try:
            from sets import Set
        except ImportError:
            pass
        else:
            assert sqlrepr(Set([1])) == "(1)"

def test_str_or_sqlrepr():
    select = Select(['id', 'name'], staticTables=['employees'],
                    where='value>0', orderBy='id')
    assert sqlrepr(select, 'sqlite') == \
        'SELECT id, name FROM employees WHERE value>0 ORDER BY id'

    select = Select(['id', 'name'], staticTables=['employees'],
                    where='value>0', orderBy='id', lazyColumns=True)
    assert sqlrepr(select, 'sqlite') == \
        'SELECT id FROM employees WHERE value>0 ORDER BY id'

    insert = Insert('employees', values={'id': 1, 'name': 'test'})
    assert sqlrepr(insert, 'sqlite') == \
        "INSERT INTO employees (id, name) VALUES (1, 'test')"

    update = Update('employees', {'name': 'test'}, where='id=1')
    assert sqlrepr(update, 'sqlite') == \
        "UPDATE employees SET name='test' WHERE id=1"

    update = Update('employees', {'name': 'test', 'age': 42}, where='id=1')
    assert sqlrepr(update, 'sqlite') == \
        "UPDATE employees SET age=42, name='test' WHERE id=1"

    delete = Delete('employees', where='id=1')
    assert sqlrepr(delete, 'sqlite') == \
        "DELETE FROM employees WHERE id=1"

    raises(TypeError, Delete, 'employees')

    delete = Delete('employees', where=None)
    assert sqlrepr(delete, 'sqlite') == \
        "DELETE FROM employees"

def test_CONCAT():
    setupClass(SOTestSQLBuilder)
    SOTestSQLBuilder(name='test', value=42)

    assert sqlrepr(CONCAT('a', 'b'), 'mysql') == "CONCAT('a', 'b')"
    assert sqlrepr(CONCAT('a', 'b'), 'sqlite') == "'a' || 'b'"
    assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'mysql') == \
        "CONCAT('prefix', so_test_sql_builder.name)"
    assert sqlrepr(CONCAT('prefix', SOTestSQLBuilder.q.name), 'sqlite') == \
        "'prefix' || so_test_sql_builder.name"

    select = Select([CONCAT(SOTestSQLBuilder.q.name, '-suffix')],
                    staticTables=['so_test_sql_builder'])
    connection = getConnection()
    rows = connection.queryAll(connection.sqlrepr(select))
    result = rows[0][0]
    if not PY2 and not isinstance(result, str):
        result = result.decode('ascii')
    assert result == "test-suffix"

def test_Select():
    setupClass(SOTestSQLBuilder)

    select1 = Select([const.id, func.MAX(const.salary)],
                     staticTables=['employees'])
    assert sqlrepr(select1) == 'SELECT id, MAX(salary) FROM employees'

    select2 = Select([SOTestSQLBuilder.q.name, SOTestSQLBuilder.q.value])
    assert sqlrepr(select2) == \
        'SELECT so_test_sql_builder.name, so_test_sql_builder.value ' \
        'FROM so_test_sql_builder'

    union = Union(select1, select2)
    assert sqlrepr(union) == \
        'SELECT id, MAX(salary) FROM employees ' \
        'UNION SELECT so_test_sql_builder.name, so_test_sql_builder.value ' \
        'FROM so_test_sql_builder'

    union = Union(SOTestSQLBuilder.select().queryForSelect())
    assert sqlrepr(union) == \
        'SELECT so_test_sql_builder.id, so_test_sql_builder.name, ' \
        'so_test_sql_builder.value FROM so_test_sql_builder WHERE 1 = 1'

def test_op():
    instance = SQLOp('and', 'this', 'that')
    assert sqlrepr(instance, 'mysql') == "(('this') AND ('that'))"

def test_bool():
    assert sqlrepr(True, 'postgres') == "'t'"
    assert sqlrepr(False, 'postgres') == "'f'"
    assert sqlrepr(True, 'mysql') == "1"
    assert sqlrepr(False, 'mysql') == "0"

def test_tuple():
    assert sqlrepr(('one', 'two', 'three'), 'postgres') == "('one', 'two', 'three')"

def test_list():
    assert sqlrepr(['one', 'two', 'three'], 'postgres') == "('one', 'two', 'three')"

def test_dict():
    assert sqlrepr({"key": "value"}, "sqlite") == "('key')"

def test_modulo():
    setupClass(SOTestSQLBuilder)
    assert sqlrepr(SOTestSQLBuilder.q.value % 2 == 0, 'mysql') == \
        "((MOD(so_test_sql_builder.value, 2)) = (0))"
    assert sqlrepr(SOTestSQLBuilder.q.value % 2 == 0, 'sqlite') == \
        "(((so_test_sql_builder.value) % (2)) = (0))"

def test_string_():
    assert sqlrepr('A String\tAnother', 'postgres') == "'A String\\tAnother'"
    assert sqlrepr('A String\'Another', 'firebird') == "'A String''Another'"

def test_string_b():
    assert sqlrepr('A String\bAnother', 'postgres') == "'A String\\bAnother'"

def test_string_000():
    assert sqlrepr('A String\000Another', 'postgres') == "'A String\\0Another'"

def test_string_newline():
    assert sqlrepr('A String\nAnother', 'postgres') == "'A String\\nAnother'"
    assert sqlrepr('A String\nAnother', 'sqlite') == "'A String\nAnother'"

def test_simple_string():
    assert sqlrepr('A String', 'firebird') == "'A String'"

def test_constant():
    instance = SQLConstant('test')
    assert sqlrepr(instance) == repr(instance)