"""RQL to SQL generator for native sources.SQL queries optimization~~~~~~~~~~~~~~~~~~~~~~~~1. EUser X WHERE X in_group G, G name 'users': EUser is the only subject entity type for the in_group relation, which allow us to do :: SELECT eid_from FROM in_group, EGroup WHERE in_group.eid_to = EGroup.eid_from AND EGroup.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 generationis done according to this information:organization: Logilab:copyright: 2001-2008 LOGILAB S.A. (Paris, FRANCE), all rights reserved.:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr"""__docformat__="restructuredtext en"importthreadingfromrqlimportBadRQLQuery,CoercionErrorfromrql.stmtsimportUnion,Selectfromrql.nodesimport(SortTerm,VariableRef,Constant,Function,Not,Variable,ColumnAlias,Relation,SubQuery,Exists)fromcubicwebimportserverfromcubicweb.server.utilsimportcleanup_solutionsdef_new_var(select,varname):newvar=select.get_variable(varname)ifnot'relations'innewvar.stinfo:# not yet initializednewvar.prepare_annotation()newvar.stinfo['scope']=selectnewvar._q_invariant=Falsereturnnewvardef_fill_to_wrap_rel(var,newselect,towrap,schema):forrelinvar.stinfo['relations']-var.stinfo['rhsrelations']:rschema=schema.rschema(rel.r_type)ifrschema.inlined:towrap.add((var,rel))forvrefinrel.children[1].iget_nodes(VariableRef):newivar=_new_var(newselect,vref.name)newselect.selection.append(VariableRef(newivar))_fill_to_wrap_rel(vref.variable,newselect,towrap,schema)elifrschema.is_final():towrap.add((var,rel))defrewrite_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 """torewrite=set()modified=Falseforvarnameintuple(unstable):var=select.defined_vars[varname]ifnotvar.stinfo['optrelations']:continuemodified=Trueunstable.remove(varname)torewrite.add(var)newselect=Select()newselect.need_distinct=Falsemyunion=Union()myunion.append(newselect)# extract aliases / selectionnewvar=_new_var(newselect,var.name)newselect.selection=[VariableRef(newvar)]foravarinselect.defined_vars.itervalues():ifavar.stinfo['attrvar']isvar:newavar=_new_var(newselect,avar.name)newavar.stinfo['attrvar']=newvarnewselect.selection.append(VariableRef(newavar))towrap_rels=set()_fill_to_wrap_rel(var,newselect,towrap_rels,schema)# extract relationsforvar,relintowrap_rels:newrel=rel.copy(newselect)newselect.add_restriction(newrel)select.remove_node(rel)var.stinfo['relations'].remove(rel)newvar.stinfo['relations'].add(newrel)ifrel.optionalin('left','both'):newvar.stinfo['optrelations'].add(newrel)forvrefinnewrel.children[1].iget_nodes(VariableRef):var=vref.variablevar.stinfo['relations'].add(newrel)var.stinfo['rhsrelations'].add(newrel)ifrel.optionalin('right','both'):var.stinfo['optrelations'].add(newrel)# extract subquery solutionssolutions=[sol.copy()forsolinsolutions]cleanup_solutions(newselect,solutions)newselect.set_possible_types(solutions)# full sub-queryaliases=[VariableRef(select.get_variable(avar.name,i))fori,avarinenumerate(newselect.selection)]select.add_subquery(SubQuery(aliases,myunion),check=False)returnmodifieddef_new_solutions(rqlst,solutions):"""first filter out subqueries variables from solutions"""newsolutions=[]fororigsolinsolutions:asol={}forvnameinrqlst.defined_vars:asol[vname]=origsol[vname]ifnotasolinnewsolutions:newsolutions.append(asol)returnnewsolutionsdefremove_unused_solutions(rqlst,solutions,varmap,schema):"""cleanup solutions: remove solutions where invariant variables are taking different types """newsolutions=_new_solutions(rqlst,solutions)existssols={}unstable=set()forvname,varinrqlst.defined_vars.iteritems():vtype=newsolutions[0][vname]ifvar._q_invariantorvnameinvarmap:foriinxrange(len(newsolutions)-1,0,-1):ifvtype!=newsolutions[i][vname]:newsolutions.pop(i)elifnotvar.scopeisrqlst:# move appart variables which are in a EXISTS scope and are variatingtry:thisexistssols,thisexistsvars=existssols[var.scope]exceptKeyError:thisexistssols=[newsolutions[0]]thisexistsvars=set()existssols[var.scope]=thisexistssols,thisexistsvarsforiinxrange(len(newsolutions)-1,0,-1):ifvtype!=newsolutions[i][vname]:thisexistssols.append(newsolutions.pop(i))thisexistsvars.add(vname)else:# remember unstable variablesforiinxrange(1,len(newsolutions)):ifvtype!=newsolutions[i][vname]:unstable.add(vname)iflen(newsolutions)>1:ifrewrite_unstable_outer_join(rqlst,newsolutions,unstable,schema):# remove variables extracted to subqueries from solutionsnewsolutions=_new_solutions(rqlst,newsolutions)returnnewsolutions,existssols,unstabledefrelation_info(relation):lhs,rhs=relation.get_variable_parts()try:lhs=lhs.variablelhsconst=lhs.stinfo['constnode']exceptAttributeError:lhsconst=lhslhs=NoneexceptKeyError:lhsconst=None# ColumnAliastry:rhs=rhs.variablerhsconst=rhs.stinfo['constnode']exceptAttributeError:rhsconst=rhsrhs=NoneexceptKeyError:rhsconst=None# ColumnAliasreturnlhs,lhsconst,rhs,rhsconstdefswitch_relation_field(sql,table=''):switchedsql=sql.replace(table+'.eid_from','__eid_from__')switchedsql=switchedsql.replace(table+'.eid_to',table+'.eid_from')returnswitchedsql.replace('__eid_from__',table+'.eid_to')defsort_term_selection(sorts,selectedidx,rqlst,groups):# XXX beurkifisinstance(rqlst,list):defappend(term):rqlst.append(term)else:defappend(term):rqlst.selection.append(term.copy(rqlst))forsortterminsorts:term=sortterm.termifnotisinstance(term,Constant)andnotstr(term)inselectedidx:selectedidx.append(str(term))append(term)ifgroups:forvrefinterm.iget_nodes(VariableRef):ifnotvrefingroups:groups.append(vref)deffix_selection(rqlst,selectedidx,needwrap,sorts,groups,having):ifsorts:sort_term_selection(sorts,selectedidx,rqlst,notneedwrapandgroups)ifneedwrap:ifgroups:forvrefingroups:ifnotvref.nameinselectedidx:selectedidx.append(vref.name)rqlst.selection.append(vref)ifhaving:forterminhaving:forvrefinterm.iget_nodes(VariableRef):ifnotvref.nameinselectedidx:selectedidx.append(vref.name)rqlst.selection.append(vref)# IGenerator implementation for RQL->SQL ######################################classStateInfo(object):def__init__(self,existssols,unstablevars):self.existssols=existssolsself.unstablevars=unstablevarsself.subtables={}defreset(self,solution):"""reset some visit variables"""self.solution=solutionself.count=0self.done=set()self.tables=self.subtables.copy()self.actual_tables=[[]]for_,tsqlinself.tables.itervalues():self.actual_tables[-1].append(tsql)self.outer_tables={}self.duplicate_switches=[]self.attr_vars={}self.aliases={}self.restrictions=[]self._restr_stack=[]defadd_restriction(self,restr):ifrestr:self.restrictions.append(restr)defiter_exists_sols(self,exists):ifnotexistsinself.existssols:yield1returnthisexistssols,thisexistsvars=self.existssols[exists]origsol=self.solutionorigtables=self.tablesdone=self.doneforthisexistssolinthisexistssols:forvnameinself.unstablevars:ifthisexistssol[vname]!=origsol[vname]andvnameinthisexistsvars:breakelse:self.tables=origtables.copy()self.solution=thisexistssolyield1# cleanup self.done from stuff specific to existsforvarinthisexistsvars:ifvarindone:done.remove(var)forrelinexists.iget_nodes(Relation):ifrelindone:done.remove(rel)self.solution=origsolself.tables=origtablesdefpush_scope(self):self.actual_tables.append([])self._restr_stack.append(self.restrictions)self.restrictions=[]defpop_scope(self):restrictions=self.restrictionsself.restrictions=self._restr_stack.pop()returnrestrictions,self.actual_tables.pop()classSQLGenerator(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,dbms_helper,dbencoding='UTF-8'):self.schema=schemaself.dbms_helper=dbms_helperself.dbencoding=dbencodingself.keyword_map={'NOW':self.dbms_helper.sql_current_timestamp,'TODAY':self.dbms_helper.sql_current_date,}ifnotself.dbms_helper.union_parentheses_support:self.union_sql=self.noparen_union_sqlself._lock=threading.Lock()defgenerate(self,union,args=None,varmap=None):"""return SQL queries and a variable dictionnary 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 """ifargsisNone:args={}ifvarmapisNone:varmap={}self._lock.acquire()self._args=argsself._varmap=varmapself._query_attrs={}self._state=Nonetry:# union query for each rqlst / solutionsql=self.union_sql(union)# we are donereturnsql,self._query_attrsfinally:self._lock.release()defunion_sql(self,union,needalias=False):# pylint: disable-msg=E0202iflen(union.children)==1:returnself.select_sql(union.children[0],needalias)sqls=('(%s)'%self.select_sql(select,needalias)forselectinunion.children)return'\nUNION ALL\n'.join(sqls)defnoparen_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=3074sqls=(self.select_sql(select,needalias)fori,selectinenumerate(union.children))return'\nUNION ALL\n'.join(sqls)defselect_sql(self,select,needalias=False):"""return SQL queries and a variable dictionnary from a RQL syntax tree :select: a selection statement of the syntax tree (`rql.stmts.Select`) :solution: a dictionnary containing variables binding. A solution's dictionnary 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) """distinct=selectsortterms=select.need_distinctsorts=select.orderbygroups=select.groupbyhaving=select.having# remember selection, it may be changed and have to be restoredorigselection=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=Falsesols=select.solutionsiflen(sols)>1:# remove invariant from solutionssols,existssols,unstable=remove_unused_solutions(select,sols,self._varmap,self.schema)iflen(sols)>1:# if there is still more than one solution, a UNION will be# generated and so sort terms have to be selectedselectsortterms=True# and if select is using group by or aggregat, a wrapping# query will be necessaryifgroupsorselect.has_aggregat:select.select_only_variables()needwrap=Trueelse:existssols,unstable={},()state=StateInfo(existssols,unstable)# treat subqueriesself._subqueries_sql(select,state)# generate sql for this select nodeselectidx=[str(term)forterminselect.selection]ifneedwrap:outerselection=origselection[:]ifsortsandselectsortterms:outerselectidx=[str(term)forterminouterselection]ifdistinct:sort_term_selection(sorts,outerselectidx,outerselection,groups)else:outerselectidx=selectidx[:]fix_selection(select,selectidx,needwrap,selectsorttermsandsorts,groups,having)ifneedwrap:fselectidx=outerselectidxfneedwrap=len(outerselection)!=len(origselection)else:fselectidx=selectidxfneedwrap=len(select.selection)!=len(origselection)iffneedwrap:needalias=Trueself._in_wrapping_query=Falseself._state=statetry:sql=self._solutions_sql(select,sols,distinct,needaliasorneedwrap)# generate groups / having before wrapping query selection to# get correct column aliasesself._in_wrapping_query=needwrapifgroups:# no constant should be inserted in GROUP BY else the backend will# interpret it as a positional index in the selectiongroups=','.join(vref.accept(self)forvrefingroupsifnotisinstance(vref,Constant))ifhaving:# filter out constants as for GROUP BYhaving=','.join(vref.accept(self)forvrefinhavingifnotisinstance(vref,Constant))ifneedwrap:sql='%s FROM (%s) AS T1'%(self._selection_sql(outerselection,distinct,needalias),sql)ifgroups:sql+='\nGROUP BY %s'%groupsifhaving:sql+='\nHAVING %s'%having# sortifsorts:sql+='\nORDER BY %s'%','.join(self._sortterm_sql(sortterm,fselectidx)forsortterminsorts)iffneedwrap:selection=['T1.C%s'%iforiinxrange(len(origselection))]sql='SELECT %s FROM (%s) AS T1'%(','.join(selection),sql)finally:select.selection=origselection# limit / offsetlimit=select.limitiflimit:sql+='\nLIMIT %s'%limitoffset=select.offsetifoffset:sql+='\nOFFSET %s'%offsetreturnsqldef_subqueries_sql(self,select,state):fori,subqueryinenumerate(select.with_):sql=self.union_sql(subquery.query,needalias=True)tablealias='_T%s'%isql='(%s) AS %s'%(sql,tablealias)state.subtables[tablealias]=(0,sql)forvrefinsubquery.aliases:alias=vref.variablealias._q_sqltable=tablealiasalias._q_sql='%s.C%s'%(tablealias,alias.colnum)def_solutions_sql(self,select,solutions,distinct,needalias):sqls=[]forsolutioninsolutions:self._state.reset(solution)# visit restriction subtreeifselect.whereisnotNone:self._state.add_restriction(select.where.accept(self))sql=[self._selection_sql(select.selection,distinct,needalias)]ifself._state.restrictions:sql.append('WHERE %s'%' AND '.join(self._state.restrictions))# add required tablesassertlen(self._state.actual_tables)==1,self._state.actual_tablestables=self._state.actual_tables[-1]iftables:# sort for test predictabilitysql.insert(1,'FROM %s'%', '.join(sorted(tables)))elifself._state.restrictionsandself.dbms_helper.needs_from_clause:sql.insert(1,'FROM (SELECT 1) AS _T')sqls.append('\n'.join(sql))ifdistinct:return'\nUNION\n'.join(sqls)else:return'\nUNION ALL\n'.join(sqls)def_selection_sql(self,selected,distinct,needaliasing=False):clause=[]forterminselected:sql=term.accept(self)ifneedaliasing:colalias='C%s'%len(clause)clause.append('%s AS %s'%(sql,colalias))ifisinstance(term,VariableRef):self._state.aliases[term.name]=colaliaselse:clause.append(sql)ifdistinct:return'SELECT DISTINCT %s'%', '.join(clause)return'SELECT %s'%', '.join(clause)def_sortterm_sql(self,sortterm,selectidx):term=sortterm.termtry:sqlterm=str(selectidx.index(str(term))+1)exceptValueError:# Constant node or non selected termsqlterm=str(term.accept(self))ifsortterm.asc:returnsqltermelse:return'%s DESC'%sqltermdefvisit_and(self,et):"""generate SQL for a AND subtree"""res=[]forcinet.children:part=c.accept(self)ifpart:res.append(part)return' AND '.join(res)defvisit_or(self,ou):"""generate SQL for a OR subtree"""res=[]forcinou.children:part=c.accept(self)ifpart:res.append('(%s)'%part)ifres:iflen(res)>1:return'(%s)'%' OR '.join(res)returnres[0]return''defvisit_not(self,node):self._state.push_scope()csql=node.children[0].accept(self)sqls,tables=self._state.pop_scope()ifnodeinself._state.doneornotcsql:# already processed or no sql generated by childrenself._state.actual_tables[-1]+=tablesself._state.restrictions+=sqlsreturncsqlifisinstance(node.children[0],Exists):assertnotsqls,(sqls,str(node.stmt))assertnottables,(tables,str(node.stmt))return'NOT %s'%csqlsqls.append(csql)iftables:select='SELECT 1 FROM %s'%','.join(tables)else:select='SELECT 1'ifsqls:sql='NOT EXISTS(%s WHERE %s)'%(select,' AND '.join(sqls))else:sql='NOT EXISTS(%s)'%selectreturnsqldefvisit_exists(self,exists):"""generate SQL name for a exists subquery"""sqls=[]fordummyinself._state.iter_exists_sols(exists):sql=self._visit_exists(exists)ifsql:sqls.append(sql)ifnotsqls:return''return'EXISTS(%s)'%' UNION '.join(sqls)def_visit_exists(self,exists):self._state.push_scope()restriction=exists.children[0].accept(self)restrictions,tables=self._state.pop_scope()ifrestriction:restrictions.append(restriction)restriction=' AND '.join(restrictions)ifnotrestriction:return''ifnottables:# XXX could leave surrounding EXISTS() in this case no?sql='SELECT 1 WHERE %s'%restrictionelse:sql='SELECT 1 FROM %s WHERE %s'%(', '.join(tables),restriction)returnsqldefvisit_relation(self,relation):"""generate SQL for a relation"""rtype=relation.r_type# don't care of type constraint statement (i.e. relation_type = 'is')ifrelation.is_types_restriction():return''lhs,rhs=relation.get_parts()rschema=self.schema.rschema(rtype)ifrschema.is_final():ifrtype=='eid'andlhs.variable._q_invariantand \lhs.variable.stinfo['constnode']:# special case where this restriction is already generated by# some other relationreturn''# attribute relationifrtype=='has_text':sql=self._visit_has_text_relation(relation)else:rhs_vars=rhs.get_nodes(VariableRef)ifrhs_vars:# if variable(s) in the RHSsql=self._visit_var_attr_relation(relation,rhs_vars)else:# no variables in the RHSsql=self._visit_attribute_relation(relation)ifrelation.neged(strict=True):self._state.done.add(relation.parent)sql='NOT (%s)'%sqlelse:ifrtype=='is'andrhs.operator=='IS':# special case "C is NULL"iflhs.nameinself._varmap:lhssql=self._varmap[lhs.name]else:lhssql=lhs.accept(self)return'%s%s'%(lhssql,rhs.accept(self))if'%s.%s'%(lhs,relation.r_type)inself._varmap:# relation has already been processed by a previous stepreturnifrelation.optional:# check it has not already been treaten (to get necessary# information to add an outer join condition)ifrelationinself._state.done:return# OPTIONAL relation, generate a left|right outer joinsql=self._visit_outer_join_relation(relation,rschema)elifrschema.inlined:sql=self._visit_inlined_relation(relation)# elif isinstance(relation.parent, Not):# self._state.done.add(relation.parent)# # NOT relation# sql = self._visit_not_relation(relation, rschema)else:# regular (non final) relationsql=self._visit_relation(relation,rschema)returnsqldef_visit_inlined_relation(self,relation):lhsvar,_,rhsvar,rhsconst=relation_info(relation)# we are sure here to have a lhsvarassertlhsvarisnotNonelhssql=self._inlined_var_sql(lhsvar,relation.r_type)ifisinstance(relation.parent,Not):self._state.done.add(relation.parent)sql="%s IS NULL"%lhssqlifrhsvarisnotNoneandnotrhsvar._q_invariant:sql='(%s OR %s!=%s)'%(sql,lhssql,rhsvar.accept(self))returnsqlifrhsconstisnotNone:return'%s=%s'%(lhssql,rhsconst.accept(self))ifisinstance(rhsvar,Variable)andnotrhsvar.nameinself._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# relationiflen(rhsvar.stinfo['relations'])==1andrhsvar._q_invariant:return'%s IS NOT NULL'%lhssqlifrhsvar._q_invariant:returnself._extra_join_sql(relation,lhssql,rhsvar)return'%s=%s'%(lhssql,rhsvar.accept(self))def_process_relation_term(self,relation,rid,termvar,termconst,relfield):iftermconstorisinstance(termvar,ColumnAlias)ornottermvar._q_invariant:termsql=termconstandtermconst.accept(self)ortermvar.accept(self)yield'%s.%s=%s'%(rid,relfield,termsql)eliftermvar._q_invariant:# if the variable is mapped, generate restriction anywayiftermvar.nameinself._varmap:termsql=termvar.accept(self)yield'%s.%s=%s'%(rid,relfield,termsql)extrajoin=self._extra_join_sql(relation,'%s.%s'%(rid,relfield),termvar)ifextrajoin:yieldextrajoindef_visit_relation(self,relation,rschema):"""generate SQL for a relation implements optimization 1. """ifrelation.r_type=='identity':# special case "X identity Y"lhs,rhs=relation.get_parts()ifisinstance(relation.parent,Not):self._state.done.add(relation.parent)return'NOT %s%s'%(lhs.accept(self),rhs.accept(self))return'%s%s'%(lhs.accept(self),rhs.accept(self))lhsvar,lhsconst,rhsvar,rhsconst=relation_info(relation)rid=self._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)ifrschema.symetric:sql='(%s OR %s)'%(sql,switch_relation_field(sql))returnsqldef_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) """lhsvar,lhsconst,rhsvar,rhsconst=relation_info(relation)ifrelation.optional=='right':joinattr,restrattr='eid_from','eid_to'else:lhsvar,rhsvar=rhsvar,lhsvarlhsconst,rhsconst=rhsconst,lhsconstjoinattr,restrattr='eid_to','eid_from'ifrelation.optional=='both':outertype='FULL'else:outertype='LEFT'ifrschema.inlinedorrelation.r_type=='identity':self._state.done.add(relation)t1=self._var_table(lhsvar)ifrelation.r_type=='identity':attr='eid'else:attr=relation.r_type# reset lhs/rhs, we need the initial order nowlhs,rhs=relation.get_variable_parts()if'%s.%s'%(lhs.name,attr)inself._varmap:lhssql=self._varmap['%s.%s'%(lhs.name,attr)]else:lhssql='%s.%s'%(self._var_table(lhs.variable),attr)ifnotrhsvarisNone:t2=self._var_table(rhsvar)ift2isNone:ifrhsconstisnotNone:# inlined relation with invariant as rhscondition='%s=%s'%(lhssql,rhsconst.accept(self))ifrelation.r_type!='identity':condition='(%s OR %s IS NULL)'%(condition,lhssql)ifnotlhsvar.stinfo['optrelations']:returnconditionself.add_outer_join_condition(lhsvar,t1,condition)returnelse:condition='%s=%s'%(lhssql,rhsconst.accept(self))self.add_outer_join_condition(lhsvar,t1,condition)join='%s OUTER JOIN %s ON (%s=%s)'%(outertype,self._state.tables[t2][1],lhssql,rhs.accept(self))self.replace_tables_by_outer_join(join,t1,t2)return''lhssql=lhsconstandlhsconst.accept(self)orlhsvar.accept(self)rhssql=rhsconstandrhsconst.accept(self)orrhsvar.accept(self)rid=self._relation_table(relation)ifnotlhsvar:join=''toreplace=[]maintable=ridelse:join='%s OUTER JOIN %s ON (%s.%s=%s'%(outertype,self._state.tables[rid][1],rid,joinattr,lhssql)toreplace=[rid]maintable=self._var_table(lhsvar)ifrhsconst:join+=' AND %s.%s=%s)'%(rid,restrattr,rhssql)else:join+=')'ifnotrhsconst:rhstable=self._var_table(rhsvar)ifrhstable:assertrhstableisnotNone,rhsvarjoin+=' %s OUTER JOIN %s ON (%s.%s=%s)'%(outertype,self._state.tables[rhstable][1],rid,restrattr,rhssql)toreplace.append(rhstable)self.replace_tables_by_outer_join(join,maintable,*toreplace)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. """contextrels={}attrvars=self._state.attr_varsforvarinrhs_vars:try:contextrels[var.name]=attrvars[var.name]exceptKeyError:attrvars[var.name]=relationifnotcontextrels:relation.children[1].accept(self,contextrels)return''# at least one variable is already in attr_vars, this means we have to# generate unification expressionlhssql=self._inlined_var_sql(relation.children[0].variable,relation.r_type)return'%s%s'%(lhssql,relation.children[1].accept(self,contextrels))def_visit_attribute_relation(self,relation):"""generate SQL for an attribute relation"""lhs,rhs=relation.get_parts()rhssql=rhs.accept(self)table=self._var_table(lhs.variable)iftableisNone:assertrelation.r_type=='eid'lhssql=lhs.accept(self)else:try:lhssql=self._varmap['%s.%s'%(lhs.name,relation.r_type)]exceptKeyError:lhssql='%s.%s'%(table,relation.r_type)try:ifrelation._q_needcast=='TODAY':sql='DATE(%s)%s'%(lhssql,rhssql)# XXX which cast function should be used#elif relation._q_needcast == 'NOW':# sql = 'TIMESTAMP(%s)%s' % (lhssql, rhssql)else:sql='%s%s'%(lhssql,rhssql)exceptAttributeError:sql='%s%s'%(lhssql,rhssql)iflhs.variable.stinfo['optrelations']:self.add_outer_join_condition(lhs.variable,table,sql)else:returnsqldef_visit_has_text_relation(self,relation):"""generate SQL for a has_text relation"""lhs,rhs=relation.get_parts()const=rhs.children[0]alias=self._fti_table(relation)jointo=lhs.accept(self)restriction=''lhsvar=lhs.variableme_is_principal=lhsvar.stinfo.get('principal')isrelationifme_is_principal:ifnotlhsvar.stinfo['typerels']:# the variable is using the fti table, no join neededjointo=Noneelifnotlhsvar.nameinself._varmap:# join on entities instead of etype's table to get result for# external entities on multisources configurationsealias=lhsvar._q_sqltable=lhsvar.namejointo=lhsvar._q_sql='%s.eid'%ealiasself.add_table('entities AS %s'%ealias,ealias)ifnotlhsvar._q_invariantorlen(lhsvar.stinfo['possibletypes'])==1:restriction=" AND %s.type='%s'"%(ealias,self._state.solution[lhs.name])else:etypes=','.join("'%s'"%etypeforetypeinlhsvar.stinfo['possibletypes'])restriction=" AND %s.type IN (%s)"%(ealias,etypes)ifisinstance(relation.parent,Not):self._state.done.add(relation.parent)not_=Trueelse:not_=Falsereturnself.dbms_helper.fti_restriction_sql(alias,const.eval(self._args),jointo,not_)+restrictiondefvisit_comparison(self,cmp,contextrels=None):"""generate SQL for a comparaison"""iflen(cmp.children)==2:lhs,rhs=cmp.childrenelse:lhs=Nonerhs=cmp.children[0]operator=cmp.operatorifoperatorin('IS','LIKE','ILIKE'):ifoperator=='ILIKE'andnotself.dbms_helper.ilike_support:operator=' LIKE 'else:operator=' %s '%operatorelifisinstance(rhs,Function)andrhs.name=='IN':assertoperator=='='operator=' 'iflhsisNone:return'%s%s'%(operator,rhs.accept(self,contextrels))return'%s%s%s'%(lhs.accept(self,contextrels),operator,rhs.accept(self,contextrels))defvisit_mathexpression(self,mexpr,contextrels=None):"""generate SQL for a mathematic expression"""lhs,rhs=mexpr.get_parts()# check for string concatenationoperator=mexpr.operatortry:ifmexpr.operator=='+'andmexpr.get_type(self._state.solution,self._args)=='String':operator='||'exceptCoercionError:passreturn'(%s%s%s)'%(lhs.accept(self,contextrels),operator,rhs.accept(self,contextrels))defvisit_function(self,func,contextrels=None):"""generate SQL name for a function"""# function_description will check function is supported by the backendself.dbms_helper.function_description(func.name)return'%s(%s)'%(func.name,', '.join(c.accept(self,contextrels)forcinfunc.children))defvisit_constant(self,constant,contextrels=None):"""generate SQL name for a constant"""value=constant.valueifconstant.typeisNone:return'NULL'ifconstant.type=='Int'andisinstance(constant.parent,SortTerm):returnconstant.valueifconstant.typein('Date','Datetime'):rel=constant.relation()ifrelisnotNone:rel._q_needcast=valuereturnself.keyword_map[value]()ifconstant.type=='Substitute':_id=constant.valueifisinstance(_id,unicode):_id=_id.encode()else:_id=str(id(constant)).replace('-','',1)ifisinstance(value,unicode):value=value.encode(self.dbencoding)self._query_attrs[_id]=valuereturn'%%(%s)s'%_iddefvisit_variableref(self,variableref,contextrels=None):"""get the sql name for a variable reference"""# use accept, .variable may be a variable or a columnaliasreturnvariableref.variable.accept(self,contextrels)defvisit_columnalias(self,colalias,contextrels=None):"""get the sql name for a subquery column alias"""ifcolalias.nameinself._varmap:sql=self._varmap[colalias.name]self.add_table(sql.split('.',1)[0])returnsqlreturncolalias._q_sqldefvisit_variable(self,variable,contextrels=None):"""get the table name and sql string for a variable"""ifcontextrelsisNoneandvariable.nameinself._state.done:ifself._in_wrapping_query:return'T1.%s'%self._state.aliases[variable.name]returnvariable._q_sqlself._state.done.add(variable.name)vtablename=NoneifcontextrelsisNoneandvariable.nameinself._varmap:sql,vtablename=self._var_info(variable)elifvariable.stinfo['attrvar']:# attribute variable (systematically used in rhs of final# relation(s)), get table name and sql from any rhs relationsql=self._linked_var_sql(variable,contextrels)elifvariable._q_invariant:# since variable is invariant, we know we won't found final relationprincipal=variable.stinfo['principal']ifprincipalisNone:vtablename=variable.nameself.add_table('entities AS %s'%variable.name,vtablename)sql='%s.eid'%vtablenameifvariable.stinfo['typerels']:# add additional restriction on entities.type columnpts=variable.stinfo['possibletypes']iflen(pts)==1:etype=iter(variable.stinfo['possibletypes']).next()restr="%s.type='%s'"%(vtablename,etype)else:etypes=','.join("'%s'"%etforetinpts)restr='%s.type IN (%s)'%(vtablename,etypes)self._state.add_restriction(restr)elifprincipal.r_type=='has_text':sql='%s.%s'%(self._fti_table(principal),self.dbms_helper.fti_uid_attr)elifprincipalinvariable.stinfo['rhsrelations']:ifself.schema.rschema(principal.r_type).inlined:sql=self._linked_var_sql(variable,contextrels)else:sql='%s.eid_to'%self._relation_table(principal)else:sql='%s.eid_from'%self._relation_table(principal)else:# standard variable: get table name according to etype and use .eid# attributesql,vtablename=self._var_info(variable)variable._q_sqltable=vtablenamevariable._q_sql=sqlreturnsql# various utilities #######################################################def_extra_join_sql(self,relation,sql,var):# if rhs var is invariant, and this relation is not its principal,# generate extra jointry:ifnotvar.stinfo['principal']isrelation:# need a predicable result for testsreturn'%s=%s'%tuple(sorted((sql,var.accept(self))))exceptKeyError:# no principal defined, relation is necessarily the principal and# so nothing to return herepassreturn''def_var_info(self,var):# if current var or one of its attribute is selected , it *must*# appear in the toplevel's FROM even if we're currently visiting# a EXISTS nodeifvar.sqlscopeisvar.stmt:scope=0else:scope=-1try:sql=self._varmap[var.name]table=sql.split('.',1)[0]ifscope==-1:scope=self._varmap_table_scope(var.stmt,table)self.add_table(table,scope=scope)exceptKeyError:etype=self._state.solution[var.name]# XXX this check should be moved in rql.stcheckifself.schema.eschema(etype).is_final():raiseBadRQLQuery(var.stmt.root)table=var.namesql='%s.eid'%tableself.add_table('%s AS %s'%(etype,table),table,scope=scope)returnsql,tabledef_inlined_var_sql(self,var,rtype):try:sql=self._varmap['%s.%s'%(var.name,rtype)]scope=var.sqlscopeisvar.stmtand0or-1self.add_table(sql.split('.',1)[0],scope=scope)exceptKeyError:sql='%s.%s'%(self._var_table(var),rtype)#self._state.done.add(var.name)returnsqldef_linked_var_sql(self,variable,contextrels=None):ifcontextrelsisNone:try:returnself._varmap[variable.name]exceptKeyError:passrel=(contextrelsandcontextrels.get(variable.name)orvariable.stinfo.get('principal')oriter(variable.stinfo['rhsrelations']).next())linkedvar=rel.children[0].variableifrel.r_type=='eid':returnlinkedvar.accept(self)ifisinstance(linkedvar,ColumnAlias):raiseBadRQLQuery('variable %s should be selected by the subquery'%variable.name)try:sql=self._varmap['%s.%s'%(linkedvar.name,rel.r_type)]exceptKeyError:linkedvar.accept(self)sql='%s.%s'%(linkedvar._q_sqltable,rel.r_type)returnsql# tables handling #########################################################defalias_and_add_table(self,tablename):alias='%s%s'%(tablename,self._state.count)self._state.count+=1self.add_table('%s AS %s'%(tablename,alias),alias)returnaliasdefadd_table(self,table,key=None,scope=-1):ifkeyisNone:key=tableifkeyinself._state.tables:returnself._state.tables[key]=(len(self._state.actual_tables)-1,table)self._state.actual_tables[scope].append(table)defreplace_tables_by_outer_join(self,substitute,lefttable,*tables):fortableintables:try:scope,alias=self._state.tables[table]self._state.actual_tables[scope].remove(alias)exceptValueError:# huum, not sure about what should be done heremsg="%s already used in an outer join, don't know what to do!"raiseException(msg%table)try:tablealias=self._state.outer_tables[lefttable]actualtables=self._state.actual_tables[-1]exceptKeyError:tablescope,tablealias=self._state.tables[lefttable]actualtables=self._state.actual_tables[tablescope]outerjoin='%s%s'%(tablealias,substitute)self._update_outer_tables(lefttable,actualtables,tablealias,outerjoin)fortableintables:self._state.outer_tables[table]=outerjoindefadd_outer_join_condition(self,var,table,condition):try:tablealias=self._state.outer_tables[table]actualtables=self._state.actual_tables[-1]exceptKeyError:forrelinvar.stinfo['optrelations']:self.visit_relation(rel)assertself._state.outer_tablesself.add_outer_join_condition(var,table,condition)returnbefore,after=tablealias.split(' AS %s '%table,1)beforep,afterp=after.split(')',1)outerjoin='%s AS %s%s AND %s) %s'%(before,table,beforep,condition,afterp)self._update_outer_tables(table,actualtables,tablealias,outerjoin)def_update_outer_tables(self,table,actualtables,oldalias,newalias):actualtables.remove(oldalias)actualtables.append(newalias)# some tables which have already been used as outer table and replaced# by <oldalias> may not be reused here, though their associated value# in the outer_tables dict has to be updated as wellfortable,outerexprinself._state.outer_tables.iteritems():ifouterexpr==oldalias:self._state.outer_tables[table]=newaliasself._state.outer_tables[table]=newaliasdef_var_table(self,var):var.accept(self)#.visit_variable(var)returnvar._q_sqltabledef_relation_table(self,relation):"""return the table alias used by the given relation"""ifrelationinself._state.done:returnrelation._q_sqltableassertnotself.schema.rschema(relation.r_type).is_final(),relation.r_typerid='rel_%s%s'%(relation.r_type,self._state.count)# relation's table is belonging to the root scope if it is the principal# table of one of it's variable and if that variable belong's to parent # scopeforvarrefinrelation.iget_nodes(VariableRef):var=varref.variableifisinstance(var,ColumnAlias):scope=0break# XXX may have a principal without being invariant for this generation,# not sure this is a pb or notifvar.stinfo.get('principal')isrelationandvar.sqlscopeisvar.stmt:scope=0breakelse:scope=-1self._state.count+=1self.add_table('%s_relation AS %s'%(relation.r_type,rid),rid,scope=scope)relation._q_sqltable=ridself._state.done.add(relation)returnriddef_fti_table(self,relation):ifrelationinself._state.done:try:returnrelation._q_sqltableexceptAttributeError:passself._state.done.add(relation)alias=self.alias_and_add_table(self.dbms_helper.fti_table)relation._q_sqltable=aliasreturnaliasdef_varmap_table_scope(self,select,table):"""since a varmap table may be used for multiple variable, its scope is the most outer scope of each variables """scope=-1forvarname,aliasinself._varmap.iteritems():# check '.' in varname since there are 'X.attribute' keys in varmapifnot'.'invarnameandalias.split('.',1)[0]==table:ifselect.defined_vars[varname].sqlscopeisselect:return0returnscope