- a fair amount of cleanup to the schema package, removal of ambiguous

methods, methods that are no longer needed.  slightly more constrained
useage, greater emphasis on explicitness.
- table_iterator signature fixup, includes fix for [ticket:288]
- the "primary_key" attribute of Table and other selectables becomes
a setlike ColumnCollection object; is no longer ordered or numerically
indexed.  a comparison clause between two pks that are derived from the
same underlying tables (i.e. such as two Alias objects) can be generated
via table1.primary_key==table2.primary_key
- append_item() methods removed from Table and Column; preferably
construct Table/Column/related objects inline, but if needed use
append_column(), append_foreign_key(), append_constraint(), etc.
- table.create() no longer returns the Table object, instead has no
return value.  the usual case is that tables are created via metadata,
which is preferable since it will handle table dependencies.
- added UniqueConstraint (goes at Table level), CheckConstraint
(goes at Table or Column level) fixes [ticket:217]
- index=False/unique=True on Column now creates a UniqueConstraint,
index=True/unique=False creates a plain Index,
index=True/unique=True on Column creates a unique Index.  'index'
and 'unique' keyword arguments to column are now boolean only; for
explcit names and groupings of indexes or unique constraints, use the
UniqueConstraint/Index constructs explicitly.
- relationship of Metadata/Table/SchemaGenerator/Dropper has been
improved so that the schemavisitor receives the metadata object
for greater control over groupings of creates/drops.
- added "use_alter" argument to ForeignKey, ForeignKeyConstraint,
but it doesnt do anything yet.  will utilize new generator/dropper
behavior to implement.

9 files changed, 138 insertions, 102 deletions

diff --git a/test/engine/reflection.py b/test/engine/reflection.py
index 45010111e..469aab20e 100644
--- a/test/engine/reflection.py
+++ b/test/engine/reflection.py
@@ -59,8 +59,6 @@ class ReflectionTest(PersistTest):
             mysql_engine='InnoDB'
         )
 
-#        users.c.parent_user_id.set_foreign_key(ForeignKey(users.c.user_id))
-
         users.create()
         addresses.create()
 
@@ -154,6 +152,7 @@ class ReflectionTest(PersistTest):
                 autoload=True)
             u2 = Table('users', meta2, autoload=True)
 
+            print "ITS", list(a2.primary_key)
             assert list(a2.primary_key) == [a2.c.id]
             assert list(u2.primary_key) == [u2.c.id]
             assert u2.join(a2).onclause == u2.c.id==a2.c.id
@@ -226,19 +225,19 @@ class ReflectionTest(PersistTest):
             
     def testmultipk(self):
         """test that creating a table checks for a sequence before creating it"""
+        meta = BoundMetaData(testbase.db)
         table = Table(
-            'engine_multi', testbase.db, 
+            'engine_multi', meta, 
             Column('multi_id', Integer, Sequence('multi_id_seq'), primary_key=True),
             Column('multi_rev', Integer, Sequence('multi_rev_seq'), primary_key=True),
             Column('name', String(50), nullable=False),
             Column('val', String(100))
         )
         table.create()
-        # clear out table registry
-        table.deregister()
 
+        meta2 = BoundMetaData(testbase.db)
         try:
-            table = Table('engine_multi', testbase.db, autoload=True)
+            table = Table('engine_multi', meta2, autoload=True)
         finally:
             table.drop()
         
@@ -348,19 +347,20 @@ class ReflectionTest(PersistTest):
                           testbase.db, autoload=True)
         
     def testoverride(self):
+        meta = BoundMetaData(testbase.db)
         table = Table(
-            'override_test', testbase.db, 
+            'override_test', meta, 
             Column('col1', Integer, primary_key=True),
             Column('col2', String(20)),
             Column('col3', Numeric)
         )
         table.create()
         # clear out table registry
-        table.deregister()
 
+        meta2 = BoundMetaData(testbase.db)
         try:
             table = Table(
-                'override_test', testbase.db,
+                'override_test', meta2,
                 Column('col2', Unicode()),
                 Column('col4', String(30)), autoload=True)
         
@@ -403,22 +403,22 @@ class CreateDropTest(PersistTest):
         )
 
     def test_sorter( self ):
