示例#1
0
    def test_varchar_raise(self):
        for type_ in (
            String,
            VARCHAR,
            String(),
            VARCHAR(),
            NVARCHAR(),
            Unicode,
            Unicode(),
        ):
            type_ = sqltypes.to_instance(type_)
            assert_raises_message(
                exc.CompileError,
                "VARCHAR requires a length on dialect mysql",
                type_.compile,
                dialect=mysql.dialect()
            )

            t1 = Table('sometable', MetaData(),
                Column('somecolumn', type_)
            )
            assert_raises_message(
                exc.CompileError,
                r"\(in table 'sometable', column 'somecolumn'\)\: "
                r"(?:N)?VARCHAR requires a length on dialect mysql",
                schema.CreateTable(t1).compile,
                dialect=mysql.dialect()
            )
 def test_cast_grouped_expression_pre_4(self):
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     with expect_warnings("Current MySQL version does not support CAST;"):
         self.assert_compile(cast(
             sql.column('x') + sql.column('y'), Integer),
                             "(x + y)",
                             dialect=dialect)
示例#3
0
    def test_varchar_raise(self, type_):
        type_ = sqltypes.to_instance(type_)
        assert_raises_message(
            exc.CompileError,
            "VARCHAR requires a length on dialect mysql",
            type_.compile,
            dialect=mysql.dialect(),
        )

        t1 = Table("sometable", MetaData(), Column("somecolumn", type_))
        assert_raises_message(
            exc.CompileError,
            r"\(in table 'sometable', column 'somecolumn'\)\: "
            r"(?:N)?VARCHAR requires a length on dialect mysql",
            schema.CreateTable(t1).compile,
            dialect=mysql.dialect(),
        )
示例#4
0
 def test_cast_grouped_expression_pre_4(self):
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     self.assert_compile(
         cast(sql.column('x') + sql.column('y'), Integer),
         "(x + y)",
         dialect=dialect
     )
示例#5
0
 def test_no_cast_pre_4(self):
     self.assert_compile(cast(Column("foo", Integer), String),
                         "CAST(foo AS CHAR)")
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     with expect_warnings("Current MySQL version does not support CAST;"):
         self.assert_compile(cast(Column("foo", Integer), String),
                             "foo",
                             dialect=dialect)
示例#6
0
 def test_plain_stringify_returning(self):
     t = Table(
         "t",
         MetaData(),
         Column("myid", Integer, primary_key=True),
         Column("name", String, server_default="some str"),
         Column("description", String, default=func.lower("hi")),
     )
     stmt = t.insert().values().return_defaults()
     eq_ignore_whitespace(
         str(stmt.compile(dialect=mysql.dialect(is_mariadb=True))),
         "INSERT INTO t (description) VALUES (lower(%s)) "
         "RETURNING t.myid, t.name, t.description",
     )
     eq_ignore_whitespace(
         str(stmt.compile(dialect=mysql.dialect())),
         "INSERT INTO t (description) VALUES (lower(%s))",
     )
示例#7
0
 def test_cast_grouped_expression_pre_4(self):
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     with expect_warnings("Current MySQL version does not support CAST;"):
         self.assert_compile(
             cast(sql.column('x') + sql.column('y'), Integer),
             "(x + y)",
             dialect=dialect
         )
    def test_backslash_escaping(self):
        self.assert_compile(
            sql.column('foo').like('bar', escape='\\'),
            "foo LIKE %s ESCAPE '\\\\'")

        dialect = mysql.dialect()
        dialect._backslash_escapes = False
        self.assert_compile(sql.column('foo').like('bar', escape='\\'),
                            "foo LIKE %s ESCAPE '\\'",
                            dialect=dialect)
示例#9
0
 def test_no_cast_pre_4(self):
     self.assert_compile(
         cast(Column('foo', Integer), String),
         "CAST(foo AS CHAR)",
     )
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     self.assert_compile(cast(Column('foo', Integer), String),
                         "foo",
                         dialect=dialect)
示例#10
0
 def test_no_cast_pre_4(self):
     self.assert_compile(
         cast(Column("foo", Integer), String), "CAST(foo AS CHAR)"
     )
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     with expect_warnings("Current MySQL version does not support CAST;"):
         self.assert_compile(
             cast(Column("foo", Integer), String), "foo", dialect=dialect
         )
示例#11
0
    def test_float_cast(self, type_, expected, maria_db):

        dialect = mysql.dialect()
        if maria_db:
            dialect.is_mariadb = maria_db
            dialect.server_version_info = (10, 4, 5)
        else:
            dialect.server_version_info = (8, 0, 17)
        t = sql.table("t", sql.column("col"))
        self.assert_compile(cast(t.c.col, type_), expected, dialect=dialect)
示例#12
0
 def test_drop_constraint_mysql(self):
     m = MetaData()
     table_name = "testtbl"
     constraint_name = "constraint"
     constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
     tbl = Table(table_name, m, Column("data", String(255)), constraint)
     dialect = mysql.dialect()
     self.assert_compile(schema.DropConstraint(constraint),
                         "ALTER TABLE %s DROP CHECK `%s`" %
                         (table_name, constraint_name),
                         dialect=dialect)
示例#13
0
    def test_match_kw_raises(self):
        m = MetaData()
        Table("t1", m, Column("id", Integer, primary_key=True))
        t2 = Table("t2", m, Column("id", Integer, ForeignKey("t1.id", match="XYZ"), primary_key=True))

        assert_raises_message(
            exc.CompileError,
            "MySQL ignores the 'MATCH' keyword while at the same time causes "
            "ON UPDATE/ON DELETE clauses to be ignored.",
            schema.CreateTable(t2).compile,
            dialect=mysql.dialect(),
        )
示例#14
0
 def test_drop_constraint_mariadb(self):
     m = MetaData()
     table_name = "testtbl"
     constraint_name = "constraint"
     constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
     tbl = Table(table_name, m, Column("data", String(255)), constraint)
     dialect = mysql.dialect()
     dialect.server_version_info = (10, 1, 1, "MariaDB")
     self.assert_compile(schema.DropConstraint(constraint),
                         "ALTER TABLE %s DROP CONSTRAINT `%s`" %
                         (table_name, constraint_name),
                         dialect=dialect)
示例#15
0
class CompileTest(AssertsCompiledSQL, fixtures.TestBase):

    __dialect__ = mysql.dialect()

    def test_distinct_string(self):
        s = select(["*"]).select_from(table("foo"))
        s._distinct = "foo"

        with expect_deprecated(
                "Sending string values for 'distinct' is deprecated in the MySQL "
                "dialect and will be removed in a future release"):
            self.assert_compile(s, "SELECT FOO * FROM foo")
示例#16
0
 def test_no_cast_pre_4(self):
     self.assert_compile(
                 cast(Column('foo', Integer), String),
                 "CAST(foo AS CHAR)",
         )
     dialect = mysql.dialect()
     dialect.server_version_info = (3, 2, 3)
     self.assert_compile(
                 cast(Column('foo', Integer), String),
                 "foo",
                 dialect=dialect
         )
示例#17
0
class InsertOnDuplicateTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mysql.dialect()

    def setup(self):
        self.table = Table(
            'foos',
            MetaData(),
            Column('id', Integer, primary_key=True),
            Column('bar', String(10)),
            Column('baz', String(10)),
        )

    def test_from_values(self):
        stmt = insert(self.table).values([{
            'id': 1,
            'bar': 'ab'
        }, {
            'id': 2,
            'bar': 'b'
        }])
        stmt = stmt.on_duplicate_key_update(bar=stmt.inserted.bar,
                                            baz=stmt.inserted.baz)
        expected_sql = (
            'INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) '
            'ON DUPLICATE KEY UPDATE bar = VALUES(bar), baz = VALUES(baz)')
        self.assert_compile(stmt, expected_sql)

    def test_from_literal(self):
        stmt = insert(self.table).values([{
            'id': 1,
            'bar': 'ab'
        }, {
            'id': 2,
            'bar': 'b'
        }])
        stmt = stmt.on_duplicate_key_update(bar=literal_column('bb'))
        expected_sql = ('INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) '
                        'ON DUPLICATE KEY UPDATE bar = bb')
        self.assert_compile(stmt, expected_sql)

    def test_python_values(self):
        stmt = insert(self.table).values([{
            'id': 1,
            'bar': 'ab'
        }, {
            'id': 2,
            'bar': 'b'
        }])
        stmt = stmt.on_duplicate_key_update(bar="foobar")
        expected_sql = ('INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) '
                        'ON DUPLICATE KEY UPDATE bar = %s')
        self.assert_compile(stmt, expected_sql)
示例#18
0
class InsertOnDuplicateTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mysql.dialect()

    def setup(self):
        self.table = Table(
            "foos",
            MetaData(),
            Column("id", Integer, primary_key=True),
            Column("bar", String(10)),
            Column("baz", String(10)),
        )

    def test_from_values(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar=stmt.inserted.bar,
                                            baz=stmt.inserted.baz)
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
            "ON DUPLICATE KEY UPDATE bar = VALUES(bar), baz = VALUES(baz)")
        self.assert_compile(stmt, expected_sql)

    def test_from_literal(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar=literal_column("bb"))
        expected_sql = ("INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
                        "ON DUPLICATE KEY UPDATE bar = bb")
        self.assert_compile(stmt, expected_sql)

    def test_python_values(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar="foobar")
        expected_sql = ("INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
                        "ON DUPLICATE KEY UPDATE bar = %s")
        self.assert_compile(stmt, expected_sql)
示例#19
0
    def test_match_kw_raises(self):
        m = MetaData()
        t1 = Table('t1', m, Column('id', Integer, primary_key=True))
        t2 = Table('t2', m, Column('id', Integer,
                        ForeignKey('t1.id', match="XYZ"),
                            primary_key=True))

        assert_raises_message(
            exc.CompileError,
            "MySQL ignores the 'MATCH' keyword while at the same time causes "
            "ON UPDATE/ON DELETE clauses to be ignored.",
            schema.CreateTable(t2).compile, dialect=mysql.dialect()
        )
示例#20
0
 def test_drop_constraint_mysql(self):
     m = MetaData()
     table_name = "testtbl"
     constraint_name = "constraint"
     constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
     tbl = Table(table_name, m, Column("data", String(255)), constraint)
     dialect = mysql.dialect()
     self.assert_compile(
         schema.DropConstraint(constraint),
         "ALTER TABLE %s DROP CHECK `%s`"
         % (table_name, constraint_name),
         dialect=dialect
     )
示例#21
0
    def test_backslash_escaping(self):
        self.assert_compile(
            sql.column("foo").like("bar", escape="\\"),
            "foo LIKE %s ESCAPE '\\\\'",
        )

        dialect = mysql.dialect()
        dialect._backslash_escapes = False
        self.assert_compile(
            sql.column("foo").like("bar", escape="\\"),
            "foo LIKE %s ESCAPE '\\'",
            dialect=dialect,
        )
示例#22
0
    def test_backslash_escaping(self):
        self.assert_compile(
            sql.column('foo').like('bar', escape='\\'),
            "foo LIKE %s ESCAPE '\\\\'"
        )

        dialect = mysql.dialect()
        dialect._backslash_escapes=False
        self.assert_compile(
            sql.column('foo').like('bar', escape='\\'),
            "foo LIKE %s ESCAPE '\\'",
            dialect=dialect
        )
示例#23
0
    def test_backslash_escaping(self):
        self.assert_compile(
            sql.column("foo").like("bar", escape="\\"),
            "foo LIKE %s ESCAPE '\\\\'",
        )

        dialect = mysql.dialect()
        dialect._backslash_escapes = False
        self.assert_compile(
            sql.column("foo").like("bar", escape="\\"),
            "foo LIKE %s ESCAPE '\\'",
            dialect=dialect,
        )
示例#24
0
 def test_drop_constraint_mariadb(self):
     m = MetaData()
     table_name = "testtbl"
     constraint_name = "constraint"
     constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
     tbl = Table(table_name, m, Column("data", String(255)), constraint)
     dialect = mysql.dialect()
     dialect.server_version_info = (10, 1, 1, "MariaDB")
     self.assert_compile(
         schema.DropConstraint(constraint),
         "ALTER TABLE %s DROP CONSTRAINT `%s`"
         % (table_name, constraint_name),
         dialect=dialect
     )
    def test_varchar_raise(self):
        for type_ in (
                String,
                VARCHAR,
                String(),
                VARCHAR(),
                NVARCHAR(),
                Unicode,
                Unicode(),
        ):
            type_ = sqltypes.to_instance(type_)
            assert_raises_message(exc.CompileError,
                                  "VARCHAR requires a length on dialect mysql",
                                  type_.compile,
                                  dialect=mysql.dialect())

            t1 = Table('sometable', MetaData(), Column('somecolumn', type_))
            assert_raises_message(
                exc.CompileError,
                r"\(in table 'sometable', column 'somecolumn'\)\: "
                r"(?:N)?VARCHAR requires a length on dialect mysql",
                schema.CreateTable(t1).compile,
                dialect=mysql.dialect())
示例#26
0
class MatchExpressionTest(fixtures.TestBase, AssertsCompiledSQL):

    __dialect__ = mysql.dialect()

    match_table = table(
        "user",
        column("firstname", String),
        column("lastname", String),
    )

    @testing.combinations(
        (
            lambda title: title.match("somstr", mysql_boolean_mode=False),
            "MATCH (matchtable.title) AGAINST (%s)",
        ),
        (
            lambda title: title.match(
                "somstr",
                mysql_boolean_mode=False,
                mysql_natural_language=True,
            ),
            "MATCH (matchtable.title) AGAINST (%s IN NATURAL LANGUAGE MODE)",
        ),
        (
            lambda title: title.match(
                "somstr",
                mysql_boolean_mode=False,
                mysql_query_expansion=True,
            ),
            "MATCH (matchtable.title) AGAINST (%s WITH QUERY EXPANSION)",
        ),
        (
            lambda title: title.match(
                "somstr",
                mysql_boolean_mode=False,
                mysql_natural_language=True,
                mysql_query_expansion=True,
            ),
            "MATCH (matchtable.title) AGAINST "
            "(%s IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)",
        ),
    )
    def test_match_expression_single_col(self, case, expected):
        matchtable = table("matchtable", column("title", String))
        title = matchtable.c.title

        expr = case(title)
        self.assert_compile(expr, expected)
