0010545: API check build 135 fails - Openbravo Issue Tracking System

Notes
(0019723) shuehner (administrator) 2009-09-10 14:02	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

(0019760) vmromanos (manager) 2009-09-10 17:59	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.

(0019803) pjuvara (reporter) 2009-09-11 13:07	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.

(0019807) vmromanos (manager) 2009-09-11 13:44	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?

(0019813) pjuvara (reporter) 2009-09-11 16:57	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.

(0019879) vmromanos (manager) 2009-09-14 10:29	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 (reporter) 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.

(0019951) vmromanos (manager) 2009-09-14 20:06	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 (developer) 2009-09-14 20:07	Repository: erp/devel/api-checks Changeset: 3f16f525e54ff3218745bb9be86eb1fd85fc2703 Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> Date: Mon Sep 14 17:42:50 2009 +0200 URL: http://code.openbravo.com/erp/devel/api-checks/rev/3f16f525e54ff3218745bb9be86eb1fd85fc2703 [^] Fixed bug 10545: Added C_PERIOD_UNIQUE_PERIOD constraint to C_PERIOD table --- M model/model/tables/C_PERIOD.xml ---

Issue History
Date Modified	Username	Field	Change
2009-09-10 14:01	shuehner	New Issue
2009-09-10 14:01	shuehner	Assigned To	=> vmromanos
2009-09-10 14:02	shuehner	Note Added: 0019723
2009-09-10 14:02	shuehner	Relationship added	related to 0010472
2009-09-10 17:59	vmromanos	Note Added: 0019760
2009-09-10 17:59	vmromanos	Assigned To	vmromanos => pjuvara
2009-09-11 13:07	pjuvara	Note Added: 0019803
2009-09-11 13:07	pjuvara	Status	new => scheduled
2009-09-11 13:07	pjuvara	Assigned To	pjuvara => vmromanos
2009-09-11 13:07	pjuvara	fix_in_branch	=> pi
2009-09-11 13:44	vmromanos	Note Added: 0019807
2009-09-11 13:45	vmromanos	Assigned To	vmromanos => pjuvara
2009-09-11 16:57	pjuvara	Note Added: 0019813
2009-09-11 16:57	pjuvara	Assigned To	pjuvara => vmromanos
2009-09-14 10:29	vmromanos	Note Added: 0019879
2009-09-14 10:29	vmromanos	Assigned To	vmromanos => pjuvara
2009-09-14 17:27	pjuvara	Note Added: 0019915
2009-09-14 17:27	pjuvara	Assigned To	pjuvara => vmromanos
2009-09-14 17:28	pjuvara	Note Edited: 0019915
2009-09-14 20:06	vmromanos	Note Added: 0019951
2009-09-14 20:07	hgbot	Checkin
2009-09-14 20:07	hgbot	Note Added: 0019952
2009-09-14 20:07	hgbot	Status	scheduled => resolved
2009-09-14 20:07	hgbot	Resolution	open => fixed
2009-09-14 20:07	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/api-checks/rev/3f16f525e54ff3218745bb9be86eb1fd85fc2703 [^]
2009-09-17 19:28	psarobe	Status	resolved => closed
2009-09-17 19:28	psarobe	Fixed in Version	=> 2.50MP6
2009-09-18 00:00	anonymous	sf_bug_id	0 => 2861009