|
Notes |
|
|
|
This commit added a new unique constraint, which will create problems on upgrades if the existing data is not valid with the new constraint.
changeset: 4878:e81b96696403
user: Víctor Martínez Romanos <victor.martinez@openbravo.com>
date: Tue Sep 08 13:01:29 2009 +0200
files: src-db/database/model/tables/C_PERIOD.xml src-db/database/sourcedata/AD_MESSAGE.xml
description:
Fixed bug 10472: Do not allow to create two periods in the same dates |
|
|
|
|
We have added a new database unique constraint in the C_Period table to avoid the creation of duplicated accounting periods (same start and end date) for a client (see 0010472).
This is can be a dangerous change for upgrades just in case the user has duplicate periods for the same client. In this case the user should manually remove the duplicate period before updating the system. |
|
|
|
|
I am afraid that we cannot afford to make this change in 2.50 and we need to postpone it to a later release (i.e wait till we have a maintenance branch for 2.50 and pi/main are for the next release).
For 2.50, I suggest to avoid adding an index and to enforce the constraints at application level (when the user saves a period, check if it is duplicate).
This avoids failure risks with the MPs. |
|
|
|
|
Paolo,
the problem is that we cannot use a trigger for controlling it, because we need to query the same table of the trigger, so a mutating table error will appear.
However, a possibility we have is to use a "table level trigger". This kind of trigger doesn't have the mutating table problem, and it should work fine except for one case: if the database has a duplicate period before. In this case the trigger will always detect an error when the user creates new periods.
What do you think? |
|
|
|
|
Do you need to use triggers in the first place? Can you do it with a call out instead? For example, when the user enters the start and end date of a period, you can invoke the call out. Also the Create Period process could also check if it is about to create duplicate periods.
This would work unless you can create duplicate periods without going through the UI. |
|
|
|
|
From my point of view this rule must be implemented using a constraint and, if not possible, using a trigger.
A callout is not designed for controlling this kind of rules, instead of that a callout is perfect for showing warning/info messages or update fields, but with a callout you cannot avoid the user to introduce a duplicate period. This kind of rules must be implemented at database level.
Finally, we can implement this check inside the Create Period process, but this won't work when the user manually creates periods.
So, as a summary:
- if you want to properly control no duplicate periods are in the database, a check at database level must be implemented (constraint or trigger)
- if you just want to avoid the user to duplicate periods using the Create Period process, we must modified it
- if you just want to display a warning when the user introduces a duplicate period, a callout should be implemented, but take into account that this won't avoid the user to create the duplicated period |
|
|
|
(0019915)
|
|
pjuvara
|
2009-09-14 17:27
(edited on: 2009-09-14 17:28) |
|
At the end we decided to go for the unique constraint and accept this violation as an exception.
The rationale is:
1) It is the best solution from a functional and technical stand point
2) The update is not impacted unless you already have a data corruption (duplicate periods) that will cause other problems unless resolved
3) If there is an error, the update process is not stopped. Instead, an error is raised and displayed in the log and the new constraint is left inactive.
Next steps:
1) Send the new violation alert
2) Be prepared to document this as a known issue for MP6 including instructions on how to remove the duplicate period and re-enable the constraint.
|
|
|
|
|
A new alert module (http://forge.openbravo.com/projects/duplicatedperiodalert [^]) has been created to help the user to determinate whether he has duplicated periods for the same client or not. The module is available through the Central Repository.
This module can be installed before upgrading the application and it will show the duplicated periods of the client (if any). This way the user can delete them before upgrading the system.
Moreover, the module can also be installed after upgrading the application if the user has detected an error about the C_PERIOD_UNIQUE_PERIOD constraint in the C_PERIOD table in the upgrade process. |
|
|
|
(0019952)
|
|
hgbot
|
|
2009-09-14 20:07
|
|
|