Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0029495Retail ModulesMulti UPC/EAN for Productpublic2015-04-01 16:452015-05-13 16:27
ReporterVictorVillar 
Assigned ToOrekaria 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR15Q3 
Merge Request Status
Review Assigned Tomalsasua
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0029495: It is possible to add the same UPC for different products
DescriptionIt is possible to add the same UPC for different products
Steps To ReproduceAs group admin role,
 go to Product window,
   Create a new product and add the UPC: 123
Go to Multi UPC tab.
   create a new UPC: 123

It should not be possible to do that.

   Create a new product and add the UPC: 123 (core problem, it should not be possible)
   Go to Multi UPC tab.
   create a new UPC: 123

It should not be possible to do that.

Proposed SolutionHow to test this issue:

This issue has 2 topics:
A) the build validation
B) the constraints to prevent duplicated UPCs while in production
C) performance tests


Suggestions to verify topic A:

1)
revert MultiUPC module to version 1.1.1
apply the openbravo patch
apply the sampledata patch a
apply the sampledata patch b
ant install.source

2) update MultiUPC to tip
ant update.database
    expected output:

     [java] 280 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 283 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     [java] 288 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - The build validation failed.
     [java] 289 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - Found 3 duplicated UPCs in the m_products table. The MultiUPC module does not allow duplicated UPC codes within the Products of the same Client. Please fix it before continuing. This query may be handy to find out which UPCs are duplicated: 'SELECT upc FROM m_product WHERE upc IS NOT NULL GROUP BY upc, ad_client_id HAVING COUNT(*) > 1'
     [java] 3344234 (2 found)
     [java] 3344111 (2 found)
     [java] 3344567 (2 found)
     [java]
     [java]

     BUILD FAILED


3) remove the openbravo patch
revert back the MultiUPC module to version 1.1.1
ant install.source
ant update.database
    expected output:

     [java] 285 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 287 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     [java] 294 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - The build validation failed.
     [java] 295 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - Found 1 duplicated UPCs in the obmupc_prod_multiupc table. The MultiUPC module does not allow duplicated UPC codes within the Products of the same Client. Please fix it before continuing. This query may be handy to find out which UPCs are duplicated: 'SELECT upc FROM obmupc_prod_multiupc WHERE upc IS NOT NULL GROUP BY upc, ad_client_id HAVING COUNT(*) > 1'
     [java] x10 (2 found)
     [java]
     [java]

     BUILD FAILED

4) remove the sampledata patch a
apply the sampledata patch b
revert back the MultiUPC module to version 1.1.1
ant install.source
ant update.database
    expected output:
     [java] 281 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 283 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     [java] 290 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - The build validation failed.
     [java] 291 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - Found 1 duplicated UPCs when joining the obmupc_prod_multiupc and the m_product tables. The MultiUPC module does not allow duplicated UPC codes within the Products of the same Client. Please fix it before continuing. This query may be handy to find out which UPCs are duplicated: 'SELECT m_product.upc FROM m_product JOIN obmupc_prod_multiupc ON m_product.upc = obmupc_prod_multiupc.upc'
     [java] 3344567 (1 found)
     [java]
     [java]

     BUILD FAILED

5) remove all attached patches
ant install.source
ant update.database
verify that the build is SUCCESSFUL

7) execute ant update.database again
verify that the UPC verification is skipped:
     [java] 296 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 298 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Skipping verification that UPCs are unique. It can be activated setting the 'Look for duplicated UPCs when building' preference to 'Y'
     ...

6) login in the ERP as system administrator
verify that a new preference has been introduced, called 'Look for duplicated UPCs when building' with value 'Y'

7) modify the value of the 'Look for duplicated UPCs when building' preference to 'N'
ant update.database
verify that the build validation is being executed again
     [java] 282 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 284 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     ...


Suggestions to verify topic B:

1) verify that, given an existing UPC code of a Product, it can not be added in the MultiUPC tab of that or other product
2) verify that, given an existing UPC in the MultiUPC tab, it can not be added to any other product in the MultiUPC window (it can still be added to an existing product, but that is not what this issue is trying to fix)


