Skip to content
Advertisement

Expression language to SQL where clause transformations?

We have one domain object,

Person
    firstName
    lastName
    role

I’m looking for a simple expression language that we can write that will generate SQL. (particulary the where clause of SQL)

For example.

firstName contains "joe"

translates to

FIRST_NAME LIKE '%joe%'

Does anything like this exist, or do I have to write my own?

Please let me know if you think I might as well be searching for the fountain of youth, or if you think i’m wasting my time writing this!

Advertisement

Answer

“However, there is also a large class of users who, while they are not computer specialists, would be willing to learn to interact with a computer in a reasonably high-level, non-procedural query language. Examples of such users are accountants, engineers, architects, and urban planners.”

That’s from Donald D. Chamberlin and Raymond F. Boyce’s 1974 paper introducing SQL (then SEQUEL). From their introduction, I’d guess they designed SQL for your non-technical users.

That’s not to say SQL is a perfect language. Despite thirty-some years of changes, it still has flaws. There’s a chance you could build something better, but two things could go wrong:

  1. Your language could be worse.
  2. If one of your non-technical users wanted to beef up their relational data skills, they would be limited by your language versus SQL. There are many, many resources to improve SQL skills and far fewer ways to improve skills in a new language.

Maybe consider a compromise: offer an expression-building UI. Less technical users could choose a property, an operator, and enter values which would then appear in an expression text area. They could build moderately complex SQL expressions piece-by-piece without needing to remember SQL syntax. More sophisticated users could edit the SQL directly.

This doesn’t solve all of your problems. You’ll still need a way to limit SQL so users don’t damage your database or inadvertently use up all of your db resources with a mind-boggling Cartesian join.

Advertisement