Multiple MSSQL fixes; see ticket #581

1 files changed, 65 insertions, 22 deletions

diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index 1cadbd14d..4336296dd 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -99,14 +99,14 @@ class MSDateTime(sqltypes.DateTime):
     def get_col_spec(self):
         return "DATETIME"
 
-    def convert_bind_param(self, value, dialect):
-        if hasattr(value, "isoformat"):
-            #return value.isoformat(' ')
-            # isoformat() bings on apodbapi -- reported/suggested by Peter Buschman
-            return value.strftime('%Y-%m-%d %H:%M:%S')
-        else:
-            return value
+class MSDate(sqltypes.Date):
+    def __init__(self, *a, **kw):
+        super(MSDate, self).__init__(False)
 
+    def get_col_spec(self):
+        return "SMALLDATETIME"
+
+class MSDateTime_adodbapi(MSDateTime):
     def convert_result_value(self, value, dialect):
         # adodbapi will return datetimes with empty time values as datetime.date() objects.
         # Promote them back to full datetime.datetime()
@@ -114,23 +114,34 @@ class MSDateTime(sqltypes.DateTime):
             return datetime.datetime(value.year, value.month, value.day)
         return value
 
-class MSDate(sqltypes.Date):
-    def __init__(self, *a, **kw):
-        super(MSDate, self).__init__(False)
+class MSDateTime_pyodbc(MSDateTime):
+    def convert_bind_param(self, value, dialect):
+        if value and not hasattr(value, 'second'):
+            return datetime.datetime(value.year, value.month, value.day)
+        else:
+            return value
 
-    def get_col_spec(self):
-        return "SMALLDATETIME"
-    
+class MSDate_pyodbc(MSDate):
     def convert_bind_param(self, value, dialect):
-        if value and hasattr(value, "isoformat"):
-            return value.strftime('%Y-%m-%d %H:%M')
-        return value
+        if value and not hasattr(value, 'second'):
+            return datetime.datetime(value.year, value.month, value.day)
+        else:
+            return value
 
     def convert_result_value(self, value, dialect):
+        # pyodbc returns SMALLDATETIME values as datetime.datetime(). truncate it back to datetime.date()
+        if value and hasattr(value, 'second'):
+            return value.date()
+        else:
+            return value
+
+class MSDate_pymssql(MSDate):
+    def convert_result_value(self, value, dialect):
         # pymssql will return SMALLDATETIME values as datetime.datetime(), truncate it back to datetime.date()
         if value and hasattr(value, 'second'):
             return value.date()
-        return value
+        else:
+            return value
 
 class MSText(sqltypes.TEXT):
     def get_col_spec(self):
@@ -143,7 +154,7 @@ class MSString(sqltypes.String):
     def get_col_spec(self):
         return "VARCHAR(%(length)s)" % {'length' : self.length}
 
-class MSNVarchar(MSString):
+class MSNVarchar(sqltypes.Unicode):
     def get_col_spec(self):
         if self.length:
             return "NVARCHAR(%(length)s)" % {'length' : self.length}
@@ -191,6 +202,10 @@ class MSBoolean(sqltypes.Boolean):
         else:
             return value and True or False
         