Suggestions to verify topic C:

1) verify the time spent by the trigger:
in a postgresql console, e.g:
  psql -U tad -d obtip
activate the timing
  \timing
insert an existing value in the MultiUPC table, e.g:
  INSERT INTO obmupc_prod_multiupc (obmupc_prod_multiupc_id, ad_client_id, ad_org_id, isactive, createdby, updatedby, upc, m_product_id) VALUES ('F9F477F2752D40219D17E85EC450EC01', '4028E6C72959682B01295A070852010D', 0, 'Y', 100, 100, 'x10', 'A9AED2CBF1E64493A65EB07128557934');
try to insert it again:
  time spent can be seen in the console

2) add 10 million product to the database
replace the About.java file in /openbravo/src/org/openbravo/erpCommon/ad_forms/About.java with the attached About.java file
start Tomcat
log into the ERP
in the help menu, click About (depending on your computer, a short or a long wait will happen)
open pgAdmin
select the context database
stop all triggers in the m_product table:
    alter table m_product disable trigger all
execute:
    copy m_product from '/tmp/copyProducts.sql' CSV
    this process can take several minutes, be patient, take a long break
    10 million products will have been added to the database
activate all triggers again
  alter table m_product enable trigger all

3) verify again the time spent by the trigger that prevent UPCs in the Product to be duplicated in the MultiUPC table:
in a postgresql console, e.g:
  psql -U tad -d obtip
activate the timing
  \timing
try to insert a UPC that is already in the m_product table, e.g:
  obtip=# INSERT INTO obmupc_prod_multiupc (obmupc_prod_multiupc_id, ad_client_id, ad_org_id, isactive, createdby, updatedby, upc, m_product_id) VALUES ('F9F577F2752D40219D17E85EC450EC01', '4028E6C72959682B01295A070852010D', 0, 'Y', 100, 100, '3344567', 'A9AED2CBF1E64493A65EB07128557934');
expected output:
  ERROR: @OBMUPC_DUPLICATED_UPC@
  Time: 0.xxx ms (ACI)
