- adjust some tests and such to work better with a mysql 5.5 install

  - Added mysql_length parameter to Index construct,
    specifies "length" for indexes.  [ticket:2293]

8 files changed, 101 insertions, 13 deletions

diff --git a/CHANGES b/CHANGES
index 2119269bb..f34bd810d 100644
--- a/CHANGES
+++ b/CHANGES
@@ -247,6 +247,9 @@ CHANGES
     MySQL's arbitrary rules regarding if it will actually
     work or not.  [ticket:2225]  Also in 0.6.9.
 
+  - Added mysql_length parameter to Index construct,
+    specifies "length" for indexes.  [ticket:2293]
+
 - mssql
   - Changes to attempt support of FreeTDS 0.91 with 
     Pyodbc.  This includes that string binds are sent as 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 80067f9dc..1a30e15fd 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -197,6 +197,32 @@ within an application, overrides the compilation of the
 fully rendering CAST; else the internal element of the
 construct is rendered directly.
 
+
+.. _mysql_indexes:
+
+MySQL Specific Index Options
+----------------------------
+
+MySQL-specific extensions to the :class:`.Index` construct are available.
+
+Index Length
+~~~~~~~~~~~~~
+
+MySQL provides an option to create index entries with a certain length, where
+"length" refers to the number of characters or bytes in each value which will
+become part of the index. SQLAlchemy provides this feature via the
+``mysql_length`` parameter::
+
+    Index('my_index', my_table.c.data, mysql_length=10)
+
+Prefix lengths are given in characters for nonbinary string types and in bytes
+for binary string types. The value passed to the keyword argument will be
+simply passed through to the underlying CREATE INDEX command, so it *must* be
+an integer. MySQL only allows a length for an index if it is for a CHAR,
+VARCHAR, TEXT, BINARY, VARBINARY and BLOB.
+
+More information can be found at:
+http://dev.mysql.com/doc/refman/5.0/en/create-index.html
 """
 
 import datetime, inspect, re, sys
@@ -1395,6 +1421,30 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             table_opts.append(joiner.join((opt, arg)))
         return ' '.join(table_opts)
 
+    def visit_create_index(self, create):
+        index = create.element
+        preparer = self.preparer
+        text = "CREATE "
+        if index.unique:
+            text += "UNIQUE "
+        text += "INDEX %s ON %s " \
+                    % (preparer.quote(self._index_identifier(index.name), 
+                        index.quote),preparer.format_table(index.table))
+        if 'mysql_length' in index.kwargs:
+            length = index.kwargs['mysql_length']
+        else:
+            length = None
+        if length is not None:
+            text+= "(%s(%d))" \
+                    % (', '.join(preparer.quote(c.name, c.quote)
+                                 for c in index.columns), length)
+        else:
+            text+= "(%s)" \
+                    % (', '.join(preparer.quote(c.name, c.quote)
+                                 for c in index.columns))
+        return text
+
+
     def visit_drop_index(self, drop):
         index = drop.element
 
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index 99c8acbfe..3d00b3197 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -2086,6 +2086,8 @@ class Index(ColumnCollectionMixin, SchemaItem):
     :ref:`schema_indexes` - General information on :class:`.Index`.
 
     :ref:`postgresql_indexes` - PostgreSQL-specific options available for the :class:`.Index` construct.
+
+    :ref:`mysql_indexes` - MySQL-specific options available for the :class:`.Index` construct.
     """
 
     __visit_name__ = 'index'
diff --git a/test/dialect/test_mysql.py b/test/dialect/test_mysql.py
index 433c3eded..51b4062fc 100644
--- a/test/dialect/test_mysql.py
+++ b/test/dialect/test_mysql.py
@@ -29,6 +29,21 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(x, 
             '''SELECT mysql_table.col1, mysql_table.`master_ssl_verify_server_cert` FROM mysql_table''')
 
+    def test_create_index_with_length(self):
+        m = MetaData()
+        tbl = Table('testtbl', m, Column('data', String(255)))
+        idx = Index('test_idx1', tbl.c.data,
+                    mysql_length=10)
+        idx2 = Index('test_idx2', tbl.c.data,
+                    mysql_length=5)
+
+        self.assert_compile(schema.CreateIndex(idx),
+                            'CREATE INDEX test_idx1 ON testtbl (data(10))',
+                            dialect=mysql.dialect())
+        self.assert_compile(schema.CreateIndex(idx2),
+                            "CREATE INDEX test_idx2 ON testtbl (data(5))",
+                            dialect=mysql.dialect())
+
 class DialectTest(fixtures.TestBase):
     __only_on__ = 'mysql'
 
