Fog Creek Software
Discussion Board




DB Grammar Defs and Software that ALMOST works

We've seen Interface and Database definitions within a database and some agree that there are circumstances in which these are useful, while others believe it is an inexperienced developer trying to prove that he has created something brilliant when indeed he has needlessy complicated his own software.

Let's add Grammar to the above list of meta-data.  In the MS-Access database that is part of the project I am working on, there are the Interface and the Database within a database defintions.  To top it all off there is the ever so wonderful Grammar definition via database tables. aka Biggest-Mess-I-Have-Ever-Seen.

Most of us know that it takes a simple grammar to parse simple expressions (i.e. (x + 5) * 7 ).  There are tools to create such grammars (Lex, Yacc; flex, bison) and, in fact, the code to parse such a grammar is easily implemented without those tools.  So why define such a monster in a database?  I don't know.  What I do know is that I have tables such as OperandA, Operator, OperandB and ConnectOp.  Does it make sense?  Yes, it seems logical and it also ->appears<- to fill its intended purpose.  It's purpose is to define named rates.  This allows the user to select which rate he/she wants to use to determine a price.

The key statement here is that it only appears to fill its intended purpose.  It only appears to work.  It should work, but the fact is that it does'nt.  If it did, I would'nt be working on it.

The problem is that the ConnectOp only works with the '+' operator, although there is code to handle the other operators, the code is wrong and does not work and unfortunately this grammar in a database is connect to the interface and database in a database definitions making it a PITA to debug.

Instead, this Rate definition system should be seperate from the database and the interface.  There should be no dependency on either except to store the rate formula in a field in the appropriate database table.  In other words the database should make provisions to store the rate and the interface should make provisions to display and edit the rate formula.  The rate should not provide itself space in the database and a form on the screen.  The rate should only be able to evaluate itself.

I'll say this about the rate system,  If it would have been properly designed to begin with, it would work.

This whole application that I am working on ALMOST works.  ALMOST does'nt cut it.  Every customer I have spoken with has said 'It just looks like it should work.'  I agree it does LOOK like it should work, but it does'nt.

Which brings up my final point.  I believe that it is immoral to sell an application that does not, by definition, work.  What I mean is, that if it does not accomplish useful work, then I believe you have absolutely no right to sell that application.

Business(wo)men say we need to sell it to keep the business alive.  They say a few bugs won't hurt.  There is a HUGE difference between a few bugs in an application and an application that does not work or accomplish something useful, an appication that on the surface only appears to work.  Convincing a hard-headed business(wo)man of the situation at hand is tough especially when he/she is about to go belly-up.  Imagine for a second if the application had not been sold (only marketed) and that it would have been thoroughly tested to ensure that it accomplished it's tasks.  It sure would save everyone a lot of headaches.

Has anyone else worked on applications that almost work or only "appear" to work?

How did you handle it?

How did you inform your boss of the situation (As if they don't know, but you never know.  I made a bug list and handed it in... I don't think he was too happy. heh)?

Do you believe it's right to sell an application that does not work with the intent to make it work at a later date?

Has anyone ever implemented this grammar in a database with success?

Dave B.
Saturday, February 08, 2003

<snip>
Has anyone else worked on applications that almost work or only "appear" to work?
</snip>
Yep.  Same situation as you described, project handed over by some incompetent.

<snip>
How did you handle it?
</snip>
I convinced management that there were some fundamental architectural flaws (it redefined spaghetti code) and urged them to rewrite/refactor the software.  They already suspected that was the case so it was an easy sell.

<snip>
How did you inform your boss of the situation (As if they don't know, but you never know.  I made a bug list and handed it in... I don't think he was too happy. heh)?
</snip>
I prepared a presentation describing my proposal.  I outlined the benefits of my ideas and contrasted them to the exisiting situation.

<snip>
Do you believe it's right to sell an application that does not work with the intent to make it work at a later date?
</snip>
No.

<snip>
Has anyone ever implemented this grammar in a database with success?
</snip>
We had to implement a grammar into our product.  Basically, you could take a predefined set up inputs from the database and apply any valid mathematical formula to them.  We considered 4 solutions:
1.  Grammar in db
2.  lexx/yacc
3.  Inline parser
4.  Integrate VBA

#1 was nixed pretty quickly.  It was slow, messy and difficult to extend. 

#2 we all liked, since it was the real way to solve this problem but it was going to take a long time to implement our solution.  Plus finding a proper lexer and parser was a little more difficult than we would have liked (licencing issues, cost, etc.). 

#3 was just as good technically, as #2, but time was a factor here (development/testing). 

#4 was the choice we went with.  Basically, it involved building an editor to input the expressions in a friendly way.  It was the quickest way to get this feature to market, but its not the most 'technically' robust solution. 
However, it works and our customers are thrilled with the results.  I guess, that what counts in the end.

Good luck!

Chad R. Millen
Saturday, February 08, 2003