Additional Information
TagsNo tags attached.
Relationships
related to defect 0029400 closed vmromanos Openbravo ERP It is possible to add the same UPC for different products 
blocks feature request 0029465RR16Q1 closed Retail Retail Modules QA: RR15Q3: Add tests for Mobile applications 
blocks feature request 0029573 closed migueldejuana Retail Modules If the build fails because duplicated UPC have been found, report those UPCs 
Attached Filespatch openbravo.patch (1,686) 2015-04-17 16:14
https://issues.openbravo.com/file_download.php?file_id=8000&type=bug
patch sampledata a.patch (2,433) 2015-04-17 16:14
https://issues.openbravo.com/file_download.php?file_id=8001&type=bug
patch sampledata b.patch (1,318) 2015-04-17 16:14
https://issues.openbravo.com/file_download.php?file_id=8002&type=bug
? About.java (8,356) 2015-04-17 16:19
https://issues.openbravo.com/file_download.php?file_id=8003&type=bug
? products10k.sql (3,660,000) 2015-05-12 17:46
https://issues.openbravo.com/file_download.php?file_id=8077&type=bug
Issue History
Date ModifiedUsernameFieldChange
2015-04-01 16:45VictorVillarNew Issue
2015-04-01 16:45VictorVillarAssigned To => Orekaria
2015-04-01 16:45VictorVillarResolution time => 1429826400
2015-04-01 16:45VictorVillarTriggers an Emergency Pack => No
2015-04-01 16:46VictorVillarIssue Monitored: networkb
2015-04-01 16:46VictorVillarRelationship addedrelated to 0029400
2015-04-07 09:55OrekariaRelationship addedblocks 0029465
2015-04-07 17:16hgbotCheckin
2015-04-07 17:16hgbotNote Added: 0076373
2015-04-07 17:16hgbotStatusnew => resolved
2015-04-07 17:16hgbotResolutionopen => fixed
2015-04-07 17:16hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/18a6d71104c23f976047aae2076291666cc5ca83 [^]
2015-04-10 10:54hgbotCheckin
2015-04-10 10:54hgbotNote Added: 0076438
2015-04-10 10:54hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/18a6d71104c23f976047aae2076291666cc5ca83 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/558abd4a94c652ec23bb7d3ce2aba94b3018f2be [^]
2015-04-13 15:35OrekariaRelationship addedblocks 0029573
2015-04-13 16:12hgbotCheckin
2015-04-13 16:12hgbotNote Added: 0076482
2015-04-15 15:45hgbotCheckin
2015-04-15 15:45hgbotNote Added: 0076553
2015-04-15 15:45hgbotCheckin
2015-04-15 15:45hgbotNote Added: 0076554
2015-04-15 15:45hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/558abd4a94c652ec23bb7d3ce2aba94b3018f2be [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/50d4bd781645f827cca05b81c3de7a2fc5de4c9d [^]
2015-04-17 16:12OrekariaNote Added: 0076613
2015-04-17 16:12OrekariaNote Edited: 0076613bug_revision_view_page.php?bugnote_id=0076613#r8299
2015-04-17 16:14OrekariaFile Added: openbravo.patch
2015-04-17 16:14OrekariaFile Added: sampledata a.patch
2015-04-17 16:14OrekariaFile Added: sampledata b.patch
2015-04-17 16:16OrekariaNote Edited: 0076613bug_revision_view_page.php?bugnote_id=0076613#r8300
2015-04-17 16:19OrekariaFile Added: About.java
2015-04-17 16:27OrekariaFile Added: sampledata c.patch
2015-04-17 17:16OrekariaFile Deleted: sampledata c.patch
2015-04-17 17:16OrekariaNote Edited: 0076613bug_revision_view_page.php?bugnote_id=0076613#r8301
2015-04-17 17:17OrekariaNote Edited: 0076613bug_revision_view_page.php?bugnote_id=0076613#r8302
2015-04-17 17:35OrekariaNote Edited: 0076613bug_revision_view_page.php?bugnote_id=0076613#r8303
2015-04-17 17:35OrekariaNote Edited: 0076613bug_revision_view_page.php?bugnote_id=0076613#r8304
2015-04-17 17:37hgbotCheckin
2015-04-17 17:37hgbotNote Added: 0076615
2015-04-20 12:41adrianromeroNote Added: 0076625
2015-04-20 12:41adrianromeroStatusresolved => new
2015-04-20 12:41adrianromeroResolutionfixed => open
2015-04-23 17:32hgbotCheckin
2015-04-23 17:32hgbotNote Added: 0076732
2015-04-23 17:32hgbotStatusnew => resolved
2015-04-23 17:32hgbotResolutionopen => fixed
2015-04-23 17:32hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/50d4bd781645f827cca05b81c3de7a2fc5de4c9d [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/6890535238d97a5ebcf83e211d0c877764a44aa4 [^]
2015-04-27 10:39hgbotCheckin
2015-04-27 10:39hgbotNote Added: 0076773
2015-04-28 09:19hgbotCheckin
2015-04-28 09:19hgbotNote Added: 0076804
2015-04-28 09:19hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/6890535238d97a5ebcf83e211d0c877764a44aa4 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/3691dc9698be5c597b7be3533e4be54fd34dce17 [^]
2015-04-28 10:22hgbotCheckin
2015-04-28 10:22hgbotNote Added: 0076813
2015-04-28 10:22hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/3691dc9698be5c597b7be3533e4be54fd34dce17 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5e99b27d151c47ffa6f989449e6b6c2ce12c317a [^]
2015-04-28 10:44hgbotCheckin
2015-04-28 10:44hgbotNote Added: 0076815
2015-04-28 10:44hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5e99b27d151c47ffa6f989449e6b6c2ce12c317a [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e7b80ab72f58b4dc51fd1d9e670b0d2bb55b49fa [^]
2015-04-29 13:45hgbotCheckin
2015-04-29 13:45hgbotNote Added: 0076875
2015-04-29 13:45hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e7b80ab72f58b4dc51fd1d9e670b0d2bb55b49fa [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/09f385d4ac4155d0d0c0d704531db2d820bf6781 [^]
2015-04-29 17:53hgbotCheckin
2015-04-29 17:53hgbotNote Added: 0076881
2015-04-29 17:53hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/09f385d4ac4155d0d0c0d704531db2d820bf6781 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e90cca7c3f39f6fb17aa36bf9456fbdc7ad7366e [^]
2015-04-29 19:31hgbotCheckin
2015-04-29 19:31hgbotNote Added: 0076890
2015-04-29 19:31hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e90cca7c3f39f6fb17aa36bf9456fbdc7ad7366e [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f42a3149088a44e635f4daeddee21c4545ef1f7 [^]
2015-04-30 12:48hgbotCheckin
2015-04-30 12:48hgbotNote Added: 0076910
2015-04-30 12:48hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f42a3149088a44e635f4daeddee21c4545ef1f7 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/9544311f8a7ae7a895ab99657f4306d0b7533bfd [^]
2015-05-06 18:13OrekariaProposed Solution updated
2015-05-06 18:14OrekariaProposed Solution updated
2015-05-07 22:16hudsonbotCheckin
2015-05-07 22:16hudsonbotNote Added: 0077128
2015-05-11 18:37hgbotCheckin
2015-05-11 18:37hgbotNote Added: 0077305
2015-05-11 18:37hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/9544311f8a7ae7a895ab99657f4306d0b7533bfd [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e67b4ae87d9171b72c72dea6f072fe7b89bed0c0 [^]
2015-05-12 13:58malsasuaNote Added: 0077340
2015-05-12 17:46OrekariaFile Added: products10k.sql
2015-05-12 19:36OrekariaNote Added: 0077354
2015-05-12 19:37OrekariaNote Edited: 0077354bug_revision_view_page.php?bugnote_id=0077354#r8530
2015-05-12 19:38OrekariaNote Edited: 0077354bug_revision_view_page.php?bugnote_id=0077354#r8531
2015-05-12 19:44hgbotCheckin
2015-05-12 19:44hgbotNote Added: 0077355
2015-05-12 19:44hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e67b4ae87d9171b72c72dea6f072fe7b89bed0c0 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/38284870a5bf759590dfbc7145d1b660be3341a9 [^]
2015-05-13 09:58OrekariaNote Edited: 0077354bug_revision_view_page.php?bugnote_id=0077354#r8532
2015-05-13 10:00OrekariaNote Edited: 0077354bug_revision_view_page.php?bugnote_id=0077354#r8533
2015-05-13 11:44OrekariaNote Edited: 0077354bug_revision_view_page.php?bugnote_id=0077354#r8534
2015-05-13 12:19hgbotCheckin
2015-05-13 12:19hgbotNote Added: 0077373
2015-05-13 12:19hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/38284870a5bf759590dfbc7145d1b660be3341a9 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/f94c4c4843e78c7c53c7c1c38d20d18b769cb6f4 [^]
2015-05-13 16:27malsasuaReview Assigned To => malsasua
2015-05-13 16:27malsasuaNote Added: 0077393
2015-05-13 16:27malsasuaStatusresolved => closed
2015-05-13 16:27malsasuaFixed in Version => RR15Q3

Notes
(0076373)
hgbot   
2015-04-07 17:16   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 18a6d71104c23f976047aae2076291666cc5ca83
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Tue Apr 07 17:16:06 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/18a6d71104c23f976047aae2076291666cc5ca83 [^]

Fixes issue 29495: Disallow duplicated UPCs

Show a message if the user tries to insert a duplicated UPC

---
M src-db/database/model/tables/OBMUPC_PROD_MULTIUPC.xml
A src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
A src-db/database/sourcedata/AD_MESSAGE.xml
---
(0076438)
hgbot   
2015-04-10 10:54   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 558abd4a94c652ec23bb7d3ce2aba94b3018f2be
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Tue Apr 07 19:48:06 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/558abd4a94c652ec23bb7d3ce2aba94b3018f2be [^]

Fixes issue 29495: Disallow duplicated UPCs. Trigger query scoped to Client

Also:
- added a build validation with control preference
- better message

---
M src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
M src-db/database/sourcedata/AD_MESSAGE.xml
A build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClient.class
A build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
A src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient.java
A src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076482)
hgbot   
2015-04-13 16:12   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: a2ee1e2ff212a725366b11a8f023f23f6a414f25
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Mon Apr 13 16:11:48 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/a2ee1e2ff212a725366b11a8f023f23f6a414f25 [^]

Related to issue 29495: Compiled for Java 6

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClient.class
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
---
(0076553)
hgbot   
2015-04-15 15:45   
Repository: erp/devel/pi
Changeset: 8f1883dcced251ffa4e1db1c70157d6fce92b61e
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Wed Apr 15 14:40:46 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/8f1883dcced251ffa4e1db1c70157d6fce92b61e [^]

Related to issue 29495: Changed duplicated UPCs in the 'QA Testing' sampledata

---
M referencedata/sampledata/QA_Testing/M_PRODUCT.xml
---
(0076554)
hgbot   
2015-04-15 15:45   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 50d4bd781645f827cca05b81c3de7a2fc5de4c9d
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Wed Apr 15 14:40:38 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/50d4bd781645f827cca05b81c3de7a2fc5de4c9d [^]

Fixed issue 29495: Added more improvements

- 2 more checks have been added:
  verify that there are not duplicates in the MultiUPC table when the module is updated
  verify that there are not cross duplicates between the Product and the MultiUPC tables
- Better messages when the checks detect duplicated UPCs

Side note: 'ant install.source' can insert duplicated UPCs

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClient.class
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient.java
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076613)
Orekaria   
2015-04-17 16:12   
(edited on: 2015-04-17 17:35)
How to test this issue:

This issue has 2 topics:
A) the build validation
B) the constraints to prevent duplicated UPCs while in production
C) performance tests


Suggestions to verify topic A:

1)
revert MultiUPC module to version 1.1.1
apply the openbravo patch
apply the sampledata patch a
apply the sampledata patch b
ant install.source

