those created during ALTER TABLE, typically in the database directory<\/li>\n<\/ul>\nYou may have seen these “#sql-foo.frm” etc files around.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[75,1],"tags":[70],"class_list":["post-1285","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-general","tag-drizzle"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-kJ","jetpack-related-posts":[{"id":1274,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/26\/temporary-tables-in-any-engine\/","url_meta":{"origin":1285,"position":0},"title":"Temporary tables in any engine….","author":"Stewart Smith","date":"2008-11-26","format":false,"excerpt":"Well... nearly any engine. I have a plan forming in my head to add some hooks to engines to help with creating temporary tables (the ones created while executing a query, not ones created during ALTER TABLE). Currently, if you ALTER TABLE and we require a temporary table, it's still\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2121,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/08\/20\/show-create-table-using-table-protobuf-message\/","url_meta":{"origin":1285,"position":1},"title":"SHOW CREATE TABLE using table protobuf message","author":"Stewart Smith","date":"2010-08-20","format":false,"excerpt":"... and really testing the replication code path for CREATE TABLE. So, for a very long time now, Drizzle has been using a protobuf based structure to describe the structure of tables. The idea was to be able to have engines rather painlessly generate this structure themselves (which several now\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1763,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/12\/the-table-protobuf-message-format\/","url_meta":{"origin":1285,"position":2},"title":"The Table protobuf message format","author":"Stewart Smith","date":"2009-12-12","format":false,"excerpt":"If you've ever opened up drizzled\/message\/table.proto in the Drizzle source tree you will have seen what's in the table message: the structure that describes a database table in Drizzle. Previously I've talked about the Table message more generally, giving a fair bit of history of the FRM file and how\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2329,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/23\/multi-tenancy-drizzle\/","url_meta":{"origin":1285,"position":3},"title":"Multi-tenancy Drizzle","author":"Stewart Smith","date":"2011-03-23","format":false,"excerpt":"My previous post focused on some of the problems of doing multi-tenant MySQL. One of the reasons why I started hacking on Drizzle was that the multi-tenancy options for MySQL just weren't very good (this is also the reason why I run my blog in a VM and not a\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":1285,"position":4},"title":"Drizzle FRM replacement: the table proto","author":"Stewart Smith","date":"2009-12-09","format":false,"excerpt":"Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1292,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/12\/10\/stewart-learns-sql-oddities\/","url_meta":{"origin":1285,"position":5},"title":"Stewart learns SQL oddities…","author":"Stewart Smith","date":"2008-12-10","format":false,"excerpt":"What would you expect the following to fail with? CREATE TABLE t1 (a int, b int); insert into t1 values (100,100); CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1285","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=1285"}],"version-history":[{"count":4,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1285\/revisions"}],"predecessor-version":[{"id":1288,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1285\/revisions\/1288"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1285"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1285"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1285"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}