+class MSTimeStamp(sqltypes.TIMESTAMP):
+    def get_col_spec(self):
+        return "TIMESTAMP"
+        
 def descriptor():
     return {'name':'mssql',
     'description':'MSSQL',
@@ -240,7 +255,7 @@ class MSSQLExecutionContext(default.DefaultExecutionContext):
 
             if self.IINSERT:
                 # TODO: quoting rules for table name here ?
-                self.cursor.execute("SET IDENTITY_INSERT %s ON" % self.compiled.statement.table.name)
+                self.cursor.execute("SET IDENTITY_INSERT %s ON" % self.compiled.statement.table.fullname)
 
         super(MSSQLExecutionContext, self).pre_exec()
 
@@ -253,7 +268,7 @@ class MSSQLExecutionContext(default.DefaultExecutionContext):
         if self.compiled.isinsert:
             if self.IINSERT:
                 # TODO: quoting rules for table name here ?
-                self.cursor.execute("SET IDENTITY_INSERT %s OFF" % self.compiled.statement.table.name)
+                self.cursor.execute("SET IDENTITY_INSERT %s OFF" % self.compiled.statement.table.fullname)
                 self.IINSERT = False
             elif self.HASIDENT:
                 if self.dialect.use_scope_identity:
@@ -294,6 +309,7 @@ class MSSQLDialect(ansisql.ANSIDialect):
         sqltypes.TEXT : MSText,
         sqltypes.CHAR: MSChar,
         sqltypes.NCHAR: MSNChar,
+        sqltypes.TIMESTAMP: MSTimeStamp,
     }
 
     ischema_names = {
@@ -314,7 +330,8 @@ class MSSQLDialect(ansisql.ANSIDialect):
         'binary' : MSBinary,
         'bit': MSBoolean,
         'real' : MSFloat,
-        'image' : MSBinary
+        'image' : MSBinary,
+        'timestamp': MSTimeStamp,
     }
 
     def __new__(cls, dbapi=None, *args, **kwargs):
@@ -330,7 +347,7 @@ class MSSQLDialect(ansisql.ANSIDialect):
         super(MSSQLDialect, self).__init__(**params)
         self.auto_identity_insert = auto_identity_insert
         self.text_as_varchar = False
-        self.use_scope_identity = True
+        self.use_scope_identity = False
         self.set_default_schema_name("dbo")
 
     def dbapi(cls, module_name=None):
@@ -570,6 +587,16 @@ class MSSQLDialect_pymssql(MSSQLDialect):
         return module
     import_dbapi = classmethod(import_dbapi)
     
+    colspecs = MSSQLDialect.colspecs.copy()
+    colspecs[sqltypes.Date] = MSDate_pymssql
+
+    ischema_names = MSSQLDialect.ischema_names.copy()
+    ischema_names['smalldatetime'] = MSDate_pymssql
+
+    def __init__(self, **params):
+        super(MSSQLDialect_pymssql, self).__init__(**params)
+        self.use_scope_identity = True
+
     def supports_sane_rowcount(self):
         return True
 
@@ -641,12 +668,21 @@ class MSSQLDialect_pyodbc(MSSQLDialect):
     
     colspecs = MSSQLDialect.colspecs.copy()
     colspecs[sqltypes.Unicode] = AdoMSNVarchar
+    colspecs[sqltypes.Date] = MSDate_pyodbc
+    colspecs[sqltypes.DateTime] = MSDateTime_pyodbc
+
     ischema_names = MSSQLDialect.ischema_names.copy()
     ischema_names['nvarchar'] = AdoMSNVarchar
+    ischema_names['smalldatetime'] = MSDate_pyodbc
+    ischema_names['datetime'] = MSDateTime_pyodbc
 
     def supports_sane_rowcount(self):
         return False
 
+    def supports_unicode_statements(self):
+        """indicate whether the DBAPI can receive SQL statements as Python unicode strings"""
+        return True
+
     def make_connect_string(self, keys):
         connectors = ["Driver={SQL Server}"]
         connectors.append("Server=%s" % keys.get("host"))
@@ -674,12 +710,19 @@ class MSSQLDialect_adodbapi(MSSQLDialect):
 
     colspecs = MSSQLDialect.colspecs.copy()
     colspecs[sqltypes.Unicode] = AdoMSNVarchar
+    colspecs[sqltypes.DateTime] = MSDateTime_adodbapi
+
     ischema_names = MSSQLDialect.ischema_names.copy()
     ischema_names['nvarchar'] = AdoMSNVarchar
+    ischema_names['datetime'] = MSDateTime_adodbapi
 
     def supports_sane_rowcount(self):
         return True
 
+    def supports_unicode_statements(self):
+        """indicate whether the DBAPI can receive SQL statements as Python unicode strings"""
+        return True
+
     def make_connect_string(self, keys):
         connectors = ["Provider=SQLOLEDB"]
         if 'port' in keys: