Commits

Mike Bayer  committed 9211ecb

- Unit tests pass 100% on MySQL installed
on windows, after aggressive exclusion of a wide variety
of tests. Not clear to what degree the failures are related to
version 5.5 vs. the usage of windows, in particular the ON UPDATE CASCADE
immediately crashes the server. The features being tested here are all
edge cases not likely to be used in typical MySQL environments.
- Removed the "adjust casing" step that would
fail when reflecting a table on MySQL
on windows with a mixed case name. After some
experimenting with a windows MySQL server, it's
been determined that this step wasn't really
helping the situation much; MySQL does not return
FK names with proper casing on non-windows
platforms either, and removing the step at
least allows the reflection to act more like
it does on other OSes. A warning here
has been considered but its difficult to
determine under what conditions such a warning
can be raised, so punted on that for now -
added some docs instead. [ticket:2181]

- supports_sane_rowcount will be set to False
if using MySQLdb and the DBAPI doesn't provide
the constants.CLIENT module.

  • Participants
  • Parent commits bf0890c

Comments (0)

Files changed (11)

     and are redundant:  reflecttable(), create(), 
     drop(), text(), engine.func
 
+- mysql
+  - Unit tests pass 100% on MySQL installed
+    on windows.
+
+  - Removed the "adjust casing" step that would
+    fail when reflecting a table on MySQL
+    on windows with a mixed case name.  After some
+    experimenting with a windows MySQL server, it's
+    been determined that this step wasn't really 
+    helping the situation much; MySQL does not return
+    FK names with proper casing on non-windows 
+    platforms either, and removing the step at
+    least allows the reflection to act more like
+    it does on other OSes.   A warning here
+    has been considered but its difficult to 
+    determine under what conditions such a warning
+    can be raised, so punted on that for now - 
+    added some docs instead. [ticket:2181]
+
+  - supports_sane_rowcount will be set to False
+    if using MySQLdb and the DBAPI doesn't provide 
+    the constants.CLIENT module.
+
 0.7.0
 =======
 - This section documents those changes from 0.7b4

File README.unittests

 
 Additional steps specific to individual databases are as follows:
 
+    MYSQL: Default storage engine should be "MyISAM".   Tests that require
+    "InnoDB" as the engine will specify this explicitly.
+
     ORACLE: a user named "test_schema" is created.
 
     The primary database user needs to be able to create and drop tables,

File lib/sqlalchemy/connectors/mysqldb.py

                                     ).constants.CLIENT
                 client_flag |= CLIENT_FLAGS.FOUND_ROWS
             except (AttributeError, ImportError):
-                pass
+                self.supports_sane_rowcount = False
             opts['client_flag'] = client_flag
         return [[], opts]
 

File lib/sqlalchemy/dialects/mysql/base.py

         mysql_charset='utf8'
        )
 
+Case Sensitivity and Table Reflection
+-------------------------------------
+
+MySQL has inconsistent support for case-sensitive identifier
+names, basing support on specific details of the underlying
+operating system. However, it has been observed that no matter
+what case sensitivity behavior is present, the names of tables in
+foreign key declarations are *always* received from the database
+as all-lower case, making it impossible to accurately reflect a
+schema where inter-related tables use mixed-case identifier names.
+
+Therefore it is strongly advised that table names be declared as
+all lower case both within SQLAlchemy as well as on the MySQL
+database itself, especially if database reflection features are
+to be used.
+
 Keys
 ----
 
             sql = parser._describe_to_create(table_name, columns)
         return parser.parse(sql, charset)
 
-    def _adjust_casing(self, table, charset=None):
-        """Adjust Table name to the server case sensitivity, if needed."""
-
-        casing = self._server_casing
-
-        # For winxx database hosts.  TODO: is this really needed?
-        if casing == 1 and table.name != table.name.lower():
-            table.name = table.name.lower()
-            lc_alias = sa_schema._get_table_key(table.name, table.schema)
-            table.metadata.tables[lc_alias] = table
-
     def _detect_charset(self, connection):
         raise NotImplementedError()
 
         self.dialect = dialect
         self.preparer = preparer
         self._prep_regexes()
