overhaul to schema, addition of ForeignKeyConstraint/

PrimaryKeyConstraint objects (also UniqueConstraint not
completed yet).  table creation and reflection modified
to be more oriented towards these new table-level objects.
reflection for sqlite/postgres/mysql supports composite
foreign keys; oracle/mssql/firebird not converted yet.

3 files changed, 85 insertions, 21 deletions

diff --git a/test/engine/reflection.py b/test/engine/reflection.py
index f9fa4e40c..ec59d652c 100644
--- a/test/engine/reflection.py
+++ b/test/engine/reflection.py
@@ -29,8 +29,10 @@ class ReflectionTest(PersistTest):
         else:
             deftype2 = Integer
             defval2 = "15"
-            
-        users = Table('engine_users', testbase.db,
+        
+        meta = BoundMetaData(testbase.db)
+        
+        users = Table('engine_users', meta,
             Column('user_id', INT, primary_key = True),
             Column('user_name', VARCHAR(20), nullable = False),
             Column('test1', CHAR(5), nullable = False),
@@ -49,14 +51,13 @@ class ReflectionTest(PersistTest):
             mysql_engine='InnoDB'
         )
 
-        addresses = Table('engine_email_addresses', testbase.db,
+        addresses = Table('engine_email_addresses', meta,
             Column('address_id', Integer, primary_key = True),
             Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),
             Column('email_address', String(20)),
             mysql_engine='InnoDB'
         )
 
-        
 #        users.c.parent_user_id.set_foreign_key(ForeignKey(users.c.user_id))
 
         users.create()
@@ -119,28 +120,69 @@ class ReflectionTest(PersistTest):
         table.insert().execute({'multi_id':3,'multi_rev':3,'name':'row3', 'val':'value3'})
         table.select().execute().fetchall()
         table.drop()
-    
+
+    def testcompositefk(self):
+        meta = BoundMetaData(testbase.db)
+        table = Table(
+            'multi', meta, 
+            Column('multi_id', Integer, primary_key=True),
+            Column('multi_rev', Integer, primary_key=True),
+            Column('name', String(50), nullable=False),
+            Column('val', String(100)),
+            mysql_engine='InnoDB'
+        )
+        table2 = Table('multi2', meta, 
+            Column('id', Integer, primary_key=True),
+            Column('foo', Integer),
+            Column('bar', Integer),
+            Column('data', String(50)),
+            ForeignKeyConstraint(['foo', 'bar'], ['multi.multi_id', 'multi.multi_rev']),
+            mysql_engine='InnoDB'
+        )
+        meta.create_all()
+        meta.clear()
+        
+        try:
+            table = Table('multi', meta, autoload=True)
+            table2 = Table('multi2', meta, autoload=True)
+            
+            print table
+            print table2
+            j = join(table, table2)
+            print str(j.onclause)
+            self.assert_(and_(table.c.multi_id==table2.c.foo, table.c.multi_rev==table2.c.bar).compare(j.onclause))
+
+        finally:
+            meta.drop_all()
+
     def testtoengine(self):
         meta = MetaData('md1')
         meta2 = MetaData('md2')
         
         table = Table('mytable', meta,
-            Column('myid', Integer, key = 'id'),
-            Column('name', String, key = 'name', nullable=False),
-            Column('description', String, key = 'description'),
+            Column('myid', Integer, primary_key=True),
+            Column('name', String, nullable=False),
+            Column('description', String(30)),
         )
         
-        print repr(table)
-        
-        table2 = table.tometadata(meta2)
+        table2 = Table('othertable', meta,
+            Column('id', Integer, primary_key=True),
+            Column('myid', Integer, ForeignKey('mytable.myid'))
+            )
+            
         
-        print repr(table2)
+        table_c = table.tometadata(meta2)
+        table2_c = table2.tometadata(meta2)
+
+        assert table is not table_c
+        assert table_c.c.myid.primary_key
+        assert not table_c.c.name.nullable 
+        assert table_c.c.description.nullable 
+        assert table.primary_key is not table_c.primary_key
+        assert [x.name for x in table.primary_key] == [x.name for x in table_c.primary_key]
+        assert table2_c.c.myid.foreign_key.column is table_c.c.myid
+        assert table2_c.c.myid.foreign_key.column is not table.c.myid
         
-        assert table is not table2
-        assert table2.c.id.nullable 
-        assert not table2.c.name.nullable 
-        assert table2.c.description.nullable 
-    
     # mysql throws its own exception for no such table, resulting in 
     # a sqlalchemy.SQLError instead of sqlalchemy.NoSuchTableError.
     # this could probably be fixed at some point.
diff --git a/test/orm/objectstore.py b/test/orm/objectstore.py
index 6d3a71276..237c4b554 100644
--- a/test/orm/objectstore.py
+++ b/test/orm/objectstore.py
@@ -230,7 +230,7 @@ class PKTest(SessionTest):
     @testbase.unsupported('mssql')
     def setUpAll(self):
         SessionTest.setUpAll(self)
-        db.echo = False
+        #db.echo = False
         global table
         global table2
         global table3
@@ -266,6 +266,8 @@ class PKTest(SessionTest):
         db.echo = testbase.echo
         SessionTest.tearDownAll(self)
         
+    # not support on sqlite since sqlite's auto-pk generation only works with
+    # single column primary keys    
     @testbase.unsupported('sqlite', 'mssql')
     def testprimarykey(self):
         class Entry(object):
@@ -279,6 +281,8 @@ class PKTest(SessionTest):
         ctx.current.clear()
         e2 = Entry.mapper.get((e.multi_id, 2))
         self.assert_(e is not e2 and e._instance_key == e2._instance_key)
+        
+    # this one works with sqlite since we are manually setting up pk values
     @testbase.unsupported('mssql')
     def testmanualpk(self):
         class Entry(object):
@@ -289,6 +293,7 @@ class PKTest(SessionTest):
         e.pk_col_2 = 'pk1_related'
         e.data = 'im the data'
         ctx.current.flush()
+        
     @testbase.unsupported('mssql')
     def testkeypks(self):
         import datetime
diff --git a/test/sql/indexes.py b/test/sql/indexes.py
index ec72beda3..e9af301de 100644
--- a/test/sql/indexes.py
+++ b/test/sql/indexes.py
@@ -5,16 +5,33 @@ import sys
 class IndexTest(testbase.AssertMixin):
     
     def setUp(self):
-	global metadata
-	metadata = BoundMetaData(testbase.db)
+        global metadata
+        metadata = BoundMetaData(testbase.db)
         self.echo = testbase.db.echo
         self.logger = testbase.db.logger
         
     def tearDown(self):
         testbase.db.echo = self.echo
         testbase.db.logger = testbase.db.engine.logger = self.logger
-	metadata.drop_all()
+        metadata.drop_all()
     
+    def test_constraint(self):
+        employees = Table('employees', metadata, 
+            Column('id', Integer),
+            Column('soc', String(40)),
+            Column('name', String(30)),
+            PrimaryKeyConstraint('id', 'soc')
+            )
+        elements = Table('elements', metadata,
+            Column('id', Integer),
+            Column('stuff', String(30)),
+            Column('emp_id', Integer),
+            Column('emp_soc', String(40)),
+            PrimaryKeyConstraint('id'),
+            ForeignKeyConstraint(['emp_id', 'emp_soc'], ['employees.id', 'employees.soc'])
+            )
+        metadata.create_all()
+        
     def test_index_create(self):
         employees = Table('employees', metadata,
                           Column('id', Integer, primary_key=True),