2) update MultiUPC to tip
ant update.database
    expected output:

     [java] 280 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 283 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     [java] 288 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - The build validation failed.
     [java] 289 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - Found 3 duplicated UPCs in the m_products table. The MultiUPC module does not allow duplicated UPC codes within the Products of the same Client. Please fix it before continuing. This query may be handy to find out which UPCs are duplicated: 'SELECT upc FROM m_product WHERE upc IS NOT NULL GROUP BY upc, ad_client_id HAVING COUNT(*) > 1'
     [java] 3344234 (2 found)
     [java] 3344111 (2 found)
     [java] 3344567 (2 found)
     [java]
     [java]

     BUILD FAILED


3) remove the openbravo patch
revert back the MultiUPC module to version 1.1.1
ant install.source
ant update.database
    expected output:

     [java] 285 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 287 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     [java] 294 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - The build validation failed.
     [java] 295 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - Found 1 duplicated UPCs in the obmupc_prod_multiupc table. The MultiUPC module does not allow duplicated UPC codes within the Products of the same Client. Please fix it before continuing. This query may be handy to find out which UPCs are duplicated: 'SELECT upc FROM obmupc_prod_multiupc WHERE upc IS NOT NULL GROUP BY upc, ad_client_id HAVING COUNT(*) > 1'
     [java] x10 (2 found)
     [java]
     [java]

     BUILD FAILED

