extract() is now dialect-sensitive and supports SQLite and others.

8 files changed, 147 insertions, 17 deletions

diff --git a/test/dialect/access.py b/test/dialect/access.py
index 311231947..57af45a9d 100644
--- a/test/dialect/access.py
+++ b/test/dialect/access.py
@@ -1,14 +1,33 @@
 import testenv; testenv.configure_for_tests()
 from sqlalchemy import *
+from sqlalchemy import sql
 from sqlalchemy.databases import access
 from testlib import *
 
 
-class BasicTest(TestBase, AssertsExecutionResults):
-    # A simple import of the database/ module should work on all systems.
-    def test_import(self):
-        # we got this far, right?
-        return True
+class CompileTest(TestBase, AssertsCompiledSQL):
+    __dialect__ = access.dialect()
+
+    def test_extract(self):
+        t = sql.table('t', sql.column('col1'))
+
+        mapping = {
+            'month': 'm',
+            'day': 'd',
+            'year': 'yyyy',
+            'second': 's',
+            'hour': 'h',
+            'doy': 'y',
+            'minute': 'n',
+            'quarter': 'q',
+            'dow': 'w',
+            'week': 'ww'
+            }
+
+        for field, subst in mapping.items():
+            self.assert_compile(
+                select([extract(field, t.c.col1)]),
+                'SELECT DATEPART("%s", t.col1) AS anon_1 FROM t' % subst)
 
 
 if __name__ == "__main__":
diff --git a/test/dialect/mssql.py b/test/dialect/mssql.py
index 0962f59f3..de9c5cd62 100755
--- a/test/dialect/mssql.py
+++ b/test/dialect/mssql.py
@@ -125,6 +125,14 @@ class CompileTest(TestBase, AssertsCompiledSQL):
         self.assert_compile(func.current_date(), "GETDATE()")
         self.assert_compile(func.length(3), "LEN(:length_1)")
 
+    def test_extract(self):
+        t = table('t', column('col1'))
+
+        for field in 'day', 'month', 'year':
+            self.assert_compile(
+                select([extract(field, t.c.col1)]),
+                'SELECT DATEPART("%s", t.col1) AS anon_1 FROM t' % field)
+
 
 class IdentityInsertTest(TestBase, AssertsCompiledSQL):
     __only_on__ = 'mssql'
diff --git a/test/dialect/mysql.py b/test/dialect/mysql.py
index a233c25f5..fa8a85ec4 100644
--- a/test/dialect/mysql.py
+++ b/test/dialect/mysql.py
@@ -982,6 +982,19 @@ class SQLTest(TestBase, AssertsCompiledSQL):
         for type_, expected in specs:
             self.assert_compile(cast(t.c.col, type_), expected)
 
