43 cross RDMS note : read `Comparison of different SQL implementations`_ |
43 cross RDMS note : read `Comparison of different SQL implementations`_ |
44 by Troels Arvin. Features SQL ISO Standard, PG, mysql, Oracle, MS SQL, DB2 |
44 by Troels Arvin. Features SQL ISO Standard, PG, mysql, Oracle, MS SQL, DB2 |
45 and Informix. |
45 and Informix. |
46 |
46 |
47 .. _Comparison of different SQL implementations: http://www.troels.arvin.dk/db/rdbms |
47 .. _Comparison of different SQL implementations: http://www.troels.arvin.dk/db/rdbms |
48 |
|
49 |
|
50 """ |
48 """ |
|
49 |
51 __docformat__ = "restructuredtext en" |
50 __docformat__ = "restructuredtext en" |
52 |
51 |
53 import threading |
52 import threading |
54 |
53 |
55 from logilab.database import FunctionDescr, SQL_FUNCTIONS_REGISTRY |
54 from logilab.database import FunctionDescr, SQL_FUNCTIONS_REGISTRY |
56 |
55 |
57 from rql import BadRQLQuery, CoercionError |
56 from rql import BadRQLQuery, CoercionError |
58 from rql.stmts import Union, Select |
57 from rql.stmts import Union, Select |
59 from rql.nodes import (SortTerm, VariableRef, Constant, Function, Not, |
58 from rql.nodes import (SortTerm, VariableRef, Constant, Function, Variable, Or, |
60 Variable, ColumnAlias, Relation, SubQuery, Exists) |
59 Not, Comparison, ColumnAlias, Relation, SubQuery, Exists) |
61 |
60 |
62 from cubicweb import QueryError |
61 from cubicweb import QueryError |
63 from cubicweb.server.sqlutils import SQL_PREFIX |
62 from cubicweb.server.sqlutils import SQL_PREFIX |
64 from cubicweb.server.utils import cleanup_solutions |
63 from cubicweb.server.utils import cleanup_solutions |
65 |
64 |
395 self.scope_nodes.pop() |
394 self.scope_nodes.pop() |
396 restrictions = self.restrictions |
395 restrictions = self.restrictions |
397 self.restrictions = self._restr_stack.pop() |
396 self.restrictions = self._restr_stack.pop() |
398 return restrictions, self.actual_tables.pop() |
397 return restrictions, self.actual_tables.pop() |
399 |
398 |
|
399 def extract_fake_having_terms(having): |
|
400 """RQL's HAVING may be used to contains stuff that should go in the WHERE |
|
401 clause of the SQL query, due to RQL grammar limitation. Split them... |
|
402 |
|
403 Return a list nodes that can be ANDed with query's WHERE clause. Having |
|
404 subtrees updated in place. |
|
405 """ |
|
406 fakehaving = [] |
|
407 for subtree in having: |
|
408 ors, tocheck = set(), [] |
|
409 for compnode in subtree.get_nodes(Comparison): |
|
410 for fnode in compnode.get_nodes(Function): |
|
411 if fnode.descr().aggregat: |
|
412 p = compnode.parent |
|
413 oor = None |
|
414 while not isinstance(p, Select): |
|
415 if isinstance(p, Or): |
|
416 oor = p |
|
417 p = p.parent |
|
418 if oor is not None: |
|
419 ors.add(oor) |
|
420 break |
|
421 else: |
|
422 tocheck.append(compnode) |
|
423 # tocheck hold a set of comparison not implying an aggregat function |
|
424 # put them in fakehaving if the don't share an Or node as ancestor |
|
425 # with another comparison containing an aggregat function |
|
426 for compnode in tocheck: |
|
427 parents = set() |
|
428 p = compnode.parent |
|
429 oor = None |
|
430 while not isinstance(p, Select): |
|
431 if p in ors: |
|
432 break |
|
433 if isinstance(p, Or): |
|
434 oor = p |
|
435 p = p.parent |
|
436 else: |
|
437 node = oor or compnode |
|
438 if not node in fakehaving: |
|
439 fakehaving.append(node) |
|
440 compnode.parent.remove(node) |
|
441 return fakehaving |
400 |
442 |
401 class SQLGenerator(object): |
443 class SQLGenerator(object): |
402 """ |
444 """ |
403 generation of SQL from the fully expanded RQL syntax tree |
445 generation of SQL from the fully expanded RQL syntax tree |
404 SQL is designed to be used with a CubicWeb SQL schema |
446 SQL is designed to be used with a CubicWeb SQL schema |
492 """ |
534 """ |
493 distinct = selectsortterms = select.need_distinct |
535 distinct = selectsortterms = select.need_distinct |
494 sorts = select.orderby |
536 sorts = select.orderby |
495 groups = select.groupby |
537 groups = select.groupby |
496 having = select.having |
538 having = select.having |
|
539 morerestr = extract_fake_having_terms(having) |
497 # remember selection, it may be changed and have to be restored |
540 # remember selection, it may be changed and have to be restored |
498 origselection = select.selection[:] |
541 origselection = select.selection[:] |
499 # check if the query will have union subquery, if it need sort term |
542 # check if the query will have union subquery, if it need sort term |
500 # selection (union or distinct query) and wrapping (union with groups) |
543 # selection (union or distinct query) and wrapping (union with groups) |
501 needwrap = False |
544 needwrap = False |
543 if fneedwrap: |
586 if fneedwrap: |
544 needalias = True |
587 needalias = True |
545 self._in_wrapping_query = False |
588 self._in_wrapping_query = False |
546 self._state = state |
589 self._state = state |
547 try: |
590 try: |
548 sql = self._solutions_sql(select, sols, distinct, needalias or needwrap) |
591 sql = self._solutions_sql(select, morerestr, sols, distinct, |
|
592 needalias or needwrap) |
549 # generate groups / having before wrapping query selection to |
593 # generate groups / having before wrapping query selection to |
550 # get correct column aliases |
594 # get correct column aliases |
551 self._in_wrapping_query = needwrap |
595 self._in_wrapping_query = needwrap |
552 if groups: |
596 if groups: |
553 # no constant should be inserted in GROUP BY else the backend will |
597 # no constant should be inserted in GROUP BY else the backend will |
606 update_source_cb_stack(state, select, vref, stack) |
650 update_source_cb_stack(state, select, vref, stack) |
607 state.subquery_source_cb[selectidx] = stack |
651 state.subquery_source_cb[selectidx] = stack |
608 except KeyError: |
652 except KeyError: |
609 continue |
653 continue |
610 |
654 |
611 def _solutions_sql(self, select, solutions, distinct, needalias): |
655 def _solutions_sql(self, select, morerestr, solutions, distinct, needalias): |
612 sqls = [] |
656 sqls = [] |
613 for solution in solutions: |
657 for solution in solutions: |
614 self._state.reset(solution) |
658 self._state.reset(solution) |
615 # visit restriction subtree |
659 # visit restriction subtree |
616 if select.where is not None: |
660 if select.where is not None: |
617 self._state.add_restriction(select.where.accept(self)) |
661 self._state.add_restriction(select.where.accept(self)) |
|
662 for restriction in morerestr: |
|
663 self._state.add_restriction(restriction.accept(self)) |
618 sql = [self._selection_sql(select.selection, distinct, needalias)] |
664 sql = [self._selection_sql(select.selection, distinct, needalias)] |
619 if self._state.restrictions: |
665 if self._state.restrictions: |
620 sql.append('WHERE %s' % ' AND '.join(self._state.restrictions)) |
666 sql.append('WHERE %s' % ' AND '.join(self._state.restrictions)) |
621 self._state.merge_source_cbs(self._state._needs_source_cb) |
667 self._state.merge_source_cbs(self._state._needs_source_cb) |
622 # add required tables |
668 # add required tables |