1394 def test_date_extraction(self): |
1394 def test_date_extraction(self): |
1395 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
1395 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
1396 '''SELECT CAST(EXTRACT(MONTH from _P.cw_creation_date) AS INTEGER) |
1396 '''SELECT CAST(EXTRACT(MONTH from _P.cw_creation_date) AS INTEGER) |
1397 FROM cw_Personne AS _P''') |
1397 FROM cw_Personne AS _P''') |
1398 |
1398 |
|
1399 def test_weekday_extraction(self): |
|
1400 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
1401 '''SELECT (CAST(EXTRACT(DOW from _P.cw_creation_date) AS INTEGER) + 1) |
|
1402 FROM cw_Personne AS _P''') |
|
1403 |
1399 def test_substring(self): |
1404 def test_substring(self): |
1400 self._check("Any SUBSTRING(N, 1, 1) WHERE P nom N, P is Personne", |
1405 self._check("Any SUBSTRING(N, 1, 1) WHERE P nom N, P is Personne", |
1401 '''SELECT SUBSTR(_P.cw_nom, 1, 1) |
1406 '''SELECT SUBSTR(_P.cw_nom, 1, 1) |
1402 FROM cw_Personne AS _P''') |
1407 FROM cw_Personne AS _P''') |
1403 |
1408 |
1522 ('Any A WHERE A ordernum O, A is CWAttribute WITH O BEING (Any MAX(O) WHERE A ordernum O, A is CWAttribute)', |
1527 ('Any A WHERE A ordernum O, A is CWAttribute WITH O BEING (Any MAX(O) WHERE A ordernum O, A is CWAttribute)', |
1523 '''SELECT _A.cw_eid |
1528 '''SELECT _A.cw_eid |
1524 FROM (SELECT MAX(_A.cw_ordernum) AS C0 |
1529 FROM (SELECT MAX(_A.cw_ordernum) AS C0 |
1525 FROM cw_CWAttribute AS _A) AS _T0, cw_CWAttribute AS _A |
1530 FROM cw_CWAttribute AS _A) AS _T0, cw_CWAttribute AS _A |
1526 WHERE _A.cw_ordernum=_T0.C0'''), |
1531 WHERE _A.cw_ordernum=_T0.C0'''), |
|
1532 |
|
1533 ('Any O1 HAVING O1=O2? WITH O1 BEING (Any MAX(O) WHERE A ordernum O, A is CWAttribute), O2 BEING (Any MAX(O) WHERE A ordernum O, A is CWRelation)', |
|
1534 '''SELECT _T0.C0 |
|
1535 FROM (SELECT MAX(_A.cw_ordernum) AS C0 |
|
1536 FROM cw_CWAttribute AS _A) AS _T0 LEFT OUTER JOIN (SELECT MAX(_A.cw_ordernum) AS C0 |
|
1537 FROM cw_CWRelation AS _A) AS _T1 ON (_T0.C0=_T1.C0)'''), |
1527 )): |
1538 )): |
1528 yield t |
1539 yield t |
1529 |
1540 |
1530 |
1541 |
1531 def test_subquery_error(self): |
1542 def test_subquery_error(self): |
1747 |
1758 |
1748 def test_or_having_fake_terms_base(self): |
1759 def test_or_having_fake_terms_base(self): |
1749 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
1760 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
1750 '''SELECT _X.cw_eid |
1761 '''SELECT _X.cw_eid |
1751 FROM cw_CWUser AS _X |
1762 FROM cw_CWUser AS _X |
1752 WHERE ((YEAR(_X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''') |
1763 WHERE ((DATEPART(YEAR, _X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''') |
1753 |
1764 |
1754 def test_date_extraction(self): |
1765 def test_date_extraction(self): |
1755 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
1766 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
1756 '''SELECT MONTH(_P.cw_creation_date) |
1767 '''SELECT DATEPART(MONTH, _P.cw_creation_date) |
|
1768 FROM cw_Personne AS _P''') |
|
1769 |
|
1770 def test_weekday_extraction(self): |
|
1771 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
1772 '''SELECT DATEPART(WEEKDAY, _P.cw_creation_date) |
1757 FROM cw_Personne AS _P''') |
1773 FROM cw_Personne AS _P''') |
1758 |
1774 |
1759 def test_symmetric(self): |
1775 def test_symmetric(self): |
1760 for t in self._parse(SYMMETRIC): |
1776 for t in self._parse(SYMMETRIC): |
1761 yield t |
1777 yield t |
1891 def test_groupby_orderby_insertion_dont_modify_intention(self): |
1907 def test_groupby_orderby_insertion_dont_modify_intention(self): |
1892 self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) ' |
1908 self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) ' |
1893 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 ' |
1909 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 ' |
1894 'WHERE X creation_date XSCT, X modification_date XECT, ' |
1910 'WHERE X creation_date XSCT, X modification_date XECT, ' |
1895 'X ordernum XCE, X is CWAttribute', |
1911 'X ordernum XCE, X is CWAttribute', |
1896 '''SELECT ((YEAR(_X.cw_modification_date) * 100) + MONTH(_X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date)) |
1912 '''SELECT ((DATEPART(YEAR, _X.cw_modification_date) * 100) + DATEPART(MONTH, _X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date)) |
1897 FROM cw_CWAttribute AS _X |
1913 FROM cw_CWAttribute AS _X |
1898 GROUP BY YEAR(_X.cw_modification_date),MONTH(_X.cw_modification_date) |
1914 GROUP BY DATEPART(YEAR, _X.cw_modification_date),DATEPART(MONTH, _X.cw_modification_date) |
1899 ORDER BY 1'''), |
1915 ORDER BY 1'''), |
1900 |
1916 |
1901 |
1917 |
1902 class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC): |
1918 class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC): |
1903 backend = 'sqlite' |
1919 backend = 'sqlite' |
1906 return sql.strip().replace(' ILIKE ', ' LIKE ').replace('TRUE', '1').replace('FALSE', '0') |
1922 return sql.strip().replace(' ILIKE ', ' LIKE ').replace('TRUE', '1').replace('FALSE', '0') |
1907 |
1923 |
1908 def test_date_extraction(self): |
1924 def test_date_extraction(self): |
1909 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
1925 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
1910 '''SELECT MONTH(_P.cw_creation_date) |
1926 '''SELECT MONTH(_P.cw_creation_date) |
|
1927 FROM cw_Personne AS _P''') |
|
1928 |
|
1929 def test_weekday_extraction(self): |
|
1930 # custom impl. in cw.server.sqlutils |
|
1931 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
1932 '''SELECT WEEKDAY(_P.cw_creation_date) |
1911 FROM cw_Personne AS _P''') |
1933 FROM cw_Personne AS _P''') |
1912 |
1934 |
1913 def test_regexp(self): |
1935 def test_regexp(self): |
1914 self._check("Any X WHERE X login REGEXP '[0-9].*'", |
1936 self._check("Any X WHERE X login REGEXP '[0-9].*'", |
1915 '''SELECT _X.cw_eid |
1937 '''SELECT _X.cw_eid |
2064 return '\n'.join(newsql) |
2086 return '\n'.join(newsql) |
2065 |
2087 |
2066 def test_date_extraction(self): |
2088 def test_date_extraction(self): |
2067 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
2089 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
2068 '''SELECT EXTRACT(MONTH from _P.cw_creation_date) |
2090 '''SELECT EXTRACT(MONTH from _P.cw_creation_date) |
|
2091 FROM cw_Personne AS _P''') |
|
2092 |
|
2093 def test_weekday_extraction(self): |
|
2094 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
2095 '''SELECT DAYOFWEEK(_P.cw_creation_date) |
2069 FROM cw_Personne AS _P''') |
2096 FROM cw_Personne AS _P''') |
2070 |
2097 |
2071 def test_cast(self): |
2098 def test_cast(self): |
2072 self._check("Any CAST(String, P) WHERE P is Personne", |
2099 self._check("Any CAST(String, P) WHERE P is Personne", |
2073 '''SELECT CAST(_P.cw_eid AS mediumtext) |
2100 '''SELECT CAST(_P.cw_eid AS mediumtext) |