@@ -1002,39 +1017,47 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
             Table('ai_1', meta,
                   Column('int_y', Integer, primary_key=True),
                   Column('int_n', Integer, DefaultClause('0'),
-                         primary_key=True))
+                         primary_key=True),
+                         mysql_engine='MyISAM')
             Table('ai_2', meta,
                   Column('int_y', Integer, primary_key=True),
                   Column('int_n', Integer, DefaultClause('0'),
-                         primary_key=True))
+                         primary_key=True),
+                         mysql_engine='MyISAM')
             Table('ai_3', meta,
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True, autoincrement=False),
-                  Column('int_y', Integer, primary_key=True))
+                  Column('int_y', Integer, primary_key=True),
+                         mysql_engine='MyISAM')
             Table('ai_4', meta,
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True, autoincrement=False),
                   Column('int_n2', Integer, DefaultClause('0'),
-                         primary_key=True, autoincrement=False))
+                         primary_key=True, autoincrement=False),
+                         mysql_engine='MyISAM')
             Table('ai_5', meta,
                   Column('int_y', Integer, primary_key=True),
                   Column('int_n', Integer, DefaultClause('0'),
-                         primary_key=True, autoincrement=False))
+                         primary_key=True, autoincrement=False),
+                         mysql_engine='MyISAM')
             Table('ai_6', meta,
                   Column('o1', String(1), DefaultClause('x'),
                          primary_key=True),
-                  Column('int_y', Integer, primary_key=True))
+                  Column('int_y', Integer, primary_key=True),
+                         mysql_engine='MyISAM')
             Table('ai_7', meta,
                   Column('o1', String(1), DefaultClause('x'),
                          primary_key=True),
                   Column('o2', String(1), DefaultClause('x'),
                          primary_key=True),
-                  Column('int_y', Integer, primary_key=True))
+                  Column('int_y', Integer, primary_key=True),
+                         mysql_engine='MyISAM')
             Table('ai_8', meta,
                   Column('o1', String(1), DefaultClause('x'),
                          primary_key=True),
                   Column('o2', String(1), DefaultClause('x'),
-                         primary_key=True))
+                         primary_key=True),
+                         mysql_engine='MyISAM')
             meta.create_all()
 
             table_names = ['ai_1', 'ai_2', 'ai_3', 'ai_4',
@@ -1438,11 +1461,13 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
         cattable = Table('cattable', metadata,
             Column('id', Integer, primary_key=True),
             Column('description', String(50)),
+            mysql_engine='MyISAM'
         )
         matchtable = Table('matchtable', metadata,
             Column('id', Integer, primary_key=True),
             Column('title', String(200)),
             Column('category_id', Integer, ForeignKey('cattable.id')),
+            mysql_engine='MyISAM'
         )
         metadata.create_all()
 
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index c79e09ca8..b79d3b7c2 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -160,12 +160,14 @@ class ReflectionTest(fixtures.TestBase, ComparesTables):
         t1 = Table('test', meta,
             Column('id', sa.Integer, primary_key=True),
             Column('data', sa.String(50)),
+            mysql_engine='MyISAM'
         )
         t2 = Table('test2', meta,
             Column('id', sa.Integer, sa.ForeignKey('test.id'),
                                         primary_key=True),
             Column('id2', sa.Integer, primary_key=True),
             Column('data', sa.String(50)),
+            mysql_engine='MyISAM'
         )
         meta.create_all()
         try:
@@ -1166,8 +1168,9 @@ class CaseSensitiveTest(fixtures.TablesTest):
         assert t1.c.x is not None
 
     @testing.fails_if(lambda: 
-            testing.against('mysql') and 
-            not testing.requires._has_mysql_fully_case_sensitive())
+            testing.against(('mysql', '<', (5, 5))) and 
+            not testing.requires._has_mysql_fully_case_sensitive()
+            )
     def test_reflect_via_fk(self):
         m = MetaData()
         t2 = Table("SomeOtherTable", m, autoload=True, autoload_with=testing.db)
diff --git a/test/engine/test_transaction.py b/test/engine/test_transaction.py
index 344a30734..2d6861f0d 100644
--- a/test/engine/test_transaction.py
+++ b/test/engine/test_transaction.py
@@ -298,6 +298,7 @@ class TransactionTest(fixtures.TestBase):
     # PG emergency shutdown:
     # select * from pg_prepared_xacts
     # ROLLBACK PREPARED '<xid>'
+    @testing.crashes('mysql', 'Crashing on 5.5, not worth it')
     @testing.requires.skip_mysql_on_windows
     @testing.requires.two_phase_transactions
     @testing.requires.savepoints
diff --git a/test/orm/test_unitofwork.py b/test/orm/test_unitofwork.py
index 10d13ec8a..c81f15c98 100644
--- a/test/orm/test_unitofwork.py
+++ b/test/orm/test_unitofwork.py
@@ -746,11 +746,13 @@ class DefaultTest(fixtures.MappedTest):
                    test_needs_autoincrement=True),
             Column('hoho', hohotype, server_default=str(hohoval)),
             Column('counter', Integer, default=sa.func.char_length("1234567", type_=Integer)),
-            Column('foober', String(30), default="im foober", onupdate="im the update"))
+            Column('foober', String(30), default="im foober", onupdate="im the update"),
+            mysql_engine='MyISAM')
 
         st = Table('secondary_table', metadata,
             Column('id', Integer, primary_key=True, test_needs_autoincrement=True),
-            Column('data', String(50)))
+            Column('data', String(50)),
+            mysql_engine='MyISAM')
 
         if testing.against('postgresql', 'oracle'):
             dt.append_column(
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 4d1af2cff..7f127a6ef 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -188,11 +188,13 @@ class QueryTest(fixtures.TestBase):
     @testing.fails_on('sqlite', "sqlite autoincremnt doesn't work with composite pks")
     def test_misordered_lastrow(self):
         related = Table('related', metadata,
-            Column('id', Integer, primary_key=True)
+            Column('id', Integer, primary_key=True),
+            mysql_engine='MyISAM'
         )
         t6 = Table("t6", metadata,
             Column('manual_id', Integer, ForeignKey('related.id'), primary_key=True),
             Column('auto_id', Integer, primary_key=True, test_needs_autoincrement=True),
+            mysql_engine='MyISAM'
         )
 
         metadata.create_all()