Is there any framework for parsing a SQL-like query into its component parts?

Question

I'm interested in writing a SQL-like query syntax for a CMS I work with. The idea would be that a CMS query could be written in a SQL-ish syntax, and I would convert that to execute through the CMS API.

There would be no field or table selection, so I need some way to get from this:

SELECT WHERE Something = 'something' AND (SomethingElse != 'something' OR AnotherThing == 'something')

Essentially then, I need some way to get the WHERE clauses grouped correctly based on their parentheticals and AND/ORs.

Is there some framework for doing this? Some example of when it's been done? I don't want to re-invent the wheel here, and I know someone else has to have done this in the past.

Answer 1

The answer is yes, there are many frameworks that work in an analog of SQL and convert to SQL. Linq and various Linq translators are a prime example. Knowing exactly which CMS you're working with, and thus which language and platform you're developing in, would be helpful. Some .NET ORMs that support code queries are:

NHibernate - allows use of a SQL-ish language called HQL in strings, or more code-based query construction using expression lists and Linq.

Linq2SQL - On its way out, but for your simpler applications it should be fine. The framework generates DAO classes that map between tables and your domain objects, and you can use coded Linq queries to work with the classes very much like the real tables.

And of course you can use good ol' vanilla ADO.NET with a string SQL query. This has numerous drawbacks, but if you want to have queries in your code, why not make them real SQL? If you wanted to hide your table structure, you could translate table names before submitting queries, so the SQL contained at the web layer (shudder) won't run against your DB.