Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035519Openbravo ERPY. DBSourceManagerpublic2017-03-15 10:192017-03-17 09:59
Reporteralostale 
Assigned Toalostale 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q2 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035519: can only have one function column in db indexes
DescriptionDB indexes can only have one column based on an index. This limitation is documented [1].

[1] http://wiki.openbravo.com/wiki/Constraints_and_Triggers#Indexes [^]
Steps To Reproduce1. Create the following index in PG:
CREATE INDEX c_bpartner_name2_id
  ON c_bpartner
  USING btree
  (UPPER(REPLACE(name, 'a', 'b')),
   UPPER(c_bpartner_id));
2. export database
3. check how it gets exported -> ERROR: both functions are merged in a single column
hg diff src-db/database/model/tables/C_BPARTNER.xml
diff -r afcca1baf74c src-db/database/model/tables/C_BPARTNER.xml
--- a/src-db/database/model/tables/C_BPARTNER.xml Tue Mar 14 09:22:54 2017 +0100
+++ b/src-db/database/model/tables/C_BPARTNER.xml Wed Mar 15 10:17:33 2017 +0100
@@ -468,6 +468,9 @@
         <index-column name="NAME"/>
         <index-column name="C_BPARTNER_ID"/>
       </index>
+ <index name="C_BPARTNER_NAME2_ID" unique="false">
+ <index-column name="functionBasedColumn" functionExpression="UPPER(REPLACE(NAME,'a','b')), upper(c_bpartner_id)"/>
+ </index>
       <index name="C_BPARTNER_REFNO_ID" unique="false">
         <index-column name="REFERENCENO"/>
         <index-column name="C_BPARTNER_ID"/>
4. update database
5. Check index definition
  -> ERROR: it is not as the original
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
related to defect 0035474 closed alostale Function based indexes do not support arithmetic operations on columns 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-03-15 10:19alostaleNew Issue
2017-03-15 10:19alostaleAssigned To => platform
2017-03-15 10:19alostaleModules => Core
2017-03-15 10:19alostaleTriggers an Emergency Pack => No
2017-03-15 10:19alostaleStatusnew => acknowledged
2017-03-15 10:19alostaleRelationship addedrelated to 0030179
2017-03-15 14:07alostaleReview Assigned To => caristu
2017-03-15 14:07alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=14807#r14807
2017-03-15 14:08hgbotCheckin
2017-03-15 14:08hgbotNote Added: 0095035
2017-03-15 14:08hgbotStatusacknowledged => resolved
2017-03-15 14:08hgbotResolutionopen => fixed
2017-03-15 14:08hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7 [^]
2017-03-15 14:09hgbotCheckin
2017-03-15 14:09hgbotNote Added: 0095037
2017-03-15 14:09hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/2686a1fd45b975ea70ca9eb413a38775bff31190 [^]
2017-03-15 20:22hudsonbotCheckin
2017-03-15 20:22hudsonbotNote Added: 0095303
2017-03-17 09:58caristuNote Added: 0095356
2017-03-17 09:58caristuStatusresolved => closed
2017-03-17 09:58caristuFixed in Version => 3.0PR17Q2
2017-03-17 09:59caristuRelationship addedrelated to 0035474
2017-03-17 09:59caristuAssigned Toplatform => alostale

Notes
(0095035)
hgbot   
2017-03-15 14:08   
Repository: erp/devel/pi
Changeset: a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Mar 15 14:08:12 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7 [^]

fixed bug 35474, fixed issue 35519: function based indexes improvements

  Improved the way function definitions in indexes are read from DB, now they are
  read per column, using in PG the index specific function pg_get_indexdef. This
  allows to:

   * properly read indexes based on arithmethic expressions ie (col1-col2)
   * support more than one index column based on function

---
M src-db/database/lib/dbsourcemanager.jar
---
(0095037)
hgbot   
2017-03-15 14:09   
Repository: erp/devel/dbsm-main
Changeset: 2686a1fd45b975ea70ca9eb413a38775bff31190
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Mar 15 13:35:00 2017 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/2686a1fd45b975ea70ca9eb413a38775bff31190 [^]

fixed bug 35474, fixed issue 35519: function based indexes improvements

  Improved the way function definitions in indexes are read from DB, now they are
  read per column, using in PG the index specific function pg_get_indexdef. This
  allows to:

   * properly read indexes based on arithmethic expressions ie (col1-col2)
   * support more than one index column based on function

---
M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
A src-test/model/indexes/FUNCTION_INDEX_MULTI_COL.xml
A src-test/model/indexes/FUNCTION_INDEX_OPERATOR.xml
---
(0095303)
hudsonbot   
2017-03-15 20:22   
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/54e102bef53e [^]
Maturity status: Test
(0095356)
caristu   
2017-03-17 09:58   
Code reviewed + testing OK.