Test suite modernization in progress. Big changes:

 - @unsupported now only accepts a single target and demands a reason
   for not running the test.
 - @exclude also demands an exclusion reason
 - Greatly expanded @testing.requires.<feature>, eliminating many
   decorators in the suite and signficantly easing integration of
   multi-driver support.
 - New ORM test base class, and a featureful base for mapped tests
 - Usage of 'global' for shared setup going away, * imports as well

8 files changed, 77 insertions, 47 deletions

diff --git a/test/sql/case_statement.py b/test/sql/case_statement.py
index 876f820b5..667d2a22d 100644
--- a/test/sql/case_statement.py
+++ b/test/sql/case_statement.py
@@ -28,6 +28,7 @@ class CaseTest(TestBase, AssertsCompiledSQL):
         info_table.drop()
 
     @testing.fails_on('maxdb')
+    @testing.requires.subqueries
     def testcase(self):
         inner = select([case([
                 [info_table.c.pk < 3,
diff --git a/test/sql/constraints.py b/test/sql/constraints.py
index 966930ca9..5ce5cca34 100644
--- a/test/sql/constraints.py
+++ b/test/sql/constraints.py
@@ -54,7 +54,7 @@ class ConstraintTest(TestBase, AssertsExecutionResults):
             )
         metadata.create_all()
 
-    @testing.unsupported('mysql')
+    @testing.fails_on('mysql')
     def test_check_constraint(self):
         foo = Table('foo', metadata,
             Column('id', Integer, primary_key=True),
diff --git a/test/sql/defaults.py b/test/sql/defaults.py
index e9ed21a65..3ba8c7d02 100644
--- a/test/sql/defaults.py
+++ b/test/sql/defaults.py
@@ -268,6 +268,8 @@ class DefaultTest(TestBase):
             testing.db.execute("drop table speedy_users", None)
 
 class PKDefaultTest(TestBase):
+    __requires__ = ('subqueries',)
+
     def setUpAll(self):
         global metadata, t1, t2
 
@@ -277,7 +279,8 @@ class PKDefaultTest(TestBase):
             Column('nextid', Integer))
 
         t1 = Table('t1', metadata,
-            Column('id', Integer, primary_key=True, default=select([func.max(t2.c.nextid)]).as_scalar()),
+            Column('id', Integer, primary_key=True,
+                   default=select([func.max(t2.c.nextid)]).as_scalar()),
             Column('data', String(30)))
 
         metadata.create_all()
@@ -285,7 +288,7 @@ class PKDefaultTest(TestBase):
     def tearDownAll(self):
         metadata.drop_all()
 
-    @testing.unsupported('mssql')
+    @testing.unsupported('mssql', 'FIXME: unknown, verify not fails_on')
     def test_basic(self):
         t2.insert().execute(nextid=1)
         r = t1.insert().execute(data='hi')
@@ -407,8 +410,8 @@ class AutoIncrementTest(TestBase):
 
 
 class SequenceTest(TestBase):
-    __unsupported_on__ = ('sqlite', 'mysql', 'mssql', 'firebird',
-                          'sybase', 'access')
+    __requires__ = ('sequences',)
+    # TODO: 'firebird' was in __unsupported__.  are they, truly?
 
     def setUpAll(self):
         global cartitems, sometable, metadata
diff --git a/test/sql/labels.py b/test/sql/labels.py
index cbcd4636e..78b31adc4 100644
--- a/test/sql/labels.py
+++ b/test/sql/labels.py
@@ -99,30 +99,34 @@ class LongLabelsTest(TestBase, AssertsCompiledSQL):
         table1.insert().execute(**{"this_is_the_data_column":"data3"})
         table1.insert().execute(**{"this_is_the_data_column":"data4"})
 
+    @testing.requires.subqueries
     def test_subquery(self):
-      # this is the test that fails if the "max identifier length" is shorter than the
-      # length of the actual columns created, because the column names get truncated.
-      # if you try to separate "physical columns" from "labels", and only truncate the labels,
-      # the compiler.DefaultCompiler.visit_select() logic which auto-labels columns in a subquery (for the purposes of sqlite compat) breaks the code,
-      # since it is creating "labels" on the fly but not affecting derived columns, which think they are
-      # still "physical"
-      q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias('foo')
-      x = select([q])
-      print x.execute().fetchall()
-
+        # this is the test that fails if the "max identifier length" is
+        # shorter than the length of the actual columns created, because the
+        # column names get truncated.  if you try to separate "physical
+        # columns" from "labels", and only truncate the labels, the
+        # compiler.DefaultCompiler.visit_select() logic which auto-labels
+        # columns in a subquery (for the purposes of sqlite compat) breaks the
+        # code, since it is creating "labels" on the fly but not affecting
+        # derived columns, which think they are still "physical"
+        q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias('foo')
+        x = select([q])
+        print x.execute().fetchall()
+
+    @testing.requires.subqueries
     def test_anon_alias(self):
-      compile_dialect = default.DefaultDialect()
-      compile_dialect.max_identifier_length = IDENT_LENGTH
+        compile_dialect = default.DefaultDialect()
+        compile_dialect.max_identifier_length = IDENT_LENGTH
 
-      q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias()
-      x = select([q], use_labels=True)
+        q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias()
+        x = select([q], use_labels=True)
 
-      self.assert_compile(x, "SELECT anon_1.this_is_the_primarykey_column AS anon_1_this_is_the_prim_1, anon_1.this_is_the_data_column AS anon_1_this_is_the_data_2 "
+        self.assert_compile(x, "SELECT anon_1.this_is_the_primarykey_column AS anon_1_this_is_the_prim_1, anon_1.this_is_the_data_column AS anon_1_this_is_the_data_2 "
             "FROM (SELECT some_large_named_table.this_is_the_primarykey_column AS this_is_the_primarykey_column, some_large_named_table.this_is_the_data_column AS this_is_the_data_column "
             "FROM some_large_named_table "
             "WHERE some_large_named_table.this_is_the_primarykey_column = :this_is_the_primarykey__1) AS anon_1", dialect=compile_dialect)
 
-      print x.execute().fetchall()
+        print x.execute().fetchall()
 
     def test_oid(self):
         """test that a primary key column compiled as the 'oid' column gets proper length truncation"""
diff --git a/test/sql/query.py b/test/sql/query.py
index a305a5314..f9ed7f540 100644
--- a/test/sql/query.py
+++ b/test/sql/query.py
@@ -140,6 +140,7 @@ class QueryTest(TestBase):
             l.append(row)
         self.assert_(len(l) == 3)
 
+    @testing.requires.subqueries
     def test_anonymous_rows(self):
         users.insert().execute(
             {'user_id':7, 'user_name':'jack'},
@@ -294,7 +295,7 @@ class QueryTest(TestBase):
         r = users.select(limit=3, order_by=[users.c.user_id]).execute().fetchall()
         self.assert_(r == [(1, 'john'), (2, 'jack'), (3, 'ed')], repr(r))
 
-    @testing.unsupported('mssql')
+    @testing.unsupported('mssql', 'FIXME: guessing')
     @testing.fails_on('maxdb')
     def test_select_limit_offset(self):
         users.insert().execute(user_id=1, user_name='john')
@@ -309,7 +310,7 @@ class QueryTest(TestBase):
         r = users.select(offset=5, order_by=[users.c.user_id]).execute().fetchall()
         self.assert_(r==[(6, 'ralph'), (7, 'fido')])
 
-    @testing.exclude('mysql', '<', (5, 0, 37))
+    @testing.exclude('mysql', '<', (5, 0, 37), 'database bug')
     def test_scalar_select(self):
         """test that scalar subqueries with labels get their type propigated to the result set."""
         # mysql and/or mysqldb has a bug here, type isn't propagated for scalar
@@ -430,6 +431,7 @@ class QueryTest(TestBase):
             assert str(e) == "Ambiguous column name 'user_id' in result set! try 'use_labels' option on select statement." or \
                    str(e) == "Ambiguous column name 'USER_ID' in result set! try 'use_labels' option on select statement."
 
+    @testing.requires.subqueries
     def test_column_label_targeting(self):
         users.insert().execute(user_id=7, user_name='ed')
 
@@ -489,7 +491,9 @@ class QueryTest(TestBase):
         self.assertEqual([x.lower() for x in r.keys()], ['user_name', 'user_id'])
         self.assertEqual(r.values(), ['foo', 1])
 
-    @testing.unsupported('oracle', 'firebird', 'maxdb')
+    @testing.unsupported('oracle', 'FIXME: unknown, varify not fails_on()')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on()')
+    @testing.unsupported('maxdb', 'FIXME: unknown, verify not fails_on()')
     def test_column_accessor_shadow(self):
         meta = MetaData(testing.db)
         shadowed = Table('test_shadowed', meta,
@@ -617,6 +621,7 @@ class CompoundTest(TestBase):
     def _fetchall_sorted(self, executed):
         return sorted([tuple(row) for row in executed.fetchall()])
 
+    @testing.requires.subqueries
     def test_union(self):
         (s1, s2) = (
             select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
@@ -648,6 +653,7 @@ class CompoundTest(TestBase):
         self.assertEquals(u.execute().fetchall(), wanted)
 
     @testing.fails_on('maxdb')
+    @testing.requires.subqueries
     def test_union_ordered_alias(self):
         (s1, s2) = (
             select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
@@ -661,7 +667,9 @@ class CompoundTest(TestBase):
                   ('ccc', 'aaa')]
         self.assertEquals(u.alias('bar').select().execute().fetchall(), wanted)
 
-    @testing.unsupported('sqlite', 'mysql', 'oracle')
+    @testing.unsupported('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
+    @testing.fails_on('sqlite')
     def test_union_all(self):
         e = union_all(
             select([t1.c.col3]),
@@ -678,7 +686,9 @@ class CompoundTest(TestBase):
         found2 = self._fetchall_sorted(e.alias('foo').select().execute())
         self.assertEquals(found2, wanted)
 
-    @testing.unsupported('firebird', 'mysql', 'sybase')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('sybase', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
     def test_intersect(self):
         i = intersect(
             select([t2.c.col3, t2.c.col4]),
@@ -693,7 +703,10 @@ class CompoundTest(TestBase):
         found2 = self._fetchall_sorted(i.alias('bar').select().execute())
         self.assertEquals(found2, wanted)
 
-    @testing.unsupported('firebird', 'mysql', 'oracle', 'sybase')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('sybase', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
     def test_except_style1(self):
         e = except_(union(
             select([t1.c.col3, t1.c.col4]),
@@ -707,7 +720,10 @@ class CompoundTest(TestBase):
         found = self._fetchall_sorted(e.alias('bar').select().execute())
         self.assertEquals(found, wanted)
 
-    @testing.unsupported('firebird', 'mysql', 'oracle', 'sybase')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('sybase', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
     def test_except_style2(self):
         e = except_(union(
             select([t1.c.col3, t1.c.col4]),
@@ -724,7 +740,11 @@ class CompoundTest(TestBase):
         found2 = self._fetchall_sorted(e.alias('bar').select().execute())
         self.assertEquals(found2, wanted)
 
-    @testing.unsupported('firebird', 'mysql', 'oracle', 'sqlite', 'sybase')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.unsupported('sybase', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
+    @testing.fails_on('sqlite')
     def test_except_style3(self):
         # aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
         e = except_(
@@ -738,7 +758,8 @@ class CompoundTest(TestBase):
         self.assertEquals(e.alias('foo').select().execute().fetchall(),
                           [('ccc',)])
 
-    @testing.unsupported('firebird', 'mysql')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
     def test_composite(self):
         u = intersect(
             select([t2.c.col3, t2.c.col4]),
@@ -753,7 +774,8 @@ class CompoundTest(TestBase):
 
         self.assertEquals(found, wanted)
 
-    @testing.unsupported('firebird', 'mysql')
+    @testing.unsupported('firebird', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql')
     def test_composite_alias(self):
         ua = intersect(
             select([t2.c.col3, t2.c.col4]),
diff --git a/test/sql/quote.py b/test/sql/quote.py
index d137b44a3..2e5910c7b 100644
--- a/test/sql/quote.py
+++ b/test/sql/quote.py
@@ -84,7 +84,8 @@ class QuoteTest(TestBase, AssertsCompiledSQL):
             Column('ColumnOne', Integer, quote=False), quote=False, schema="FooBar", quote_schema=False)
         self.assert_compile(t1.select(), '''SELECT FooBar.TableOne.ColumnOne FROM FooBar.TableOne''')
         
-    @testing.unsupported('oracle')
+    @testing.unsupported('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.requires.subqueries
     def testlabels(self):
         """test the quoting of labels.
 
diff --git a/test/sql/testtypes.py b/test/sql/testtypes.py
index b3331ba59..d199890b6 100644
--- a/test/sql/testtypes.py
+++ b/test/sql/testtypes.py
@@ -381,13 +381,17 @@ class UnicodeTest(TestBase, AssertsExecutionResults):
             testing.db.engine.dialect.convert_unicode = prev_unicode
             testing.db.engine.dialect.convert_unicode = prev_assert
 
-    @testing.unsupported('oracle')
+    @testing.unsupported('oracle', 'FIXME: unknown, verify not fails_on')
     def testlength(self):
         """checks the database correctly understands the length of a unicode string"""
         teststr = u'aaa\x1234'
         self.assert_(testing.db.func.length(teststr).scalar() == len(teststr))
 
 class BinaryTest(TestBase, AssertsExecutionResults):
+    __excluded_on__ = (
+        ('mysql', '<', (4, 1, 1)),  # screwy varbinary types
+        )
+
     def setUpAll(self):
         global binary_table, MyPickleType
 
@@ -650,20 +654,18 @@ class DateTest(TestBase, AssertsExecutionResults):
             t.drop(checkfirst=True)
 
 class StringTest(TestBase, AssertsExecutionResults):
-    
     @testing.fails_on('mysql')
     def test_nolength_string(self):
-        # this tests what happens with String DDL with no length.
-        # seems like we need to decide amongst "VARCHAR" (sqlite, postgres), "TEXT" (mysql)
-        # i.e. theres some inconsisency here.
-        
+        # this tests what happens with String DDL with no length.  seems like
+        # we need to decide amongst "VARCHAR" (sqlite, postgres), "TEXT"
+        # (mysql) i.e. theres some inconsisency here.
+
         metadata = MetaData(testing.db)
-        foo =Table('foo', metadata,
-            Column('one', String))
-        
+        foo = Table('foo', metadata, Column('one', String))
+
         foo.create()
         foo.drop()
-        
+
 def _missing_decimal():
     """Python implementation supports decimals"""
     try:
diff --git a/test/sql/unicode.py b/test/sql/unicode.py
index 9e3ea257e..c5002aaff 100644
--- a/test/sql/unicode.py
+++ b/test/sql/unicode.py
@@ -8,7 +8,8 @@ from testlib.engines import utf8_engine
 from sqlalchemy.sql import column
 
 class UnicodeSchemaTest(TestBase):
-    @testing.unsupported('maxdb', 'oracle', 'sybase')
+    __requires__ = ('unicode_ddl',)
+
     def setUpAll(self):
         global unicode_bind, metadata, t1, t2, t3
 
@@ -55,20 +56,17 @@ class UnicodeSchemaTest(TestBase):
                        )
         metadata.create_all()
 
-    @testing.unsupported('maxdb', 'oracle', 'sybase')
     def tearDown(self):
         if metadata.tables:
             t3.delete().execute()
             t2.delete().execute()
             t1.delete().execute()
 
-    @testing.unsupported('maxdb', 'oracle', 'sybase')
     def tearDownAll(self):
         global unicode_bind
         metadata.drop_all()
         del unicode_bind
 
-    @testing.unsupported('maxdb', 'oracle', 'sybase')
     def test_insert(self):
         t1.insert().execute({u'méil':1, u'\u6e2c\u8a66':5})
         t2.insert().execute({'a':1, 'b':1})
@@ -81,7 +79,6 @@ class UnicodeSchemaTest(TestBase):
         assert t2.select().execute().fetchall() == [(1, 1)]
         assert t3.select().execute().fetchall() == [(1, 5, 1, 1)]
 
-    @testing.unsupported('maxdb', 'oracle', 'sybase')
     def test_reflect(self):
         t1.insert().execute({u'méil':2, u'\u6e2c\u8a66':7})
         t2.insert().execute({'a':2, 'b':2})