software to parse and evaluate user defined fields and formulas

[ttuareg]

Hi everybody,

This is my first post here, i'm not sure if this is the right forum for this kind of problem, please excuse my english if I make mistakes.

I'm trying to make the design for a software module to perform calculations based on parsing user defined formulas, in a model with user defined fields, quite similar to a excel sheet with only one column. This module will be used in a multiuser, client-server application, with web-based interface. Database = Oracle 10g.

I'll write a simplified description of my model, I need your advice if I'm making it right, or if you can give me some documentaion, or links to similar problems.

Each record in the database will have attached a set of calculations fields, as in the model below.

+--------------------------------------------------------.....----+
|Main record fields - they are not used in the formulas ..........|
+--------------------------------------------------------.....----+
  
// the set of fields attached to the record
+--------+---------------------+
  I1     |            ________ | //user input 
+--------+---------------------+ //fields 
  I2     |            ________ | //(up to 20-30 fields)
+--------+---------------------+
  I3     |            ________ |
+--------+---------------------+
  I4     |            ________ |
+--------+---------------------+
  C1     | = I1 + I2           | //auto-calculated 
+--------+---------------------+ //fields 
  C2     | = C1 + (I3/I4)      | //(up to 30-40 fields)
+--------+---------------------+
  C3     | = C1 - C2           |
+--------+---------------------+
  C4     | = If(C3<0,I1-I3,C3) |
+--------+---------------------+
//calculated fields will refer only to fields 
//in the model, not in the main record

The Application Administrator must be able to define his own set fields, and into some of them to define custom formulas.

When user creates new record, he will fill the editable fields I1..I4 and then submit the record to the database, and on the server-side the software will parse the model and calculate the fields with formulas (C1..C4, according to the model), and then store the whole set of fields in the database.


My problems are:
1. I cannot decide on the way to store the user-model-definition in the database, having in mind the speed of parsing the records, and also maintain the data integrity which is very important (the software will be used in a bank system, and accessed simoultaneously by maximum 4-500 users)
2. a solution for a fast parser to evaluate user-defined formulas.


One solution was to write the whole model in a XML structure, stored in a single varchar field.
Another solution was to store the user-defined fields in separate records in the database.

Considering to reduce the amount of read-write operations to the database, I think that would be better to use a XML structure stored as a string in a single varchar field, and to develop a parser to load the XML structure into an array, and then iterate through fields and make calculations according to the formulas.


Any ideas or suggestions (or links to some documentation) would be much appreciated,

Thanx in advance.

 

[BobRodes]

What I'm seeing here is basically an object, with some properties, and then a "properties" property that is an array of properties. Some are read only, some are writeable, and this group is definable.

Now, I would just implement this in a database. I would first make it WORK, then make it FAST. To make it work, I would make 2 tables. One would be the "Main record fields" with a key, and the other would be your "user-defined fields" which would be children of the main record table.

In general, the more restrictive the "language" used in parsing, the faster it will run. You can look at things like regular expressions, and brute force parsing.

HTH

Bob

 

[ttuareg]

Thank you for answering, BobRodes.

What I need could be defined as a custom calculation engine based on a spreadsheet template; after a few days of "googling", I realise that I must not re-invent the wheel, and there must be some dedicated engines/parsers for this.

I found that an excel spreadsheet could be a very good solution for parsing user defined business rules; do you think it's a reliable in a multiuser application ? I mean, user submit the data from a web interface, data arrives on the server and it's filled in a spreadsheet which will make all the calculations, then the results will be retrieved from the spreadsheet to be stored in the database.

I'm interested in a calculation engine, based on a xml definition of business rules, what should I look for ?


If I want to store in the database the data definition, after all all the calculated fields were updated, which way is better: to store them as a XML string ? or as a set of "many-to-one" related records to the main record ?

 

[adrstan1]

You can existing parsers to generate code according to a grammar describing your expression processing.
Take a look at: yacc, bison, lemon, boost spirit