- Modified SQLite's representation of "microseconds" to

match the output of str(somedatetime), i.e. in that the
microseconds are represented as fractional seconds in
string format.  [ticket:1090]
- implemented a __legacy_microseconds__ flag on DateTimeMixin which can
be used per-class or per-type instances to get the old behavior, for
compatibility with existing SQLite databases encoded by a previous
version of SQLAlchemy.
- will implement the reverse legacy behavior in 0.4.

3 files changed, 50 insertions, 5 deletions

diff --git a/CHANGES b/CHANGES
index 1327c636d..50b9fb418 100644
--- a/CHANGES
+++ b/CHANGES
@@ -25,7 +25,31 @@ CHANGES
       query.join(cls, aliased=True).  [ticket:1082]
 
     - Repaired `__str__()` method on Query. [ticket:1066]
-    
+
+- sqlite
+    - Modified SQLite's representation of "microseconds" to 
+      match the output of str(somedatetime), i.e. in that the
+      microseconds are represented as fractional seconds in
+      string format.  This makes SQLA's SQLite date type
+      compatible with datetimes that were saved directly
+      using Pysqlite (which just calls str()).  
+      Note that this is incompatible with the existing microseconds
+      values in a SQLA 0.4 generated SQLite database file.
+      
+      To get the old behavior globally:
+      
+           from sqlalchemy.databases.sqlite import DateTimeMixin
+           DateTimeMixin.__legacy_microseconds__ = True
+           
+      To get the behavior on individual DateTime types:
+      
+            t = sqlite.SLDateTime()
+            t.__legacy_microseconds__ = True
+            
+      Then use "t" as the type on the Column.
+           
+      [ticket:1090]
+      
 0.5beta1
 ========
     - An ongoing document describing the changes from 0.4 to 0.5
diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py
index 1b85fb016..4f1fa9da5 100644
--- a/lib/sqlalchemy/databases/sqlite.py
+++ b/lib/sqlalchemy/databases/sqlite.py
@@ -42,7 +42,8 @@ class SLSmallInteger(sqltypes.Smallinteger):
 
 class DateTimeMixin(object):
     __format__ = "%Y-%m-%d %H:%M:%S"
-
+    __legacy_microseconds__ = False
+    
     def bind_processor(self, dialect):
         def process(value):
             if isinstance(value, basestring):
@@ -50,7 +51,10 @@ class DateTimeMixin(object):
                 return value
             elif value is not None:
                 if self.__microsecond__ and getattr(value, 'microsecond', None) is not None:
-                    return value.strftime(self.__format__ + "." + str(value.microsecond))
+                    if self.__legacy_microseconds__:
+                        return value.strftime(self.__format__ + '.' + str(value.microsecond))
+                    else:
+                        return value.strftime(self.__format__ + ('.%06d' % value.microsecond))
                 else:
                     return value.strftime(self.__format__)
             else:
@@ -62,7 +66,10 @@ class DateTimeMixin(object):
             return None
         try:
             (value, microsecond) = value.split('.')
-            microsecond = int(microsecond)
+            if self.__legacy_microseconds__:
+                microsecond = int(microsecond)
+            else:
+                microsecond = int((microsecond + '000000')[0:6])
         except ValueError:
             microsecond = 0
         return time.strptime(value, self.__format__)[0:6] + (microsecond,)
diff --git a/test/dialect/sqlite.py b/test/dialect/sqlite.py
index c493b8d43..a902ecb24 100644
--- a/test/dialect/sqlite.py
+++ b/test/dialect/sqlite.py
@@ -30,7 +30,21 @@ class TestTypes(TestBase, AssertsExecutionResults):
 
         finally:
             meta.drop_all()
-
+    
+    def test_time_microseconds(self):
+        dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125)  # 125 usec
+        self.assertEquals(str(dt), '2008-06-27 12:00:00.000125')
+        sldt = sqlite.SLDateTime()
+        bp = sldt.bind_processor(None)
+        self.assertEquals(bp(dt), '2008-06-27 12:00:00.000125')
+        
+        rp = sldt.result_processor(None)
+        self.assertEquals(rp(bp(dt)), dt)
+        
+        sldt.__legacy_microseconds__ = True
+        self.assertEquals(bp(dt), '2008-06-27 12:00:00.125')
+        self.assertEquals(rp(bp(dt)), dt)
+        
     @testing.uses_deprecated('Using String type with no length')
     def test_type_reflection(self):
         # (ask_for, roundtripped_as_if_different)