-    
+
     def parse(self, show_create, charset):
         state = ReflectedState()
         state.charset = charset

File lib/sqlalchemy/engine/reflection.py

         """
         dialect = self.bind.dialect
 
-        # MySQL dialect does this.  Applicable with other dialects?
-        if hasattr(dialect, '_connection_charset') \
-                                        and hasattr(dialect, '_adjust_casing'):
-            charset = dialect._connection_charset
-            dialect._adjust_casing(table)
-
         # table attributes we might need.
         reflection_options = dict(
             (k, table.kwargs.get(k)) for k in dialect.reflection_options if k in table.kwargs)

File test/engine/test_reflection.py

         finally:
             meta.drop_all()
 
+    @testing.provide_metadata
     def test_nonreflected_fk_raises(self):
         """test that a NoReferencedColumnError is raised when reflecting
         a table with an FK to another table which has not included the target
 
         """
 
-        meta = MetaData(testing.db)
+        meta = self.metadata
         a1 = Table('a', meta,
             Column('x', sa.Integer, primary_key=True),
             Column('z', sa.Integer),
             test_needs_fk=True
         )
         meta.create_all()
-        try:
-            m2 = MetaData(testing.db)
-            a2 = Table('a', m2, include_columns=['z'], autoload=True)
-            b2 = Table('b', m2, autoload=True)
-
-            assert_raises(sa.exc.NoReferencedColumnError, a2.join, b2)
-        finally:
-            meta.drop_all()
+        m2 = MetaData(testing.db)
+        a2 = Table('a', m2, include_columns=['z'], autoload=True)
+        b2 = Table('b', m2, autoload=True)
 
+        assert_raises(sa.exc.NoReferencedColumnError, a2.join, b2)
 
     @testing.exclude('mysql', '<', (4, 1, 1), 'innodb funkiness')
     def test_override_existing_fk(self):
 
 
     @testing.crashes('oracle', 'FIXME: unknown, confirm not fails_on')
-    @testing.fails_on('+informixdb', 'FIXME: should be supported via the DELIMITED env var but that breaks everything else for now')
+    @testing.fails_on('+informixdb', 
+                        "FIXME: should be supported via the "
+                        "DELIMITED env var but that breaks "
+                        "everything else for now")
     def test_reserved(self):
 
         # check a table that uses an SQL reserved name doesn't cause an
                             'weird_casing."Col2", weird_casing."col3" '
                             'FROM weird_casing')
 
+class CaseSensitiveTest(fixtures.TablesTest):
+    """Nail down case sensitive behaviors, mostly on MySQL."""
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table('SomeTable', metadata, 
+            Column('x', Integer, primary_key=True),
+            test_needs_fk=True
+        )
+        Table('SomeOtherTable', metadata, 
+            Column('x', Integer, primary_key=True),
+            Column('y', Integer, sa.ForeignKey("SomeTable.x")),
+            test_needs_fk=True
+        )
+
+    @testing.fails_if(testing.requires._has_mysql_on_windows)
+    def test_table_names(self):
+        x = testing.db.run_callable(
+            testing.db.dialect.get_table_names
+        )
+        assert set(["SomeTable", "SomeOtherTable"]).issubset(x)
+
+    def test_reflect_exact_name(self):
+        m = MetaData()
+        t1 = Table("SomeTable", m, autoload=True, autoload_with=testing.db)
+        eq_(t1.name, "SomeTable")
+        assert t1.c.x is not None
+
+    @testing.fails_on('mysql', 'FKs come back as lower case no matter what')
+    def test_reflect_via_fk(self):
+        m = MetaData()
+        t2 = Table("SomeOtherTable", m, autoload=True, autoload_with=testing.db)
+        eq_(t2.name, "SomeOtherTable")
+        assert "SomeTable" in m.tables
+
+    @testing.fails_on_everything_except('sqlite', 'mysql')
+    def test_reflect_case_insensitive(self):
+        m = MetaData()
+        t2 = Table("sOmEtAbLe", m, autoload=True, autoload_with=testing.db)
+        eq_(t2.name, "sOmEtAbLe")
+
+
 class ComponentReflectionTest(fixtures.TestBase):
 
     @testing.requires.schemas

File test/engine/test_transaction.py

     # PG emergency shutdown:
     # select * from pg_prepared_xacts
     # ROLLBACK PREPARED '<xid>'
+    @testing.requires.skip_mysql_on_windows
     @testing.requires.two_phase_transactions
     @testing.requires.savepoints
     def test_mixed_two_phase_transaction(self):

File test/lib/requires.py

     return _chain_decorators_on(
         fn,
         emits_warning_on('mssql', 'Savepoint support in mssql is experimental and may lead to data loss.'),
-        no_support('access', 'not supported by database'),
-        no_support('sqlite', 'not supported by database'),
-        no_support('sybase', 'FIXME: guessing, needs confirmation'),
-        exclude('mysql', '<', (5, 0, 3), 'not supported by database'),
-        exclude('informix', '<', (11, 55, 'xC3'), 'not supported by database'),
+        no_support('access', 'savepoints not supported'),
+        no_support('sqlite', 'savepoints not supported'),
+        no_support('sybase', 'savepoints not supported'),
+        exclude('mysql', '<', (5, 0, 3), 'savepoints not supported'),
+        exclude('informix', '<', (11, 55, 'xC3'), 'savepoints not supported'),
         )
 
 def denormalized_names(fn):
     except ImportError:
         return False
 
+def _has_mysql_on_windows():
+    return testing.against('mysql+mysqldb') and \
+            testing.db.dialect._server_casing == 1
+
 def sqlite(fn):
     return _chain_decorators_on(
         fn,
         fn,
         skip_if(lambda: config.options.low_connections)
     )
+
+def skip_mysql_on_windows(fn):
+    """Catchall for a large variety of MySQL on Windows failures"""
+
+    return _chain_decorators_on(
+        fn,
+        skip_if(_has_mysql_on_windows,
+            "Not supported on MySQL + Windows"
+        )
+    )

File test/lib/testing.py

       _is_excluded('bigdb', '==', (9,0,9))
       _is_excluded('yikesdb', 'in', ((0, 3, 'alpha2'), (0, 3, 'alpha3')))
     """
+
     vendor_spec = db_spec(db)
 
     if not vendor_spec(config.db):

File test/orm/test_naturalpks.py

 from test.orm import _fixtures
 
 class NaturalPKTest(fixtures.MappedTest):
+    # MySQL 5.5 on Windows crashes (the entire server, not the client)
+    # if you screw around with ON UPDATE CASCADE type of stuff.
+    __requires__ = 'skip_mysql_on_windows',
 
     @classmethod
     def define_tables(cls, metadata):
 
 
 class NonPKCascadeTest(fixtures.MappedTest):
+    __requires__ = 'skip_mysql_on_windows',
+
     @classmethod
     def define_tables(cls, metadata):
         if testing.against('oracle'):
     # mssql doesn't allow ON UPDATE on self-referential keys
     __unsupported_on__ = ('mssql',) 
 
+    __requires__ = 'skip_mysql_on_windows',
+
     @classmethod
     def define_tables(cls, metadata):
         if testing.against('oracle'):

File test/sql/test_types.py

         eq_(e1.adapt(ENUM).name, 'foo')
         eq_(e1.adapt(ENUM).schema, 'bar')
 
-    @testing.fails_on('mysql+mysqldb', "MySQL seems to issue a 'data truncated' warning.")
-    @testing.fails_on('mysql+pymysql', "MySQL seems to issue a 'data truncated' warning.")
+    @testing.crashes('mysql', 
+                    'Inconsistent behavior across various OS/drivers'
+                )
     def test_constraint(self):
         assert_raises(exc.DBAPIError, 
             enum_table.insert().execute,