[teiid-issues] [JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13726592#comment-13726592 ] 

Steven Hawkins commented on TEIID-5725:
---------------------------------------

> For my understanding of the description a virtual procedure with side effects should always be mapped to an action.

Our notion of updating model count is unfortunately not the same as side-effect free.  The value we compute is to determine whether a statement level transaction is required.

0 = no statement level transaction needed
1 = no statement level transaction needed if this is the only thing being called.  Otherwise if it's nested in a procedure or there are other statements that may also need a transaction, then a statement level one will be started.
2 = always start a user level transaction

By the way we currently compute the value the usage of the atomic block means that we don't have to consider a higher level transaction as required for those statements - we'll create a block level transaction for that.

So to have the same semantics as OData is looking for we would need a secondary check.

Another thing to consider is if you even need an atomic block.  Are you worried about a competing update to NutritionGoal in between the select and update? 

> I do not understand than why there is a virtual function and virtual procedure specification. I would have expected to get a function for a virtual function and a action for a virtual procedure.

It is not necessarily intended that virtual functions are represented in OData metadata at all.  The only reason they are there is because when they are defined by a DDL procedure body there is procedure in our system metadata.  When the OData metadata is built it is looking at procedures only - note that source functions, Teiid system functions, etc. are not represented in the OData metadata.  I would like this to be more consistent than it is.  In general the mapping of function to function, and procedure to action is simpler - except not all Teiid functions qualify as OData functions as they can have lob parameters and side-effects.  Ramesh would have to comment more on this as he did the initial metadata mapping - [~rareddy] did you ever intend to have all Teiid functions represented in OData?  Perhaps as composible?

> Maybe you could also add the options to an example in the documentation to make it transparent? Would this than be OPTIONS(UPDATECOUNT '1');

Yes that is the workaround in this case if you want it as an action.  More than likely though we'd want the semantics of "side effect free" to be represented rather than documenting this as a corner case.  Can you log an issue about this?

> By the way, if you see how I could write this function more efficiently, I would be grateful for a comment 

You could combine both of the selects, and you may find it easier to read using a loop rather than a lot of scalar subqueries:

CREATE VIRTUAL PROCEDURE copyNutritionGoalsOfWeekdayToEntireWeek(vWeekday VARCHAR(1))
RETURNS BOOLEAN AS
BEGIN ATOMIC
IF ( vWeekday NOT IN ('0', '1', '2', '3', '4', '5', '6') )
RETURN FALSE;

--##### read the relevant parameters of the selected workday into a temporary table #####
SELECT 
fkProfile, BMR_Formula, BMR_Value, ActivityLevel, CaloriesBurned, WeekGoal, GoalCaloriesDelta, 
CarbsGoalInPercent, ProteinsGoalInPercent, FatGoalInPercent 
INTO 
#tmpTable 
FROM 
NutritionGoal 
WHERE 
fkProfile = (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36)) AND Weekday = vWeekday;

IF (ROWCOUNT = 0)
RETURN FALSE;

--##### update the other weekdays #####

LOOP ON (SELECT * FROM #tmpTable) as cur
BEGIN
UPDATE 
NutritionGoal 
SET 
NutritionGoal.BMR_Formula = cur.BMR_Formula,
NutritionGoal.BMR_Value = cur.BMR_Value,
NutritionGoal.ActivityLevel	= cur.ActivityLevel, 
NutritionGoal.CaloriesBurned	= cur.CaloriesBurned, 
NutritionGoal.WeekGoal	= cur.WeekGoal, 
NutritionGoal.GoalCaloriesDelta	= cur.GoalCaloriesDelta, 
NutritionGoal.CarbsGoalInPercent	= cur.CarbsGoalInPercent, 
NutritionGoal.ProteinsGoalInPercent	= cur.ProteinsGoalInPercent, 
NutritionGoal.FatGoalInPercent	= cur.FatGoalInPercent 
WHERE 
NutritionGoal.fkProfile = cur.vfkProfile AND NutritionGoal.Weekday != vWeekday;
END

RETURN TRUE;
END



> Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5725
>                 URL: https://issues.jboss.org/browse/TEIID-5725
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Documentation
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Critical
>
> I refer to the discussion with Ramesh in 
> https://developer.jboss.org/message/989048#989048 and would like to ask for a more detailed explanation on how to write virtual procedures and functions. 
> -Especially, how are OUT and INOUT params set and how can they be obtained via odata. 
> - How is the return value of a virtual function set?
> - How are collections and arrays of collections returned?



--
This message was sent by Atlassian Jira
(v7.12.1#712002)