Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0030525Openbravo ERPA. Platformpublic2015-08-07 14:302016-10-14 08:26
ReporterAugustoMauch 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR15Q4 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0030525: Provide support in DBSourceManager to define an operator class in index columns
DescriptionDefining operator classes [1] in indexes allows to use them in queries involving pattern matching expressions (for instances, select name from c_bpartner where UPPER(name) ilike 'Ali%';)

This is needed in Postgresql, but it is already working in Oracle

[1] http://www.postgresql.org/docs/9.3/static/indexes-opclass.html [^]
Steps To Reproduce-
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to defect 0034206 acknowledged caristu In Oracle comments are not updated properly after adding/deleting the operator class on an existing function based index 
related to feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
related to defect 0034236 closed caristu newIndexesPostAction is executing duplicated actions 
related to design defect 0034258 new Triage Platform Base Not possible to apply two indexes on the same column but with different operator class in Oracle 
related to defect 0034646 closed caristu Comments should be queried from user tables 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-08-07 14:30AugustoMauchNew Issue
2015-08-07 14:30AugustoMauchAssigned To => AugustoMauch
2015-08-07 14:30AugustoMauchModules => Core
2015-08-07 14:30AugustoMauchTriggers an Emergency Pack => No
2015-08-07 14:31AugustoMauchSummaryProvide support in DBSourceManager to define an operator class in column indexes => Provide support in DBSourceManager to define an operator class in index columns
2015-08-07 14:32AugustoMauchDescription Updatedbug_revision_view_page.php?rev_id=9184#r9184
2015-08-07 14:33AugustoMauchRelationship addedrelated to 0030179
2015-08-19 08:48AugustoMauchIssue Monitored: alostale
2015-08-19 08:49AugustoMauchReview Assigned To => alostale
2015-08-19 08:50hgbotCheckin
2015-08-19 08:50hgbotNote Added: 0079417
2015-08-19 08:50hgbotStatusnew => resolved
2015-08-19 08:50hgbotResolutionopen => fixed
2015-08-19 08:50hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/63aa498556707c5b8a2459f7801eb30fcaf9e692 [^]
2015-08-19 09:14hgbotCheckin
2015-08-19 09:14hgbotNote Added: 0079419
2015-08-19 09:16hgbotCheckin
2015-08-19 09:16hgbotNote Added: 0079421
2015-08-19 09:16hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/63aa498556707c5b8a2459f7801eb30fcaf9e692 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/a052282baa1a6d30972cf16ca5512ba59dc8ffee [^]
2015-08-20 23:16hudsonbotCheckin
2015-08-20 23:16hudsonbotNote Added: 0079558
2015-08-20 23:16hudsonbotCheckin
2015-08-20 23:16hudsonbotNote Added: 0079560
2015-08-24 10:20hgbotCheckin
2015-08-24 10:20hgbotNote Added: 0079629
2015-08-24 17:38hudsonbotCheckin
2015-08-24 17:38hudsonbotNote Added: 0079644
2015-09-03 10:47dmitry_mezentsevNote Added: 0080035
2015-09-04 11:05alostaleTag Attached: Performance
2015-09-04 11:17alostaleNote Added: 0080089
2015-09-04 11:17alostaleStatusresolved => closed
2015-09-04 11:17alostaleFixed in Version => 3.0PR15Q4
2016-10-14 08:26caristuRelationship addedrelated to 0034206
2016-10-19 10:28caristuRelationship addedrelated to 0034236
2016-10-20 19:00caristuRelationship addedrelated to 0034258
2016-11-28 12:12caristuRelationship addedrelated to 0034646

Notes
(0079417)
hgbot   
2015-08-19 08:50   
Repository: erp/devel/pi
Changeset: 63aa498556707c5b8a2459f7801eb30fcaf9e692
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Aug 19 08:47:15 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/63aa498556707c5b8a2459f7801eb30fcaf9e692 [^]

