def test_mixed_query_chained_explicit_implicit_joins(self):
query = Query()
query.tables.extend(['"product_product"', '"product_template"'])
query.where_clause.append(
"product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id",
"id", "categ_id"),
implicit=False,
outer=False) # add normal join
query.add_join(("product_template__categ_id", "res_user", "user_id",
"id", "user_id"),
implicit=False,
outer=True) # CHAINED outer join
query.tables.append('"account.account"')
query.where_clause.append(
"product_category.expense_account_id = account_account.id"
) # additional implicit join
self.assertEquals(
query.get_sql()[0].strip(),
""""product_product","product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" as "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id"),"account.account" """
.strip())
self.assertEquals(
query.get_sql()[1].strip(),
"""product_product.template_id = product_template.id AND product_category.expense_account_id = account_account.id"""
.strip())
def test_query_chained_explicit_joins(self):
query = Query()
query.tables.extend(['"product_product"', '"product_template"'])
query.where_clause.append("product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id", "id", "categ_id"), implicit=False, outer=False) # add normal join
query.add_join(("product_template__categ_id", "res_user", "user_id", "id", "user_id"), implicit=False, outer=True) # CHAINED outer join
self.assertEquals(query.get_sql()[0].strip(),
""""product_product","product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" as "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id")""".strip())
self.assertEquals(query.get_sql()[1].strip(), """product_product.template_id = product_template.id""".strip())
def test_basic_query(self):
query = Query()
query.tables.extend(['"product_product"', '"product_template"'])
query.where_clause.append("product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id", "id", "categ_id"), implicit=False, outer=False) # add normal join
query.add_join(("product_product", "res_user", "user_id", "id", "user_id"), implicit=False, outer=True) # outer join
self.assertEquals(query.get_sql()[0].strip(),
""""product_product" LEFT JOIN "res_user" as "product_product__user_id" ON ("product_product"."user_id" = "product_product__user_id"."id"),"product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") """.strip())
self.assertEquals(query.get_sql()[1].strip(), """product_product.template_id = product_template.id""".strip())
def _compute_application_count(self):
self.flush(fnames=['email_from'])
# Filter and gather emails at the same time
applicants = self.env['hr.applicant']
mails = set()
for applicant in self:
if applicant.email_from:
applicants |= applicant
mails.add(applicant.email_from.lower())
# Done via SQL since read_group does not support grouping by lowercase field
if mails:
query = Query(self.env.cr, self._table, self._table_query)
query.add_where('LOWER("hr_applicant".email_from) in %s', [tuple(mails)])
self._apply_ir_rules(query)
from_clause, where_clause, where_clause_params = query.get_sql()
query_str = """
SELECT LOWER("%(table)s".email_from) as l_email_from,
COUNT("%(table)s".id) as count
FROM %(from)s
%(where)s
GROUP BY l_email_from
""" % {
'table': self._table,
'from': from_clause,
'where': ('WHERE %s' % where_clause) if where_clause else '',
}
self.env.cr.execute(query_str, where_clause_params)
application_data_mapped = dict((data['l_email_from'], data['count']) for data in self.env.cr.dictfetchall())
else:
application_data_mapped = dict()
for applicant in applicants:
applicant.application_count = application_data_mapped.get(applicant.email_from.lower(), 1)
(self - applicants).application_count = False
def test_mixed_query_chained_explicit_implicit_joins(self):
query = Query(None, 'product_product')
query.add_table('product_template')
query.add_where("product_product.template_id = product_template.id")
# add inner join
alias = query.join("product_template", "categ_id", "product_category",
"id", "categ_id")
self.assertEqual(alias, 'product_template__categ_id')
# add CHAINED left join
alias = query.left_join("product_template__categ_id", "user_id",
"res_user", "id", "user_id")
self.assertEqual(alias, 'product_template__categ_id__user_id')
# additional implicit join
query.add_table('account.account')
query.add_where(
"product_category.expense_account_id = account_account.id")
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(
from_clause,
'"product_product", "product_template", "account.account" JOIN "product_category" AS "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" AS "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id")'
)
self.assertEqual(
where_clause,
"product_product.template_id = product_template.id AND product_category.expense_account_id = account_account.id"
)
def _search_is_authorized(self, operator, value):
if operator not in ('=', '!=', '<>'):
raise ValueError('Invalid operator: %s' % (operator, ))
SS = self.env['sale.subscription']
tbls = (self._table, SS._table)
query = Query(tbls,
["%s.subscription_template_id = %s.template_id" % tbls],
[])
SS._apply_ir_rules(query)
from_clause, where_clause, where_clause_params = query.get_sql()
self.env.cr.execute(
"""
SELECT {self}.id
FROM {from_}
WHERE {where}
""".format(self=self._table, from_=from_clause, where=where_clause),
where_clause_params)
ids = [i[0] for i in self.env.cr.fetchall()]
op = 'in' if (operator == '=' and value) or (
operator != '=' and not value) else 'not in'
return [('id', op, ids)]
def test_table_expression(self):
query = Query(None, 'foo')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(from_clause, '"foo"')
query = Query(None, 'bar', 'SELECT id FROM foo')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(from_clause, '(SELECT id FROM foo) AS "bar"')
query = Query(None, 'foo')
query.add_table('bar', 'SELECT id FROM foo')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(from_clause, '"foo", (SELECT id FROM foo) AS "bar"')
query = Query(None, 'foo')
query.join('foo', 'bar_id', 'SELECT id FROM foo', 'id', 'bar')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(
from_clause,
'"foo" JOIN (SELECT id FROM foo) AS "foo__bar" ON ("foo"."bar_id" = "foo__bar"."id")'
)
def test_query_chained_explicit_joins(self):
query = Query()
query.add_table('product_product')
query.add_table('product_template')
query.where_clause.append(
"product_product.template_id = product_template.id")
query.add_join(("product_template", "product_category", "categ_id",
"id", "categ_id"),
implicit=False,
outer=False) # add normal join
query.add_join(("product_template__categ_id", "res_user", "user_id",
"id", "user_id"),
implicit=False,
outer=True) # CHAINED outer join
self.assertEqual(
query.get_sql()[0].strip(),
""""product_product","product_template" JOIN "product_category" as "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" as "product_template__categ_id__user_id" ON ("product_template__categ_id"."user_id" = "product_template__categ_id__user_id"."id")"""
.strip())
self.assertEqual(
query.get_sql()[1].strip(),
"""product_product.template_id = product_template.id""".strip())
def test_basic_query(self):
query = Query(None, 'product_product')
query.add_table('product_template')
query.add_where("product_product.template_id = product_template.id")
# add inner join
alias = query.join("product_template", "categ_id", "product_category",
"id", "categ_id")
self.assertEqual(alias, 'product_template__categ_id')
# add left join
alias = query.left_join("product_product", "user_id", "res_user", "id",
"user_id")
self.assertEqual(alias, 'product_product__user_id')
from_clause, where_clause, where_params = query.get_sql()
self.assertEqual(
from_clause,
'"product_product", "product_template" JOIN "product_category" AS "product_template__categ_id" ON ("product_template"."categ_id" = "product_template__categ_id"."id") LEFT JOIN "res_user" AS "product_product__user_id" ON ("product_product"."user_id" = "product_product__user_id"."id")'
)
self.assertEqual(where_clause,
"product_product.template_id = product_template.id")
def _compute_application_count(self):
self.flush_model(['email_from'])
applicants = self.env['hr.applicant']
for applicant in self:
if applicant.email_from or applicant.partner_phone or applicant.partner_mobile:
applicants |= applicant
# Done via SQL since read_group does not support grouping by lowercase field
if applicants.ids:
query = Query(self.env.cr, self._table, self._table_query)
query.add_where('hr_applicant.id in %s', [tuple(applicants.ids)])
# Count into the companies that are selected from the multi-company widget
company_ids = self.env.context.get('allowed_company_ids')
if company_ids:
query.add_where('other.company_id in %s', [tuple(company_ids)])
self._apply_ir_rules(query)
from_clause, where_clause, where_clause_params = query.get_sql()
# In case the applicant phone or mobile is configured in wrong field
query_str = """
SELECT hr_applicant.id as appl_id,
COUNT(other.id) as count
FROM hr_applicant
JOIN hr_applicant other ON LOWER(other.email_from) = LOWER(hr_applicant.email_from)
OR other.partner_phone = hr_applicant.partner_phone OR other.partner_phone = hr_applicant.partner_mobile
OR other.partner_mobile = hr_applicant.partner_mobile OR other.partner_mobile = hr_applicant.partner_phone
%(where)s
GROUP BY hr_applicant.id
""" % {
'where': ('WHERE %s' % where_clause) if where_clause else '',
}
self.env.cr.execute(query_str, where_clause_params)
application_data_mapped = dict(
(data['appl_id'], data['count'])
for data in self.env.cr.dictfetchall())
else:
application_data_mapped = dict()
for applicant in applicants:
applicant.application_count = application_data_mapped.get(
applicant.id, 1) - 1
(self - applicants).application_count = False
def _read_from_database(self, field_names, inherited_field_names=[]):
""" Read the given fields of the records in ``self`` from the database,
and store them in cache. Access errors are also stored in cache.
:param field_names: list of column names of model ``self``; all those
fields are guaranteed to be read
:param inherited_field_names: list of column names from parent
models; some of those fields may not be read
"""
if not self:
return
env = self.env
cr, user, context = env.args
# make a query object for selecting ids, and apply security rules to it
param_ids = object()
query = Query(['"%s"' % self._table],
['"%s".id IN %%s' % self._table], [param_ids])
self._apply_ir_rules(query, 'read')
# determine the fields that are stored as columns in tables; ignore 'id'
fields_pre = [
field
for field in (self._fields[name]
for name in field_names + inherited_field_names)
if field.name != 'id'
if field.base_field.store and field.base_field.column_type
if not (
field.inherited and callable(field.base_field.translate))
]
# the query may involve several tables: we need fully-qualified names
def qualify(field):
col = field.name
res = self._inherits_join_calc(self._table, field.name, query)
if field.type == 'binary' and (context.get('bin_size') or
context.get('bin_size_' + col)):
# PG 9.2 introduces conflicting pg_size_pretty(numeric) -> need ::cast
res = 'pg_size_pretty(length(%s)::bigint)' % res
return '%s as "%s"' % (res, col)
qual_names = [
qualify(name) for name in [self._fields['id']] + fields_pre
]
# determine the actual query to execute
from_clause, where_clause, params = query.get_sql()
query_str = "SELECT %s FROM %s WHERE %s" % (
",".join(qual_names), from_clause, where_clause)
result = []
param_pos = params.index(param_ids)
for sub_ids in cr.split_for_in_conditions(self.ids):
params[param_pos] = tuple(sub_ids)
cr.execute(query_str, params)
result.extend(cr.dictfetchall())
ids = [vals['id'] for vals in result]
fetched = self.browse(ids)
if ids:
# translate the fields if necessary
if context.get('lang'):
for field in fields_pre:
if not field.inherited and callable(field.translate):
name = field.name
translate = field.get_trans_func(fetched)
for vals in result:
vals[name] = translate(vals['id'], vals[name])
# store result in cache
for vals in result:
record = self.browse(vals.pop('id'), self._prefetch)
record._cache.update(
record._convert_to_cache(vals, validate=False))
# determine the fields that must be processed now;
# for the sake of simplicity, we ignore inherited fields
for name in field_names:
field = self._fields[name]
if not field.column_type:
field.read(fetched)
# Warn about deprecated fields now that fields_pre and fields_post are computed
for name in field_names:
field = self._fields[name]
if field.deprecated:
_logger.warning('Field %s is deprecated: %s', field,
field.deprecated)
# store failed values in cache for the records that could not be read
missing = self - fetched
if missing:
extras = fetched - self
if extras:
raise AccessError(
_("Database fetch misses ids ({}) and has extra ids ({}), may be caused by a type incoherence in a previous request"
).format(
missing._ids,
extras._ids,
))
# mark non-existing records in missing
forbidden = missing.exists()
if forbidden:
_logger.info(
_('The requested operation cannot be completed due to record rules: Document type: %s, Operation: %s, Records: %s, User: %s') % \
(self._name, 'read', ','.join([str(r.id) for r in self][:6]), self._uid))
# store an access error exception in existing records
exc = AccessError(
# _('The requested operation cannot be completed due to security restrictions. Please contact your system administrator.\n\n(Document type: %s, Operation: %s)') % \
# (self._name, 'read')
# Modificación del mensaje de error: José Candelas
_('The requested operation cannot be completed due to security restrictions. Please contact your system administrator.\n\n(Document type: %s, Operation: %s, Forbidden: %s)') % \
(self._name, 'read', forbidden)
)
self.env.cache.set_failed(forbidden, self._fields.values(),
exc)