4) remove the sampledata patch a
apply the sampledata patch b
revert back the MultiUPC module to version 1.1.1
ant install.source
ant update.database
    expected output:
     [java] 281 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 283 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     [java] 290 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - The build validation failed.
     [java] 291 [main] ERROR org.openbravo.buildvalidation.BuildValidationHandler - Found 1 duplicated UPCs when joining the obmupc_prod_multiupc and the m_product tables. The MultiUPC module does not allow duplicated UPC codes within the Products of the same Client. Please fix it before continuing. This query may be handy to find out which UPCs are duplicated: 'SELECT m_product.upc FROM m_product JOIN obmupc_prod_multiupc ON m_product.upc = obmupc_prod_multiupc.upc'
     [java] 3344567 (1 found)
     [java]
     [java]

     BUILD FAILED

5) remove all attached patches
ant install.source
ant update.database
verify that the build is SUCCESSFUL

7) execute ant update.database again
verify that the UPC verification is skipped:
     [java] 296 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 298 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Skipping verification that UPCs are unique. It can be activated setting the 'Look for duplicated UPCs when building' preference to 'Y'
     ...

6) login in the ERP as system administrator
verify that a new preference has been introduced, called 'Look for duplicated UPCs when building' with value 'Y'

7) modify the value of the 'Look for duplicated UPCs when building' preference to 'N'
ant update.database
verify that the build validation is being executed again
     [java] 282 [main] INFO org.openbravo.buildvalidation.BuildValidationHandler - Executing build validation: org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient
     [java] 284 [main] INFO org.openbravo.multiupc.buildvalidation.DuplicatedUPCForClient - Verifying that the UPCs are unique. This process can take a while but will only be executed once unless the 'Look for duplicated UPCs when building' preference is set to 'Y'
     ...


Suggestions to verify topic B:

1) verify that, given an existing UPC code of a Product, it can not be added in the MultiUPC tab of that or other product
2) verify that, given an existing UPC in the MultiUPC tab, it can not be added to any other product in the MultiUPC window (it can still be added to an existing product, but that is not what this issue is trying to fix)


Suggestions to verify topic C:

1) verify the time spent by the trigger:
in a postgresql console, e.g:
  psql -U tad -d obtip
activate the timing
  \timing
insert an existing value in the MultiUPC table, e.g:
  INSERT INTO obmupc_prod_multiupc (obmupc_prod_multiupc_id, ad_client_id, ad_org_id, isactive, createdby, updatedby, upc, m_product_id) VALUES ('F9F477F2752D40219D17E85EC450EC01', '4028E6C72959682B01295A070852010D', 0, 'Y', 100, 100, 'x10', 'A9AED2CBF1E64493A65EB07128557934');
try to insert it again:
  time spent can be seen in the console

2) add 10 million product to the database
replace the About.java file in /openbravo/src/org/openbravo/erpCommon/ad_forms/About.java with the attached About.java file
start Tomcat
log into the ERP
in the help menu, click About (depending on your computer, a short or a long wait will happen)
open pgAdmin
select the context database
stop all triggers in the m_product table:
    alter table m_product disable trigger all
execute:
    copy m_product from '/tmp/copyProducts.sql' CSV
    this process can take several minutes, be patient, take a long break
    10 million products will have been added to the database
activate all triggers again
  alter table m_product enable trigger all

3) verify again the time spent by the trigger that prevent UPCs in the Product to be duplicated in the MultiUPC table:
in a postgresql console, e.g:
  psql -U tad -d obtip
activate the timing
  \timing
try to insert a UPC that is already in the m_product table, e.g:
  obtip=# INSERT INTO obmupc_prod_multiupc (obmupc_prod_multiupc_id, ad_client_id, ad_org_id, isactive, createdby, updatedby, upc, m_product_id) VALUES ('F9F577F2752D40219D17E85EC450EC01', '4028E6C72959682B01295A070852010D', 0, 'Y', 100, 100, '3344567', 'A9AED2CBF1E64493A65EB07128557934');
expected output:
  ERROR: @OBMUPC_DUPLICATED_UPC@
  Time: 0.xxx ms (ACI)
