Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0035497
TypeCategorySeverityReproducibilityDate SubmittedLast Update
feature request[Openbravo ERP] Y. DBSourceManagerminoralways2017-03-13 14:242017-03-13 14:24
ReportervmromanosView Statuspublic 
Assigned Toplatform 
PrioritynormalResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035497: 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
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
There are no notes attached to this issue.

- Issue History
Date Modified Username Field Change
2017-03-13 14:24 vmromanos New Issue
2017-03-13 14:24 vmromanos Assigned To => platform
2017-03-13 14:24 vmromanos Modules => Core
2017-03-13 14:24 vmromanos Triggers an Emergency Pack => No


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker