cubicweb: comparison server/sources/rql2sql.py

equal deleted inserted replaced

-:672acc730ce5
+:d628defebc17
 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
 from cubicweb import server
 from cubicweb.server.sqlutils import SQL_PREFIX
 from cubicweb.server.utils import cleanup_solutions
 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
 newivar = _new_var(newselect, vref.name)
 newselect.selection.append(VariableRef(newivar))
 _fill_to_wrap_rel(vref.variable, newselect, towrap, schema)
 elif rschema.is_final():
 towrap.add( (var, rel) )
 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
 """
 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.stinfo['optrelations'].add(newrel)
 # extract subquery solutions
 solutions = [sol.copy() for sol in solutions]
 cleanup_solutions(newselect, solutions)
 newselect.set_possible_types(solutions)
 # full sub-query
 append(term)
 if groups:
 for vref in term.iget_nodes(VariableRef):
 if not vref in groups:
 groups.append(vref)
 def fix_selection(rqlst, selectedidx, needwrap, sorts, groups, having):
 if sorts:
 sort_term_selection(sorts, selectedidx, rqlst, not needwrap and groups)
 if needwrap:
 if groups:
 class StateInfo(object):
 def __init__(self, existssols, unstablevars):
 self.existssols = existssols
 self.unstablevars = unstablevars
 self.subtables = {}
 def reset(self, solution):
 """reset some visit variables"""
 self.solution = solution
 self.count = 0
 self.done = set()
 self.duplicate_switches = []
 self.attr_vars = {}
 self.aliases = {}
 self.restrictions = []
 self._restr_stack = []
 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]
 def pop_scope(self):
 restrictions = self.restrictions
 self.restrictions = self._restr_stack.pop()
 return restrictions, self.actual_tables.pop()
 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, dbms_helper, dbencoding='UTF-8'):
 self.schema = schema
 self.dbms_helper = dbms_helper
 self.dbencoding = dbencoding
 self.keyword_map = {'NOW' : self.dbms_helper.sql_current_timestamp,
 'TODAY': self.dbms_helper.sql_current_date,
 }
 if not self.dbms_helper.union_parentheses_support:
 self.union_sql = self.noparen_union_sql
 self._lock = threading.Lock()
 def generate(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
 # 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 dictionnary from a RQL syntax tree
 :select: a selection statement of the syntax tree (`rql.stmts.Select`)
 :solution: a dictionnary containing variables binding.
 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(existssols, unstable)
 # treat subqueries
 self._subqueries_sql(select, state)
 sql += '\nGROUP BY %s' % groups
 if having:
 sql += '\nHAVING %s' % having
 # sort
 if sorts:
 sql += '\nORDER BY %s' % ','.join(self._sortterm_sql(sortterm,
 fselectidx)
 for sortterm in sorts)
 if fneedwrap:
 selection = ['T1.C%s' % i for i in xrange(len(origselection))]
 sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
 return '\nINTERSECT ALL\n'.join(sqls)
 elif 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:
 if res:
 if len(res) > 1:
 return '(%s)' % ' OR '.join(res)
 return res[0]
 return ''
 def visit_not(self, node):
 self._state.push_scope()
 csql = node.children[0].accept(self)
 sqls, tables = self._state.pop_scope()
 if node in self._state.done or not csql:
 if sql:
 sqls.append(sql)
 if not sqls:
 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()
 if restriction:
 restriction = ' AND '.join(restrictions)
 if not restriction:
 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' % (', '.join(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():
 termsql = termvar.accept(self)
 yield '%s.%s=%s' % (rid, relfield, termsql)
 extrajoin = self._extra_join_sql(relation, '%s.%s' % (rid, relfield), termvar)
 if extrajoin:
 yield extrajoin
 def _visit_relation(self, relation, rschema):
 """generate SQL for a relation
 implements optimization 1.
 """
 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:
 # at least one variable is already in attr_vars, this means we have to
 # generate unification expression
 lhssql = 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)
 not_ = True
 else:
 not_ = False
 return self.dbms_helper.fti_restriction_sql(alias, const.eval(self._args),
 jointo, not_) + restriction
 def visit_comparison(self, cmp, contextrels=None):
 """generate SQL for a comparaison"""
 if len(cmp.children) == 2:
 lhs, rhs = cmp.children
 else:
 operator = ' '
 if lhs is None:
 return '%s%s'% (operator, rhs.accept(self, contextrels))
 return '%s%s%s'% (lhs.accept(self, contextrels), operator,
 rhs.accept(self, contextrels))
 def visit_mathexpression(self, mexpr, contextrels=None):
 """generate SQL for a mathematic expression"""
 lhs, rhs = mexpr.get_parts()
 # check for string concatenation
 operator = mexpr.operator
 operator = '||'
 except CoercionError:
 pass
 return '(%s %s %s)'% (lhs.accept(self, contextrels), operator,
 rhs.accept(self, contextrels))
 def visit_function(self, func, contextrels=None):
 """generate SQL name for a function"""
 # function_description will check function is supported by the backend
 sqlname = self.dbms_helper.func_sqlname(func.name)
 return '%s(%s)' % (sqlname, ', '.join(c.accept(self, contextrels)
 for c in func.children))
 def visit_constant(self, constant, contextrels=None):
 """generate SQL name for a constant"""
 _id = str(id(constant)).replace('-', '', 1)
 if isinstance(value, unicode):
 value = value.encode(self.dbencoding)
 self._query_attrs[_id] = value
 return '%%(%s)s' % _id
 def visit_variableref(self, variableref, contextrels=None):
 """get the sql name for a variable reference"""
 # use accept, .variable may be a variable or a columnalias
 return variableref.variable.accept(self, contextrels)
 colalias._q_sqltable = table
 colalias._q_sql = sql
 self.add_table(table)
 return sql
 return colalias._q_sql
 def visit_variable(self, variable, contextrels=None):
 """get the table name and sql string for a variable"""
 if contextrels is None and 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 contextrels is None 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, contextrels)
 elif variable._q_invariant:
 except KeyError:
 # no principal defined, relation is necessarily the principal and
 # so nothing to return here
 pass
 return ''
 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 node
 if var.sqlscope is var.stmt:
 raise BadRQLQuery(var.stmt.root)
 table = var.name
 sql = '%s.%seid' % (table, SQL_PREFIX)
 self.add_table('%s%s AS %s' % (SQL_PREFIX, etype, table), table, scope=scope)
 return sql, table
 def _inlined_var_sql(self, var, rtype):
 try:
 sql = self._varmap['%s.%s' % (var.name, rtype)]
 scope = var.sqlscope is var.stmt and 0 or -1
 self.add_table(sql.split('.', 1)[0], scope=scope)
 except KeyError:
 sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype)
 #self._state.done.add(var.name)
 return sql
 def _linked_var_sql(self, variable, contextrels=None):
 if contextrels is None:
 try:
 return self._varmap[variable.name]
 except KeyError:
 pass
 rel = (contextrels and contextrels.get(variable.name) or
 variable.stinfo.get('principal') or
 iter(variable.stinfo['rhsrelations']).next())
 linkedvar = rel.children[0].variable
 if rel.r_type == 'eid':
 return linkedvar.accept(self)
 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:
 linkedvar.accept(self)
 sql = '%s.%s%s' % (linkedvar._q_sqltable, SQL_PREFIX, rel.r_type)
 return sql
 # tables handling #########################################################
 def alias_and_add_table(self, tablename):
 alias = '%s%s' % (tablename, self._state.count)
 self._state.count += 1
 self.add_table('%s AS %s' % (tablename, alias), alias)
 return alias
 def add_table(self, table, key=None, scope=-1):
 if key is None:
 key = table
 if key in self._state.tables:
 return
 self._state.tables[key] = (len(self._state.actual_tables) - 1, table)
 self._state.actual_tables[scope].append(table)
 def replace_tables_by_outer_join(self, substitute, lefttable, *tables):
 for table in tables:
 try:
 scope, alias = self._state.tables[table]
 self._state.actual_tables[scope].remove(alias)
 # by <oldalias> may not be reused here, though their associated value
 # in the outer_tables dict has to be updated as well
 for table, outerexpr in self._state.outer_tables.iteritems():
 if outerexpr == oldalias:
 self._state.outer_tables[table] = newalias
 self._state.outer_tables[table] = newalias
 def _var_table(self, var):
 var.accept(self)#.visit_variable(var)
 return var._q_sqltable
 def _relation_table(self, relation):
 if relation in self._state.done:
 return relation._q_sqltable
 assert not self.schema.rschema(relation.r_type).is_final(), relation.r_type
 rid = '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
 # scope
 for varref in relation.iget_nodes(VariableRef):
 var = varref.variable
 if isinstance(var, ColumnAlias):
 scope = 0
 self._state.count += 1
 self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope)
 relation._q_sqltable = rid
 self._state.done.add(relation)
 return rid
 def _fti_table(self, relation):
 if relation in self._state.done:
 try:
 return relation._q_sqltable
 except AttributeError:
 pass
 self._state.done.add(relation)
 alias = self.alias_and_add_table(self.dbms_helper.fti_table)
 relation._q_sqltable = alias
 return alias
 def _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 = -1

branch	tls-sprint
changeset 1802	d628defebc17
parent 1522	47b2ffbee760
child 1862	94dc8ccd320b