Well… there’s:
- database
- table name
(both of these are quite obvious).
But then you have:
- temporary tables
Well… two types of temporary tables:
- those created in the course of query execution, typically in /tmp/
- those created during ALTER TABLE, typically in the database directory
You may have seen these “#sql-foo.frm” etc files around.
but you can also CREATE TABLE `#sql-foo` (you know, because that’s a good string to use in your app). In fact, you can (and shouldn’t) create a table with the exact same name as the temporary #sql table and use it in your app.
So really the primary key for a table is: string database name, string table name, bool is_tmp. Oh, and the /tmp/ temporary tables just to screw with your braiiiinn.
In drizzle, this is what the storage engine API is ending up looking like. It’s the responsibility of the engine to encode the temporary table names so that they don’t clash with the non-temporary table names. This could be done by putting them in a different set of files, marking them with som eflag, or just passing the is_tmp flag to build_table_filename (or make_table_key) and being done with it.
I think this ends up being the least-hacky approach and it does mean we can remove the bass ackwards if(table_name[0]==’#’) checks from some engines.
don’t forget user-created temporary tables (“CREATE TEMPORARY TABLE”). you can create one with the same name as an existing non-temporary table.
even stranger, you can first create the temporary table, and then create the non-temporary table that you then can’t see until you drop the temporary table or try from another session.
BTW continuing topic of user-created temporary tables. Have you considered moving them to separate namespace in Drizzle? MySQL’s current scheme with temporary tables shadowing base tables is painful implementation-wise and security-wise.