From 0a713f408d2eded6856c7502b2950792ab460a91 Mon Sep 17 00:00:00 2001 From: Jason Kirtland Date: Thu, 31 May 2007 22:56:24 +0000 Subject: [PATCH] - Emit BOOL rather than BOOLEAN for MySQL booleans in DDL, for old versions of MySQL (#583) - MySQL columns (such as times) with colons in their default values couldn't be roundtripped, fixed (also in Postgres, but not fixed here.) - BINARY/VARBINARY columns aren't really binary at all on ancient versions of MySQL. The type.Binary(123) passthrough now always makes BLOBs. Removed the short-lived MSBaseBinary. - Added mysql.get_version_info, given a connectable returns a tuple of server version info. - Backed off on the reflection tests for older versions of MySQL, for now. --- CHANGES | 4 +-- lib/sqlalchemy/databases/mysql.py | 48 ++++++++++++++++++------------- test/dialect/mysql.py | 13 +++++---- test/engine/reflection.py | 9 ++++-- 4 files changed, 44 insertions(+), 30 deletions(-) diff --git a/CHANGES b/CHANGES index b14bd7db73..b220985ec3 100644 --- a/CHANGES +++ b/CHANGES @@ -48,8 +48,8 @@ - mysql - Nearly all MySQL column types are now supported for declaration and reflection. Added NCHAR, NVARCHAR, VARBINARY, TINYBLOB, LONGBLOB, YEAR - - The sqltypes.Binary passthrough now builds a VARBINARY rather than a - BINARY if given a length + - The sqltypes.Binary passthrough now always builds a BLOB, avoiding + problems with very old database versions - support for column-level CHARACTER SET and COLLATE declarations, as well as ASCII, UNICODE, NATIONAL and BINARY shorthand. - firebird diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py index 2fd8ca4a57..2039bdffe6 100644 --- a/lib/sqlalchemy/databases/mysql.py +++ b/lib/sqlalchemy/databases/mysql.py @@ -678,21 +678,12 @@ class MSNChar(_StringType, sqltypes.CHAR): # We'll actually generate the equiv. "NATIONAL CHAR" instead of "NCHAR". return self._extend("CHAR(%(length)s)" % {'length': self.length}) -class MSBaseBinary(sqltypes.Binary): - """Flexible binary type""" - - def __init__(self, length=None, **kw): - """Flexibly construct a binary column type. Will construct a - VARBINARY or BLOB depending on the length requested, if any. - - length - Maximum data length, in bytes. - """ - super(MSBaseBinary, self).__init__(length, **kw) +class _BinaryType(sqltypes.Binary): + """MySQL binary types""" def get_col_spec(self): - if self.length and self.length <= 255: - return "VARBINARY(%d)" % self.length + if self.length: + return "BLOB(%d)" % self.length else: return "BLOB" @@ -702,7 +693,7 @@ class MSBaseBinary(sqltypes.Binary): else: return buffer(value) -class MSVarBinary(MSBaseBinary): +class MSVarBinary(_BinaryType): """MySQL VARBINARY type, for variable length binary data""" def __init__(self, length=None, **kw): @@ -719,7 +710,7 @@ class MSVarBinary(MSBaseBinary): else: return "BLOB" -class MSBinary(MSBaseBinary): +class MSBinary(_BinaryType): """MySQL BINARY type, for fixed length binary data""" def __init__(self, length=None, **kw): @@ -746,7 +737,7 @@ class MSBinary(MSBaseBinary): else: return buffer(value) -class MSBlob(MSBaseBinary): +class MSBlob(_BinaryType): """MySQL BLOB type, for binary data up to 2^16 bytes""" @@ -865,7 +856,7 @@ class MSEnum(MSString): class MSBoolean(sqltypes.Boolean): def get_col_spec(self): - return "BOOLEAN" + return "BOOL" def convert_result_value(self, value, dialect): if value is None: @@ -893,14 +884,14 @@ colspecs = { sqltypes.Date : MSDate, sqltypes.Time : MSTime, sqltypes.String : MSString, - sqltypes.Binary : MSVarBinary, + sqltypes.Binary : MSBlob, sqltypes.Boolean : MSBoolean, sqltypes.TEXT : MSText, sqltypes.CHAR: MSChar, sqltypes.NCHAR: MSNChar, sqltypes.TIMESTAMP: MSTimeStamp, sqltypes.BLOB: MSBlob, - MSBaseBinary: MSBaseBinary, + _BinaryType: _BinaryType, } @@ -1069,6 +1060,19 @@ class MySQLDialect(ansisql.ANSIDialect): else: raise + def get_version_info(self, connectable): + if hasattr(connectable, 'connect'): + con = connectable.connect().connection + else: + con = connectable + version = [] + for n in con.get_server_info().split('.'): + try: + version.append(int(n)) + except ValueError: + version.append(n) + return tuple(version) + def reflecttable(self, connection, table): # reference: http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html cs = connection.execute("show variables like 'lower_case_table_names'").fetchone()[1] @@ -1125,7 +1129,11 @@ class MySQLDialect(ansisql.ANSIDialect): colargs= [] if default: - colargs.append(schema.PassiveDefault(sql.text(default))) + if col_type == 'timestamp' and default == 'CURRENT_TIMESTAMP': + arg = sql.text(default) + else: + arg = default + colargs.append(schema.PassiveDefault(arg)) table.append_column(schema.Column(name, coltype, *colargs, **dict(primary_key=primary_key, nullable=nullable, diff --git a/test/dialect/mysql.py b/test/dialect/mysql.py index 75c8c01666..4972c8e243 100644 --- a/test/dialect/mysql.py +++ b/test/dialect/mysql.py @@ -294,6 +294,10 @@ class TypesTest(AssertMixin): @testbase.supported('mysql') def test_type_reflection(self): + # FIXME: older versions need their own test + if db.dialect.get_version_info(db) < (5, 0): + return + # (ask_for, roundtripped_as_if_different) specs = [( String(), mysql.MSText(), ), ( String(1), mysql.MSString(1), ), @@ -307,11 +311,9 @@ class TypesTest(AssertMixin): ( Smallinteger(4), mysql.MSSmallInteger(4), ), ( mysql.MSSmallInteger(), ), ( mysql.MSSmallInteger(4), mysql.MSSmallInteger(4), ), - ( Binary(3), mysql.MSVarBinary(3), ), + ( Binary(3), mysql.MSBlob(3), ), ( Binary(), mysql.MSBlob() ), ( mysql.MSBinary(3), mysql.MSBinary(3), ), - ( mysql.MSBaseBinary(), mysql.MSBlob(), ), - ( mysql.MSBaseBinary(3), mysql.MSVarBinary(3), ), ( mysql.MSVarBinary(3),), ( mysql.MSVarBinary(), mysql.MSBlob()), ( mysql.MSTinyBlob(),), @@ -331,13 +333,14 @@ class TypesTest(AssertMixin): m2 = BoundMetaData(db) rt = Table('mysql_types', m2, autoload=True) + #print expected = [len(c) > 1 and c[1] or c[0] for c in specs] for i, reflected in enumerate(rt.c): #print (reflected, specs[i][0], '->', # reflected.type, '==', expected[i]) - assert type(reflected.type) == type(expected[i]) + assert isinstance(reflected.type, type(expected[i])) - #m.drop_all() + m.drop_all() if __name__ == "__main__": testbase.main() diff --git a/test/engine/reflection.py b/test/engine/reflection.py index ea26bb64db..76dd849475 100644 --- a/test/engine/reflection.py +++ b/test/engine/reflection.py @@ -25,9 +25,11 @@ class ReflectionTest(PersistTest): if use_string_defaults: deftype2 = String defval2 = "im a default" + deftype3 = DateTime + defval3 = '1999-09-09 00:00:00' else: - deftype2 = Integer - defval2 = "15" + deftype2, deftype3 = Integer, Integer + defval2, defval3 = "15", "16" meta = BoundMetaData(testbase.db) @@ -46,6 +48,7 @@ class ReflectionTest(PersistTest): Column('test_passivedefault', deftype, PassiveDefault(defval)), Column('test_passivedefault2', Integer, PassiveDefault("5")), Column('test_passivedefault3', deftype2, PassiveDefault(defval2)), + Column('test_passivedefault4', deftype3, PassiveDefault(defval3)), Column('test9', Binary(100)), Column('test_numeric', Numeric(None, None)), mysql_engine='InnoDB' @@ -58,7 +61,7 @@ class ReflectionTest(PersistTest): mysql_engine='InnoDB' ) meta.drop_all() - + users.create() addresses.create() -- 2.47.2