Some unchanged constraints dropped and created at each module update

[Guewen Baconnier @ Camptocamp]

Hello,

When a module is updated, a mecanism check if a constraint have been modified, in such case, it drop it and create it with the new definition.

It appears that for some constraints, it wrongly believes that the constraint has changed, so drop it and re-create it.

I take as example the constraints defined on account.move.line in account model :
    _sql_constraints = [
        ('credit_debit1', 'CHECK (credit*debit=0)', 'Wrong credit or debit value in accounting entry !'),
        ('credit_debit2', 'CHECK (credit+debit>=0)', 'Wrong credit or debit value in accounting entry !'),
    ]
On a database with a few hundred thousand move lines, the update of the account module can take tens of minutes just to create again the check constraints.

In the server openerp/osv/orm.py method

    def _add_sql_constraints(self, cr):
        [...]
        def unify_cons_text(txt):
            return txt.lower().replace(', ',',').replace(' (','(')
        [...]
            elif unify_cons_text(con) not in [unify_cons_text(item['condef']) for item in existing_constraints]:
        [...]

On the elif condition upper, for the constraint credit_debit1:
 - con is : 'CHECK (credit*debit=0)'
 - condef in existing_constraints is : 'CHECK ((credit * debit) = 0::numeric)'

You can see that there is an extra ::numeric in the last item, that's why it believe that it doesn't exist, but that's the same definition.

The unify_cons_text function should be more smart and remove casts like ::numeric, however, I'm not sure what kind of replacement we have to do.
Maybe a regexp like that : re.sub('::\w+', '', txt) ?

On the other hand, we could also do not change the server but modify the constraint definitions in addons by :
    _sql_constraints = [
        ('credit_debit1', 'CHECK ((credit * debit) = 0::numeric)', 'Wrong credit or debit value in accounting entry !'),
        ('credit_debit2', 'CHECK ((credit + debit) >= 0::numeric)', 'Wrong credit or debit value in accounting entry !'),
    ]

What's your thoughts ?

Guewen

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

We could write:

def unify_cons_text(txt):
    return re.sub('(::\w+)|\s', '', cons).lower()

But one thing annoys me, if a sql constraint defines explicitly a cast like ::numeric and is changed later, the constraint won't be re-created.

[Leonardo Pistone (lepistone)]

I move that to https://github.com/odoo/odoo/issues/2112. Thanks!