2

We have an application where the user is allowed to enter expressions for performing calculations on the fields of a database table. The calculations allows various types of functions (math, logic, string, date etc). For e.g MAX(col1, col2, col3). Note that these expressions can get complex by having nested functions. For e.g. IF(LENGTH(StringColumn)=0, MAX(col1, col2, 32), MIN(col1, col2, col3)) > LENGTH(col2)

One way we have implemented this is having a java cc parser to parse the user entered expressions and then generating a tree type of data structure. The tree is then parsed in java and sql queries are generated for each of the functions used in the expressions. Finally after the queries are generated for each of the user entered expression, java executes this query using simple database call.

A major problem with this framework is that the database issues are to be handled in java. By database issues I mean some database limitation or any performance optimization. One database limitation with Microsoft SQL Server is that only 10 nested CASE WHEN statements are allowed. This means that while parsing the java code needs to estimate how many CASE WHEN's would the query string have before it is translated. Similarly if there are any sql performance optimizations to be done, handling them in java simply not logical.

Does anyone know about any better design approaches for this problem?

Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113

5 Answers5

1

Rather than reimplement a very SQL-like language that gets translated to SQL, have your users query the database with SQL.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • 1
    This is a functionality provided by the software. Software users are not technical enough to know SQL language. They are being provided of business functions like I mention above. For e.g. math function like MAX(column1,column2). These functions then need to be translated to sql queries which then performs the expected calculations. – Andy Dufresne Dec 10 '10 at 09:31
  • Your language is more complex than SQL! You rarely see even a developer write something like `IF(LENGTH(StringColumn)=0, MAX(col1, col2, 32), MIN(col1, col2, col3)) > LENGTH(col2)`, yet your "business users" are writing that. If they can write that, they can write SQL! And they don't even have to learn a proprietary language; they get the benefit of decades of documentation, examples and tools. I hope that you at least lobbied against creation of this system when the application was speced. – Dan Grossman Dec 10 '10 at 09:34
  • I was not part of the function team who designs the application specifications. Given that the functionality needs to be provided as I mentioned, do you think there is any better way of implementing this functionality. – Andy Dufresne Dec 10 '10 at 09:42
1

I would look into Hibernate and it's HQL query language.

In response to the poster above, I think it would be a bad idea to let your users query the database with SQL directly, as you'd be opening yourself up to SQL injection attacks.

MattDavey
  • 8,897
  • 3
  • 31
  • 54
  • These are not hibernate mapped objects. The functionality provided by the application is to allow users perform calculations on the data present in the table. As I mentioned, calculations mean business functions. The user can enter expressions which are then parsed by the software to perform the expected calculations. The output of this process is another data table which includes calculated data. – Andy Dufresne Dec 10 '10 at 09:29
  • You can allow direct SQL access without opening yourself to injection. That's what several layers of the database model are dedicated to -- access control, external views. You create read-only views, or don't give the user they connect as permission to modify things they shouldn't modify. – Dan Grossman Dec 10 '10 at 09:45
0

Some time ago i wrote a java applet with dynamic filter routines and there i translate the sql statements to javascript statements and execute them with javascripts exec function

wickie79
  • 470
  • 1
  • 3
  • 7
  • What do you mean by "execute with javascripts exec function"? The queries need to run against a database. – sleske Dec 10 '10 at 09:21
0

You could have a look at JPA 2.0 Criteria API or Hibernate Criteria API

  • JPA 2.0 provides the so called Criteria API (http://stackoverflow.com/questions/2602757/creating-queries-using-criteria-api-jpa-2-0)
  • Hibernate has its own Criteria API (even before JPA 2.0) - but it is different from JPA 2.0 Criteria API. (http://www.ibm.com/developerworks/java/library/j-typesafejpa/)

The aim of both Criteria APIs is to provide a way to create sql queries at runtime in a more pleasant way then concatenating strings. (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html)

(JPA 2.0 Critiera API has a extra feature, it provides some kind of code generation, that makes it possible to write queries in a compile time save way. (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html))

Ralph
  • 118,862
  • 56
  • 287
  • 383
  • The calculations are to be performed on a database table which is not a hibernate mapped object. Hence this possibility would not be valid right? – Andy Dufresne Dec 10 '10 at 09:36
0

Another approach which I could think was to look for language recognizers supported by database (which is Oracle in my case). Similar to what we currently use in java (i.e. javacc) if a similar framework is supported by the database then the intermediate string could be parsed and translated into a sql query. The intermediate string I refer here is similar to the user entered string but may be exactly the same (e.g. column names could be transformed to actual physical column names).

Any thoughts (pros and cons) about this approach? Also any suggestions on language recognizers in Oracle would be highly appreciated.

Thank you.

Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113