Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0035497Openbravo ERPY. DBSourceManagerpublic2017-03-13 14:242022-02-01 08:09
Triage Platform Base 
0035497: Support recursive CTEs
Recursive 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
Create 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
* 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 [^]
* 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) ;
  INTO v_treeID
  WITH RECURSIVE organizationtree(levelno, orgId, parentOrgId)
AS (
  select 2, node_id, PARENT_ID
  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;
  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
No tags attached.
Issue History
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.