(0076615)
hgbot   
2015-04-17 17:37   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 358ec850bf82165ceff2a7cc739adc3fcdefa74f
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Fri Apr 17 17:36:51 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/358ec850bf82165ceff2a7cc739adc3fcdefa74f [^]

Related to issue 29495: Added more improvements (II)

- Performance improvements
- Better messages
- If duplicated UPCs are found, the first 15 will be logged

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClient.class
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient.java
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076625)
adrianromero   
2015-04-20 12:41   
The following case does not work properly
* Create an UPC value in the multiUPC tab for some product.
* Go to another product and modify the UPC value in the product tab with the same value created in the previous step.
* Verify that no error is raised

This action must raise an error that the UPC is duplicated.

This can be fixed adding a trigger for the m_product table in the multiupc module that verifies this condition.
(0076732)
hgbot   
2015-04-23 17:32   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 6890535238d97a5ebcf83e211d0c877764a44aa4
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Thu Apr 23 17:32:23 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/6890535238d97a5ebcf83e211d0c877764a44aa4 [^]

Fixes issue 29495: Provide global UPC uniqueness

After further considerations, we now provide global UPC uniqueness as it benefits the end user and data integrity. To achive this, in addition to the previous changes, a constraint and a trigger has been added to the m_product table

Also
- triggers have been changed to check what they are really checking, that is: if another UPC exists
- in the build, the message when a duplicated UPC has been found, has been improved showing the client search_key and the product search_key in addition to the duplicated UPC

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClient.class
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
M src-db/database/sourcedata/AD_MESSAGE.xml
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient.java
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
A src-db/database/model/modifiedTables/M_PRODUCT.xml
A src-db/database/model/modifiedTables/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0076773)
hgbot   
2015-04-27 10:39   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 748bdee5ccd38ec4ad68c455178245738413544d
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Mon Apr 27 10:39:09 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/748bdee5ccd38ec4ad68c455178245738413544d [^]

