From 81fd0c8bb9af8cd3df56c7343e64c0580046ba9f Mon Sep 17 00:00:00 2001
From: Paul Johnston <paj@pajhome.org.uk>
Date: Wed, 11 Jul 2007 17:58:45 +0000
Subject: [PATCH] mssql now able to reflect start and increment values for
 identity columns

---
 CHANGES                           |  1 +
 lib/sqlalchemy/databases/mssql.py | 21 +++++++++++++++------
 test/engine/reflection.py         | 17 +++++++++++++++++
 3 files changed, 33 insertions(+), 6 deletions(-)

diff --git a/CHANGES b/CHANGES
index b281a8fb6c..9fa41dfb2f 100644
--- a/CHANGES
+++ b/CHANGES
@@ -96,6 +96,7 @@
 
 - mssql
     - fix port option handling for pyodbc [ticket:634]
+    - now able to reflect start and increment values for identity columns
 
 - extensions
     - added selectone_by() to assignmapper
diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index b4ae8053fd..934a644017 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -32,9 +32,6 @@ Known issues / TODO:
 
 * No support for more than one ``IDENTITY`` column per table
 
-* No support for table reflection of ``IDENTITY`` columns with
-  (seed,increment) values other than (1,1)
-
 * No support for ``GUID`` type columns (yet)
 
 * pymssql has problems with binary and unicode data that this module
@@ -524,10 +521,8 @@ class MSSQLDialect(ansisql.ANSIDialect):
             raise exceptions.NoSuchTableError(table.name)
 
         # We also run an sp_columns to check for identity columns:
-        # FIXME: note that this only fetches the existence of an identity column, not it's properties like (seed, increment)
-        #        also, add a check to make sure we specify the schema name of the table
-        # cursor = table.engine.execute("sp_columns " + table.name, {})
         cursor = connection.execute("sp_columns " + table.name)
+        ic = None
         while True:
             row = cursor.fetchone()
             if row is None:
@@ -537,6 +532,20 @@ class MSSQLDialect(ansisql.ANSIDialect):
                 ic = table.c[col_name]
                 # setup a psuedo-sequence to represent the identity attribute - we interpret this at table.create() time as the identity attribute
                 ic.sequence = schema.Sequence(ic.name + '_identity')
+                # MSSQL: only one identity per table allowed
+                cursor.close()
+                break
+        if not ic is None:
+            try:
+                cursor = connection.execute("select ident_seed(?), ident_incr(?)", table.fullname, table.fullname)
+                row = cursor.fetchone()
+                cursor.close()
+                if not row is None:
+                    ic.sequence.start=int(row[0])
+                    ic.sequence.increment=int(row[1])
+            except:
+                # ignoring it, works just like before
+                pass
 
         # Add constraints
         RR = self.uppercase_table(ischema.ref_constraints)    #information_schema.referential_constraints
diff --git a/test/engine/reflection.py b/test/engine/reflection.py
index 344d5a94f5..26cbe3722a 100644
--- a/test/engine/reflection.py
+++ b/test/engine/reflection.py
@@ -435,6 +435,23 @@ class ReflectionTest(PersistTest):
         finally:
             table.drop()
 
+    @testbase.supported('mssql')
+    def testidentity(self):
+        meta = MetaData(testbase.db)
+        table = Table(
+            'identity_test', meta, 
+            Column('col1', Integer, Sequence('fred', 2, 3), primary_key=True)
+        )
+        table.create()
+        
+        meta2 = MetaData(testbase.db)
+        try:
+            table2 = Table('identity_test', meta2, autoload=True)
+            print table2.c['col1'].sequence
+        finally:
+            table.drop()
+
+
 class CreateDropTest(PersistTest):
     def setUpAll(self):
         global metadata, users
-- 
2.47.2