Best Schema to Persist User Generated Functions?

Question

My project requires users to create some basic math functions, I need to store these in a ms sql databse to use on datasets in my business logic.

The functions will be similar to: when (value1 between 20 and 40) then value2 = value 2 * 37.8

What is the best schema to store something like this ?

And what is the most efficient way to translate the persisted function back so I can apply it to datasets ?

I'm using c# and ms sql 2008

Answer 1

Store them as user defined functions

Nice article on them here

Answer 2

This is a tough one. Basically you want to create a parser.

I believe that you would need to have two tables : first table would have a functionID ,orderID , operatorID

The second table would have 1 to many relationship between operatorID and operands.

FunctionID would be unique identifier for the function. orderID would determine in which order we need to parse the various rows for the same functionID. operands would be the actual variables that need to be apllied with the operator.

For ex :

Table 1 

FuncID         OrderID         OperatorID
1              1               1 (for *)
1              2               2 (for +)

Table 2

OperatorID       Operands
1                a
1                b
2                c

After parsing this would become f(x) = a*b + c

this is a very basic idea. There are a lot of gotchas. Hope that helps.

Answer 3

If reflection is available, read a bunch of help on Emit and the various forms of Load to see if you can parse the user's function and produce a .Net executable in a form that you can store in the database.

Be really careful about security and injection though.

Sorry, can't be any more helpful than pointing you in a possibly-useful direction.