- 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.

3 files changed, 76 insertions, 21 deletions

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."""