# HG changeset patch
# User Sylvain Thénault <sylvain.thenault@logilab.fr>
# Date 1317716315 -7200
# Node ID f41bb38dda7cc9b177157392e385ce0cf9a5960c
# Parent  ac658ab4c7b773651020ba68c3b4a1a2e7866244
[rql, sql] support for weekday function introduced in lgdp 1.7 (closes #1979717)

diff -r ac658ab4c7b7 -r f41bb38dda7c server/sqlutils.py
--- a/server/sqlutils.py	Tue Oct 04 10:15:46 2011 +0200
+++ b/server/sqlutils.py	Tue Oct 04 10:18:35 2011 +0200
@@ -338,6 +338,17 @@
         return _limit_size(text, maxsize)
     cnx.create_function("TEXT_LIMIT_SIZE", 2, limit_size2)
 
+    from logilab.common.date import strptime
+    def weekday(ustr):
+        try:
+            dt = strptime(ustr, '%Y-%m-%d %H:%M:%S')
+        except:
+            dt =  strptime(ustr, '%Y-%m-%d')
+        # expect sunday to be 1, saturday 7 while weekday method return 0 for
+        # monday
+        return (dt.weekday() + 1) % 7
+    cnx.create_function("WEEKDAY", 1, weekday)
+
     import yams.constraints
     yams.constraints.patch_sqlite_decimal()
 
diff -r ac658ab4c7b7 -r f41bb38dda7c server/test/unittest_querier.py
--- a/server/test/unittest_querier.py	Tue Oct 04 10:15:46 2011 +0200
+++ b/server/test/unittest_querier.py	Tue Oct 04 10:18:35 2011 +0200
@@ -443,7 +443,7 @@
         self.execute("INSERT Personne X: X nom 'foo', X datenaiss %(d)s",
                      {'d': datetime(2001, 2,3, 12,13)})
         test_data = [('YEAR', 2001), ('MONTH', 2), ('DAY', 3),
-                     ('HOUR', 12), ('MINUTE', 13)]
+                     ('HOUR', 12), ('MINUTE', 13), ('WEEKDAY', 6)]
         for funcname, result in test_data:
             rset = self.execute('Any %s(D) WHERE X is Personne, X datenaiss D'
                                 % funcname)
diff -r ac658ab4c7b7 -r f41bb38dda7c server/test/unittest_rql2sql.py
--- a/server/test/unittest_rql2sql.py	Tue Oct 04 10:15:46 2011 +0200
+++ b/server/test/unittest_rql2sql.py	Tue Oct 04 10:18:35 2011 +0200
@@ -1396,6 +1396,11 @@
                     '''SELECT CAST(EXTRACT(MONTH from _P.cw_creation_date) AS INTEGER)
 FROM cw_Personne AS _P''')
 
+    def test_weekday_extraction(self):
+        self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D",
+                    '''SELECT (CAST(EXTRACT(DOW from _P.cw_creation_date) AS INTEGER) + 1)
+FROM cw_Personne AS _P''')
+
     def test_substring(self):
         self._check("Any SUBSTRING(N, 1, 1) WHERE P nom N, P is Personne",
                     '''SELECT SUBSTR(_P.cw_nom, 1, 1)
@@ -1759,7 +1764,12 @@
 
     def test_date_extraction(self):
         self._check("Any MONTH(D) WHERE P is Personne, P creation_date D",
-                    '''SELECT MONTH(_P.cw_creation_date)
+                    '''SELECT DATEPART(MONTH, _P.cw_creation_date)
+FROM cw_Personne AS _P''')
+
+    def test_weekday_extraction(self):
+        self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D",
+                    '''SELECT DATEPART(WEEKDAY, _P.cw_creation_date)
 FROM cw_Personne AS _P''')
 
     def test_symmetric(self):
@@ -1916,6 +1926,12 @@
                     '''SELECT MONTH(_P.cw_creation_date)
 FROM cw_Personne AS _P''')
 
+    def test_weekday_extraction(self):
+        # custom impl. in cw.server.sqlutils
+        self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D",
+                    '''SELECT WEEKDAY(_P.cw_creation_date)
+FROM cw_Personne AS _P''')
+
     def test_regexp(self):
         self._check("Any X WHERE X login REGEXP '[0-9].*'",
                     '''SELECT _X.cw_eid
@@ -2074,6 +2090,11 @@
                     '''SELECT EXTRACT(MONTH from _P.cw_creation_date)
 FROM cw_Personne AS _P''')
 
+    def test_weekday_extraction(self):
+        self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D",
+                    '''SELECT DAYOFWEEK(_P.cw_creation_date)
+FROM cw_Personne AS _P''')
+
     def test_cast(self):
         self._check("Any CAST(String, P) WHERE P is Personne",
                     '''SELECT CAST(_P.cw_eid AS mediumtext)