{"id":2286,"date":"2011-02-08T10:03:30","date_gmt":"2011-02-08T00:03:30","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2286"},"modified":"2014-10-08T09:16:08","modified_gmt":"2014-10-07T23:16:08","slug":"drizzle-metadata-tables","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2011\/02\/08\/drizzle-metadata-tables\/","title":{"rendered":"Drizzle metadata tables"},"content":{"rendered":"

Giuseppe<\/a> has a great post about the Evolution of MySQL metadata<\/a>, and I thought I’d have a look at what we have in Drizzle<\/a>. It’s pretty easy to work out how many tables are in each schema, we just query the standard INFORMATION_SCHEMA.TABLES view:<\/p>\n

drizzle> select table_schema,count(table_name)\r\n    ->  from information_schema.tables\r\n    -> group by table_schema;\r\n+--------------------+-------------------+\r\n| table_schema       | count(table_name) |\r\n+--------------------+-------------------+\r\n| DATA_DICTIONARY    |                53 |\r\n| INFORMATION_SCHEMA |                20 |\r\n+--------------------+-------------------+\r\n2 rows in set (0 sec)<\/pre>\n
In Drizzle it’s important to note that there is a differentiation between SQL Standard INFORMATION_SCHEMA tables (found in the INFORMATION_SCHEMA schema) and the extensions and extra information available from Drizzle that is Drizzle specific (found in DATA_DICTIONARY). Since I know that the PostgreSQL version I have on my laptop (8.4) also implements INFORMATION_SCHEMA, I can run this query there as well:<\/p>\n
stewart=# select table_schema,count(table_name)\r\n from information_schema.tables\r\n group by table_schema;\r\n    table_schema    | count\r\n--------------------+-------\r\n information_schema |    55\r\n pg_catalog         |    78\r\n(2 rows)<\/pre>\n
If we had written the query to the Drizzle DATA_DICTIONARY tables, it only may<\/strong> have been portable – and as we can see, certainly wouldn’t have run unmodified on PostgreSQL. Personally, I really like this feature, and wish more systems did something like it.<\/p>\n","protected":false},"excerpt":{"rendered":"
Giuseppe has a great post about the Evolution of MySQL metadata, and I thought I’d have a look at what we have in Drizzle. It’s pretty easy to work out how many tables are in each schema, we just query … 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_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_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}},"categories":[75],"tags":[259,70,203,408,628,342],"class_list":["post-2286","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","tag-data_dictionary","tag-drizzle","tag-information_schema","tag-metadata","tag-mysql","tag-postgresql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-AS","jetpack-related-posts":[{"id":2303,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/16\/fixed-in-drizzle-no-more-gotchas\/","url_meta":{"origin":2286,"position":0},"title":"Fixed in Drizzle: No more “GOTCHA’s”","author":"Stewart Smith","date":"2011-03-16","format":false,"excerpt":"\u00a0 At the upcoming MySQL Conference and Expo, I'm going to give a Thursday afternoon (2pm) session entitled Fixed in Drizzle: No more \"GOTCHA's\". I plan to have a lot of fun with this session.. If you go back to the very start of when I started submitting code to\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":2286,"position":1},"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":1776,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/01\/05\/information_schema-always-comes-first\/","url_meta":{"origin":2286,"position":2},"title":"INFORMATION_SCHEMA always comes first","author":"Stewart Smith","date":"2010-01-05","format":false,"excerpt":"SHOW DATABASES will always have INFORMATION_SCHEMA first, everything else is alphabetical. Why? because it hates you.","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1655,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/06\/09\/drizzle-pluggable-metadatastore-or-no-table-definition-file-on-disk\/","url_meta":{"origin":2286,"position":3},"title":"Drizzle pluggable MetadataStore (or: no table definition file on disk)","author":"Stewart Smith","date":"2009-06-09","format":false,"excerpt":"My code is shaping up rather nicely (see https:\/\/code.launchpad.net\/~stewart\/drizzle\/discovery) and I'm planning to submit a merge-request for it later today. I'm about to commit code that implements a MetadataStore for the ARCHIVE engine. This means that for ARCHIVE tables, you only have the .ARZ file on disk. The table definition\u2026","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1743,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/10\/21\/first-flesh-wound-in-create_tmp_table\/","url_meta":{"origin":2286,"position":4},"title":"First flesh wound in create_tmp_table()","author":"Stewart Smith","date":"2009-10-21","format":false,"excerpt":"If you have needed a good reason to drink heavily and forget, may I suggest taking a look at create_tmp_table() and those who call it. It's probably one of the best illustrations of rot and awful, incomprehensible APIs in the server (Drizzle inherited it from MySQL). In the normal paths\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":1738,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/10\/14\/return-of-the-top-5-mysql-wishlist-and-looking-at-drizzle\/","url_meta":{"origin":2286,"position":5},"title":"Return of the “Top 5 MySQL Wishlist” and looking at Drizzle","author":"Stewart Smith","date":"2009-10-14","format":false,"excerpt":"It's coming up on a year since I started working full time on Drizzle. So, I got a bit reflective... Have we done things that I (and others) really wanted done? Back in 2007, I wrote my top 5 wishlist for the MySQL Server. I am not going to pretend\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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2286","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=2286"}],"version-history":[{"count":3,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2286\/revisions"}],"predecessor-version":[{"id":3847,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2286\/revisions\/3847"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}