[REF] core: improve the API and implementation of Query

Introduce better methods for introducing tables, join clauses and where
clauses to the Query object, and other methods to generate a complete
SELECT query.  The Query object can also behave as a tuple of ids, where
the query is made on demand, and the result is memoized.

2 files changed, 97 insertions, 31 deletions

diff --git a/odoo/addons/base/tests/test_expression.py b/odoo/addons/base/tests/test_expression.py
index 88f86fbcb38..4e1c6197b10 100644
--- a/odoo/addons/base/tests/test_expression.py
+++ b/odoo/addons/base/tests/test_expression.py
@@ -1014,6 +1014,37 @@ class TestQueries(TransactionCase):
         ''']):
             Model.search(domain)
 
+    def test_order(self):
+        Model = self.env['res.partner']
+        Model.search([('name', 'like', 'foo')])
+
+        with self.assertQueries(['''
+            SELECT "res_partner".id
+            FROM "res_partner"
+            WHERE (("res_partner"."active" = %s) AND ("res_partner"."name"::text LIKE %s))
+            ORDER BY "res_partner"."display_name"
+        ''']):
+            Model.search([('name', 'like', 'foo')])
+
+        with self.assertQueries(['''
+            SELECT "res_partner".id
+            FROM "res_partner"
+            WHERE (("res_partner"."active" = %s) AND ("res_partner"."name"::text LIKE %s))
+            ORDER BY "res_partner"."id"
+        ''']):
+            Model.search([('name', 'like', 'foo')], order='id')
+
+    def test_count(self):
+        Model = self.env['res.partner']
+        Model.search([('name', 'like', 'foo')])
+
+        with self.assertQueries(['''
+            SELECT count(1)
+            FROM "res_partner"
+            WHERE (("res_partner"."active" = %s) AND ("res_partner"."name"::text LIKE %s))
+        ''']):
+            Model.search_count([('name', 'like', 'foo')])
+
     def test_translated_field(self):
         self.env['res.lang']._activate_lang('fr_FR')
         Model = self.env['res.partner.title'].with_context(lang='fr_FR')
@@ -1057,10 +1088,11 @@ class TestQueries(TransactionCase):
 
         with self.assertQueries(['''
             SELECT "res_users".id
-            FROM "res_users", "res_partner" AS "res_users__partner_id"
+            FROM "res_users"
+            LEFT JOIN "res_partner" AS "res_users__partner_id" ON
+                ("res_users"."partner_id" = "res_users__partner_id"."id")
             WHERE ("res_users"."active" = %s)
             AND ("res_users"."id" = %s)
-            AND ("res_users"."partner_id" = "res_users__partner_id"."id")
             AND ("res_users__partner_id"."id" = %s)
             ORDER BY "res_users__partner_id"."name", "res_users"."login"
         ''']):
diff --git a/odoo/addons/base/tests/test_osv.py b/odoo/addons/base/tests/test_osv.py
index ffa0de33174..6dc11d8fd98 100644
--- a/odoo/addons/base/tests/test_osv.py
+++ b/odoo/addons/base/tests/test_osv.py
@@ -8,41 +8,75 @@ from odoo.tests.common import BaseCase
 class QueryTestCase(BaseCase):
 
     def test_basic_query(self):
-        query = Query()
-        query.add_table('product_product')
+        query = Query(None, '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_product", "res_user", "user_id", "id", "user_id"), implicit=False, outer=True)  # outer join
-        self.assertEqual(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.assertEqual(query.get_sql()[1].strip(), """product_product.template_id = product_template.id""".strip())
+        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 test_query_chained_explicit_joins(self):
-        query = Query()
-        query.add_table('product_product')
+        query = Query(None, '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())
+        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')
+
+        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_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")
 
     def test_mixed_query_chained_explicit_implicit_joins(self):
-        query = Query()
-        query.add_table('product_product')
+        query = Query(None, '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
-        query.add_table('account_account')
-        query.where_clause.append("product_category.expense_account_id = account_account.id")  # additional implicit 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"),"account_account" """.strip())
-        self.assertEqual(query.get_sql()[1].strip(), """product_product.template_id = product_template.id AND product_category.expense_account_id = account_account.id""".strip())
+        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 test_raise_missing_lhs(self):
-        query = Query()
-        query.add_table('product_product')
-        self.assertRaises(AssertionError, query.add_join, ("product_template", "product_category", "categ_id", "id", "categ_id"), implicit=False, outer=False)
+        query = Query(None, 'product_product')
+        with self.assertRaises(AssertionError):
+            query.join("product_template", "categ_id", "product_category", "id", "categ_id")
+
+    def test_long_aliases(self):
+        query = Query(None, 'product_product')
+        tmp = query.join('product_product', 'product_tmpl_id', 'product_template', 'id', 'product_tmpl_id')
+        self.assertEqual(tmp, 'product_product__product_tmpl_id')
+        # no hashing
+        tmp_cat = query.join(tmp, 'product_category_id', 'product_category', 'id', 'product_category_id')
+        self.assertEqual(tmp_cat, 'product_product__product_tmpl_id__product_category_id')
+        # hashing to limit identifier length
+        tmp_cat_cmp = query.join(tmp_cat, 'company_id', 'res_company', 'id', 'company_id')
+        self.assertEqual(tmp_cat_cmp, 'product_product__product_tmpl_id__product_category_id__9f0ddff7')
+        tmp_cat_stm = query.join(tmp_cat, 'salesteam_id', 'res_company', 'id', 'salesteam_id')
+        self.assertEqual(tmp_cat_stm, 'product_product__product_tmpl_id__product_category_id__953a466f')
+        # extend hashed identifiers
+        tmp_cat_cmp_par = query.join(tmp_cat_cmp, 'partner_id', 'res_partner', 'id', 'partner_id')
+        self.assertEqual(tmp_cat_cmp_par, 'product_product__product_tmpl_id__product_category_id__56d55687')
+        tmp_cat_stm_par = query.join(tmp_cat_stm, 'partner_id', 'res_partner', 'id', 'partner_id')
+        self.assertEqual(tmp_cat_stm_par, 'product_product__product_tmpl_id__product_category_id__9_363fdd')