- [feature] Added "collation" parameter to all

    String types.  When present, renders as
    COLLATE <collation>.  This to support the
    COLLATE keyword now supported by several
    databases including MySQL, SQLite, and Postgresql.
    [ticket:2276]

  - [change] The Text() type renders the length
    given to it, if a length was specified.

4 files changed, 53 insertions, 30 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 1ba567682..c69ed24e8 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -389,8 +389,10 @@ class _StringType(sqltypes.String):
                  ascii=False, binary=False,
                  national=False, **kw):
         self.charset = charset
+
         # allow collate= or collation=
-        self.collation = kw.pop('collate', collation)
+        kw.setdefault('collation', kw.pop('collate', collation))
+
         self.ascii = ascii
         # We have to munge the 'unicode' param strictly as a dict
         # otherwise 2to3 will turn it into str.
@@ -402,19 +404,6 @@ class _StringType(sqltypes.String):
         self.national = national
         super(_StringType, self).__init__(**kw)
 
-    def __repr__(self):
-        attributes = inspect.getargspec(self.__init__)[0][1:]
-        attributes.extend(inspect.getargspec(_StringType.__init__)[0][1:])
-
-        params = {}
-        for attr in attributes:
-            val = getattr(self, attr)
-            if val is not None and val is not False:
-                params[attr] = val
-
-        return "%s(%s)" % (self.__class__.__name__,
-                           ', '.join(['%s=%r' % (k, params[k]) for k in params]))
-
 
 class NUMERIC(_NumericType, sqltypes.NUMERIC):
     """MySQL NUMERIC type."""