<snip>
Business(wo)men say we need to sell it to keep the business alive.  They say a few bugs won't hurt. 
</snip>

What you described sounds more like fundamental flaws in the app than a "few bugs".  Even your customers are saying that it doesn't work.

<snip>
Has anyone else worked on applications that almost work or only "appear" to work?
</snip>

Oh, too many.  IMO your company is at a crossroads.  Where it goes from here is entirely up to your "hard-headed business(wo)men".  They will either:

A) push the product to market prematurely, thereby irritating customers who thought they were buying a working product, erode any potential profit margin with added support costs, burn out employees by frantically trying to fix bugs, and permanently damage their reputation in the industry,

or,

B) get off their asses and earn their fat salaries by pursuing investment (or other income sources), giving you the time to fix the issues and produce a quality product that will be warmly received and cause your customers to name their firstborn children after you.

So which will it be?  I've never worked for or even heard of a company that didn't choose option A.  Based on what you've said, it doesn't sound like your company will be the exception.

If I were you, I'd make a presentation like Chad suggested above, and if they choose option A, start looking for another job.

Bottom line is that you can't polish a turd.  If your company decides to try, let them find someone else to do it.

Joe Paradise
Saturday, February 08, 2003

Hum, ok as I rub my hands and crack my fingers before I place them on my keyboard. Lets look at this.

>>What I do know is that I have tables such as OperandA, Operator, OperandB and ConnectOp. Does it make sense?

Does that make sense? Hum, sure. If you don’t want to write a general expression parser, then you simply place some combo boxes on a screen.

The first combo box will be a restricted list values/fields from OperandA

2nd combo box will be the operator

3rd combo box will be a restricted list values/fields from Operandb

Presto, the code to process the above is now simple. You don’t need a parser anymore!

Field1 =comboOperandA
Field2 = comboOperandB

StrSql = “select “ & Field1 & “ “ & Operator & & Field2 & _
              “as MyResult from tblRates where RateType = ?? bla bla bal

Anyway, you get the idea. Even the result is not a SQL expression, but some code that grabs the field values and a simple “case” statement for the operand (ie: multiply, or add), then the whole approach seems quite simple.

Thus, a whole rate table can be built as a few fields based on some combo "lookup" boxes to those simple tables. Hum, yea…it seems to make sense on the surface.

I mean, a full expression parser was / is not available, and would in fact be quite difficult to implement anyway. Are we taking general values/variables in the expression? Do the values come from a table? I would think that a full general expression parser would present a very difficult training problem from a GUI point of view. In addtion, how to integrate a general exprssion into the system would be VERY hard.

Having a few combos to select those values from those tables makes a lot of sense.

The only real enemy here is what does the code look like that looks up a rate, and does the calculation? Is it isolated in one spot? Are we dealing with a SQL expression that are created from the values?  Just what/how is the expression to calculate on?

As for the ethics. Hum, I have not comment, but I have never delivered anything that does not work.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Sunday, February 09, 2003

Well Albert, as I stated in my original post, it does appear to work when you first examine the problem and does in fact 'work'. (Or it would work if it were coded correctly).  I cannot explain, in so many words, the entire scope of the problem on this message board.

The first part of the problem comes from the fact that only the '+' operator works in the ConnectOp column.  This is a problem with the code. 

The next problem comes when you try to establish an operator hierarchy due to the absence of parenthesis.  The only way to do this is to have the customer 'rearrange' their equations so they are evaluted correctly.

For example (very simplified):

Let's say that our rate was calculated with this formula:
4 + (3 * 2).

The customer expects to be able to place that very formula in the program, but they can't without 're-working' it.

OperandA Operator OperandB ConnectOp
4 + ? ?
3 * 2 END

So they have to think and re-work the formula:

OperandA Operator OperandB ConnectOp
3 * 2 +
4 END

Again, very simplified case.

The target audience for the application is not 'into'  math, so it would be beneficial to make this as easy as possible.
Now if you or anyone else has any SOLID idea how to include operator hierarchy in this scheme I will hear you out, but the fact is I have already written a parser.

Now, to me, this parser was very easy to write.  Perhaps becuase I have done this before.  I wrote the whole thing in VB 6.0 and it took about a days work.  Granted if you are not familiar with parsers and so on, then this task may seem daunting to you.  To me it was an enjoyable experience.

So now the customer simply enters their equation in a text box and it is stored in the database 'Rate' table as a set of tokens.  It is then executed when called for through the very simple parser/executor. 

This is as opposed to forcing users to manually select operands and operators from combo-boxes to "build" equations.

I guess if the original database method had worked (and worked with operator precedence) I would have left it alone, but it did'nt work and I could not find a reasonable solution for it, so I implemented the one that I knew worked and also the one that I had the know how to do.

I am always open to suggestions though and if anyone can tell me how to implement operator precendence in a Database Grammar, I would be interested.

Dave B.
Sunday, February 09, 2003

