Sharing rules between javascript and SQL

Question

I'm mostly looking for ideas here. The odds of my group purchasing something to solve the problem is incredibly low, but go ahead and suggest any commercial products that would help.

The basic problem stems from the following scenario:

1. Users input data into a form, which auto-populates some values based on existing data.
2. This data is saved and everything is great.
3. A report is generated and it is in sync with the data that was saved.
4. The database content which determines the auto-populated values now changes.
5. Opening the form, the auto-populated values are modified.
6. Without saving, the report values need to be updated as well, but they are not.

Other details:

• Our reporting solution requires that the answers and rule evaluations all come from stored procedures or queries in the Oracle database.
• The form is run in a browser.
• Based on the auto-populated values, some parts of the form/report will be visible or invisible.

The Question: How can I implement a set of rules (stored in the database) in a way that I can easily evaluate them from both javascript and SQL?

A rule may look like any of the following:

• If Question 1 is answered with a "Yes", hide questions 2 - 10.
• If Question 3 is answered with a "No", automatically answer "X" to Question 4.
• If Question 1 is "Yes" and Question 3 is "No" and Question 4 is "Yes", then run method "whatever".

I have many possible solutions running through my mind right now, but most of them involve writing two interpreters (one in javascript and one in sql). While this isn't necessarily horrible, dealing with only one interpreter would be best.

Also, the client needs to update the form while the user is entering values, so popping back to the database every time the user changes an answer is not likely to be a practical solution.

Update / Modification

I'm currently leaning toward implementing rules that can be evaluated directly by the javascript (with a bit of code surrounding the rule), and converted/processed by a store procedure into dynamic sql that can evaluate the rules in Oracle.

Any suggestions for that?

Answer 1

Polling the database every time the user makes a change is not as impractical as you think, unless you are on a very slow network. If Google instant can execute a web search every time you type a character, I think you can manage to do an ajax call every time a user answers a question.

Answer 2

This might be a good use-case for http://nodejs.org/ . With Server-side Javascript, you only have to develop the API in JS once. You might need to extend it a little bit on the server or client, but it would at least take away part of your burden.

Answer 3

Use JSON.

Define your rules in a JSON file - it might look something like this:

[
    {
        name: "my_first_rule",
        validation: {
            common: ["required", "[0-9]+"]
            // If you had rules that needed different implementations in .NET
            // vs. Javascript you could set those up as separate attributes here
            // i.e. js: ["some_complex_regex_or_fuction_call"],
            //      net: ["which_needs_to_be_different_in_.NET"]
        },
    {
        name: "my_second_rule",
        validation: {
            common: ["required", "\w+"]
        }
    }
]

Parse those rules and use them to drive both the front-end javascript and the back end .aspx files (or stored procedures - whichever makes more sense with your setup.)

If you want to avoid not only writing two sets of rules but also two sets of rule-interpreters then going with Node.js or else making calls to an embedded JScript interpreter (or stand-alone Rhino / Spidermonkey environment) is probably the way to go (Switching to Silverlight on the client-side and writing all of your validation in .NET might be the other way to go -- if you have users on 56K modems however, they might not thank you for that choice.)

Answer 4

I think you answered your own question in the comment:

I understand that some of our users are on the old 56kbps modems in foreign countries

If you want responsive and speed for these users, you have to take the upfront slowness of loading your logic into a JavaScript file and letting the client handle it. I would stay away from AJAX, because going back to the server a lot with a 56kbps modem would not be fun.

Answer 5

Well, I ended up writing some simple string replacement "parsers" using a syntax something like this:

Condition: '{Question = "1" and Answer = "Y"} and {Question = "2" and Answer = "N"}',
Action: 'Hide',
Target: '3'

In the database the Condition, Action, and Target fields are columns within a row containing a bit more useful information. In javascript, it will be a JSON object.

Doing some simple REGEXP_REPLACE in PL/SQL and Repace(/.../g) in javascript, I can get to where we need to be -- without wasting much time on parsers. (The javascript objects being tested will have properties that match the properties in the condition, and the database rows being tested will have the same column names.)

It turns out that we do end up with multiple interpreters, but keeping them simple via string replacement, this isn't much of a pain.