def testmysqltypes(self):
meta1 = BoundMetaData(testbase.db)
table = Table(
'mysql_types',
meta1,
Column('id', Integer, primary_key=True),
Column('num1', mysql.MSInteger(unsigned=True)),
Column('text1', mysql.MSLongText),
Column('text2', mysql.MSLongText()),
Column('num2', mysql.MSBigInteger),
Column('num3', mysql.MSBigInteger()),
Column('num4', mysql.MSDouble),
Column('num5', mysql.MSDouble()),
Column('enum1', mysql.MSEnum('"black"', '"white"')),
)
try:
table.create(checkfirst=True)
meta2 = BoundMetaData(testbase.db)
t2 = Table('mysql_types', meta2, autoload=True)
assert isinstance(t2.c.num1.type, mysql.MSInteger)
assert t2.c.num1.type.unsigned
assert isinstance(t2.c.text1.type, mysql.MSLongText)
assert isinstance(t2.c.text2.type, mysql.MSLongText)
assert isinstance(t2.c.num2.type, mysql.MSBigInteger)
assert isinstance(t2.c.num3.type, mysql.MSBigInteger)
assert isinstance(t2.c.num4.type, mysql.MSDouble)
assert isinstance(t2.c.num5.type, mysql.MSDouble)
assert isinstance(t2.c.enum1.type, mysql.MSEnum)
t2.drop()
t2.create()
finally:
table.drop(checkfirst=True)
def test_enum_parse(self):
"""More exercises for the ENUM type."""
# MySQL 3.23 can't handle an ENUM of ''....
enum_table = Table(
'mysql_enum', MetaData(testing.db),
Column('e1', mysql.MSEnum("'a'")), Column('e2',
mysql.MSEnum("''")),
Column('e3', mysql.MSEnum('a')), Column('e4', mysql.MSEnum('')),
Column('e5', mysql.MSEnum("'a'", "''")),
Column('e6', mysql.MSEnum("''", "'a'")),
Column('e7', mysql.MSEnum("''", "'''a'''", "'b''b'", "''''")))
for col in enum_table.c:
self.assert_(repr(col))
try:
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", "'"])
finally:
enum_table.drop()
def test_basic(self):
meta1 = MetaData(testing.db)
table = Table(
'mysql_types',
meta1,
Column('id', Integer, primary_key=True),
Column('num1', mysql.MSInteger(unsigned=True)),
Column('text1', mysql.MSLongText),
Column('text2', mysql.MSLongText()),
Column('num2', mysql.MSBigInteger),
Column('num3', mysql.MSBigInteger()),
Column('num4', mysql.MSDouble),
Column('num5', mysql.MSDouble()),
Column('num6', mysql.MSMediumInteger),
Column('enum1', mysql.MSEnum("'black'", "'white'")),
Column('enum2', mysql.MSEnum("dog", "cat")),
)
try:
table.drop(checkfirst=True)
table.create()
meta2 = MetaData(testing.db)
t2 = Table('mysql_types', meta2, autoload=True)
assert isinstance(t2.c.num1.type, mysql.MSInteger)
assert t2.c.num1.type.unsigned
assert isinstance(t2.c.text1.type, mysql.MSLongText)
assert isinstance(t2.c.text2.type, mysql.MSLongText)
assert isinstance(t2.c.num2.type, mysql.MSBigInteger)
assert isinstance(t2.c.num3.type, mysql.MSBigInteger)
assert isinstance(t2.c.num4.type, mysql.MSDouble)
assert isinstance(t2.c.num5.type, mysql.MSDouble)
assert isinstance(t2.c.num6.type, mysql.MSMediumInteger)
assert isinstance(t2.c.enum1.type, mysql.MSEnum)
assert isinstance(t2.c.enum2.type, mysql.MSEnum)
t2.drop()
t2.create()
finally:
meta1.drop_all()
def test_type_reflection(self):
# (ask_for, roundtripped_as_if_different)
specs = [
(
String(1),
mysql.MSString(1),
),
(
String(3),
mysql.MSString(3),
),
(
Text(),
mysql.MSText(),
),
(
Unicode(1),
mysql.MSString(1),
),
(
Unicode(3),
mysql.MSString(3),
),
(
UnicodeText(),
mysql.MSText(),
),
(mysql.MSChar(1), ),
(mysql.MSChar(3), ),
(
NCHAR(2),
mysql.MSChar(2),
),
(
mysql.MSNChar(2),
mysql.MSChar(2),
), # N is CREATE only
(
mysql.MSNVarChar(22),
mysql.MSString(22),
),
(
SmallInteger(),
mysql.MSSmallInteger(),
),
(
SmallInteger(),
mysql.MSSmallInteger(4),
),
(mysql.MSSmallInteger(), ),
(
mysql.MSSmallInteger(4),
mysql.MSSmallInteger(4),
),
(
mysql.MSMediumInteger(),
mysql.MSMediumInteger(),
),
(
mysql.MSMediumInteger(8),
mysql.MSMediumInteger(8),
),
(
Binary(3),
mysql.MSBlob(3),
),
(Binary(), mysql.MSBlob()),
(
mysql.MSBinary(3),
mysql.MSBinary(3),
),
(mysql.MSVarBinary(3), ),
(mysql.MSVarBinary(), mysql.MSBlob()),
(mysql.MSTinyBlob(), ),
(mysql.MSBlob(), ),
(mysql.MSBlob(1234), mysql.MSBlob()),
(mysql.MSMediumBlob(), ),
(mysql.MSLongBlob(), ),
(mysql.MSEnum("''", "'fleem'"), ),
]
columns = [Column('c%i' % (i + 1), t[0]) for i, t in enumerate(specs)]
db = testing.db
m = MetaData(db)
t_table = Table('mysql_types', m, *columns)
try:
m.create_all()
m2 = MetaData(db)
rt = Table('mysql_types', m2, autoload=True)
try:
db.execute('CREATE OR REPLACE VIEW mysql_types_v '
'AS SELECT * from mysql_types')
rv = Table('mysql_types_v', m2, autoload=True)
expected = [len(c) > 1 and c[1] or c[0] for c in specs]
# Early 5.0 releases seem to report more "general" for columns
# in a view, e.g. char -> varchar, tinyblob -> mediumblob
#
# Not sure exactly which point version has the fix.
if db.dialect.server_version_info(db.connect()) < (5, 0, 11):
tables = rt,
else:
tables = rt, rv
for table in tables:
for i, reflected in enumerate(table.c):
assert isinstance(reflected.type, type(expected[i]))
finally:
db.execute('DROP VIEW mysql_types_v')
finally:
m.drop_all()
def test_enum(self):
"""Exercise the ENUM type."""
db = testing.db
enum_table = Table(
'mysql_enum',
MetaData(testing.db),
Column('e1', mysql.MSEnum("'a'", "'b'")),
Column('e2', mysql.MSEnum("'a'", "'b'"), nullable=False),
Column('e3', mysql.MSEnum("'a'", "'b'", strict=True)),
Column('e4',
mysql.MSEnum("'a'", "'b'", strict=True),
nullable=False),
Column('e5', mysql.MSEnum("a", "b")),
Column('e6', mysql.MSEnum("'a'", "b")),
)
self.assert_eq(colspec(enum_table.c.e1), "e1 ENUM('a','b')")
self.assert_eq(colspec(enum_table.c.e2), "e2 ENUM('a','b') NOT NULL")
self.assert_eq(colspec(enum_table.c.e3), "e3 ENUM('a','b')")
self.assert_eq(colspec(enum_table.c.e4), "e4 ENUM('a','b') NOT NULL")
self.assert_eq(colspec(enum_table.c.e5), "e5 ENUM('a','b')")
self.assert_eq(colspec(enum_table.c.e6), "e6 ENUM('''a''','b')")
enum_table.drop(checkfirst=True)
enum_table.create()
try:
enum_table.insert().execute(e1=None, e2=None, e3=None, e4=None)
self.assert_(False)
except exc.SQLError:
self.assert_(True)
try:
enum_table.insert().execute(e1='c',
e2='c',
e3='c',
e4='c',
e5='c',
e6='c')
self.assert_(False)
except exc.InvalidRequestError:
self.assert_(True)
enum_table.insert().execute()
enum_table.insert().execute(e1='a',
e2='a',
e3='a',
e4='a',
e5='a',
e6="'a'")
enum_table.insert().execute(e1='b',
e2='b',
e3='b',
e4='b',
e5='b',
e6='b')
res = enum_table.select().execute().fetchall()
expected = [(None, 'a', None, 'a', None, None),
('a', 'a', 'a', 'a', 'a', "'a'"),
('b', 'b', 'b', 'b', 'b', 'b')]
# This is known to fail with MySQLDB 1.2.2 beta versions
# which return these as sets.Set(['a']), sets.Set(['b'])
# (even on Pythons with __builtin__.set)
if testing.db.dialect.dbapi.version_info < (1, 2, 2, 'beta', 3) and \
testing.db.dialect.dbapi.version_info >= (1, 2, 2):
# these mysqldb seem to always uses 'sets', even on later pythons
import sets
def convert(value):
if value is None:
return value
if value == '':
return sets.Set([])
else:
return sets.Set([value])
e = []
for row in expected:
e.append(tuple([convert(c) for c in row]))
expected = e
self.assert_eq(res, expected)
enum_table.drop()