storing and parsing boolean expressions in databas

问题:

this is a follow-up question for my previous question which design pattern is suitable for this workflow requirement?. I would like to know what is best way to store a boolean expression in database and then evaluate it at runtime without much effort. I have a requirement where a set of jobs need to be executed from the screen every month-end and the decision of whether a job can be executed depends on the outcome of previous jobs. so I should be able to save an expression in database something like E = (A or B) and (C or D) (could be any complex nested expression). which means E can run if A or B is successful and C or D is successful. how can this be represented in database and parsed and condition be evaluated later?

thinking of doing it like this (not tested yet)
if "Job E = (A and B) or (C and D)". store it as a tree structure in the table

JobId DependentJobId SuccessJobId FailureJobId
E        A             B             C              
E        C             D             0
E        D             1             0
E        B             1             0

this means "if A is successful, check B else check C", "if C is successful check D". process starts from highest node which no "DependentJobId" and stops at leaf node. I use dummy Job Ids "1" for success and "0" for failure. if the leaf node is zero, then job is allowed to run. not sure how best I can evaluate this in T-sql. I may have to use a recursive function.

Technology to be used: ASP.NET 3.5, C# 3.0, SQL Server 2008

回答1:

You can store expressions as strings in the database and then parse them at runtime using either a set of custom functions, or an existing engine. To achieve this I have used the below in previous projects:

FLEE (Fast Lightweight Expression Evaluator)

Dynamic Linq

I would say Dynamic Linq is the easier to go for as you can essentially construct a where clause using familiar Linq / C# syntax and store as a string in the database. I have used FLEE in projects where I wanted to construct my own simple query language however a lot of these I could probably now acheive by using Dynamic Linq.

回答2:

It appears to me you are trying to write a business rule in a table. I believe writing the code to populate the table would be better spent writing the rule in a stored procedure. The stored procedure could select run status, run date, etc of jobs from MSDB. If there are conditions which are not SQL Agent jobs the results could be stored in a table containing the job name, job status and job date. The stored proc could use simple variable assignment to retrieve all the values then process the business logic statement using TSQL Bitwise operators.

& (Bitwise AND) (Transact-SQL) 
~ (Bitwise NOT) (Transact-SQL) 
| (Bitwise OR) (Transact-SQL) 
^ (Bitwise Exclusive OR) (Transact-SQL)

Your example of E=(A and B) or (C and D) would become

Select @E=(@A & @B) | (@C & @D)

Try the code below and see if it will work in you business rule.

declare @E bit
declare @Bit1 bit
declare @Bit2 bit
declare @Bit3 bit
declare @Bit4 bit
declare @Bit5 bit
SET @Bit1 = 1
SET @Bit2 = 0
SET @Bit3 = 1
SET @Bit4 = 'fALSE'
SET @Bit5 = 0

SELECT @Bit1, @Bit2, @Bit3, @Bit4, @Bit5

SELECT @Bit1 & @Bit2


 Select @E=(@Bit1 & @Bit2) | (@Bit3 & @Bit4)
 Select @E as [E value]

回答3:

Both Macros and RC_Cleland have great answers. The only thing I would add is the possibility of leveraging Windows Workflow Foundation to write a workflow for the processing requirements.

回答4:

I solved it using the approach I mentioned in the question. though it is not good to put the business rules in a table, I needed a quick but maintainable solution for this, so I chose this. if I store the job dependencies in a table, I can make changes to it easily without changing the code which is useful once it goes to production. I have written a recursive function to walk through the job dependency graph and determine the result. thanks for all your replies.