diff options
| -rw-r--r-- | CHANGES | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 21 | ||||
| -rw-r--r-- | test/dialect/test_oracle.py | 42 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 2 |
4 files changed, 68 insertions, 7 deletions
@@ -72,6 +72,16 @@ CHANGES "sqlalchemy.dialects.postgresql" logger name. [ticket:877] +- oracle + - The Oracle dialect will issue VARCHAR type definitions + using character counts, i.e. VARCHAR2(50 CHAR), so that + the column is sized in terms of characters and not bytes. + Column reflection of character types will also use + ALL_TAB_COLUMNS.CHAR_LENGTH instead of + ALL_TAB_COLUMNS.DATA_LENGTH. Both of these behaviors take + effect when the server version is 9 or higher - for + version 8, the old behaviors are used. [ticket:1744] + - declarative - Using a mixin won't break if the mixin implements an unpredictable __getattribute__(), i.e. Zope interfaces. diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 2af5bdd7d..332fa805d 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -286,7 +286,10 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler): return "%(name)s(%(precision)s, %(scale)s)" % {'name':name,'precision': precision, 'scale' : scale} def visit_VARCHAR(self, type_): - return "VARCHAR(%(length)s)" % {'length' : type_.length} + if self.dialect.supports_char_length: + return "VARCHAR(%(length)s CHAR)" % {'length' : type_.length} + else: + return "VARCHAR(%(length)s)" % {'length' : type_.length} def visit_NVARCHAR(self, type_): return "NVARCHAR2(%(length)s)" % {'length' : type_.length} @@ -569,7 +572,8 @@ class OracleDialect(default.DefaultDialect): execution_ctx_cls = OracleExecutionContext reflection_options = ('oracle_resolve_synonyms', ) - + + supports_char_length = True def __init__(self, use_ansi=True, @@ -584,6 +588,8 @@ class OracleDialect(default.DefaultDialect): self.implicit_returning = self.server_version_info > (10, ) and \ self.__dict__.get('implicit_returning', True) + self.supports_char_length = self.server_version_info >= (9, ) + if self.server_version_info < (9,): self.colspecs = self.colspecs.copy() self.colspecs.pop(sqltypes.Interval) @@ -749,11 +755,16 @@ class OracleDialect(default.DefaultDialect): resolve_synonyms, dblink, info_cache=info_cache) columns = [] + if self.supports_char_length: + char_length_col = 'char_length' + else: + char_length_col = 'data_length' + c = connection.execute(sql.text( - "SELECT column_name, data_type, data_length, data_precision, data_scale, " + "SELECT column_name, data_type, %(char_length_col)s, data_precision, data_scale, " "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s " "WHERE table_name = :table_name AND owner = :owner " - "ORDER BY column_id" % {'dblink': dblink}), + "ORDER BY column_id" % {'dblink': dblink, 'char_length_col':char_length_col}), table_name=table_name, owner=schema) for row in c: @@ -762,7 +773,7 @@ class OracleDialect(default.DefaultDialect): if coltype == 'NUMBER' : coltype = NUMBER(precision, scale) - elif coltype=='CHAR' or coltype=='VARCHAR2': + elif coltype in ('VARCHAR2', 'NVARCHAR2', 'CHAR'): coltype = self.ischema_names.get(coltype)(length) elif 'WITH TIME ZONE' in coltype: coltype = TIMESTAMP(timezone=True) diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index 566a182ed..bcb34b05c 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -735,7 +735,47 @@ class TypesTest(TestBase, AssertsCompiledSQL): assert isinstance(res, unicode) finally: metadata.drop_all() - + + def test_char_length(self): + self.assert_compile( + VARCHAR(50), + "VARCHAR(50 CHAR)", + ) + + oracle8dialect = oracle.dialect() + oracle8dialect.supports_char_length = False + self.assert_compile( + VARCHAR(50), + "VARCHAR(50)", + dialect=oracle8dialect + ) + + self.assert_compile( + NVARCHAR(50), + "NVARCHAR2(50)", + ) + self.assert_compile( + CHAR(50), + "CHAR(50)", + ) + metadata = MetaData(testing.db) + t1 = Table('t1', metadata, + Column("c1", VARCHAR(50)), + Column("c2", NVARCHAR(250)), + Column("c3", CHAR(200)) + ) + t1.create() + try: + m2 = MetaData(testing.db) + t2 = Table('t1', m2, autoload=True) + eq_(t2.c.c1.type.length, 50) + eq_(t2.c.c2.type.length, 250) + eq_(t2.c.c3.type.length, 200) + finally: + t1.drop() + + + def test_longstring(self): metadata = MetaData(testing.db) testing.db.execute(""" diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index f567d4a5a..fb32c29bb 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1673,7 +1673,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A check_results(postgresql.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%(param_1)s') # then the Oracle engine - check_results(oracle.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20)'], ':param_1') + check_results(oracle.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20 CHAR)'], ':param_1') # then the sqlite engine check_results(sqlite.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '?') |