Related to issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076804)
hgbot   
2015-04-28 09:19   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 3691dc9698be5c597b7be3533e4be54fd34dce17
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Tue Apr 28 09:19:20 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/3691dc9698be5c597b7be3533e4be54fd34dce17 [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (II)

We can look for the new MultiUPC table in the ad_table

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076813)
hgbot   
2015-04-28 10:22   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 5e99b27d151c47ffa6f989449e6b6c2ce12c317a
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Tue Apr 28 10:21:58 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5e99b27d151c47ffa6f989449e6b6c2ce12c317a [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (III)

Fix for Oracle

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076815)
hgbot   
2015-04-28 10:44   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: e7b80ab72f58b4dc51fd1d9e670b0d2bb55b49fa
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Tue Apr 28 10:44:48 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e7b80ab72f58b4dc51fd1d9e670b0d2bb55b49fa [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (IV)

Fix for Oracle

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0076875)
hgbot   
2015-04-29 13:45   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 09f385d4ac4155d0d0c0d704531db2d820bf6781
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Wed Apr 29 13:33:52 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/09f385d4ac4155d0d0c0d704531db2d820bf6781 [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (V)

- Oracle does not allow 2 different indexes over 1 fiel. The unique index has been changed by a constraint
- a file has been moved to its exported folder
- formatting changes after exporting

---
M src-db/database/model/modifiedTables/M_PRODUCT.xml
M src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
A src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
R src-db/database/model/modifiedTables/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0076881)
hgbot   
2015-04-29 17:53   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: e90cca7c3f39f6fb17aa36bf9456fbdc7ad7366e
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Wed Apr 29 17:52:23 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e90cca7c3f39f6fb17aa36bf9456fbdc7ad7366e [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (VI)

Oracle does not allow an index and a constraint over the same field. In addition, the Client must be part of the constraint

---
M src-db/database/model/modifiedTables/M_PRODUCT.xml
---
(0076890)
hgbot   
2015-04-29 19:31   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 5f42a3149088a44e635f4daeddee21c4545ef1f7
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Wed Apr 29 19:30:58 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f42a3149088a44e635f4daeddee21c4545ef1f7 [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (VII)

---
M src-db/database/model/modifiedTables/M_PRODUCT.xml
---
(0076910)
hgbot   
2015-04-30 12:48   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 9544311f8a7ae7a895ab99657f4306d0b7533bfd
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Thu Apr 30 12:48:08 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/9544311f8a7ae7a895ab99657f4306d0b7533bfd [^]

Fixed issue 29495: The uniqueness of the UPCs in the m_product table is enforced with a trigger instead of an index

The problem was that Oracle does not allow nulls in a unique index

---
M src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
R src-db/database/model/modifiedTables/M_PRODUCT.xml
---
(0077128)
hudsonbot   
2015-05-07 22:16   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/a1817f84bd8b [^]
Maturity status: Test
(0077305)
hgbot   
2015-05-11 18:37   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: e67b4ae87d9171b72c72dea6f072fe7b89bed0c0
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Mon May 11 18:34:52 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/e67b4ae87d9171b72c72dea6f072fe7b89bed0c0 [^]

Fixed issue 29495: Changed query to verify if the 'obmupc_prod_multiupc' exists (VIII - Optimizations)

---
M build/classes/org/openbravo/multiupc/buildvalidation/DuplicatedUPCForClientData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedUPCForClient_data.xsql
---
(0077340)
malsasua   
2015-05-12 13:58   
performance test plan:
ORACLE: insert 10.000 RECORDS-> without/with trigger: 155 secs/190 secs (122.5%)
(0077354)
Orekaria   
2015-05-12 19:36   
(edited on: 2015-05-13 11:44)
performance tests inserting 10.000 products in a 10 million product table:
note that the statement trigger is being executed once before the transaction ends, so optimized batch insert or updates should see a performance improvement
note that the trigger is reporting instantly in single row transactions


statementExecuting: select count(1) from m_product
Query 1 of 1, Rows read: 1, Elapsed time (seconds) - Total: 1.154, SQL query: 1.153, Reading results: 0.001


1) without trigger:

statementExecuting: copy m_product from '/tmp/copyProducts.sql' CSV
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 14.233, SQL query: 14.233, Reading results: 0

statementExecuting: copy m_product from '/tmp/copyProducts.sql' CSV
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 14.245, SQL query: 14.245, Reading results: 0

2) trigger type 1: if exists with row scope

statementExecuting: copy m_product from '/tmp/products10k.sql' CSV
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 14.88, SQL query: 14.88, Reading results: 0

statementExecuting: copy m_product from '/tmp/products10kb.sql' CSV
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 15.212, SQL query: 15.212, Reading results: 0


3) trigger type 2: count > 0 with statement scope

statementExecuting: copy m_product from '/tmp/products10kc.sql' CSV
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 34.129, SQL query: 34.129, Reading results: 0

statementExecuting: copy m_product from '/tmp/products10kd.sql' CSV
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 34.142, SQL query: 34.142, Reading results: 0
(0077355)
hgbot   
2015-05-12 19:44   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 38284870a5bf759590dfbc7145d1b660be3341a9
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Tue May 12 19:29:26 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/38284870a5bf759590dfbc7145d1b660be3341a9 [^]

Fixed issue 29495: The uniqueness of the UPCs in the m_product table is enforced with a trigger instead of an index (II)

Oracle fixes

---
M src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0077373)
hgbot   
2015-05-13 12:19   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: f94c4c4843e78c7c53c7c1c38d20d18b769cb6f4
Author: Rafa Alonso <rafael.alonso <at> openbravo.com>
Date: Wed May 13 12:19:26 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/f94c4c4843e78c7c53c7c1c38d20d18b769cb6f4 [^]

Fixed issue 29495: The uniqueness of the UPCs in the m_product table is enforced with a trigger instead of an index (III)

Oracle fixes

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0077393)
malsasua   
2015-05-13 16:27   
verified