MySQL custom engine: already existing data.

Question

I'm currently exploring if a custom MySQL engine could fulfill my needs. I've been looking at http://forge.mysql.com/wiki/MySQL_Internals_Custom_Engine. One thing I wonder is: can I create an engine using an already existing (binary) file?

If yes, I guess(?) there is no "CREATE TABLE..." so, how does MySQL know about this table?

EDITED Say I have a tool writing a big binary file called 'records.bin' which is a concatenation of the following C structure

    struct Record
    {
        char field1[10];
        int field2;
    };

I want to write a custom MySQL engine to perform the following query:

((declare table t1 as 'records.bin' exists somewhere ???))
select field1 from t1 where field2=1;

It should be possible, as the documentation says:

The most basic storage engines implement read-only table scanning. Such engines might be used to support SQL queries of logs and other data files that are populated outside of MySQL.

Answer 1

If by "binary file" you mean a data file ripped from the mysql data directory, I doubt very much you could use a data file created by one storage engine to populate a table in another storage engine.

If by "binary file" you mean a mysqldump, then yes, that should be no trouble. Mysqldump should have included a create table statement for you - in case it hasn't, you can do it yourself (do a SHOW CREATE TABLE t and copy the result).

You can also move tables between storage engines without dumping/reloading them, using ALTER TABLE t ENGINE = e;

Answer 2

Yes, this is possible. Just because CREATE TABLE actually creates files in the traditional storage engines like myisam doesn't mean that yours have to.

In fact, the CSV storage engine will use an existing appropriately named CSV file in the data directory. If the file doesn't exist, then it will create a blank file.