- Changed the scheme used to generate truncated

"auto" index names when using the "index=True"
flag on Column.   The truncation only takes
place with the auto-generated name, not one
that is user-defined (an error would be
raised instead), and the truncation scheme
itself is now based on a fragment of an md5
hash of the identifier name, so that multiple
indexes on columns with similar names still
have unique names.  [ticket:1855]

12 files changed, 73 insertions, 34 deletions

diff --git a/CHANGES b/CHANGES
index 12730f954..3aaff9b00 100644
--- a/CHANGES
+++ b/CHANGES
@@ -3,6 +3,20 @@
 =======
 CHANGES
 =======
+0.6.4
+=====
+- sql
+  - Changed the scheme used to generate truncated
+    "auto" index names when using the "index=True"
+    flag on Column.   The truncation only takes
+    place with the auto-generated name, not one
+    that is user-defined (an error would be 
+    raised instead), and the truncation scheme 
+    itself is now based on a fragment of an md5
+    hash of the identifier name, so that multiple
+    indexes on columns with similar names still
+    have unique names.  [ticket:1855]
+
 0.6.3
 =====
 - orm
@@ -38,7 +52,7 @@ CHANGES
     naming/typing information about the entities
     the Query will return.  Can be helpful for 
     building GUIs on top of ORM queries.
-
+    
 - mysql
 
   - The _extract_error_code() method now works 
diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index 35f91a5c1..2d809e339 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -114,6 +114,6 @@ from sqlalchemy.engine import create_engine, engine_from_config
 __all__ = sorted(name for name, obj in locals().items()
                  if not (name.startswith('_') or inspect.ismodule(obj)))
                  
-__version__ = '0.6.3'
+__version__ = '0.6.4'
 
 del inspect, sys
diff --git a/lib/sqlalchemy/dialects/access/base.py b/lib/sqlalchemy/dialects/access/base.py
index 8efea5941..75ea91287 100644
--- a/lib/sqlalchemy/dialects/access/base.py
+++ b/lib/sqlalchemy/dialects/access/base.py
@@ -436,7 +436,7 @@ class AccessDDLCompiler(compiler.DDLCompiler):
         index = drop.element
         self.append("\nDROP INDEX [%s].[%s]" % \
                         (index.table.name,
-                        self._validate_identifier(index.name, False)))
+                        self._index_identifier(index.name)))
 
 class AccessIdentifierPreparer(compiler.IdentifierPreparer):
     reserved_words = compiler.RESERVED_WORDS.copy()
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 820b3a0c2..395015c77 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -967,7 +967,7 @@ class MSDDLCompiler(compiler.DDLCompiler):
         return "\nDROP INDEX %s.%s" % (
             self.preparer.quote_identifier(drop.element.table.name),
             self.preparer.quote(
-                        self._validate_identifier(drop.element.name, False),
+                        self._index_identifier(drop.element.name),
                         drop.element.quote)
             )
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index af18ae551..08978dfe3 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1358,7 +1358,7 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
         index = drop.element
         
         return "\nDROP INDEX %s ON %s" % \
-                    (self.preparer.quote(self._validate_identifier(index.name, False), index.quote),
+                    (self.preparer.quote(self._index_identifier(index.name), index.quote),
                      self.preparer.format_table(index.table))
 
     def visit_drop_constraint(self, drop):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index c260e5f68..5eba16372 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -480,7 +480,7 @@ class PGDDLCompiler(compiler.DDLCompiler):
             text += "UNIQUE "
         text += "INDEX %s ON %s (%s)" \
                 % (preparer.quote(
-                    self._validate_identifier(index.name, True), index.quote),
+                    self._index_identifier(index.name), index.quote),
                    preparer.format_table(index.table),
                    ', '.join([preparer.format_column(c) 
                                 for c in index.columns]))
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 030b45aff..2d9e56baf 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -280,7 +280,7 @@ class SQLiteDDLCompiler(compiler.DDLCompiler):
             text += "UNIQUE "
         text += "INDEX %s ON %s (%s)" \
                     % (preparer.format_index(index,
-                       name=self._validate_identifier(index.name, True)),
+                       name=self._index_identifier(index.name)),
                        preparer.format_table(index.table, use_schema=False),
                        ', '.join(preparer.quote(c.name, c.quote)
                                  for c in index.columns))
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index 53ceedc2b..b0b1bbff4 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -356,7 +356,7 @@ class SybaseDDLCompiler(compiler.DDLCompiler):
         return "\nDROP INDEX %s.%s" % (
             self.preparer.quote_identifier(index.table.name),
             self.preparer.quote(
-                    self._validate_identifier(index.name, False), index.quote)
+                    self._index_identifier(index.name), index.quote)
             )
 
 class SybaseIdentifierPreparer(compiler.IdentifierPreparer):
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index a2f697937..75bbfd070 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -825,7 +825,7 @@ class Column(SchemaItem, expression.ColumnClause):
                     "The 'index' keyword argument on Column is boolean only. "
                     "To create indexes with a specific name, create an "
                     "explicit Index object external to the Table.")