-        tables = metadata._sort_tables(metadata.tables.values())
+        tables = metadata.table_iterator(reverse=False)
         table_names = [t.name for t in tables]
         self.assert_( table_names == ['users', 'orders', 'items', 'email_addresses'] or table_names ==  ['users', 'email_addresses', 'orders', 'items'])
 
 
     def test_createdrop(self):
-        metadata.create_all(engine=testbase.db)
+        metadata.create_all(connectable=testbase.db)
         self.assertEqual( testbase.db.has_table('items'), True )
         self.assertEqual( testbase.db.has_table('email_addresses'), True )        
-        metadata.create_all(engine=testbase.db)
+        metadata.create_all(connectable=testbase.db)
         self.assertEqual( testbase.db.has_table('items'), True )        
 
-        metadata.drop_all(engine=testbase.db)
+        metadata.drop_all(connectable=testbase.db)
         self.assertEqual( testbase.db.has_table('items'), False )
         self.assertEqual( testbase.db.has_table('email_addresses'), False )                
-        metadata.drop_all(engine=testbase.db)
+        metadata.drop_all(connectable=testbase.db)
         self.assertEqual( testbase.db.has_table('items'), False )                
 
 class SchemaTest(PersistTest):
@@ -438,7 +438,7 @@ class SchemaTest(PersistTest):
         buf = StringIO.StringIO()
         def foo(s, p):
             buf.write(s)
-        gen = testbase.db.dialect.schemagenerator(testbase.db.engine, foo)
+        gen = testbase.db.dialect.schemagenerator(testbase.db.engine, foo, None)
         table1.accept_schema_visitor(gen)
         table2.accept_schema_visitor(gen)
         buf = buf.getvalue()
diff --git a/test/orm/cycles.py b/test/orm/cycles.py
index 63eb5b0f6..eebe7af75 100644
--- a/test/orm/cycles.py
+++ b/test/orm/cycles.py
@@ -109,7 +109,7 @@ class BiDirectionalOneToManyTest(AssertMixin):
             Column('c2', Integer)
         )
         metadata.create_all()
-        t2.c.c2.append_item(ForeignKey('t1.c1'))
+        t2.c.c2.append_foreign_key(ForeignKey('t1.c1'))
     def tearDownAll(self):
         t1.drop()
         t2.drop()
@@ -153,7 +153,7 @@ class BiDirectionalOneToManyTest2(AssertMixin):
         )
         t2.create()
         t1.create()
-        t2.c.c2.append_item(ForeignKey('t1.c1'))
+        t2.c.c2.append_foreign_key(ForeignKey('t1.c1'))
         t3 = Table('t1_data', metadata, 
             Column('c1', Integer, primary_key=True),
             Column('t1id', Integer, ForeignKey('t1.c1')),
@@ -225,8 +225,7 @@ class OneToManyManyToOneTest(AssertMixin):
 
         ball.create()
         person.create()
-#        person.c.favorite_ball_id.append_item(ForeignKey('ball.id'))
-        ball.c.person_id.append_item(ForeignKey('person.id'))
+        ball.c.person_id.append_foreign_key(ForeignKey('person.id'))
         
         # make the test more complete for postgres
         if db.engine.__module__.endswith('postgres'):
diff --git a/test/orm/inheritance.py b/test/orm/inheritance.py
index ce9a35479..392e54407 100644
--- a/test/orm/inheritance.py
+++ b/test/orm/inheritance.py
@@ -96,16 +96,16 @@ class InheritTest2(testbase.AssertMixin):
         foo = Table('foo', metadata,
             Column('id', Integer, Sequence('foo_id_seq'), primary_key=True),
             Column('data', String(20)),
-            ).create()
+            )
 
         bar = Table('bar', metadata,
             Column('bid', Integer, ForeignKey('foo.id'), primary_key=True),
             #Column('fid', Integer, ForeignKey('foo.id'), )
-            ).create()
+            )
 
         foo_bar = Table('foo_bar', metadata,
             Column('foo_id', Integer, ForeignKey('foo.id')),
-            Column('bar_id', Integer, ForeignKey('bar.bid'))).create()
+            Column('bar_id', Integer, ForeignKey('bar.bid')))
         metadata.create_all()
     def tearDownAll(self):
         metadata.drop_all()