@@ -1489,7 +1478,7 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
 
         opts = dict(
             (
-                k[len(self.dialect.name)+1:].upper(),
+                k[len(self.dialect.name) + 1:].upper(),
                 v
             )
             for k, v in table.kwargs.items()
@@ -1772,7 +1761,8 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
 
     def visit_CHAR(self, type_):
         if type_.length:
-            return self._extend_string(type_, {}, "CHAR(%(length)s)" % {'length' : type_.length})
+            return self._extend_string(type_, {}, "CHAR(%(length)s)" %
+                                        {'length': type_.length})
         else:
             return self._extend_string(type_, {}, "CHAR")
 
@@ -1780,7 +1770,8 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
         # We'll actually generate the equiv. "NATIONAL VARCHAR" instead
         # of "NVARCHAR".
         if type_.length:
-            return self._extend_string(type_, {'national':True}, "VARCHAR(%(length)s)" % {'length': type_.length})
+            return self._extend_string(type_, {'national': True},
+                        "VARCHAR(%(length)s)" % {'length': type_.length})
         else:
             raise exc.CompileError(
                     "NVARCHAR requires a length on dialect %s" %
@@ -1789,9 +1780,10 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
     def visit_NCHAR(self, type_):
         # We'll actually generate the equiv. "NATIONAL CHAR" instead of "NCHAR".
         if type_.length:
-            return self._extend_string(type_, {'national':True}, "CHAR(%(length)s)" % {'length': type_.length})
+            return self._extend_string(type_, {'national': True},
+                        "CHAR(%(length)s)" % {'length': type_.length})
         else:
-            return self._extend_string(type_, {'national':True}, "CHAR")
+            return self._extend_string(type_, {'national': True}, "CHAR")
 
     def visit_VARBINARY(self, type_):
         return "VARBINARY(%d)" % type_.length
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index cc41e6182..f705a216e 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2055,11 +2055,6 @@ class DDLCompiler(engine.Compiled):
 
 
 class GenericTypeCompiler(engine.TypeCompiler):
-    def visit_CHAR(self, type_):
-        return "CHAR" + (type_.length and "(%d)" % type_.length or "")
-
-    def visit_NCHAR(self, type_):
-        return "NCHAR" + (type_.length and "(%d)" % type_.length or "")
 
     def visit_FLOAT(self, type_):
         return "FLOAT"
@@ -2108,11 +2103,29 @@ class GenericTypeCompiler(engine.TypeCompiler):
     def visit_NCLOB(self, type_):
         return "NCLOB"
 
+    def _render_string_type(self, type_, name):
+
+        text = name
+        if type_.length:
+            text += "(%d)" % type_.length
+        if type_.collation:
+            text += ' COLLATE "%s"' % type_.collation
+        return text
+
+    def visit_CHAR(self, type_):
+        return self._render_string_type(type_, "CHAR")
+
+    def visit_NCHAR(self, type_):
+        return self._render_string_type(type_, "NCHAR")
+
     def visit_VARCHAR(self, type_):
-        return "VARCHAR" + (type_.length and "(%d)" % type_.length or "")
+        return self._render_string_type(type_, "VARCHAR")
 
     def visit_NVARCHAR(self, type_):
-        return "NVARCHAR" + (type_.length and "(%d)" % type_.length or "")
+        return self._render_string_type(type_, "NVARCHAR")
+
+    def visit_TEXT(self, type_):
+        return self._render_string_type(type_, "TEXT")
 
     def visit_BLOB(self, type_):
         return "BLOB"
@@ -2126,8 +2139,6 @@ class GenericTypeCompiler(engine.TypeCompiler):
     def visit_BOOLEAN(self, type_):
         return "BOOLEAN"
 
-    def visit_TEXT(self, type_):
-        return "TEXT"
 
     def visit_large_binary(self, type_):
         return self.visit_BLOB(type_)
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 560bc9c97..bdd619bad 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -162,3 +162,9 @@ class SuiteRequirements(Requirements):
     @property
     def index_reflection(self):
         return exclusions.open()
+
+    @property
+    def unbounded_varchar(self):
+        """Target database must support VARCHAR with no length"""
+
+        return exclusions.open()
diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py
index eeb19496b..71bd39ba6 100644
--- a/lib/sqlalchemy/types.py
+++ b/lib/sqlalchemy/types.py
@@ -971,7 +971,8 @@ class String(Concatenable, TypeEngine):
 
     __visit_name__ = 'string'
 
-    def __init__(self, length=None, convert_unicode=False,
+    def __init__(self, length=None, collation=None,
+                        convert_unicode=False,
                         assert_unicode=None, unicode_error=None,
                         _warn_on_bytestring=False
                         ):
@@ -979,13 +980,25 @@ class String(Concatenable, TypeEngine):
         Create a string-holding type.
 
         :param length: optional, a length for the column for use in
-          DDL statements.  May be safely omitted if no ``CREATE
+          DDL and CAST expressions.  May be safely omitted if no ``CREATE
           TABLE`` will be issued.  Certain databases may require a
           ``length`` for use in DDL, and will raise an exception when
           the ``CREATE TABLE`` DDL is issued if a ``VARCHAR``
           with no length is included.  Whether the value is
           interpreted as bytes or characters is database specific.
 
+        :param collation: Optional, a column-level collation for
+          use in DDL and CAST expressions.  Renders using the
+          COLLATE keyword supported by SQLite, MySQL, and Postgresql.
+          E.g.::
+
+            >>> from sqlalchemy import cast, select, String
+            >>> print select([cast('some string', String(collation='utf8'))])
+            SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
+
+          .. versionadded:: 0.8 Added support for COLLATE to all
+             string types.
+
         :param convert_unicode: When set to ``True``, the
           :class:`.String` type will assume that
           input is to be passed as Python ``unicode`` objects,
@@ -1046,6 +1059,7 @@ class String(Concatenable, TypeEngine):
                                  '*not* apply to DBAPIs that coerce '
                                  'Unicode natively.')
         self.length = length
+        self.collation = collation
         self.convert_unicode = convert_unicode
         self.unicode_error = unicode_error
         self._warn_on_bytestring = _warn_on_bytestring