Commits

Mike Bayer committed 2ea9f75

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

Comments (0)

Files changed (4)

      "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.

lib/sqlalchemy/dialects/oracle/base.py

             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}
     execution_ctx_cls = OracleExecutionContext
     
     reflection_options = ('oracle_resolve_synonyms', )
-    
+
+    supports_char_length = True    
     
     def __init__(self, 
                 use_ansi=True, 
         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)
                                           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:
 
             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)

test/dialect/test_oracle.py

             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("""

test/sql/test_compiler.py

         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)'], '?')
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.