Ah, ok, the problem is that the customer does need more then just

A + b
A * b

You need stuff like your examples. That means storing Just two values, and a operand will not really do the trick. So, the person did a stop gap, and allowed a bunch of “sets” of calcs to be string together one after another.

While that was a neat idea, your parser sounds much better.

The one other thing here is the values of “a” and “b” known at run time? Are these field values on a screen, or some values actually typed in?

Since you now have  a parser, then I guess the problem is solved.

You do realize that ms-access has a function called eval().  I would have assumed that it is not being used for some reason?

The expression service in ms-access is quite good, and you can actually include values from a form. Hence if you have  a form called Ftest with:

Field A                FieldCalc: (forms!Ftest!A * forms!Ftest!b)
Field B
Field C
Field D

We could consider the above fields A to D our 4 memories in our calculator

To display the results of Field Calc, we could go:

Msgbox Eval(me.FieldCalc)

The above will show a multiplied by B. We of course really need to cast the data types above to currency. Such as:

FieldCalc: (  ccur(forms!Ftest!A) + ccur(forms!Ftest!b) )

Anyway, I sure you get the idea. You can also use the Eval() function to call functions that are in a variable.
You can also go:

Msgbox Eval( (2 + 3) * 4))

Or, here we ask the user what function to run, and stuff it in a var:

StrWhatFunction = inputbox(…)
=Eval(strWhatFunction)

Will thus call the function name you typed in!

Thus, you can even place your own user defined functions in the text, that also works. In fact, you could make 4 functions that returns the values, and the expression services will process that for you.

You can’t use internal VB variables in the expression service, but you can use controls on forms as your vars as I did above.

Thus, any valid expression that works in a sql or a screen control will work with Eval().

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Monday, February 10, 2003

[SNIP]
Ah, ok, the problem is that the customer does need more then just

A + b
A * b

You need stuff like your examples. That means storing Just two values, and a operand will not really do the trick. So, the person did a stop gap, and allowed a bunch of “sets” of calcs to be string together one after another.
[/SNIP]


It was implemented like you say, "string together a bunch of 'sets'", with the addition of a connection operator between sets.  Unfortunately, due to code there is a fixed order of evaluation of those sets thus the need for the customer to re-arrange their equations.  For example, the code could evaluate your above example several different ways. ( I added the '-' connect op. )

(a + b) - (a * b)

(((a + b) - a) * b)

etc etc.

Of course there are many more ways to write that equation depending on how the customer uses it. One can't possibly code all forms of evaluation though.


[SNIP]
While that was a neat idea, your parser sounds much better.
[/SNIP]


Thank you.  The parser worked out very well indeed.


[SNIP]
The one other thing here is the values of “a” and “b” known at run time? Are these field values on a screen, or some values actually typed in?

Since you now have  a parser, then I guess the problem is solved.
[/SNIP]


Without going into too much detail, certain measurements used in the equation are dynamically calculated by the program according to user input.  The customer much choose which of these 'user defined rates' will be applied to certain classes of input.


[SNIP]
You do realize that ms-access has a function called eval().  I would have assumed that it is not being used for some reason?

The expression service in ms-access is quite good, and you can actually include values from a form. Hence if you have  a form called Ftest with:

Field A                FieldCalc: (forms!Ftest!A * forms!Ftest!b)
Field B
Field C
Field D

We could consider the above fields A to D our 4 memories in our calculator

To display the results of Field Calc, we could go:

Msgbox Eval(me.FieldCalc)

The above will show a multiplied by B. We of course really need to cast the data types above to currency. Such as:

FieldCalc: (  ccur(forms!Ftest!A) + ccur(forms!Ftest!b) )

Anyway, I sure you get the idea. You can also use the Eval() function to call functions that are in a variable.
You can also go:

Msgbox Eval( (2 + 3) * 4))

Or, here we ask the user what function to run, and stuff it in a var:

StrWhatFunction = inputbox(…)
=Eval(strWhatFunction)

Will thus call the function name you typed in!

Thus, you can even place your own user defined functions in the text, that also works. In fact, you could make 4 functions that returns the values, and the expression services will process that for you.

You can’t use internal VB variables in the expression service, but you can use controls on forms as your vars as I did above.

Thus, any valid expression that works in a sql or a screen control will work with Eval().
[/SNIP]


While it is true that VB Scripting Edition and (VBA?) have the function eval(), I don't believe VB 6.0 does.  The application I am working on uses VB 6.0, DAO 3.51 and the MS-Jet engine to work with an 'Access' database.  I apologize for not making it clear that I am not programming in or using Access except to modify the database entities and relationships.

If VB 6.0 did indeed have an eval function, I don't believe that I could use it, as the customer needs the capability of an 'IF THEN ELSE ENDIF' statement, which is implemented in the parser.

Dave B.
Monday, February 10, 2003

*  Recent Topics

*  Fog Creek Home