+    def test_extract(self):
+        t = sql.table('t', sql.column('col1'))
+
+        for field in 'year', 'month', 'day':
+            self.assert_compile(
+                select([extract(field, t.c.col1)]),
+                "SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field)
+
+        # millsecondS to millisecond
+        self.assert_compile(
+            select([extract('milliseconds', t.c.col1)]),
+            "SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t")
+
 
 class RawReflectionTest(TestBase):
     def setUp(self):
diff --git a/test/dialect/postgres.py b/test/dialect/postgres.py
index 3867d1b01..d613ad2dd 100644
--- a/test/dialect/postgres.py
+++ b/test/dialect/postgres.py
@@ -22,6 +22,8 @@ class SequenceTest(TestBase, AssertsCompiledSQL):
         assert dialect.identifier_preparer.format_sequence(seq) == '"Some_Schema"."My_Seq"'
 
 class CompileTest(TestBase, AssertsCompiledSQL):
+    __dialect__ = postgres.dialect()
+
     def test_update_returning(self):
         dialect = postgres.dialect()
         table1 = table('mytable',
@@ -58,6 +60,16 @@ class CompileTest(TestBase, AssertsCompiledSQL):
         i = insert(table1, values=dict(name='foo'), postgres_returning=[func.length(table1.c.name)])
         self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)", dialect=dialect)
 
+    def test_extract(self):
+        t = table('t', column('col1'))
+
+        for field in 'year', 'month', 'day':
+            self.assert_compile(
+                select([extract(field, t.c.col1)]),
+                "SELECT EXTRACT(%s FROM t.col1::timestamp) AS anon_1 "
+                "FROM t" % field)
+
+
 class ReturningTest(TestBase, AssertsExecutionResults):
     __only_on__ = 'postgres'
 
diff --git a/test/dialect/sqlite.py b/test/dialect/sqlite.py
index 97d12bf60..005fad66b 100644
--- a/test/dialect/sqlite.py
+++ b/test/dialect/sqlite.py
@@ -3,7 +3,7 @@
 import testenv; testenv.configure_for_tests()
 import datetime
 from sqlalchemy import *
-from sqlalchemy import exc
+from sqlalchemy import exc, sql
 from sqlalchemy.databases import sqlite
 from testlib import *
 
@@ -283,6 +283,36 @@ class DialectTest(TestBase, AssertsExecutionResults):
                 pass
             raise
 
+
+class SQLTest(TestBase, AssertsCompiledSQL):
+    """Tests SQLite-dialect specific compilation."""
+
+    __dialect__ = sqlite.dialect()
+
+
+    def test_extract(self):
+        t = sql.table('t', sql.column('col1'))
+
+        mapping = {
+            'month': '%m',
+            'day': '%d',
+            'year': '%Y',
+            'second': '%S',
+            'hour': '%H',
+            'doy': '%j',
+            'minute': '%M',
+            'epoch': '%s',
+            'dow': '%w',
+            'week': '%W',
+            }
+
+        for field, subst in mapping.items():
+            self.assert_compile(
+                select([extract(field, t.c.col1)]),
+                "SELECT CAST(STRFTIME('%s', t.col1) AS INTEGER) AS anon_1 "
+                "FROM t" % subst)
+
+
 class InsertTest(TestBase, AssertsExecutionResults):
     """Tests inserts and autoincrement."""
 
diff --git a/test/dialect/sybase.py b/test/dialect/sybase.py
index 19cca465b..32b9904d8 100644
--- a/test/dialect/sybase.py
+++ b/test/dialect/sybase.py
@@ -1,14 +1,30 @@
 import testenv; testenv.configure_for_tests()
 from sqlalchemy import *
+from sqlalchemy import sql
 from sqlalchemy.databases import sybase
 from testlib import *
 
 
-class BasicTest(TestBase, AssertsExecutionResults):
-    # A simple import of the database/ module should work on all systems.
-    def test_import(self):
-        # we got this far, right?
-        return True
+class CompileTest(TestBase, AssertsCompiledSQL):
+    __dialect__ = sybase.dialect()
+
+    def test_extract(self):
+        t = sql.table('t', sql.column('col1'))
+
+        mapping = {
+            'day': 'day',
+            'doy': 'dayofyear',
+            'dow': 'weekday',
+            'milliseconds': 'millisecond',
+            'millisecond': 'millisecond',
+            'year': 'year',
+            }
+
+        for field, subst in mapping.items():
+            self.assert_compile(
+                select([extract(field, t.c.col1)]),
+                'SELECT DATEPART("%s", t.col1) AS anon_1 FROM t' % subst)
+
 
 
 if __name__ == "__main__":
diff --git a/test/sql/functions.py b/test/sql/functions.py
index 151957503..17d8a35e9 100644
--- a/test/sql/functions.py
+++ b/test/sql/functions.py
@@ -271,6 +271,44 @@ class ExecuteTest(TestBase):
 
         assert x == y == z == w == q == r
 
+    def test_extract_bind(self):
+        """Basic common denominator execution tests for extract()"""
+
+        date = datetime.date(2010, 5, 1)
+
+        def execute(field):
+            return testing.db.execute(select([extract(field, date)])).scalar()
+
+        assert execute('year') == 2010
+        assert execute('month') == 5
+        assert execute('day') == 1
+
+        date = datetime.datetime(2010, 5, 1, 12, 11, 10)
+
+        assert execute('year') == 2010
+        assert execute('month') == 5
+        assert execute('day') == 1
+
+    def test_extract_expression(self):
+        meta = MetaData(testing.db)
+        table = Table('test', meta,
+                      Column('dt', DateTime),
+                      Column('d', Date))
+        meta.create_all()
+        try:
+            table.insert().execute(
+                {'dt': datetime.datetime(2010, 5, 1, 12, 11, 10),
+                 'd': datetime.date(2010, 5, 1) })
+            rs = select([extract('year', table.c.dt),
+                         extract('month', table.c.d)]).execute()
+            row = rs.fetchone()
+            assert row[0] == 2010
+            assert row[1] == 5
+            rs.close()
+        finally:
+            meta.drop_all()
+
+
 def exec_sorted(statement, *args, **kw):
     """Executes a statement and returns a sorted list plain tuple rows."""
 
diff --git a/test/sql/select.py b/test/sql/select.py
index 15c47a674..52c382f81 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -831,12 +831,6 @@ FROM mytable, myothertable WHERE foo.id = foofoo(lala) AND datetime(foo) = Today
              "SELECT values.id FROM values WHERE values.val1 / (values.val2 - values.val1) / values.val1 > :param_1"
          )
 
-    def test_extract(self):
-        """test the EXTRACT function"""
-        self.assert_compile(select([extract("month", table3.c.otherstuff)]), "SELECT extract(month FROM thirdtable.otherstuff) AS extract_1 FROM thirdtable")
-
-        self.assert_compile(select([extract("day", func.to_date("03/20/2005", "MM/DD/YYYY"))]), "SELECT extract(day FROM to_date(:to_date_1, :to_date_2)) AS extract_1")
-
     def test_collate(self):
         for expr in (select([table1.c.name.collate('latin1_german2_ci')]),
                      select([collate(table1.c.name, 'latin1_german2_ci')])):