{"id":1763,"date":"2009-12-12T08:00:15","date_gmt":"2009-12-11T22:00:15","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1763"},"modified":"2014-10-08T09:15:16","modified_gmt":"2014-10-07T23:15:16","slug":"the-table-protobuf-message-format","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/12\/the-table-protobuf-message-format\/","title":{"rendered":"The Table protobuf message format"},"content":{"rendered":"

If you’ve ever opened up drizzled\/message\/table.proto<\/a> in the Drizzle<\/a> source tree you will have seen what’s in the table message: the structure that describes a database table in Drizzle<\/a>. Previously I’ve talked about the Table message more generally<\/a>, giving a fair bit of history of the FRM file and how we’ve replaced it with both the Table protobuf message and an infrastructure inside Drizzle so that Storage Engines own their own metadata.<\/p>\n

Yesterday I talked about the Schema protobuf message format<\/a> in more detail, and this time I’m talking about the Table protobuf message in a similar amount.<\/p>\n

The first time we were loading (then only part of) the table definition out of a protobuf message was way back in January 2009 (I blogged about it too<\/a>). It was an adventure<\/a> untangling<\/a> all<\/a> sorts<\/a> of<\/a> things<\/a> to get to a much nicer place (where we are now). The code in the server is not perfect… I’ll be the first to admit that some of it is rather strange, but that’s mostly all behind the scenes for people interested in the protobuf Table message!<\/p>\n

The Table message has several embedded messages in it too. We need to have information on the Storage Engine, Fields and Indexes (and each of those can have other properties). It is much more complex than the simple Schema message<\/a>.<\/p>\n

Let’s have a look at the basic structure of the Table message:<\/p>\n

message Table {\r\n\r\n    \/* *SNIP* (Here goes the definitions for TableType, StorageEngine, Field, Index, ForeignKeyConstrain, TableOptions and TableStats) *\/\r\n\r\n  required string name = 1;\r\n  required TableType type = 5;\r\n  required StorageEngine engine = 2;\r\n  repeated Field field = 3;\r\n  repeated Index indexes = 4;\r\n\r\n  repeated ForeignKeyConstraint fk_constraint = 8;\r\n  optional TableOptions options = 9;\r\n  optional TableStats stats = 10;\r\n}<\/pre>\n
(We’ve skipped the definitions for the embedded messages for now)<\/p>\n
This seems all pretty logical; a table has a name, a type, is in a Storage Engine, has Fields, may have Indexes, may have foreign key constraints, it has some options and statistics (the statistics may go away at some point “soon”).<\/p>\n
Let’s have a look at the TableType message definition:<\/p>\n
  enum TableType {\r\n    STANDARD = 0;\r\n    TEMPORARY = 1;\r\n    INTERNAL = 2;\r\n  }<\/pre>\n
It’s pretty simple, the table type is either a standard table (what you get from CREATE TABLE), a temporary table (what you get from CREATE TEMPORARY TABLE) or an INTERNAL table (what you get when Drizzle uses a temporary table during query execution).<\/p>\n
Next, the StorageEngine message:<\/p>\n
  message StorageEngine {\r\n\r\n    message EngineOption {\r\n      enum EngineOptionType {\r\n        BOOL = 0;\r\n        INTEGER = 1;\r\n        STRING = 2;\r\n      }\r\n\r\n      required string option_name = 1;\r\n      required string option_value = 2;\r\n      required EngineOptionType option_type = 3;\r\n    }\r\n\r\n    required string name = 1;\r\n    repeated EngineOption option = 2;\r\n  }<\/pre>\n
The main part is the “name” member, which is just the name of the storage engine (e.g. “PBXT”, \u00c2\u00a0“INNODB”, “ARCHIVE”). We do however have support specified in the StorageEngine message for engine specific options (in key value form). Expect these to be used more in the near future.<\/p>\n
Specifying Fields is probably the most complex part of the table message. The Field message looks like this (with many embedded messages):<\/p>\n
message Field {\r\n    required string name = 1;\r\n    required FieldType type = 2;\r\n    optional FieldFormatType format = 3;\r\n    optional FieldOptions options = 4;\r\n    optional FieldConstraints constraints = 5;\r\n    optional NumericFieldOptions numeric_options = 6;\r\n    optional StringFieldOptions string_options = 7;\r\n\r\n    optional string comment = 16; \/* Reserve 0-15 for frequently accessed attributes *\/\r\n    optional SetFieldOptions set_options = 17;\r\n    optional TimestampFieldOptions timestamp_options = 18;\r\n}<\/pre>\n
So… what does this all mean? Well, Fields have a type, they’re stored in a format, there’s options attached to them, there may be constraints as well as field type specific options.<\/p>\n
The different field types should be fairly familiar by now:<\/p>\n
    enum FieldType {\r\n      DOUBLE = 0;\r\n      VARCHAR = 1;\r\n      BLOB = 2;\r\n      ENUM = 3;\r\n      INTEGER = 4;\r\n      BIGINT = 5;\r\n      DECIMAL = 6;\r\n      DATE = 7;\r\n      TIME = 8;\r\n      TIMESTAMP = 9;\r\n      DATETIME = 10;\r\n    }<\/pre>\n
We also allow fields in different formats. Currently, these are default, fixed and dynamic. The idea is you can tell the engine (or the engine can tell you) how it’s storing the field. This is currently here as a nicety and the users for this are few and far between.<\/p>\n
    enum FieldFormatType {\r\n      DefaultFormat= 0;\r\n      FixedFormat= 1;\r\n      DynamicFormat= 2;\r\n    }<\/pre>\n
The FieldOptions get interesting though:<\/p>\n
    message FieldOptions {\r\n      optional string default_value = 1;\r\n      optional string update_value = 2;\r\n      optional bool default_null = 3 [default = false];\r\n      optional bytes default_bin_value = 4;\r\n    }<\/pre>\n
You’ll no doubt be intrigued by the existence of both “default_value” and “default_bin_value”. Ordinarily, using a string to contain a textual representation of the default value (e.g. “foo” or “42”) is fine. However, for BLOB columns, you can have defaults that aren’t representable in a text string, you need binary data (e.g. the default value contains ‘\\0’).<\/p>\n
For TIMESTAMP columns, we continue to support DEFAULT NOW() and the ability to update the timestamp column on UPDATE. How is this represented in the table message? Well… default_value will be “NOW()” and update_value will be “NOW()”. It is intended that in the future it will be possible to have arbitrary SQL expressions for these. This does, of course, require support in the Drizzle server.<\/p>\n
The default_null bool should be rather obvious :)<\/p>\n
Well… that’s enough for today. Next time: more of the Field message!<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … 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_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],"tags":[70,87,185,205],"class_list":["post-1763","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","tag-drizzle","tag-frm","tag-protobuf","tag-table-message"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-sr","jetpack-related-posts":[{"id":1765,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/11\/the-schema-protobuf-message-drizzles-metadata-on-a-schema\/","url_meta":{"origin":1763,"position":0},"title":"The Schema protobuf message: Drizzle’s metadata on a schema","author":"Stewart Smith","date":"2009-12-11","format":false,"excerpt":"I've previously talked about table metadata in Drizzle and how we use the table protobuf message to describe a table (see Drizzle FRM Replacement and others). The model in Drizzle is that the engine is responsible for its metadata. For schemas (you may be thinking 'database' but we're moving 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":1813,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/12\/writing-a-storage-engine-for-drizzle-part-2-create-table\/","url_meta":{"origin":1763,"position":1},"title":"Writing A Storage Engine for Drizzle, Part 2: CREATE TABLE","author":"Stewart Smith","date":"2010-03-12","format":false,"excerpt":"The DDL code paths for Drizzle are increasingly different from MySQL. For example, the embedded_innodb StorageEngine CREATE TABLE code path is completely different than what it would have to be for MySQL. This is because of a number of reasons, the primary one being that Drizzle uses a protobuf message\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":1763,"position":2},"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":2323,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/17\/things-ive-done-in-drizzle\/","url_meta":{"origin":1763,"position":3},"title":"Things I’ve done in Drizzle","author":"Stewart Smith","date":"2011-03-17","format":false,"excerpt":"When writing my Dropping ACID: Eating Data in a Web 2.0 Cloud World talk for LCA2011 I came to the realisation that I had forgotten a lot of the things I had worked on in MySQL and MySQL Cluster. So, as a bit of a retrospective as part of the\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":1763,"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":1650,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/27\/pluggable-metadata-stores-or-the-revenge-of-table-discovery\/","url_meta":{"origin":1763,"position":5},"title":"Pluggable Metadata stores (or… the revenge of table discovery)","author":"Stewart Smith","date":"2009-05-27","format":false,"excerpt":"Users of the ARCHIVE or NDB storage engines in MySQL may be aware of a MySQL feature known as \"table discovery\". For ARCHIVE, you can copy the archive data file around between servers and it magically works (you don't need to copy the FRM). For MySQL Cluster (NDB) it works\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\/1763","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=1763"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1763\/revisions"}],"predecessor-version":[{"id":3828,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1763\/revisions\/3828"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}