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)
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
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])
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),
)