-            Index('ix_%s' % self._label, self, unique=self.unique)
+            Index(expression._generated_label('ix_%s' % self._label), self, unique=self.unique)
         elif self.unique:
             if isinstance(self.unique, basestring):
                 raise exc.ArgumentError(
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index c54931b87..0383f9690 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1179,7 +1179,18 @@ class DDLCompiler(engine.Compiled):
         
     def visit_drop_table(self, drop):
         return "\nDROP TABLE " + self.preparer.format_table(drop.element)
-        
+
+    def _index_identifier(self, ident):
+        if isinstance(ident, sql._generated_label):
+            if len(ident) > self.dialect.max_identifier_length:
+                return ident[0:self.dialect.max_identifier_length - 8] + \
+                                "_" + util.md5_hex(ident)[-4:]
+            else:
+                return ident
+        else:
+            self.dialect.validate_identifier(ident)
+            return ident
+
     def visit_create_index(self, create):
         index = create.element
         preparer = self.preparer
@@ -1187,7 +1198,8 @@ class DDLCompiler(engine.Compiled):
         if index.unique:
             text += "UNIQUE "   
         text += "INDEX %s ON %s (%s)" \
-                    % (preparer.quote(self._validate_identifier(index.name, True), index.quote),
+                    % (preparer.quote(self._index_identifier(index.name), 
+                        index.quote),
                        preparer.format_table(index.table),
                        ', '.join(preparer.quote(c.name, c.quote)
                                  for c in index.columns))
@@ -1196,7 +1208,7 @@ class DDLCompiler(engine.Compiled):
     def visit_drop_index(self, drop):
         index = drop.element
         return "\nDROP INDEX " + \
-                    self.preparer.quote(self._validate_identifier(index.name, False), index.quote)
+                    self.preparer.quote(self._index_identifier(index.name), index.quote)
 
     def visit_add_constraint(self, create):
         preparer = self.preparer
@@ -1238,18 +1250,6 @@ class DDLCompiler(engine.Compiled):
     def post_create_table(self, table):
         return ''
 
-    def _validate_identifier(self, ident, truncate):
-        if truncate:
-            if len(ident) > self.dialect.max_identifier_length:
-                counter = getattr(self, 'counter', 0)
-                self.counter = counter + 1
-                return ident[0:self.dialect.max_identifier_length - 6] + "_" + hex(self.counter)[2:]
-            else:
-                return ident
-        else:
-            self.dialect.validate_identifier(ident)
-            return ident
-
     def get_column_default_string(self, column):
         if isinstance(column.server_default, schema.DefaultClause):
             if isinstance(column.server_default.arg, basestring):
diff --git a/lib/sqlalchemy/util.py b/lib/sqlalchemy/util.py
index efbb4c653..7ed11ca96 100644
--- a/lib/sqlalchemy/util.py
+++ b/lib/sqlalchemy/util.py
@@ -216,6 +216,18 @@ except:
             return func(*(args + fargs), **newkeywords)
         return newfunc
 
+try:
+    import hashlib
+    _md5 = hashlib.md5
+except ImportError:
+    import md5
+    _md5 = md5.new
+
+def md5_hex(x):
+    m = _md5()
+    m.update(x)
+    return m.hexdigest()
+
 
 def accepts_a_list_as_starargs(list_deprecation=None):
     def decorate(fn):
diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py
index 33e4b8d76..5624c0ec6 100644
--- a/test/sql/test_constraints.py
+++ b/test/sql/test_constraints.py
@@ -183,18 +183,31 @@ class ConstraintTest(TestBase, AssertsExecutionResults, AssertsCompiledSQL):
 
     def test_too_long_idx_name(self):
         dialect = testing.db.dialect.__class__()
-        dialect.max_identifier_length = 20
+        dialect.max_identifier_length = 22
 
-        t1 = Table("sometable", MetaData(), Column("foo", Integer))
-        self.assert_compile(
-            schema.CreateIndex(Index("this_name_is_too_long_for_what_were_doing", t1.c.foo)),
-            "CREATE INDEX this_name_is_t_1 ON sometable (foo)",
-            dialect=dialect
-        )
+        for tname, cname, exp in [
+            ('sometable', 'this_name_is_too_long', 'ix_sometable_t_09aa'),
+            ('sometable', 'this_name_alsois_long', 'ix_sometable_t_3cf1'),
+        ]:
         
-        self.assert_compile(
-            schema.CreateIndex(Index("this_other_name_is_too_long_for_what_were_doing", t1.c.foo)),
-            "CREATE INDEX this_other_nam_1 ON sometable (foo)",
+            t1 = Table(tname, MetaData(), 
+                        Column(cname, Integer, index=True),
+                    )
+            ix1 = list(t1.indexes)[0]
+        
+            self.assert_compile(
+                schema.CreateIndex(ix1),
+                "CREATE INDEX %s "
+                "ON %s (%s)" % (exp, tname, cname),
+                dialect=dialect
+            )
+        
+        t1 = Table('t', MetaData(), Column('c', Integer))
+        assert_raises(
+            exc.IdentifierError,
+            schema.CreateIndex(Index(
+                        "this_other_name_is_too_long_for_what_were_doing", 
+                        t1.c.c)).compile,
             dialect=dialect
         )