示例#27
0
class TypeCompileTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mysql.dialect()

    @testing.combinations(
        # column type, args, kwargs, expected ddl
        # e.g. Column(Integer(10, unsigned=True)) ==
        # 'INTEGER(10) UNSIGNED'
        (mysql.MSNumeric, [], {}, "NUMERIC"),
        (mysql.MSNumeric, [None], {}, "NUMERIC"),
        (mysql.MSNumeric, [12], {}, "NUMERIC(12)"),
        (
            mysql.MSNumeric,
            [12, 4],
            {
                "unsigned": True
            },
            "NUMERIC(12, 4) UNSIGNED",
        ),
        (
            mysql.MSNumeric,
            [12, 4],
            {
                "zerofill": True
            },
            "NUMERIC(12, 4) ZEROFILL",
        ),
        (
            mysql.MSNumeric,
            [12, 4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "NUMERIC(12, 4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSDecimal, [], {}, "DECIMAL"),
        (mysql.MSDecimal, [None], {}, "DECIMAL"),
        (mysql.MSDecimal, [12], {}, "DECIMAL(12)"),
        (mysql.MSDecimal, [12, None], {}, "DECIMAL(12)"),
        (
            mysql.MSDecimal,
            [12, 4],
            {
                "unsigned": True
            },
            "DECIMAL(12, 4) UNSIGNED",
        ),
        (
            mysql.MSDecimal,
            [12, 4],
            {
                "zerofill": True
            },
            "DECIMAL(12, 4) ZEROFILL",
        ),
        (
            mysql.MSDecimal,
            [12, 4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "DECIMAL(12, 4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSDouble, [None, None], {}, "DOUBLE"),
        (
            mysql.MSDouble,
            [12, 4],
            {
                "unsigned": True
            },
            "DOUBLE(12, 4) UNSIGNED",
        ),
        (
            mysql.MSDouble,
            [12, 4],
            {
                "zerofill": True
            },
            "DOUBLE(12, 4) ZEROFILL",
        ),
        (
            mysql.MSDouble,
            [12, 4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "DOUBLE(12, 4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSReal, [None, None], {}, "REAL"),
        (mysql.MSReal, [12, 4], {
            "unsigned": True
        }, "REAL(12, 4) UNSIGNED"),
        (mysql.MSReal, [12, 4], {
            "zerofill": True
        }, "REAL(12, 4) ZEROFILL"),
        (
            mysql.MSReal,
            [12, 4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "REAL(12, 4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSFloat, [], {}, "FLOAT"),
        (mysql.MSFloat, [None], {}, "FLOAT"),
        (mysql.MSFloat, [12], {}, "FLOAT(12)"),
        (mysql.MSFloat, [12, 4], {}, "FLOAT(12, 4)"),
        (mysql.MSFloat, [12, 4], {
            "unsigned": True
        }, "FLOAT(12, 4) UNSIGNED"),
        (mysql.MSFloat, [12, 4], {
            "zerofill": True
        }, "FLOAT(12, 4) ZEROFILL"),
        (
            mysql.MSFloat,
            [12, 4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "FLOAT(12, 4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSInteger, [], {}, "INTEGER"),
        (mysql.MSInteger, [4], {}, "INTEGER(4)"),
        (mysql.MSInteger, [4], {
            "unsigned": True
        }, "INTEGER(4) UNSIGNED"),
        (mysql.MSInteger, [4], {
            "zerofill": True
        }, "INTEGER(4) ZEROFILL"),
        (
            mysql.MSInteger,
            [4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "INTEGER(4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSBigInteger, [], {}, "BIGINT"),
        (mysql.MSBigInteger, [4], {}, "BIGINT(4)"),
        (mysql.MSBigInteger, [4], {
            "unsigned": True
        }, "BIGINT(4) UNSIGNED"),
        (mysql.MSBigInteger, [4], {
            "zerofill": True
        }, "BIGINT(4) ZEROFILL"),
        (
            mysql.MSBigInteger,
            [4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "BIGINT(4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSMediumInteger, [], {}, "MEDIUMINT"),
        (mysql.MSMediumInteger, [4], {}, "MEDIUMINT(4)"),
        (
            mysql.MSMediumInteger,
            [4],
            {
                "unsigned": True
            },
            "MEDIUMINT(4) UNSIGNED",
        ),
        (
            mysql.MSMediumInteger,
            [4],
            {
                "zerofill": True
            },
            "MEDIUMINT(4) ZEROFILL",
        ),
        (
            mysql.MSMediumInteger,
            [4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "MEDIUMINT(4) UNSIGNED ZEROFILL",
        ),
        (mysql.MSTinyInteger, [], {}, "TINYINT"),
        (mysql.MSTinyInteger, [1], {}, "TINYINT(1)"),
        (mysql.MSTinyInteger, [1], {
            "unsigned": True
        }, "TINYINT(1) UNSIGNED"),
        (mysql.MSTinyInteger, [1], {
            "zerofill": True
        }, "TINYINT(1) ZEROFILL"),
        (
            mysql.MSTinyInteger,
            [1],
            {
                "zerofill": True,
                "unsigned": True
            },
            "TINYINT(1) UNSIGNED ZEROFILL",
        ),
        (mysql.MSSmallInteger, [], {}, "SMALLINT"),
        (mysql.MSSmallInteger, [4], {}, "SMALLINT(4)"),
        (
            mysql.MSSmallInteger,
            [4],
            {
                "unsigned": True
            },
            "SMALLINT(4) UNSIGNED",
        ),
        (
            mysql.MSSmallInteger,
            [4],
            {
                "zerofill": True
            },
            "SMALLINT(4) ZEROFILL",
        ),
        (
            mysql.MSSmallInteger,
            [4],
            {
                "zerofill": True,
                "unsigned": True
            },
            "SMALLINT(4) UNSIGNED ZEROFILL",
        ),
    )
    def test_numeric(self, type_, args, kw, res):
        "Exercise type specification and options for numeric types."

        type_inst = type_(*args, **kw)
        self.assert_compile(type_inst, res)
        # test that repr() copies out all arguments
        self.assert_compile(eval("mysql.%r" % type_inst), res)

    @testing.combinations(
        (mysql.MSChar, [1], {}, "CHAR(1)"),
        (mysql.NCHAR, [1], {}, "NATIONAL CHAR(1)"),
        (mysql.MSChar, [1], {
            "binary": True
        }, "CHAR(1) BINARY"),
        (mysql.MSChar, [1], {
            "ascii": True
        }, "CHAR(1) ASCII"),
        (mysql.MSChar, [1], {
            "unicode": True
        }, "CHAR(1) UNICODE"),
        (
            mysql.MSChar,
            [1],
            {
                "ascii": True,
                "binary": True
            },
            "CHAR(1) ASCII BINARY",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "unicode": True,
                "binary": True
            },
            "CHAR(1) UNICODE BINARY",
        ),
        (mysql.MSChar, [1], {
            "charset": "utf8"
        }, "CHAR(1) CHARACTER SET utf8"),
        (
            mysql.MSChar,
            [1],
            {
                "charset": "utf8",
                "binary": True
            },
            "CHAR(1) CHARACTER SET utf8 BINARY",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "charset": "utf8",
                "unicode": True
            },
            "CHAR(1) CHARACTER SET utf8",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "charset": "utf8",
                "ascii": True
            },
            "CHAR(1) CHARACTER SET utf8",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "collation": "utf8_bin"
            },
            "CHAR(1) COLLATE utf8_bin",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "charset": "utf8",
                "collation": "utf8_bin"
            },
            "CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "charset": "utf8",
                "binary": True
            },
            "CHAR(1) CHARACTER SET utf8 BINARY",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "charset": "utf8",
                "collation": "utf8_bin",
                "binary": True
            },
            "CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
        ),
        (mysql.MSChar, [1], {
            "national": True
        }, "NATIONAL CHAR(1)"),
        (
            mysql.MSChar,
            [1],
            {
                "national": True,
                "charset": "utf8"
            },
            "NATIONAL CHAR(1)",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "national": True,
                "charset": "utf8",
                "binary": True
            },
            "NATIONAL CHAR(1) BINARY",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "national": True,
                "binary": True,
                "unicode": True
            },
            "NATIONAL CHAR(1) BINARY",
        ),
        (
            mysql.MSChar,
            [1],
            {
                "national": True,
                "collation": "utf8_bin"
            },
            "NATIONAL CHAR(1) COLLATE utf8_bin",
        ),
        (
            mysql.MSString,
            [1],
            {
                "charset": "utf8",
                "collation": "utf8_bin"
            },
            "VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
        ),
        (
            mysql.MSString,
            [1],
            {
                "national": True,
                "collation": "utf8_bin"
            },
            "NATIONAL VARCHAR(1) COLLATE utf8_bin",
        ),
        (
            mysql.MSTinyText,
            [],
            {
                "charset": "utf8",
                "collation": "utf8_bin"
            },
            "TINYTEXT CHARACTER SET utf8 COLLATE utf8_bin",
        ),
        (
            mysql.MSMediumText,
            [],
            {
                "charset": "utf8",
                "binary": True
            },
            "MEDIUMTEXT CHARACTER SET utf8 BINARY",
        ),
        (mysql.MSLongText, [], {
            "ascii": True
        }, "LONGTEXT ASCII"),
        (
            mysql.ENUM,
            ["foo", "bar"],
            {
                "unicode": True
            },
            """ENUM('foo','bar') UNICODE""",
        ),
        (String, [20], {
            "collation": "utf8"
        }, "VARCHAR(20) COLLATE utf8"),
    )
    @testing.exclude("mysql", "<", (4, 1, 1), "no charset support")
    def test_charset(self, type_, args, kw, res):
        """Exercise CHARACTER SET and COLLATE-ish options on string types."""

        type_inst = type_(*args, **kw)
        self.assert_compile(type_inst, res)

    @testing.combinations(
        (mysql.MSBit(), "BIT"),
        (mysql.MSBit(1), "BIT(1)"),
        (mysql.MSBit(63), "BIT(63)"),
    )
    def test_bit_50(self, type_, expected):
        """Exercise BIT types on 5.0+ (not valid for all engine types)"""

        self.assert_compile(type_, expected)

    @testing.combinations(
        (BOOLEAN(), "BOOL"),
        (Boolean(), "BOOL"),
        (mysql.TINYINT(1), "TINYINT(1)"),
        (mysql.TINYINT(1, unsigned=True), "TINYINT(1) UNSIGNED"),
    )
    def test_boolean_compile(self, type_, expected):
        self.assert_compile(type_, expected)

    def test_timestamp_fsp(self):
        self.assert_compile(mysql.TIMESTAMP(fsp=5), "TIMESTAMP(5)")

    @testing.combinations(
        ([TIMESTAMP], {}, "TIMESTAMP NULL"),
        ([mysql.MSTimeStamp], {}, "TIMESTAMP NULL"),
        (
            [
                mysql.MSTimeStamp(),
                DefaultClause(sql.text("CURRENT_TIMESTAMP")),
            ],
            {},
            "TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP",
        ),
        (
            [mysql.MSTimeStamp,
             DefaultClause(sql.text("CURRENT_TIMESTAMP"))],
            {
                "nullable": False
            },
            "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
        ),
        (
            [
                mysql.MSTimeStamp,
                DefaultClause(sql.text("'1999-09-09 09:09:09'")),
            ],
            {
                "nullable": False
            },
            "TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09'",
        ),
        (
            [
                mysql.MSTimeStamp(),
                DefaultClause(sql.text("'1999-09-09 09:09:09'")),
            ],
            {},
            "TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09'",
        ),
        (
            [
                mysql.MSTimeStamp(),
                DefaultClause(
                    sql.text("'1999-09-09 09:09:09' "
                             "ON UPDATE CURRENT_TIMESTAMP")),
            ],
            {},
            "TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09' "
            "ON UPDATE CURRENT_TIMESTAMP",
        ),
        (
            [
                mysql.MSTimeStamp,
                DefaultClause(
                    sql.text("'1999-09-09 09:09:09' "
                             "ON UPDATE CURRENT_TIMESTAMP")),
            ],
            {
                "nullable": False
            },
            "TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09' "
            "ON UPDATE CURRENT_TIMESTAMP",
        ),
        (
            [
                mysql.MSTimeStamp(),
                DefaultClause(
                    sql.text("CURRENT_TIMESTAMP "
                             "ON UPDATE CURRENT_TIMESTAMP")),
            ],
            {},
            "TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP "
            "ON UPDATE CURRENT_TIMESTAMP",
        ),
        (
            [
                mysql.MSTimeStamp,
                DefaultClause(
                    sql.text("CURRENT_TIMESTAMP "
                             "ON UPDATE CURRENT_TIMESTAMP")),
            ],
            {
                "nullable": False
            },
            "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
            "ON UPDATE CURRENT_TIMESTAMP",
        ),
    )
    def test_timestamp_defaults(self, spec, kw, expected):
        """Exercise funky TIMESTAMP default syntax when used in columns."""

        c = Column("t", *spec, **kw)
        Table("t", MetaData(), c)
        self.assert_compile(schema.CreateColumn(c), "t %s" % expected)

    def test_datetime_generic(self):
        self.assert_compile(mysql.DATETIME(), "DATETIME")

    def test_datetime_fsp(self):
        self.assert_compile(mysql.DATETIME(fsp=4), "DATETIME(4)")

    def test_time_generic(self):
        """"Exercise TIME."""

        self.assert_compile(mysql.TIME(), "TIME")

    def test_time_fsp(self):
        self.assert_compile(mysql.TIME(fsp=5), "TIME(5)")

    def test_time_result_processor(self):
        eq_(
            mysql.TIME().result_processor(None, None)(datetime.timedelta(
                seconds=35, minutes=517, microseconds=450)),
            datetime.time(8, 37, 35, 450),
        )
示例#28
0
class EnumSetTest(fixtures.TestBase, AssertsExecutionResults,
                  AssertsCompiledSQL):

    __only_on__ = 'mysql'
    __dialect__ = mysql.dialect()

    @testing.provide_metadata
    def test_enum(self):
        """Exercise the ENUM type."""

        with testing.expect_deprecated('Manually quoting ENUM value literals'):
            e1, e2 = mysql.ENUM("'a'", "'b'"), mysql.ENUM("'a'", "'b'")

        enum_table = Table(
            'mysql_enum',
            self.metadata,
            Column('e1', e1),
            Column('e2', e2, nullable=False),
            Column('e2generic', Enum("a", "b"), nullable=False),
            Column('e3', mysql.ENUM("'a'", "'b'", strict=True)),
            Column('e4', mysql.ENUM("'a'", "'b'", strict=True),
                   nullable=False),
            Column('e5', mysql.ENUM("a", "b")),
            Column('e5generic', Enum("a", "b")),
            Column('e6', mysql.ENUM("'a'", "b")),
        )

        eq_(colspec(enum_table.c.e1), "e1 ENUM('a','b')")
        eq_(colspec(enum_table.c.e2), "e2 ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e2generic),
            "e2generic ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e3), "e3 ENUM('a','b')")
        eq_(colspec(enum_table.c.e4), "e4 ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e5), "e5 ENUM('a','b')")
        eq_(colspec(enum_table.c.e5generic), "e5generic ENUM('a','b')")
        eq_(colspec(enum_table.c.e6), "e6 ENUM('''a''','b')")
        enum_table.create()

        assert_raises(exc.DBAPIError,
                      enum_table.insert().execute,
                      e1=None,
                      e2=None,
                      e3=None,
                      e4=None)

        assert_raises(exc.StatementError,
                      enum_table.insert().execute,
                      e1='c',
                      e2='c',
                      e2generic='c',
                      e3='c',
                      e4='c',
                      e5='c',
                      e5generic='c',
                      e6='c')

        enum_table.insert().execute()
        enum_table.insert().execute(e1='a',
                                    e2='a',
                                    e2generic='a',
                                    e3='a',
                                    e4='a',
                                    e5='a',
                                    e5generic='a',
                                    e6="'a'")
        enum_table.insert().execute(e1='b',
                                    e2='b',
                                    e2generic='b',
                                    e3='b',
                                    e4='b',
                                    e5='b',
                                    e5generic='b',
                                    e6='b')

        res = enum_table.select().execute().fetchall()

        expected = [(None, 'a', 'a', None, 'a', None, None, None),
                    ('a', 'a', 'a', 'a', 'a', 'a', 'a', "'a'"),
                    ('b', 'b', 'b', 'b', 'b', 'b', 'b', 'b')]

        eq_(res, expected)

    @testing.provide_metadata
    def test_set(self):

        with testing.expect_deprecated('Manually quoting SET value literals'):
            e1, e2 = mysql.SET("'a'", "'b'"), mysql.SET("'a'", "'b'")

        set_table = Table(
            'mysql_set', self.metadata, Column('e1', e1),
            Column('e2', e2, nullable=False), Column('e3', mysql.SET("a",
                                                                     "b")),
            Column('e4', mysql.SET("'a'", "b")),
            Column('e5', mysql.SET("'a'", "'b'", quoting="quoted")))

        eq_(colspec(set_table.c.e1), "e1 SET('a','b')")
        eq_(colspec(set_table.c.e2), "e2 SET('a','b') NOT NULL")
        eq_(colspec(set_table.c.e3), "e3 SET('a','b')")
        eq_(colspec(set_table.c.e4), "e4 SET('''a''','b')")
        eq_(colspec(set_table.c.e5), "e5 SET('a','b')")
        set_table.create()

        assert_raises(exc.DBAPIError,
                      set_table.insert().execute,
                      e1=None,
                      e2=None,
                      e3=None,
                      e4=None)

        if testing.against("+oursql"):
            assert_raises(exc.StatementError,
                          set_table.insert().execute,
                          e1='c',
                          e2='c',
                          e3='c',
                          e4='c')

        set_table.insert().execute(e1='a', e2='a', e3='a', e4="'a'", e5="a,b")
        set_table.insert().execute(e1='b', e2='b', e3='b', e4='b', e5="a,b")

        res = set_table.select().execute().fetchall()

        if testing.against("+oursql"):
            expected = [
                # 1st row with all c's, data truncated
                (set(['']), set(['']), set(['']), set(['']), None),
            ]
        else:
            expected = []

        expected.extend([
            (set(['a']), set(['a']), set(['a']), set(["'a'"]), set(['a',
                                                                    'b'])),
            (set(['b']), set(['b']), set(['b']), set(['b']), set(['a', 'b']))
        ])

        eq_(res, expected)

    @testing.provide_metadata
    def test_set_roundtrip_plus_reflection(self):
        set_table = Table('mysql_set', self.metadata,
                          Column('s1', mysql.SET("dq", "sq")),
                          Column('s2', mysql.SET("a")),
                          Column('s3', mysql.SET("5", "7", "9")))

        eq_(colspec(set_table.c.s1), "s1 SET('dq','sq')")
        eq_(colspec(set_table.c.s2), "s2 SET('a')")
        eq_(colspec(set_table.c.s3), "s3 SET('5','7','9')")
        set_table.create()
        reflected = Table('mysql_set', MetaData(testing.db), autoload=True)
        for table in set_table, reflected:

            def roundtrip(store, expected=None):
                expected = expected or store
                table.insert(store).execute()
                row = table.select().execute().first()
                self.assert_(list(row) == expected)
                table.delete().execute()

            roundtrip([None, None, None], [None] * 3)
            roundtrip(['', '', ''], [set([''])] * 3)
            roundtrip([set(['dq']), set(['a']), set(['5'])])
            roundtrip(['dq', 'a', '5'], [set(['dq']), set(['a']), set(['5'])])
            roundtrip([1, 1, 1], [set(['dq']), set(['a']), set(['5'])])
            roundtrip([set(['dq', 'sq']), None, set(['9', '5', '7'])])
        set_table.insert().execute({'s3': set(['5'])}, {'s3': set(['5', '7'])},
                                   {'s3': set(['5', '7', '9'])},
                                   {'s3': set(['7', '9'])})

        # NOTE: the string sent to MySQL here is sensitive to ordering.
        # for some reason the set ordering is always "5, 7" when we test on
        # MySQLdb but in Py3K this is not guaranteed.   So basically our
        # SET type doesn't do ordering correctly (not sure how it can,
        # as we don't know how the SET was configured in the first place.)
        rows = select([set_table.c.s3],
                      set_table.c.s3.in_([set(['5']),
                                          ['5', '7']])).execute().fetchall()
        found = set([frozenset(row[0]) for row in rows])
        eq_(found, set([frozenset(['5']), frozenset(['5', '7'])]))

    def test_unicode_enum(self):
        unicode_engine = utf8_engine()
        metadata = MetaData(unicode_engine)
        t1 = Table(
            'table', metadata, Column('id', Integer, primary_key=True),
            Column('value', Enum(u('réveillé'), u('drôle'), u('S’il'))),
            Column('value2', mysql.ENUM(u('réveillé'), u('drôle'), u('S’il'))))
        metadata.create_all()
        try:
            t1.insert().execute(value=u('drôle'), value2=u('drôle'))
            t1.insert().execute(value=u('réveillé'), value2=u('réveillé'))
            t1.insert().execute(value=u('S’il'), value2=u('S’il'))
            eq_(t1.select().order_by(t1.c.id).execute().fetchall(),
                [(1, u('drôle'), u('drôle')),
                 (2, u('réveillé'), u('réveillé')), (3, u('S’il'), u('S’il'))])

            # test reflection of the enum labels

            m2 = MetaData(testing.db)
            t2 = Table('table', m2, autoload=True)

            # TODO: what's wrong with the last element ?  is there
            # latin-1 stuff forcing its way in ?

            assert t2.c.value.type.enums[0:2] == \
                    (u('réveillé'), u('drôle'))  # u'S’il') # eh ?

            assert t2.c.value2.type.enums[0:2] == \
                    (u('réveillé'), u('drôle'))  # u'S’il') # eh ?
        finally:
            metadata.drop_all()

    def test_enum_compile(self):
        e1 = Enum('x', 'y', 'z', name='somename')
        t1 = Table('sometable', MetaData(), Column('somecolumn', e1))
        self.assert_compile(
            schema.CreateTable(t1), "CREATE TABLE sometable (somecolumn "
            "ENUM('x','y','z'))")
        t1 = Table(
            'sometable', MetaData(),
            Column('somecolumn', Enum('x', 'y', 'z', native_enum=False)))
        self.assert_compile(
            schema.CreateTable(t1), "CREATE TABLE sometable (somecolumn "
            "VARCHAR(1), CHECK (somecolumn IN ('x', "
            "'y', 'z')))")

    @testing.provide_metadata
    @testing.exclude('mysql', '<', (4, ), "3.23 can't handle an ENUM of ''")
    def test_enum_parse(self):

        with testing.expect_deprecated('Manually quoting ENUM value literals'):
            enum_table = Table(
                'mysql_enum', self.metadata, Column('e1', mysql.ENUM("'a'")),
                Column('e2', mysql.ENUM("''")), Column('e3', mysql.ENUM('a')),
                Column('e4', mysql.ENUM('')),
                Column('e5', mysql.ENUM("'a'", "''")),
                Column('e6', mysql.ENUM("''", "'a'")),
                Column('e7', mysql.ENUM("''", "'''a'''", "'b''b'", "''''")))

        for col in enum_table.c:
            self.assert_(repr(col))

        enum_table.create()
        reflected = Table('mysql_enum', MetaData(testing.db), autoload=True)
        for t in enum_table, reflected:
            eq_(t.c.e1.type.enums, ("a", ))
            eq_(t.c.e2.type.enums, ("", ))
            eq_(t.c.e3.type.enums, ("a", ))
            eq_(t.c.e4.type.enums, ("", ))
            eq_(t.c.e5.type.enums, ("a", ""))
            eq_(t.c.e6.type.enums, ("", "a"))
            eq_(t.c.e7.type.enums, ("", "'a'", "b'b", "'"))

    @testing.provide_metadata
    @testing.exclude('mysql', '<', (5, ))
    def test_set_parse(self):
        with testing.expect_deprecated('Manually quoting SET value literals'):
            set_table = Table(
                'mysql_set', self.metadata, Column('e1', mysql.SET("'a'")),
                Column('e2', mysql.SET("''")), Column('e3', mysql.SET('a')),
                Column('e4', mysql.SET('')),
                Column('e5', mysql.SET("'a'", "''")),
                Column('e6', mysql.SET("''", "'a'")),
                Column('e7', mysql.SET("''", "'''a'''", "'b''b'", "''''")))

        for col in set_table.c:
            self.assert_(repr(col))

        set_table.create()

        # don't want any warnings on reflection
        reflected = Table('mysql_set', MetaData(testing.db), autoload=True)
        for t in set_table, reflected:
            eq_(t.c.e1.type.values, ("a", ))
            eq_(t.c.e2.type.values, ("", ))
            eq_(t.c.e3.type.values, ("a", ))
            eq_(t.c.e4.type.values, ("", ))
            eq_(t.c.e5.type.values, ("a", ""))
            eq_(t.c.e6.type.values, ("", "a"))
            eq_(t.c.e7.type.values, ("", "'a'", "b'b", "'"))
class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
    """Tests MySQL-dialect specific compilation."""

    __dialect__ = mysql.dialect()

    def test_precolumns(self):
        dialect = self.__dialect__

        def gen(distinct=None, prefixes=None):
            kw = {}
            if distinct is not None:
                kw['distinct'] = distinct
            if prefixes is not None:
                kw['prefixes'] = prefixes
            return str(select([column('q')], **kw).compile(dialect=dialect))

        eq_(gen(None), 'SELECT q')
        eq_(gen(True), 'SELECT DISTINCT q')

        eq_(gen(prefixes=['ALL']), 'SELECT ALL q')
        eq_(gen(prefixes=['DISTINCTROW']), 'SELECT DISTINCTROW q')

        # Interaction with MySQL prefix extensions
        eq_(gen(None, ['straight_join']), 'SELECT straight_join q')
        eq_(gen(False, ['HIGH_PRIORITY', 'SQL_SMALL_RESULT', 'ALL']),
            'SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL q')
        eq_(gen(True, ['high_priority', sql.text('sql_cache')]),
            'SELECT high_priority sql_cache DISTINCT q')

    def test_backslash_escaping(self):
        self.assert_compile(
            sql.column('foo').like('bar', escape='\\'),
            "foo LIKE %s ESCAPE '\\\\'")

        dialect = mysql.dialect()
        dialect._backslash_escapes = False
        self.assert_compile(sql.column('foo').like('bar', escape='\\'),
                            "foo LIKE %s ESCAPE '\\'",
                            dialect=dialect)

    def test_limit(self):
        t = sql.table('t', sql.column('col1'), sql.column('col2'))

        self.assert_compile(
            select([t]).limit(10).offset(20),
            "SELECT t.col1, t.col2 FROM t  LIMIT %s, %s", {
                'param_1': 20,
                'param_2': 10
            })
        self.assert_compile(
            select([t]).limit(10), "SELECT t.col1, t.col2 FROM t  LIMIT %s",
            {'param_1': 10})

        self.assert_compile(
            select([t]).offset(10),
            "SELECT t.col1, t.col2 FROM t  LIMIT %s, 18446744073709551615",
            {'param_1': 10})

    def test_varchar_raise(self):
        for type_ in (
                String,
                VARCHAR,
                String(),
                VARCHAR(),
                NVARCHAR(),
                Unicode,
                Unicode(),
        ):
            type_ = sqltypes.to_instance(type_)
            assert_raises_message(exc.CompileError,
                                  "VARCHAR requires a length on dialect mysql",
                                  type_.compile,
                                  dialect=mysql.dialect())

            t1 = Table('sometable', MetaData(), Column('somecolumn', type_))
            assert_raises_message(
                exc.CompileError,
                r"\(in table 'sometable', column 'somecolumn'\)\: "
                r"(?:N)?VARCHAR requires a length on dialect mysql",
                schema.CreateTable(t1).compile,
                dialect=mysql.dialect())

    def test_update_limit(self):
        t = sql.table('t', sql.column('col1'), sql.column('col2'))

        self.assert_compile(t.update(values={'col1': 123}),
                            "UPDATE t SET col1=%s")
        self.assert_compile(t.update(values={'col1': 123}, mysql_limit=5),
                            "UPDATE t SET col1=%s LIMIT 5")
        self.assert_compile(t.update(values={'col1': 123}, mysql_limit=None),
                            "UPDATE t SET col1=%s")
        self.assert_compile(
            t.update(t.c.col2 == 456, values={'col1': 123}, mysql_limit=1),
            "UPDATE t SET col1=%s WHERE t.col2 = %s LIMIT 1")

    def test_utc_timestamp(self):
        self.assert_compile(func.utc_timestamp(), "UTC_TIMESTAMP")

    def test_sysdate(self):
        self.assert_compile(func.sysdate(), "SYSDATE()")

    def test_cast(self):
        t = sql.table('t', sql.column('col'))
        m = mysql

        specs = [
            (Integer, "CAST(t.col AS SIGNED INTEGER)"),
            (INT, "CAST(t.col AS SIGNED INTEGER)"),
            (m.MSInteger, "CAST(t.col AS SIGNED INTEGER)"),
            (m.MSInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),
            (SmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
            (m.MSSmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
            (m.MSTinyInteger, "CAST(t.col AS SIGNED INTEGER)"),
            # 'SIGNED INTEGER' is a bigint, so this is ok.
            (m.MSBigInteger, "CAST(t.col AS SIGNED INTEGER)"),
            (m.MSBigInteger(unsigned=False), "CAST(t.col AS SIGNED INTEGER)"),
            (m.MSBigInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),

            # this is kind of sucky.  thank you default arguments!
            (NUMERIC, "CAST(t.col AS DECIMAL)"),
            (DECIMAL, "CAST(t.col AS DECIMAL)"),
            (Numeric, "CAST(t.col AS DECIMAL)"),
            (m.MSNumeric, "CAST(t.col AS DECIMAL)"),
            (m.MSDecimal, "CAST(t.col AS DECIMAL)"),
            (TIMESTAMP, "CAST(t.col AS DATETIME)"),
            (DATETIME, "CAST(t.col AS DATETIME)"),
            (DATE, "CAST(t.col AS DATE)"),
            (TIME, "CAST(t.col AS TIME)"),
            (DateTime, "CAST(t.col AS DATETIME)"),
            (Date, "CAST(t.col AS DATE)"),
            (Time, "CAST(t.col AS TIME)"),
            (DateTime, "CAST(t.col AS DATETIME)"),
            (Date, "CAST(t.col AS DATE)"),
            (m.MSTime, "CAST(t.col AS TIME)"),
            (m.MSTimeStamp, "CAST(t.col AS DATETIME)"),
            (String, "CAST(t.col AS CHAR)"),
            (Unicode, "CAST(t.col AS CHAR)"),
            (UnicodeText, "CAST(t.col AS CHAR)"),
            (VARCHAR, "CAST(t.col AS CHAR)"),
            (NCHAR, "CAST(t.col AS CHAR)"),
            (CHAR, "CAST(t.col AS CHAR)"),
            (m.CHAR(charset='utf8'), "CAST(t.col AS CHAR CHARACTER SET utf8)"),
            (CLOB, "CAST(t.col AS CHAR)"),
            (TEXT, "CAST(t.col AS CHAR)"),
            (m.TEXT(charset='utf8'), "CAST(t.col AS CHAR CHARACTER SET utf8)"),
            (String(32), "CAST(t.col AS CHAR(32))"),
            (Unicode(32), "CAST(t.col AS CHAR(32))"),
            (CHAR(32), "CAST(t.col AS CHAR(32))"),
            (m.MSString, "CAST(t.col AS CHAR)"),
            (m.MSText, "CAST(t.col AS CHAR)"),
            (m.MSTinyText, "CAST(t.col AS CHAR)"),
            (m.MSMediumText, "CAST(t.col AS CHAR)"),
            (m.MSLongText, "CAST(t.col AS CHAR)"),
            (m.MSNChar, "CAST(t.col AS CHAR)"),
            (m.MSNVarChar, "CAST(t.col AS CHAR)"),
            (LargeBinary, "CAST(t.col AS BINARY)"),
            (BLOB, "CAST(t.col AS BINARY)"),
            (m.MSBlob, "CAST(t.col AS BINARY)"),
            (m.MSBlob(32), "CAST(t.col AS BINARY)"),
            (m.MSTinyBlob, "CAST(t.col AS BINARY)"),
            (m.MSMediumBlob, "CAST(t.col AS BINARY)"),
            (m.MSLongBlob, "CAST(t.col AS BINARY)"),
            (m.MSBinary, "CAST(t.col AS BINARY)"),
            (m.MSBinary(32), "CAST(t.col AS BINARY)"),
            (m.MSVarBinary, "CAST(t.col AS BINARY)"),
            (m.MSVarBinary(32), "CAST(t.col AS BINARY)"),
            (Interval, "CAST(t.col AS DATETIME)"),
        ]

        for type_, expected in specs:
            self.assert_compile(cast(t.c.col, type_), expected)

    def test_cast_type_decorator(self):
        class MyInteger(sqltypes.TypeDecorator):
            impl = Integer

        type_ = MyInteger()
        t = sql.table('t', sql.column('col'))
        self.assert_compile(cast(t.c.col, type_),
                            "CAST(t.col AS SIGNED INTEGER)")

    def test_unsupported_casts(self):

        t = sql.table('t', sql.column('col'))
        m = mysql

        specs = [
            (m.MSBit, "t.col"),
            (FLOAT, "t.col"),
            (Float, "t.col"),
            (m.MSFloat, "t.col"),
            (m.MSDouble, "t.col"),
            (m.MSReal, "t.col"),
            (m.MSYear, "t.col"),
            (m.MSYear(2), "t.col"),
            (Boolean, "t.col"),
            (BOOLEAN, "t.col"),
            (m.MSEnum, "t.col"),
            (m.MSEnum("1", "2"), "t.col"),
            (m.MSSet, "t.col"),
            (m.MSSet("1", "2"), "t.col"),
        ]

        for type_, expected in specs:
            with expect_warnings(
                    "Datatype .* does not support CAST on MySQL;"):
                self.assert_compile(cast(t.c.col, type_), expected)

    def test_no_cast_pre_4(self):
        self.assert_compile(
            cast(Column('foo', Integer), String),
            "CAST(foo AS CHAR)",
        )
        dialect = mysql.dialect()
        dialect.server_version_info = (3, 2, 3)
        with expect_warnings("Current MySQL version does not support CAST;"):
            self.assert_compile(cast(Column('foo', Integer), String),
                                "foo",
                                dialect=dialect)

    def test_cast_grouped_expression_non_castable(self):
        with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
            self.assert_compile(cast(sql.column('x') + sql.column('y'), Float),
                                "(x + y)")

    def test_cast_grouped_expression_pre_4(self):
        dialect = mysql.dialect()
        dialect.server_version_info = (3, 2, 3)
        with expect_warnings("Current MySQL version does not support CAST;"):
            self.assert_compile(cast(
                sql.column('x') + sql.column('y'), Integer),
                                "(x + y)",
                                dialect=dialect)

    def test_extract(self):
        t = sql.table('t', sql.column('col1'))

        for field in 'year', 'month', 'day':
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                "SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field)

        # millsecondS to millisecond
        self.assert_compile(
            select([extract('milliseconds', t.c.col1)]),
            "SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t")

    def test_too_long_index(self):
        exp = 'ix_zyrenian_zyme_zyzzogeton_zyzzogeton_zyrenian_zyme_zyz_5cd2'
        tname = 'zyrenian_zyme_zyzzogeton_zyzzogeton'
        cname = 'zyrenian_zyme_zyzzogeton_zo'

        t1 = Table(
            tname,
            MetaData(),
            Column(cname, Integer, index=True),
        )
        ix1 = list(t1.indexes)[0]

        self.assert_compile(
            schema.CreateIndex(ix1), "CREATE INDEX %s "
            "ON %s (%s)" % (exp, tname, cname))

    def test_innodb_autoincrement(self):
        t1 = Table('sometable',
                   MetaData(),
                   Column('assigned_id',
                          Integer(),
                          primary_key=True,
                          autoincrement=False),
                   Column('id',
                          Integer(),
                          primary_key=True,
                          autoincrement=True),
                   mysql_engine='InnoDB')
        self.assert_compile(
            schema.CreateTable(t1), 'CREATE TABLE sometable (assigned_id '
            'INTEGER NOT NULL, id INTEGER NOT NULL '
            'AUTO_INCREMENT, PRIMARY KEY (assigned_id, '
            'id), KEY idx_autoinc_id (id))ENGINE=Inn'
            'oDB')

        t1 = Table('sometable',
                   MetaData(),
                   Column('assigned_id',
                          Integer(),
                          primary_key=True,
                          autoincrement=True),
                   Column('id',
                          Integer(),
                          primary_key=True,
                          autoincrement=False),
                   mysql_engine='InnoDB')
        self.assert_compile(
            schema.CreateTable(t1), 'CREATE TABLE sometable (assigned_id '
            'INTEGER NOT NULL AUTO_INCREMENT, id '
            'INTEGER NOT NULL, PRIMARY KEY '
            '(assigned_id, id))ENGINE=InnoDB')

    def test_innodb_autoincrement_reserved_word_column_name(self):
        t1 = Table('sometable',
                   MetaData(),
                   Column('id',
                          Integer(),
                          primary_key=True,
                          autoincrement=False),
                   Column('order',
                          Integer(),
                          primary_key=True,
                          autoincrement=True),
                   mysql_engine='InnoDB')
        self.assert_compile(
            schema.CreateTable(t1), 'CREATE TABLE sometable ('
            'id INTEGER NOT NULL, '
            '`order` INTEGER NOT NULL AUTO_INCREMENT, '
            'PRIMARY KEY (id, `order`), '
            'KEY idx_autoinc_order (`order`)'
            ')ENGINE=InnoDB')

    def test_create_table_with_partition(self):
        t1 = Table('testtable',
                   MetaData(),
                   Column('id',
                          Integer(),
                          primary_key=True,
                          autoincrement=True),
                   Column('other_id',
                          Integer(),
                          primary_key=True,
                          autoincrement=False),
                   mysql_partitions='2',
                   mysql_partition_by='KEY(other_id)')
        self.assert_compile(
            schema.CreateTable(t1), 'CREATE TABLE testtable ('
            'id INTEGER NOT NULL AUTO_INCREMENT, '
            'other_id INTEGER NOT NULL, '
            'PRIMARY KEY (id, other_id)'
            ')PARTITION BY KEY(other_id) PARTITIONS 2')

    def test_create_table_with_partition_hash(self):
        t1 = Table('testtable',
                   MetaData(),
                   Column('id',
                          Integer(),
                          primary_key=True,
                          autoincrement=True),
                   Column('other_id',
                          Integer(),
                          primary_key=True,
                          autoincrement=False),
                   mysql_partitions='2',
                   mysql_partition_by='HASH(other_id)')
        self.assert_compile(
            schema.CreateTable(t1), 'CREATE TABLE testtable ('
            'id INTEGER NOT NULL AUTO_INCREMENT, '
            'other_id INTEGER NOT NULL, '
            'PRIMARY KEY (id, other_id)'
            ')PARTITION BY HASH(other_id) PARTITIONS 2')
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):

    __dialect__ = mysql.dialect()

    def test_reserved_words(self):
        table = Table("mysql_table", MetaData(), Column("col1", Integer),
                      Column("master_ssl_verify_server_cert", Integer))
        x = select([table.c.col1, table.c.master_ssl_verify_server_cert])

        self.assert_compile(
            x, "SELECT mysql_table.col1, "
            "mysql_table.`master_ssl_verify_server_cert` FROM mysql_table")

    def test_create_index_simple(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('data', String(255)))
        idx = Index('test_idx1', tbl.c.data)

        self.assert_compile(schema.CreateIndex(idx),
                            'CREATE INDEX test_idx1 ON testtbl (data)')

    def test_create_index_with_length(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('data', String(255)))
        idx1 = Index('test_idx1', tbl.c.data, mysql_length=10)
        idx2 = Index('test_idx2', tbl.c.data, mysql_length=5)

        self.assert_compile(schema.CreateIndex(idx1),
                            'CREATE INDEX test_idx1 ON testtbl (data(10))')
        self.assert_compile(schema.CreateIndex(idx2),
                            'CREATE INDEX test_idx2 ON testtbl (data(5))')

    def test_create_index_with_length_quoted(self):
        m = MetaData()
        tbl = Table('testtbl', m,
                    Column('some quoted data', String(255), key='s'))
        idx1 = Index('test_idx1', tbl.c.s, mysql_length=10)

        self.assert_compile(
            schema.CreateIndex(idx1),
            'CREATE INDEX test_idx1 ON testtbl (`some quoted data`(10))')

    def test_create_composite_index_with_length_quoted(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('some Quoted a', String(255),
                                         key='a'),
                    Column('some Quoted b', String(255), key='b'))
        idx1 = Index('test_idx1',
                     tbl.c.a,
                     tbl.c.b,
                     mysql_length={
                         'some Quoted a': 10,
                         'some Quoted b': 20
                     })

        self.assert_compile(
            schema.CreateIndex(idx1), 'CREATE INDEX test_idx1 ON testtbl '
            '(`some Quoted a`(10), `some Quoted b`(20))')

    def test_create_composite_index_with_length_quoted_3085_workaround(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('some quoted a', String(255),
                                         key='a'),
                    Column('some quoted b', String(255), key='b'))
        idx1 = Index('test_idx1',
                     tbl.c.a,
                     tbl.c.b,
                     mysql_length={
                         '`some quoted a`': 10,
                         '`some quoted b`': 20
                     })

        self.assert_compile(
            schema.CreateIndex(idx1), 'CREATE INDEX test_idx1 ON testtbl '
            '(`some quoted a`(10), `some quoted b`(20))')

    def test_create_composite_index_with_length(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('a', String(255)),
                    Column('b', String(255)))

        idx1 = Index('test_idx1',
                     tbl.c.a,
                     tbl.c.b,
                     mysql_length={
                         'a': 10,
                         'b': 20
                     })
        idx2 = Index('test_idx2', tbl.c.a, tbl.c.b, mysql_length={'a': 15})
        idx3 = Index('test_idx3', tbl.c.a, tbl.c.b, mysql_length=30)

        self.assert_compile(
            schema.CreateIndex(idx1),
            'CREATE INDEX test_idx1 ON testtbl (a(10), b(20))')
        self.assert_compile(schema.CreateIndex(idx2),
                            'CREATE INDEX test_idx2 ON testtbl (a(15), b)')
        self.assert_compile(
            schema.CreateIndex(idx3),
            'CREATE INDEX test_idx3 ON testtbl (a(30), b(30))')

    def test_create_index_with_using(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('data', String(255)))
        idx1 = Index('test_idx1', tbl.c.data, mysql_using='btree')
        idx2 = Index('test_idx2', tbl.c.data, mysql_using='hash')

        self.assert_compile(
            schema.CreateIndex(idx1),
            'CREATE INDEX test_idx1 ON testtbl (data) USING btree')
        self.assert_compile(
            schema.CreateIndex(idx2),
            'CREATE INDEX test_idx2 ON testtbl (data) USING hash')

    def test_create_pk_plain(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('data', String(255)),
                    PrimaryKeyConstraint('data'))

        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE testtbl (data VARCHAR(255) NOT NULL, "
            "PRIMARY KEY (data))")

    def test_create_pk_with_using(self):
        m = MetaData()
        tbl = Table('testtbl', m, Column('data', String(255)),
                    PrimaryKeyConstraint('data', mysql_using='btree'))

        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE testtbl (data VARCHAR(255) NOT NULL, "
            "PRIMARY KEY (data) USING btree)")

    def test_create_index_expr(self):
        m = MetaData()
        t1 = Table('foo', m, Column('x', Integer))
        self.assert_compile(schema.CreateIndex(Index("bar", t1.c.x > 5)),
                            "CREATE INDEX bar ON foo (x > 5)")

    def test_deferrable_initially_kw_not_ignored(self):
        m = MetaData()
        Table('t1', m, Column('id', Integer, primary_key=True))
        t2 = Table(
            't2', m,
            Column('id',
                   Integer,
                   ForeignKey('t1.id', deferrable=True, initially="XYZ"),
                   primary_key=True))

        self.assert_compile(
            schema.CreateTable(t2), "CREATE TABLE t2 (id INTEGER NOT NULL, "
            "PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES t1 (id) "
            "DEFERRABLE INITIALLY XYZ)")

    def test_match_kw_raises(self):
        m = MetaData()
        Table('t1', m, Column('id', Integer, primary_key=True))
        t2 = Table(
            't2', m,
            Column('id',
                   Integer,
                   ForeignKey('t1.id', match="XYZ"),
                   primary_key=True))

        assert_raises_message(
            exc.CompileError,
            "MySQL ignores the 'MATCH' keyword while at the same time causes "
            "ON UPDATE/ON DELETE clauses to be ignored.",
            schema.CreateTable(t2).compile,
            dialect=mysql.dialect())

    def test_for_update(self):
        table1 = table('mytable', column('myid'), column('name'),
                       column('description'))

        self.assert_compile(
            table1.select(table1.c.myid == 7).with_for_update(),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s FOR UPDATE")

        self.assert_compile(
            table1.select(table1.c.myid == 7).with_for_update(read=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE")
示例#31
0
class EnumSetTest(fixtures.TestBase, AssertsExecutionResults,
                  AssertsCompiledSQL):

    __only_on__ = 'mysql'
    __dialect__ = mysql.dialect()
    __backend__ = True

    @testing.provide_metadata
    def test_enum(self):
        """Exercise the ENUM type."""

        with testing.expect_deprecated('Manually quoting ENUM value literals'):
            e1, e2 = mysql.ENUM("'a'", "'b'"), mysql.ENUM("'a'", "'b'")
            e3 = mysql.ENUM("'a'", "'b'", strict=True)
            e4 = mysql.ENUM("'a'", "'b'", strict=True)

        enum_table = Table(
            'mysql_enum',
            self.metadata,
            Column('e1', e1),
            Column('e2', e2, nullable=False),
            Column('e2generic',
                   Enum("a", "b", validate_strings=True),
                   nullable=False),
            Column('e3', e3),
            Column('e4', e4, nullable=False),
            Column('e5', mysql.ENUM("a", "b")),
            Column('e5generic', Enum("a", "b")),
            Column('e6', mysql.ENUM("'a'", "b")),
        )

        eq_(colspec(enum_table.c.e1), "e1 ENUM('a','b')")
        eq_(colspec(enum_table.c.e2), "e2 ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e2generic),
            "e2generic ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e3), "e3 ENUM('a','b')")
        eq_(colspec(enum_table.c.e4), "e4 ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e5), "e5 ENUM('a','b')")
        eq_(colspec(enum_table.c.e5generic), "e5generic ENUM('a','b')")
        eq_(colspec(enum_table.c.e6), "e6 ENUM('''a''','b')")
        enum_table.create()

        assert_raises(exc.DBAPIError,
                      enum_table.insert().execute,
                      e1=None,
                      e2=None,
                      e3=None,
                      e4=None)

        assert_raises(exc.StatementError,
                      enum_table.insert().execute,
                      e1='c',
                      e2='c',
                      e2generic='c',
                      e3='c',
                      e4='c',
                      e5='c',
                      e5generic='c',
                      e6='c')

        enum_table.insert().execute()
        enum_table.insert().execute(e1='a',
                                    e2='a',
                                    e2generic='a',
                                    e3='a',
                                    e4='a',
                                    e5='a',
                                    e5generic='a',
                                    e6="'a'")
        enum_table.insert().execute(e1='b',
                                    e2='b',
                                    e2generic='b',
                                    e3='b',
                                    e4='b',
                                    e5='b',
                                    e5generic='b',
                                    e6='b')

        res = enum_table.select().execute().fetchall()

        expected = [(None, 'a', 'a', None, 'a', None, None, None),
                    ('a', 'a', 'a', 'a', 'a', 'a', 'a', "'a'"),
                    ('b', 'b', 'b', 'b', 'b', 'b', 'b', 'b')]

        eq_(res, expected)

    def _set_fixture_one(self):
        with testing.expect_deprecated('Manually quoting SET value literals'):
            e1, e2 = mysql.SET("'a'", "'b'"), mysql.SET("'a'", "'b'")
            e4 = mysql.SET("'a'", "b")
            e5 = mysql.SET("'a'", "'b'", quoting="quoted")

        set_table = Table('mysql_set', self.metadata, Column('e1', e1),
                          Column('e2', e2, nullable=False),
                          Column('e3', mysql.SET("a", "b")), Column('e4', e4),
                          Column('e5', e5))
        return set_table

    def test_set_colspec(self):
        self.metadata = MetaData()
        set_table = self._set_fixture_one()
        eq_(colspec(set_table.c.e1), "e1 SET('a','b')")
        eq_(colspec(set_table.c.e2), "e2 SET('a','b') NOT NULL")
        eq_(colspec(set_table.c.e3), "e3 SET('a','b')")
        eq_(colspec(set_table.c.e4), "e4 SET('''a''','b')")
        eq_(colspec(set_table.c.e5), "e5 SET('a','b')")

    @testing.provide_metadata
    def test_no_null(self):
        set_table = self._set_fixture_one()
        set_table.create()
        assert_raises(exc.DBAPIError,
                      set_table.insert().execute,
                      e1=None,
                      e2=None,
                      e3=None,
                      e4=None)

    @testing.only_on('+oursql')
    @testing.provide_metadata
    def test_oursql_error_one(self):
        set_table = self._set_fixture_one()
        set_table.create()
        assert_raises(exc.StatementError,
                      set_table.insert().execute,
                      e1='c',
                      e2='c',
                      e3='c',
                      e4='c')

    @testing.requires.mysql_non_strict
    @testing.provide_metadata
    def test_empty_set_no_empty_string(self):
        t = Table('t', self.metadata, Column('id', Integer),
                  Column('data', mysql.SET("a", "b")))
        t.create()
        with testing.db.begin() as conn:
            conn.execute(
                t.insert(),
                {
                    'id': 1,
                    'data': set()
                },
                {
                    'id': 2,
                    'data': set([''])
                },
                {
                    'id': 3,
                    'data': set(['a', ''])
                },
                {
                    'id': 4,
                    'data': set(['b'])
                },
            )
            eq_(
                conn.execute(t.select().order_by(t.c.id)).fetchall(), [
                    (1, set()),
                    (2, set()),
                    (3, set(['a'])),
                    (4, set(['b'])),
                ])

    def test_bitwise_required_for_empty(self):
        assert_raises_message(
            exc.ArgumentError,
            "Can't use the blank value '' in a SET without setting "
            "retrieve_as_bitwise=True", mysql.SET, "a", "b", '')

    @testing.provide_metadata
    def test_empty_set_empty_string(self):
        t = Table(
            't', self.metadata, Column('id', Integer),
            Column('data', mysql.SET("a", "b", '', retrieve_as_bitwise=True)))
        t.create()
        with testing.db.begin() as conn:
            conn.execute(
                t.insert(),
                {
                    'id': 1,
                    'data': set()
                },
                {
                    'id': 2,
                    'data': set([''])
                },
                {
                    'id': 3,
                    'data': set(['a', ''])
                },
                {
                    'id': 4,
                    'data': set(['b'])
                },
            )
            eq_(
                conn.execute(t.select().order_by(t.c.id)).fetchall(), [
                    (1, set()),
                    (2, set([''])),
                    (3, set(['a', ''])),
                    (4, set(['b'])),
                ])

    @testing.provide_metadata
    def test_string_roundtrip(self):
        set_table = self._set_fixture_one()
        set_table.create()
        with testing.db.begin() as conn:
            conn.execute(set_table.insert(),
                         dict(e1='a', e2='a', e3='a', e4="'a'", e5="a,b"))
            conn.execute(set_table.insert(),
                         dict(e1='b', e2='b', e3='b', e4='b', e5="a,b"))

            expected = [(set(['a']), set(['a']), set(['a']), set(["'a'"]),
                         set(['a', 'b'])),
                        (set(['b']), set(['b']), set(['b']), set(['b']),
                         set(['a', 'b']))]
            res = conn.execute(set_table.select()).fetchall()

            eq_(res, expected)

    @testing.provide_metadata
    def test_unicode_roundtrip(self):
        set_table = Table(
            't',
            self.metadata,
            Column('id', Integer, primary_key=True),
            Column(
                'data',
                mysql.SET(u('réveillé'),
                          u('drôle'),
                          u('S’il'),
                          convert_unicode=True)),
        )

        set_table.create()
        with testing.db.begin() as conn:
            conn.execute(
                set_table.insert(),
                {"data": set([u('réveillé'), u('drôle')])})

            row = conn.execute(set_table.select()).first()

            eq_(row, (1, set([u('réveillé'), u('drôle')])))

    @testing.provide_metadata
    def test_int_roundtrip(self):
        set_table = self._set_fixture_one()
        set_table.create()
        with testing.db.begin() as conn:
            conn.execute(set_table.insert(), dict(e1=1, e2=2, e3=3, e4=3,
                                                  e5=0))
            res = conn.execute(set_table.select()).first()
            eq_(res, (set(['a']), set(['b']), set(['a', 'b']), set(
                ["'a'", 'b']), set([])))

    @testing.provide_metadata
    def test_set_roundtrip_plus_reflection(self):
        set_table = Table('mysql_set', self.metadata,
                          Column('s1', mysql.SET("dq", "sq")),
                          Column('s2', mysql.SET("a")),
                          Column('s3', mysql.SET("5", "7", "9")))

        eq_(colspec(set_table.c.s1), "s1 SET('dq','sq')")
        eq_(colspec(set_table.c.s2), "s2 SET('a')")
        eq_(colspec(set_table.c.s3), "s3 SET('5','7','9')")
        set_table.create()
        reflected = Table('mysql_set', MetaData(testing.db), autoload=True)
        for table in set_table, reflected:

            def roundtrip(store, expected=None):
                expected = expected or store
                table.insert(store).execute()
                row = table.select().execute().first()
                eq_(row, tuple(expected))
                table.delete().execute()

            roundtrip([None, None, None], [None] * 3)
            roundtrip(['', '', ''], [set([])] * 3)
            roundtrip([set(['dq']), set(['a']), set(['5'])])
            roundtrip(['dq', 'a', '5'], [set(['dq']), set(['a']), set(['5'])])
            roundtrip([1, 1, 1], [set(['dq']), set(['a']), set(['5'])])
            roundtrip([set(['dq', 'sq']), None, set(['9', '5', '7'])])
        set_table.insert().execute({'s3': set(['5'])}, {'s3': set(['5', '7'])},
                                   {'s3': set(['5', '7', '9'])},
                                   {'s3': set(['7', '9'])})

        rows = select([set_table.c.s3],
                      set_table.c.s3.in_([set(['5']),
                                          ['5', '7']])).execute().fetchall()
        found = set([frozenset(row[0]) for row in rows])
        eq_(found, set([frozenset(['5']), frozenset(['5', '7'])]))

    @testing.provide_metadata
    def test_unicode_enum(self):
        metadata = self.metadata
        t1 = Table(
            'table', metadata, Column('id', Integer, primary_key=True),
            Column('value', Enum(u('réveillé'), u('drôle'), u('S’il'))),
            Column('value2', mysql.ENUM(u('réveillé'), u('drôle'), u('S’il'))))
        metadata.create_all()
        t1.insert().execute(value=u('drôle'), value2=u('drôle'))
        t1.insert().execute(value=u('réveillé'), value2=u('réveillé'))
        t1.insert().execute(value=u('S’il'), value2=u('S’il'))
        eq_(t1.select().order_by(t1.c.id).execute().fetchall(),
            [(1, u('drôle'), u('drôle')), (2, u('réveillé'), u('réveillé')),
             (3, u('S’il'), u('S’il'))])

        # test reflection of the enum labels

        m2 = MetaData(testing.db)
        t2 = Table('table', m2, autoload=True)

        # TODO: what's wrong with the last element ?  is there
        # latin-1 stuff forcing its way in ?

        eq_(
            t2.c.value.type.enums[0:2],
            [u('réveillé'), u('drôle')]  # u'S’il') # eh ?
        )

        eq_(
            t2.c.value2.type.enums[0:2],
            [u('réveillé'), u('drôle')]  # u'S’il') # eh ?
        )

    def test_enum_compile(self):
        e1 = Enum('x', 'y', 'z', name='somename')
        t1 = Table('sometable', MetaData(), Column('somecolumn', e1))
        self.assert_compile(
            schema.CreateTable(t1), "CREATE TABLE sometable (somecolumn "
            "ENUM('x','y','z'))")
        t1 = Table(
            'sometable', MetaData(),
            Column('somecolumn', Enum('x', 'y', 'z', native_enum=False)))
        self.assert_compile(
            schema.CreateTable(t1), "CREATE TABLE sometable (somecolumn "
            "VARCHAR(1), CHECK (somecolumn IN ('x', "
            "'y', 'z')))")

    @testing.provide_metadata
    @testing.exclude('mysql', '<', (4, ), "3.23 can't handle an ENUM of ''")
    def test_enum_parse(self):

        with testing.expect_deprecated('Manually quoting ENUM value literals'):
            enum_table = Table(
                'mysql_enum', self.metadata, Column('e1', mysql.ENUM("'a'")),
                Column('e2', mysql.ENUM("''")), Column('e3', mysql.ENUM('a')),
                Column('e4', mysql.ENUM('')),
                Column('e5', mysql.ENUM("'a'", "''")),
                Column('e6', mysql.ENUM("''", "'a'")),
                Column('e7', mysql.ENUM("''", "'''a'''", "'b''b'", "''''")))

        for col in enum_table.c:
            self.assert_(repr(col))

        enum_table.create()
        reflected = Table('mysql_enum', MetaData(testing.db), autoload=True)
        for t in enum_table, reflected:
            eq_(t.c.e1.type.enums, ["a"])
            eq_(t.c.e2.type.enums, [""])
            eq_(t.c.e3.type.enums, ["a"])
            eq_(t.c.e4.type.enums, [""])
            eq_(t.c.e5.type.enums, ["a", ""])
            eq_(t.c.e6.type.enums, ["", "a"])
            eq_(t.c.e7.type.enums, ["", "'a'", "b'b", "'"])

    @testing.provide_metadata
    @testing.exclude('mysql', '<', (5, ))
    def test_set_parse(self):
        with testing.expect_deprecated('Manually quoting SET value literals'):
            set_table = Table(
                'mysql_set', self.metadata, Column('e1', mysql.SET("'a'")),
                Column('e2', mysql.SET("''", retrieve_as_bitwise=True)),
                Column('e3', mysql.SET('a')),
                Column('e4', mysql.SET('', retrieve_as_bitwise=True)),
                Column('e5', mysql.SET("'a'", "''", retrieve_as_bitwise=True)),
                Column('e6', mysql.SET("''", "'a'", retrieve_as_bitwise=True)),
                Column(
                    'e7',
                    mysql.SET("''",
                              "'''a'''",
                              "'b''b'",
                              "''''",
                              retrieve_as_bitwise=True)))

        for col in set_table.c:
            self.assert_(repr(col))

        set_table.create()

        # don't want any warnings on reflection
        reflected = Table('mysql_set', MetaData(testing.db), autoload=True)
        for t in set_table, reflected:
            eq_(t.c.e1.type.values, ("a", ))
            eq_(t.c.e2.type.values, ("", ))
            eq_(t.c.e3.type.values, ("a", ))
            eq_(t.c.e4.type.values, ("", ))
            eq_(t.c.e5.type.values, ("a", ""))
            eq_(t.c.e6.type.values, ("", "a"))
            eq_(t.c.e7.type.values, ("", "'a'", "b'b", "'"))

    @testing.requires.mysql_non_strict
    @testing.provide_metadata
    def test_broken_enum_returns_blanks(self):
        t = Table('enum_missing', self.metadata,
                  Column('id', Integer, primary_key=True),
                  Column('e1', sqltypes.Enum('one', 'two', 'three')),
                  Column('e2', mysql.ENUM('one', 'two', 'three')))
        t.create()

        with testing.db.connect() as conn:
            conn.execute(t.insert(), {
                "e1": "nonexistent",
                "e2": "nonexistent"
            })
            conn.execute(t.insert(), {"e1": "", "e2": ""})
            conn.execute(t.insert(), {"e1": "two", "e2": "two"})
            conn.execute(t.insert(), {"e1": None, "e2": None})

            eq_(
                conn.execute(select([t.c.e1,
                                     t.c.e2]).order_by(t.c.id)).fetchall(),
                [("", ""), ("", ""), ("two", "two"), (None, None)])
示例#32
0
class InsertOnDuplicateTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mysql.dialect()

    def setup_test(self):
        self.table = Table(
            "foos",
            MetaData(),
            Column("id", Integer, primary_key=True),
            Column("bar", String(10)),
            Column("baz", String(10)),
        )

    def test_no_call_twice(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar=stmt.inserted.bar,
                                            baz=stmt.inserted.baz)
        with testing.expect_raises_message(
                exc.InvalidRequestError,
                "This Insert construct already has an "
                "ON DUPLICATE KEY clause present",
        ):
            stmt = stmt.on_duplicate_key_update(bar=stmt.inserted.bar,
                                                baz=stmt.inserted.baz)

    def test_from_values(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar=stmt.inserted.bar,
                                            baz=stmt.inserted.baz)
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
            "ON DUPLICATE KEY UPDATE bar = VALUES(bar), baz = VALUES(baz)")
        self.assert_compile(stmt, expected_sql)

    def test_from_literal(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar=literal_column("bb"))
        expected_sql = ("INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
                        "ON DUPLICATE KEY UPDATE bar = bb")
        self.assert_compile(stmt, expected_sql)

    def test_python_values(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(bar="foobar")
        expected_sql = ("INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
                        "ON DUPLICATE KEY UPDATE bar = %s")
        self.assert_compile(stmt, expected_sql)

    def test_update_sql_expr(self):
        stmt = insert(self.table).values([{
            "id": 1,
            "bar": "ab"
        }, {
            "id": 2,
            "bar": "b"
        }])
        stmt = stmt.on_duplicate_key_update(
            bar=func.coalesce(stmt.inserted.bar),
            baz=stmt.inserted.baz + "some literal",
        )
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) ON "
            "DUPLICATE KEY UPDATE bar = coalesce(VALUES(bar)), "
            "baz = (concat(VALUES(baz), %s))")
        self.assert_compile(
            stmt,
            expected_sql,
            checkparams={
                "id_m0": 1,
                "bar_m0": "ab",
                "id_m1": 2,
                "bar_m1": "b",
                "baz_1": "some literal",
            },
        )
示例#33
0
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):

    __dialect__ = mysql.dialect()

    def test_reserved_words(self):
        table = Table(
            "mysql_table",
            MetaData(),
            Column("col1", Integer),
            Column("master_ssl_verify_server_cert", Integer),
        )
        x = select([table.c.col1, table.c.master_ssl_verify_server_cert])

        self.assert_compile(
            x,
            "SELECT mysql_table.col1, "
            "mysql_table.`master_ssl_verify_server_cert` FROM mysql_table",
        )

    def test_create_index_simple(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx = Index("test_idx1", tbl.c.data)

        self.assert_compile(schema.CreateIndex(idx),
                            "CREATE INDEX test_idx1 ON testtbl (data)")

    def test_create_index_with_prefix(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx = Index("test_idx1",
                    tbl.c.data,
                    mysql_length=10,
                    mysql_prefix="FULLTEXT")

        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE FULLTEXT INDEX test_idx1 "
            "ON testtbl (data(10))",
        )

    def test_create_index_with_parser(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx = Index(
            "test_idx1",
            tbl.c.data,
            mysql_length=10,
            mysql_prefix="FULLTEXT",
            mysql_with_parser="ngram",
        )

        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE FULLTEXT INDEX test_idx1 "
            "ON testtbl (data(10)) WITH PARSER ngram",
        )

    def test_create_index_with_length(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx1 = Index("test_idx1", tbl.c.data, mysql_length=10)
        idx2 = Index("test_idx2", tbl.c.data, mysql_length=5)

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (data(10))",
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl (data(5))",
        )

    def test_drop_constraint_mysql(self):
        m = MetaData()
        table_name = "testtbl"
        constraint_name = "constraint"
        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
        Table(table_name, m, Column("data", String(255)), constraint)
        dialect = mysql.dialect()
        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE %s DROP CHECK `%s`" % (table_name, constraint_name),
            dialect=dialect,
        )

    def test_drop_constraint_mariadb(self):
        m = MetaData()
        table_name = "testtbl"
        constraint_name = "constraint"
        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
        Table(table_name, m, Column("data", String(255)), constraint)
        dialect = mysql.dialect()
        dialect.server_version_info = (10, 1, 1, "MariaDB")
        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE %s DROP CONSTRAINT `%s`" %
            (table_name, constraint_name),
            dialect=dialect,
        )

    def test_create_index_with_length_quoted(self):
        m = MetaData()
        tbl = Table("testtbl", m,
                    Column("some quoted data", String(255), key="s"))
        idx1 = Index("test_idx1", tbl.c.s, mysql_length=10)

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (`some quoted data`(10))",
        )

    def test_create_composite_index_with_length_quoted(self):
        m = MetaData()
        tbl = Table(
            "testtbl",
            m,
            Column("some Quoted a", String(255), key="a"),
            Column("some Quoted b", String(255), key="b"),
        )
        idx1 = Index(
            "test_idx1",
            tbl.c.a,
            tbl.c.b,
            mysql_length={
                "some Quoted a": 10,
                "some Quoted b": 20
            },
        )

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl "
            "(`some Quoted a`(10), `some Quoted b`(20))",
        )

    def test_create_composite_index_with_length_quoted_3085_workaround(self):
        m = MetaData()
        tbl = Table(
            "testtbl",
            m,
            Column("some quoted a", String(255), key="a"),
            Column("some quoted b", String(255), key="b"),
        )
        idx1 = Index(
            "test_idx1",
            tbl.c.a,
            tbl.c.b,
            mysql_length={
                "`some quoted a`": 10,
                "`some quoted b`": 20
            },
        )

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl "
            "(`some quoted a`(10), `some quoted b`(20))",
        )

    def test_create_composite_index_with_length(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("a", String(255)),
                    Column("b", String(255)))

        idx1 = Index("test_idx1",
                     tbl.c.a,
                     tbl.c.b,
                     mysql_length={
                         "a": 10,
                         "b": 20
                     })
        idx2 = Index("test_idx2", tbl.c.a, tbl.c.b, mysql_length={"a": 15})
        idx3 = Index("test_idx3", tbl.c.a, tbl.c.b, mysql_length=30)

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (a(10), b(20))",
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl (a(15), b)",
        )
        self.assert_compile(
            schema.CreateIndex(idx3),
            "CREATE INDEX test_idx3 ON testtbl (a(30), b(30))",
        )

    def test_create_index_with_using(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx1 = Index("test_idx1", tbl.c.data, mysql_using="btree")
        idx2 = Index("test_idx2", tbl.c.data, mysql_using="hash")

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (data) USING btree",
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl (data) USING hash",
        )

    def test_create_pk_plain(self):
        m = MetaData()
        tbl = Table(
            "testtbl",
            m,
            Column("data", String(255)),
            PrimaryKeyConstraint("data"),
        )

        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE testtbl (data VARCHAR(255) NOT NULL, "
            "PRIMARY KEY (data))",
        )

    def test_create_pk_with_using(self):
        m = MetaData()
        tbl = Table(
            "testtbl",
            m,
            Column("data", String(255)),
            PrimaryKeyConstraint("data", mysql_using="btree"),
        )

        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE testtbl (data VARCHAR(255) NOT NULL, "
            "PRIMARY KEY (data) USING btree)",
        )

    def test_create_index_expr(self):
        m = MetaData()
        t1 = Table("foo", m, Column("x", Integer))
        self.assert_compile(
            schema.CreateIndex(Index("bar", t1.c.x > 5)),
            "CREATE INDEX bar ON foo (x > 5)",
        )

    def test_deferrable_initially_kw_not_ignored(self):
        m = MetaData()
        Table("t1", m, Column("id", Integer, primary_key=True))
        t2 = Table(
            "t2",
            m,
            Column(
                "id",
                Integer,
                ForeignKey("t1.id", deferrable=True, initially="DEFERRED"),
                primary_key=True,
            ),
        )

        self.assert_compile(
            schema.CreateTable(t2),
            "CREATE TABLE t2 (id INTEGER NOT NULL, "
            "PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES t1 (id) "
            "DEFERRABLE INITIALLY DEFERRED)",
        )

    def test_match_kw_raises(self):
        m = MetaData()
        Table("t1", m, Column("id", Integer, primary_key=True))
        t2 = Table(
            "t2",
            m,
            Column(
                "id",
                Integer,
                ForeignKey("t1.id", match="XYZ"),
                primary_key=True,
            ),
        )

        assert_raises_message(
            exc.CompileError,
            "MySQL ignores the 'MATCH' keyword while at the same time causes "
            "ON UPDATE/ON DELETE clauses to be ignored.",
            schema.CreateTable(t2).compile,
            dialect=mysql.dialect(),
        )

    def test_match(self):
        matchtable = table("matchtable", column("title", String))
        self.assert_compile(
            matchtable.c.title.match("somstr"),
            "MATCH (matchtable.title) AGAINST (%s IN BOOLEAN MODE)",
        )

    def test_match_compile_kw(self):
        expr = literal("x").match(literal("y"))
        self.assert_compile(
            expr,
            "MATCH ('x') AGAINST ('y' IN BOOLEAN MODE)",
            literal_binds=True,
        )

    def test_concat_compile_kw(self):
        expr = literal("x", type_=String) + literal("y", type_=String)
        self.assert_compile(expr, "concat('x', 'y')", literal_binds=True)

    def test_for_update(self):
        table1 = table("mytable", column("myid"), column("name"),
                       column("description"))

        self.assert_compile(
            table1.select(table1.c.myid == 7).with_for_update(),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s FOR UPDATE",
        )

        self.assert_compile(
            table1.select(table1.c.myid == 7).with_for_update(read=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE",
        )

    def test_delete_extra_froms(self):
        t1 = table("t1", column("c1"))
        t2 = table("t2", column("c1"))
        q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
        self.assert_compile(q,
                            "DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1")

    def test_delete_extra_froms_alias(self):
        a1 = table("t1", column("c1")).alias("a1")
        t2 = table("t2", column("c1"))
        q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
        self.assert_compile(
            q, "DELETE FROM a1 USING t1 AS a1, t2 WHERE a1.c1 = t2.c1")
        self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")

    @testing.combinations(
        ("no_persisted", "", "ignore"),
        ("persisted_none", "", None),
        ("persisted_true", " STORED", True),
        ("persisted_false", " VIRTUAL", False),
        id_="iaa",
    )
    def test_column_computed(self, text, persisted):
        m = MetaData()
        kwargs = {"persisted": persisted} if persisted != "ignore" else {}
        t = Table(
            "t",
            m,
            Column("x", Integer),
            Column("y", Integer, Computed("x + 2", **kwargs)),
        )
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE t (x INTEGER, y INTEGER GENERATED "
            "ALWAYS AS (x + 2)%s)" % text,
        )
示例#34
0
 def setup(self):
     dialect = mysql.dialect()
     self.parser = mysql.MySQLTableDefinitionParser(dialect, dialect.identifier_preparer)
示例#35
0
class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
    """Tests MySQL-dialect specific compilation."""

    __dialect__ = mysql.dialect()

    def test_precolumns(self):
        dialect = self.__dialect__

        def gen(distinct=None, prefixes=None):
            kw = {}
            if distinct is not None:
                kw["distinct"] = distinct
            if prefixes is not None:
                kw["prefixes"] = prefixes
            return str(select([column("q")], **kw).compile(dialect=dialect))

        eq_(gen(None), "SELECT q")
        eq_(gen(True), "SELECT DISTINCT q")

        eq_(gen(prefixes=["ALL"]), "SELECT ALL q")
        eq_(gen(prefixes=["DISTINCTROW"]), "SELECT DISTINCTROW q")

        # Interaction with MySQL prefix extensions
        eq_(gen(None, ["straight_join"]), "SELECT straight_join q")
        eq_(
            gen(False, ["HIGH_PRIORITY", "SQL_SMALL_RESULT", "ALL"]),
            "SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL q",
        )
        eq_(
            gen(True, ["high_priority", sql.text("sql_cache")]),
            "SELECT high_priority sql_cache DISTINCT q",
        )

    def test_backslash_escaping(self):
        self.assert_compile(
            sql.column("foo").like("bar", escape="\\"),
            "foo LIKE %s ESCAPE '\\\\'",
        )

        dialect = mysql.dialect()
        dialect._backslash_escapes = False
        self.assert_compile(
            sql.column("foo").like("bar", escape="\\"),
            "foo LIKE %s ESCAPE '\\'",
            dialect=dialect,
        )

    def test_limit(self):
        t = sql.table("t", sql.column("col1"), sql.column("col2"))

        self.assert_compile(
            select([t]).limit(10).offset(20),
            "SELECT t.col1, t.col2 FROM t  LIMIT %s, %s",
            {
                "param_1": 20,
                "param_2": 10
            },
        )
        self.assert_compile(
            select([t]).limit(10),
            "SELECT t.col1, t.col2 FROM t  LIMIT %s",
            {"param_1": 10},
        )

        self.assert_compile(
            select([t]).offset(10),
            "SELECT t.col1, t.col2 FROM t  LIMIT %s, 18446744073709551615",
            {"param_1": 10},
        )

    @testing.combinations(
        (String, ),
        (VARCHAR, ),
        (String(), ),
        (VARCHAR(), ),
        (NVARCHAR(), ),
        (Unicode, ),
        (Unicode(), ),
    )
    def test_varchar_raise(self, type_):
        type_ = sqltypes.to_instance(type_)
        assert_raises_message(
            exc.CompileError,
            "VARCHAR requires a length on dialect mysql",
            type_.compile,
            dialect=mysql.dialect(),
        )

        t1 = Table("sometable", MetaData(), Column("somecolumn", type_))
        assert_raises_message(
            exc.CompileError,
            r"\(in table 'sometable', column 'somecolumn'\)\: "
            r"(?:N)?VARCHAR requires a length on dialect mysql",
            schema.CreateTable(t1).compile,
            dialect=mysql.dialect(),
        )

    def test_update_limit(self):
        t = sql.table("t", sql.column("col1"), sql.column("col2"))

        self.assert_compile(t.update(values={"col1": 123}),
                            "UPDATE t SET col1=%s")
        self.assert_compile(
            t.update(values={"col1": 123}, mysql_limit=5),
            "UPDATE t SET col1=%s LIMIT 5",
        )
        self.assert_compile(
            t.update(values={"col1": 123}, mysql_limit=None),
            "UPDATE t SET col1=%s",
        )
        self.assert_compile(
            t.update(t.c.col2 == 456, values={"col1": 123}, mysql_limit=1),
            "UPDATE t SET col1=%s WHERE t.col2 = %s LIMIT 1",
        )

    def test_utc_timestamp(self):
        self.assert_compile(func.utc_timestamp(), "utc_timestamp()")

    def test_utc_timestamp_fsp(self):
        self.assert_compile(
            func.utc_timestamp(5),
            "utc_timestamp(%s)",
            checkparams={"utc_timestamp_1": 5},
        )

    def test_sysdate(self):
        self.assert_compile(func.sysdate(), "SYSDATE()")

    m = mysql

    @testing.combinations(
        (Integer, "CAST(t.col AS SIGNED INTEGER)"),
        (INT, "CAST(t.col AS SIGNED INTEGER)"),
        (m.MSInteger, "CAST(t.col AS SIGNED INTEGER)"),
        (m.MSInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),
        (SmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
        (m.MSSmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
        (m.MSTinyInteger, "CAST(t.col AS SIGNED INTEGER)"),
        # 'SIGNED INTEGER' is a bigint, so this is ok.
        (m.MSBigInteger, "CAST(t.col AS SIGNED INTEGER)"),
        (m.MSBigInteger(unsigned=False), "CAST(t.col AS SIGNED INTEGER)"),
        (m.MSBigInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),
        # this is kind of sucky.  thank you default arguments!
        (NUMERIC, "CAST(t.col AS DECIMAL)"),
        (DECIMAL, "CAST(t.col AS DECIMAL)"),
        (Numeric, "CAST(t.col AS DECIMAL)"),
        (m.MSNumeric, "CAST(t.col AS DECIMAL)"),
        (m.MSDecimal, "CAST(t.col AS DECIMAL)"),
        (TIMESTAMP, "CAST(t.col AS DATETIME)"),
        (DATETIME, "CAST(t.col AS DATETIME)"),
        (DATE, "CAST(t.col AS DATE)"),
        (TIME, "CAST(t.col AS TIME)"),
        (DateTime, "CAST(t.col AS DATETIME)"),
        (Date, "CAST(t.col AS DATE)"),
        (Time, "CAST(t.col AS TIME)"),
        (DateTime, "CAST(t.col AS DATETIME)"),
        (Date, "CAST(t.col AS DATE)"),
        (m.MSTime, "CAST(t.col AS TIME)"),
        (m.MSTimeStamp, "CAST(t.col AS DATETIME)"),
        (String, "CAST(t.col AS CHAR)"),
        (Unicode, "CAST(t.col AS CHAR)"),
        (UnicodeText, "CAST(t.col AS CHAR)"),
        (VARCHAR, "CAST(t.col AS CHAR)"),
        (NCHAR, "CAST(t.col AS CHAR)"),
        (CHAR, "CAST(t.col AS CHAR)"),
        (m.CHAR(charset="utf8"), "CAST(t.col AS CHAR CHARACTER SET utf8)"),
        (CLOB, "CAST(t.col AS CHAR)"),
        (TEXT, "CAST(t.col AS CHAR)"),
        (m.TEXT(charset="utf8"), "CAST(t.col AS CHAR CHARACTER SET utf8)"),
        (String(32), "CAST(t.col AS CHAR(32))"),
        (Unicode(32), "CAST(t.col AS CHAR(32))"),
        (CHAR(32), "CAST(t.col AS CHAR(32))"),
        (m.MSString, "CAST(t.col AS CHAR)"),
        (m.MSText, "CAST(t.col AS CHAR)"),
        (m.MSTinyText, "CAST(t.col AS CHAR)"),
        (m.MSMediumText, "CAST(t.col AS CHAR)"),
        (m.MSLongText, "CAST(t.col AS CHAR)"),
        (m.MSNChar, "CAST(t.col AS CHAR)"),
        (m.MSNVarChar, "CAST(t.col AS CHAR)"),
        (LargeBinary, "CAST(t.col AS BINARY)"),
        (BLOB, "CAST(t.col AS BINARY)"),
        (m.MSBlob, "CAST(t.col AS BINARY)"),
        (m.MSBlob(32), "CAST(t.col AS BINARY)"),
        (m.MSTinyBlob, "CAST(t.col AS BINARY)"),
        (m.MSMediumBlob, "CAST(t.col AS BINARY)"),
        (m.MSLongBlob, "CAST(t.col AS BINARY)"),
        (m.MSBinary, "CAST(t.col AS BINARY)"),
        (m.MSBinary(32), "CAST(t.col AS BINARY)"),
        (m.MSVarBinary, "CAST(t.col AS BINARY)"),
        (m.MSVarBinary(32), "CAST(t.col AS BINARY)"),
        (Interval, "CAST(t.col AS DATETIME)"),
    )
    def test_cast(self, type_, expected):
        t = sql.table("t", sql.column("col"))
        self.assert_compile(cast(t.c.col, type_), expected)

    def test_cast_type_decorator(self):
        class MyInteger(sqltypes.TypeDecorator):
            impl = Integer

        type_ = MyInteger()
        t = sql.table("t", sql.column("col"))
        self.assert_compile(cast(t.c.col, type_),
                            "CAST(t.col AS SIGNED INTEGER)")

    def test_cast_literal_bind(self):
        expr = cast(column("foo", Integer) + 5, Integer())

        self.assert_compile(expr,
                            "CAST(foo + 5 AS SIGNED INTEGER)",
                            literal_binds=True)

    def test_unsupported_cast_literal_bind(self):
        expr = cast(column("foo", Integer) + 5, Float)

        with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
            self.assert_compile(expr, "(foo + 5)", literal_binds=True)

        dialect = mysql.MySQLDialect()
        dialect.server_version_info = (3, 9, 8)
        with expect_warnings("Current MySQL version does not support CAST"):
            eq_(
                str(
                    expr.compile(dialect=dialect,
                                 compile_kwargs={"literal_binds": True})),
                "(foo + 5)",
            )

    m = mysql

    @testing.combinations(
        (m.MSBit, "t.col"),
        (FLOAT, "t.col"),
        (Float, "t.col"),
        (m.MSFloat, "t.col"),
        (m.MSDouble, "t.col"),
        (m.MSReal, "t.col"),
        (m.MSYear, "t.col"),
        (m.MSYear(2), "t.col"),
        (Boolean, "t.col"),
        (BOOLEAN, "t.col"),
        (m.MSEnum, "t.col"),
        (m.MSEnum("1", "2"), "t.col"),
        (m.MSSet, "t.col"),
        (m.MSSet("1", "2"), "t.col"),
    )
    def test_unsupported_casts(self, type_, expected):

        t = sql.table("t", sql.column("col"))
        with expect_warnings("Datatype .* does not support CAST on MySQL;"):
            self.assert_compile(cast(t.c.col, type_), expected)

    def test_no_cast_pre_4(self):
        self.assert_compile(cast(Column("foo", Integer), String),
                            "CAST(foo AS CHAR)")
        dialect = mysql.dialect()
        dialect.server_version_info = (3, 2, 3)
        with expect_warnings("Current MySQL version does not support CAST;"):
            self.assert_compile(cast(Column("foo", Integer), String),
                                "foo",
                                dialect=dialect)

    def test_cast_grouped_expression_non_castable(self):
        with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
            self.assert_compile(cast(sql.column("x") + sql.column("y"), Float),
                                "(x + y)")

    def test_cast_grouped_expression_pre_4(self):
        dialect = mysql.dialect()
        dialect.server_version_info = (3, 2, 3)
        with expect_warnings("Current MySQL version does not support CAST;"):
            self.assert_compile(
                cast(sql.column("x") + sql.column("y"), Integer),
                "(x + y)",
                dialect=dialect,
            )

    def test_extract(self):
        t = sql.table("t", sql.column("col1"))

        for field in "year", "month", "day":
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                "SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field,
            )

        # millsecondS to millisecond
        self.assert_compile(
            select([extract("milliseconds", t.c.col1)]),
            "SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t",
        )

    def test_too_long_index(self):
        exp = "ix_zyrenian_zyme_zyzzogeton_zyzzogeton_zyrenian_zyme_zyz_5cd2"
        tname = "zyrenian_zyme_zyzzogeton_zyzzogeton"
        cname = "zyrenian_zyme_zyzzogeton_zo"

        t1 = Table(tname, MetaData(), Column(cname, Integer, index=True))
        ix1 = list(t1.indexes)[0]

        self.assert_compile(
            schema.CreateIndex(ix1),
            "CREATE INDEX %s "
            "ON %s (%s)" % (exp, tname, cname),
        )

    def test_innodb_autoincrement(self):
        t1 = Table(
            "sometable",
            MetaData(),
            Column("assigned_id",
                   Integer(),
                   primary_key=True,
                   autoincrement=False),
            Column("id", Integer(), primary_key=True, autoincrement=True),
            mysql_engine="InnoDB",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE sometable (assigned_id "
            "INTEGER NOT NULL, id INTEGER NOT NULL "
            "AUTO_INCREMENT, PRIMARY KEY (id, assigned_id)"
            ")ENGINE=InnoDB",
        )

        t1 = Table(
            "sometable",
            MetaData(),
            Column("assigned_id",
                   Integer(),
                   primary_key=True,
                   autoincrement=True),
            Column("id", Integer(), primary_key=True, autoincrement=False),
            mysql_engine="InnoDB",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE sometable (assigned_id "
            "INTEGER NOT NULL AUTO_INCREMENT, id "
            "INTEGER NOT NULL, PRIMARY KEY "
            "(assigned_id, id))ENGINE=InnoDB",
        )

    def test_innodb_autoincrement_reserved_word_column_name(self):
        t1 = Table(
            "sometable",
            MetaData(),
            Column("id", Integer(), primary_key=True, autoincrement=False),
            Column("order", Integer(), primary_key=True, autoincrement=True),
            mysql_engine="InnoDB",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE sometable ("
            "id INTEGER NOT NULL, "
            "`order` INTEGER NOT NULL AUTO_INCREMENT, "
            "PRIMARY KEY (`order`, id)"
            ")ENGINE=InnoDB",
        )

    def test_create_table_with_partition(self):
        t1 = Table(
            "testtable",
            MetaData(),
            Column("id", Integer(), primary_key=True, autoincrement=True),
            Column("other_id",
                   Integer(),
                   primary_key=True,
                   autoincrement=False),
            mysql_partitions="2",
            mysql_partition_by="KEY(other_id)",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE testtable ("
            "id INTEGER NOT NULL AUTO_INCREMENT, "
            "other_id INTEGER NOT NULL, "
            "PRIMARY KEY (id, other_id)"
            ")PARTITION BY KEY(other_id) PARTITIONS 2",
        )

    def test_create_table_with_subpartition(self):
        t1 = Table(
            "testtable",
            MetaData(),
            Column("id", Integer(), primary_key=True, autoincrement=True),
            Column("other_id",
                   Integer(),
                   primary_key=True,
                   autoincrement=False),
            mysql_partitions="2",
            mysql_partition_by="KEY(other_id)",
            mysql_subpartition_by="HASH(some_expr)",
            mysql_subpartitions="2",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE testtable ("
            "id INTEGER NOT NULL AUTO_INCREMENT, "
            "other_id INTEGER NOT NULL, "
            "PRIMARY KEY (id, other_id)"
            ")PARTITION BY KEY(other_id) PARTITIONS 2 "
            "SUBPARTITION BY HASH(some_expr) SUBPARTITIONS 2",
        )

    def test_create_table_with_partition_hash(self):
        t1 = Table(
            "testtable",
            MetaData(),
            Column("id", Integer(), primary_key=True, autoincrement=True),
            Column("other_id",
                   Integer(),
                   primary_key=True,
                   autoincrement=False),
            mysql_partitions="2",
            mysql_partition_by="HASH(other_id)",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE testtable ("
            "id INTEGER NOT NULL AUTO_INCREMENT, "
            "other_id INTEGER NOT NULL, "
            "PRIMARY KEY (id, other_id)"
            ")PARTITION BY HASH(other_id) PARTITIONS 2",
        )

    def test_create_table_with_partition_and_other_opts(self):
        t1 = Table(
            "testtable",
            MetaData(),
            Column("id", Integer(), primary_key=True, autoincrement=True),
            Column("other_id",
                   Integer(),
                   primary_key=True,
                   autoincrement=False),
            mysql_stats_sample_pages="2",
            mysql_partitions="2",
            mysql_partition_by="HASH(other_id)",
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE testtable ("
            "id INTEGER NOT NULL AUTO_INCREMENT, "
            "other_id INTEGER NOT NULL, "
            "PRIMARY KEY (id, other_id)"
            ")STATS_SAMPLE_PAGES=2 PARTITION BY HASH(other_id) PARTITIONS 2",
        )

    def test_inner_join(self):
        t1 = table("t1", column("x"))
        t2 = table("t2", column("y"))

        self.assert_compile(t1.join(t2, t1.c.x == t2.c.y),
                            "t1 INNER JOIN t2 ON t1.x = t2.y")

    def test_outer_join(self):
        t1 = table("t1", column("x"))
        t2 = table("t2", column("y"))

        self.assert_compile(
            t1.outerjoin(t2, t1.c.x == t2.c.y),
            "t1 LEFT OUTER JOIN t2 ON t1.x = t2.y",
        )

    def test_full_outer_join(self):
        t1 = table("t1", column("x"))
        t2 = table("t2", column("y"))

        self.assert_compile(
            t1.outerjoin(t2, t1.c.x == t2.c.y, full=True),
            "t1 FULL OUTER JOIN t2 ON t1.x = t2.y",
        )
示例#36
0
class TypesTest(fixtures.TestBase, AssertsExecutionResults,
                AssertsCompiledSQL):
    "Test MySQL column types"

    __dialect__ = mysql.dialect()
    __only_on__ = "mysql"
    __backend__ = True

    def test_numeric(self):
        "Exercise type specification and options for numeric types."

        columns = [
            # column type, args, kwargs, expected ddl
            # e.g. Column(Integer(10, unsigned=True)) ==
            # 'INTEGER(10) UNSIGNED'
            (mysql.MSNumeric, [], {}, "NUMERIC"),
            (mysql.MSNumeric, [None], {}, "NUMERIC"),
            (mysql.MSNumeric, [12], {}, "NUMERIC(12)"),
            (
                mysql.MSNumeric,
                [12, 4],
                {
                    "unsigned": True
                },
                "NUMERIC(12, 4) UNSIGNED",
            ),
            (
                mysql.MSNumeric,
                [12, 4],
                {
                    "zerofill": True
                },
                "NUMERIC(12, 4) ZEROFILL",
            ),
            (
                mysql.MSNumeric,
                [12, 4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "NUMERIC(12, 4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSDecimal, [], {}, "DECIMAL"),
            (mysql.MSDecimal, [None], {}, "DECIMAL"),
            (mysql.MSDecimal, [12], {}, "DECIMAL(12)"),
            (mysql.MSDecimal, [12, None], {}, "DECIMAL(12)"),
            (
                mysql.MSDecimal,
                [12, 4],
                {
                    "unsigned": True
                },
                "DECIMAL(12, 4) UNSIGNED",
            ),
            (
                mysql.MSDecimal,
                [12, 4],
                {
                    "zerofill": True
                },
                "DECIMAL(12, 4) ZEROFILL",
            ),
            (
                mysql.MSDecimal,
                [12, 4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "DECIMAL(12, 4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSDouble, [None, None], {}, "DOUBLE"),
            (
                mysql.MSDouble,
                [12, 4],
                {
                    "unsigned": True
                },
                "DOUBLE(12, 4) UNSIGNED",
            ),
            (
                mysql.MSDouble,
                [12, 4],
                {
                    "zerofill": True
                },
                "DOUBLE(12, 4) ZEROFILL",
            ),
            (
                mysql.MSDouble,
                [12, 4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "DOUBLE(12, 4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSReal, [None, None], {}, "REAL"),
            (
                mysql.MSReal,
                [12, 4],
                {
                    "unsigned": True
                },
                "REAL(12, 4) UNSIGNED",
            ),
            (
                mysql.MSReal,
                [12, 4],
                {
                    "zerofill": True
                },
                "REAL(12, 4) ZEROFILL",
            ),
            (
                mysql.MSReal,
                [12, 4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "REAL(12, 4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSFloat, [], {}, "FLOAT"),
            (mysql.MSFloat, [None], {}, "FLOAT"),
            (mysql.MSFloat, [12], {}, "FLOAT(12)"),
            (mysql.MSFloat, [12, 4], {}, "FLOAT(12, 4)"),
            (
                mysql.MSFloat,
                [12, 4],
                {
                    "unsigned": True
                },
                "FLOAT(12, 4) UNSIGNED",
            ),
            (
                mysql.MSFloat,
                [12, 4],
                {
                    "zerofill": True
                },
                "FLOAT(12, 4) ZEROFILL",
            ),
            (
                mysql.MSFloat,
                [12, 4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "FLOAT(12, 4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSInteger, [], {}, "INTEGER"),
            (mysql.MSInteger, [4], {}, "INTEGER(4)"),
            (mysql.MSInteger, [4], {
                "unsigned": True
            }, "INTEGER(4) UNSIGNED"),
            (mysql.MSInteger, [4], {
                "zerofill": True
            }, "INTEGER(4) ZEROFILL"),
            (
                mysql.MSInteger,
                [4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "INTEGER(4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSBigInteger, [], {}, "BIGINT"),
            (mysql.MSBigInteger, [4], {}, "BIGINT(4)"),
            (
                mysql.MSBigInteger,
                [4],
                {
                    "unsigned": True
                },
                "BIGINT(4) UNSIGNED",
            ),
            (
                mysql.MSBigInteger,
                [4],
                {
                    "zerofill": True
                },
                "BIGINT(4) ZEROFILL",
            ),
            (
                mysql.MSBigInteger,
                [4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "BIGINT(4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSMediumInteger, [], {}, "MEDIUMINT"),
            (mysql.MSMediumInteger, [4], {}, "MEDIUMINT(4)"),
            (
                mysql.MSMediumInteger,
                [4],
                {
                    "unsigned": True
                },
                "MEDIUMINT(4) UNSIGNED",
            ),
            (
                mysql.MSMediumInteger,
                [4],
                {
                    "zerofill": True
                },
                "MEDIUMINT(4) ZEROFILL",
            ),
            (
                mysql.MSMediumInteger,
                [4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "MEDIUMINT(4) UNSIGNED ZEROFILL",
            ),
            (mysql.MSTinyInteger, [], {}, "TINYINT"),
            (mysql.MSTinyInteger, [1], {}, "TINYINT(1)"),
            (
                mysql.MSTinyInteger,
                [1],
                {
                    "unsigned": True
                },
                "TINYINT(1) UNSIGNED",
            ),
            (
                mysql.MSTinyInteger,
                [1],
                {
                    "zerofill": True
                },
                "TINYINT(1) ZEROFILL",
            ),
            (
                mysql.MSTinyInteger,
                [1],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "TINYINT(1) UNSIGNED ZEROFILL",
            ),
            (mysql.MSSmallInteger, [], {}, "SMALLINT"),
            (mysql.MSSmallInteger, [4], {}, "SMALLINT(4)"),
            (
                mysql.MSSmallInteger,
                [4],
                {
                    "unsigned": True
                },
                "SMALLINT(4) UNSIGNED",
            ),
            (
                mysql.MSSmallInteger,
                [4],
                {
                    "zerofill": True
                },
                "SMALLINT(4) ZEROFILL",
            ),
            (
                mysql.MSSmallInteger,
                [4],
                {
                    "zerofill": True,
                    "unsigned": True
                },
                "SMALLINT(4) UNSIGNED ZEROFILL",
            ),
        ]

        for type_, args, kw, res in columns:
            type_inst = type_(*args, **kw)
            self.assert_compile(type_inst, res)
            # test that repr() copies out all arguments
            self.assert_compile(eval("mysql.%r" % type_inst), res)

    # fixed in mysql-connector as of 2.0.1,
    # see http://bugs.mysql.com/bug.php?id=73266
    @testing.provide_metadata
    def test_precision_float_roundtrip(self):
        t = Table(
            "t",
            self.metadata,
            Column(
                "scale_value",
                mysql.DOUBLE(precision=15, scale=12, asdecimal=True),
            ),
            Column(
                "unscale_value",
                mysql.DOUBLE(decimal_return_scale=12, asdecimal=True),
            ),
        )
        t.create(testing.db)
        testing.db.execute(
            t.insert(),
            scale_value=45.768392065789,
            unscale_value=45.768392065789,
        )
        result = testing.db.scalar(select([t.c.scale_value]))
        eq_(result, decimal.Decimal("45.768392065789"))

        result = testing.db.scalar(select([t.c.unscale_value]))
        eq_(result, decimal.Decimal("45.768392065789"))

    @testing.exclude("mysql", "<", (4, 1, 1), "no charset support")
    def test_charset(self):
        """Exercise CHARACTER SET and COLLATE-ish options on string types."""

        columns = [
            (mysql.MSChar, [1], {}, "CHAR(1)"),
            (mysql.NCHAR, [1], {}, "NATIONAL CHAR(1)"),
            (mysql.MSChar, [1], {
                "binary": True
            }, "CHAR(1) BINARY"),
            (mysql.MSChar, [1], {
                "ascii": True
            }, "CHAR(1) ASCII"),
            (mysql.MSChar, [1], {
                "unicode": True
            }, "CHAR(1) UNICODE"),
            (
                mysql.MSChar,
                [1],
                {
                    "ascii": True,
                    "binary": True
                },
                "CHAR(1) ASCII BINARY",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "unicode": True,
                    "binary": True
                },
                "CHAR(1) UNICODE BINARY",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8"
                },
                "CHAR(1) CHARACTER SET utf8",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8",
                    "binary": True
                },
                "CHAR(1) CHARACTER SET utf8 BINARY",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8",
                    "unicode": True
                },
                "CHAR(1) CHARACTER SET utf8",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8",
                    "ascii": True
                },
                "CHAR(1) CHARACTER SET utf8",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "collation": "utf8_bin"
                },
                "CHAR(1) COLLATE utf8_bin",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8",
                    "collation": "utf8_bin"
                },
                "CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8",
                    "binary": True
                },
                "CHAR(1) CHARACTER SET utf8 BINARY",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "charset": "utf8",
                    "collation": "utf8_bin",
                    "binary": True
                },
                "CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
            ),
            (mysql.MSChar, [1], {
                "national": True
            }, "NATIONAL CHAR(1)"),
            (
                mysql.MSChar,
                [1],
                {
                    "national": True,
                    "charset": "utf8"
                },
                "NATIONAL CHAR(1)",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "national": True,
                    "charset": "utf8",
                    "binary": True
                },
                "NATIONAL CHAR(1) BINARY",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "national": True,
                    "binary": True,
                    "unicode": True
                },
                "NATIONAL CHAR(1) BINARY",
            ),
            (
                mysql.MSChar,
                [1],
                {
                    "national": True,
                    "collation": "utf8_bin"
                },
                "NATIONAL CHAR(1) COLLATE utf8_bin",
            ),
            (
                mysql.MSString,
                [1],
                {
                    "charset": "utf8",
                    "collation": "utf8_bin"
                },
                "VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
            ),
            (
                mysql.MSString,
                [1],
                {
                    "national": True,
                    "collation": "utf8_bin"
                },
                "NATIONAL VARCHAR(1) COLLATE utf8_bin",
            ),
            (
                mysql.MSTinyText,
                [],
                {
                    "charset": "utf8",
                    "collation": "utf8_bin"
                },
                "TINYTEXT CHARACTER SET utf8 COLLATE utf8_bin",
            ),
            (
                mysql.MSMediumText,
                [],
                {
                    "charset": "utf8",
                    "binary": True
                },
                "MEDIUMTEXT CHARACTER SET utf8 BINARY",
            ),
            (mysql.MSLongText, [], {
                "ascii": True
            }, "LONGTEXT ASCII"),
            (
                mysql.ENUM,
                ["foo", "bar"],
                {
                    "unicode": True
                },
                """ENUM('foo','bar') UNICODE""",
            ),
            (String, [20], {
                "collation": "utf8"
            }, "VARCHAR(20) COLLATE utf8"),
        ]

        for type_, args, kw, res in columns:
            type_inst = type_(*args, **kw)
            self.assert_compile(type_inst, res)
            # test that repr() copies out all arguments
            self.assert_compile(
                eval("mysql.%r" %
                     type_inst) if type_ is not String else eval("%r" %
                                                                 type_inst),
                res,
            )

    @testing.only_if("mysql")
    @testing.fails_on("mysql+mysqlconnector", "different unicode behavior")
    @testing.exclude("mysql", "<", (5, 0, 5), "a 5.0+ feature")
    @testing.provide_metadata
    def test_charset_collate_table(self):
        t = Table(
            "foo",
            self.metadata,
            Column("id", Integer),
            Column("data", UnicodeText),
            mysql_default_charset="utf8",
            mysql_collate="utf8_bin",
        )
        t.create()
        m2 = MetaData(testing.db)
        t2 = Table("foo", m2, autoload=True)
        eq_(t2.kwargs["mysql_collate"], "utf8_bin")
        eq_(t2.kwargs["mysql_default charset"], "utf8")

        # test [ticket:2906]
        # in order to test the condition here, need to use
        # MySQLdb 1.2.3 and also need to pass either use_unicode=1
        # or charset=utf8 to the URL.
        t.insert().execute(id=1, data=u("some text"))
        assert isinstance(testing.db.scalar(select([t.c.data])),
                          util.text_type)

    def test_bit_50(self):
        """Exercise BIT types on 5.0+ (not valid for all engine types)"""

        for type_, expected in [
            (mysql.MSBit(), "BIT"),
            (mysql.MSBit(1), "BIT(1)"),
            (mysql.MSBit(63), "BIT(63)"),
        ]:
            self.assert_compile(type_, expected)

    @testing.exclude("mysql", "<", (5, 0, 5), "a 5.0+ feature")
    @testing.provide_metadata
    def test_bit_50_roundtrip(self):
        bit_table = Table(
            "mysql_bits",
            self.metadata,
            Column("b1", mysql.MSBit),
            Column("b2", mysql.MSBit()),
            Column("b3", mysql.MSBit(), nullable=False),
            Column("b4", mysql.MSBit(1)),
            Column("b5", mysql.MSBit(8)),
            Column("b6", mysql.MSBit(32)),
            Column("b7", mysql.MSBit(63)),
            Column("b8", mysql.MSBit(64)),
        )
        self.metadata.create_all()

        meta2 = MetaData(testing.db)
        reflected = Table("mysql_bits", meta2, autoload=True)

        for table in bit_table, reflected:

            def roundtrip(store, expected=None):
                expected = expected or store
                table.insert(store).execute()
                row = table.select().execute().first()
                try:
                    self.assert_(list(row) == expected)
                except Exception:
                    print("Storing %s" % store)
                    print("Expected %s" % expected)
                    print("Found %s" % list(row))
                    raise
                table.delete().execute().close()

            roundtrip([0] * 8)
            roundtrip([None, None, 0, None, None, None, None, None])
            roundtrip([1] * 8)
            roundtrip([sql.text("b'1'")] * 8, [1] * 8)

            i = 255
            roundtrip([0, 0, 0, 0, i, i, i, i])
            i = 2**32 - 1
            roundtrip([0, 0, 0, 0, 0, i, i, i])
            i = 2**63 - 1
            roundtrip([0, 0, 0, 0, 0, 0, i, i])
            i = 2**64 - 1
            roundtrip([0, 0, 0, 0, 0, 0, 0, i])

    def test_boolean(self):
        for type_, expected in [
            (BOOLEAN(), "BOOL"),
            (Boolean(), "BOOL"),
            (mysql.TINYINT(1), "TINYINT(1)"),
            (mysql.TINYINT(1, unsigned=True), "TINYINT(1) UNSIGNED"),
        ]:
            self.assert_compile(type_, expected)

    @testing.provide_metadata
    def test_boolean_roundtrip(self):
        bool_table = Table(
            "mysql_bool",
            self.metadata,
            Column("b1", BOOLEAN),
            Column("b2", Boolean),
            Column("b3", mysql.MSTinyInteger(1)),
            Column("b4", mysql.MSTinyInteger(1, unsigned=True)),
            Column("b5", mysql.MSTinyInteger),
        )
        self.metadata.create_all()
        table = bool_table

        def roundtrip(store, expected=None):
            expected = expected or store
            table.insert(store).execute()
            row = table.select().execute().first()
            self.assert_(list(row) == expected)
            for i, val in enumerate(expected):
                if isinstance(val, bool):
                    self.assert_(val is row[i])
            table.delete().execute()

        roundtrip([None, None, None, None, None])
        roundtrip([True, True, 1, 1, 1])
        roundtrip([False, False, 0, 0, 0])
        roundtrip([True, True, True, True, True], [True, True, 1, 1, 1])
        roundtrip([False, False, 0, 0, 0], [False, False, 0, 0, 0])

        meta2 = MetaData(testing.db)
        table = Table("mysql_bool", meta2, autoload=True)
        eq_(colspec(table.c.b3), "b3 TINYINT(1)")
        eq_(colspec(table.c.b4), "b4 TINYINT(1) UNSIGNED")
        meta2 = MetaData(testing.db)
        table = Table(
            "mysql_bool",
            meta2,
            Column("b1", BOOLEAN),
            Column("b2", Boolean),
            Column("b3", BOOLEAN),
            Column("b4", BOOLEAN),
            autoload=True,
        )
        eq_(colspec(table.c.b3), "b3 BOOL")
        eq_(colspec(table.c.b4), "b4 BOOL")
        roundtrip([None, None, None, None, None])
        roundtrip([True, True, 1, 1, 1], [True, True, True, True, 1])
        roundtrip([False, False, 0, 0, 0], [False, False, False, False, 0])
        roundtrip([True, True, True, True, True], [True, True, True, True, 1])
        roundtrip([False, False, 0, 0, 0], [False, False, False, False, 0])

    def test_timestamp_fsp(self):
        self.assert_compile(mysql.TIMESTAMP(fsp=5), "TIMESTAMP(5)")

    def test_timestamp_defaults(self):
        """Exercise funky TIMESTAMP default syntax when used in columns."""

        columns = [
            ([TIMESTAMP], {}, "TIMESTAMP NULL"),
            ([mysql.MSTimeStamp], {}, "TIMESTAMP NULL"),
            (
                [
                    mysql.MSTimeStamp(),
                    DefaultClause(sql.text("CURRENT_TIMESTAMP")),
                ],
                {},
                "TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP",
            ),
            (
                [
                    mysql.MSTimeStamp,
                    DefaultClause(sql.text("CURRENT_TIMESTAMP")),
                ],
                {
                    "nullable": False
                },
                "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ),
            (
                [
                    mysql.MSTimeStamp,
                    DefaultClause(sql.text("'1999-09-09 09:09:09'")),
                ],
                {
                    "nullable": False
                },
                "TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09'",
            ),
            (
                [
                    mysql.MSTimeStamp(),
                    DefaultClause(sql.text("'1999-09-09 09:09:09'")),
                ],
                {},
                "TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09'",
            ),
            (
                [
                    mysql.MSTimeStamp(),
                    DefaultClause(
                        sql.text("'1999-09-09 09:09:09' "
                                 "ON UPDATE CURRENT_TIMESTAMP")),
                ],
                {},
                "TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09' "
                "ON UPDATE CURRENT_TIMESTAMP",
            ),
            (
                [
                    mysql.MSTimeStamp,
                    DefaultClause(
                        sql.text("'1999-09-09 09:09:09' "
                                 "ON UPDATE CURRENT_TIMESTAMP")),
                ],
                {
                    "nullable": False
                },
                "TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09' "
                "ON UPDATE CURRENT_TIMESTAMP",
            ),
            (
                [
                    mysql.MSTimeStamp(),
                    DefaultClause(
                        sql.text("CURRENT_TIMESTAMP "
                                 "ON UPDATE CURRENT_TIMESTAMP")),
                ],
                {},
                "TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP "
                "ON UPDATE CURRENT_TIMESTAMP",
            ),
            (
                [
                    mysql.MSTimeStamp,
                    DefaultClause(
                        sql.text("CURRENT_TIMESTAMP "
                                 "ON UPDATE CURRENT_TIMESTAMP")),
                ],
                {
                    "nullable": False
                },
                "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
                "ON UPDATE CURRENT_TIMESTAMP",
            ),
        ]
        for spec, kw, expected in columns:
            c = Column("t", *spec, **kw)
            Table("t", MetaData(), c)
            self.assert_compile(schema.CreateColumn(c), "t %s" % expected)

    @testing.requires.mysql_zero_date
    @testing.provide_metadata
    def test_timestamp_nullable(self):
        ts_table = Table(
            "mysql_timestamp",
            self.metadata,
            Column("t1", TIMESTAMP),
            Column("t2", TIMESTAMP, nullable=False),
            mysql_engine="InnoDB",
        )
        self.metadata.create_all()

        # TIMESTAMP without NULL inserts current time when passed
        # NULL.  when not passed, generates 0000-00-00 quite
        # annoyingly.
        # the flag http://dev.mysql.com/doc/refman/5.6/en/\
        # server-system-variables.html#sysvar_explicit_defaults_for_timestamp
        # changes this for 5.6 if set.

        # normalize dates that are over the second boundary
        def normalize(dt):
            if dt is None:
                return None
            elif (dt - now).seconds < 5:
                return now
            else:
                return dt

        with testing.db.begin() as conn:
            now = conn.scalar("select now()")

            conn.execute(ts_table.insert(), {"t1": now, "t2": None})
            conn.execute(ts_table.insert(), {"t1": None, "t2": None})
            conn.execute(ts_table.insert(), {"t2": None})

            eq_(
                [
                    tuple([normalize(dt) for dt in row])
                    for row in conn.execute(ts_table.select())
                ],
                [(now, now), (None, now), (None, now)],
            )

    def test_datetime_generic(self):
        self.assert_compile(mysql.DATETIME(), "DATETIME")

    def test_datetime_fsp(self):
        self.assert_compile(mysql.DATETIME(fsp=4), "DATETIME(4)")

    def test_time_generic(self):
        """"Exercise TIME."""

        self.assert_compile(mysql.TIME(), "TIME")

    def test_time_fsp(self):
        self.assert_compile(mysql.TIME(fsp=5), "TIME(5)")

    def test_time_result_processor(self):
        eq_(
            mysql.TIME().result_processor(None, None)(datetime.timedelta(
                seconds=35, minutes=517, microseconds=450)),
            datetime.time(8, 37, 35, 450),
        )

    @testing.fails_on("mysql+oursql", "TODO: probable OurSQL bug")
    @testing.provide_metadata
    def test_time_roundtrip(self):
        t = Table("mysql_time", self.metadata, Column("t1", mysql.TIME()))
        t.create()
        t.insert().values(t1=datetime.time(8, 37, 35)).execute()
        eq_(select([t.c.t1]).scalar(), datetime.time(8, 37, 35))

    @testing.provide_metadata
    def test_year(self):
        """Exercise YEAR."""

        year_table = Table(
            "mysql_year",
            self.metadata,
            Column("y1", mysql.MSYear),
            Column("y2", mysql.MSYear),
            Column("y3", mysql.MSYear),
            Column("y5", mysql.MSYear(4)),
        )

        for col in year_table.c:
            self.assert_(repr(col))
        year_table.create()
        reflected = Table("mysql_year", MetaData(testing.db), autoload=True)

        for table in year_table, reflected:
            table.insert(["1950", "50", None, 1950]).execute()
            row = table.select().execute().first()
            eq_(list(row), [1950, 2050, None, 1950])
            table.delete().execute()
            self.assert_(colspec(table.c.y1).startswith("y1 YEAR"))
            eq_(colspec(table.c.y5), "y5 YEAR(4)")
示例#37
0
class TypeRoundTripTest(fixtures.TestBase, AssertsExecutionResults):

    __dialect__ = mysql.dialect()
    __only_on__ = "mysql", "mariadb"
    __backend__ = True

    # fixed in mysql-connector as of 2.0.1,
    # see http://bugs.mysql.com/bug.php?id=73266
    def test_precision_float_roundtrip(self, metadata, connection):
        t = Table(
            "t",
            metadata,
            Column(
                "scale_value",
                mysql.DOUBLE(precision=15, scale=12, asdecimal=True),
            ),
            Column(
                "unscale_value",
                mysql.DOUBLE(decimal_return_scale=12, asdecimal=True),
            ),
        )
        t.create(connection)
        connection.execute(
            t.insert(),
            dict(
                scale_value=45.768392065789,
                unscale_value=45.768392065789,
            ),
        )
        result = connection.scalar(select(t.c.scale_value))
        eq_(result, decimal.Decimal("45.768392065789"))

        result = connection.scalar(select(t.c.unscale_value))
        eq_(result, decimal.Decimal("45.768392065789"))

    @testing.only_if("mysql")
    def test_charset_collate_table(self, metadata, connection):
        t = Table(
            "foo",
            metadata,
            Column("id", Integer),
            Column("data", UnicodeText),
            mysql_default_charset="utf8",
            mysql_collate="utf8_bin",
        )
        t.create(connection)
        t2 = Table("foo", MetaData(), autoload_with=connection)
        eq_(t2.kwargs["mysql_collate"], "utf8_bin")
        eq_(t2.kwargs["mysql_default charset"], "utf8")

        # test [ticket:2906]
        # in order to test the condition here, need to use
        # MySQLdb 1.2.3 and also need to pass either use_unicode=1
        # or charset=utf8 to the URL.
        connection.execute(t.insert(), dict(id=1, data=u("some text")))
        assert isinstance(connection.scalar(select(t.c.data)), util.text_type)

    @testing.metadata_fixture(ddl="class")
    def bit_table(self, metadata):
        bit_table = Table(
            "mysql_bits",
            metadata,
            Column("b1", mysql.MSBit),
            Column("b2", mysql.MSBit()),
            Column("b3", mysql.MSBit(), nullable=False),
            Column("b4", mysql.MSBit(1)),
            Column("b5", mysql.MSBit(8)),
            Column("b6", mysql.MSBit(32)),
            Column("b7", mysql.MSBit(63)),
            Column("b8", mysql.MSBit(64)),
        )
        return bit_table

    i, j, k, l = 255, 2**32 - 1, 2**63 - 1, 2**64 - 1

    @testing.combinations(
        (([0] * 8), None),
        ([None, None, 0, None, None, None, None, None], None),
        (([1] * 8), None),
        ([sql.text("b'1'")] * 8, [1] * 8),
        ([0, 0, 0, 0, i, i, i, i], None),
        ([0, 0, 0, 0, 0, j, j, j], None),
        ([0, 0, 0, 0, 0, 0, k, k], None),
        ([0, 0, 0, 0, 0, 0, 0, l], None),
        argnames="store, expected",
    )
    def test_bit_50_roundtrip(self, connection, bit_table, store, expected):

        reflected = Table("mysql_bits", MetaData(), autoload_with=connection)

        expected = expected or store
        connection.execute(reflected.insert().values(store))
        row = connection.execute(reflected.select()).first()
        eq_(list(row), expected)

    @testing.combinations(
        (([0] * 8), None),
        ([None, None, 0, None, None, None, None, None], None),
        (([1] * 8), None),
        ([sql.text("b'1'")] * 8, [1] * 8),
        ([0, 0, 0, 0, i, i, i, i], None),
        ([0, 0, 0, 0, 0, j, j, j], None),
        ([0, 0, 0, 0, 0, 0, k, k], None),
        ([0, 0, 0, 0, 0, 0, 0, l], None),
        argnames="store, expected",
    )
    def test_bit_50_roundtrip_reflected(self, connection, bit_table, store,
                                        expected):
        bit_table = Table("mysql_bits", MetaData(), autoload_with=connection)

        expected = expected or store
        connection.execute(bit_table.insert().values(store))
        row = connection.execute(bit_table.select()).first()
        eq_(list(row), expected)

    @testing.metadata_fixture(ddl="class")
    def boolean_table(self, metadata):
        bool_table = Table(
            "mysql_bool",
            metadata,
            Column("b1", BOOLEAN),
            Column("b2", Boolean),
            Column("b3", mysql.MSTinyInteger(1)),
            Column("b4", mysql.MSTinyInteger(1, unsigned=True)),
            Column("b5", mysql.MSTinyInteger),
        )
        return bool_table

    @testing.combinations(
        ([None, None, None, None, None], None),
        ([True, True, 1, 1, 1], None),
        ([False, False, 0, 0, 0], None),
        ([True, True, True, True, True], [True, True, 1, 1, 1]),
        ([False, False, 0, 0, 0], [False, False, 0, 0, 0]),
        argnames="store, expected",
    )
    def test_boolean_roundtrip(self, connection, boolean_table, store,
                               expected):
        table = boolean_table

        expected = expected or store
        connection.execute(table.insert().values(store))
        row = connection.execute(table.select()).first()
        eq_(list(row), expected)
        for i, val in enumerate(expected):
            if isinstance(val, bool):
                self.assert_(val is row[i])

    @testing.combinations(
        ([None, None, None, None, None], None),
        ([True, True, 1, 1, 1], [True, True, True, True, 1]),
        ([False, False, 0, 0, 0], [False, False, False, False, 0]),
        ([True, True, True, True, True], [True, True, True, True, 1]),
        ([False, False, 0, 0, 0], [False, False, False, False, 0]),
        argnames="store, expected",
    )
    def test_boolean_roundtrip_reflected(self, connection, boolean_table,
                                         store, expected):
        table = Table("mysql_bool", MetaData(), autoload_with=connection)
        eq_(colspec(table.c.b3), "b3 TINYINT(1)")
        eq_regex(colspec(table.c.b4), r"b4 TINYINT(?:\(1\))? UNSIGNED")

        table = Table(
            "mysql_bool",
            MetaData(),
            Column("b1", BOOLEAN),
            Column("b2", Boolean),
            Column("b3", BOOLEAN),
            Column("b4", BOOLEAN),
            autoload_with=connection,
        )
        eq_(colspec(table.c.b3), "b3 BOOL")
        eq_(colspec(table.c.b4), "b4 BOOL")

        expected = expected or store
        connection.execute(table.insert().values(store))
        row = connection.execute(table.select()).first()
        eq_(list(row), expected)
        for i, val in enumerate(expected):
            if isinstance(val, bool):
                self.assert_(val is row[i])

    class MyTime(TypeDecorator):
        impl = TIMESTAMP

    @testing.combinations(
        (TIMESTAMP, ),
        (MyTime(), ),
        (String().with_variant(TIMESTAMP, "mysql"), ),
        argnames="type_",
    )
    @testing.requires.mysql_zero_date
    def test_timestamp_nullable(self, metadata, connection, type_):
        ts_table = Table(
            "mysql_timestamp",
            metadata,
            Column("t1", type_),
            Column("t2", type_, nullable=False),
            mysql_engine="InnoDB",
        )
        metadata.create_all(connection)

        # TIMESTAMP without NULL inserts current time when passed
        # NULL.  when not passed, generates 0000-00-00 quite
        # annoyingly.
        # the flag http://dev.mysql.com/doc/refman/5.6/en/\
        # server-system-variables.html#sysvar_explicit_defaults_for_timestamp
        # changes this for 5.6 if set.

        # normalize dates for the amount of time the operation took
        def normalize(dt):
            if dt is None:
                return None
            elif now <= dt <= new_now:
                return now
            else:
                return dt

        now = connection.exec_driver_sql("select now()").scalar()
        connection.execute(ts_table.insert(), {"t1": now, "t2": None})
        connection.execute(ts_table.insert(), {"t1": None, "t2": None})
        connection.execute(ts_table.insert(), {"t2": None})

        new_now = connection.exec_driver_sql("select now()").scalar()

        eq_(
            [
                tuple([normalize(dt) for dt in row])
                for row in connection.execute(ts_table.select())
            ],
            [(now, now), (None, now), (None, now)],
        )

    def test_time_roundtrip(self, metadata, connection):
        t = Table("mysql_time", metadata, Column("t1", mysql.TIME()))

        t.create(connection)

        connection.execute(t.insert().values(t1=datetime.time(8, 37, 35)))
        eq_(
            connection.execute(select(t.c.t1)).scalar(),
            datetime.time(8, 37, 35),
        )

    def test_year(self, metadata, connection):
        """Exercise YEAR."""

        year_table = Table(
            "mysql_year",
            metadata,
            Column("y1", mysql.MSYear),
            Column("y2", mysql.MSYear),
            Column("y3", mysql.MSYear),
            Column("y5", mysql.MSYear(4)),
        )

        for col in year_table.c:
            self.assert_(repr(col))
        year_table.create(connection)
        reflected = Table("mysql_year", MetaData(), autoload_with=connection)

        for table in year_table, reflected:
            connection.execute(table.insert().values(
                ["1950", "50", None, 1950]))
            row = connection.execute(table.select()).first()
            eq_(list(row), [1950, 2050, None, 1950])
            self.assert_(colspec(table.c.y1).startswith("y1 YEAR"))
            eq_regex(colspec(table.c.y5), r"y5 YEAR(?:\(4\))?")
示例#38
0
class EnumSetTest(fixtures.TestBase, AssertsExecutionResults,
                  AssertsCompiledSQL):

    __only_on__ = "mysql", "mariadb"
    __dialect__ = mysql.dialect()
    __backend__ = True

    class SomeEnum(object):
        # Implements PEP 435 in the minimal fashion needed by SQLAlchemy
        __members__ = OrderedDict()

        def __init__(self, name, value):
            self.name = name
            self.value = value
            self.__members__[name] = self
            setattr(self.__class__, name, self)

    one = SomeEnum("one", 1)
    two = SomeEnum("two", 2)
    three = SomeEnum("three", 3)
    a_member = SomeEnum("AMember", "a")
    b_member = SomeEnum("BMember", "b")

    @staticmethod
    def get_enum_string_values(some_enum):
        return [str(v.value) for v in some_enum.__members__.values()]

    def test_enum(self, metadata, connection):
        """Exercise the ENUM type."""

        e1 = mysql.ENUM("a", "b")
        e2 = mysql.ENUM("a", "b")
        e3 = mysql.ENUM("a", "b", strict=True)
        e4 = mysql.ENUM("a", "b", strict=True)

        enum_table = Table(
            "mysql_enum",
            metadata,
            Column("e1", e1),
            Column("e2", e2, nullable=False),
            Column(
                "e2generic",
                Enum("a", "b", validate_strings=True),
                nullable=False,
            ),
            Column("e3", e3),
            Column("e4", e4, nullable=False),
            Column("e5", mysql.ENUM("a", "b")),
            Column("e5generic", Enum("a", "b")),
            Column("e6", mysql.ENUM("'a'", "b")),
            Column(
                "e7",
                mysql.ENUM(
                    EnumSetTest.SomeEnum,
                    values_callable=EnumSetTest.get_enum_string_values,
                ),
            ),
            Column("e8", mysql.ENUM(EnumSetTest.SomeEnum)),
        )

        eq_(colspec(enum_table.c.e1), "e1 ENUM('a','b')")
        eq_(colspec(enum_table.c.e2), "e2 ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e2generic),
            "e2generic ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e3), "e3 ENUM('a','b')")
        eq_(colspec(enum_table.c.e4), "e4 ENUM('a','b') NOT NULL")
        eq_(colspec(enum_table.c.e5), "e5 ENUM('a','b')")
        eq_(colspec(enum_table.c.e5generic), "e5generic ENUM('a','b')")
        eq_(colspec(enum_table.c.e6), "e6 ENUM('''a''','b')")
        eq_(colspec(enum_table.c.e7), "e7 ENUM('1','2','3','a','b')")
        eq_(
            colspec(enum_table.c.e8),
            "e8 ENUM('one','two','three','AMember','BMember')",
        )
        enum_table.create(connection)

        assert_raises(
            exc.DBAPIError,
            connection.execute,
            enum_table.insert(),
            dict(
                e1=None,
                e2=None,
                e3=None,
                e4=None,
            ),
        )

        assert enum_table.c.e2generic.type.validate_strings

        assert_raises(
            exc.StatementError,
            connection.execute,
            enum_table.insert(),
            dict(
                e1="c",
                e2="c",
                e2generic="c",
                e3="c",
                e4="c",
                e5="c",
                e5generic="c",
                e6="c",
                e7="c",
                e8="c",
            ),
        )

        connection.execute(enum_table.insert())
        connection.execute(
            enum_table.insert(),
            dict(
                e1="a",
                e2="a",
                e2generic="a",
                e3="a",
                e4="a",
                e5="a",
                e5generic="a",
                e6="'a'",
                e7="a",
                e8="AMember",
            ),
        )
        connection.execute(
            enum_table.insert(),
            dict(
                e1="b",
                e2="b",
                e2generic="b",
                e3="b",
                e4="b",
                e5="b",
                e5generic="b",
                e6="b",
                e7="b",
                e8="BMember",
            ),
        )

        res = connection.execute(enum_table.select()).fetchall()

        expected = [
            (None, "a", "a", None, "a", None, None, None, None, None),
            (
                "a",
                "a",
                "a",
                "a",
                "a",
                "a",
                "a",
                "'a'",
                EnumSetTest.SomeEnum.AMember,
                EnumSetTest.SomeEnum.AMember,
            ),
            (
                "b",
                "b",
                "b",
                "b",
                "b",
                "b",
                "b",
                "b",
                EnumSetTest.SomeEnum.BMember,
                EnumSetTest.SomeEnum.BMember,
            ),
        ]

        eq_(res, expected)

    def _set_fixture_one(self, metadata):
        e1 = mysql.SET("a", "b")
        e2 = mysql.SET("a", "b")
        e4 = mysql.SET("'a'", "b")
        e5 = mysql.SET("a", "b")

        set_table = Table(
            "mysql_set",
            metadata,
            Column("e1", e1),
            Column("e2", e2, nullable=False),
            Column("e3", mysql.SET("a", "b")),
            Column("e4", e4),
            Column("e5", e5),
        )
        return set_table

    def test_set_colspec(self, metadata):
        set_table = self._set_fixture_one(metadata)
        eq_(colspec(set_table.c.e1), "e1 SET('a','b')")
        eq_(colspec(set_table.c.e2), "e2 SET('a','b') NOT NULL")
        eq_(colspec(set_table.c.e3), "e3 SET('a','b')")
        eq_(colspec(set_table.c.e4), "e4 SET('''a''','b')")
        eq_(colspec(set_table.c.e5), "e5 SET('a','b')")

    def test_no_null(self, metadata, connection):
        set_table = self._set_fixture_one(metadata)
        set_table.create(connection)
        assert_raises(
            exc.DBAPIError,
            connection.execute,
            set_table.insert(),
            dict(e1=None, e2=None, e3=None, e4=None),
        )

    @testing.requires.mysql_non_strict
    def test_empty_set_no_empty_string(self, metadata, connection):
        t = Table(
            "t",
            metadata,
            Column("id", Integer),
            Column("data", mysql.SET("a", "b")),
        )
        t.create(connection)
        connection.execute(
            t.insert(),
            [
                {
                    "id": 1,
                    "data": set()
                },
                {
                    "id": 2,
                    "data": set([""])
                },
                {
                    "id": 3,
                    "data": set(["a", ""])
                },
                {
                    "id": 4,
                    "data": set(["b"])
                },
            ],
        )
        eq_(
            connection.execute(t.select().order_by(t.c.id)).fetchall(),
            [(1, set()), (2, set()), (3, set(["a"])), (4, set(["b"]))],
        )

    def test_bitwise_required_for_empty(self):
        assert_raises_message(
            exc.ArgumentError,
            "Can't use the blank value '' in a SET without setting "
            "retrieve_as_bitwise=True",
            mysql.SET,
            "a",
            "b",
            "",
        )

    def test_empty_set_empty_string(self, metadata, connection):
        t = Table(
            "t",
            metadata,
            Column("id", Integer),
            Column("data", mysql.SET("a", "b", "", retrieve_as_bitwise=True)),
        )
        t.create(connection)
        connection.execute(
            t.insert(),
            [
                {
                    "id": 1,
                    "data": set()
                },
                {
                    "id": 2,
                    "data": set([""])
                },
                {
                    "id": 3,
                    "data": set(["a", ""])
                },
                {
                    "id": 4,
                    "data": set(["b"])
                },
            ],
        )
        eq_(
            connection.execute(t.select().order_by(t.c.id)).fetchall(),
            [
                (1, set()),
                (2, set([""])),
                (3, set(["a", ""])),
                (4, set(["b"])),
            ],
        )

    def test_string_roundtrip(self, metadata, connection):
        set_table = self._set_fixture_one(metadata)
        set_table.create(connection)
        connection.execute(
            set_table.insert(),
            dict(e1="a", e2="a", e3="a", e4="'a'", e5="a,b"),
        )
        connection.execute(
            set_table.insert(),
            dict(e1="b", e2="b", e3="b", e4="b", e5="a,b"),
        )

        expected = [
            (
                set(["a"]),
                set(["a"]),
                set(["a"]),
                set(["'a'"]),
                set(["a", "b"]),
            ),
            (
                set(["b"]),
                set(["b"]),
                set(["b"]),
                set(["b"]),
                set(["a", "b"]),
            ),
        ]
        res = connection.execute(set_table.select()).fetchall()

        eq_(res, expected)

    def test_unicode_roundtrip(self, metadata, connection):
        set_table = Table(
            "t",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", mysql.SET(u("réveillé"), u("drôle"), u("S’il"))),
        )

        set_table.create(connection)
        connection.execute(
            set_table.insert(),
            {"data": set([u("réveillé"), u("drôle")])})

        row = connection.execute(set_table.select()).first()

        eq_(row, (1, set([u("réveillé"), u("drôle")])))

    def test_int_roundtrip(self, metadata, connection):
        set_table = self._set_fixture_one(metadata)
        set_table.create(connection)
        connection.execute(set_table.insert(),
                           dict(e1=1, e2=2, e3=3, e4=3, e5=0))
        res = connection.execute(set_table.select()).first()
        eq_(
            res,
            (
                set(["a"]),
                set(["b"]),
                set(["a", "b"]),
                set(["'a'", "b"]),
                set([]),
            ),
        )

    def test_set_roundtrip_plus_reflection(self, metadata, connection):
        set_table = Table(
            "mysql_set",
            metadata,
            Column("s1", mysql.SET("dq", "sq")),
            Column("s2", mysql.SET("a")),
            Column("s3", mysql.SET("5", "7", "9")),
        )

        eq_(colspec(set_table.c.s1), "s1 SET('dq','sq')")
        eq_(colspec(set_table.c.s2), "s2 SET('a')")
        eq_(colspec(set_table.c.s3), "s3 SET('5','7','9')")
        set_table.create(connection)
        reflected = Table("mysql_set", MetaData(), autoload_with=connection)
        for table in set_table, reflected:

            def roundtrip(store, expected=None):
                expected = expected or store
                connection.execute(table.insert().values(store))
                row = connection.execute(table.select()).first()
                eq_(row, tuple(expected))
                connection.execute(table.delete())

            roundtrip([None, None, None], [None] * 3)
            roundtrip(["", "", ""], [set([])] * 3)
            roundtrip([set(["dq"]), set(["a"]), set(["5"])])
            roundtrip(["dq", "a", "5"], [set(["dq"]), set(["a"]), set(["5"])])
            roundtrip([1, 1, 1], [set(["dq"]), set(["a"]), set(["5"])])
            roundtrip([set(["dq", "sq"]), None, set(["9", "5", "7"])])
        connection.execute(
            set_table.insert(),
            [
                {
                    "s3": set(["5"])
                },
                {
                    "s3": set(["5", "7"])
                },
                {
                    "s3": set(["5", "7", "9"])
                },
                {
                    "s3": set(["7", "9"])
                },
            ],
        )

        rows = connection.execute(
            select(set_table.c.s3).where(
                set_table.c.s3.in_([set(["5"]), ["5", "7"]]))).fetchall()

        eq_(list(rows), [({"5"}, ), ({"7", "5"}, )])

    def test_unicode_enum(self, metadata, connection):
        t1 = Table(
            "table",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("value", Enum(u("réveillé"), u("drôle"), u("S’il"))),
            Column("value2", mysql.ENUM(u("réveillé"), u("drôle"), u("S’il"))),
        )
        metadata.create_all(connection)

        connection.execute(
            t1.insert(),
            [
                dict(value=u("drôle"), value2=u("drôle")),
                dict(value=u("réveillé"), value2=u("réveillé")),
                dict(value=u("S’il"), value2=u("S’il")),
            ],
        )
        eq_(
            connection.execute(t1.select().order_by(t1.c.id)).fetchall(),
            [
                (1, u("drôle"), u("drôle")),
                (2, u("réveillé"), u("réveillé")),
                (3, u("S’il"), u("S’il")),
            ],
        )

        # test reflection of the enum labels

        t2 = Table("table", MetaData(), autoload_with=connection)

        # TODO: what's wrong with the last element ?  is there
        #       latin-1 stuff forcing its way in ?

        eq_(t2.c.value.type.enums[0:2],
            [u("réveillé"), u("drôle")])  # u'S’il') # eh ?

        eq_(t2.c.value2.type.enums[0:2],
            [u("réveillé"), u("drôle")])  # u'S’il') # eh ?

    def test_enum_compile(self):
        e1 = Enum("x", "y", "z", name="somename")
        t1 = Table("sometable", MetaData(), Column("somecolumn", e1))
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE sometable (somecolumn "
            "ENUM('x','y','z'))",
        )
        t1 = Table(
            "sometable",
            MetaData(),
            Column(
                "somecolumn",
                Enum("x", "y", "z", native_enum=False, create_constraint=True),
            ),
        )
        self.assert_compile(
            schema.CreateTable(t1),
            "CREATE TABLE sometable (somecolumn "
            "VARCHAR(1), CHECK (somecolumn IN ('x', "
            "'y', 'z')))",
        )

    def test_enum_parse(self, metadata, connection):

        enum_table = Table(
            "mysql_enum",
            metadata,
            Column("e1", mysql.ENUM("a")),
            Column("e2", mysql.ENUM("")),
            Column("e3", mysql.ENUM("a")),
            Column("e4", mysql.ENUM("")),
            Column("e5", mysql.ENUM("a", "")),
            Column("e6", mysql.ENUM("", "a")),
            Column("e7", mysql.ENUM("", "'a'", "b'b", "'")),
        )

        for col in enum_table.c:
            self.assert_(repr(col))

        enum_table.create(connection)
        reflected = Table("mysql_enum", MetaData(), autoload_with=connection)
        for t in enum_table, reflected:
            eq_(t.c.e1.type.enums, ["a"])
            eq_(t.c.e2.type.enums, [""])
            eq_(t.c.e3.type.enums, ["a"])
            eq_(t.c.e4.type.enums, [""])
            eq_(t.c.e5.type.enums, ["a", ""])
            eq_(t.c.e6.type.enums, ["", "a"])
            eq_(t.c.e7.type.enums, ["", "'a'", "b'b", "'"])

    def test_set_parse(self, metadata, connection):
        set_table = Table(
            "mysql_set",
            metadata,
            Column("e1", mysql.SET("a")),
            Column("e2", mysql.SET("", retrieve_as_bitwise=True)),
            Column("e3", mysql.SET("a")),
            Column("e4", mysql.SET("", retrieve_as_bitwise=True)),
            Column("e5", mysql.SET("a", "", retrieve_as_bitwise=True)),
            Column("e6", mysql.SET("", "a", retrieve_as_bitwise=True)),
            Column(
                "e7",
                mysql.SET(
                    "",
                    "'a'",
                    "b'b",
                    "'",
                    retrieve_as_bitwise=True,
                ),
            ),
        )

        for col in set_table.c:
            self.assert_(repr(col))

        set_table.create(connection)

        # don't want any warnings on reflection
        reflected = Table("mysql_set", MetaData(), autoload_with=connection)
        for t in set_table, reflected:
            eq_(t.c.e1.type.values, ("a", ))
            eq_(t.c.e2.type.values, ("", ))
            eq_(t.c.e3.type.values, ("a", ))
            eq_(t.c.e4.type.values, ("", ))
            eq_(t.c.e5.type.values, ("a", ""))
            eq_(t.c.e6.type.values, ("", "a"))
            eq_(t.c.e7.type.values, ("", "'a'", "b'b", "'"))

    @testing.requires.mysql_non_strict
    def test_broken_enum_returns_blanks(self, metadata, connection):
        t = Table(
            "enum_missing",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("e1", sqltypes.Enum("one", "two", "three")),
            Column("e2", mysql.ENUM("one", "two", "three")),
        )
        t.create(connection)

        connection.execute(
            t.insert(),
            [
                {
                    "e1": "nonexistent",
                    "e2": "nonexistent"
                },
                {
                    "e1": "",
                    "e2": ""
                },
                {
                    "e1": "two",
                    "e2": "two"
                },
                {
                    "e1": None,
                    "e2": None
                },
            ],
        )

        eq_(
            connection.execute(select(t.c.e1,
                                      t.c.e2).order_by(t.c.id)).fetchall(),
            [("", ""), ("", ""), ("two", "two"), (None, None)],
        )
示例#39
0
class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mysql.dialect()

    table1 = table("mytable", column("myid"), column("name"),
                   column("description"))
    table2 = table("table2", column("mytable_id"))
    join = table2.join(table1, table2.c.mytable_id == table1.c.myid)
    for_update_of_dialect = mysql.dialect()
    for_update_of_dialect.server_version_info = (8, 0, 0)
    for_update_of_dialect.supports_for_update_of = True

    def test_for_update_basic(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s FOR UPDATE",
        )

    def test_for_update_read(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE",
        )

    def test_for_update_skip_locked(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                skip_locked=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE SKIP LOCKED",
        )

    def test_for_update_read_and_skip_locked(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True, skip_locked=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "LOCK IN SHARE MODE SKIP LOCKED",
        )

    def test_for_update_nowait(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                nowait=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE NOWAIT",
        )

    def test_for_update_read_and_nowait(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True, nowait=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "LOCK IN SHARE MODE NOWAIT",
        )

    def test_for_update_of_nowait(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                of=self.table1, nowait=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE OF mytable NOWAIT",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_basic(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                of=self.table1),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE OF mytable",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_skip_locked(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                of=self.table1, skip_locked=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE OF mytable SKIP LOCKED",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_join_one(self):
        self.assert_compile(
            self.join.select(self.table2.c.mytable_id == 7).with_for_update(
                of=[self.join]),
            "SELECT table2.mytable_id, "
            "mytable.myid, mytable.name, mytable.description "
            "FROM table2 "
            "INNER JOIN mytable ON table2.mytable_id = mytable.myid "
            "WHERE table2.mytable_id = %s "
            "FOR UPDATE OF mytable, table2",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_column_list_aliased(self):
        ta = self.table1.alias()
        self.assert_compile(
            ta.select(ta.c.myid == 7).with_for_update(
                of=[ta.c.myid, ta.c.name]),
            "SELECT mytable_1.myid, mytable_1.name, mytable_1.description "
            "FROM mytable AS mytable_1 "
            "WHERE mytable_1.myid = %s FOR UPDATE OF mytable_1",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_join_aliased(self):
        ta = self.table1.alias()
        alias_join = self.table2.join(ta,
                                      self.table2.c.mytable_id == ta.c.myid)
        self.assert_compile(
            alias_join.select(self.table2.c.mytable_id == 7).with_for_update(
                of=[alias_join]),
            "SELECT table2.mytable_id, "
            "mytable_1.myid, mytable_1.name, mytable_1.description "
            "FROM table2 "
            "INNER JOIN mytable AS mytable_1 "
            "ON table2.mytable_id = mytable_1.myid "
            "WHERE table2.mytable_id = %s "
            "FOR UPDATE OF mytable_1, table2",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_read_nowait(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True, of=self.table1, nowait=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "LOCK IN SHARE MODE OF mytable NOWAIT",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_read_skip_locked(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True, of=self.table1, skip_locked=True),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "LOCK IN SHARE MODE OF mytable SKIP LOCKED",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_read_nowait_column_list(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True,
                of=[self.table1.c.myid, self.table1.c.name],
                nowait=True,
            ),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "LOCK IN SHARE MODE OF mytable NOWAIT",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_of_read(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                read=True, of=self.table1),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "LOCK IN SHARE MODE OF mytable",
            dialect=self.for_update_of_dialect,
        )

    def test_for_update_textual_of(self):
        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                of=text("mytable")),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE OF mytable",
            dialect=self.for_update_of_dialect,
        )

        self.assert_compile(
            self.table1.select(self.table1.c.myid == 7).with_for_update(
                of=literal_column("mytable")),
            "SELECT mytable.myid, mytable.name, mytable.description "
            "FROM mytable WHERE mytable.myid = %s "
            "FOR UPDATE OF mytable",
            dialect=self.for_update_of_dialect,
        )
示例#40
0
 def setup(self):
     dialect = mysql.dialect()
     self.parser = _reflection.MySQLTableDefinitionParser(
         dialect, dialect.identifier_preparer)
示例#41
0
 def setUp(self):
     self.dialect = mysql.dialect()
     self.parser = mysql.MySQLTableDefinitionParser(self.dialect)