From 0ce045bd853ec078943c14fc93b87897d2169882 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 5 Dec 2014 14:46:43 -0500
Subject: - The SQLite dialect, when using the :class:`.sqlite.DATE`,
 :class:`.sqlite.TIME`, or :class:`.sqlite.DATETIME` types, and given a
 ``storage_format`` that only renders numbers, will render the types in DDL as
 ``DATE_CHAR``, ``TIME_CHAR``, and ``DATETIME_CHAR``, so that despite the lack
 of alpha characters in the values, the column will still deliver the "text
 affinity".  Normally this is not needed, as the textual values within the
 default storage formats already imply text. fixes #3257

---
 test/dialect/test_sqlite.py | 57 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 57 insertions(+)

(limited to 'test/dialect')

diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 04e82e686..22772d2fb 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -128,6 +128,53 @@ class TestTypes(fixtures.TestBase, AssertsExecutionResults):
                 (datetime.datetime(2010, 10, 15, 12, 37),)]
         )
 
+    @testing.provide_metadata
+    def test_custom_datetime_text_affinity(self):
+        sqlite_date = sqlite.DATETIME(
+            storage_format="%(year)04d%(month)02d%(day)02d"
+            "%(hour)02d%(minute)02d%(second)02d",
+            regexp=r"(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})",
+        )
+        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 ('20040521000000')")
+        eq_(
+            testing.db.execute("select * from t order by d").fetchall(),
+            [('20040521000000',), ('20101015123700',)]
+        )
+        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_text_affinity(self):
+        sqlite_date = sqlite.DATE(
+            storage_format="%(year)04d%(month)02d%(day)02d",
+            regexp=r"(\d{4})(\d{2})(\d{2})",
+        )
+        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 ('20040521')")
+        eq_(
+            testing.db.execute("select * from t order by d").fetchall(),
+            [('20040521',), ('20101015',)]
+        )
+        eq_(
+            testing.db.execute(select([t.c.d]).order_by(t.c.d)).fetchall(),
+            [
+                (datetime.date(2004, 5, 21),),
+                (datetime.date(2010, 10, 15),)]
+        )
+
     @testing.provide_metadata
     def test_custom_date(self):
         sqlite_date = sqlite.DATE(
@@ -1167,6 +1214,16 @@ class TypeReflectionTest(fixtures.TestBase):
             (sqltypes.Time, sqltypes.TIME()),
             (sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
             (sqltypes.Boolean, sqltypes.BOOLEAN()),
+            (sqlite.DATE(
+                storage_format="%(year)04d%(month)02d%(day)02d",
+            ), sqltypes.DATE()),
+            (sqlite.TIME(
+                storage_format="%(hour)02d%(minute)02d%(second)02d",
+            ), sqltypes.TIME()),
+            (sqlite.DATETIME(
+                storage_format="%(year)04d%(month)02d%(day)02d"
+                "%(hour)02d%(minute)02d%(second)02d",
+            ), sqltypes.DATETIME()),
         ]
 
     def _unsupported_args_fixture(self):
-- 
cgit v1.2.1