--- a/server/sources/rql2sql.py Mon Jan 04 18:40:30 2016 +0100
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,1703 +0,0 @@
-# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
-# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
-#
-# This file is part of CubicWeb.
-#
-# CubicWeb is free software: you can redistribute it and/or modify it under the
-# terms of the GNU Lesser General Public License as published by the Free
-# Software Foundation, either version 2.1 of the License, or (at your option)
-# any later version.
-#
-# CubicWeb is distributed in the hope that it will be useful, but WITHOUT
-# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
-# FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
-# details.
-#
-# You should have received a copy of the GNU Lesser General Public License along
-# with CubicWeb. If not, see <http://www.gnu.org/licenses/>.
-"""RQL to SQL generator for native sources.
-
-
-SQL queries optimization
-~~~~~~~~~~~~~~~~~~~~~~~~
-1. CWUser X WHERE X in_group G, G name 'users':
-
- CWUser is the only subject entity type for the in_group relation,
- which allow us to do ::
-
- SELECT eid_from FROM in_group, CWGroup
- WHERE in_group.eid_to = CWGroup.eid_from
- AND CWGroup.name = 'users'
-
-
-2. Any X WHERE X nonfinal1 Y, Y nonfinal2 Z
-
- -> direct join between nonfinal1 and nonfinal2, whatever X,Y, Z (unless
- inlined...)
-
- NOT IMPLEMENTED (and quite hard to implement)
-
-Potential optimization information is collected by the querier, sql generation
-is done according to this information
-
-cross RDMS note : read `Comparison of different SQL implementations`_
-by Troels Arvin. Features SQL ISO Standard, PG, mysql, Oracle, MS SQL, DB2
-and Informix.
-
-.. _Comparison of different SQL implementations: http://www.troels.arvin.dk/db/rdbms
-"""
-
-__docformat__ = "restructuredtext en"
-
-import threading
-
-from six import PY2
-from six.moves import range
-
-from logilab.database import FunctionDescr, SQL_FUNCTIONS_REGISTRY
-
-from rql import BadRQLQuery, CoercionError
-from rql.utils import common_parent
-from rql.stmts import Union, Select
-from rql.nodes import (VariableRef, Constant, Function, Variable, Or,
- Not, Comparison, ColumnAlias, Relation, SubQuery)
-
-from cubicweb import QueryError
-from cubicweb.rqlrewrite import cleanup_solutions
-from cubicweb.server.sqlutils import SQL_PREFIX
-
-ColumnAlias._q_invariant = False # avoid to check for ColumnAlias / Variable
-
-FunctionDescr.source_execute = None
-
-def default_update_cb_stack(self, stack):
- stack.append(self.source_execute)
-FunctionDescr.update_cb_stack = default_update_cb_stack
-
-get_func_descr = SQL_FUNCTIONS_REGISTRY.get_function
-
-LENGTH = get_func_descr('LENGTH')
-def length_source_execute(source, session, value):
- return len(value.getvalue())
-LENGTH.source_execute = length_source_execute
-
-def _new_var(select, varname):
- newvar = select.get_variable(varname)
- if not 'relations' in newvar.stinfo:
- # not yet initialized
- newvar.prepare_annotation()
- newvar.stinfo['scope'] = select
- newvar._q_invariant = False
- select.selection.append(VariableRef(newvar))
- return newvar
-
-def _fill_to_wrap_rel(var, newselect, towrap, schema):
- for rel in var.stinfo['relations'] - var.stinfo['rhsrelations']:
- rschema = schema.rschema(rel.r_type)
- if rschema.inlined:
- towrap.add( (var, rel) )
- for vref in rel.children[1].iget_nodes(VariableRef):
- newivar = _new_var(newselect, vref.name)
- _fill_to_wrap_rel(vref.variable, newselect, towrap, schema)
- elif rschema.final:
- towrap.add( (var, rel) )
- for vref in rel.children[1].iget_nodes(VariableRef):
- newivar = _new_var(newselect, vref.name)
- newivar.stinfo['attrvar'] = (var, rel.r_type)
-
-def rewrite_unstable_outer_join(select, solutions, unstable, schema):
- """if some optional variables are unstable, they should be selected in a
- subquery. This function check this and rewrite the rql syntax tree if
- necessary (in place). Return a boolean telling if the tree has been modified
- """
- modified = False
- for varname in tuple(unstable):
- var = select.defined_vars[varname]
- if not var.stinfo.get('optrelations'):
- continue
- unstable.remove(varname)
- newselect = Select()
- myunion = Union()
- myunion.append(newselect)
- # extract aliases / selection
- newvar = _new_var(newselect, var.name)
- newselect.selection = [VariableRef(newvar)]
- towrap_rels = set()
- _fill_to_wrap_rel(var, newselect, towrap_rels, schema)
- # extract relations
- for var, rel in towrap_rels:
- newrel = rel.copy(newselect)
- newselect.add_restriction(newrel)
- select.remove_node(rel)
- var.stinfo['relations'].remove(rel)
- newvar.stinfo['relations'].add(newrel)
- if rel.optional in ('left', 'both'):
- newvar.add_optional_relation(newrel)
- for vref in newrel.children[1].iget_nodes(VariableRef):
- var = vref.variable
- var.stinfo['relations'].add(newrel)
- var.stinfo['rhsrelations'].add(newrel)
- if rel.optional in ('right', 'both'):
- var.add_optional_relation(newrel)
- if not select.where and not modified:
- # oops, generated the same thing as the original select....
- # restore original query, else we'll indefinitly loop
- for var, rel in towrap_rels:
- select.add_restriction(rel)
- continue
- modified = True
- # extract subquery solutions
- mysolutions = [sol.copy() for sol in solutions]
- cleanup_solutions(newselect, mysolutions)
- newselect.set_possible_types(mysolutions)
- # full sub-query
- aliases = [VariableRef(select.get_variable(avar.name, i))
- for i, avar in enumerate(newselect.selection)]
- select.add_subquery(SubQuery(aliases, myunion), check=False)
- return modified
-
-def _new_solutions(rqlst, solutions):
- """first filter out subqueries variables from solutions"""
- newsolutions = []
- for origsol in solutions:
- asol = {}
- for vname in rqlst.defined_vars:
- asol[vname] = origsol[vname]
- if not asol in newsolutions:
- newsolutions.append(asol)
- return newsolutions
-
-def remove_unused_solutions(rqlst, solutions, varmap, schema):
- """cleanup solutions: remove solutions where invariant variables are taking
- different types
- """
- newsols = _new_solutions(rqlst, solutions)
- existssols = {}
- unstable = set()
- invariants = {}
- for vname, var in rqlst.defined_vars.items():
- vtype = newsols[0][vname]
- if var._q_invariant or vname in varmap:
- # remove invariant variable from solutions to remove duplicates
- # later, then reinserting a type for the variable even later
- for sol in newsols:
- invariants.setdefault(id(sol), {})[vname] = sol.pop(vname)
- elif var.scope is not rqlst:
- # move apart variables which are in a EXISTS scope and are variating
- try:
- thisexistssols, thisexistsvars = existssols[var.scope]
- except KeyError:
- thisexistssols = [newsols[0]]
- thisexistsvars = set()
- existssols[var.scope] = thisexistssols, thisexistsvars
- for i in range(len(newsols)-1, 0, -1):
- if vtype != newsols[i][vname]:
- thisexistssols.append(newsols.pop(i))
- thisexistsvars.add(vname)
- else:
- # remember unstable variables
- for i in range(1, len(newsols)):
- if vtype != newsols[i][vname]:
- unstable.add(vname)
- if invariants:
- # filter out duplicates
- newsols_ = []
- for sol in newsols:
- if not sol in newsols_:
- newsols_.append(sol)
- newsols = newsols_
- # reinsert solutions for invariants
- for sol in newsols:
- for invvar, vartype in invariants[id(sol)].items():
- sol[invvar] = vartype
- for sol in existssols:
- try:
- for invvar, vartype in invariants[id(sol)].items():
- sol[invvar] = vartype
- except KeyError:
- continue
- if len(newsols) > 1:
- if rewrite_unstable_outer_join(rqlst, newsols, unstable, schema):
- # remove variables extracted to subqueries from solutions
- newsols = _new_solutions(rqlst, newsols)
- return newsols, existssols, unstable
-
-def relation_info(relation):
- lhs, rhs = relation.get_variable_parts()
- try:
- lhs = lhs.variable
- lhsconst = lhs.stinfo['constnode']
- except AttributeError:
- lhsconst = lhs
- lhs = None
- except KeyError:
- lhsconst = None # ColumnAlias
- try:
- rhs = rhs.variable
- rhsconst = rhs.stinfo['constnode']
- except AttributeError:
- rhsconst = rhs
- rhs = None
- except KeyError:
- rhsconst = None # ColumnAlias
- return lhs, lhsconst, rhs, rhsconst
-
-def sort_term_selection(sorts, rqlst, groups):
- # XXX beurk
- if isinstance(rqlst, list):
- def append(term):
- rqlst.append(term)
- selectionidx = set(str(term) for term in rqlst)
- else:
- def append(term):
- rqlst.selection.append(term.copy(rqlst))
- selectionidx = set(str(term) for term in rqlst.selection)
-
- for sortterm in sorts:
- term = sortterm.term
- if not isinstance(term, Constant) and not str(term) in selectionidx:
- selectionidx.add(str(term))
- append(term)
- if groups:
- for vref in term.iget_nodes(VariableRef):
- if not any(vref.is_equivalent(g) for g in groups):
- groups.append(vref)
-
-def fix_selection_and_group(rqlst, needwrap, selectsortterms,
- sorts, groups, having):
- if selectsortterms and sorts:
- sort_term_selection(sorts, rqlst, not needwrap and groups)
- groupvrefs = [vref for term in groups for vref in term.iget_nodes(VariableRef)]
- if sorts and groups:
- # when a query is grouped, ensure sort terms are grouped as well
- for sortterm in sorts:
- term = sortterm.term
- if not (isinstance(term, Constant) or \
- (isinstance(term, Function) and
- get_func_descr(term.name).aggregat)):
- for vref in term.iget_nodes(VariableRef):
- if not any(vref.is_equivalent(group) for group in groupvrefs):
- groups.append(vref)
- groupvrefs.append(vref)
- if needwrap and (groups or having):
- selectedidx = set(vref.name for term in rqlst.selection
- for vref in term.get_nodes(VariableRef))
- if groups:
- for vref in groupvrefs:
- if vref.name not in selectedidx:
- selectedidx.add(vref.name)
- rqlst.selection.append(vref)
- if having:
- for term in having:
- for vref in term.iget_nodes(VariableRef):
- if vref.name not in selectedidx:
- selectedidx.add(vref.name)
- rqlst.selection.append(vref)
-
-def iter_mapped_var_sels(stmt, variable):
- # variable is a Variable or ColumnAlias node mapped to a source side
- # callback
- if not (len(variable.stinfo['rhsrelations']) <= 1 and # < 1 on column alias
- variable.stinfo['selected']):
- raise QueryError("can't use %s as a restriction variable"
- % variable.name)
- for selectidx in variable.stinfo['selected']:
- vrefs = stmt.selection[selectidx].get_nodes(VariableRef)
- if len(vrefs) != 1:
- raise QueryError()
- yield selectidx, vrefs[0]
-
-def update_source_cb_stack(state, stmt, node, stack):
- while True:
- node = node.parent
- if node is stmt:
- break
- if not isinstance(node, Function):
- raise QueryError()
- funcd = get_func_descr(node.name)
- if funcd.source_execute is None:
- raise QueryError('%s can not be called on mapped attribute'
- % node.name)
- state.source_cb_funcs.add(node)
- funcd.update_cb_stack(stack)
-
-
-# IGenerator implementation for RQL->SQL #######################################
-
-class StateInfo(object):
- """this class stores data accumulated during the RQL syntax tree visit
- for later SQL generation.
-
- Attributes related to OUTER JOIN handling
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- * `outer_chains`, list of list of strings. Each list represent a tables
- that have to be outer joined together.
-
- * `outer_tables`, dictionary used as index of tables used in outer join ::
-
- 'table alias': (outertype, [conditions], [chain])
-
- where:
-
- * `outertype` is one of None, 'LEFT', 'RIGHT', 'FULL'
- * `conditions` is a list of join conditions (string)
- * `chain` is a list of table alias (the *outer chain*) in which the key
- alias appears
-
- * `outer_pending` is a dictionary containing some conditions that will have
- to be added to the outer join when the table will be turned into an
- outerjoin ::
-
- 'table alias': [conditions]
- """
- def __init__(self, select, existssols, unstablevars):
- self.existssols = existssols
- self.unstablevars = unstablevars
- self.subtables = {}
- self.needs_source_cb = None
- self.subquery_source_cb = None
- self.source_cb_funcs = set()
- self.scopes = {select: 0}
- self.scope_nodes = []
-
- def reset(self, solution):
- """reset some visit variables"""
- self.solution = solution
- self.count = 0
- self.done = set()
- self.tables = self.subtables.copy()
- self.actual_tables = [[]]
- for _, tsql in self.tables.values():
- self.actual_tables[-1].append(tsql)
- self.outer_chains = []
- self.outer_tables = {}
- self.outer_pending = {}
- self.duplicate_switches = []
- self.aliases = {}
- self.restrictions = []
- self._restr_stack = []
- self.ignore_varmap = False
- self._needs_source_cb = {}
-
- def merge_source_cbs(self, needs_source_cb):
- if self.needs_source_cb is None:
- self.needs_source_cb = needs_source_cb
- elif needs_source_cb != self.needs_source_cb:
- raise QueryError('query fetch some source mapped attribute, some not')
-
- def finalize_source_cbs(self):
- if self.subquery_source_cb is not None:
- self.needs_source_cb.update(self.subquery_source_cb)
-
- def add_restriction(self, restr):
- if restr:
- self.restrictions.append(restr)
-
- def iter_exists_sols(self, exists):
- if not exists in self.existssols:
- yield 1
- return
- thisexistssols, thisexistsvars = self.existssols[exists]
- notdone_outside_vars = set()
- # when iterating other solutions inner to an EXISTS subquery, we should
- # reset variables which have this exists node as scope at each iteration
- for var in exists.stmt.defined_vars.values():
- if var.scope is exists:
- thisexistsvars.add(var.name)
- elif var.name not in self.done:
- notdone_outside_vars.add(var)
- origsol = self.solution
- origtables = self.tables
- done = self.done
- for thisexistssol in thisexistssols:
- for vname in self.unstablevars:
- if thisexistssol[vname] != origsol[vname] and vname in thisexistsvars:
- break
- else:
- self.tables = origtables.copy()
- self.solution = thisexistssol
- yield 1
- # cleanup self.done from stuff specific to exists
- for var in thisexistsvars:
- if var in done:
- done.remove(var)
- for var in list(notdone_outside_vars):
- if var.name in done and var._q_sqltable in self.tables:
- origtables[var._q_sqltable] = self.tables[var._q_sqltable]
- notdone_outside_vars.remove(var)
- for rel in exists.iget_nodes(Relation):
- if rel in done:
- done.remove(rel)
- self.solution = origsol
- self.tables = origtables
-
- def push_scope(self, scope_node):
- self.scope_nodes.append(scope_node)
- self.scopes[scope_node] = len(self.actual_tables)
- self.actual_tables.append([])
- self._restr_stack.append(self.restrictions)
- self.restrictions = []
-
- def pop_scope(self):
- del self.scopes[self.scope_nodes[-1]]
- self.scope_nodes.pop()
- restrictions = self.restrictions
- self.restrictions = self._restr_stack.pop()
- scope = len(self.actual_tables) - 1
- # check if we have some outer chain for this scope
- matching_chains = []
- for chain in self.outer_chains:
- for tablealias in chain:
- if self.tables[tablealias][0] < scope:
- # chain belongs to outer scope
- break
- else:
- # chain match current scope
- matching_chains.append(chain)
- # call to `tables_sql` will pop actual_tables
- tables = self.tables_sql(matching_chains)
- # cleanup outer join related structure for tables in matching chains
- for chain in matching_chains:
- self.outer_chains.remove(chain)
- for alias in chain:
- del self.outer_tables[alias]
- return restrictions, tables
-
- # tables handling #########################################################
-
- def add_table(self, table, key=None, scope=-1):
- if key is None:
- key = table
- if key in self.tables:
- return
- if scope < 0:
- scope = len(self.actual_tables) + scope
- self.tables[key] = (scope, table)
- self.actual_tables[scope].append(table)
-
- def alias_and_add_table(self, tablename, scope=-1):
- alias = '%s%s' % (tablename, self.count)
- self.count += 1
- self.add_table('%s AS %s' % (tablename, alias), alias, scope)
- return alias
-
- def relation_table(self, relation):
- """return the table alias used by the given relation"""
- if relation in self.done:
- return relation._q_sqltable
- rid = 'rel_%s%s' % (relation.r_type, self.count)
- # relation's table is belonging to the root scope if it is the principal
- # table of one of its variable and that variable belong's to parent
- # scope
- for varref in relation.iget_nodes(VariableRef):
- var = varref.variable
- # XXX may have a principal without being invariant for this generation,
- # not sure this is a pb or not
- if var.stinfo.get('principal') is relation and var.scope is var.stmt:
- scope = 0
- break
- else:
- scope = -1
- self.count += 1
- self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope)
- relation._q_sqltable = rid
- self.done.add(relation)
- return rid
-
- def fti_table(self, relation, fti_table):
- """return the table alias used by the given has_text relation,
- `fti_table` being the table name for the plain text index
- """
- if relation in self.done:
- try:
- return relation._q_sqltable
- except AttributeError:
- pass
- self.done.add(relation)
- scope = self.scopes[relation.scope]
- alias = self.alias_and_add_table(fti_table, scope=scope)
- relation._q_sqltable = alias
- return alias
-
- # outer join handling ######################################################
-
- def mark_as_used_in_outer_join(self, tablealias, addpending=True):
- """Mark table of given alias as used in outer join. This must be called
- after `outer_tables[tablealias]` has been initialized.
- """
- # remove a table from actual_table because it's used in an outer join
- # chain
- scope, tabledef = self.tables[tablealias]
- self.actual_tables[scope].remove(tabledef)
- # check if there are some pending outer join condition for this table
- if addpending:
- try:
- pending_conditions = self.outer_pending.pop(tablealias)
- except KeyError:
- pass
- else:
- self.outer_tables[tablealias][1].extend(pending_conditions)
- else:
- assert not tablealias in self.outer_pending
-
- def add_outer_join_condition(self, tablealias, condition):
- try:
- outer, conditions, chain = self.outer_tables[tablealias]
- conditions.append(condition)
- except KeyError:
- self.outer_pending.setdefault(tablealias, []).append(condition)
-
- def replace_tables_by_outer_join(self, leftalias, rightalias,
- outertype, condition):
- """tell we need <leftalias> <outertype> JOIN <rightalias> ON <condition>
- """
- assert leftalias != rightalias, leftalias
- outer_tables = self.outer_tables
- louter, lconditions, lchain = outer_tables.get(leftalias,
- (None, None, None))
- router, rconditions, rchain = outer_tables.get(rightalias,
- (None, None, None))
- if lchain is None and rchain is None:
- # create a new outer chaine
- chain = [leftalias, rightalias]
- outer_tables[leftalias] = (None, [], chain)
- outer_tables[rightalias] = (outertype, [condition], chain)
- self.outer_chains.append(chain)
- self.mark_as_used_in_outer_join(leftalias, addpending=False)
- self.mark_as_used_in_outer_join(rightalias)
- elif lchain is None:
- # [A > B > C] + [D > A] -> [D > A > B > C]
- if rightalias == rchain[0]:
- outer_tables[leftalias] = (None, [], rchain)
- conditions = outer_tables[rightalias][1] + [condition]
- outer_tables[rightalias] = (outertype, conditions, rchain)
- rchain.insert(0, leftalias)
- else:
- # [A > B > C] + [D > B] -> [A > B > C < D]
- if outertype == 'LEFT':
- outertype = 'RIGHT'
- outer_tables[leftalias] = (outertype, [condition], rchain)
- rchain.append(leftalias)
- self.mark_as_used_in_outer_join(leftalias)
- elif rchain is None:
- # [A > B > C] + [B > D] -> [A > B > C > D]
- outer_tables[rightalias] = (outertype, [condition], lchain)
- lchain.append(rightalias)
- self.mark_as_used_in_outer_join(rightalias)
- elif lchain is rchain:
- # already in the same chain, simply check compatibility and append
- # the condition if it's ok
- lidx = lchain.index(leftalias)
- ridx = lchain.index(rightalias)
- if (outertype == 'FULL' and router != 'FULL') \
- or (lidx < ridx and router != 'LEFT') \
- or (ridx < lidx and louter != 'RIGHT'):
- raise BadRQLQuery()
- # merge conditions
- if lidx < ridx:
- rconditions.append(condition)
- else:
- lconditions.append(condition)
- elif louter is None:
- # merge chains
- self.outer_chains.remove(lchain)
- rchain += lchain
- self.mark_as_used_in_outer_join(leftalias)
- for alias, (aouter, aconditions, achain) in outer_tables.items():
- if achain is lchain:
- outer_tables[alias] = (aouter, aconditions, rchain)
- else:
- raise BadRQLQuery()
-
- # sql generation helpers ###################################################
-
- def tables_sql(self, outer_chains=None):
- """generate SQL for FROM clause"""
- # sort for test predictability
- tables = sorted(self.actual_tables.pop())
- # process outer joins
- if outer_chains is None:
- assert not self.actual_tables, self.actual_tables
- assert not self.outer_pending
- outer_chains = self.outer_chains
- for chain in sorted(outer_chains):
- tablealias = chain[0]
- outertype, conditions, _ = self.outer_tables[tablealias]
- assert _ is chain, (chain, _)
- assert outertype is None, (chain, self.outer_chains)
- assert not conditions, (chain, self.outer_chains)
- assert len(chain) > 1
- tabledef = self.tables[tablealias][1]
- outerjoin = [tabledef]
- for tablealias in chain[1:]:
- outertype, conditions, _ = self.outer_tables[tablealias]
- assert _ is chain, (chain, self.outer_chains)
- assert outertype in ('LEFT', 'RIGHT', 'FULL'), (
- tablealias, outertype, conditions)
- assert isinstance(conditions, (list)), (
- tablealias, outertype, conditions)
- tabledef = self.tables[tablealias][1]
- outerjoin.append('%s OUTER JOIN %s ON (%s)' % (
- outertype, tabledef, ' AND '.join(conditions)))
- tables.append(' '.join(outerjoin))
- return ', '.join(tables)
-
-
-def extract_fake_having_terms(having):
- """RQL's HAVING may be used to contains stuff that should go in the WHERE
- clause of the SQL query, due to RQL grammar limitation. Split them...
-
- Return a list nodes that can be ANDed with query's WHERE clause. Having
- subtrees updated in place.
- """
- fakehaving = []
- for subtree in having:
- ors, tocheck = set(), []
- for compnode in subtree.get_nodes(Comparison):
- for fnode in compnode.get_nodes(Function):
- if fnode.descr().aggregat:
- p = compnode.parent
- oor = None
- while not isinstance(p, Select):
- if isinstance(p, (Or, Not)):
- oor = p
- p = p.parent
- if oor is not None:
- ors.add(oor)
- break
- else:
- tocheck.append(compnode)
- # tocheck hold a set of comparison not implying an aggregat function
- # put them in fakehaving if they don't share an Or node as ancestor
- # with another comparison containing an aggregat function
- for compnode in tocheck:
- parents = set()
- p = compnode.parent
- oor = None
- while not isinstance(p, Select):
- if p in ors or p is None: # p is None for nodes already in fakehaving
- break
- if isinstance(p, (Or, Not)):
- oor = p
- p = p.parent
- else:
- node = oor or compnode
- fakehaving.append(node)
- node.parent.remove(node)
- return fakehaving
-
-
-class SQLGenerator(object):
- """
- generation of SQL from the fully expanded RQL syntax tree
- SQL is designed to be used with a CubicWeb SQL schema
-
- Groups and sort are not handled here since they should not be handled at
- this level (see cubicweb.server.querier)
-
- we should not have errors here!
-
- WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is
- protected by a lock
- """
-
- def __init__(self, schema, dbhelper, attrmap=None):
- self.schema = schema
- self.dbhelper = dbhelper
- self.dbencoding = dbhelper.dbencoding
- self.keyword_map = {'NOW' : self.dbhelper.sql_current_timestamp,
- 'TODAY': self.dbhelper.sql_current_date,
- }
- if not self.dbhelper.union_parentheses_support:
- self.union_sql = self.noparen_union_sql
- self._lock = threading.Lock()
- if attrmap is None:
- attrmap = {}
- self.attr_map = attrmap
-
- def generate(self, union, args=None, varmap=None):
- """return SQL queries and a variable dictionary from a RQL syntax tree
-
- :partrqls: a list of couple (rqlst, solutions)
- :args: optional dictionary with values of substitutions used in the query
- :varmap: optional dictionary mapping variable name to a special table
- name, in case the query as to fetch data from temporary tables
-
- return an sql string and a dictionary with substitutions values
- """
- if args is None:
- args = {}
- if varmap is None:
- varmap = {}
- self._lock.acquire()
- self._args = args
- self._varmap = varmap
- self._query_attrs = {}
- self._state = None
- # self._not_scope_offset = 0
- try:
- # union query for each rqlst / solution
- sql = self.union_sql(union)
- # we are done
- return sql, self._query_attrs, self._state.needs_source_cb
- finally:
- self._lock.release()
-
- def union_sql(self, union, needalias=False): # pylint: disable=E0202
- if len(union.children) == 1:
- return self.select_sql(union.children[0], needalias)
- sqls = ('(%s)' % self.select_sql(select, needalias)
- for select in union.children)
- return '\nUNION ALL\n'.join(sqls)
-
- def noparen_union_sql(self, union, needalias=False):
- # needed for sqlite backend which doesn't like parentheses around union
- # query. This may cause bug in some condition (sort in one of the
- # subquery) but will work in most case
- #
- # see http://www.sqlite.org/cvstrac/tktview?tn=3074
- sqls = (self.select_sql(select, needalias)
- for i, select in enumerate(union.children))
- return '\nUNION ALL\n'.join(sqls)
-
- def select_sql(self, select, needalias=False):
- """return SQL queries and a variable dictionary from a RQL syntax tree
-
- :select: a selection statement of the syntax tree (`rql.stmts.Select`)
- :solution: a dictionary containing variables binding.
- A solution's dictionary has variable's names as key and variable's
- types as values
- :needwrap: boolean telling if the query will be wrapped in an outer
- query (to deal with aggregat and/or grouping)
- """
- if select.distinct:
- distinct = True
- elif self.dbhelper.fti_need_distinct:
- distinct = getattr(select.parent, 'has_text_query', False)
- else:
- distinct = False
- sorts = select.orderby
- groups = select.groupby
- having = select.having
- for restr in extract_fake_having_terms(having):
- scope = None
- for vref in restr.get_nodes(VariableRef):
- vscope = vref.variable.scope
- if vscope is select:
- continue # ignore select scope, so restriction is added to
- # the inner most scope possible
- if scope is None:
- scope = vscope
- elif vscope is not scope:
- scope = common_parent(scope, vscope).scope
- if scope is None:
- scope = select
- scope.add_restriction(restr)
- # remember selection, it may be changed and have to be restored
- origselection = select.selection[:]
- # check if the query will have union subquery, if it need sort term
- # selection (union or distinct query) and wrapping (union with groups)
- needwrap = False
- sols = select.solutions
- selectsortterms = distinct
- if len(sols) > 1:
- # remove invariant from solutions
- sols, existssols, unstable = remove_unused_solutions(
- select, sols, self._varmap, self.schema)
- if len(sols) > 1:
- # if there is still more than one solution, a UNION will be
- # generated and so sort terms have to be selected
- selectsortterms = True
- # and if select is using group by or aggregat, a wrapping
- # query will be necessary
- if groups or select.has_aggregat:
- select.select_only_variables()
- needwrap = True
- else:
- existssols, unstable = {}, ()
- state = StateInfo(select, existssols, unstable)
- if self._state is not None:
- # state from a previous unioned select
- state.merge_source_cbs(self._state.needs_source_cb)
- # treat subqueries
- self._subqueries_sql(select, state)
- # generate sql for this select node
- if needwrap:
- outerselection = origselection[:]
- if sorts and selectsortterms:
- if distinct:
- sort_term_selection(sorts, outerselection, groups)
- fix_selection_and_group(select, needwrap, selectsortterms,
- sorts, groups, having)
- if needwrap:
- fneedwrap = len(outerselection) != len(origselection)
- else:
- fneedwrap = len(select.selection) != len(origselection)
- if fneedwrap:
- needalias = True
- self._in_wrapping_query = False
- self._state = state
- try:
- sql = self._solutions_sql(select, sols, distinct,
- needalias or needwrap)
- # generate groups / having before wrapping query selection to get
- # correct column aliases
- self._in_wrapping_query = needwrap
- if groups:
- # no constant should be inserted in GROUP BY else the backend
- # will interpret it as a positional index in the selection
- groups = ','.join(vref.accept(self) for vref in groups
- if not isinstance(vref, Constant))
- if having:
- # filter out constants as for GROUP BY
- having = ' AND '.join(term.accept(self) for term in having
- if not isinstance(term, Constant))
- if needwrap:
- sql = '%s FROM (%s) AS T1' % (
- self._selection_sql(outerselection, distinct,needalias),
- sql)
- if groups:
- sql += '\nGROUP BY %s' % groups
- if having:
- sql += '\nHAVING %s' % having
- # sort
- if sorts:
- sqlsortterms = []
- if needwrap:
- selectidx = [str(term) for term in outerselection]
- else:
- selectidx = [str(term) for term in select.selection]
- for sortterm in sorts:
- _term = self._sortterm_sql(sortterm, selectidx)
- if _term is not None:
- sqlsortterms.append(_term)
- if sqlsortterms:
- sql = self.dbhelper.sql_add_order_by(
- sql, sqlsortterms, origselection, fneedwrap,
- select.limit or select.offset)
- else:
- sqlsortterms = None
- state.finalize_source_cbs()
- finally:
- select.selection = origselection
- # limit / offset
- sql = self.dbhelper.sql_add_limit_offset(sql,
- select.limit,
- select.offset,
- sqlsortterms)
- return sql
-
- def _subqueries_sql(self, select, state):
- for i, subquery in enumerate(select.with_):
- sql = self.union_sql(subquery.query, needalias=True)
- tablealias = '_T%s' % i # XXX nested subqueries
- sql = '(%s) AS %s' % (sql, tablealias)
- state.subtables[tablealias] = (0, sql)
- latest_state = self._state
- for vref in subquery.aliases:
- alias = vref.variable
- alias._q_sqltable = tablealias
- alias._q_sql = '%s.C%s' % (tablealias, alias.colnum)
- try:
- stack = latest_state.needs_source_cb[alias.colnum]
- if state.subquery_source_cb is None:
- state.subquery_source_cb = {}
- for selectidx, vref in iter_mapped_var_sels(select, alias):
- stack = stack[:]
- update_source_cb_stack(state, select, vref, stack)
- state.subquery_source_cb[selectidx] = stack
- except KeyError:
- continue
-
- def _solutions_sql(self, select, solutions, distinct, needalias):
- sqls = []
- for solution in solutions:
- self._state.reset(solution)
- # visit restriction subtree
- if select.where is not None:
- self._state.add_restriction(select.where.accept(self))
- sql = [self._selection_sql(select.selection, distinct, needalias)]
- if self._state.restrictions:
- sql.append('WHERE %s' % ' AND '.join(self._state.restrictions))
- self._state.merge_source_cbs(self._state._needs_source_cb)
- # add required tables
- assert len(self._state.actual_tables) == 1, self._state.actual_tables
- tables = self._state.tables_sql()
- if tables:
- sql.insert(1, 'FROM %s' % tables)
- elif self._state.restrictions and self.dbhelper.needs_from_clause:
- sql.insert(1, 'FROM (SELECT 1) AS _T')
- sqls.append('\n'.join(sql))
- if distinct:
- return '\nUNION\n'.join(sqls)
- else:
- return '\nUNION ALL\n'.join(sqls)
-
- def _selection_sql(self, selected, distinct, needaliasing=False):
- clause = []
- for term in selected:
- sql = term.accept(self)
- if needaliasing:
- colalias = 'C%s' % len(clause)
- clause.append('%s AS %s' % (sql, colalias))
- if isinstance(term, VariableRef):
- self._state.aliases[term.name] = colalias
- else:
- clause.append(sql)
- if distinct:
- return 'SELECT DISTINCT %s' % ', '.join(clause)
- return 'SELECT %s' % ', '.join(clause)
-
- def _sortterm_sql(self, sortterm, selectidx):
- term = sortterm.term
- try:
- sqlterm = selectidx.index(str(term)) + 1
- except ValueError:
- # Constant node or non selected term
- sqlterm = term.accept(self)
- if sqlterm is None:
- return None
- if sortterm.asc:
- return str(sqlterm)
- else:
- return '%s DESC' % sqlterm
-
- def visit_and(self, et):
- """generate SQL for a AND subtree"""
- res = []
- for c in et.children:
- part = c.accept(self)
- if part:
- res.append(part)
- return ' AND '.join(res)
-
- def visit_or(self, ou):
- """generate SQL for a OR subtree"""
- res = []
- for c in ou.children:
- part = c.accept(self)
- if part:
- res.append('(%s)' % part)
- if res:
- if len(res) > 1:
- return '(%s)' % ' OR '.join(res)
- return res[0]
- return ''
-
- def visit_not(self, node):
- csql = node.children[0].accept(self)
- if node in self._state.done or not csql:
- # already processed or no sql generated by children
- return csql
- return 'NOT (%s)' % csql
-
- def visit_exists(self, exists):
- """generate SQL name for a exists subquery"""
- sqls = []
- for dummy in self._state.iter_exists_sols(exists):
- sql = self._visit_exists(exists)
- if sql:
- sqls.append(sql)
- if not sqls:
- return ''
- return 'EXISTS(%s)' % ' UNION '.join(sqls)
-
- def _visit_exists(self, exists):
- self._state.push_scope(exists)
- restriction = exists.children[0].accept(self)
- restrictions, tables = self._state.pop_scope()
- if restriction:
- restrictions.append(restriction)
- restriction = ' AND '.join(restrictions)
- if not restriction:
- if tables:
- return 'SELECT 1 FROM %s' % tables
- return ''
- if not tables:
- # XXX could leave surrounding EXISTS() in this case no?
- sql = 'SELECT 1 WHERE %s' % restriction
- else:
- sql = 'SELECT 1 FROM %s WHERE %s' % (tables, restriction)
- return sql
-
-
- def visit_relation(self, relation):
- """generate SQL for a relation"""
- rtype = relation.r_type
- # don't care of type constraint statement (i.e. relation_type = 'is')
- if relation.is_types_restriction():
- return ''
- lhs, rhs = relation.get_parts()
- rschema = self.schema.rschema(rtype)
- if rschema.final:
- if rtype == 'eid' and lhs.variable._q_invariant and \
- lhs.variable.stinfo['constnode']:
- # special case where this restriction is already generated by
- # some other relation
- return ''
- # attribute relation
- if rtype == 'has_text':
- sql = self._visit_has_text_relation(relation)
- else:
- rhs_vars = rhs.get_nodes(VariableRef)
- if rhs_vars:
- # if variable(s) in the RHS
- sql = self._visit_var_attr_relation(relation, rhs_vars)
- else:
- # no variables in the RHS
- sql = self._visit_attribute_relation(relation)
- elif (rtype == 'is' and isinstance(rhs.children[0], Constant)
- and rhs.children[0].eval(self._args) is None):
- # special case "C is NULL"
- if lhs.name in self._varmap:
- lhssql = self._varmap[lhs.name]
- else:
- lhssql = lhs.accept(self)
- return '%s%s' % (lhssql, rhs.accept(self))
- elif '%s.%s' % (lhs, relation.r_type) in self._varmap:
- # relation has already been processed by a previous step
- return ''
- elif relation.optional:
- # OPTIONAL relation, generate a left|right outer join
- if rtype == 'identity' or rschema.inlined:
- sql = self._visit_outer_join_inlined_relation(relation, rschema)
- else:
- sql = self._visit_outer_join_relation(relation, rschema)
- elif rschema.inlined:
- sql = self._visit_inlined_relation(relation)
- else:
- # regular (non final) relation
- sql = self._visit_relation(relation, rschema)
- return sql
-
- def _visit_inlined_relation(self, relation):
- lhsvar, _, rhsvar, rhsconst = relation_info(relation)
- # we are sure lhsvar is not None
- lhssql = self._inlined_var_sql(lhsvar, relation.r_type)
- if rhsvar is None:
- moresql = None
- else:
- moresql = self._extra_join_sql(relation, lhssql, rhsvar)
- if isinstance(relation.parent, Not):
- self._state.done.add(relation.parent)
- if rhsvar is not None and rhsvar._q_invariant:
- sql = '%s IS NULL' % lhssql
- else:
- # column != 1234 may not get back rows where column is NULL...
- sql = '(%s IS NULL OR %s!=%s)' % (
- lhssql, lhssql, (rhsvar or rhsconst).accept(self))
- elif rhsconst is not None:
- sql = '%s=%s' % (lhssql, rhsconst.accept(self))
- elif isinstance(rhsvar, Variable) and rhsvar._q_invariant and \
- not rhsvar.name in self._varmap:
- # if the rhs variable is only linked to this relation, this mean we
- # only want the relation to exists, eg NOT NULL in case of inlined
- # relation
- if moresql is not None:
- return moresql
- return '%s IS NOT NULL' % lhssql
- else:
- sql = '%s=%s' % (lhssql, rhsvar.accept(self))
- if moresql is None:
- return sql
- return '%s AND %s' % (sql, moresql)
-
- def _process_relation_term(self, relation, rid, termvar, termconst, relfield):
- if termconst or not termvar._q_invariant:
- termsql = termconst and termconst.accept(self) or termvar.accept(self)
- yield '%s.%s=%s' % (rid, relfield, termsql)
- elif termvar._q_invariant:
- # if the variable is mapped, generate restriction anyway
- if termvar.name in self._varmap:
- termsql = termvar.accept(self)
- yield '%s.%s=%s' % (rid, relfield, termsql)
- extrajoin = self._extra_join_sql(relation, '%s.%s' % (rid, relfield), termvar)
- if extrajoin is not None:
- yield extrajoin
-
- def _visit_relation(self, relation, rschema):
- """generate SQL for a relation
-
- implements optimization 1.
- """
- if relation.r_type == 'identity':
- # special case "X identity Y"
- lhs, rhs = relation.get_parts()
- return '%s%s' % (lhs.accept(self), rhs.accept(self))
- lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
- rid = self._state.relation_table(relation)
- sqls = []
- sqls += self._process_relation_term(relation, rid, lhsvar, lhsconst, 'eid_from')
- sqls += self._process_relation_term(relation, rid, rhsvar, rhsconst, 'eid_to')
- sql = ' AND '.join(sqls)
- return sql
-
- def _visit_outer_join_relation(self, relation, rschema):
- """
- left outer join syntax (optional=='right'):
- X relation Y?
-
- right outer join syntax (optional=='left'):
- X? relation Y
-
- full outer join syntaxes (optional=='both'):
- X? relation Y?
-
- if relation is inlined:
- if it's a left outer join:
- -> X LEFT OUTER JOIN Y ON (X.relation=Y.eid)
- elif it's a right outer join:
- -> Y LEFT OUTER JOIN X ON (X.relation=Y.eid)
- elif it's a full outer join:
- -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
- else:
- if it's a left outer join:
- -> X LEFT OUTER JOIN relation ON (relation.eid_from=X.eid)
- LEFT OUTER JOIN Y ON (relation.eid_to=Y.eid)
- elif it's a right outer join:
- -> Y LEFT OUTER JOIN relation ON (relation.eid_to=Y.eid)
- LEFT OUTER JOIN X ON (relation.eid_from=X.eid)
- elif it's a full outer join:
- -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
- """
- leftvar, leftconst, rightvar, rightconst = relation_info(relation)
- assert not (leftconst and rightconst), "doesn't make sense"
- if relation.optional == 'left':
- leftvar, rightvar = rightvar, leftvar
- leftconst, rightconst = rightconst, leftconst
- joinattr, restrattr = 'eid_to', 'eid_from'
- else:
- joinattr, restrattr = 'eid_from', 'eid_to'
- # search table for this variable, to use as left table of the outer join
- leftalias = None
- if leftvar:
- # take care, may return None for invariant variable
- leftalias = self._var_table(leftvar)
- if leftalias is None:
- if leftvar.stinfo['principal'] is not relation:
- # use variable's principal relation
- leftalias = leftvar.stinfo['principal']._q_sqltable
- else:
- # search for relation on which we should join
- for orelation in leftvar.stinfo['relations']:
- if (orelation is not relation and
- not self.schema.rschema(orelation.r_type).final):
- break
- else:
- for orelation in rightvar.stinfo['relations']:
- if (orelation is not relation and
- not self.schema.rschema(orelation.r_type).final
- and orelation.optional):
- break
- else:
- # unexpected
- assert False, leftvar
- leftalias = self._state.relation_table(orelation)
- # right table of the outer join
- rightalias = self._state.relation_table(relation)
- # compute join condition
- if not leftconst or (leftvar and not leftvar._q_invariant):
- leftsql = leftvar.accept(self)
- else:
- leftsql = leftconst.accept(self)
- condition = '%s.%s=%s' % (rightalias, joinattr, leftsql)
- if rightconst:
- condition += ' AND %s.%s=%s' % (rightalias, restrattr, rightconst.accept(self))
- # record outer join
- outertype = 'FULL' if relation.optional == 'both' else 'LEFT'
- self._state.replace_tables_by_outer_join(leftalias, rightalias,
- outertype, condition)
- # need another join?
- if rightconst is None:
- # we need another outer join for the other side of the relation (e.g.
- # for "X relation Y?" in RQL, we treated earlier the (cw_X.eid /
- # relation.eid_from) join, now we've to do (relation.eid_to /
- # cw_Y.eid)
- leftalias = rightalias
- rightsql = rightvar.accept(self) # accept before using var_table
- rightalias = self._var_table(rightvar)
- if rightalias is None:
- if rightvar.stinfo['principal'] is not relation:
- self._state.replace_tables_by_outer_join(
- leftalias, rightvar.stinfo['principal']._q_sqltable,
- outertype, '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self)))
- else:
- self._state.replace_tables_by_outer_join(
- leftalias, rightalias, outertype,
- '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self)))
- # this relation will hence be expressed in FROM clause, return nothing
- # here
- return ''
-
-
- def _visit_outer_join_inlined_relation(self, relation, rschema):
- lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
- assert not (lhsconst and rhsconst), "doesn't make sense"
- attr = 'eid' if relation.r_type == 'identity' else relation.r_type
- lhsalias = self._var_table(lhsvar)
- rhsalias = rhsvar and self._var_table(rhsvar)
- try:
- lhssql = self._varmap['%s.%s' % (lhsvar.name, attr)]
- except KeyError:
- if lhsalias is None:
- lhssql = lhsconst.accept(self)
- elif attr == 'eid':
- lhssql = lhsvar.accept(self)
- else:
- lhssql = '%s.%s%s' % (lhsalias, SQL_PREFIX, attr)
- condition = '%s=%s' % (lhssql, (rhsconst or rhsvar).accept(self))
- # this is not a typo, rhs optional variable means lhs outer join and vice-versa
- if relation.optional == 'left':
- lhsvar, rhsvar = rhsvar, lhsvar
- lhsconst, rhsconst = rhsconst, lhsconst
- lhsalias, rhsalias = rhsalias, lhsalias
- outertype = 'LEFT'
- elif relation.optional == 'both':
- outertype = 'FULL'
- else:
- outertype = 'LEFT'
- if rhsalias is None:
- if rhsconst is not None:
- # inlined relation with invariant as rhs
- if relation.r_type != 'identity':
- condition = '(%s OR %s IS NULL)' % (condition, lhssql)
- if not lhsvar.stinfo.get('optrelations'):
- return condition
- self._state.add_outer_join_condition(lhsalias, condition)
- return
- if lhsalias is None:
- if lhsconst is not None and not rhsvar.stinfo.get('optrelations'):
- return condition
- lhsalias = lhsvar._q_sql.split('.', 1)[0]
- if lhsalias == rhsalias:
- self._state.add_outer_join_condition(lhsalias, condition)
- else:
- self._state.replace_tables_by_outer_join(
- lhsalias, rhsalias, outertype, condition)
- return ''
-
- def _visit_var_attr_relation(self, relation, rhs_vars):
- """visit an attribute relation with variable(s) in the RHS
-
- attribute variables are used either in the selection or for unification
- (eg X attr1 A, Y attr2 A). In case of selection, nothing to do here.
- """
- ored = relation.ored()
- for vref in rhs_vars:
- var = vref.variable
- if var.name in self._varmap:
- # ensure table is added
- self._var_info(var)
- if isinstance(var, ColumnAlias):
- # force sql generation whatever the computed principal
- principal = 1
- else:
- principal = var.stinfo.get('principal')
- # we've to return some sql if:
- # 1. visited relation is ored
- # 2. variable's principal is not this relation and not 1.
- if ored or (principal is not None and principal is not relation
- and not getattr(principal, 'ored', lambda : 0)()):
- # we have to generate unification expression
- if principal is relation:
- # take care if ored case and principal is the relation to
- # use the right relation in the unification term
- _rel = [rel for rel in var.stinfo['rhsrelations']
- if not rel is principal][0]
- else:
- _rel = relation
- lhssql = self._inlined_var_sql(_rel.children[0].variable,
- _rel.r_type)
- try:
- self._state.ignore_varmap = True
- sql = lhssql + relation.children[1].accept(self)
- finally:
- self._state.ignore_varmap = False
- if relation.optional == 'right':
- leftalias = self._var_table(principal.children[0].variable)
- rightalias = self._var_table(relation.children[0].variable)
- self._state.replace_tables_by_outer_join(
- leftalias, rightalias, 'LEFT', sql)
- return ''
- return sql
- return ''
-
- def _visit_attribute_relation(self, rel):
- """generate SQL for an attribute relation"""
- lhs, rhs = rel.get_parts()
- rhssql = rhs.accept(self)
- table = self._var_table(lhs.variable)
- if table is None:
- assert rel.r_type == 'eid'
- lhssql = lhs.accept(self)
- else:
- try:
- lhssql = self._varmap['%s.%s' % (lhs.name, rel.r_type)]
- except KeyError:
- mapkey = '%s.%s' % (self._state.solution[lhs.name], rel.r_type)
- if mapkey in self.attr_map:
- cb, sourcecb = self.attr_map[mapkey]
- if sourcecb:
- # callback is a source callback, we can't use this
- # attribute in restriction
- raise QueryError("can't use %s (%s) in restriction"
- % (mapkey, rel.as_string()))
- lhssql = cb(self, lhs.variable, rel)
- elif rel.r_type == 'eid':
- lhssql = lhs.variable._q_sql
- else:
- lhssql = '%s.%s%s' % (table, SQL_PREFIX, rel.r_type)
- try:
- if rel._q_needcast == 'TODAY':
- sql = 'DATE(%s)%s' % (lhssql, rhssql)
- # XXX which cast function should be used
- #elif rel._q_needcast == 'NOW':
- # sql = 'TIMESTAMP(%s)%s' % (lhssql, rhssql)
- else:
- sql = '%s%s' % (lhssql, rhssql)
- except AttributeError:
- sql = '%s%s' % (lhssql, rhssql)
- if lhs.variable.stinfo.get('optrelations'):
- self._state.add_outer_join_condition(table, sql)
- else:
- return sql
-
- def _visit_has_text_relation(self, rel):
- """generate SQL for a has_text relation"""
- lhs, rhs = rel.get_parts()
- const = rhs.children[0]
- alias = self._state.fti_table(rel, self.dbhelper.fti_table)
- jointo = lhs.accept(self)
- restriction = ''
- lhsvar = lhs.variable
- me_is_principal = lhsvar.stinfo.get('principal') is rel
- if me_is_principal:
- if lhsvar.stinfo['typerel'] is None:
- # the variable is using the fti table, no join needed
- jointo = None
- elif not lhsvar.name in self._varmap:
- # join on entities instead of etype's table to get result for
- # external entities on multisources configurations
- ealias = lhsvar._q_sqltable = '_' + lhsvar.name
- jointo = lhsvar._q_sql = '%s.eid' % ealias
- self._state.add_table('entities AS %s' % ealias, ealias)
- if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1:
- restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name])
- else:
- etypes = ','.join("'%s'" % etype for etype in lhsvar.stinfo['possibletypes'])
- restriction = " AND %s.type IN (%s)" % (ealias, etypes)
- if isinstance(rel.parent, Not):
- self._state.done.add(rel.parent)
- not_ = True
- else:
- not_ = False
- query = const.eval(self._args)
- return self.dbhelper.fti_restriction_sql(alias, query,
- jointo, not_) + restriction
-
- def visit_comparison(self, cmp):
- """generate SQL for a comparison"""
- optional = getattr(cmp, 'optional', None) # rql < 0.30
- if len(cmp.children) == 2:
- # simplified expression from HAVING clause
- lhs, rhs = cmp.children
- else:
- lhs = None
- rhs = cmp.children[0]
- assert not optional
- sql = None
- operator = cmp.operator
- if operator in ('LIKE', 'ILIKE'):
- if operator == 'ILIKE' and not self.dbhelper.ilike_support:
- operator = ' LIKE '
- else:
- operator = ' %s ' % operator
- elif operator == 'REGEXP':
- sql = ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self))
- elif (operator == '=' and isinstance(rhs, Constant)
- and rhs.eval(self._args) is None):
- if lhs is None:
- sql = ' IS NULL'
- else:
- sql = '%s IS NULL' % lhs.accept(self)
- elif isinstance(rhs, Function) and rhs.name == 'IN':
- assert operator == '='
- operator = ' '
- if sql is None:
- if lhs is None:
- sql = '%s%s'% (operator, rhs.accept(self))
- else:
- sql = '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self))
- if optional is None:
- return sql
- leftvars = cmp.children[0].get_nodes(VariableRef)
- assert len(leftvars) == 1
- if leftvars[0].variable.stinfo['attrvar'] is None:
- assert isinstance(leftvars[0].variable, ColumnAlias)
- leftalias = leftvars[0].variable._q_sqltable
- else:
- leftalias = self._var_table(leftvars[0].variable.stinfo['attrvar'])
- rightvars = cmp.children[1].get_nodes(VariableRef)
- assert len(rightvars) == 1
- if rightvars[0].variable.stinfo['attrvar'] is None:
- assert isinstance(rightvars[0].variable, ColumnAlias)
- rightalias = rightvars[0].variable._q_sqltable
- else:
- rightalias = self._var_table(rightvars[0].variable.stinfo['attrvar'])
- if optional == 'right':
- self._state.replace_tables_by_outer_join(
- leftalias, rightalias, 'LEFT', sql)
- elif optional == 'left':
- self._state.replace_tables_by_outer_join(
- rightalias, leftalias, 'LEFT', sql)
- else:
- self._state.replace_tables_by_outer_join(
- leftalias, rightalias, 'FULL', sql)
- return ''
-
- def visit_mathexpression(self, mexpr):
- """generate SQL for a mathematic expression"""
- lhs, rhs = mexpr.get_parts()
- # check for string concatenation
- operator = mexpr.operator
- if operator == '%':
- operator = '%%'
- try:
- if mexpr.operator == '+' and mexpr.get_type(self._state.solution, self._args) == 'String':
- return '(%s)' % self.dbhelper.sql_concat_string(lhs.accept(self),
- rhs.accept(self))
- except CoercionError:
- pass
- return '(%s %s %s)'% (lhs.accept(self), operator, rhs.accept(self))
-
- def visit_unaryexpression(self, uexpr):
- """generate SQL for a unary expression"""
- return '%s%s'% (uexpr.operator, uexpr.children[0].accept(self))
-
- def visit_function(self, func):
- """generate SQL name for a function"""
- if func.name == 'FTIRANK':
- try:
- rel = next(iter(func.children[0].variable.stinfo['ftirels']))
- except KeyError:
- raise BadRQLQuery("can't use FTIRANK on variable not used in an"
- " 'has_text' relation (eg full-text search)")
- const = rel.get_parts()[1].children[0]
- return self.dbhelper.fti_rank_order(
- self._state.fti_table(rel, self.dbhelper.fti_table),
- const.eval(self._args))
- args = [c.accept(self) for c in func.children]
- if func in self._state.source_cb_funcs:
- # function executed as a callback on the source
- assert len(args) == 1
- return args[0]
- # func_as_sql will check function is supported by the backend
- return self.dbhelper.func_as_sql(func.name, args)
-
- def visit_constant(self, constant):
- """generate SQL name for a constant"""
- if constant.type is None:
- return 'NULL'
- value = constant.value
- if constant.type == 'etype':
- return value
- # don't substitute int, causes pb when used as sorting column number
- if constant.type == 'Int':
- return str(value)
- if constant.type in ('Date', 'Datetime'):
- rel = constant.relation()
- if rel is not None:
- rel._q_needcast = value
- return self.keyword_map[value]()
- if constant.type == 'Substitute':
- try:
- # we may found constant from simplified var in varmap
- return self._mapped_term(constant, '%%(%s)s' % value)[0]
- except KeyError:
- _id = value
- if PY2 and isinstance(_id, unicode):
- _id = _id.encode()
- else:
- _id = str(id(constant)).replace('-', '', 1)
- self._query_attrs[_id] = value
- return '%%(%s)s' % _id
-
- def visit_variableref(self, variableref):
- """get the sql name for a variable reference"""
- # use accept, .variable may be a variable or a columnalias
- return variableref.variable.accept(self)
-
- def visit_columnalias(self, colalias):
- """get the sql name for a subquery column alias"""
- if colalias.name in self._varmap:
- sql = self._varmap[colalias.name]
- table = sql.split('.', 1)[0]
- colalias._q_sqltable = table
- colalias._q_sql = sql
- self._state.add_table(table)
- return sql
- return colalias._q_sql
-
- def visit_variable(self, variable):
- """get the table name and sql string for a variable"""
- #if contextrels is None and variable.name in self._state.done:
- if variable.name in self._state.done:
- if self._in_wrapping_query:
- return 'T1.%s' % self._state.aliases[variable.name]
- return variable._q_sql
- self._state.done.add(variable.name)
- vtablename = None
- if not self._state.ignore_varmap and variable.name in self._varmap:
- sql, vtablename = self._var_info(variable)
- elif variable.stinfo['attrvar']:
- # attribute variable (systematically used in rhs of final
- # relation(s)), get table name and sql from any rhs relation
- sql = self._linked_var_sql(variable)
- elif variable._q_invariant:
- # since variable is invariant, we know we won't found final relation
- principal = variable.stinfo['principal']
- if principal is None:
- vtablename = '_' + variable.name
- self._state.add_table('entities AS %s' % vtablename, vtablename)
- sql = '%s.eid' % vtablename
- if variable.stinfo['typerel'] is not None:
- # add additional restriction on entities.type column
- pts = variable.stinfo['possibletypes']
- if len(pts) == 1:
- etype = next(iter(variable.stinfo['possibletypes']))
- restr = "%s.type='%s'" % (vtablename, etype)
- else:
- etypes = ','.join("'%s'" % et for et in pts)
- restr = '%s.type IN (%s)' % (vtablename, etypes)
- self._state.add_restriction(restr)
- elif principal.r_type == 'has_text':
- sql = '%s.%s' % (self._state.fti_table(principal,
- self.dbhelper.fti_table),
- self.dbhelper.fti_uid_attr)
- elif principal in variable.stinfo['rhsrelations']:
- if self.schema.rschema(principal.r_type).inlined:
- sql = self._linked_var_sql(variable)
- else:
- sql = '%s.eid_to' % self._state.relation_table(principal)
- else:
- sql = '%s.eid_from' % self._state.relation_table(principal)
- else:
- # standard variable: get table name according to etype and use .eid
- # attribute
- sql, vtablename = self._var_info(variable)
- variable._q_sqltable = vtablename
- variable._q_sql = sql
- return sql
-
- # various utilities #######################################################
-
- def _extra_join_sql(self, relation, sql, var):
- # if rhs var is invariant, and this relation is not its principal,
- # generate extra join
- try:
- if not var.stinfo['principal'] is relation:
- op = relation.operator()
- if op == '=':
- # need a predicable result for tests
- args = sorted( (sql, var.accept(self)) )
- args.insert(1, op)
- else:
- args = (sql, op, var.accept(self))
- return '%s%s%s' % tuple(args)
- except KeyError:
- # no principal defined, relation is necessarily the principal and
- # so nothing to return here
- pass
- return None
-
- def _temp_table_scope(self, select, table):
- scope = 9999
- for var, sql in self._varmap.items():
- # skip "attribute variable" in varmap (such 'T.login')
- if not '.' in var and table == sql.split('.', 1)[0]:
- try:
- scope = min(scope, self._state.scopes[select.defined_vars[var].scope])
- except KeyError:
- scope = 0 # XXX
- if scope == 0:
- break
- return scope
-
- def _mapped_term(self, term, key):
- """return sql and table alias to the `term`, mapped as `key` or raise
- KeyError when the key is not found in the varmap
- """
- sql = self._varmap[key]
- tablealias = sql.split('.', 1)[0]
- scope = self._temp_table_scope(term.stmt, tablealias)
- self._state.add_table(tablealias, scope=scope)
- return sql, tablealias
-
- def _var_info(self, var):
- try:
- return self._mapped_term(var, var.name)
- except KeyError:
- scope = self._state.scopes[var.scope]
- etype = self._state.solution[var.name]
- # XXX this check should be moved in rql.stcheck
- if self.schema.eschema(etype).final:
- raise BadRQLQuery(var.stmt.root)
- tablealias = '_' + var.name
- sql = '%s.%seid' % (tablealias, SQL_PREFIX)
- self._state.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias),
- tablealias, scope=scope)
- return sql, tablealias
-
- def _inlined_var_sql(self, var, rtype):
- try:
- sql = self._varmap['%s.%s' % (var.name, rtype)]
- scope = self._state.scopes[var.scope]
- self._state.add_table(sql.split('.', 1)[0], scope=scope)
- except KeyError:
- # rtype may be an attribute relation when called from
- # _visit_var_attr_relation. take care about 'eid' rtype, since in
- # some case we may use the `entities` table, so in that case we've
- # to properly use variable'sql
- if rtype == 'eid':
- sql = var.accept(self)
- else:
- sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype)
- return sql
-
- def _linked_var_sql(self, variable):
- if not self._state.ignore_varmap:
- try:
- return self._varmap[variable.name]
- except KeyError:
- pass
- rel = (variable.stinfo.get('principal') or
- next(iter(variable.stinfo['rhsrelations'])))
- linkedvar = rel.children[0].variable
- if rel.r_type == 'eid':
- return linkedvar.accept(self)
- if isinstance(linkedvar, ColumnAlias):
- raise BadRQLQuery('variable %s should be selected by the subquery'
- % variable.name)
- try:
- sql = self._varmap['%s.%s' % (linkedvar.name, rel.r_type)]
- except KeyError:
- mapkey = '%s.%s' % (self._state.solution[linkedvar.name], rel.r_type)
- if mapkey in self.attr_map:
- cb, sourcecb = self.attr_map[mapkey]
- if not sourcecb:
- return cb(self, linkedvar, rel)
- # attribute mapped at the source level (bfss for instance)
- stmt = rel.stmt
- for selectidx, vref in iter_mapped_var_sels(stmt, variable):
- stack = [cb]
- update_source_cb_stack(self._state, stmt, vref, stack)
- self._state._needs_source_cb[selectidx] = stack
- linkedvar.accept(self)
- sql = '%s.%s%s' % (linkedvar._q_sqltable, SQL_PREFIX, rel.r_type)
- return sql
-
- # tables handling #########################################################
-
- def _var_table(self, var):
- var.accept(self)#.visit_variable(var)
- return var._q_sqltable