0047817: Not able to use a database immutable function in a table index

diff --git a/src-db/database/model/functions/C_LOCATION_GETIDENTIFIER.xml b/src-db/database/model/functions/C_LOCATION_GETIDENTIFIER.xml new file mode 100644 index 0000000000..30d499673a --- /dev/null +++ b/src-db/database/model/functions/C_LOCATION_GETIDENTIFIER.xml @@ -0,0 +1,50 @@ +<?xml version="1.0"?> + <database name="FUNCTION C_LOCATION_GETIDENTIFIER"> + <function name="C_LOCATION_GETIDENTIFIER" type="VARCHAR" volatility="IMMUTABLE"> + <parameter name="p_address1" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_address2" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_postal" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_city" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_region" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_country" type="VARCHAR" mode="in"> + <default/> + </parameter> + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2021 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ +/************************************************************************* +* Title: Return the identifier of the location concatenating the main columns: +* address1, address2, postal, city, c_region_id and c_country_id +* +* This function is immutable as it only depends on defined parameters +* instead of stored data. +************************************************************************/ +BEGIN + RETURN COALESCE(p_address1, '') || ' - ' || COALESCE(p_address2, '') || ' - ' || COALESCE(p_postal, '') || ' - ' || COALESCE(p_city, '') || ' - ' || COALESCE(p_region, '') || ' - ' || COALESCE(p_country, ''); +END C_LOCATION_GETIDENTIFIER +]]></body> + </function> + </database> diff --git a/src-db/database/model/tables/C_LOCATION.xml b/src-db/database/model/tables/C_LOCATION.xml index e7c99d2792..00a8e116c1 100644 --- a/src-db/database/model/tables/C_LOCATION.xml +++ b/src-db/database/model/tables/C_LOCATION.xml @@ -84,6 +84,9 @@ <foreign-key foreignTable="C_REGION" name="C_REGION_LOCATION"> <reference local="C_REGION_ID" foreign="C_REGION_ID"/> </foreign-key> + <index name="C_LOCATION_IDENTIFIER" unique="false"> + <index-column name="functionBasedColumn" functionExpression="C_LOCATION_GETIDENTIFIER(ADDRESS1,ADDRESS2,POSTAL,CITY,C_REGION_ID,C_COUNTRY_ID)"/> + </index> <check name="C_LOCATION_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> </table> </database>

Notes
(0132338) hgbot (developer) 2021-10-14 09:52	Merge Request created: https://gitlab.com/openbravo/product/dbsm/-/merge_requests/32 [^]

(0132557) hgbot (developer) 2021-10-22 10:00	Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/443 [^]

(0132581) hgbot (developer) 2021-10-22 11:42	Directly closing issue as related merge request is already approved. Repository: https://gitlab.com/openbravo/product/openbravo [^] Changeset: bb422b20c7f51c400c70fbc6fff0629c2045fef4 Author: Augusto Mauch <augusto.mauch@openbravo.com> Date: 2021-10-22T09:53:11+02:00 URL: https://gitlab.com/openbravo/product/openbravo/-/commit/bb422b20c7f51c400c70fbc6fff0629c2045fef4 [^] Fixes ISSUE-47817: Cannot use new immutable function in function index Now the immutable functions are created before the tables and its indexes. This way it is possible to define an immutable function, export it to a module and then reference it from a table index. --- M src-db/database/lib/dbsourcemanager.jar ---

(0132582) hgbot (developer) 2021-10-22 11:42	Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/443 [^]

(0132585) hgbot (developer) 2021-10-22 12:29	Directly closing issue as related merge request is already approved. Repository: https://gitlab.com/openbravo/product/dbsm [^] Changeset: c2c0cfafbfca0d1c651abbe20b622ea4f3f1a52a Author: Augusto Mauch <augusto.mauch@openbravo.com> Date: 2021-10-22T09:56:41+02:00 URL: https://gitlab.com/openbravo/product/dbsm/-/commit/c2c0cfafbfca0d1c651abbe20b622ea4f3f1a52a [^] Fixes ISSUE-47817: Cannot use new immutable function in function index Now immutable functions are created just before updating the windows, so if an index reference one of the new immutable functions, they will be already available --- A src-test/model/indexes/FUNCTION_INDEX_WITH_OB_DEFINED_FUNCTION.xml A src/org/apache/ddlutils/alteration/AddImmutableFunctionChange.java A src/org/apache/ddlutils/alteration/AddMutableFunctionChange.java M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java M src/org/apache/ddlutils/alteration/AddFunctionChange.java M src/org/apache/ddlutils/alteration/ModelComparator.java M src/org/apache/ddlutils/platform/SqlBuilder.java ---

(0132586) hgbot (developer) 2021-10-22 12:29	Merge request merged: https://gitlab.com/openbravo/product/dbsm/-/merge_requests/32 [^]

Issue History
Date Modified	Username	Field	Change
2021-10-06 09:54	aferraz	New Issue
2021-10-06 09:54	aferraz	Assigned To	=> platform
2021-10-06 09:54	aferraz	OBNetwork customer	=> No
2021-10-06 09:54	aferraz	Modules	=> Core
2021-10-06 09:54	aferraz	Triggers an Emergency Pack	=> No
2021-10-06 09:57	aferraz	File Added: Diff.diff
2021-10-06 09:59	aferraz	Summary	Not able to use a database function defined by the user in a table index => Not able to use a database immutable function in a table index
2021-10-06 09:59	aferraz	Description Updated	View Revisions
2021-10-14 09:52	hgbot	Merge Request Status	=> open
2021-10-14 09:52	hgbot	Note Added: 0132338
2021-10-19 16:16	hgbot	Merge Request Status	open => approved
2021-10-22 10:00	hgbot	Note Added: 0132557
2021-10-22 11:42	hgbot	Resolution	open => fixed
2021-10-22 11:42	hgbot	Status	new => closed
2021-10-22 11:42	hgbot	Fixed in Version	=> PR22Q1
2021-10-22 11:42	hgbot	Note Added: 0132581
2021-10-22 11:42	hgbot	Note Added: 0132582
2021-10-22 12:29	hgbot	Note Added: 0132585
2021-10-22 12:29	hgbot	Note Added: 0132586
2022-01-13 11:15	plujan	Assigned To	platform => AugustoMauch

Relationships [ Relation Graph ] [ Dependency Graph ]