Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
all DB engines do not support the same syntax for these features, MS SQLServer being the bad boy we try to support in CW.
* Use two new methods of dbhelper to add LIMIT/OFFSET clauses and ORDER BY clauses
* added unit tests for sqlserver backend
* changed unittest_rql2sql to lauch the backend tests even if the driver module is not installed on the machine, so
that we can run the sqlserver tests on linux (and the mysql tests too)
* adapt msstep.py to the new interface
closes #1154756
--- a/__pkginfo__.py Thu Mar 24 13:31:12 2011 +0100
+++ b/__pkginfo__.py Tue Mar 22 15:11:38 2011 +0100
@@ -52,7 +52,7 @@
'Twisted': '',
# XXX graphviz
# server dependencies
- 'logilab-database': '>= 1.3.3',
+ 'logilab-database': '>= 1.4.0',
'pysqlite': '>= 2.5.5', # XXX install pysqlite2
}
--- a/debian/control Thu Mar 24 13:31:12 2011 +0100
+++ b/debian/control Tue Mar 22 15:11:38 2011 +0100
@@ -33,7 +33,7 @@
Conflicts: cubicweb-multisources
Replaces: cubicweb-multisources
Provides: cubicweb-multisources
-Depends: ${misc:Depends}, ${python:Depends}, cubicweb-common (= ${source:Version}), cubicweb-ctl (= ${source:Version}), python-logilab-database (>= 1.3.3), cubicweb-postgresql-support | cubicweb-mysql-support | python-pysqlite2
+Depends: ${misc:Depends}, ${python:Depends}, cubicweb-common (= ${source:Version}), cubicweb-ctl (= ${source:Version}), python-logilab-database (>= 1.4.0), cubicweb-postgresql-support | cubicweb-mysql-support | python-pysqlite2
Recommends: pyro (<< 4.0.0), cubicweb-documentation (= ${source:Version})
Description: server part of the CubicWeb framework
CubicWeb is a semantic web application framework.
--- a/server/mssteps.py Thu Mar 24 13:31:12 2011 +0100
+++ b/server/mssteps.py Tue Mar 22 15:11:38 2011 +0100
@@ -162,6 +162,7 @@
def get_sql(self):
self.inputmap = inputmap = self.children[-1].outputmap
+ dbhelper=self.plan.syssource.dbhelper
# get the select clause
clause = []
for i, term in enumerate(self.selection):
@@ -218,12 +219,16 @@
if not vref.name in grouped:
sql[-1] += ', ' + self.inputmap[vref.name]
grouped.add(vref.name)
- sql.append('ORDER BY %s' % ', '.join(clause))
- if self.limit:
- sql.append('LIMIT %s' % self.limit)
- if self.offset:
- sql.append('OFFSET %s' % self.offset)
- return ' '.join(sql)
+ sql = dbhelper.sql_add_order_by(' '.join(sql),
+ clause,
+ None, False,
+ self.limit or self.offset)
+ else:
+ sql = ' '.join(sql)
+ clause = None
+
+ sql = dbhelper.sql_add_limit_offset(sql, self.limit, self.offset, clause)
+ return sql
def visit_function(self, function):
"""generate SQL name for a function"""
--- a/server/sources/rql2sql.py Thu Mar 24 13:31:12 2011 +0100
+++ b/server/sources/rql2sql.py Tue Mar 22 15:11:38 2011 +0100
@@ -620,24 +620,30 @@
sql += '\nHAVING %s' % having
# sort
if sorts:
- sqlsortterms = [self._sortterm_sql(sortterm, fselectidx)
- for sortterm in sorts]
- sqlsortterms = [x for x in sqlsortterms if x is not None]
+ sqlsortterms = []
+ for sortterm in sorts:
+ _term = self._sortterm_sql(sortterm, fselectidx)
+ if _term is not None:
+ sqlsortterms.append(_term)
if sqlsortterms:
- sql += '\nORDER BY %s' % ','.join(sqlsortterms)
- if sorts and fneedwrap:
- selection = ['T1.C%s' % i for i in xrange(len(origselection))]
- sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
+ sql = self.dbhelper.sql_add_order_by(sql, sqlsortterms,
+ origselection,
+ fneedwrap,
+ select.limit or select.offset)
+ ## sql += '\nORDER BY %s' % ','.join(sqlsortterms)
+ ## if sorts and fneedwrap:
+ ## selection = ['T1.C%s' % i for i in xrange(len(origselection))]
+ ## sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
+ else:
+ sqlsortterms = None
state.finalize_source_cbs()
finally:
select.selection = origselection
# limit / offset
- limit = select.limit
- if limit:
- sql += '\nLIMIT %s' % limit
- offset = select.offset
- if offset:
- sql += '\nOFFSET %s' % offset
+ sql = self.dbhelper.sql_add_limit_offset(sql,
+ select.limit,
+ select.offset,
+ sqlsortterms)
return sql
def _subqueries_sql(self, select, state):
--- a/server/test/unittest_msplanner.py Thu Mar 24 13:31:12 2011 +0100
+++ b/server/test/unittest_msplanner.py Tue Mar 22 15:11:38 2011 +0100
@@ -430,7 +430,7 @@
"""retrieve CWUser X from both sources and return concatenation of results
"""
self._test('CWUser X ORDERBY X LIMIT 10 OFFSET 10',
- [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY table0.C0 LIMIT 10 OFFSET 10', None, [
+ [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY table0.C0\nLIMIT 10\nOFFSET 10', None, [
('FetchStep', [('Any X WHERE X is CWUser', [{'X': 'CWUser'}])],
[self.ldap, self.system], {}, {'X': 'table0.C0'}, []),
]),
@@ -515,7 +515,7 @@
def test_complex_ordered(self):
self._test('Any L ORDERBY L WHERE X login L',
- [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY table0.C0', None,
+ [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY table0.C0', None,
[('FetchStep', [('Any L WHERE X login L, X is CWUser',
[{'X': 'CWUser', 'L': 'String'}])],
[self.ldap, self.system], {}, {'X.login': 'table0.C0', 'L': 'table0.C0'}, []),
@@ -524,7 +524,7 @@
def test_complex_ordered_limit_offset(self):
self._test('Any L ORDERBY L LIMIT 10 OFFSET 10 WHERE X login L',
- [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY table0.C0 LIMIT 10 OFFSET 10', None,
+ [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY table0.C0\nLIMIT 10\nOFFSET 10', None,
[('FetchStep', [('Any L WHERE X login L, X is CWUser',
[{'X': 'CWUser', 'L': 'String'}])],
[self.ldap, self.system], {}, {'X.login': 'table0.C0', 'L': 'table0.C0'}, []),
@@ -610,7 +610,7 @@
2. return content of the table sorted
"""
self._test('Any X,F ORDERBY F WHERE X firstname F',
- [('AggrStep', 'SELECT table0.C0, table0.C1 FROM table0 ORDER BY table0.C1', None,
+ [('AggrStep', 'SELECT table0.C0, table0.C1 FROM table0\nORDER BY table0.C1', None,
[('FetchStep', [('Any X,F WHERE X firstname F, X is CWUser',
[{'X': 'CWUser', 'F': 'String'}])],
[self.ldap, self.system], {},
@@ -1344,7 +1344,7 @@
self._test('Any X ORDERBY FTIRANK(X) WHERE X has_text "bla", X firstname "bla"',
[('FetchStep', [('Any X WHERE X firstname "bla", X is CWUser', [{'X': 'CWUser'}])],
[self.ldap, self.system], None, {'X': 'table0.C0'}, []),
- ('AggrStep', 'SELECT table1.C1 FROM table1 ORDER BY table1.C0', None, [
+ ('AggrStep', 'SELECT table1.C1 FROM table1\nORDER BY table1.C0', None, [
('FetchStep', [('Any FTIRANK(X),X WHERE X has_text "bla", X is CWUser',
[{'X': 'CWUser'}])],
[self.system], {'X': 'table0.C0'}, {'FTIRANK(X)': 'table1.C0', 'X': 'table1.C1'}, []),
@@ -1401,7 +1401,7 @@
def test_sort_func(self):
self._test('Note X ORDERBY DUMB_SORT(RF) WHERE X type RF',
- [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY DUMB_SORT(table0.C1)', None, [
+ [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY DUMB_SORT(table0.C1)', None, [
('FetchStep', [('Any X,RF WHERE X type RF, X is Note',
[{'X': 'Note', 'RF': 'String'}])],
[self.cards, self.system], {}, {'X': 'table0.C0', 'X.type': 'table0.C1', 'RF': 'table0.C1'}, []),
@@ -1410,7 +1410,7 @@
def test_ambigous_sort_func(self):
self._test('Any X ORDERBY DUMB_SORT(RF) WHERE X title RF, X is IN (Bookmark, Card, EmailThread)',
- [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY DUMB_SORT(table0.C1)', None,
+ [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY DUMB_SORT(table0.C1)', None,
[('FetchStep', [('Any X,RF WHERE X title RF, X is Card',
[{'X': 'Card', 'RF': 'String'}])],
[self.cards, self.system], {},
@@ -1897,7 +1897,7 @@
try:
self._test('Any X,AA ORDERBY AA WHERE E eid %(x)s, E see_also X, X modification_date AA',
[('AggrStep',
- 'SELECT table0.C0, table0.C1 FROM table0 ORDER BY table0.C1',
+ 'SELECT table0.C0, table0.C1 FROM table0\nORDER BY table0.C1',
None,
[('FetchStep',
[('Any X,AA WHERE 999999 see_also X, X modification_date AA, X is Note',
@@ -2071,7 +2071,7 @@
try:
self._test('Any X,AA ORDERBY AA WHERE E eid %(x)s, E see_also X, X modification_date AA',
[('AggrStep',
- 'SELECT table0.C0, table0.C1 FROM table0 ORDER BY table0.C1',
+ 'SELECT table0.C0, table0.C1 FROM table0\nORDER BY table0.C1',
None,
[('FetchStep',
[('Any X,AA WHERE 999999 see_also X, X modification_date AA, X is Note',
@@ -2118,7 +2118,7 @@
('FetchStep', [('Any X,D WHERE X modification_date D, X is CWUser',
[{'X': 'CWUser', 'D': 'Datetime'}])],
[self.ldap, self.system], None, {'X': 'table1.C0', 'X.modification_date': 'table1.C1', 'D': 'table1.C1'}, []),
- ('AggrStep', 'SELECT table2.C0 FROM table2 ORDER BY table2.C1 DESC', None, [
+ ('AggrStep', 'SELECT table2.C0 FROM table2\nORDER BY table2.C1 DESC', None, [
('FetchStep', [('Any X,D WHERE E eid %s, E wf_info_for X, X modification_date D, E is TrInfo, X is Affaire'%treid,
[{'X': 'Affaire', 'E': 'TrInfo', 'D': 'Datetime'}])],
[self.system],
@@ -2267,7 +2267,7 @@
[{'X': 'Note', 'Z': 'Datetime'}])],
[self.cards, self.system], None, {'X': 'table0.C0', 'X.modification_date': 'table0.C1', 'Z': 'table0.C1'},
[]),
- ('AggrStep', 'SELECT table1.C0 FROM table1 ORDER BY table1.C1 DESC', None,
+ ('AggrStep', 'SELECT table1.C0 FROM table1\nORDER BY table1.C1 DESC', None,
[('FetchStep', [('Any X,Z WHERE X modification_date Z, 999999 see_also X, X is Bookmark',
[{'X': 'Bookmark', 'Z': 'Datetime'}])],
[self.system], {}, {'X': 'table1.C0', 'X.modification_date': 'table1.C1',
--- a/server/test/unittest_rql2sql.py Thu Mar 24 13:31:12 2011 +0100
+++ b/server/test/unittest_rql2sql.py Tue Mar 22 15:11:38 2011 +0100
@@ -18,6 +18,7 @@
"""unit tests for module cubicweb.server.sources.rql2sql"""
import sys
+import os
from logilab.common.testlib import TestCase, unittest_main, mock_object
@@ -37,6 +38,24 @@
except AssertionError, ex:
pass # already registered
+from logilab import database as db
+def monkey_patch_import_driver_module(driver, drivers, quiet=True):
+ if not driver in drivers:
+ raise db.UnknownDriver(driver)
+ for modname in drivers[driver]:
+ try:
+ if not quiet:
+ print >> sys.stderr, 'Trying %s' % modname
+ module = db.load_module_from_name(modname, use_sys=False)
+ break
+ except ImportError:
+ if not quiet:
+ print >> sys.stderr, '%s is not available' % modname
+ continue
+ else:
+ return None, drivers[driver][0]
+ return module, modname
+
def setUpModule():
global config, schema
@@ -46,10 +65,14 @@
schema['in_state'].inlined = True
schema['state_of'].inlined = False
schema['comments'].inlined = False
+ db._backup_import_driver_module = db._import_driver_module
+ db._import_driver_module = monkey_patch_import_driver_module
def tearDownModule():
global config, schema
del config, schema
+ db._import_driver_module = db._backup_import_driver_module
+ del db._backup_import_driver_module
PARSER = [
(r"Personne P WHERE P nom 'Zig\'oto';",
@@ -93,12 +116,6 @@
("Personne P WHERE P eid -1",
'''SELECT -1'''),
- ("Personne P LIMIT 20 OFFSET 10",
- '''SELECT _P.cw_eid
-FROM cw_Personne AS _P
-LIMIT 20
-OFFSET 10'''),
-
("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';",
'''SELECT rel_travaille0.eid_from
FROM cw_Societe AS _S, travaille_relation AS rel_travaille0
@@ -186,6 +203,14 @@
WHERE NOT (_X.cw_wikiid=_X.cw_title) AND NOT (_X.cw_title=parent)''')
]
+BASIC_WITH_LIMIT = [
+ ("Personne P LIMIT 20 OFFSET 10",
+ '''SELECT _P.cw_eid
+FROM cw_Personne AS _P
+LIMIT 20
+OFFSET 10'''),
+ ]
+
ADVANCED = [
("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'",
@@ -279,12 +304,6 @@
FROM cw_Note AS _S, cw_Personne AS _O
WHERE (_S.cw_ecrit_par IS NULL OR _S.cw_ecrit_par!=_O.cw_eid) AND _S.cw_eid=1 AND _S.cw_inline1 IS NOT NULL AND _O.cw_inline2=_S.cw_inline1'''),
- ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',
- '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1
-FROM cw_Note AS _S
-WHERE _S.cw_ecrit_par IS NULL
-ORDER BY 2) AS T1'''),
-
('Any N WHERE N todo_by U, N is Note, U eid 2, N filed_under T, T eid 3',
# N would actually be invarient if U eid 2 had given a specific type to U
'''SELECT _N.cw_eid
@@ -333,13 +352,6 @@
WHERE rel_tags0.eid_to=_X.cw_eid AND _X.cw_in_state=32
GROUP BY _X.cw_eid'''),
- ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))',
- '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom
-FROM concerne_relation AS rel_concerne0, cw_Societe AS _C
-WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
-GROUP BY _C.cw_nom
-ORDER BY 1 DESC
-LIMIT 10'''),
('Any X WHERE Y evaluee X, Y is CWUser',
'''SELECT rel_evaluee0.eid_to
@@ -435,13 +447,6 @@
GROUP BY _X.cw_data_name,_X.cw_data_format
ORDER BY 1,2,_X.cw_data_format'''),
- ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X data_name N, X data D, X data_format DF;',
- '''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))) AS C0, _X.cw_data_name AS C1, _X.cw_data_format AS C2
-FROM cw_File AS _X
-GROUP BY _X.cw_data_name,_X.cw_data_format
-ORDER BY 2,3) AS T1
-'''),
-
# ambiguity in EXISTS() -> should union the sub-query
('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))',
'''SELECT _T.cw_eid
@@ -512,6 +517,72 @@
GROUP BY rel_owned_by0.eid_to
HAVING COUNT(rel_owned_by0.eid_from)>10'''),
+
+ ("Any X WHERE X eid 0, X test TRUE",
+ '''SELECT _X.cw_eid
+FROM cw_Personne AS _X
+WHERE _X.cw_eid=0 AND _X.cw_test=TRUE'''),
+
+ ('Any 1 WHERE X in_group G, X is CWUser',
+ '''SELECT 1
+FROM in_group_relation AS rel_in_group0'''),
+
+ ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"',
+ '''SELECT _X.cw_eid
+FROM cw_CWEType AS _X
+WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''),
+ ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"',
+ '''SELECT _X.cw_eid
+FROM cw_CWEType AS _X
+WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''),
+ ]
+
+ADVANCED_WITH_GROUP_CONCAT = [
+ ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup",
+ '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name)
+FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
+WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
+GROUP BY _X.cw_eid,_X.cw_name
+ORDER BY _X.cw_name'''),
+
+ ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN",
+ '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
+FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
+WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
+UNION ALL
+SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
+FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
+WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
+UNION ALL
+SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
+FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0
+WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1
+GROUP BY T1.C0,T1.C2
+ORDER BY T1.C2'''),
+
+]
+
+ADVANCED_WITH_LIMIT_OR_ORDERBY = [
+ ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))',
+ '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom
+FROM concerne_relation AS rel_concerne0, cw_Societe AS _C
+WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
+GROUP BY _C.cw_nom
+ORDER BY 1 DESC
+LIMIT 10'''),
+ ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',
+ '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1
+FROM cw_Note AS _S
+WHERE _S.cw_ecrit_par IS NULL
+ORDER BY 2) AS T1'''),
+
+ ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X data_name N, X data D, X data_format DF;',
+ '''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))) AS C0, _X.cw_data_name AS C1, _X.cw_data_format AS C2
+FROM cw_File AS _X
+GROUP BY _X.cw_data_name,_X.cw_data_format
+ORDER BY 2,3) AS T1
+'''),
+
('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X',
'''SELECT T1.C0 FROM (SELECT DISTINCT _U.cw_login AS C0, STOCKPROC(_U.cw_login) AS C1
FROM cw_CWUser AS _U
@@ -546,48 +617,8 @@
ORDER BY 4 DESC'''),
- ("Any X WHERE X eid 0, X test TRUE",
- '''SELECT _X.cw_eid
-FROM cw_Personne AS _X
-WHERE _X.cw_eid=0 AND _X.cw_test=TRUE'''),
-
- ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup",
- '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name)
-FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
-WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
-GROUP BY _X.cw_eid,_X.cw_name
-ORDER BY _X.cw_name'''),
-
- ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN",
- '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
-FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
-WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
-UNION ALL
-SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
-FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
-WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
-UNION ALL
-SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
-FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0
-WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1
-GROUP BY T1.C0,T1.C2
-ORDER BY T1.C2'''),
-
- ('Any 1 WHERE X in_group G, X is CWUser',
- '''SELECT 1
-FROM in_group_relation AS rel_in_group0'''),
-
- ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"',
- '''SELECT _X.cw_eid
-FROM cw_CWEType AS _X
-WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''),
- ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"',
- '''SELECT _X.cw_eid
-FROM cw_CWEType AS _X
-WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''),
]
-
MULTIPLE_SEL = [
("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;",
'''SELECT DISTINCT _P.cw_nom, _P.cw_prenom
@@ -712,12 +743,39 @@
'''SELECT _S.cw_eid
FROM cw_State AS _S
WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''),
+ ]
+
+HAS_TEXT_LG_INDEXER = [
+ ('Any X WHERE X has_text "toto tata"',
+ """SELECT DISTINCT appears0.uid
+FROM appears AS appears0
+WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""),
+ ('Personne X WHERE X has_text "toto tata"',
+ """SELECT DISTINCT _X.eid
+FROM appears AS appears0, entities AS _X
+WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.eid AND _X.type='Personne'"""),
+ ('Personne X WHERE X has_text %(text)s',
+ """SELECT DISTINCT _X.eid
+FROM appears AS appears0, entities AS _X
+WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo')) AND appears0.uid=_X.eid AND _X.type='Personne'
+"""),
+ ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)',
+ """SELECT DISTINCT _X.cw_eid
+FROM appears AS appears0, cw_Basket AS _X
+WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu
+UNION
+SELECT DISTINCT _X.cw_eid
+FROM appears AS appears0, cw_Folder AS _X
+WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu""")
+ ]
+
+
# XXXFIXME fail
# ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute',
# '''SELECT _X.cw_eid, _X.cw_relation_type
# FROM cw_CWRelation AS _X'''),
-]
+
OUTER_JOIN = [
('Any X,S WHERE X travaille S?',
@@ -965,20 +1023,22 @@
WHERE (rel_connait0.eid_from=_X.cw_eid AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=_X.cw_eid AND rel_connait0.eid_from=_P.cw_eid) AND _P.cw_nom=nom'''
),
- ('Any X ORDERBY X DESC LIMIT 9 WHERE E eid 0, E connait X',
+ ('DISTINCT Any P WHERE P connait S OR S connait P, S nom "chouette"',
+ '''SELECT DISTINCT _P.cw_eid
+FROM connait_relation AS rel_connait0, cw_Personne AS _P, cw_Personne AS _S
+WHERE (rel_connait0.eid_from=_P.cw_eid AND rel_connait0.eid_to=_S.cw_eid OR rel_connait0.eid_to=_P.cw_eid AND rel_connait0.eid_from=_S.cw_eid) AND _S.cw_nom=chouette'''
+ )
+ ]
+
+SYMMETRIC_WITH_LIMIT = [
+ ('Any X ORDERBY X DESC LIMIT 9 WHERE E eid 0, E connait X',
'''SELECT DISTINCT _X.cw_eid
FROM connait_relation AS rel_connait0, cw_Personne AS _X
WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_X.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_X.cw_eid)
ORDER BY 1 DESC
LIMIT 9'''
),
-
- ('DISTINCT Any P WHERE P connait S OR S connait P, S nom "chouette"',
- '''SELECT DISTINCT _P.cw_eid
-FROM connait_relation AS rel_connait0, cw_Personne AS _P, cw_Personne AS _S
-WHERE (rel_connait0.eid_from=_P.cw_eid AND rel_connait0.eid_to=_S.cw_eid OR rel_connait0.eid_to=_P.cw_eid AND rel_connait0.eid_from=_S.cw_eid) AND _S.cw_nom=chouette'''
- )
- ]
+]
INLINE = [
@@ -1244,11 +1304,11 @@
yield t
def test_basic_parse(self):
- for t in self._parse(BASIC):
+ for t in self._parse(BASIC + BASIC_WITH_LIMIT):
yield t
def test_advanced_parse(self):
- for t in self._parse(ADVANCED):
+ for t in self._parse(ADVANCED + ADVANCED_WITH_LIMIT_OR_ORDERBY + ADVANCED_WITH_GROUP_CONCAT):
yield t
def test_outer_join_parse(self):
@@ -1357,7 +1417,7 @@
self.assertRaises(BadRQLQuery, self.o.generate, rqlst)
def test_symmetric(self):
- for t in self._parse(SYMMETRIC):
+ for t in self._parse(SYMMETRIC + SYMMETRIC_WITH_LIMIT):
yield t
def test_inline(self):
@@ -1509,6 +1569,111 @@
'''SELECT 1
WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''')
+class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC):
+ backend = 'sqlserver2005'
+ def _norm_sql(self, sql):
+ return sql.strip().replace(' SUBSTR', ' SUBSTRING').replace(' || ', ' + ').replace(' ILIKE ', ' LIKE ')
+
+ def test_has_text(self):
+ for t in self._parse(HAS_TEXT_LG_INDEXER):
+ yield t
+
+ def test_or_having_fake_terms(self):
+ self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL',
+ '''SELECT _X.cw_eid
+FROM cw_CWUser AS _X
+WHERE ((YEAR(_X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''')
+
+ def test_date_extraction(self):
+ self._check("Any MONTH(D) WHERE P is Personne, P creation_date D",
+ '''SELECT MONTH(_P.cw_creation_date)
+FROM cw_Personne AS _P''')
+
+ def test_symmetric(self):
+ for t in self._parse(SYMMETRIC):
+ yield t
+
+ def test_basic_parse(self):
+ for t in self._parse(BASIC):# + BASIC_WITH_LIMIT):
+ yield t
+
+ def test_advanced_parse(self):
+ for t in self._parse(ADVANCED):# + ADVANCED_WITH_LIMIT_OR_ORDERBY):
+ yield t
+
+ def test_limit_offset(self):
+ WITH_LIMIT = [
+ ("Personne P LIMIT 20 OFFSET 10",
+ '''WITH orderedrows AS (
+SELECT
+_L01
+, ROW_NUMBER() OVER (ORDER BY _L01) AS __RowNumber
+FROM (
+SELECT _P.cw_eid AS _L01 FROM cw_Personne AS _P
+) AS _SQ1 )
+SELECT
+_L01
+FROM orderedrows WHERE
+__RowNumber <= 30 AND __RowNumber > 10
+ '''),
+
+ ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))',
+ '''WITH orderedrows AS (
+SELECT
+_L01, _L02
+, ROW_NUMBER() OVER (ORDER BY _L01 DESC) AS __RowNumber
+FROM (
+SELECT COUNT(rel_concerne0.eid_from) AS _L01, _C.cw_nom AS _L02 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C
+WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published)))
+GROUP BY _C.cw_nom
+) AS _SQ1 )
+SELECT
+_L01, _L02
+FROM orderedrows WHERE
+__RowNumber <= 10
+ '''),
+
+ ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X data_name N, X data D, X data_format DF;',
+ '''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))) AS C0, _X.cw_data_name AS C1, _X.cw_data_format AS C2
+FROM cw_File AS _X
+GROUP BY _X.cw_data_name,_X.cw_data_format) AS T1
+ORDER BY T1.C1,T1.C2
+'''),
+
+
+ ('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y',
+ '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _X.cw_login AS C1
+FROM bookmarked_by_relation AS rel_bookmarked_by0, cw_CWUser AS _X
+WHERE rel_bookmarked_by0.eid_to=_X.cw_eid) AS T1
+ORDER BY T1.C1
+ '''),
+
+ ('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN',
+ '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1
+FROM cw_Affaire AS _X, cw_State AS _S
+WHERE _X.cw_in_state=_S.cw_eid
+UNION
+SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1
+FROM cw_CWUser AS _X, cw_State AS _S
+WHERE _X.cw_in_state=_S.cw_eid
+UNION
+SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1
+FROM cw_Note AS _X, cw_State AS _S
+WHERE _X.cw_in_state=_S.cw_eid) AS T1
+ORDER BY T1.C1'''),
+
+ ('Any O,AA,AB,AC ORDERBY AC DESC '
+ 'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, '
+ 'EXISTS(A use_email O, EXISTS(A identity B, NOT B in_group D, D name "guests", D is CWGroup), A is CWUser), B eid 2',
+ '''
+SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date
+FROM cw_EmailAddress AS _O
+WHERE NOT (EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email0 WHERE rel_use_email0.eid_from=1 AND rel_use_email0.eid_to=_O.cw_eid)) AND EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email1 WHERE rel_use_email1.eid_to=_O.cw_eid AND EXISTS(SELECT 1 FROM cw_CWGroup AS _D WHERE rel_use_email1.eid_from=2 AND NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group2 WHERE rel_in_group2.eid_from=2 AND rel_in_group2.eid_to=_D.cw_eid)) AND _D.cw_name=guests))
+ORDER BY 4 DESC'''),
+ ]
+ for t in self._parse(WITH_LIMIT):# + ADVANCED_WITH_LIMIT_OR_ORDERBY):
+ yield t
+
class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC):