Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035497Openbravo ERPY. DBSourceManagerpublic2017-03-13 14:242022-02-01 08:09
Reportervmromanos 
Assigned ToTriage Platform Base 
PrioritynormalSeverityminorReproducibilityalways
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035497: Support recursive CTEs
DescriptionRecursive CTEs work fine in Oracle and PostgreSQL supported by Openbravo versions, however the DBSM is unable to properly work with them.

In PostgreSQL the syntaxis is: WITH RECURSIVE …
In Oracle the syntaxis is: WITH …


The DBSM should export to XML the "WITH RECURSIVE" always, but depending on the database, the way to apply it should be different:
PostgreSQL should have the same syntaxis as in the XML
Oracle should remove the keyword "RECURSIVE" and add it as a note somewhere so it can be properly understood by the DBSM when exporting the database
Steps To ReproduceCreate a function like this one in PostgreSQL:

CREATE OR REPLACE FUNCTION ad_isorgincluded_cte(
    p_orgid character varying,
    p_parentorgid character varying,
    p_clientid character varying)
  RETURNS numeric AS
$BODY$ DECLARE
/*************************************************************************
* 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) 2001-2006 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Return 'Org Level' if orgID is a leaf of the org tree
* where parentOrgID is the root. Else return -1
************************************************************************/
  v_Level NUMERIC:=0;
  v_treeID VARCHAR(32) ; --OBTG:varchar2--
  v_Parent VARCHAR(32) ;
  v_Node VARCHAR(32) ;
BEGIN
  SELECT MAX(AD_TREE_ORG_ID)
  INTO v_treeID
  FROM AD_CLIENTINFO
  WHERE AD_CLIENT_ID=p_clientID;
  v_Parent:=p_OrgID;
  
  WITH RECURSIVE organizationtree(levelno, orgId, parentOrgId)
AS (
  select 2, node_id, PARENT_ID
  from AD_TREENODE
  where AD_TREE_ID=v_treeID
  and node_id = p_orgid
  union all
  select levelno+1, tn.NODE_ID, tn.PARENT_ID
  from organizationtree ot
  join ad_treenode tn on (ot.parentOrgId = tn.NODE_ID
  and tn.AD_TREE_ID=v_treeID
  and tn.PARENT_ID NOT IN ('0', v_Parent))
)
select ot.levelno
into v_Level
from organizationtree ot
where ot.parentOrgId = p_parentorgid;

return v_Level;
END ; $BODY$
  LANGUAGE plpgsql STABLE
  COST 100;

Execute it and verify it works fine. Example:
select *, ad_isorgincluded(ad_org_id, '0', '23C59575B9CF467C9620760EB255B389') from ad_org

Export the database, verify it is properly exported

Apply the diff to an Oracle instance and update the database.
Verify it seems to be properly updated.
Try to execute the function and verify an error is raised. Oracle doesn't understand the RECURSIVE keyword. If you manually edit the function accordingly it will work fine in Oracle
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-03-13 14:24vmromanosNew Issue
2017-03-13 14:24vmromanosAssigned To => platform
2017-03-13 14:24vmromanosModules => Core
2017-03-13 14:24vmromanosTriggers an Emergency Pack => No
2022-02-01 08:09alostaleAssigned Toplatform => Triage Platform Base

There are no notes attached to this issue.