Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0030179Openbravo ERPA. Platformpublic2015-06-15 12:502017-03-16 15:25
Reportermtaal 
Assigned ToAugustoMauch 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR15Q4Fixed 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
Summary0030179: DBSourceManager support indexes with functions
DescriptionExtend dbsourcemanager so that it can import/export and create/update indexes in the database which include functions such as UPPER.

An example of such an index:
CREATE INDEX c_bpartner_value_upper ON c_bpartner USING btree (upper(value) COLLATE pg_catalog."default" varchar_pattern_ops);

These type of indexes are used to do case insensitive searching from the user interface.
Steps To ReproduceSee:
http://wiki.openbravo.com/wiki/Projects:DB_Support_Indexes_With_Functions [^]
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to feature request 0030525 closed AugustoMauch Provide support in DBSourceManager to define an operator class in index columns 
related to defect 0035543 closed alostale incorrect export of indexes including CASE expressions 
related to defect 0034488 closed caristu Unsolvable dbconsistency problems in index functions 
related to feature request 00346503.0PR17Q2 closed caristu Ability to generate indexes to speed up searching using 'contains' operators 
related to defect 0035458 closed platform com.openbravo.test.integration.erp.modules.ci.Upgrade_FS fails with function index 
related to defect 0035474 closed alostale Function based indexes do not support arithmetic operations on columns 
related to design defect 0035519 closed alostale can only have one function column in db indexes 
related to feature request 0029943 closed alostale support to define DB functions volatility 
related to defect 0041785 closed alostale Error with function index including a coalesce with a varchar 
blocks defect 0035210 closed markmm82 sequential scans in c_bpartner using partner selector drop down filter 
causes defect 0032707 closed NaroaIriarte java.sql.SQLSyntaxErrorException: ORA-00918 while packaging module 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-06-15 12:50mtaalNew Issue
2015-06-15 12:50mtaalAssigned To => AugustoMauch
2015-06-15 12:50mtaalModules => Core
2015-06-15 12:50mtaalTriggers an Emergency Pack => No
2015-06-17 02:34eintelauIssue Monitored: eintelau
2015-08-07 14:33AugustoMauchRelationship addedrelated to 0030525
2015-08-19 08:41AugustoMauchIssue Monitored: alostale
2015-08-19 08:43AugustoMauchReview Assigned To => alostale
2015-08-19 08:44hgbotCheckin
2015-08-19 08:44hgbotNote Added: 0079416
2015-08-19 08:44hgbotStatusnew => resolved
2015-08-19 08:44hgbotResolutionopen => fixed
2015-08-19 08:44hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/243b29f38be260b5dec6743eebe89e5adbbafb24 [^]
2015-08-19 09:07hgbotCheckin
2015-08-19 09:07hgbotNote Added: 0079418
2015-08-19 09:15hgbotCheckin
2015-08-19 09:15hgbotNote Added: 0079420
2015-08-19 09:15hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/243b29f38be260b5dec6743eebe89e5adbbafb24 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/c9baf7485dd9f33f94641aa8ed068b5d390d4950 [^]
2015-08-20 23:16hudsonbotCheckin
2015-08-20 23:16hudsonbotNote Added: 0079557
2015-08-20 23:16hudsonbotCheckin
2015-08-20 23:16hudsonbotNote Added: 0079559
2015-09-03 10:47dmitry_mezentsevNote Added: 0080036
2015-09-04 11:05alostaleTag Attached: Performance
2015-09-04 11:17alostaleNote Added: 0080090
2015-09-04 11:17alostaleStatusresolved => closed
2015-09-04 11:17alostaleFixed in Version => 3.0PR15Q4
2016-04-20 15:38NaroaIriarteRelationship addedcauses 0032707
2016-10-14 08:25caristuRelationship addedrelated to 0034206
2016-10-14 08:25caristuRelationship deletedrelated to 0034206
2016-11-25 09:53alostaleRelationship addedrelated to 0034488
2017-02-09 10:05alostaleRelationship addedblocks 0035210
2017-02-09 10:06alostaleRelationship addedrelated to 0034650
2017-03-10 09:39alostaleRelationship addedrelated to 0035458
2017-03-10 13:48alostaleRelationship addedrelated to 0035474
2017-03-15 10:19alostaleRelationship addedrelated to 0035519
2017-03-16 15:25alostaleRelationship addedrelated to 0035543
2019-03-20 13:40alostaleRelationship addedrelated to 0029943
2019-09-10 10:30alostaleRelationship addedrelated to 0041785

Notes
(0079416)
hgbot   
2015-08-19 08:44   
Repository: erp/devel/pi
Changeset: 243b29f38be260b5dec6743eebe89e5adbbafb24
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Aug 19 08:36:11 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/243b29f38be260b5dec6743eebe89e5adbbafb24 [^]

Fixes issue 30179: Adds support to function based indexes in dbsourcemanager

Now it is possible to export and import indexes whose columns use functions, for instance:

CREATE INDEX c_bpartner_upper_name
  ON c_bpartner
  USING btree
  (upper(name) COLLATE pg_catalog."default");

CREATE INDEX c_bpartner_upper_replace_name
  ON c_bpartner
  USING btree
  (replace(upper(name),'A','B') COLLATE pg_catalog."default");

There are two restrictions:
1- An index cannot have more than one column that uses a function expression. So this is supported:

CREATE INDEX c_bpartner_upper_replace_name_id
  ON c_bpartner
  USING btree
  (replace(upper(name),'A','B') COLLATE pg_catalog."default", id);

But this is not:

CREATE INDEX c_bpartner_upper_replace_name_id
  ON c_bpartner
  USING btree
  (replace(upper(name),'A','B') COLLATE pg_catalog."default", upper(id));

2- A function expression should not contain empty strings. In Oracle an empty String is treated as NULL, so if one index is imported with a function containing an empty string parameter, when that index is exported the empty string will be replaced by NULL.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0079418)
hgbot   
2015-08-19 09:07   
Repository: erp/devel/pi
Changeset: 3b8dead7ca40004b8deb88a37f55d4d80d45c35e
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Aug 19 09:05:39 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/3b8dead7ca40004b8deb88a37f55d4d80d45c35e [^]

Related with issue 30179: Fixes problem with merge

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

Fixes issue 30179: Merge [func-idx] with dbms main branch

---
M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java
---
(0079557)
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
(0079559)
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
(0080036)
dmitry_mezentsev   
2015-09-03 10:47   
Not closed for 15 days!!!
(0080090)
alostale   
2015-09-04 11:17   
reviewed as part of the project