Emulating a mySQL server (or postgres, or msSql)

[ggrot]

Pretty tough question:
I would like to write a piece of software that will emulate a SQL server of any kind. The goal is to have a different type of data source than a relational database, but to abstract it to respond to simple queries in the same way that any SQL flavor would.

Any ideas on how one would go about this? I can do the abstraction part, including parsing queries - that isn't the trick. It's a question of how to 'pretend' that the software is mySQL or some other SQL. I only need a solution for 1 flavor, not all of the above.

[Brett_Tabke]

There is a perl module (I forget the name) that littleman turned me onto a couple years ago that takes mysql syntax but actually uses flatfiles for storage. That might be of use.

[bcc1234]

Read some docs on data access objects, that will give you some ideas.

[dkubb]

I think Brett is referring to DBD::CSV,a perl module that allows you to issue normal SQL queries against it just like a database. Underneath it can use a normal comma separated flat file as the data source.

The latest version is even supposed to support joins between two or more flat files (which I personally find amazing) -- although I haven't had the time to test that feature out yet.

Keep in mind that the module won't support any database specific commands, so you're SQL will probably have to stay pretty close to ANSI 92 compliant. To do this, I try to validate my SQL commands against the Mimer SQL Validator, which can be found with a google search.

Sounds like you're thinking of using the module as part of unit tests. I do the same thing. I run all my unit tests using it and compare the expected output with what I know my fake database contains. I've found structuring my code to be tested in a tightly controlled sandbox speeds up testing considerably.

[ggrot]

That is a pretty interesting module, but not what I'm looking for. If I understand correctly, it is a perl specific module that parses SQL commands and applies them to flat file databases.

What I'd like to do is write a program that would pretend to be a database such that other programs (in any language) could use their native database connection code to perform simple queries against this program.

So for example, this emulator program could be accessed via PHP through the mysql_connect and mysql_query commands, through perl with the DBI stuff, and java through JDBC. It would do this by implementing the same communication as one of the major database providers (mysql, postgres, etc). The emulator program itself would form the query results live from some more complicated data sources. In other words, it would be a database wrapper for a completely custom data source.

[eaden]

Ok.. So you'd have to impliment the mysql api, and the JDBC api... a lot of work. Know C?

Source code for mysql is available.