diff --git a/test/orm/manytomany.py b/test/orm/manytomany.py
index 3966041b5..dc343cb95 100644
--- a/test/orm/manytomany.py
+++ b/test/orm/manytomany.py
@@ -28,7 +28,7 @@ class Transition(object):
         
 class M2MTest(testbase.AssertMixin):
     def setUpAll(self):
-        self.install_threadlocal()
+        global metadata
         metadata = testbase.metadata
         global place
         place = Table('place', metadata,
@@ -68,28 +68,14 @@ class M2MTest(testbase.AssertMixin):
             Column('pl1_id', Integer, ForeignKey('place.place_id')),
             Column('pl2_id', Integer, ForeignKey('place.place_id')),
             )
-
-        place.create()
-        transition.create()
-        place_input.create()
-        place_output.create()
-        place_thingy.create()
-        place_place.create()
+        metadata.create_all()
 
     def tearDownAll(self):
-        place_place.drop()
-        place_input.drop()
-        place_output.drop()
-        place_thingy.drop()
-        place.drop()
-        transition.drop()
-        objectstore.clear()
+        metadata.drop_all()
         clear_mappers()
         #testbase.db.tables.clear()
-        self.uninstall_threadlocal()
         
     def setUp(self):
-        objectstore.clear()
         clear_mappers()
 
     def tearDown(self):
@@ -111,6 +97,7 @@ class M2MTest(testbase.AssertMixin):
             lazy=True,
             ))
 
+        sess = create_session()
         p1 = Place('place1')
         p2 = Place('place2')
         p3 = Place('place3')
@@ -118,7 +105,7 @@ class M2MTest(testbase.AssertMixin):
         p5 = Place('place5')
         p6 = Place('place6')
         p7 = Place('place7')
-
+        [sess.save(x) for x in [p1,p2,p3,p4,p5,p6,p7]]
         p1.places.append(p2)
         p1.places.append(p3)
         p5.places.append(p6)
@@ -127,10 +114,10 @@ class M2MTest(testbase.AssertMixin):
         p1.places.append(p5)
         p4.places.append(p3)
         p3.places.append(p4)
-        objectstore.flush()
+        sess.flush()
 
-        objectstore.clear()
-        l = Place.mapper.select(order_by=place.c.place_id)
+        sess.clear()
+        l = sess.query(Place).select(order_by=place.c.place_id)
         (p1, p2, p3, p4, p5, p6, p7) = l
         assert p1.places == [p2,p3,p5]
         assert p5.places == [p6]
@@ -144,8 +131,8 @@ class M2MTest(testbase.AssertMixin):
             pp = p.places
             self.echo("Place " + str(p) +" places " + repr(pp))
 
