UNIQUE constraint support

Hello, I really miss support for UNIQUE constraint in SQLAlchemy. I've just added support for it, but it's tested on postgresql only. Test case failure is highly db-api dependent, I think; on UNIQUE constraint violation psycopg raises ProgrammingError, although it seems that pysqlite and mysqldb share a similar behaviour.

This patches engine.py, all the database files and the engine.py test.

Warning: this is untested on sqlite, mysql and oracle. I got the info from the docs but I never tested it; I guess this may lead to glitches on mysql where the constraints aren't available for all table types (I think?)

diff -ur sqlalchemy-20060217/lib/sqlalchemy/databases/mysql.py sqlalchemy-mymod/lib/sqlalchemy/databases/mysql.py
--- sqlalchemy-20060217/lib/sqlalchemy/databases/mysql.py   Fri Feb 17 21:22:43 2006
+++ sqlalchemy-mymod/lib/sqlalchemy/databases/mysql.py  Fri Feb 17 22:51:41 2006
@@ -266,6 +266,8 @@
                 colspec += " PRIMARY KEY"
             if first_pk and isinstance(column.type, types.Integer):
                 colspec += " AUTO_INCREMENT"
+        if column.unique:
+           colspec += " UNIQUE"
         if column.foreign_key:
             colspec += ", FOREIGN KEY (%s) REFERENCES %s(%s)" % (column.name, column.column.foreign_key.column.table.name, column.column.foreign_key.column.name) 
         return colspec
diff -ur sqlalchemy-20060217/lib/sqlalchemy/databases/oracle.py sqlalchemy-mymod/lib/sqlalchemy/databases/oracle.py
--- sqlalchemy-20060217/lib/sqlalchemy/databases/oracle.py  Fri Feb 17 21:22:43 2006
+++ sqlalchemy-mymod/lib/sqlalchemy/databases/oracle.py Fri Feb 17 22:48:53 2006
@@ -242,6 +242,8 @@
             colspec += " NOT NULL"
         if column.primary_key and not override_pk:
             colspec += " PRIMARY KEY"
+        if column.unique:
+             colspec += " UNIQUE"
         if column.foreign_key:
             colspec += " REFERENCES %s(%s)" % (column.column.foreign_key.column.table.name, column.column.foreign_key.column.name) 
         return colspec
diff -ur sqlalchemy-20060217/lib/sqlalchemy/databases/postgres.py sqlalchemy-mymod/lib/sqlalchemy/databases/postgres.py
--- sqlalchemy-20060217/lib/sqlalchemy/databases/postgres.py    Fri Feb 17 21:22:43 2006
+++ sqlalchemy-mymod/lib/sqlalchemy/databases/postgres.py   Fri Feb 17 21:32:51 2006
@@ -302,6 +302,8 @@
             colspec += " PRIMARY KEY"
         if column.foreign_key:
             colspec += " REFERENCES %s(%s)" % (column.column.foreign_key.column.table.fullname, column.column.foreign_key.column.name) 
+        if column.unique:
+             colspec += " UNIQUE"    
         return colspec

     def visit_sequence(self, sequence):
diff -ur sqlalchemy-20060217/lib/sqlalchemy/databases/sqlite.py sqlalchemy-mymod/lib/sqlalchemy/databases/sqlite.py
--- sqlalchemy-20060217/lib/sqlalchemy/databases/sqlite.py  Fri Feb 17 21:22:43 2006
+++ sqlalchemy-mymod/lib/sqlalchemy/databases/sqlite.py Fri Feb 17 22:45:54 2006
@@ -237,6 +237,8 @@
             colspec += " NOT NULL"
         if column.primary_key and not override_pk:
             colspec += " PRIMARY KEY"
+        if column.unique:
+             colspec += " UNIQUE"
         if column.foreign_key:
             colspec += " REFERENCES %s(%s)" % (column.foreign_key.column.table.name, column.foreign_key.column.name) 
         return colspec
diff -ur sqlalchemy-20060217/lib/sqlalchemy/schema.py sqlalchemy-mymod/lib/sqlalchemy/schema.py
--- sqlalchemy-20060217/lib/sqlalchemy/schema.py    Fri Feb 17 21:23:14 2006
+++ sqlalchemy-mymod/lib/sqlalchemy/schema.py   Fri Feb 17 21:35:29 2006
@@ -245,6 +245,7 @@
         self.nullable = kwargs.pop('nullable', not self.primary_key)
         self.hidden = kwargs.pop('hidden', False)
         self.default = kwargs.pop('default', None)
+        self.unique = kwargs.pop('unique', False)
         self.foreign_key = None
         self._orig = None
         if len(kwargs):
diff -ur sqlalchemy-20060217/test/engines.py sqlalchemy-mymod/test/engines.py
--- sqlalchemy-20060217/test/engines.py Fri Feb 17 21:21:55 2006
+++ sqlalchemy-mymod/test/engines.py    Fri Feb 17 22:42:29 2006
@@ -41,7 +41,7 @@
             Column('test4', DECIMAL, nullable = False),
             Column('test5', TIMESTAMP),
             Column('parent_user_id', Integer, ForeignKey('engine_users.user_id')),
-            Column('test6', DateTime, nullable = False),
+            Column('test6', DateTime, nullable = False,),
             Column('test7', String),
             Column('test8', Binary),
             Column('test_passivedefault', deftype, PassiveDefault(defval)),
@@ -165,7 +165,27 @@
             self.assert_(isinstance(table.c.col4.type, String))
         finally:
             table.drop()
-            
+        
+    def testunique(self):
+        table = Table(
+            'unique_test', testbase.db,
+            Column('id', Integer, primary_key=True),
+            Column('altid', Integer, nullable=False, unique=True),
+            Column('stringaltid', String(20), unique=True)
+        )
+        
+        try:
+           table.create()
+           table.insert().execute({"id":1, "altid":2, "stringaltid":"abc"})
+           table.insert().execute({"id":2, "altid":4, "stringaltid":"cde"})
+           self.assertRaises(testbase.db.dbapi().ProgrammingError, 
+                             table.insert(values=(3,2,"feg")).execute )
+           self.assertRaises(testbase.db.dbapi().ProgrammingError, 
+                             table.insert(values=(4,5,"cde")).execute )
+        finally:
+           table.drop()    
+           
+                   
 if __name__ == "__main__":
     testbase.main()

Comments (2)