The newly added SQLite DATETIME arguments storage_format and

regexp apparently were not fully implemented correctly; while the
arguments were accepted, in practice they would have no effect;
this has been fixed.  Also in 0.8.3.
[ticket:2781]

4 files changed, 70 insertions, 1 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 8ec248b89..0212fb3b0 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -7,6 +7,15 @@
     :version: 0.8.3
 
     .. change::
+        :tags: bug, sqlite
+        :tickets: 2781
+
+        The newly added SQLite DATETIME arguments storage_format and
+        regexp apparently were not fully implemented correctly; while the
+        arguments were accepted, in practice they would have no effect;
+        this has been fixed.
+
+    .. change::
         :tags: bug, sql, postgresql
         :tickets: 2780
 
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index b1966f899..84110f0f3 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -7,6 +7,15 @@
     :version: 0.9.0
 
     .. change::
+        :tags: bug, sqlite
+        :tickets: 2781
+
+        The newly added SQLite DATETIME arguments storage_format and
+        regexp apparently were not fully implemented correctly; while the
+        arguments were accepted, in practice they would have no effect;
+        this has been fixed.  Also in 0.8.3.
+
+    .. change::
         :tags: bug, sql, postgresql
         :tickets: 2780
 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index a9a81394f..a7bcda128 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -153,6 +153,12 @@ class _DateTimeMixin(object):
         if storage_format is not None:
             self._storage_format = storage_format
 
+    def adapt(self, cls, **kw):
+        if self._storage_format:
+            kw["storage_format"] = self._storage_format
+        if self._reg:
+            kw["regexp"] = self._reg
+        return util.constructor_copy(self, cls, **kw)
 
 class DATETIME(_DateTimeMixin, sqltypes.DateTime):
     """Represent a Python datetime object in SQLite using a string.
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index d75211a71..1b424e6c8 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -96,6 +96,52 @@ class TestTypes(fixtures.TestBase, AssertsExecutionResults):
             t.drop(engine)
             engine.dispose()
 
+    @testing.provide_metadata
+    def test_custom_datetime(self):
+        sqlite_date = sqlite.DATETIME(
+                # 2004-05-21T00:00:00
+                storage_format="%(year)04d-%(month)02d-%(day)02d"
+                    "T%(hour)02d:%(minute)02d:%(second)02d",
+                regexp=r"(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)",
+            )
+        t = Table('t', self.metadata, Column('d', sqlite_date))
+        self.metadata.create_all(testing.db)
+        testing.db.execute(t.insert().
+                        values(d=datetime.datetime(2010, 10, 15, 12, 37, 0)))
+        testing.db.execute("insert into t (d) values ('2004-05-21T00:00:00')")
+        eq_(
+            testing.db.execute("select * from t order by d").fetchall(),
+            [(u'2004-05-21T00:00:00',), (u'2010-10-15T12:37:00',)]
+        )
+        eq_(
+            testing.db.execute(select([t.c.d]).order_by(t.c.d)).fetchall(),
+            [(datetime.datetime(2004, 5, 21, 0, 0),),
+            (datetime.datetime(2010, 10, 15, 12, 37),)]
+        )
+
+    @testing.provide_metadata
+    def test_custom_date(self):
+        sqlite_date = sqlite.DATE(
+                # 2004-05-21T00:00:00
+                storage_format="%(year)04d|%(month)02d|%(day)02d",
+                regexp=r"(\d+)\|(\d+)\|(\d+)",
+            )
+        t = Table('t', self.metadata, Column('d', sqlite_date))
+        self.metadata.create_all(testing.db)
+        testing.db.execute(t.insert().
+                        values(d=datetime.date(2010, 10, 15)))
+        testing.db.execute("insert into t (d) values ('2004|05|21')")
+        eq_(
+            testing.db.execute("select * from t order by d").fetchall(),
+            [(u'2004|05|21',), (u'2010|10|15',)]
+        )
+        eq_(
+            testing.db.execute(select([t.c.d]).order_by(t.c.d)).fetchall(),
+            [(datetime.date(2004, 5, 21),),
+            (datetime.date(2010, 10, 15),)]
+        )
+
+
     def test_no_convert_unicode(self):
         """test no utf-8 encoding occurs"""
 
@@ -221,7 +267,6 @@ class DateTimeTest(fixtures.TestBase, AssertsCompiledSQL):
         rp = sldt.result_processor(None, None)
         eq_(rp(bp(dt)), dt)
 
-
 class DateTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_default(self):