# HG changeset patch # User Sylvain Thénault # Date 1302125059 -7200 # Node ID 79686c864bbfcbb1ccb7b336147e5c5f48fe6098 # Parent 496f51b9215412816843cec3cb1ef9d52ea40803# Parent 7eaef037ea9db1376a39ba195d67f11b78e4d78f backport stable diff -r 496f51b92154 -r 79686c864bbf hooks/syncschema.py --- a/hooks/syncschema.py Wed Apr 06 16:01:19 2011 +0200 +++ b/hooks/syncschema.py Wed Apr 06 23:24:19 2011 +0200 @@ -913,8 +913,8 @@ entity = self.entity if entity.cw_edited.get('final'): # final entity types don't need a table in the database and are - # systematically added by yams at initialization time so there is no - # need to do further processing. Simply assign its eid. + # systematically added by yams at schema initialization time so + # there is no need to do further processing. Simply assign its eid. self._cw.vreg.schema[entity.name].eid = entity.eid return CWETypeAddOp(self._cw, entity=entity) diff -r 496f51b92154 -r 79686c864bbf hooks/test/unittest_hooks.py --- a/hooks/test/unittest_hooks.py Wed Apr 06 16:01:19 2011 +0200 +++ b/hooks/test/unittest_hooks.py Wed Apr 06 23:24:19 2011 +0200 @@ -145,29 +145,6 @@ self.failIf(self.execute('Any X WHERE X created_by Y, X eid >= %(x)s', {'x': eid})) -class CWPropertyHooksTC(CubicWebTC): - - def test_unexistant_eproperty(self): - with self.assertRaises(ValidationError) as cm: - self.execute('INSERT CWProperty X: X pkey "bla.bla", X value "hop", X for_user U') - self.assertEqual(cm.exception.errors, {'pkey-subject': 'unknown property key bla.bla'}) - with self.assertRaises(ValidationError) as cm: - self.execute('INSERT CWProperty X: X pkey "bla.bla", X value "hop"') - self.assertEqual(cm.exception.errors, {'pkey-subject': 'unknown property key bla.bla'}) - - def test_site_wide_eproperty(self): - with self.assertRaises(ValidationError) as cm: - self.execute('INSERT CWProperty X: X pkey "ui.site-title", X value "hop", X for_user U') - self.assertEqual(cm.exception.errors, {'for_user-subject': "site-wide property can't be set for user"}) - - def test_bad_type_eproperty(self): - with self.assertRaises(ValidationError) as cm: - self.execute('INSERT CWProperty X: X pkey "ui.language", X value "hop", X for_user U') - self.assertEqual(cm.exception.errors, {'value-subject': u'unauthorized value'}) - with self.assertRaises(ValidationError) as cm: - self.execute('INSERT CWProperty X: X pkey "ui.language", X value "hop"') - self.assertEqual(cm.exception.errors, {'value-subject': u'unauthorized value'}) - class SchemaHooksTC(CubicWebTC): diff -r 496f51b92154 -r 79686c864bbf hooks/test/unittest_syncsession.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/hooks/test/unittest_syncsession.py Wed Apr 06 23:24:19 2011 +0200 @@ -0,0 +1,59 @@ +# -*- coding: utf-8 -*- +# copyright 2003-2011 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 . +"""functional tests for core hooks + +Note: + syncschema.py hooks are mostly tested in server/test/unittest_migrations.py +""" +from __future__ import with_statement + +from cubicweb import ValidationError +from cubicweb.devtools.testlib import CubicWebTC + +class CWPropertyHooksTC(CubicWebTC): + + def test_unexistant_cwproperty(self): + with self.assertRaises(ValidationError) as cm: + self.execute('INSERT CWProperty X: X pkey "bla.bla", X value "hop", X for_user U') + self.assertEqual(cm.exception.errors, {'pkey-subject': 'unknown property key bla.bla'}) + with self.assertRaises(ValidationError) as cm: + self.execute('INSERT CWProperty X: X pkey "bla.bla", X value "hop"') + self.assertEqual(cm.exception.errors, {'pkey-subject': 'unknown property key bla.bla'}) + + def test_site_wide_cwproperty(self): + with self.assertRaises(ValidationError) as cm: + self.execute('INSERT CWProperty X: X pkey "ui.site-title", X value "hop", X for_user U') + self.assertEqual(cm.exception.errors, {'for_user-subject': "site-wide property can't be set for user"}) + + def test_system_cwproperty(self): + with self.assertRaises(ValidationError) as cm: + self.execute('INSERT CWProperty X: X pkey "system.version.cubicweb", X value "hop", X for_user U') + self.assertEqual(cm.exception.errors, {'for_user-subject': "site-wide property can't be set for user"}) + + def test_bad_type_cwproperty(self): + with self.assertRaises(ValidationError) as cm: + self.execute('INSERT CWProperty X: X pkey "ui.language", X value "hop", X for_user U') + self.assertEqual(cm.exception.errors, {'value-subject': u'unauthorized value'}) + with self.assertRaises(ValidationError) as cm: + self.execute('INSERT CWProperty X: X pkey "ui.language", X value "hop"') + self.assertEqual(cm.exception.errors, {'value-subject': u'unauthorized value'}) + +if __name__ == '__main__': + from logilab.common.testlib import unittest_main + unittest_main() diff -r 496f51b92154 -r 79686c864bbf server/__init__.py --- a/server/__init__.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/__init__.py Wed Apr 06 23:24:19 2011 +0200 @@ -181,6 +181,7 @@ session.execute('SET X owned_by U WHERE X is IN (CWGroup,CWSource), U eid %(u)s', {'u': admin.eid}) session.commit() + session.close() repo.shutdown() # reloging using the admin user config._cubes = None # avoid assertion error @@ -204,7 +205,6 @@ repo.system_source.init_creating() cnx.commit() cnx.close() - session.close() repo.shutdown() # restore initial configuration config.creating = False diff -r 496f51b92154 -r 79686c864bbf server/msplanner.py --- a/server/msplanner.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/msplanner.py Wed Apr 06 23:24:19 2011 +0200 @@ -1223,11 +1223,22 @@ def build_non_final_part(self, select, solindices, sources, insertedvars, table): """non final step, will have to store results in a temporary table""" + inputmapkey = tuple(sorted(solindices)) solutions = [self._solutions[i] for i in solindices] - rqlst = self.plan.finalize(select, solutions, insertedvars) - step = FetchStep(self.plan, rqlst, sources, table, False) + # XXX be smarter vs rql comparison + idx_key = (select.as_string(), inputmapkey, + tuple(sorted(sources)), tuple(sorted(insertedvars))) + try: + # if a similar step has already been process, simply backport its + # input map + step = self.plan.ms_steps_idx[idx_key] + except KeyError: + # processing needed + rqlst = self.plan.finalize(select, solutions, insertedvars) + step = FetchStep(self.plan, rqlst, sources, table, False) + self.plan.ms_steps_idx[idx_key] = step + self.plan.add_step(step) # update input map for following steps, according to processed solutions - inputmapkey = tuple(sorted(solindices)) inputmap = self._inputmaps.setdefault(inputmapkey, {}) for varname, mapping in step.outputmap.iteritems(): if varname in inputmap and not '.' in varname and \ @@ -1235,7 +1246,6 @@ self._schema.eschema(solutions[0][varname]).final): self._conflicts.append((varname, inputmap[varname])) inputmap.update(step.outputmap) - self.plan.add_step(step) class MSPlanner(SSPlanner): @@ -1259,6 +1269,7 @@ print 'PLANNING', rqlst ppis = [PartPlanInformation(plan, select, self.rqlhelper) for select in rqlst.children] + plan.ms_steps_idx = {} steps = self._union_plan(plan, ppis) if server.DEBUG & server.DBG_MS: from pprint import pprint @@ -1409,9 +1420,7 @@ steps.append(ppi.build_final_part(minrqlst, solindices, inputmap, sources, insertedvars)) else: - table = '_T%s%s' % (''.join(sorted(v._ms_table_key() for v in terms)), - ''.join(sorted(str(i) for i in solindices))) - table = plan.make_temp_table_name(table) + table = plan.make_temp_table_name('T%s' % make_uid(id(select))) ppi.build_non_final_part(minrqlst, solindices, sources, insertedvars, table) # finally: join parts, deal with aggregat/group/sorts if necessary diff -r 496f51b92154 -r 79686c864bbf server/rqlannotation.py --- a/server/rqlannotation.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/rqlannotation.py Wed Apr 06 23:24:19 2011 +0200 @@ -98,10 +98,19 @@ # variable of an inlined relation if not rel in stinfo['rhsrelations'] and rschema.inlined: break - else: - # variable used as main variable of an optional relation - # can't be invariant + # variable used as main variable of an optional relation can't + # be invariant, unless we can use some other relation as + # reference for the outer join + elif not stinfo['constnode']: break + elif len(stinfo['relations']) == 2: + if onlhs: + ostinfo = rhs.children[0].variable.stinfo + else: + ostinfo = lhs.variable.stinfo + if not any(orel for orel in ostinfo['relations'] + if orel.optional and orel is not rel): + break if rschema.final or (onlhs and rschema.inlined): if rschema.type != 'has_text': # need join anyway if the variable appears in a final or diff -r 496f51b92154 -r 79686c864bbf server/sources/native.py --- a/server/sources/native.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/sources/native.py Wed Apr 06 23:24:19 2011 +0200 @@ -822,13 +822,7 @@ return None def make_temp_table_name(self, table): - try: # XXX remove this once - return self.dbhelper.temporary_table_name(table) - except AttributeError: - import warnings - warnings.warn('Please hg up logilab.database') - return table - + return self.dbhelper.temporary_table_name(table) def temp_table_def(self, selected, sol, table): return make_schema(selected, sol, table, self.dbhelper.TYPE_MAPPING) diff -r 496f51b92154 -r 79686c864bbf server/sources/rql2sql.py --- a/server/sources/rql2sql.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/sources/rql2sql.py Wed Apr 06 23:24:19 2011 +0200 @@ -321,6 +321,31 @@ # 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`, dictionnary 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 @@ -340,7 +365,9 @@ self.actual_tables = [[]] for _, tsql in self.tables.itervalues(): self.actual_tables[-1].append(tsql) + self.outer_chains = [] self.outer_tables = {} + self.outer_pending = {} self.duplicate_switches = [] self.aliases = {} self.restrictions = [] @@ -405,7 +432,208 @@ self.scope_nodes.pop() restrictions = self.restrictions self.restrictions = self._restr_stack.pop() - return restrictions, self.actual_tables.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 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 + break + # 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 JOIN ON + """ + 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) + else: + if louter is not None: + raise BadRQLQuery() + # merge chains + self.outer_chains.remove(lchain) + self.mark_as_used_in_outer_join(leftalias) + rchain += lchain + for alias, (aouter, aconditions, achain) in outer_tables.iteritems(): + if achain is lchain: + outer_tables[alias] = (aouter, aconditions, rchain) + + # 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 @@ -450,6 +678,7 @@ node.parent.remove(node) return fakehaving + class SQLGenerator(object): """ generation of SQL from the fully expanded RQL syntax tree @@ -600,12 +829,12 @@ try: sql = self._solutions_sql(select, morerestr, sols, distinct, needalias or needwrap) - # generate groups / having before wrapping query selection to - # get correct column aliases + # 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 + # 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: @@ -613,9 +842,9 @@ 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) + sql = '%s FROM (%s) AS T1' % ( + self._selection_sql(outerselection, distinct,needalias), + sql) if groups: sql += '\nGROUP BY %s' % groups if having: @@ -628,14 +857,9 @@ 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) - ## sql += '\nORDER BY %s' % ','.join(sqlsortterms) - ## if sorts and fneedwrap: - ## selection = ['T1.C%s' % i for i in xrange(len(origselection))] - ## sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql) + sql = self.dbhelper.sql_add_order_by( + sql, sqlsortterms, origselection, fneedwrap, + select.limit or select.offset) else: sqlsortterms = None state.finalize_source_cbs() @@ -685,10 +909,9 @@ 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.actual_tables[-1] + tables = self._state.tables_sql() if tables: - # sort for test predictability - sql.insert(1, 'FROM %s' % ', '.join(sorted(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)) @@ -775,13 +998,13 @@ restriction = ' AND '.join(restrictions) if not restriction: if tables: - return 'SELECT 1 FROM %s' % ', '.join(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' % (', '.join(tables), restriction) + sql = 'SELECT 1 FROM %s WHERE %s' % (tables, restriction) return sql @@ -822,12 +1045,11 @@ # relation has already been processed by a previous step return '' elif relation.optional: - # check it has not already been treaten (to get necessary - # information to add an outer join condition) - if relation in self._state.done: - return '' # OPTIONAL relation, generate a left|right outer join - sql = self._visit_outer_join_relation(relation, rschema) + 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: @@ -890,7 +1112,7 @@ lhs, rhs = relation.get_parts() return '%s%s' % (lhs.accept(self), rhs.accept(self)) lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation) - rid = self._relation_table(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') @@ -927,74 +1149,104 @@ elif it's a full outer join: -> X FULL OUTER JOIN Y ON (X.relation=Y.eid) """ - lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation) - if relation.optional == 'right': - joinattr, restrattr = 'eid_from', 'eid_to' - else: - lhsvar, rhsvar = rhsvar, lhsvar - lhsconst, rhsconst = rhsconst, lhsconst + 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' - if relation.optional == 'both': - outertype = 'FULL' else: - outertype = 'LEFT' - if rschema.inlined or relation.r_type == 'identity': - self._state.done.add(relation) - t1 = self._var_table(lhsvar) - if relation.r_type == 'identity': - attr = 'eid' - else: - attr = relation.r_type - # reset lhs/rhs, we need the initial order now - lhs, rhs = relation.get_variable_parts() - if '%s.%s' % (lhs.name, attr) in self._varmap: - lhssql = self._varmap['%s.%s' % (lhs.name, attr)] + 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: - lhssql = '%s.%s%s' % (self._var_table(lhs.variable), SQL_PREFIX, attr) - if not rhsvar is None: - t2 = self._var_table(rhsvar) - if t2 is None: - if rhsconst is not None: - # inlined relation with invariant as rhs - condition = '%s=%s' % (lhssql, rhsconst.accept(self)) - if relation.r_type != 'identity': - condition = '(%s OR %s IS NULL)' % (condition, lhssql) - if not lhsvar.stinfo.get('optrelations'): - return condition - self.add_outer_join_condition(lhsvar, t1, condition) - return + # 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: - 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 = lhsconst and lhsconst.accept(self) or lhsvar.accept(self) - rhssql = rhsconst and rhsconst.accept(self) or rhsvar.accept(self) - rid = self._relation_table(relation) - if not lhsvar: - join = '' - toreplace = [] - maintable = rid - else: - 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) - if rhsconst: - join += ' AND %s.%s=%s)' % (rid, restrattr, rhssql) - else: - join += ')' - if not rhsconst: - rhstable = rhsvar._q_sqltable - if rhstable: - assert rhstable is not None, rhsvar - join += ' %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) + 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): + leftvar, leftconst, rightvar, rightconst = relation_info(relation) + assert not (leftconst and rightconst), "doesn't make sense" + if relation.optional != 'right': + leftvar, rightvar = rightvar, leftvar + leftconst, rightconst = rightconst, leftconst + outertype = 'FULL' if relation.optional == 'both' else 'LEFT' + leftalias = self._var_table(leftvar) + attr = 'eid' if relation.r_type == 'identity' else relation.r_type + lhs, rhs = relation.get_variable_parts() + try: + lhssql = self._varmap['%s.%s' % (lhs.name, attr)] + except KeyError: + lhssql = '%s.%s%s' % (self._var_table(lhs.variable), SQL_PREFIX, attr) + if rightvar is not None: + rightalias = self._var_table(rightvar) + if rightalias is None: + if rightconst is not None: + # inlined relation with invariant as rhs + condition = '%s=%s' % (lhssql, rightconst.accept(self)) + if relation.r_type != 'identity': + condition = '(%s OR %s IS NULL)' % (condition, lhssql) + if not leftvar.stinfo.get('optrelations'): + return condition + self._state.add_outer_join_condition(leftalias, condition) + return + self._state.replace_tables_by_outer_join( + leftalias, rightalias, outertype, '%s=%s' % (lhssql, rhs.accept(self))) return '' def _visit_var_attr_relation(self, relation, rhs_vars): @@ -1056,7 +1308,7 @@ except AttributeError: sql = '%s%s' % (lhssql, rhssql) if lhs.variable.stinfo.get('optrelations'): - self.add_outer_join_condition(lhs.variable, table, sql) + self._state.add_outer_join_condition(table, sql) else: return sql @@ -1064,7 +1316,7 @@ """generate SQL for a has_text relation""" lhs, rhs = rel.get_parts() const = rhs.children[0] - alias = self._fti_table(rel) + alias = self._state.fti_table(rel, self.dbhelper.fti_table) jointo = lhs.accept(self) restriction = '' lhsvar = lhs.variable @@ -1078,7 +1330,7 @@ # external entities on multisources configurations ealias = lhsvar._q_sqltable = '_' + lhsvar.name jointo = lhsvar._q_sql = '%s.eid' % ealias - self.add_table('entities AS %s' % ealias, 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: @@ -1141,8 +1393,9 @@ 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._fti_table(rel), - const.eval(self._args)) + 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 @@ -1198,7 +1451,7 @@ table = sql.split('.', 1)[0] colalias._q_sqltable = table colalias._q_sql = sql - self.add_table(table) + self._state.add_table(table) return sql return colalias._q_sql @@ -1222,7 +1475,7 @@ principal = variable.stinfo['principal'] if principal is None: vtablename = '_' + variable.name - self.add_table('entities AS %s' % vtablename, vtablename) + 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 @@ -1235,15 +1488,16 @@ restr = '%s.type IN (%s)' % (vtablename, etypes) self._state.add_restriction(restr) elif principal.r_type == 'has_text': - sql = '%s.%s' % (self._fti_table(principal), + 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._relation_table(principal) + sql = '%s.eid_to' % self._state.relation_table(principal) else: - sql = '%s.eid_from' % self._relation_table(principal) + sql = '%s.eid_from' % self._state.relation_table(principal) else: # standard variable: get table name according to etype and use .eid # attribute @@ -1293,7 +1547,7 @@ sql = self._varmap[key] tablealias = sql.split('.', 1)[0] scope = self._temp_table_scope(term.stmt, tablealias) - self.add_table(tablealias, scope=scope) + self._state.add_table(tablealias, scope=scope) return sql, tablealias def _var_info(self, var): @@ -1307,7 +1561,7 @@ raise BadRQLQuery(var.stmt.root) tablealias = '_' + var.name sql = '%s.%seid' % (tablealias, SQL_PREFIX) - self.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias), + self._state.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias), tablealias, scope=scope) return sql, tablealias @@ -1315,7 +1569,7 @@ try: sql = self._varmap['%s.%s' % (var.name, rtype)] scope = self._state.scopes[var.scope] - self.add_table(sql.split('.', 1)[0], scope=scope) + self._state.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) @@ -1355,107 +1609,6 @@ # tables handling ######################################################### - def alias_and_add_table(self, tablename, scope=-1): - alias = '%s%s' % (tablename, self._state.count) - self._state.count += 1 - self.add_table('%s AS %s' % (tablename, alias), alias, scope) - return alias - - def add_table(self, table, key=None, scope=-1): - if key is None: - key = table - if key in self._state.tables: - return - if scope < 0: - scope = len(self._state.actual_tables) + scope - self._state.tables[key] = (scope, 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) - except ValueError: # huum, not sure about what should be done here - msg = "%s already used in an outer join, don't know what to do!" - raise Exception(msg % table) - try: - tablealias = self._state.outer_tables[lefttable] - actualtables = self._state.actual_tables[-1] - except KeyError: - 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) - for table in tables: - self._state.outer_tables[table] = outerjoin - - def add_outer_join_condition(self, var, table, condition): - try: - tablealias = self._state.outer_tables[table] - actualtables = self._state.actual_tables[-1] - except KeyError: - for rel in var.stinfo.get('optrelations'): - self.visit_relation(rel) - assert self._state.outer_tables - self.add_outer_join_condition(var, table, condition) - return - before, 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) - self._state.outer_tables[table] = newalias - # some tables which have already been used as outer table and replaced - # by 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 - def _var_table(self, var): var.accept(self)#.visit_variable(var) return var._q_sqltable - - def _relation_table(self, relation): - """return the table alias used by the given relation""" - if relation in self._state.done: - return relation._q_sqltable - assert not self.schema.rschema(relation.r_type).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 - break - # 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._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) - scope = self._state.scopes[relation.scope] - alias = self.alias_and_add_table(self.dbhelper.fti_table, scope=scope) - relation._q_sqltable = alias - return alias diff -r 496f51b92154 -r 79686c864bbf server/test/unittest_checkintegrity.py --- a/server/test/unittest_checkintegrity.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/test/unittest_checkintegrity.py Wed Apr 06 23:24:19 2011 +0200 @@ -1,4 +1,4 @@ -# copyright 2003-2010 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved. # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr # # This file is part of CubicWeb. @@ -19,14 +19,16 @@ import sys from StringIO import StringIO from logilab.common.testlib import TestCase, unittest_main -from cubicweb.devtools import init_test_database +from cubicweb.devtools import get_test_db_handler, TestServerConfiguration from cubicweb.server.checkintegrity import check, reindex_entities class CheckIntegrityTC(TestCase): def setUp(self): - self.repo, self.cnx = init_test_database(apphome=self.datadir) + handler = get_test_db_handler(TestServerConfiguration(apphome=self.datadir)) + handler.build_db_cache() + self.repo, self.cnx = handler.get_repo_and_cnx() self.execute = self.cnx.cursor().execute self.session = self.repo._sessions[self.cnx.sessionid] sys.stderr = sys.stdout = StringIO() diff -r 496f51b92154 -r 79686c864bbf server/test/unittest_msplanner.py --- a/server/test/unittest_msplanner.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/test/unittest_msplanner.py Wed Apr 06 23:24:19 2011 +0200 @@ -23,7 +23,7 @@ from yams.buildobjs import RelationDefinition from rql import BadRQLQuery -from cubicweb.devtools import init_test_database +from cubicweb.devtools import get_test_db_handler, TestServerConfiguration from cubicweb.devtools.repotest import BasePlannerTC, test_plan class _SetGenerator(object): @@ -82,7 +82,9 @@ # keep cnx so it's not garbage collected and the associated session is closed def setUpModule(*args): global repo, cnx - repo, cnx = init_test_database(apphome=BaseMSPlannerTC.datadir) + handler = get_test_db_handler(TestServerConfiguration(apphome=BaseMSPlannerTC.datadir)) + handler.build_db_cache() + repo, cnx = handler.get_repo_and_cnx() def tearDownModule(*args): global repo, cnx @@ -2383,6 +2385,56 @@ None, None, [self.system], {}, [])], {'x': 999999, 'u': 999998}) + def test_nonregr_similar_subquery(self): + repo._type_source_cache[999999] = ('Personne', 'system', 999999) + self._test('Any T,TD,U,T,UL WITH T,TD,U,UL BEING (' + '(Any T,TD,U,UL WHERE X eid %(x)s, T comments X, T content TD, T created_by U?, U login UL)' + ' UNION ' + '(Any T,TD,U,UL WHERE X eid %(x)s, X connait P, T comments P, T content TD, T created_by U?, U login UL))', + # XXX optimization: use a OneFetchStep with a UNION of both queries + [('FetchStep', [('Any U,UL WHERE U login UL, U is CWUser', + [{'U': 'CWUser', 'UL': 'String'}])], + [self.ldap, self.system], None, + {'U': 'table0.C0', 'U.login': 'table0.C1', 'UL': 'table0.C1'}, + []), + ('UnionFetchStep', + [('FetchStep', + [('Any T,TD,U,UL WHERE T comments 999999, T content TD, T created_by U?, U login UL, T is Comment, U is CWUser', + [{'T': 'Comment', 'TD': 'String', 'U': 'CWUser', 'UL': 'String'}])], + [self.system], + {'U': 'table0.C0', 'U.login': 'table0.C1', 'UL': 'table0.C1'}, + {'T': 'table1.C0', + 'T.content': 'table1.C1', + 'TD': 'table1.C1', + 'U': 'table1.C2', + 'U.login': 'table1.C3', + 'UL': 'table1.C3'}, + []), + ('FetchStep', + [('Any T,TD,U,UL WHERE 999999 connait P, T comments P, T content TD, T created_by U?, U login UL, P is Personne, T is Comment, U is CWUser', + [{'P': 'Personne', + 'T': 'Comment', + 'TD': 'String', + 'U': 'CWUser', + 'UL': 'String'}])], + [self.system], + {'U': 'table0.C0', 'U.login': 'table0.C1', 'UL': 'table0.C1'}, + {'T': 'table1.C0', + 'T.content': 'table1.C1', + 'TD': 'table1.C1', + 'U': 'table1.C2', + 'U.login': 'table1.C3', + 'UL': 'table1.C3'}, + [])]), + ('OneFetchStep', + [('Any T,TD,U,T,UL', + [{'T': 'Comment', 'TD': 'String', 'U': 'CWUser', 'UL': 'String'}])], + None, None, + [self.system], + {'T': 'table1.C0', 'TD': 'table1.C1', 'U': 'table1.C2', 'UL': 'table1.C3'}, + [])], + {'x': 999999}) + class MSPlannerTwoSameExternalSourcesTC(BasePlannerTC): """test planner related feature on a 3-sources repository: diff -r 496f51b92154 -r 79686c864bbf server/test/unittest_repository.py --- a/server/test/unittest_repository.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/test/unittest_repository.py Wed Apr 06 23:24:19 2011 +0200 @@ -1,5 +1,5 @@ # -*- coding: iso-8859-1 -*- -# copyright 2003-2010 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved. # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr # # This file is part of CubicWeb. @@ -106,7 +106,9 @@ self.failIf(self.execute('Any X WHERE NOT X cw_source S')) def test_connect(self): - self.assert_(self.repo.connect(self.admlogin, password=self.admpassword)) + cnxid = self.repo.connect(self.admlogin, password=self.admpassword) + self.assert_(cnxid) + self.repo.close(cnxid) self.assertRaises(AuthenticationError, self.repo.connect, self.admlogin, password='nimportnawak') self.assertRaises(AuthenticationError, @@ -134,7 +136,9 @@ {'login': u"barnabé", 'passwd': u"héhéhé".encode('UTF8')}) repo.commit(cnxid) repo.close(cnxid) - self.assert_(repo.connect(u"barnabé", password=u"héhéhé".encode('UTF8'))) + cnxid = repo.connect(u"barnabé", password=u"héhéhé".encode('UTF8')) + self.assert_(cnxid) + repo.close(cnxid) def test_rollback_on_commit_error(self): cnxid = self.repo.connect(self.admlogin, password=self.admpassword) @@ -143,6 +147,7 @@ {'login': u"tutetute", 'passwd': 'tutetute'}) self.assertRaises(ValidationError, self.repo.commit, cnxid) self.failIf(self.repo.execute(cnxid, 'CWUser X WHERE X login "tutetute"')) + self.repo.close(cnxid) def test_rollback_on_execute_validation_error(self): class ValidationErrorAfterHook(Hook): @@ -235,6 +240,7 @@ repo.commit(cnxid) result = repo.execute(cnxid, 'Personne X') self.assertEqual(result.rowcount, 1) + repo.close(cnxid) def test_transaction_base2(self): repo = self.repo @@ -246,6 +252,7 @@ repo.rollback(cnxid) result = repo.execute(cnxid, "Any U WHERE U in_group G, U login 'admin', G name 'guests'") self.assertEqual(result.rowcount, 0, result.rows) + repo.close(cnxid) def test_transaction_base3(self): repo = self.repo @@ -260,6 +267,7 @@ repo.rollback(cnxid) rset = repo.execute(cnxid, 'TrInfo T WHERE T wf_info_for X, X eid %(x)s', {'x': user.eid}) self.assertEqual(len(rset), 0) + repo.close(cnxid) def test_transaction_interleaved(self): self.skipTest('implement me') @@ -379,6 +387,7 @@ self.assertEqual(repo.eid2extid(repo.system_source, 2, session), None) class dummysource: uri = 'toto' self.assertRaises(UnknownEid, repo.eid2extid, dummysource, 2, session) + repo.close(cnxid) def test_public_api(self): self.assertEqual(self.repo.get_schema(), self.repo.schema) diff -r 496f51b92154 -r 79686c864bbf server/test/unittest_rql2sql.py --- a/server/test/unittest_rql2sql.py Wed Apr 06 16:01:19 2011 +0200 +++ b/server/test/unittest_rql2sql.py Wed Apr 06 23:24:19 2011 +0200 @@ -190,13 +190,13 @@ "EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", '''SELECT _X.cw_eid FROM cw_Personne AS _X -WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers)))'''), +WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers)))'''), ("Any X WHERE X prenom 'lulu'," "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", '''SELECT _X.cw_eid FROM cw_Personne AS _X -WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers))))'''), +WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers))))'''), ('Any X WHERE X title V, NOT X wikiid V, NOT X title "parent", X is Card', '''SELECT _X.cw_eid @@ -452,7 +452,7 @@ ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))', '''SELECT _T.cw_eid FROM cw_Tag AS _T -WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM tags_relation AS rel_tags0, cw_CWGroup AS _X WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM tags_relation AS rel_tags1, cw_CWUser AS _X WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''), +WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM cw_CWGroup AS _X, tags_relation AS rel_tags0 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X, tags_relation AS rel_tags1 WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''), # must not use a relation in EXISTS scope to inline a variable ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)', @@ -468,7 +468,7 @@ ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))', '''SELECT COUNT(_U.cw_eid) FROM cw_CWUser AS _U -WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''), +WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''), ('Any MAX(X)', '''SELECT MAX(_X.eid) @@ -567,7 +567,7 @@ ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))', '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom FROM concerne_relation AS rel_concerne0, cw_Societe AS _C -WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) +WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM cw_Card AS _N, documented_by_relation AS rel_documented_by2 WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) GROUP BY _C.cw_nom ORDER BY 1 DESC LIMIT 10'''), @@ -723,7 +723,7 @@ ('Any X WHERE NOT Y evaluee X, Y is CWUser', '''SELECT _X.cw_eid FROM cw_Note AS _X -WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0, cw_CWUser AS _Y WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid))'''), +WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _Y, evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid))'''), ('Any X,RT WHERE X relation_type RT, NOT X is CWAttribute', '''SELECT _X.cw_eid, _X.cw_relation_type @@ -816,7 +816,7 @@ ('Any X WHERE X is Affaire, S is Societe, EXISTS(X owned_by U OR (X concerne S?, S owned_by U))', '''SELECT _X.cw_eid FROM cw_Affaire AS _X -WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_CWUser AS _U, cw_Affaire AS _A LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_A.cw_eid) LEFT OUTER JOIN cw_Societe AS _S ON (rel_concerne1.eid_to=_S.cw_eid), owned_by_relation AS rel_owned_by2 WHERE ((rel_owned_by0.eid_from=_A.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid) OR (rel_owned_by2.eid_from=_S.cw_eid AND rel_owned_by2.eid_to=_U.cw_eid)) AND _X.cw_eid=_A.cw_eid)'''), +WHERE EXISTS(SELECT 1 FROM cw_CWUser AS _U, owned_by_relation AS rel_owned_by0, owned_by_relation AS rel_owned_by2, cw_Affaire AS _A LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_A.cw_eid) LEFT OUTER JOIN cw_Societe AS _S ON (rel_concerne1.eid_to=_S.cw_eid) WHERE ((rel_owned_by0.eid_from=_A.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid) OR (rel_owned_by2.eid_from=_S.cw_eid AND rel_owned_by2.eid_to=_U.cw_eid)) AND _X.cw_eid=_A.cw_eid)'''), ('Any C,M WHERE C travaille G?, G evaluee M?, G is Societe', '''SELECT _C.cw_eid, rel_evaluee1.eid_to @@ -827,13 +827,18 @@ 'F name "read", F require_group E, U in_group E)), U eid 1', '''SELECT _A.cw_eid, rel_documented_by0.eid_to FROM cw_Affaire AS _A LEFT OUTER JOIN documented_by_relation AS rel_documented_by0 ON (rel_documented_by0.eid_from=_A.cw_eid) -WHERE ((rel_documented_by0.eid_to IS NULL) OR (EXISTS(SELECT 1 FROM require_permission_relation AS rel_require_permission1, cw_CWPermission AS _F, require_group_relation AS rel_require_group2, in_group_relation AS rel_in_group3 WHERE rel_documented_by0.eid_to=rel_require_permission1.eid_from AND rel_require_permission1.eid_to=_F.cw_eid AND _F.cw_name=read AND rel_require_group2.eid_from=_F.cw_eid AND rel_in_group3.eid_to=rel_require_group2.eid_to AND rel_in_group3.eid_from=1)))'''), +WHERE ((rel_documented_by0.eid_to IS NULL) OR (EXISTS(SELECT 1 FROM cw_CWPermission AS _F, in_group_relation AS rel_in_group3, require_group_relation AS rel_require_group2, require_permission_relation AS rel_require_permission1 WHERE rel_documented_by0.eid_to=rel_require_permission1.eid_from AND rel_require_permission1.eid_to=_F.cw_eid AND _F.cw_name=read AND rel_require_group2.eid_from=_F.cw_eid AND rel_in_group3.eid_to=rel_require_group2.eid_to AND rel_in_group3.eid_from=1)))'''), ("Any X WHERE X eid 12, P? connait X", '''SELECT _X.cw_eid -FROM cw_Personne AS _X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=12) +FROM cw_Personne AS _X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=_X.cw_eid) WHERE _X.cw_eid=12''' ), + ("Any P WHERE X eid 12, P? concerne X, X todo_by S", + '''SELECT rel_concerne0.eid_from +FROM todo_by_relation AS rel_todo_by1 LEFT OUTER JOIN concerne_relation AS rel_concerne0 ON (rel_concerne0.eid_to=12) +WHERE rel_todo_by1.eid_from=12''' + ), ('Any GN, TN ORDERBY GN WHERE T tags G?, T name TN, G name GN', ''' @@ -899,11 +904,11 @@ FROM cw_CWUser AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop) UNION ALL SELECT _G.cw_eid AS C0, _S.cw_eid AS C1 -FROM cw_Note AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop) ) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''), +FROM cw_Note AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop)) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''), ('Any O,AD WHERE NOT S inline1 O, S eid 123, O todo_by AD?', '''SELECT _O.cw_eid, rel_todo_by0.eid_to -FROM cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid), cw_Note AS _S +FROM cw_Note AS _S, cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid) WHERE (_S.cw_inline1 IS NULL OR _S.cw_inline1!=_O.cw_eid) AND _S.cw_eid=123''') ] @@ -1121,7 +1126,7 @@ ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)', '''SELECT _X.cw_nom FROM cw_Personne AS _X -WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0, cw_Division AS _S WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid UNION SELECT 1 FROM travaille_relation AS rel_travaille1, cw_Societe AS _S WHERE rel_travaille1.eid_from=_X.cw_eid AND rel_travaille1.eid_to=_S.cw_eid))'''), +WHERE NOT (EXISTS(SELECT 1 FROM cw_Division AS _S, travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid UNION SELECT 1 FROM cw_Societe AS _S, travaille_relation AS rel_travaille1 WHERE rel_travaille1.eid_from=_X.cw_eid AND rel_travaille1.eid_to=_S.cw_eid))'''), ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)', '''SELECT _S.cw_nom @@ -1212,6 +1217,7 @@ print r, nargs print '!=' print sql.strip() + print 'RQL:', rql raise def _parse(self, rqls): @@ -1406,7 +1412,7 @@ UNION ALL (SELECT _X.cw_eid AS C0 FROM cw_Affaire AS _X -WHERE ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_owned_by0.eid_to=1)) OR (((EXISTS(SELECT 1 FROM cw_Affaire AS _D LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_D.cw_eid) LEFT OUTER JOIN cw_Note AS _B ON (rel_concerne1.eid_to=_B.cw_eid), owned_by_relation AS rel_owned_by2 WHERE rel_owned_by2.eid_from=_B.cw_eid AND rel_owned_by2.eid_to=1 AND _X.cw_eid=_D.cw_eid)) OR (EXISTS(SELECT 1 FROM cw_Affaire AS _F LEFT OUTER JOIN concerne_relation AS rel_concerne3 ON (rel_concerne3.eid_from=_F.cw_eid) LEFT OUTER JOIN cw_Societe AS _E ON (rel_concerne3.eid_to=_E.cw_eid), owned_by_relation AS rel_owned_by4 WHERE rel_owned_by4.eid_from=_E.cw_eid AND rel_owned_by4.eid_to=1 AND _X.cw_eid=_F.cw_eid))))))) AS _T0, cw_CWEType AS _ET, is_relation AS rel_is0 +WHERE ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_owned_by0.eid_to=1)) OR (((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by2, cw_Affaire AS _D LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_D.cw_eid) LEFT OUTER JOIN cw_Note AS _B ON (rel_concerne1.eid_to=_B.cw_eid) WHERE rel_owned_by2.eid_from=_B.cw_eid AND rel_owned_by2.eid_to=1 AND _X.cw_eid=_D.cw_eid)) OR (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by4, cw_Affaire AS _F LEFT OUTER JOIN concerne_relation AS rel_concerne3 ON (rel_concerne3.eid_from=_F.cw_eid) LEFT OUTER JOIN cw_Societe AS _E ON (rel_concerne3.eid_to=_E.cw_eid) WHERE rel_owned_by4.eid_from=_E.cw_eid AND rel_owned_by4.eid_to=1 AND _X.cw_eid=_F.cw_eid))))))) AS _T0, cw_CWEType AS _ET, is_relation AS rel_is0 WHERE rel_is0.eid_from=_T0.C0 AND rel_is0.eid_to=_ET.cw_eid GROUP BY _ET.cw_name'''), )): @@ -1525,7 +1531,7 @@ def test_ambigous_exists_no_from_clause(self): self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))', '''SELECT COUNT(1) -WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''') +WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''') def test_attr_map_sqlcb(self): def generate_ref(gen, linkedvar, rel): @@ -1574,6 +1580,26 @@ '''SELECT 1 WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''') + def test_nonregr_subquery_missing_join(self): + self._check('Any COUNT(P1148),G GROUPBY G ' + 'WHERE G owned_by D, D eid 1122, K1148 bookmarked_by P1148, ' + 'K1148 eid 1148, P1148? in_group G', + '''SELECT COUNT(rel_bookmarked_by1.eid_to), _G.cw_eid +FROM owned_by_relation AS rel_owned_by0, cw_CWGroup AS _G LEFT OUTER JOIN in_group_relation AS rel_in_group2 ON (rel_in_group2.eid_to=_G.cw_eid) LEFT OUTER JOIN bookmarked_by_relation AS rel_bookmarked_by1 ON (rel_in_group2.eid_from=rel_bookmarked_by1.eid_to) +WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122 AND rel_bookmarked_by1.eid_from=1148 +GROUP BY _G.cw_eid''' + ) + + def test_nonregr_subquery_missing_join2(self): + self._check('Any COUNT(P1148),G GROUPBY G ' + 'WHERE G owned_by D, D eid 1122, K1148 bookmarked_by P1148?, ' + 'K1148 eid 1148, P1148? in_group G', + '''SELECT COUNT(rel_bookmarked_by1.eid_to), _G.cw_eid +FROM owned_by_relation AS rel_owned_by0, cw_CWGroup AS _G LEFT OUTER JOIN in_group_relation AS rel_in_group2 ON (rel_in_group2.eid_to=_G.cw_eid) LEFT OUTER JOIN bookmarked_by_relation AS rel_bookmarked_by1 ON (rel_bookmarked_by1.eid_from=1148 AND rel_in_group2.eid_from=rel_bookmarked_by1.eid_to) +WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122 +GROUP BY _G.cw_eid''') + + class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC): backend = 'sqlserver2005' def _norm_sql(self, sql): @@ -1629,7 +1655,7 @@ , ROW_NUMBER() OVER (ORDER BY _L01 DESC) AS __RowNumber FROM ( SELECT COUNT(rel_concerne0.eid_from) AS _L01, _C.cw_nom AS _L02 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C -WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) +WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM cw_Card AS _N, documented_by_relation AS rel_documented_by2 WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) GROUP BY _C.cw_nom ) AS _SQ1 ) SELECT @@ -1878,7 +1904,7 @@ self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))', '''SELECT COUNT(1) FROM (SELECT 1) AS _T -WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''') +WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''') def test_groupby_multiple_outerjoins(self): self._check('Any A,U,P,group_concat(TN) GROUPBY A,U,P WHERE A is Affaire, A concerne N, N todo_by U?, T? tags A, T name TN, A todo_by P?',