From 2d406d99769a65670116d92b472f8a494a3e7812 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 11 Jul 2009 18:58:10 +0000 Subject: [PATCH] - oracle fixes - upon further consideration it seems appropriate that reflection would coerce all "default" values into text(), since this represents a DDL expression straight from the DB - adjustments to mysql to accommodate that - for some reason zoomark dropped 300 calls in create_tables. investigation pending --- lib/sqlalchemy/dialects/mysql/base.py | 9 +- lib/sqlalchemy/dialects/oracle/base.py | 4 +- lib/sqlalchemy/dialects/oracle/cx_oracle.py | 17 ++-- lib/sqlalchemy/engine/reflection.py | 4 +- test/aaa_profiling/test_zoomark.py | 2 +- test/dialect/test_mysql.py | 104 ++++++++++++-------- test/dialect/test_oracle.py | 12 +-- test/dialect/test_sqlite.py | 4 +- 8 files changed, 90 insertions(+), 66 deletions(-) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 0ebaeca0dc..133d7ef57a 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2276,14 +2276,11 @@ class MySQLTableDefinitionParser(object): # DEFAULT default = spec.get('default', None) - if default is not None and default != 'NULL': - # Defaults should be in the native charset for the moment - default = default.encode(charset) - if type_ != 'timestamp': - default = default[1:-1] - elif default == 'NULL': + + if default == 'NULL': # eliminates the need to deal with this later. default = None + col_d = dict(name=name, type=type_instance, default=default) col_d.update(col_kw) state.columns.append(col_d) diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 3140a63d5b..a512a2b1b1 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -120,7 +120,7 @@ from sqlalchemy import types as sqltypes from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, DATE, DATETIME, \ BLOB, CLOB, TIMESTAMP, FLOAT -RESERVED_WORDS = set('''SHARE RAW DROP BETWEEN FROM DESC OPTION PRIOR LONG THEN DEFAULT ALTER IS INTO MINUS INTEGER NUMBER GRANT IDENTIFIED ALL TO ORDER ON FLOAT DATE HAVING CLUSTER NOWAIT RESOURCE ANY TABLE INDEX FOR UPDATE WHERE CHECK SMALLINT WITH DELETE BY ASC REVOKE LIKE SIZE RENAME NOCOMPRESS NULL GROUP VALUES AS IN VIEW EXCLUSIVE COMPRESS SYNONYM SELECT INSERT EXISTS NOT TRIGGER ELSE CREATE INTERSECT PCTFREE DISTINCT CONNECT SET MODE OF UNIQUE VARCHAR2 VARCHAR LOCK OR CHAR DECIMAL UNION PUBLIC AND START UID COMMENT'''.split()) +RESERVED_WORDS = set('''SHARE RAW DROP BETWEEN FROM DESC OPTION PRIOR LONG THEN DEFAULT ALTER IS INTO MINUS INTEGER NUMBER GRANT IDENTIFIED ALL TO ORDER ON FLOAT DATE HAVING CLUSTER NOWAIT RESOURCE ANY TABLE INDEX FOR UPDATE WHERE CHECK SMALLINT WITH DELETE BY ASC REVOKE LIKE SIZE RENAME NOCOMPRESS NULL GROUP VALUES AS IN VIEW EXCLUSIVE COMPRESS SYNONYM SELECT INSERT EXISTS NOT TRIGGER ELSE CREATE INTERSECT PCTFREE DISTINCT USER CONNECT SET MODE OF UNIQUE VARCHAR2 VARCHAR LOCK OR CHAR DECIMAL UNION PUBLIC AND START UID COMMENT'''.split()) class RAW(sqltypes.Binary): pass @@ -178,6 +178,7 @@ ischema_names = { 'BLOB' : BLOB, 'BFILE' : BFILE, 'CLOB' : CLOB, + 'NCLOB' : NCLOB, 'TIMESTAMP' : TIMESTAMP, 'RAW' : RAW, 'FLOAT' : FLOAT, @@ -650,6 +651,7 @@ class OracleDialect(default.DefaultDialect): util.warn("Did not recognize type '%s' of column '%s'" % (coltype, colname)) coltype = sqltypes.NULLTYPE + cdict = { 'name': colname, 'type': coltype, diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 6f46224dcb..fe74dce7af 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -63,7 +63,8 @@ working successfully but this should be regarded as an experimental feature. """ -from sqlalchemy.dialects.oracle.base import OracleDialect, RAW, _OracleBoolean, RESERVED_WORDS +from sqlalchemy.dialects.oracle.base import OracleDialect, RESERVED_WORDS +from sqlalchemy.dialects.oracle import base as oracle from sqlalchemy.engine.default import DefaultExecutionContext from sqlalchemy.engine import base from sqlalchemy import types as sqltypes, util @@ -145,7 +146,7 @@ class _OracleBinary(_LOBMixin, sqltypes.Binary): return None -class _OracleRaw(_LOBMixin, RAW): +class _OracleRaw(_LOBMixin, oracle.RAW): pass @@ -153,11 +154,11 @@ colspecs = { sqltypes.DateTime : _OracleDateTime, sqltypes.Date : _OracleDate, sqltypes.Binary : _OracleBinary, - sqltypes.Boolean : _OracleBoolean, + sqltypes.Boolean : oracle._OracleBoolean, sqltypes.Text : _OracleText, sqltypes.UnicodeText : _OracleUnicodeText, sqltypes.TIMESTAMP : _OracleTimestamp, - RAW: _OracleRaw, + oracle.RAW: _OracleRaw, } class Oracle_cx_oracleExecutionContext(DefaultExecutionContext): @@ -250,10 +251,10 @@ class Oracle_cx_oracle(OracleDialect): # etc. leads to a little too much magic, reflection doesn't know if it should # expect encoded strings or unicodes, etc. self.dbapi_type_map = { - self.dbapi.CLOB: OracleText(), - self.dbapi.NCLOB:OracleUnicodeText(), - self.dbapi.BLOB: OracleBinary(), - self.dbapi.BINARY: OracleRaw(), + self.dbapi.CLOB: oracle.CLOB(), + self.dbapi.NCLOB:oracle.NCLOB(), + self.dbapi.BLOB: oracle.BLOB(), + self.dbapi.BINARY: oracle.RAW(), } self.ORACLE_BINARY_TYPES = [getattr(self.dbapi, k) for k in ["BFILE", "CLOB", "NCLOB", "BLOB"] if hasattr(self.dbapi, k)] diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py index e4dd1ed489..196c129a38 100644 --- a/lib/sqlalchemy/engine/reflection.py +++ b/lib/sqlalchemy/engine/reflection.py @@ -287,7 +287,9 @@ class Inspector(object): colargs = [] if col_d.get('default') is not None: - colargs.append(sa_schema.DefaultClause(col_d['default'])) + # the "default" value is assumed to be a literal SQL expression, + # so is wrapped in text() so that no quoting occurs on re-issuance. + colargs.append(sa_schema.DefaultClause(sql.text(col_d['default']))) if 'sequence' in col_d: # TODO: whos using this ? diff --git a/test/aaa_profiling/test_zoomark.py b/test/aaa_profiling/test_zoomark.py index c633841678..e9f5146690 100644 --- a/test/aaa_profiling/test_zoomark.py +++ b/test/aaa_profiling/test_zoomark.py @@ -319,7 +319,7 @@ class ZooMarkTest(TestBase): engine = create_engine('postgres:///', creator=player) metadata = MetaData(engine) - @profiling.function_call_count(3230, {'2.4': 1796}) + @profiling.function_call_count(2991, {'2.4': 1796}) def test_profile_1_create_tables(self): self.test_baseline_1_create_tables() diff --git a/test/dialect/test_mysql.py b/test/dialect/test_mysql.py index fd70b79635..071a736807 100644 --- a/test/dialect/test_mysql.py +++ b/test/dialect/test_mysql.py @@ -186,7 +186,7 @@ class TypesTest(TestBase, AssertsExecutionResults): for col in numeric_table.c: index = int(col.name[1:]) - self.assert_eq(gen.get_column_specification(col), + eq_(gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3])) self.assert_(repr(col)) @@ -270,7 +270,7 @@ class TypesTest(TestBase, AssertsExecutionResults): for col in charset_table.c: index = int(col.name[1:]) - self.assert_eq(gen.get_column_specification(col), + eq_(gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3])) self.assert_(repr(col)) @@ -296,14 +296,14 @@ class TypesTest(TestBase, AssertsExecutionResults): Column('b7', mysql.MSBit(63)), Column('b8', mysql.MSBit(64))) - self.assert_eq(colspec(bit_table.c.b1), 'b1 BIT') - self.assert_eq(colspec(bit_table.c.b2), 'b2 BIT') - self.assert_eq(colspec(bit_table.c.b3), 'b3 BIT NOT NULL') - self.assert_eq(colspec(bit_table.c.b4), 'b4 BIT(1)') - self.assert_eq(colspec(bit_table.c.b5), 'b5 BIT(8)') - self.assert_eq(colspec(bit_table.c.b6), 'b6 BIT(32)') - self.assert_eq(colspec(bit_table.c.b7), 'b7 BIT(63)') - self.assert_eq(colspec(bit_table.c.b8), 'b8 BIT(64)') + eq_(colspec(bit_table.c.b1), 'b1 BIT') + eq_(colspec(bit_table.c.b2), 'b2 BIT') + eq_(colspec(bit_table.c.b3), 'b3 BIT NOT NULL') + eq_(colspec(bit_table.c.b4), 'b4 BIT(1)') + eq_(colspec(bit_table.c.b5), 'b5 BIT(8)') + eq_(colspec(bit_table.c.b6), 'b6 BIT(32)') + eq_(colspec(bit_table.c.b7), 'b7 BIT(63)') + eq_(colspec(bit_table.c.b8), 'b8 BIT(64)') for col in bit_table.c: self.assert_(repr(col)) @@ -354,10 +354,10 @@ class TypesTest(TestBase, AssertsExecutionResults): Column('b3', mysql.MSTinyInteger(1)), Column('b4', mysql.MSTinyInteger)) - self.assert_eq(colspec(bool_table.c.b1), 'b1 BOOL') - self.assert_eq(colspec(bool_table.c.b2), 'b2 BOOL') - self.assert_eq(colspec(bool_table.c.b3), 'b3 TINYINT(1)') - self.assert_eq(colspec(bool_table.c.b4), 'b4 TINYINT') + eq_(colspec(bool_table.c.b1), 'b1 BOOL') + eq_(colspec(bool_table.c.b2), 'b2 BOOL') + eq_(colspec(bool_table.c.b3), 'b3 TINYINT(1)') + eq_(colspec(bool_table.c.b4), 'b4 TINYINT') for col in bool_table.c: self.assert_(repr(col)) @@ -391,7 +391,7 @@ class TypesTest(TestBase, AssertsExecutionResults): meta2 = MetaData(testing.db) # replace with reflected table = Table('mysql_bool', meta2, autoload=True) - self.assert_eq(colspec(table.c.b3), 'b3 BOOL') + eq_(colspec(table.c.b3), 'b3 BOOL') roundtrip([None, None, None, None]) roundtrip([True, True, 1, 1], [True, True, True, 1]) @@ -434,7 +434,7 @@ class TypesTest(TestBase, AssertsExecutionResults): t = Table('mysql_ts%s' % idx, meta, Column('id', Integer, primary_key=True), Column('t', *spec)) - self.assert_eq(colspec(t.c.t), "t %s" % expected) + eq_(colspec(t.c.t), "t %s" % expected) self.assert_(repr(t.c.t)) t.create() r = Table('mysql_ts%s' % idx, MetaData(testing.db), @@ -465,11 +465,11 @@ class TypesTest(TestBase, AssertsExecutionResults): for table in year_table, reflected: table.insert(['1950', '50', None, 50, 1950]).execute() row = list(table.select().execute())[0] - self.assert_eq(list(row), [1950, 2050, None, 50, 1950]) + eq_(list(row), [1950, 2050, None, 50, 1950]) table.delete().execute() self.assert_(colspec(table.c.y1).startswith('y1 YEAR')) - self.assert_eq(colspec(table.c.y4), 'y4 YEAR(2)') - self.assert_eq(colspec(table.c.y5), 'y5 YEAR(4)') + eq_(colspec(table.c.y4), 'y4 YEAR(2)') + eq_(colspec(table.c.y5), 'y5 YEAR(4)') finally: meta.drop_all() @@ -483,9 +483,9 @@ class TypesTest(TestBase, AssertsExecutionResults): Column('s2', mysql.MSSet("'a'")), Column('s3', mysql.MSSet("'5'", "'7'", "'9'"))) - self.assert_eq(colspec(set_table.c.s1), "s1 SET('dq','sq')") - self.assert_eq(colspec(set_table.c.s2), "s2 SET('a')") - self.assert_eq(colspec(set_table.c.s3), "s3 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')") for col in set_table.c: self.assert_(repr(col)) @@ -546,17 +546,17 @@ class TypesTest(TestBase, AssertsExecutionResults): Column('e6', mysql.MSEnum("'a'", "b")), ) - self.assert_eq(colspec(enum_table.c.e1), + eq_(colspec(enum_table.c.e1), "e1 ENUM('a','b')") - self.assert_eq(colspec(enum_table.c.e2), + eq_(colspec(enum_table.c.e2), "e2 ENUM('a','b') NOT NULL") - self.assert_eq(colspec(enum_table.c.e3), + eq_(colspec(enum_table.c.e3), "e3 ENUM('a','b')") - self.assert_eq(colspec(enum_table.c.e4), + eq_(colspec(enum_table.c.e4), "e4 ENUM('a','b') NOT NULL") - self.assert_eq(colspec(enum_table.c.e5), + eq_(colspec(enum_table.c.e5), "e5 ENUM('a','b')") - self.assert_eq(colspec(enum_table.c.e6), + eq_(colspec(enum_table.c.e6), "e6 ENUM('''a''','b')") enum_table.drop(checkfirst=True) enum_table.create() @@ -606,7 +606,7 @@ class TypesTest(TestBase, AssertsExecutionResults): e.append(tuple([convert(c) for c in row])) expected = e - self.assert_eq(res, expected) + eq_(res, expected) enum_table.drop() @testing.exclude('mysql', '<', (4,), "3.23 can't handle an ENUM of ''") @@ -641,25 +641,52 @@ class TypesTest(TestBase, AssertsExecutionResults): finally: enum_table.drop() + + +class ReflectionTest(TestBase, AssertsExecutionResults): + + __only_on__ = 'mysql' + def test_default_reflection(self): """Test reflection of column defaults.""" def_table = Table('mysql_def', MetaData(testing.db), Column('c1', String(10), DefaultClause('')), Column('c2', String(10), DefaultClause('0')), - Column('c3', String(10), DefaultClause('abc'))) + Column('c3', String(10), DefaultClause('abc')), + Column('c4', TIMESTAMP, DefaultClause('2009-04-05 12:00:00')), + Column('c5', TIMESTAMP, ), + + ) + def_table.create() try: - def_table.create() reflected = Table('mysql_def', MetaData(testing.db), - autoload=True) - for t in def_table, reflected: - assert t.c.c1.server_default.arg == '' - assert t.c.c2.server_default.arg == '0' - assert t.c.c3.server_default.arg == 'abc' + autoload=True) finally: def_table.drop() + + assert def_table.c.c1.server_default.arg == '' + assert def_table.c.c2.server_default.arg == '0' + assert def_table.c.c3.server_default.arg == 'abc' + assert def_table.c.c4.server_default.arg == '2009-04-05 12:00:00' + + assert str(reflected.c.c1.server_default.arg) == "''" + assert str(reflected.c.c2.server_default.arg) == "'0'" + assert str(reflected.c.c3.server_default.arg) == "'abc'" + assert str(reflected.c.c4.server_default.arg) == "'2009-04-05 12:00:00'" + + reflected.create() + try: + reflected2 = Table('mysql_def', MetaData(testing.db), autoload=True) + finally: + reflected.drop() + assert str(reflected2.c.c1.server_default.arg) == "''" + assert str(reflected2.c.c2.server_default.arg) == "'0'" + assert str(reflected2.c.c3.server_default.arg) == "'abc'" + assert str(reflected2.c.c4.server_default.arg) == "'2009-04-05 12:00:00'" + def test_reflection_on_include_columns(self): """Test reflection of include_columns to be sure they respect case.""" @@ -813,11 +840,6 @@ class TypesTest(TestBase, AssertsExecutionResults): finally: meta.drop_all() - def assert_eq(self, got, wanted): - if got != wanted: - print "Expected %s" % wanted - print "Found %s" % got - eq_(got, wanted) class SQLTest(TestBase, AssertsCompiledSQL): diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index 03052508f6..5f22616f60 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -296,17 +296,17 @@ class TypesTest(TestBase, AssertsCompiledSQL): dialect = cx_oracle.dialect() for start, test in [ - (DateTime(), cx_oracle.OracleDateTime), - (TIMESTAMP(), cx_oracle.OracleTimestamp), - (oracle.OracleRaw(), cx_oracle.cxOracleRaw), + (DateTime(), cx_oracle._OracleDateTime), + (TIMESTAMP(), cx_oracle._OracleTimestamp), + (oracle.OracleRaw(), cx_oracle._OracleRaw), (String(), String), (VARCHAR(), VARCHAR), (String(50), String), (Unicode(), Unicode), - (Text(), cx_oracle.OracleText), - (UnicodeText(), cx_oracle.OracleUnicodeText), + (Text(), cx_oracle._OracleText), + (UnicodeText(), cx_oracle._OracleUnicodeText), (NCHAR(), NCHAR), - (oracle.OracleRaw(50), oracle.OracleRaw), + (oracle.RAW(50), cx_oracle._OracleRaw), ]: assert isinstance(start.dialect_impl(dialect), test), "wanted %r got %r" % (test, start.dialect_impl(dialect)) diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index e93428eb6e..448ee947c0 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -148,7 +148,7 @@ class TestDefaults(TestBase, AssertsExecutionResults): rt = Table('t_defaults', m2, autoload=True) expected = [c[1] for c in specs] for i, reflected in enumerate(rt.c): - eq_(reflected.server_default.arg, expected[i]) + eq_(str(reflected.server_default.arg), expected[i]) finally: m.drop_all() @@ -169,7 +169,7 @@ class TestDefaults(TestBase, AssertsExecutionResults): rt = Table('r_defaults', m, autoload=True) for i, reflected in enumerate(rt.c): - eq_(reflected.server_default.arg, expected[i]) + eq_(str(reflected.server_default.arg), expected[i]) finally: db.execute("DROP TABLE r_defaults") -- 2.47.3