What's the easiest way to expose a SQL interface to my application?

Question

I'm working on an application which stores data in tables, similar to an RDBMS. I'm looking for a way to let my users query this data using SQL. Ideally, I'd like to do this without having to implement my own SQL parser, query optimizer, etc. So far, ripping parts out of something like Apache Derby is looking like the best option, but I'm wondering if there's an easier way.

I think I need:

• A SQL parser (JavaCC?)
• A query optimizer
• A way to execute the optimized queries

Alternatively, does anyone know any open source databases where I can replace the data store with my own files/data format?

One last note, I don't really need transactions, primary keys, foreign keys, constraints, etc. Thanks!

Clarifications

• This won't be exposed via the web. It will only ever be used by trained, trusted users (who have many other, easier, ways at their disposal to destroy the data in question)
• The optimization stage is fairly important, as we're talking potentially hundreds of gigabytes of data in the various tables
• This is intended to be an extension to an existing system. I can't change the data format, and there's no option to throw away the existing system and replace it with a traditional RDBMS.
• Java-based solutions are preferred :)

Thanks for the responses, guys!

Answer 1

I'm pretty sure that HSQLDB will allow you to do what you want and will also allow you to store data in flat files.

You can also use SQLite, the downside to this is that you there aren't (at least the last time I checked) any pure Java JDBC drivers. This is the SQLite format though.

Both Apache Derby and HSQLDB are pretty lightweight and you shouldn't need to strip them down.

You don't need to write a parser BTW - all you need to do is to capture SQL from the users and execute it.

The optimizer will be the tricky part but this depends on how big you think the tables are going to get - if they are small enough and the machine fast enough, you may not need to bother.

If you are going to expose this via the Web in any form - you may want to think again. What you are proposing is a hackers dream.

Answer 2

The obvious (and easiest) way is to store your data in a database. It's a little surprising to hear that you need to implement your own database, that there's no existing database that's suitable.

Answer 3

You mean your 'tables' aren't really SQL tables? And that you wish to implement a SQL interface to what is not actually a DMBS but your own clone of one?

I think you better use an existing DBMS like Derby and let the users query it directly.

You can offer some query filtering if data damage is what you are concerned about.

All in all, this doesn't look like a wise decision to make.

That said, you can implement your own MySQL storage engine through the "Storage Engine API", which lets you define the file format (and more):

• http://solutions.mysql.com/engines.html

• http://dev.mysql.com/doc/refman/6.0/en/storage-engines.html

• http://forge.mysql.com/wiki/MySQL%5FInternals%5FCustom%5FEngine

Answer 4

MySQL has support for pluggable storage engines. Here is a starting point.

Also, maybe using some kind of JDBC SDK could help.