[FIX] base: do not drop sql constraints during update

When a module is updated, the sql constraints were checked against postgres pg_get_constraintdef() to see if they have diverged from the one defined in python code.
However this check was failing as the syntax returned by pg_get_constraintdef() (e.g. "CHECK ((credit * debit) = 0::numeric)") differs from the one returned by `unify_cons_text` method (e.g. "check(debit*credit = 0")) so the constraints were always dropped and recomputed.
This adds a definition column on ir.model.constraint (storing original defintion) to detect changes.
The pg_constraint is still inspected to make sure we detect constraint without ir_model_constraint but with postgres constraint defined (e.g. name_uniq defined in base.sql).

This changes also the date_update field when the definition changes.

diff --git a/openerp/addons/base/base.sql b/openerp/addons/base/base.sql
index 9b42bac..2547a73 100644 (file)
--- a/openerp/addons/base/base.sql
+++ b/openerp/addons/base/base.sql
@@ -140,6 +140,7 @@ CREATE TABLE ir_model_constraint (
     module integer NOT NULL references ir_module_module on delete restrict,
     model integer NOT NULL references ir_model on delete restrict,
     type character varying(1) NOT NULL,
+    definition varchar,
     name varchar NOT NULL,
     primary key(id)
 );

diff --git a/openerp/addons/base/ir/ir_model.py b/openerp/addons/base/ir/ir_model.py
index e15c6f3..fd0dd03 100644 (file)
--- a/openerp/addons/base/ir/ir_model.py
+++ b/openerp/addons/base/ir/ir_model.py
@@ -511,6 +511,7 @@ class ir_model_constraint(Model):
     _columns = {
         'name': fields.char('Constraint', required=True, select=1,
             help="PostgreSQL constraint or foreign key name."),
+        'definition': fields.char('Definition', help="PostgreSQL constraint definition"),
         'model': fields.many2one('ir.model', string='Model',
             required=True, select=1),
         'module': fields.many2one('ir.module.module', string='Module',

diff --git a/openerp/models.py b/openerp/models.py
index 7d36cf0..c441f8f 100644 (file)
--- a/openerp/models.py
+++ b/openerp/models.py
@@ -2305,7 +2305,7 @@ class BaseModel(object):
                 _schema.debug("Table '%s': column '%s': dropped NOT NULL constraint",
                               self._table, column['attname'])
 
-    def _save_constraint(self, cr, constraint_name, type):
+    def _save_constraint(self, cr, constraint_name, type, definition):
         """
         Record the creation of a constraint for this model, to make it possible
         to delete it later when the module is uninstalled. Type can be either
@@ -2317,19 +2317,26 @@ class BaseModel(object):
             return
         assert type in ('f', 'u')
         cr.execute("""
-            SELECT 1 FROM ir_model_constraint, ir_module_module
+            SELECT type, definition FROM ir_model_constraint, ir_module_module
             WHERE ir_model_constraint.module=ir_module_module.id
                 AND ir_model_constraint.name=%s
                 AND ir_module_module.name=%s
             """, (constraint_name, self._module))
-        if not cr.rowcount:
+        constraints = cr.dictfetchone()
+        if not constraints:
             cr.execute("""
                 INSERT INTO ir_model_constraint
-                    (name, date_init, date_update, module, model, type)
+                    (name, date_init, date_update, module, model, type, definition)
                 VALUES (%s, now() AT TIME ZONE 'UTC', now() AT TIME ZONE 'UTC',
                     (SELECT id FROM ir_module_module WHERE name=%s),
-                    (SELECT id FROM ir_model WHERE model=%s), %s)""",
-                    (constraint_name, self._module, self._name, type))
+                    (SELECT id FROM ir_model WHERE model=%s), %s, %s)""",
+                    (constraint_name, self._module, self._name, type, definition))
+        elif constraints['type'] != type or (definition and constraints['definition'] != definition):
+            cr.execute("""
+                UPDATE ir_model_constraint
+                SET date_update=now() AT TIME ZONE 'UTC', type=%s, definition=%s
+                WHERE name=%s AND module = (SELECT id FROM ir_module_module WHERE name=%s)""",
+                    (type, definition, constraint_name, self._module))
 
     def _save_relation_table(self, cr, relation_table):
         """
@@ -2732,7 +2739,7 @@ class BaseModel(object):
         """ Create the foreign keys recorded by _auto_init. """
         for t, k, r, d in self._foreign_keys:
             cr.execute('ALTER TABLE "%s" ADD FOREIGN KEY ("%s") REFERENCES "%s" ON DELETE %s' % (t, k, r, d))
-            self._save_constraint(cr, "%s_%s_fkey" % (t, k), 'f')
+            self._save_constraint(cr, "%s_%s_fkey" % (t, k), 'f', False)
         cr.commit()
         del self._foreign_keys
 
@@ -2845,9 +2852,14 @@ class BaseModel(object):
         for (key, con, _) in self._sql_constraints:
             conname = '%s_%s' % (self._table, key)
 
-            self._save_constraint(cr, conname, 'u')
-            cr.execute("SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef FROM pg_constraint where conname=%s", (conname,))
-            existing_constraints = cr.dictfetchall()
+            # using 1 to get result if no imc but one pgc
+            cr.execute("""SELECT definition, 1
+                          FROM ir_model_constraint imc
+                          RIGHT JOIN pg_constraint pgc
+                          ON (pgc.conname = imc.name)
+                          WHERE pgc.conname=%s
+                          """, (conname, ))
+            existing_constraints = cr.dictfetchone()
             sql_actions = {
                 'drop': {
                     'execute': False,
@@ -2871,14 +2883,15 @@ class BaseModel(object):
                 # constraint does not exists:
                 sql_actions['add']['execute'] = True
                 sql_actions['add']['msg_err'] = sql_actions['add']['msg_err'] % (sql_actions['add']['query'], )
-            elif unify_cons_text(con) not in [unify_cons_text(item['condef']) for item in existing_constraints]:
+            elif unify_cons_text(con) != existing_constraints['definition']:
                 # constraint exists but its definition has changed:
                 sql_actions['drop']['execute'] = True
-                sql_actions['drop']['msg_ok'] = sql_actions['drop']['msg_ok'] % (existing_constraints[0]['condef'].lower(), )
+                sql_actions['drop']['msg_ok'] = sql_actions['drop']['msg_ok'] % (existing_constraints['definition'] or '', )
                 sql_actions['add']['execute'] = True
                 sql_actions['add']['msg_err'] = sql_actions['add']['msg_err'] % (sql_actions['add']['query'], )
 
             # we need to add the constraint:
+            self._save_constraint(cr, conname, 'u', unify_cons_text(con))
             sql_actions = [item for item in sql_actions.values()]
             sql_actions.sort(key=lambda x: x['order'])
             for sql_action in [action for action in sql_actions if action['execute']]: