--- 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)
--- 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):
--- /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 <http://www.gnu.org/licenses/>.
+"""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()
--- 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
--- 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
--- 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
--- 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)
--- 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 <leftalias> <outertype> JOIN <rightalias> ON <condition>
+ """
+ assert leftalias != rightalias, leftalias
+ outer_tables = self.outer_tables
+ louter, lconditions, lchain = outer_tables.get(leftalias,
+ (None, None, None))
+ router, rconditions, rchain = outer_tables.get(rightalias,
+ (None, None, None))
+ if lchain is None and rchain is None:
+ # create a new outer chaine
+ chain = [leftalias, rightalias]
+ outer_tables[leftalias] = (None, [], chain)
+ outer_tables[rightalias] = (outertype, [condition], chain)
+ self.outer_chains.append(chain)
+ self.mark_as_used_in_outer_join(leftalias, addpending=False)
+ self.mark_as_used_in_outer_join(rightalias)
+ elif lchain is None:
+ # [A > B > C] + [D > A] -> [D > A > B > C]
+ if rightalias == rchain[0]:
+ outer_tables[leftalias] = (None, [], rchain)
+ conditions = outer_tables[rightalias][1] + [condition]
+ outer_tables[rightalias] = (outertype, conditions, rchain)
+ rchain.insert(0, leftalias)
+ else:
+ # [A > B > C] + [D > B] -> [A > B > C < D]
+ if outertype == 'LEFT':
+ outertype = 'RIGHT'
+ outer_tables[leftalias] = (outertype, [condition], rchain)
+ rchain.append(leftalias)
+ self.mark_as_used_in_outer_join(leftalias)
+ elif rchain is None:
+ # [A > B > C] + [B > D] -> [A > B > C > D]
+ outer_tables[rightalias] = (outertype, [condition], lchain)
+ lchain.append(rightalias)
+ self.mark_as_used_in_outer_join(rightalias)
+ elif lchain is rchain:
+ # already in the same chain, simply check compatibility and append
+ # the condition if it's ok
+ lidx = lchain.index(leftalias)
+ ridx = lchain.index(rightalias)
+ if (outertype == 'FULL' and router != 'FULL') \
+ or (lidx < ridx and router != 'LEFT') \
+ or (ridx < lidx and louter != 'RIGHT'):
+ raise BadRQLQuery()
+ # merge conditions
+ if lidx < ridx:
+ rconditions.append(condition)
+ else:
+ lconditions.append(condition)
+ 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 <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
-
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
--- 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()
--- 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:
--- 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)
--- 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?',