[IMP] fields, test_new_api: add ondelete support for m2m

Each model of the relation can define `ondelete` for its `column2` foreign key.

This way the behavior is similar to m2o where the target of the `ondelete` is
the `comodel` foreign key.

Some models were already defining `ondelete` 'restrict' on m2m fields and after
review it appears the behavior introduced in this commit is the expected
behavior for them.

The 'restrict' from product.product has to be removed however because the rest
of the code needs adaptation now that it is properly restricted. These changes
will be done on a separate PR.

closes odoo/odoo#35654

Signed-off-by: Xavier Morel (xmo) <xmo@odoo.com>

4 files changed, 72 insertions, 3 deletions

diff --git a/odoo/addons/test_new_api/ir.model.access.csv b/odoo/addons/test_new_api/ir.model.access.csv
index 5e9aceeab50..66d10310f43 100644
--- a/odoo/addons/test_new_api/ir.model.access.csv
+++ b/odoo/addons/test_new_api/ir.model.access.csv
@@ -34,3 +34,5 @@ access_test_new_api_selection,access_test_new_api_selection,model_test_new_api_s
 access_test_new_api_attachment,access_test_new_api_attachment,model_test_new_api_attachment,,1,1,1,1
 access_test_new_api_attachment_host,access_test_new_api_attachment_host,model_test_new_api_attachment_host,,1,1,1,1
 access_test_new_api_model_image,access_test_new_api_model_image,model_test_new_api_model_image,,1,1,1,1
+access_test_new_api_model_a,access_test_new_api_model_a,model_test_new_api_model_a,,1,1,1,1
+access_test_new_api_model_b,access_test_new_api_model_b,model_test_new_api_model_b,,1,1,1,1
diff --git a/odoo/addons/test_new_api/models.py b/odoo/addons/test_new_api/models.py
index 9895fbd62aa..f5057eebc49 100644
--- a/odoo/addons/test_new_api/models.py
+++ b/odoo/addons/test_new_api/models.py
@@ -612,3 +612,21 @@ class DecimalPrecisionTestModel(models.Model):
     float = fields.Float()
     float_2 = fields.Float(digits=(16, 2))
     float_4 = fields.Float(digits=(16, 4))
+
+
+class ModelA(models.Model):
+    _name = 'test_new_api.model_a'
+    _description = 'Model A'
+
+    name = fields.Char()
+    a_restricted_b_ids = fields.Many2many('test_new_api.model_b', relation='rel_model_a_model_b_1')
+    b_restricted_b_ids = fields.Many2many('test_new_api.model_b', relation='rel_model_a_model_b_2', ondelete='restrict')
+
+
+class ModelB(models.Model):
+    _name = 'test_new_api.model_b'
+    _description = 'Model B'
+
+    name = fields.Char()
+    a_restricted_a_ids = fields.Many2many('test_new_api.model_a', relation='rel_model_a_model_b_1', ondelete='restrict')
+    b_restricted_a_ids = fields.Many2many('test_new_api.model_a', relation='rel_model_a_model_b_2')
diff --git a/odoo/addons/test_new_api/tests/test_new_fields.py b/odoo/addons/test_new_api/tests/test_new_fields.py
index af34c5b6027..9b17933397a 100644
--- a/odoo/addons/test_new_api/tests/test_new_fields.py
+++ b/odoo/addons/test_new_api/tests/test_new_fields.py
@@ -5,6 +5,7 @@ import base64
 from datetime import date, datetime, time
 import io
 from PIL import Image
+import psycopg2
 
 from odoo import fields
 from odoo.exceptions import AccessError, UserError
@@ -1496,6 +1497,34 @@ class TestX2many(common.TransactionCase):
         self.assertEqual(field.column1, 'test_new_api_multi_line2_id')
         self.assertEqual(field.column2, 'test_new_api_multi_tag_id')
 
