Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0031685Openbravo ERPY. DBSourceManagerpublic2015-12-10 10:412016-03-17 10:54
ReporterAugustoMauch 
Assigned ToAugustoMauch 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR16Q2 
Merge Request Status
Review Assigned Toalostale
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0031685: Allow using SQL wildcards in the dbsourmanager exclude filter
DescriptionIt would be great if SQL wildcards [1] could be used to define exclusions in the exclude filter.

For instance, if all the tables with the SYM_ prefix should be excluded, it would be enough to add this line to the exclude filter:

    <excludedTable name="SYM\_%"/>

Otherwise all the tables that use the SYM_ prefix should be listed in the excluded filter.

Note that the underscore character must be escaped, because if not it is interpreted as a substitute for any single character.

[1] http://www.w3schools.com/sql/sql_wildcards.asp [^]
Steps To Reproduce-
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to design defect 0029800 closed AugustoMauch Allow excluding sequences from model 
related to feature request 0031672 closed AugustoMauch Add tests to check that the exclusion filter works properly in DBSourceManager 
blocks defect 0024051 closed alostale Audit trail triggers are exported to core 
blocks defect 0028149 closed alostale update.database tries to delete pg_stat_statments and pg_stat_statments_reset 
causes defect 0035258 closed caristu NullPointerException thrown when updating database having a wrong declaration in exclude filters 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-12-10 10:41AugustoMauchNew Issue
2015-12-10 10:41AugustoMauchAssigned To => platform
2015-12-10 10:41AugustoMauchOBNetwork customer => No
2015-12-10 10:41AugustoMauchModules => Core
2015-12-10 10:41AugustoMauchTriggers an Emergency Pack => No
2015-12-10 10:41AugustoMauchAssigned Toplatform => AugustoMauch
2015-12-10 16:57AugustoMauchDescription Updatedbug_revision_view_page.php?rev_id=10445#r10445
2015-12-10 18:36AugustoMauchRelationship addedrelated to 0024051
2015-12-10 18:37AugustoMauchRelationship replacedblocks 0024051
2015-12-11 10:12AugustoMauchStatusnew => scheduled
2015-12-11 10:13AugustoMauchNote Added: 0082675
2016-01-20 11:44AugustoMauchReview Assigned To => alostale
2016-01-20 12:23hgbotCheckin
2016-01-20 12:23hgbotNote Added: 0083442
2016-01-20 12:23hgbotStatusscheduled => resolved
2016-01-20 12:23hgbotResolutionopen => fixed
2016-01-20 12:23hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/9cbd94b17c8829193f42387b92d1d098b018637f [^]
2016-01-20 12:24hgbotCheckin
2016-01-20 12:24hgbotNote Added: 0083447
2016-01-20 12:24hgbotCheckin
2016-01-20 12:24hgbotNote Added: 0083448
2016-01-20 12:24hgbotCheckin
2016-01-20 12:24hgbotNote Added: 0083449
2016-01-20 12:24hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/9cbd94b17c8829193f42387b92d1d098b018637f [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/edc057968bb024f542e540cfd6753b83665ee4b4 [^]
2016-01-20 12:24hgbotCheckin
2016-01-20 12:24hgbotNote Added: 0083450
2016-01-20 12:24hgbotCheckin
2016-01-20 12:24hgbotNote Added: 0083451
2016-01-20 12:24hgbotCheckin
2016-01-20 12:24hgbotNote Added: 0083452
2016-01-20 13:30AugustoMauchRelationship addedrelated to 0029800
2016-01-20 13:30AugustoMauchRelationship addedrelated to 0031672
2016-01-20 15:26alostaleNote Added: 0083465
2016-01-20 15:26alostaleStatusresolved => closed
2016-01-20 15:26alostaleFixed in Version => 3.0PR16Q2
2016-03-17 10:54hudsonbotCheckin
2016-03-17 10:54hudsonbotNote Added: 0085059
2016-12-15 09:13alostaleRelationship addedrelated to 0028149
2017-01-24 16:30alostaleRelationship replacedblocks 0028149
2017-02-14 13:08caristuRelationship addedcauses 0035258

Notes
(0082675)
AugustoMauch   
2015-12-11 10:13   
Done, waiting for the PR16Q2 window to be open
(0083442)
hgbot   
2016-01-20 12:23   
Repository: erp/devel/pi
Changeset: 9cbd94b17c8829193f42387b92d1d098b018637f
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Jan 20 12:20:10 2016 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/9cbd94b17c8829193f42387b92d1d098b018637f [^]

Fixes issue 31672, fixes issue 29800, fixes issue 31685: Wildcards in exclusion

Updates the dbsourcemanager jar, now it is allowed to use wildcards in the exclusion filter (see [1]).

[1] http://wiki.openbravo.com/wiki/How_To_Exclude_Database_Physical_Objects_From_Model [^]

---
M src-db/database/lib/dbsourcemanager.jar
---
(0083447)
hgbot   
2016-01-20 12:24   
Repository: erp/devel/dbsm-main
Changeset: 1cfc605b10b11f632cb65346042edd8ae873d760
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Dec 10 10:46:51 2015 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/1cfc605b10b11f632cb65346042edd8ae873d760 [^]

Related with issue 31685: Refactor how the exclude filter where clause is built

The logic that builds the part of the query where clause to exclude the objects from the exclude filters has been moved from the ModelLoaders to the ExcludeFilter class. That way it is done only in one place and it is easier to modify, in anticipation of the changes to support SQL wildcards in the exclude filter.

---
M src/org/apache/ddlutils/platform/ExcludeFilter.java
M src/org/apache/ddlutils/platform/ModelLoaderBase.java
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
(0083448)
hgbot   
2016-01-20 12:24   
Repository: erp/devel/dbsm-main
Changeset: f8a00436a70d644ce63dacdf83fe83a0aecdc5ea
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Dec 10 14:45:07 2015 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/f8a00436a70d644ce63dacdf83fe83a0aecdc5ea [^]

Related with issue 31685: Refactor the functions to check db objects exists

Before the refactor each function allowed to check for the existence of one specific object (EXCLUDED_TABLE, EXCLUDED_VIEW, etc). Now the name of the object is passed as a parameter.

---
M src-test/src/org/openbravo/dbsm/test/model/CheckExcludeFilter.java
---
(0083449)
hgbot   
2016-01-20 12:24   
Repository: erp/devel/dbsm-main
Changeset: edc057968bb024f542e540cfd6753b83665ee4b4
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Dec 10 17:01:21 2015 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/edc057968bb024f542e540cfd6753b83665ee4b4 [^]

Fixes issue 31685: Wildcards are supported in exclude filter

Now it is supported to use wildcards [1] inside exclude filter, for instance:

<excludedTable name="SYM\_%"/>

That sentence will exclude all the tables whose smae starts with SYM_. It is supported for all the database objects that can be excluded: tables, views, triggers, functions and sequences.

An exclusion is considered to be a wildcard if it contains the '%' character, if not what will be excluded will be the database object whose name is equals to the provided name, ignoring the casing.

To escape the underscore character a backslash must be used, like in the example. The is logic inside ExcludeFilter and OracleModelLoader to handle how this must be escaped in Oracle (nothing else is required for Postgres).

[1] http://www.w3schools.com/sql/sql_wildcards.asp [^]

---
M src/org/apache/ddlutils/platform/ExcludeFilter.java
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
---
(0083450)
hgbot   
2016-01-20 12:24   
Repository: erp/devel/dbsm-main
Changeset: b3a2447d43a9a13918725ba4055a2ab5a9af0bb5
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Dec 10 17:14:24 2015 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/b3a2447d43a9a13918725ba4055a2ab5a9af0bb5 [^]

Related with issue 31685: Adds tests for exclusion filters that use wildcards

For each database object that can be excluded (tables, views, functions, triggers and sequences) three tests have been created:
- One with a exclusion filter that defines only one exclusion using a wildcard
- One with a exclusion filter that defines several exclusions using wildcards
- One with a exclusion filter that combines exclusions based on wildcards and exclusions not based on wildcards.

All tests passed using Postgres 9.1 and Oracle 11.2. In both RDBMS using '\_' to escape the underscore character in wildcards (i.e. <excludedTable name="SYM\_%"/>) worked properly.

---
M src-test/src/org/openbravo/dbsm/test/model/CheckExcludeFilter.java
A src-test/model/excludeFilter/BASE_MODEL_WITH_SEVERAL_EXCLUDED_FUNCTIONS.xml
A src-test/model/excludeFilter/BASE_MODEL_WITH_SEVERAL_EXCLUDED_SEQUENCES.xml
A src-test/model/excludeFilter/BASE_MODEL_WITH_SEVERAL_EXCLUDED_TABLES.xml
A src-test/model/excludeFilter/BASE_MODEL_WITH_SEVERAL_EXCLUDED_TRIGGERS.xml
A src-test/model/excludeFilter/BASE_MODEL_WITH_SEVERAL_EXCLUDED_VIEWS.xml
A src-test/model/excludeFilter/excludeFunctionWithMultipleWildcards.xml
A src-test/model/excludeFilter/excludeFunctionWithWildcard.xml
A src-test/model/excludeFilter/excludeFunctionWithWildcardsAndNonWildcards.xml
A src-test/model/excludeFilter/excludeSequenceWithMultipleWildcards.xml
A src-test/model/excludeFilter/excludeSequenceWithWildcard.xml
A src-test/model/excludeFilter/excludeSequenceWithWildcardsAndNonWildcards.xml
A src-test/model/excludeFilter/excludeTableWithMultipleWildcards.xml
A src-test/model/excludeFilter/excludeTableWithWildcard.xml
A src-test/model/excludeFilter/excludeTableWithWildcardsAndNonWildcards.xml
A src-test/model/excludeFilter/excludeTriggerWithMultipleWildcards.xml
A src-test/model/excludeFilter/excludeTriggerWithWildcard.xml
A src-test/model/excludeFilter/excludeTriggerWithWildcardsAndNonWildcards.xml
A src-test/model/excludeFilter/excludeViewWithMultipleWildcards.xml
A src-test/model/excludeFilter/excludeViewWithWildcard.xml
A src-test/model/excludeFilter/excludeViewWithWildcardsAndNonWildcards.xml
---
(0083451)
hgbot   
2016-01-20 12:24   
Repository: erp/devel/dbsm-main
Changeset: 4f9d5b36e94ae62602b0eb07429a4ea33cb90ece
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Jan 18 13:07:07 2016 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/4f9d5b36e94ae62602b0eb07429a4ea33cb90ece [^]

Related with issue 31685: Escapes properly the underscore in the exclude filter

---
M src-test/model/excludeFilter/excludeFunctionWithMultipleWildcards.xml
M src-test/model/excludeFilter/excludeFunctionWithWildcardsAndNonWildcards.xml
M src-test/model/excludeFilter/excludeSequenceWithMultipleWildcards.xml
M src-test/model/excludeFilter/excludeSequenceWithWildcardsAndNonWildcards.xml
M src-test/model/excludeFilter/excludeTableWithMultipleWildcards.xml
M src-test/model/excludeFilter/excludeTableWithWildcardsAndNonWildcards.xml
M src-test/model/excludeFilter/excludeTriggerWithMultipleWildcards.xml
M src-test/model/excludeFilter/excludeTriggerWithWildcardsAndNonWildcards.xml
M src-test/model/excludeFilter/excludeViewWithMultipleWildcards.xml
M src-test/model/excludeFilter/excludeViewWithWildcardsAndNonWildcards.xml
---
(0083452)
hgbot   
2016-01-20 12:24   
Repository: erp/devel/dbsm-main
Changeset: a3f42c0c997d75f0abc772cfd150d9d5b9f8bae4
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Jan 18 14:59:09 2016 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/a3f42c0c997d75f0abc772cfd150d9d5b9f8bae4 [^]

Related with issue 31685: Split method in two to avoid having output parameters

---
M src/org/apache/ddlutils/platform/ExcludeFilter.java
---
(0083465)
alostale   
2016-01-20 15:26   
reviewed and tested as part of project http://wiki.openbravo.com/wiki/Projects:Wildcard_Support_In_Exclude_Filter [^]
(0085059)
hudsonbot   
2016-03-17 10:54   
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/b22fb0500156 [^]
Maturity status: Test