Fixes issue 30525: Supports the use of Operator Classes in index columns

The fix is very straightforward in PostgreSQL: if the column of an index defines an operator class, that operator class is included in the XML, and viceversa. This is very easy to do be
cause the info about the operator class used in the index column is stored in the indclass column of the pg_index table.

It gets more complicated for Oracle. Oracle does not have the concept of operator class, so when an index that defines one is imported, there is no standard place where to store it. And
 it really needs to be stored, because otherwise when that index is exported from Oracle it would lose the operator class. To solve this, the operator classes used by index columns are
stored as comments in the table that owns the indexes with the following format:
"indexName1.indexColumn1.operatorClass=operatorClass1$indexName2.indexColumn2.operatorClass=operatorClass2$..."

These comments need to be updated each time an index that defines an operator class is added or removed, both when it is done at the same time of the table creation or after it.

The operator classes used in the index columns should not be taken into account when comparing indexes in Oracle. With this we achieve that indexes will not be recreated in oracle if the only change is tha
t an operator class has been added/removed from an index column.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0079419)
hgbot   
2015-08-19 09:14   
Repository: erp/devel/pi
Changeset: 9ac5cf4e75d2bff447f35bec4c68170415251eec
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Aug 19 09:12:26 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/9ac5cf4e75d2bff447f35bec4c68170415251eec [^]

Related with issue 30525: Fixes problem with merge

---
M src-db/database/lib/dbsourcemanager.jar
---
(0079421)
hgbot   
2015-08-19 09:16   
Repository: erp/devel/dbsm-main
Changeset: a052282baa1a6d30972cf16ca5512ba59dc8ffee
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Aug 19 09:11:10 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/a052282baa1a6d30972cf16ca5512ba59dc8ffee [^]

Fixes issue 30525: Merges [idx-operator-class] with dbms main branch

---
M src-test/src/org/openbravo/dbsm/test/model/ModelSuite.java
M src/mapping.xml
M src/org/apache/ddlutils/PlatformInfo.java
M src/org/apache/ddlutils/alteration/ModelComparator.java
M src/org/apache/ddlutils/model/IndexColumn.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/Oracle8Builder.java
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlBuilder.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
A src-test/model/indexes/BASIC_INDEX_WITH_OPERATOR_CLASS.xml
A src-test/model/indexes/FUNCTION_INDEX_WITHOUT_OPERATOR_CLASS.xml
A src-test/model/indexes/FUNCTION_INDEX_WITH_OPERATOR_CLASS.xml
A src-test/model/indexes/OTHER_FUNCTION_INDEX_WITH_OPERATOR_CLASS.xml
A src-test/model/recreation/BASE_MODEL_VARCHAR_COL.xml
A src-test/model/recreation/IDX_VARCHAR_COL.xml
A src-test/model/recreation/OP_CLASS_IDX.xml
A src-test/src/org/openbravo/dbsm/test/model/OperatorClassIndexes.java
---
(0079558)
hudsonbot   
2015-08-20 23: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/8c91718397a5 [^]
Maturity status: Test
(0079560)
hudsonbot   
2015-08-20 23: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/8c91718397a5 [^]
Maturity status: Test
(0079629)
hgbot   
2015-08-24 10:20   
Repository: erp/devel/pi
Changeset: ad82fe86d3d4a54ae240f816000db69171be488b
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Aug 24 10:17:32 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/ad82fe86d3d4a54ae240f816000db69171be488b [^]

Related with issue 30525:Close the connection after retrieving table comments

---
M src-db/database/lib/dbsourcemanager.jar
---
(0079644)
hudsonbot   
2015-08-24 17:38   
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/5da132f0eb35 [^]
Maturity status: Test
(0080035)
dmitry_mezentsev   
2015-09-03 10:47   
Not closed for 15 days!!!
(0080089)
alostale   
2015-09-04 11:17   
reviewed as part of the project