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

authorAlexandre Fayolle <alexandre.fayolle@logilab.fr>
changesetbcdf22734059
branchstable
phasepublic
hiddenno
parent revision#5ea4bf53eff2 backport oldstable
child revision#73b3e0e095d3 [cwprops] add set_property method on CWUser instances, to ease setting of user specific properties, #611663348158 [merge] backport stable fixes into default
files modified by this revision
__pkginfo__.py
debian/control
server/mssteps.py
server/sources/rql2sql.py
server/test/unittest_msplanner.py
server/test/unittest_rql2sql.py
# HG changeset patch
# User Alexandre Fayolle <alexandre.fayolle@logilab.fr>
# Date 1300803098 -3600
# Tue Mar 22 15:11:38 2011 +0100
# Branch stable
# Node ID bcdf227340591fb0cad7b3137efdbd0d89ceca91
# Parent 5ea4bf53eff2588b9cf1feb72fec0d7dae442929
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

diff --git a/__pkginfo__.py b/__pkginfo__.py
@@ -50,11 +50,11 @@
1      'simplejson': '>= 2.0.9',
2      'lxml': '',
3      'Twisted': '',
4      # XXX graphviz
5      # server dependencies
6 -    'logilab-database': '>= 1.3.3',
7 +    'logilab-database': '>= 1.4.0',
8      'pysqlite': '>= 2.5.5', # XXX install pysqlite2
9      }
10 
11  __recommends__ = {
12      'Pyro': '>= 3.9.1, < 4.0.0',
diff --git a/debian/control b/debian/control
@@ -31,11 +31,11 @@
13  Architecture: all
14  XB-Python-Version: ${python:Versions}
15  Conflicts: cubicweb-multisources
16  Replaces: cubicweb-multisources
17  Provides: cubicweb-multisources
18 -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
19 +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
20  Recommends: pyro (<< 4.0.0), cubicweb-documentation (= ${source:Version})
21  Description: server part of the CubicWeb framework
22   CubicWeb is a semantic web application framework.
23   .
24   This package provides the repository server part of the system.
diff --git a/server/mssteps.py b/server/mssteps.py
@@ -160,10 +160,11 @@
25              sql = 'INSERT INTO %s %s' % (self.outputtable, sql)
26          return self.plan.sqlexec(sql, self.plan.args)
27 
28      def get_sql(self):
29          self.inputmap = inputmap = self.children[-1].outputmap
30 +        dbhelper=self.plan.syssource.dbhelper
31          # get the select clause
32          clause = []
33          for i, term in enumerate(self.selection):
34              try:
35                  var_name = inputmap[term.as_string()]
@@ -216,16 +217,20 @@
36                  if grouped is not None:
37                      for vref in sortterm.iget_nodes(VariableRef):
38                          if not vref.name in grouped:
39                              sql[-1] += ', ' + self.inputmap[vref.name]
40                              grouped.add(vref.name)
41 -            sql.append('ORDER BY %s' % ', '.join(clause))
42 -        if self.limit:
43 -            sql.append('LIMIT %s' % self.limit)
44 -        if self.offset:
45 -            sql.append('OFFSET %s' % self.offset)
46 -        return ' '.join(sql)
47 +            sql = dbhelper.sql_add_order_by(' '.join(sql),
48 +                                            clause,
49 +                                            None, False,
50 +                                            self.limit or self.offset)
51 +        else:
52 +            sql = ' '.join(sql)
53 +            clause = None
54 +
55 +        sql = dbhelper.sql_add_limit_offset(sql, self.limit, self.offset, clause)
56 +        return sql
57 
58      def visit_function(self, function):
59          """generate SQL name for a function"""
60          try:
61              return self.children[0].outputmap[str(function)]
diff --git a/server/sources/rql2sql.py b/server/sources/rql2sql.py
@@ -618,28 +618,34 @@
62                  sql += '\nGROUP BY %s' % groups
63              if having:
64                  sql += '\nHAVING %s' % having
65              # sort
66              if sorts:
67 -                sqlsortterms = [self._sortterm_sql(sortterm, fselectidx)
68 -                                for sortterm in sorts]
69 -                sqlsortterms = [x for x in sqlsortterms if x is not None]
70 +                sqlsortterms = []
71 +                for sortterm in sorts:
72 +                    _term = self._sortterm_sql(sortterm, fselectidx)
73 +                    if _term is not None:
74 +                        sqlsortterms.append(_term)
75                  if sqlsortterms:
76 -                    sql += '\nORDER BY %s' % ','.join(sqlsortterms)
77 -                    if sorts and fneedwrap:
78 -                        selection = ['T1.C%s' % i for i in xrange(len(origselection))]
79 -                        sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
80 +                    sql = self.dbhelper.sql_add_order_by(sql, sqlsortterms,
81 +                                                         origselection,
82 +                                                         fneedwrap,
83 +                                                         select.limit or select.offset)
84 +                    ## sql += '\nORDER BY %s' % ','.join(sqlsortterms)
85 +                    ## if sorts and fneedwrap:
86 +                    ##     selection = ['T1.C%s' % i for i in xrange(len(origselection))]
87 +                    ##     sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
88 +            else:
89 +                sqlsortterms = None
90              state.finalize_source_cbs()
91          finally:
92              select.selection = origselection
93          # limit / offset
94 -        limit = select.limit
95 -        if limit:
96 -            sql += '\nLIMIT %s' % limit
97 -        offset = select.offset
98 -        if offset:
99 -            sql += '\nOFFSET %s' % offset
100 +        sql = self.dbhelper.sql_add_limit_offset(sql,
101 +                                                 select.limit,
102 +                                                 select.offset,
103 +                                                 sqlsortterms)
104          return sql
105 
106      def _subqueries_sql(self, select, state):
107          for i, subquery in enumerate(select.with_):
108              sql = self.union_sql(subquery.query, needalias=True)
diff --git a/server/test/unittest_msplanner.py b/server/test/unittest_msplanner.py
@@ -428,11 +428,11 @@
109 
110      def test_simple_system_ldap_ordered_limit_offset(self):
111          """retrieve CWUser X from both sources and return concatenation of results
112          """
113          self._test('CWUser X ORDERBY X LIMIT 10 OFFSET 10',
114 -                   [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY table0.C0 LIMIT 10 OFFSET 10', None, [
115 +                   [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY table0.C0\nLIMIT 10\nOFFSET 10', None, [
116                         ('FetchStep', [('Any X WHERE X is CWUser', [{'X': 'CWUser'}])],
117                          [self.ldap, self.system], {}, {'X': 'table0.C0'}, []),
118                         ]),
119                     ])
120      def test_simple_system_ldap_aggregat(self):
@@ -513,20 +513,20 @@
121                       [self.system], {'X': 'table0.C0', 'X.login': 'table0.C1', 'L': 'table0.C1'}, [])
122                      ])
123 
124      def test_complex_ordered(self):
125          self._test('Any L ORDERBY L WHERE X login L',
126 -                   [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY table0.C0', None,
127 +                   [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY table0.C0', None,
128                       [('FetchStep', [('Any L WHERE X login L, X is CWUser',
129                                        [{'X': 'CWUser', 'L': 'String'}])],
130                         [self.ldap, self.system], {}, {'X.login': 'table0.C0', 'L': 'table0.C0'}, []),
131                        ])
132                      ])
133 
134      def test_complex_ordered_limit_offset(self):
135          self._test('Any L ORDERBY L LIMIT 10 OFFSET 10 WHERE X login L',
136 -                   [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY table0.C0 LIMIT 10 OFFSET 10', None,
137 +                   [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY table0.C0\nLIMIT 10\nOFFSET 10', None,
138                       [('FetchStep', [('Any L WHERE X login L, X is CWUser',
139                                        [{'X': 'CWUser', 'L': 'String'}])],
140                         [self.ldap, self.system], {}, {'X.login': 'table0.C0', 'L': 'table0.C0'}, []),
141                        ])
142                      ])
@@ -608,11 +608,11 @@
143          1. retrieve CWUser X from system and ldap sources, Person X from system source only, store
144             each result in the same temp table
145          2. return content of the table sorted
146          """
147          self._test('Any X,F ORDERBY F WHERE X firstname F',
148 -                   [('AggrStep', 'SELECT table0.C0, table0.C1 FROM table0 ORDER BY table0.C1', None,
149 +                   [('AggrStep', 'SELECT table0.C0, table0.C1 FROM table0\nORDER BY table0.C1', None,
150                       [('FetchStep', [('Any X,F WHERE X firstname F, X is CWUser',
151                                        [{'X': 'CWUser', 'F': 'String'}])],
152                         [self.ldap, self.system], {},
153                         {'X': 'table0.C0', 'X.firstname': 'table0.C1', 'F': 'table0.C1'}, []),
154                        ('FetchStep', [('Any X,F WHERE X firstname F, X is Personne',
@@ -1342,11 +1342,11 @@
155 
156      def test_has_text_orderby_rank(self):
157          self._test('Any X ORDERBY FTIRANK(X) WHERE X has_text "bla", X firstname "bla"',
158                     [('FetchStep', [('Any X WHERE X firstname "bla", X is CWUser', [{'X': 'CWUser'}])],
159                       [self.ldap, self.system], None, {'X': 'table0.C0'}, []),
160 -                    ('AggrStep', 'SELECT table1.C1 FROM table1 ORDER BY table1.C0', None, [
161 +                    ('AggrStep', 'SELECT table1.C1 FROM table1\nORDER BY table1.C0', None, [
162                          ('FetchStep', [('Any FTIRANK(X),X WHERE X has_text "bla", X is CWUser',
163                                          [{'X': 'CWUser'}])],
164                           [self.system], {'X': 'table0.C0'}, {'FTIRANK(X)': 'table1.C0', 'X': 'table1.C1'}, []),
165                          ('FetchStep', [('Any FTIRANK(X),X WHERE X has_text "bla", X firstname "bla", X is Personne',
166                                          [{'X': 'Personne'}])],
@@ -1399,20 +1399,20 @@
167                          ]),
168                      ])
169 
170      def test_sort_func(self):
171          self._test('Note X ORDERBY DUMB_SORT(RF) WHERE X type RF',
172 -                   [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY DUMB_SORT(table0.C1)', None, [
173 +                   [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY DUMB_SORT(table0.C1)', None, [
174                         ('FetchStep', [('Any X,RF WHERE X type RF, X is Note',
175                                         [{'X': 'Note', 'RF': 'String'}])],
176                          [self.cards, self.system], {}, {'X': 'table0.C0', 'X.type': 'table0.C1', 'RF': 'table0.C1'}, []),
177                         ])
178                      ])
179 
180      def test_ambigous_sort_func(self):
181          self._test('Any X ORDERBY DUMB_SORT(RF) WHERE X title RF, X is IN (Bookmark, Card, EmailThread)',
182 -                   [('AggrStep', 'SELECT table0.C0 FROM table0 ORDER BY DUMB_SORT(table0.C1)', None,
183 +                   [('AggrStep', 'SELECT table0.C0 FROM table0\nORDER BY DUMB_SORT(table0.C1)', None,
184                       [('FetchStep', [('Any X,RF WHERE X title RF, X is Card',
185                                        [{'X': 'Card', 'RF': 'String'}])],
186                         [self.cards, self.system], {},
187                         {'X': 'table0.C0', 'X.title': 'table0.C1', 'RF': 'table0.C1'}, []),
188                        ('FetchStep', [('Any X,RF WHERE X title RF, X is IN(Bookmark, EmailThread)',
@@ -1895,11 +1895,11 @@
189          self.cards.support_relations['see_also'] = True
190          self.cards.cross_relations.add('see_also')
191          try:
192              self._test('Any X,AA ORDERBY AA WHERE E eid %(x)s, E see_also X, X modification_date AA',
193                         [('AggrStep',
194 -                         'SELECT table0.C0, table0.C1 FROM table0 ORDER BY table0.C1',
195 +                         'SELECT table0.C0, table0.C1 FROM table0\nORDER BY table0.C1',
196                           None,
197                           [('FetchStep',
198                             [('Any X,AA WHERE 999999 see_also X, X modification_date AA, X is Note',
199                               [{'AA': 'Datetime', 'X': 'Note'}])], [self.cards, self.system], {},
200                             {'AA': 'table0.C1', 'X': 'table0.C0',
@@ -2069,11 +2069,11 @@
201          self.cards.support_relations['see_also'] = True
202          self.cards.cross_relations.add('see_also')
203          try:
204              self._test('Any X,AA ORDERBY AA WHERE E eid %(x)s, E see_also X, X modification_date AA',
205                         [('AggrStep',
206 -                         'SELECT table0.C0, table0.C1 FROM table0 ORDER BY table0.C1',
207 +                         'SELECT table0.C0, table0.C1 FROM table0\nORDER BY table0.C1',
208                           None,
209                           [('FetchStep',
210                             [('Any X,AA WHERE 999999 see_also X, X modification_date AA, X is Note',
211                               [{'AA': 'Datetime', 'X': 'Note'}])], [self.cards, self.system], {},
212                             {'AA': 'table0.C1', 'X': 'table0.C0',
@@ -2116,11 +2116,11 @@
213                                      [{'X': 'Note', 'D': 'Datetime'}])],
214                       [self.cards, self.system], None, {'X': 'table0.C0', 'X.modification_date': 'table0.C1', 'D': 'table0.C1'}, []),
215                      ('FetchStep', [('Any X,D WHERE X modification_date D, X is CWUser',
216                                      [{'X': 'CWUser', 'D': 'Datetime'}])],
217                       [self.ldap, self.system], None, {'X': 'table1.C0', 'X.modification_date': 'table1.C1', 'D': 'table1.C1'}, []),
218 -                    ('AggrStep', 'SELECT table2.C0 FROM table2 ORDER BY table2.C1 DESC', None, [
219 +                    ('AggrStep', 'SELECT table2.C0 FROM table2\nORDER BY table2.C1 DESC', None, [
220                          ('FetchStep', [('Any X,D WHERE E eid %s, E wf_info_for X, X modification_date D, E is TrInfo, X is Affaire'%treid,
221                                          [{'X': 'Affaire', 'E': 'TrInfo', 'D': 'Datetime'}])],
222                           [self.system],
223                           {},
224                           {'X': 'table2.C0', 'X.modification_date': 'table2.C1', 'D': 'table2.C1', 'E.wf_info_for': 'table2.C0'}, []),
@@ -2265,11 +2265,11 @@
225          self._test('Any X ORDERBY Z DESC WHERE X modification_date Z, E eid %(x)s, E see_also X',
226                     [('FetchStep', [('Any X,Z WHERE X modification_date Z, X is Note',
227                                      [{'X': 'Note', 'Z': 'Datetime'}])],
228                       [self.cards, self.system], None, {'X': 'table0.C0', 'X.modification_date': 'table0.C1', 'Z': 'table0.C1'},
229                       []),
230 -                    ('AggrStep', 'SELECT table1.C0 FROM table1 ORDER BY table1.C1 DESC', None,
231 +                    ('AggrStep', 'SELECT table1.C0 FROM table1\nORDER BY table1.C1 DESC', None,
232                       [('FetchStep', [('Any X,Z WHERE X modification_date Z, 999999 see_also X, X is Bookmark',
233                                        [{'X': 'Bookmark', 'Z': 'Datetime'}])],
234                         [self.system], {},   {'X': 'table1.C0', 'X.modification_date': 'table1.C1',
235                                               'Z': 'table1.C1'},
236                         []),
diff --git a/server/test/unittest_rql2sql.py b/server/test/unittest_rql2sql.py
@@ -16,10 +16,11 @@
237  # You should have received a copy of the GNU Lesser General Public License along
238  # with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.
239  """unit tests for module cubicweb.server.sources.rql2sql"""
240 
241  import sys
242 +import os
243 
244  from logilab.common.testlib import TestCase, unittest_main, mock_object
245 
246  from rql import BadRQLQuery
247  from rql.utils import register_function, FunctionDescr
@@ -35,23 +36,45 @@
248  try:
249      register_function(stockproc)
250  except AssertionError, ex:
251      pass # already registered
252 
253 +from logilab import database as db
254 +def monkey_patch_import_driver_module(driver, drivers, quiet=True):
255 +    if not driver in drivers:
256 +        raise db.UnknownDriver(driver)
257 +    for modname in drivers[driver]:
258 +        try:
259 +            if not quiet:
260 +                print >> sys.stderr, 'Trying %s' % modname
261 +            module = db.load_module_from_name(modname, use_sys=False)
262 +            break
263 +        except ImportError:
264 +            if not quiet:
265 +                print >> sys.stderr, '%s is not available' % modname
266 +            continue
267 +    else:
268 +        return None, drivers[driver][0]
269 +    return module, modname
270 +
271 
272  def setUpModule():
273      global config, schema
274      config = TestServerConfiguration('data', apphome=CWRQLTC.datadir)
275      config.bootstrap_cubes()
276      schema = config.load_schema()
277      schema['in_state'].inlined = True
278      schema['state_of'].inlined = False
279      schema['comments'].inlined = False
280 +    db._backup_import_driver_module = db._import_driver_module
281 +    db._import_driver_module = monkey_patch_import_driver_module
282 
283  def tearDownModule():
284      global config, schema
285      del config, schema
286 +    db._import_driver_module = db._backup_import_driver_module
287 +    del db._backup_import_driver_module
288 
289  PARSER = [
290      (r"Personne P WHERE P nom 'Zig\'oto';",
291       '''SELECT _P.cw_eid
292  FROM cw_Personne AS _P
@@ -91,16 +114,10 @@
293  WHERE _P.cw_test=FALSE'''),
294 
295      ("Personne P WHERE P eid -1",
296       '''SELECT -1'''),
297 
298 -    ("Personne P LIMIT 20 OFFSET 10",
299 -     '''SELECT _P.cw_eid
300 -FROM cw_Personne AS _P
301 -LIMIT 20
302 -OFFSET 10'''),
303 -
304      ("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';",
305       '''SELECT rel_travaille0.eid_from
306  FROM cw_Societe AS _S, travaille_relation AS rel_travaille0
307  WHERE rel_travaille0.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''),
308 
@@ -184,10 +201,18 @@
309       '''SELECT _X.cw_eid
310  FROM cw_Card AS _X
311  WHERE NOT (_X.cw_wikiid=_X.cw_title) AND NOT (_X.cw_title=parent)''')
312  ]
313 
314 +BASIC_WITH_LIMIT = [
315 +    ("Personne P LIMIT 20 OFFSET 10",
316 +     '''SELECT _P.cw_eid
317 +FROM cw_Personne AS _P
318 +LIMIT 20
319 +OFFSET 10'''),
320 +    ]
321 +
322 
323  ADVANCED = [
324      ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'",
325       '''SELECT _S.cw_eid
326  FROM cw_Societe AS _S
@@ -277,16 +302,10 @@
327      ('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P',
328       '''SELECT _O.cw_eid
329  FROM cw_Note AS _S, cw_Personne AS _O
330  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'''),
331 
332 -    ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',
333 -     '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1
334 -FROM cw_Note AS _S
335 -WHERE _S.cw_ecrit_par IS NULL
336 -ORDER BY 2) AS T1'''),
337 -
338      ('Any N WHERE N todo_by U, N is Note, U eid 2, N filed_under T, T eid 3',
339       # N would actually be invarient if U eid 2 had given a specific type to U
340       '''SELECT _N.cw_eid
341  FROM cw_Note AS _N, filed_under_relation AS rel_filed_under1, todo_by_relation AS rel_todo_by0
342  WHERE rel_todo_by0.eid_from=_N.cw_eid AND rel_todo_by0.eid_to=2 AND rel_filed_under1.eid_from=_N.cw_eid AND rel_filed_under1.eid_to=3'''),
@@ -331,17 +350,10 @@
343       '''SELECT _X.cw_eid, 32, MAX(rel_tags0.eid_from)
344  FROM cw_CWUser AS _X, tags_relation AS rel_tags0
345  WHERE rel_tags0.eid_to=_X.cw_eid AND _X.cw_in_state=32
346  GROUP BY _X.cw_eid'''),
347 
348 -    ('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"))',
349 -     '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom
350 -FROM concerne_relation AS rel_concerne0, cw_Societe AS _C
351 -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)))
352 -GROUP BY _C.cw_nom
353 -ORDER BY 1 DESC
354 -LIMIT 10'''),
355 
356      ('Any X WHERE Y evaluee X, Y is CWUser',
357       '''SELECT rel_evaluee0.eid_to
358  FROM cw_CWUser AS _Y, evaluee_relation AS rel_evaluee0
359  WHERE rel_evaluee0.eid_from=_Y.cw_eid'''),
@@ -433,17 +445,10 @@
360       '''SELECT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))), _X.cw_data_name
361  FROM cw_File AS _X
362  GROUP BY _X.cw_data_name,_X.cw_data_format
363  ORDER BY 1,2,_X.cw_data_format'''),
364 
365 -    ('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;',
366 -     '''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
367 -FROM cw_File AS _X
368 -GROUP BY _X.cw_data_name,_X.cw_data_format
369 -ORDER BY 2,3) AS T1
370 -'''),
371 -
372      # ambiguity in EXISTS() -> should union the sub-query
373      ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))',
374       '''SELECT _T.cw_eid
375  FROM cw_Tag AS _T
376  WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM tags_relation AS rel_tags0, cw_CWGroup AS _X WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM tags_relation AS rel_tags1, cw_CWUser AS _X WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''),
@@ -510,10 +515,76 @@
377  FROM owned_by_relation AS rel_owned_by0
378  WHERE rel_owned_by0.eid_to=12
379  GROUP BY rel_owned_by0.eid_to
380  HAVING COUNT(rel_owned_by0.eid_from)>10'''),
381 
382 +
383 +    ("Any X WHERE X eid 0, X test TRUE",
384 +     '''SELECT _X.cw_eid
385 +FROM cw_Personne AS _X
386 +WHERE _X.cw_eid=0 AND _X.cw_test=TRUE'''),
387 +
388 +    ('Any 1 WHERE X in_group G, X is CWUser',
389 +     '''SELECT 1
390 +FROM in_group_relation AS rel_in_group0'''),
391 +
392 +    ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"',
393 +     '''SELECT _X.cw_eid
394 +FROM cw_CWEType AS _X
395 +WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''),
396 +    ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"',
397 +     '''SELECT _X.cw_eid
398 +FROM cw_CWEType AS _X
399 +WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''),
400 +    ]
401 +
402 +ADVANCED_WITH_GROUP_CONCAT = [
403 +        ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup",
404 +     '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name)
405 +FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
406 +WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
407 +GROUP BY _X.cw_eid,_X.cw_name
408 +ORDER BY _X.cw_name'''),
409 +
410 +    ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN",
411 +     '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
412 +FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
413 +WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
414 +UNION ALL
415 +SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
416 +FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
417 +WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
418 +UNION ALL
419 +SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
420 +FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0
421 +WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1
422 +GROUP BY T1.C0,T1.C2
423 +ORDER BY T1.C2'''),
424 +
425 +]
426 +
427 +ADVANCED_WITH_LIMIT_OR_ORDERBY = [
428 +    ('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"))',
429 +     '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom
430 +FROM concerne_relation AS rel_concerne0, cw_Societe AS _C
431 +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)))
432 +GROUP BY _C.cw_nom
433 +ORDER BY 1 DESC
434 +LIMIT 10'''),
435 +    ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',
436 +     '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1
437 +FROM cw_Note AS _S
438 +WHERE _S.cw_ecrit_par IS NULL
439 +ORDER BY 2) AS T1'''),
440 +
441 +    ('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;',
442 +     '''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
443 +FROM cw_File AS _X
444 +GROUP BY _X.cw_data_name,_X.cw_data_format
445 +ORDER BY 2,3) AS T1
446 +'''),
447 +
448      ('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X',
449       '''SELECT T1.C0 FROM (SELECT DISTINCT _U.cw_login AS C0, STOCKPROC(_U.cw_login) AS C1
450  FROM cw_CWUser AS _U
451  ORDER BY 2) AS T1'''),
452 
@@ -544,52 +615,12 @@
453  FROM cw_EmailAddress AS _O
454  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))
455  ORDER BY 4 DESC'''),
456 
457 
458 -    ("Any X WHERE X eid 0, X test TRUE",
459 -     '''SELECT _X.cw_eid
460 -FROM cw_Personne AS _X
461 -WHERE _X.cw_eid=0 AND _X.cw_test=TRUE'''),
462 -
463 -    ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup",
464 -     '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name)
465 -FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
466 -WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
467 -GROUP BY _X.cw_eid,_X.cw_name
468 -ORDER BY _X.cw_name'''),
469 -
470 -    ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN",
471 -     '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
472 -FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
473 -WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
474 -UNION ALL
475 -SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
476 -FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0
477 -WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid
478 -UNION ALL
479 -SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2
480 -FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0
481 -WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1
482 -GROUP BY T1.C0,T1.C2
483 -ORDER BY T1.C2'''),
484 -
485 -    ('Any 1 WHERE X in_group G, X is CWUser',
486 -     '''SELECT 1
487 -FROM in_group_relation AS rel_in_group0'''),
488 -
489 -    ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"',
490 -     '''SELECT _X.cw_eid
491 -FROM cw_CWEType AS _X
492 -WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''),
493 -    ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"',
494 -     '''SELECT _X.cw_eid
495 -FROM cw_CWEType AS _X
496 -WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''),
497      ]
498 
499 -
500  MULTIPLE_SEL = [
501      ("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;",
502       '''SELECT DISTINCT _P.cw_nom, _P.cw_prenom
503  FROM cw_Personne AS _P'''),
504      ("Any X,Y where P is Personne, P nom X , P prenom Y, not P nom NULL;",
@@ -710,16 +741,43 @@
505 
506      ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser',
507       '''SELECT _S.cw_eid
508  FROM cw_State AS _S
509  WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''),
510 +    ]
511 +
512 +HAS_TEXT_LG_INDEXER = [
513 +            ('Any X WHERE X has_text "toto tata"',
514 +             """SELECT DISTINCT appears0.uid
515 +FROM appears AS appears0
516 +WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""),
517 +            ('Personne X WHERE X has_text "toto tata"',
518 +             """SELECT DISTINCT _X.eid
519 +FROM appears AS appears0, entities AS _X
520 +WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.eid AND _X.type='Personne'"""),
521 +            ('Personne X WHERE X has_text %(text)s',
522 +             """SELECT DISTINCT _X.eid
523 +FROM appears AS appears0, entities AS _X
524 +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'
525 +"""),
526 +            ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)',
527 +             """SELECT DISTINCT _X.cw_eid
528 +FROM appears AS appears0, cw_Basket AS _X
529 +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
530 +UNION
531 +SELECT DISTINCT _X.cw_eid
532 +FROM appears AS appears0, cw_Folder AS _X
533 +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""")
534 +        ]
535 +
536 +
537 
538  # XXXFIXME fail
539  #         ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute',
540  #      '''SELECT _X.cw_eid, _X.cw_relation_type
541  # FROM cw_CWRelation AS _X'''),
542 -]
543 +
544 
545  OUTER_JOIN = [
546      ('Any X,S WHERE X travaille S?',
547       '''SELECT _X.cw_eid, rel_travaille0.eid_to
548  FROM cw_Personne AS _X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=_X.cw_eid)'''
@@ -963,24 +1021,26 @@
549       '''SELECT DISTINCT _X.cw_eid
550  FROM connait_relation AS rel_connait0, cw_Personne AS _P, cw_Personne AS _X
551  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'''
552      ),
553 
554 -    ('Any X ORDERBY X DESC LIMIT 9 WHERE E eid 0, E connait X',
555 +    ('DISTINCT Any P WHERE P connait S OR S connait P, S nom "chouette"',
556 +     '''SELECT DISTINCT _P.cw_eid
557 +FROM connait_relation AS rel_connait0, cw_Personne AS _P, cw_Personne AS _S
558 +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'''
559 +     )
560 +    ]
561 +
562 +SYMMETRIC_WITH_LIMIT = [
563 +        ('Any X ORDERBY X DESC LIMIT 9 WHERE E eid 0, E connait X',
564      '''SELECT DISTINCT _X.cw_eid
565  FROM connait_relation AS rel_connait0, cw_Personne AS _X
566  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)
567  ORDER BY 1 DESC
568  LIMIT 9'''
569       ),
570 -
571 -    ('DISTINCT Any P WHERE P connait S OR S connait P, S nom "chouette"',
572 -     '''SELECT DISTINCT _P.cw_eid
573 -FROM connait_relation AS rel_connait0, cw_Personne AS _P, cw_Personne AS _S
574 -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'''
575 -     )
576 -    ]
577 +]
578 
579  INLINE = [
580 
581      ('Any P WHERE N eid 1, N ecrit_par P, NOT P owned_by P2',
582       '''SELECT _N.cw_ecrit_par
@@ -1242,15 +1302,15 @@
583      def test_parser_parse(self):
584          for t in self._parse(PARSER):
585              yield t
586 
587      def test_basic_parse(self):
588 -        for t in self._parse(BASIC):
589 +        for t in self._parse(BASIC + BASIC_WITH_LIMIT):
590              yield t
591 
592      def test_advanced_parse(self):
593 -        for t in self._parse(ADVANCED):
594 +        for t in self._parse(ADVANCED + ADVANCED_WITH_LIMIT_OR_ORDERBY + ADVANCED_WITH_GROUP_CONCAT):
595              yield t
596 
597      def test_outer_join_parse(self):
598          for t in self._parse(OUTER_JOIN):
599              yield t
@@ -1355,11 +1415,11 @@
600                 ' (Any X WHERE X is Transition))')
601          rqlst = self._prepare(rql)
602          self.assertRaises(BadRQLQuery, self.o.generate, rqlst)
603 
604      def test_symmetric(self):
605 -        for t in self._parse(SYMMETRIC):
606 +        for t in self._parse(SYMMETRIC + SYMMETRIC_WITH_LIMIT):
607              yield t
608 
609      def test_inline(self):
610          for t in self._parse(INLINE):
611              yield t
@@ -1507,10 +1567,115 @@
612          # X without in_group relation, or some G without it.
613          self._check('Any 1 WHERE NOT X in_group G, X is CWUser',
614                      '''SELECT 1
615  WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''')
616 
617 +class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC):
618 +    backend = 'sqlserver2005'
619 +    def _norm_sql(self, sql):
620 +        return sql.strip().replace(' SUBSTR', ' SUBSTRING').replace(' || ', ' + ').replace(' ILIKE ', ' LIKE ')
621 +
622 +    def test_has_text(self):
623 +        for t in self._parse(HAS_TEXT_LG_INDEXER):
624 +            yield t
625 +
626 +    def test_or_having_fake_terms(self):
627 +        self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL',
628 +                    '''SELECT _X.cw_eid
629 +FROM cw_CWUser AS _X
630 +WHERE ((YEAR(_X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''')
631 +
632 +    def test_date_extraction(self):
633 +        self._check("Any MONTH(D) WHERE P is Personne, P creation_date D",
634 +                    '''SELECT MONTH(_P.cw_creation_date)
635 +FROM cw_Personne AS _P''')
636 +
637 +    def test_symmetric(self):
638 +        for t in self._parse(SYMMETRIC):
639 +            yield t
640 +
641 +    def test_basic_parse(self):
642 +        for t in self._parse(BASIC):# + BASIC_WITH_LIMIT):
643 +            yield t
644 +
645 +    def test_advanced_parse(self):
646 +        for t in self._parse(ADVANCED):# + ADVANCED_WITH_LIMIT_OR_ORDERBY):
647 +            yield t
648 +
649 +    def test_limit_offset(self):
650 +        WITH_LIMIT = [
651 +    ("Personne P LIMIT 20 OFFSET 10",
652 +             '''WITH orderedrows AS (
653 +SELECT
654 +_L01
655 +, ROW_NUMBER() OVER (ORDER BY _L01) AS __RowNumber
656 +FROM (
657 +SELECT _P.cw_eid AS _L01 FROM  cw_Personne AS _P
658 +) AS _SQ1 )
659 +SELECT
660 +_L01
661 +FROM orderedrows WHERE
662 +__RowNumber <= 30 AND __RowNumber > 10
663 + '''),
664 +
665 +    ('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"))',
666 +     '''WITH orderedrows AS (
667 +SELECT
668 +_L01, _L02
669 +, ROW_NUMBER() OVER (ORDER BY _L01 DESC) AS __RowNumber
670 +FROM (
671 +SELECT COUNT(rel_concerne0.eid_from) AS _L01, _C.cw_nom AS _L02 FROM  concerne_relation AS rel_concerne0, cw_Societe AS _C
672 +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)))
673 +GROUP BY _C.cw_nom
674 +) AS _SQ1 )
675 +SELECT
676 +_L01, _L02
677 +FROM orderedrows WHERE
678 +__RowNumber <= 10
679 +     '''),
680 +
681 +    ('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;',
682 +     '''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
683 +FROM cw_File AS _X
684 +GROUP BY _X.cw_data_name,_X.cw_data_format) AS T1
685 +ORDER BY T1.C1,T1.C2
686 +'''),
687 +
688 +
689 +    ('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y',
690 +     '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _X.cw_login AS C1
691 +FROM bookmarked_by_relation AS rel_bookmarked_by0, cw_CWUser AS _X
692 +WHERE rel_bookmarked_by0.eid_to=_X.cw_eid) AS T1
693 +ORDER BY T1.C1
694 + '''),
695 +
696 +    ('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN',
697 +     '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1
698 +FROM cw_Affaire AS _X, cw_State AS _S
699 +WHERE _X.cw_in_state=_S.cw_eid
700 +UNION
701 +SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1
702 +FROM cw_CWUser AS _X, cw_State AS _S
703 +WHERE _X.cw_in_state=_S.cw_eid
704 +UNION
705 +SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1
706 +FROM cw_Note AS _X, cw_State AS _S
707 +WHERE _X.cw_in_state=_S.cw_eid) AS T1
708 +ORDER BY T1.C1'''),
709 +
710 +    ('Any O,AA,AB,AC ORDERBY AC DESC '
711 +     'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, '
712 +     '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',
713 +     '''
714 +SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date
715 +FROM cw_EmailAddress AS _O
716 +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))
717 +ORDER BY 4 DESC'''),
718 +            ]
719 +        for t in self._parse(WITH_LIMIT):# + ADVANCED_WITH_LIMIT_OR_ORDERBY):
720 +            yield t
721 +
722 
723 
724  class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC):
725      backend = 'sqlite'
726