On Mon, Apr 30, 2018 at 2:15 PM, Erik Huelsmann <ehuels@gmail.com> wrote:

Hi,

After having merged the infrastructure to check data validity in an existing database before applying an upgrade script, I'm reviewing existing scripts to see if they may qualify to be enhanced with a check and user intervention.

One script which qualifies is 1.5/invoice-tbl-cogs-constraint.sql which adds a constraint verifying that the COGS routines post data which makes sense.

Existing data does not necessarily comply with the new constraint. I wonder though what corrective action to offer a user who is the proud owner of non-compliant data. I see a number of options:

1. Tell the user to contact the mailing list (and block his migration in the mean time)

That may be necessary if other things fail anyway.

2. Modify the data to be within the bounds to be enforced (and thereby loose the COGS audit trail?

Modifying data is of course dangerous. The question here is what we mean by modifying the data. Are we talking about deleting data? or some other modifications?

3. Allow constraint creation to fail by modifying Loadorder and create a new script which creates a "not valid" constraint (which allows the existing data to remain in place, but verifies new data)

You could also create a constraint that checks if data is old and passes it too.

4. Create a check script which creates the "not valid" constraint and fakes script application by modifying dbchanges tables

I don't see what this gets us.

5. Adjust the data back into bounds, allocating the overage to more recent invoices and creating a COGS adjustment journal which the admin must post

This would get my vote.

Which should we go with?

--
Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.

Best Wishes,

Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.

http://www.efficito.com/learn_more