-        [objectstore.delete(p) for p in p1,p2,p3,p4,p5,p6,p7]
-        objectstore.flush()
+        [sess.delete(p) for p in p1,p2,p3,p4,p5,p6,p7]
+        sess.flush()
 
     def testdouble(self):
         """tests that a mapper can have two eager relations to the same table, via
@@ -165,10 +152,12 @@ class M2MTest(testbase.AssertMixin):
         tran.inputs.append(Place('place1'))
         tran.outputs.append(Place('place2'))
         tran.outputs.append(Place('place3'))
-        objectstore.flush()
+        sess = create_session()
+        sess.save(tran)
+        sess.flush()
 
-        objectstore.clear()
-        r = Transition.mapper.select()
+        sess.clear()
+        r = sess.query(Transition).select()
         self.assert_result(r, Transition, 
             {'name':'transition1', 
             'inputs' : (Place, [{'name':'place1'}]),
@@ -199,15 +188,15 @@ class M2MTest(testbase.AssertMixin):
         p2.inputs.append(t2)
         p3.inputs.append(t2)
         p1.outputs.append(t1)
-        
-        objectstore.flush()
+        sess = create_session()
+        [sess.save(x) for x in [t1,t2,t3,p1,p2,p3]]
+        sess.flush()
         
         self.assert_result([t1], Transition, {'outputs': (Place, [{'name':'place3'}, {'name':'place1'}])})
         self.assert_result([p2], Place, {'inputs': (Transition, [{'name':'transition1'},{'name':'transition2'}])})
 
 class M2MTest2(testbase.AssertMixin):        
     def setUpAll(self):
-        self.install_threadlocal()
         metadata = testbase.metadata
         global studentTbl
         studentTbl = Table('student', metadata, Column('name', String(20), primary_key=True))
@@ -217,22 +206,13 @@ class M2MTest2(testbase.AssertMixin):
         enrolTbl = Table('enrol', metadata,
             Column('student_id', String(20), ForeignKey('student.name'),primary_key=True),
             Column('course_id', String(20), ForeignKey('course.name'), primary_key=True))
-
-        studentTbl.create()
-        courseTbl.create()
-        enrolTbl.create()
+        metadata.create_all()
 
     def tearDownAll(self):
-        enrolTbl.drop()
-        studentTbl.drop()
-        courseTbl.drop()
-        objectstore.clear()
+        metadata.drop_all()
         clear_mappers()
-        #testbase.db.tables.clear()
-        self.uninstall_threadlocal()
         
     def setUp(self):
-        objectstore.clear()
         clear_mappers()
 
     def tearDown(self):
@@ -251,6 +231,7 @@ class M2MTest2(testbase.AssertMixin):
         Course.mapper = mapper(Course, courseTbl, properties = {
             'students': relation(Student.mapper, enrolTbl, lazy=True, backref='courses')
         })
+        sess = create_session()
         s1 = Student('Student1')
         c1 = Course('Course1')
         c2 = Course('Course2')
@@ -260,55 +241,53 @@ class M2MTest2(testbase.AssertMixin):
         c3.students.append(s1)
         self.assert_(len(s1.courses) == 3)
         self.assert_(len(c1.students) == 1)
-        objectstore.flush()
-        objectstore.clear()
-        s = Student.mapper.get_by(name='Student1')
-        c = Course.mapper.get_by(name='Course3')
+        sess.save(s1)
+        sess.flush()
+        sess.clear()
+        s = sess.query(Student).get_by(name='Student1')
+        c = sess.query(Course).get_by(name='Course3')
         self.assert_(len(s.courses) == 3)
         del s.courses[1]
         self.assert_(len(s.courses) == 2)
         
 class M2MTest3(testbase.AssertMixin):    
     def setUpAll(self):
-        self.install_threadlocal()
         metadata = testbase.metadata
         global c, c2a1, c2a2, b, a
         c = Table('c', metadata, 
             Column('c1', Integer, primary_key = True),
             Column('c2', String(20)),
-        ).create()
+        )
 
         a = Table('a', metadata, 
             Column('a1', Integer, primary_key=True),
             Column('a2', String(20)),
             Column('c1', Integer, ForeignKey('c.c1'))
-            ).create()
+            )
 
         c2a1 = Table('ctoaone', metadata, 
             Column('c1', Integer, ForeignKey('c.c1')),
             Column('a1', Integer, ForeignKey('a.a1'))
-        ).create()
+        )
         c2a2 = Table('ctoatwo', metadata, 
             Column('c1', Integer, ForeignKey('c.c1')),
             Column('a1', Integer, ForeignKey('a.a1'))
-        ).create()
+        )
 
         b = Table('b', metadata, 
             Column('b1', Integer, primary_key=True),
             Column('a1', Integer, ForeignKey('a.a1')),
             Column('b2', Boolean)
-        ).create()
-
+        )
+        metadata.create_all()
+        
     def tearDownAll(self):
         b.drop()
         c2a2.drop()
         c2a1.drop()
         a.drop()
         c.drop()
-        objectstore.clear()
         clear_mappers()
-        #testbase.db.tables.clear()
-        self.uninstall_threadlocal()
         
     def testbasic(self):
         class C(object):pass
diff --git a/test/orm/unitofwork.py b/test/orm/unitofwork.py
index 63d090428..6cf2b4b49 100644
--- a/test/orm/unitofwork.py
+++ b/test/orm/unitofwork.py
@@ -91,7 +91,8 @@ class VersioningTest(UnitOfWorkTest):
         Column('id', Integer, Sequence('version_test_seq'), primary_key=True ),
         Column('version_id', Integer, nullable=False),
         Column('value', String(40), nullable=False)
-        ).create()
+        )
+        version_table.create()
     def tearDownAll(self):
         version_table.drop()
         UnitOfWorkTest.tearDownAll(self)
@@ -408,12 +409,14 @@ class PrivateAttrTest(UnitOfWorkTest):
         a_table = Table('a',testbase.db,
             Column('a_id', Integer, Sequence('next_a_id'), primary_key=True),
             Column('data', String(10)),
-            ).create()
+            )
     
         b_table = Table('b',testbase.db,
             Column('b_id',Integer,Sequence('next_b_id'),primary_key=True),
             Column('a_id',Integer,ForeignKey('a.a_id')),
-            Column('data',String(10))).create()
+            Column('data',String(10)))
+        a_table.create()
+        b_table.create()
     def tearDownAll(self):
         b_table.drop()
         a_table.drop()
diff --git a/test/sql/alltests.py b/test/sql/alltests.py
index 29b638bb8..c79d7b67e 100644
--- a/test/sql/alltests.py
+++ b/test/sql/alltests.py
@@ -5,7 +5,7 @@ import unittest
 def suite():
     modules_to_test = (
         'sql.testtypes',
-        'sql.indexes',
+        'sql.constraints',
 
         # SQL syntax
         'sql.select',
diff --git a/test/sql/indexes.py b/test/sql/constraints.py
index 5c46b63f2..045d44968 100644
--- a/test/sql/indexes.py
+++ b/test/sql/constraints.py
@@ -2,7 +2,7 @@ import testbase
 from sqlalchemy import *
 import sys
 
-class IndexTest(testbase.AssertMixin):
+class ConstraintTest(testbase.AssertMixin):
     
     def setUp(self):
         global metadata
@@ -27,6 +27,59 @@ class IndexTest(testbase.AssertMixin):
             ForeignKeyConstraint(['emp_id', 'emp_soc'], ['employees.id', 'employees.soc'])
             )
         metadata.create_all()
+
+    @testbase.unsupported('sqlite', 'mysql')
+    def test_check_constraint(self):
+        foo = Table('foo', metadata, 
+            Column('id', Integer, primary_key=True),
+            Column('x', Integer),
+            Column('y', Integer),
+            CheckConstraint('x>y'))
+        bar = Table('bar', metadata, 
+            Column('id', Integer, primary_key=True),
+            Column('x', Integer, CheckConstraint('x>7')),
+            )
+
+        metadata.create_all()
+        foo.insert().execute(id=1,x=9,y=5)
+        try:
+            foo.insert().execute(id=2,x=5,y=9)
+            assert False
+        except exceptions.SQLError:
+            assert True
+
+        bar.insert().execute(id=1,x=10)
+        try:
+            bar.insert().execute(id=2,x=5)
+            assert False
+        except exceptions.SQLError:
+            assert True
+    
+    def test_unique_constraint(self):
+        foo = Table('foo', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('value', String(30), unique=True))
+        bar = Table('bar', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('value', String(30)),
+            Column('value2', String(30)),
+            UniqueConstraint('value', 'value2', name='uix1')
+            )
+        metadata.create_all()
+        foo.insert().execute(id=1, value='value1')
+        foo.insert().execute(id=2, value='value2')
+        bar.insert().execute(id=1, value='a', value2='a')
+        bar.insert().execute(id=2, value='a', value2='b')
+        try:
+            foo.insert().execute(id=3, value='value1')
+            assert False
+        except exceptions.SQLError:
+            assert True
+        try:
+            bar.insert().execute(id=3, value='a', value2='b')
+            assert False
+        except exceptions.SQLError:
+            assert True
         
     def test_index_create(self):
         employees = Table('employees', metadata,
@@ -39,12 +92,12 @@ class IndexTest(testbase.AssertMixin):
         i = Index('employee_name_index',
                   employees.c.last_name, employees.c.first_name)
         i.create()
-        assert employees.indexes['employee_name_index'] is i
+        assert i in employees.indexes
         
         i2 = Index('employee_email_index',
                    employees.c.email_address, unique=True)        
         i2.create()
-        assert employees.indexes['employee_email_index'] is i2
+        assert i2 in employees.indexes
 
     def test_index_create_camelcase(self):
         """test that mixed-case index identifiers are legal"""
@@ -76,16 +129,17 @@ class IndexTest(testbase.AssertMixin):
 
         events = Table('events', metadata,
                        Column('id', Integer, primary_key=True),
-                       Column('name', String(30), unique=True),
+                       Column('name', String(30), index=True, unique=True),
                        Column('location', String(30), index=True),
-                       Column('sport', String(30),
-                              unique='sport_announcer'),
-                       Column('announcer', String(30),
-                              unique='sport_announcer'),
-                       Column('winner', String(30), index='idx_winners'))
+                       Column('sport', String(30)),
+                       Column('announcer', String(30)),
+                       Column('winner', String(30)))
+
+        Index('sport_announcer', events.c.sport, events.c.announcer, unique=True)
+        Index('idx_winners', events.c.winner)
         
         index_names = [ ix.name for ix in events.indexes ]
-        assert 'ux_events_name' in index_names
+        assert 'ix_events_name' in index_names
         assert 'ix_events_location' in index_names
         assert 'sport_announcer' in index_names
         assert 'idx_winners' in index_names
@@ -97,19 +151,20 @@ class IndexTest(testbase.AssertMixin):
             capt.append(statement)
             capt.append(repr(parameters))
             connection.proxy(statement, parameters)
-        schemagen = testbase.db.dialect.schemagenerator(testbase.db, proxy)
+        schemagen = testbase.db.dialect.schemagenerator(testbase.db, proxy, connection)
         events.accept_schema_visitor(schemagen)
         
         assert capt[0].strip().startswith('CREATE TABLE events')
-        assert capt[2].strip() == \
-            'CREATE UNIQUE INDEX ux_events_name ON events (name)'
-        assert capt[4].strip() == \
-            'CREATE INDEX ix_events_location ON events (location)'
-        assert capt[6].strip() == \
-            'CREATE UNIQUE INDEX sport_announcer ON events (sport, announcer)'
-        assert capt[8].strip() == \
+        
+        s = set([capt[x].strip() for x in [2,4,6,8]])
+        
+        assert s == set([
+            'CREATE UNIQUE INDEX ix_events_name ON events (name)',
+            'CREATE INDEX ix_events_location ON events (location)',
+            'CREATE UNIQUE INDEX sport_announcer ON events (sport, announcer)',
             'CREATE INDEX idx_winners ON events (winner)'
-
+            ])
+            
         # verify that the table is functional
         events.insert().execute(id=1, name='hockey finals', location='rink',
                                 sport='hockey', announcer='some canadian',
diff --git a/test/sql/testtypes.py b/test/sql/testtypes.py
index e08bdb89f..ef851cf63 100644
--- a/test/sql/testtypes.py
+++ b/test/sql/testtypes.py
@@ -121,7 +121,7 @@ class ColumnsTest(AssertMixin):
         )
 
         for aCol in testTable.c:
-            self.assertEquals(expectedResults[aCol.name], db.dialect.schemagenerator(db, None).get_column_specification(aCol))
+            self.assertEquals(expectedResults[aCol.name], db.dialect.schemagenerator(db, None, None).get_column_specification(aCol))
         
 class UnicodeTest(AssertMixin):
     """tests the Unicode type.  also tests the TypeDecorator with instances in the types package."""
diff --git a/test/zblog/tests.py b/test/zblog/tests.py
index 7cec19590..e538cff9d 100644
--- a/test/zblog/tests.py
+++ b/test/zblog/tests.py
@@ -12,9 +12,9 @@ from zblog.blog import *
 class ZBlogTest(AssertMixin):
 
     def create_tables(self):
-        tables.metadata.create_all(engine=db)
+        tables.metadata.create_all(connectable=db)
     def drop_tables(self):
-        tables.metadata.drop_all(engine=db)
+        tables.metadata.drop_all(connectable=db)
         
     def setUpAll(self):
         self.create_tables()