+    def test_10_ondelete_many2many(self):
+        """Test A can't be deleted when used on the relation."""
+        record_a = self.env['test_new_api.model_a'].create({'name': 'a'})
+        record_b = self.env['test_new_api.model_b'].create({'name': 'b'})
+        record_a.write({
+            'a_restricted_b_ids': [(6, 0, record_b.ids)],
+        })
+        with self.assertRaises(psycopg2.IntegrityError):
+            with mute_logger('odoo.sql_db'), self.cr.savepoint():
+                record_a.unlink()
+        # Test B is still cascade.
+        record_b.unlink()
+        self.assertFalse(record_b.exists())
+
+    def test_11_ondelete_many2many(self):
+        """Test B can't be deleted when used on the relation."""
+        record_a = self.env['test_new_api.model_a'].create({'name': 'a'})
+        record_b = self.env['test_new_api.model_b'].create({'name': 'b'})
+        record_a.write({
+            'b_restricted_b_ids': [(6, 0, record_b.ids)],
+        })
+        with self.assertRaises(psycopg2.IntegrityError):
+            with mute_logger('odoo.sql_db'), self.cr.savepoint():
+                record_b.unlink()
+        # Test A is still cascade.
+        record_a.unlink()
+        self.assertFalse(record_a.exists())
+
     def test_search_many2many(self):
         """ Tests search on many2many fields. """
         tags = self.env['test_new_api.multi.tag']
diff --git a/odoo/fields.py b/odoo/fields.py
index e8c1d4bbb84..3c24223d083 100644
--- a/odoo/fields.py
+++ b/odoo/fields.py
@@ -3003,6 +3003,7 @@ class Many2many(_RelationalMulti):
         'column2': None,                # column of table referring to comodel
         'auto_join': False,             # whether joins are generated upon search
         'limit': None,                  # optional limit to use upon read
+        'ondelete': None,               # optional ondelete for the column2 fkey
     }
 
     def __init__(self, comodel_name=Default, relation=Default, column1=Default,
@@ -3018,6 +3019,18 @@ class Many2many(_RelationalMulti):
 
     def _setup_regular_base(self, model):
         super(Many2many, self)._setup_regular_base(model)
+        # 3 cases:
+        # 1) The ondelete attribute is not defined, we assign it a sensible default
+        # 2) The ondelete attribute is defined and its definition makes sense
+        # 3) The ondelete attribute is explicitly defined as 'set null' for a m2m,
+        #    this is considered a programming error.
+        self.ondelete = self.ondelete or 'cascade'
+        if self.ondelete == 'set null':
+            raise ValueError(
+                "The m2m field %s of model %s declares its ondelete policy "
+                "as being 'set null'. Only 'restrict' and 'cascade' make sense."
+                % (self.name, model._name)
+            )
         if self.store:
             if not (self.relation and self.column1 and self.column2):
                 self._explicit = False
@@ -3073,8 +3086,8 @@ class Many2many(_RelationalMulti):
         if not self.manual:
             model.pool.post_init(model.env['ir.model.relation']._reflect_relation,
                                  model, self.relation, self._module)
+        comodel = model.env[self.comodel_name]
         if not sql.table_exists(cr, self.relation):
-            comodel = model.env[self.comodel_name]
             query = """
                 CREATE TABLE "{rel}" ("{id1}" INTEGER NOT NULL,
                                       "{id2}" INTEGER NOT NULL,
@@ -3087,18 +3100,25 @@ class Many2many(_RelationalMulti):
             _schema.debug("Create table %r: m2m relation between %r and %r", self.relation, model._table, comodel._table)
             model.pool.post_init(self.update_db_foreign_keys, model)
             return True
+        elif sql.table_kind(cr, comodel._table) != 'v' and self.ondelete != 'cascade':
+            # Fix foreign key references with ondelete, unless the targets are
+            # SQL views.
+            # This is needed because by default the ondelete of the column1
+            # fkey is set to 'cascade', but the relation on the opposite model
+            # can override it by defining ondelete for its column2 fkey.
+            sql.fix_foreign_key(cr, self.relation, self.column2, comodel._table, 'id', self.ondelete)
 
     def update_db_foreign_keys(self, model):
         """ Add the foreign keys corresponding to the field's relation table. """
         cr = model._cr
         comodel = model.env[self.comodel_name]
         reflect = model.env['ir.model.constraint']._reflect_constraint
-        # create foreign key references with ondelete=cascade, unless the targets are SQL views
+        # create foreign key references with ondelete, unless the targets are SQL views
         if sql.table_kind(cr, model._table) != 'v':
             sql.add_foreign_key(cr, self.relation, self.column1, model._table, 'id', 'cascade')
             reflect(model, '%s_%s_fkey' % (self.relation, self.column1), 'f', None, self._module)
         if sql.table_kind(cr, comodel._table) != 'v':
-            sql.add_foreign_key(cr, self.relation, self.column2, comodel._table, 'id', 'cascade')
+            sql.add_foreign_key(cr, self.relation, self.column2, comodel._table, 'id', self.ondelete)
             reflect(model, '%s_%s_fkey' % (self.relation, self.column2), 'f', None, self._module)
 
     def read(self, records):