Google Calendar API v3 Undocumentation

Over the many collective months that I’ve been working with Google Calendar API v3, I’ve noticed many undocumented “features” and behaviors. I thought I should sum up my experiences here so that someone else struggling to use this “simple” API would have at least some forewarning of the adventure they’re about to embark upon.

The documentation up on Google’s site is woefully incomplete. Yes, it lists all the methods and most of the parameters and such, the reference documentation; that’s great for starters, but it’s the bare minimum amount of useful information. What is completely missing is the documentation of behaviors you will encounter, what I call the narrative documentation. Google seems to be very bad about narrative documentation in general.

Uniqueness Constraints

What seems to be completely missing from the documentation is uniqueness constraints of objects (or resources, as Google calls them).

For example, an event is unique by its iCalUID property. Is this important fact mentioned on the event insert method‘s documentation page? Not at all. In fact, iCalUID is not even mentioned on this page. You have to go to the generic events resource page to find the first mention of iCalUID at all. Is the uniqueness constraint mentioned there either? Nope.

While we’re on the subject of inserting events, there’s also the import event, which I have no idea about what it does differently than the insert method, other than that they’ve declared that an iCalUID is required for the import method. The summary documentation is a useless one-liner: “imports an event.” Thanks, Sherlock; that was real helpful.

Furthermore, the only documentation about the iCalUID states that it should be an “Event ID in the iCalendar format.” I’ve found this to be completely untrue. That’s probably what the field is intended for, but there is absolutely no format validation for an iCalUID. You can put anything you want in here. (TWSS)


Google Calendar API gives you the ability to assign colors to events and also assign colors to whole calendars. What they don’t tell you is that assigning colors to events is completely useless unless the calendar the events are contained within is your own personal calendar. In other words, assigning event colors to a calendar intended to be shared with others is pointless. The only control over colors you have in that circumstance is to assign the whole calendar a single color specific to the user’s calendar list. If you really want colorization of events being shared with multiple users, your only choice is to split events across multiple calendars and assign the colors at the calendar level per each user. And of course, don’t forget the uniqueness constraint on the Summary property of the calendars you create!

Also, what they don’t tell you about colors is that there is one global palette of two kinds of colors: calendar colors and event colors. They do tell you there are two palettes, but they do not indicate whether they are global palettes or user-specific palettes. The two (calendar and event) palettes are not the same palette and an event colorId is not interchangeable with a calendar colorId and vice versa. Why use the same type name “colorId” to refer to two incompatible types? Why not just call one an “eventColorId” and the other a “calendarColorId”? Would that be so hard? To be fair, the documentation does make the distinction but it’s not obvious at first glance that the distinction is meaningful.

Furthermore, when Google duplicates events on your behalf (and they do – see the Side Effects section below), they don’t necessarily duplicate all properties, including the colorId property.

Recurring Events

Creating recurring events is extremely frustrating and fraught with many gotchas and stingers. I don’t even want to go into it here; avoid it at all costs if you value your sanity.

Side Effects

WARNING! Side effects of regular Google Calendar API v3 usage may include:

  • Adding email addresses as attendees copies the event to the attendees’ personal calendars. This creates a completely different event with its own eventId, unrelated to the one you created via the API. As far as I can tell, there is no programmatic way to determine if this duplicated event originated from the event you created via the API.
  • Deleting a user which owns calendars that are shared with other users will create a private copy of the shared calendars in each users’ calendar list and will only delete the original calendars owned by the user being deleted.
  • Deleting an event causes it to be marked as a dual “deleted/cancelled” state. I simply cannot figure out the difference between deleted and cancelled, if there is one.
  • Trying to re-create a previously deleted event will cause a 409 Conflict response. You must instead resurrect the deleted/cancelled event which has the same uniqueness properties as the one you are trying to create (e.g. the iCalUID must match).
    • When fetching the event list for a calendar, always set the “showDeleted” parameter to true. This way you can detect if you’re trying to recreate an already existing yet deleted event.


  • /events/list accepts timeMin and timeMax arguments and these are simply stated as accepting a ‘datetime’ argument. Of the myriad possible standardized date-time formats, I have discovered that this value should be a UTC date-time (with offset explicitly set at 00:00) formatted as RFC3339 (yyyy-MM-ddThh:mm:ss.sss+00:00).

There are many more issues than I can list here, but these are fresh in my memory.


UPDATE (2012-10-14): removed the bit about calendars being unique by Summary as that was not true.

UPDATE (2012-10-19): added Types section to document timeMin and timeMax arguments

Google Calendar API access with 2LO (2-legged OAuth) and .NET

Getting this scenario to work has been one of the most frustrating experiences of my development career as yet.  I’m writing this incredibly detailed and informative blog post to save others from self-inflicted premature baldness.

Your scenario is that you want to write a background task or service in .NET to communicate with Google’s servers specific to your Google Apps domain for business or education. Your service will not have any external exposure to end-users of your apps domain. You want to synchronize data in bulk to Google on behalf of your apps domain users. You want your service to use the latest and greatest APIs that Google advertises that are not deprecated. At the time of this writing that is the V3 API for things like Calendar ( I will use the Calendar API as my example here since that’s what I was first interested in using for my project.

Google seems to want to make this relatively simple scenario unnecessarily difficult to find any information on how to do it correctly. All of the documentation does one of the following: (a) ultimately redirects you to a page talking about a V2 API, not a V3 API, (b) does not talk at all about 2LO and instead obsesses over 3LO, (c) is misleading and woefully incomplete with regards to the practical information you need to know to avoid the dreaded 401 Unauthorized response from the API servers.

Let me assume that you have created a Google Apps domain for business or education already and that you have superadmin access to your Google Apps domain. If you do not, please do so and find the responsible parties in your organization to grant you superadmin access (if only to a development-specific domain).

Required set-up steps: (every single one is critical; do not skip one thinking you know better or you will fail)

  1. Create an API Project at while logged in as your superadmin account for your apps domain (honestly I’m not sure if it matters which user account you create the project with but it doesn’t hurt to be consistent here just in case).
  2. Go to the API Access section.
  3. Create an OAuth 2 client named “whatever you want” at “https:// wherever you want; it doesn’t matter for this scenario”.
    1. Note that you don’t need to specify an icon and that the name of your client doesn’t matter as no other living soul (a.k.a. end-user in your apps domain) will ever see it.
  4. Copy down the generated client ID ( and the client secret (lots-oflettersanddigits-here).
  5. Go to the Services section while still in the APIs Console and enable your specific services (e.g. Calendar).
  6. Open (need superadmin rights to your google domain here)
  7. Add the client ID ( from step #4 and specify “” for your scope (assuming you want to work with Calendar API) and click Authorize.
    1. For other APIs, list the proper scopes here, comma-delimited. I just need Calendar API and this works for me. Be sure to specify https, not just http.


  8. Go to
  9. Un-check the “Allow access to all APIs” under “Two-legged OAuth access control”.
    1. Yes, un-check it. This is per As of 2012-01-25, this is still relevant and required. Perhaps this will change in the future but for now it is required.
  10. Save changes.

Now you should have a properly set-up apps domain and API project and the two are linked together.

Let’s move on now to your .NET code that will be your task/service that runs in the background on behalf of your users in your apps domain.

Firstly, I do not recommend using the open-source Google client library for .NET. I’ve had bad experiences with it, namely that it has been known to leak memory. The issue I reported with them on this matter was claimed to be resolved but I haven’t been back to check it out. I had to make progress on my project and waiting for them to resolve the issue was not an option.

I wrote my own T4 template (Visual Studio code generator) to generate a client library for Google (and other RESTful APIs which use OAuth 1.0 or 2.0) that has no memory leaks and is both ridiculously fast and efficient: It supports both synchronous and asynchronous methods of I/O. Its code is up to date as of 2012-11-12.

Check out the project locally and open the TestClient project’s Program.cs file. This is a simple console application designed to demonstrate the simplicity of the code-generated API client for Google and using OAuth 1.0 for shared-secret authentication.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WellDunne.REST;
using WellDunne.REST.Google;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace TestClient
    class Program
        static void Main(string[] args)
            // Create the authentication module to use oauth 1.0 2LO:
            // NOTE: Replace 'key' and 'secret' with your testing parameters, else you'll get a 401 (Unauthorized) response.
            var oauth = new OAuth10("", "mysecretkeyhere");

            // Create the client against
            // NOTE: This client instance is reusable across threads.
            var client = new WellDunne.REST.Google.V3.CalendarServiceAsyncClient(new Uri(""), oauth);

            // Create the request to get the user's calendar list:
            // NOTE: Each request object is NOT reusable.
            var req = client.GetMyCalendarList(null, null, null, /*requestorID:*/ "");

            // Fetch the request synchronously:
            var rsp = req.Fetch();

            // Write the response JSON object to Console.Out:
            using (var conWriter = new JsonTextWriter(Console.Out))

NOTE: This test program is specific to Google Calendar. If you are working with a different API, you’ll have to edit the RESTful/Google/ T4 template to declare the API methods you need access to. It couldn’t hurt to define some Newtonsoft.Json-enabled classes to deserialize the response data to.

For Google Calendar API testing, simply paste in the values from your API Console (client ID and client secret) into the `new OAuth10(“”, “mysecretkeyhere“)` expression.

Then paste in an actual user provisioned in your domain into the `client.GetMyCalendarList(null, null, null, /*requestorID:*/ ““)` expression.

Run the program and you should see a raw JSON dump of the response retrieved from Google.

For example, I get this output (id and summary are sanitized):



I put together a web-based SQL query tool written using ASP.NET’s IHttpHandler interface, packaged as a single query.ashx file. Its main feature is that it guarantees that you cannot construct a query that inserts or updates data. The SELECT query form is forced upon you and any means of escaping that form via SQL injection is detected and the query is rejected in that case. This makes it safe to deploy internally so that your developers may query data in a read-only manner.

Unfortunately I can’t link you to any public deployment of this tool for a demo because it queries SQL databases. I don’t have any SQL databases out in the wild and if I did I certainly wouldn’t want any random jackhole off the internet querying data from them anyway. You’ll just have to deploy it yourself for your own demo. It’s not hard to set it up since all you need is just some form of ASP.NET host that can compile and execute an ashx file. Virtually any standard IIS host will be able to handle this in its default configuration. The tool runs under the security context of the app pool you place it in so if you use connection strings with Integrated Security=SSPI, beware of that.


  • SQL query builder that allows only SELECT queries (Query tab)
    • Try to break it and find a way to UPDATE or INSERT data!
    • Strips out all SQL comments
    • Actively prevents you from overriding the forced separation of query clauses, e.g. you cannot put a FROM into the SELECT clause unless it’s part of a subquery
  • Results tab
    • Queries are forcibly executed in READ UNCOMMITTED transaction isolation level
    • SET ROWCOUNT is set to 1000 by default but can be overridden with rowlimit=# query string parameter.
    • Dynamic show/hide of SQL column type headers
    • Execution time tracked in msec
    • Results grid with left/right text alignment set per column type (numerals are right-aligned, text is left-aligned, etc.)
    • Binary data is displayed as 0xHEXADECIMALSTRINGS
    • Shows generated SQL query
    • Link to share query with someone else (opening link automatically executes the query)
    • Links to JSON and XML output formats with appropriate Content-Type headers. (Try it in Chrome’s Advanced REST Client app!)
  • Custom Connection String support (Connection tab)
    • The drop-down is sourced from web.config’s connectionStrings section
  • Recording of query history (Query Log tab)
    • Stores host name of client that executed query
    • Paged view
    • Click “GO” link to execute query
  • Parameterized queries
    • Parameter values are saved in the query log
    • Probably want to store a library of queries in addition to the query history log
    • Limited set of parameter types supported, but most common ones used should be there
  • Multiple forms of JSON and XML formatted output for programmatic consumption of query results
    • Add query string params to produce less verbose output: no_query=1, no_header=1
  • Single-file deployment
  • Self-update feature that pulls latest code from github
  • Tip jar!

I rather enjoy writing developer tools like this. I especially enjoy the ashx packaging mechanism. For an ashx file, you write all your tool code in one file that contains a primary class that implements the IHttpHandler interface (or IHttpAsyncHandler). You have complete control over the HTTP response in this way at a very low level yet you still get all the convenience of the HttpContext class with its Request and Response structures that ease the pain of dealing with HTTP headers, URLs, query strings, POST form variables, etc. at such a low level.

IVO-CMS – Part 1 – Mistakes of the Past

For the first time, I’m starting a series of blog posts. This series will focus on a web content management system (IVO-CMS) that I’m currently designing and developing, partly out of curiosity to see if it can be done, partly out of the fun in implementing something new, and partly out of a need to correct my own mistakes of the past. This first post will explain the past mistakes in designing an existing proprietary CMS for my current employer; one you’ve never heard of, never seen, and never used due to its proprietary nature. I will tell you all this in order to give you context around how the design of the new system was influenced by the failures of the current one.

Let me start out by describing the general system architecture and then demonstrating the weaknesses of such a system. Ironically, this blog post is probably the most accurate documentation of this system’s architecture that currently exists.

Since the system allows each piece of content it tracks to be revision-controlled, I started out by designing a rather simplistic revision control system.

Each versionable object domain is represented in the database by a pair of tables: the “versioned root” table and the version table. What I have termed the “versioned root” is nothing more than a conceptually meaningless container that keeps track of the currently published version record by foreign key reference and is used to tie all the versioned records together by a common ancestor/container. Its basic schema is (VersionedRootID int PK, PublishedVersionID int FK, IsPublished bit).

The version table is what contains all of the versionable data that may change over time and have a history kept track of. Its basic schema is (VersionID int PK, VersionedRootID int FK, ProjectID int FK, all other data columns that are versionable).

For instance, let’s say we have XHTMLContentFragment (versioned root table) paired with XHTMLContentFragmentVersion (version table). The XHTMLContentFragmentVersion contains all of the useful system data and the versioned root table is there to tie all of those version records together and assign an identity to each versionable content fragment object.

A project is a container of versioned items; all versioned records point back to a specific ProjectID. Creating a project must be done first before creating any new versions. Think of it as a “changeset” that’s created ahead of time to associate a set of changes that are going to be applied simultaneously. A project is what gets published, and doing so simply bumps all of the PublishedVersionID FKs on the versioned root records up to the VersionedID that is contained in the to-be-published ProjectID. All of this is done in a transaction so as to be atomic. The idea being that the entire live site gets updated at once and that it all goes live or nothing goes live.

In my eagerness to implement this design, I neglected to account for a few implementation details.

Firstly, with this schema, a versionable item is not able to be deleted from the system in an atomic fashion by doing a publish. I did not implement an IsDeleted bit on the versioned root table to keep track of which objects are still alive. Furthermore, I failed to implement a companion table that records the publish history. There is no way to know what previous VersionID was published before the current PublishedVersionID.

Furthermore, the versioned root concept is flawed in that these containers are meaningless. They do not lend themselves to performing cross-system content merging. In fact, they actively hinder such a useful action. Imagine you have several database environments, like development, testing, staging, and production. Content changes in production would need to be merged down into development so that developers have the latest content to edit for system integration purposes (adding links to new features, etc.) while the production content maintainers can continue to make content changes to the live system.

The reason these containers are meaningless is primarily because of the choice to use auto-incrementing database identity values as primary keys. VersionedRootID 132 has no meaning to anyone except the actual versioned records that point back to it as their container. Its identity value 132 represents nothing semantically useful to the system. If I try to merge content from one system’s VersionedRootID 132 to another system’s VersionedRootID 132, that means nothing. I cannot know that those two containers are the same across the two independent systems.

Finally, the nail in the coffin is that I failed to track the parent VersionID when creating a new version. The system simply copies the content from the currently-published version record and makes a new version record with it then lets the user edit that. The lack of a recorded parent VersionID means that there is no way to tell what was published at the time of object creation. Projects may be created by any user of the system at any time and there may be multiple active projects being worked on simultaneously. Without an accurate history, there’s no way to figure out how to accurately merge this project’s change with another project’s change to the same versionable object. For instance, project B gets created based off published version A and project C gets created based off published version A as well. Let’s say that project B gets published first. This means project C is still based off published version A, and not version B. If project C gets published, it effectively overwrites changes that were made in project B. Without recording the parent VersionIDs, we can have, at best, a baseless merge.

In summary, with this CMS we have a weak history in that all the previous project versions are stored, but there’s no way to know in what order each project was published in. We also don’t know whether content from one project was accidentally overwritten with changes in a second project that was created before that first project was published but was then later published. There is no way to delete versioned items. There is no clean way to merge content across independent systems/environments because an accurate history is not kept and the container structure for the versioned items is meaningless.

All of these problems got me thinking about a better solution. That solution came, somewhat surprisingly, with git… but that’s for next time!

Immutable Versionable Objects (IVO) a.k.a. The Git Object Model

Git is a distributed, content-addressable and versionable file system that is primarily used as a revision control system for project source code. That’s great and all, but we’re not really interested in what git does, but more specifically how it does what it does; we’re interested in its internal object model. I started a simple C#(5) .NET 4 project that implements git’s internal object model as a reusable library.


As a slight change of pace, I will now redirect you to first read Git For Computer Scientists (if you haven’t already) as a basic yet effective crash course on the git object model. This understanding is critical to understanding this blog post and by extension the purpose of the project I created. Seriously, go read it and come back here. I’ll wait.

You’re back now? Good. Do you understand what git’s object model is all about? In summary, it’s composed of a very small set of primitive objects that link together to form a very complicated and powerful revision-controlled file system.

The key concept of git is content-addressable objects, surprisingly. Content-addressable means that a hash is used as a unique key to identify an object based solely on its contents. The hash must be generated by concatenating all content stored in the object together in a predictable and consistent manner, and then running a well-known cryptographically-secure hashing function (such as SHA-1) over that concatenation. “Cryptographically secure” may not be the best name for such a requirement, but a hash function that produces a very good distribution and that maintains entropy of the data in its hash are the desirable qualities.


The project I have created is named IVO (pronounced /ee’-vho/), short for Immutable Versionable Objects. It is hosted on github here. Sorry, I’m bad at naming projects. I want to call everything either Orion or some combination of the names that make up the project’s uniqueness.

As I stated above, the purpose of this project is to implement git’s internal object model in a completely reusable and implementation-flexible way. The current implementation as I write this is based on persisting the object data in a SQL Server 2008 database (currently using SQL Express as a development environment). The API is defined with interfaces so any back-end provider (including regular filesystem storage like how git does it) can be implemented with ease. In fact, implementations should be cross-compatible with one another so you can dump a SQL database containing a repository into a set of flat files or vice versa.

Asynchrony and Immutability

Consistent with my latest kick for asynchronification, I’ve designed the IVO API do be entirely Task-based using the TPL (task parallel library; `System.Threading.Tasks`) part of the .NET 4 framework. The SQL Server 2008 back-end implementation executes all its database queries asynchronously using SqlConnection’s `BeginExecuteReader` / `EndExecuteReader` standard async pattern.

Hopefully, the Task-based API is easily degradable to synchronous execution where that is desired, but why would you not want it? :) I’m fairly certain it’s easier to downgrade asynchronous to synchronous than the inverse.

Consistent with the asynchronous nature of the API, and regardless of object model logical immutability, all CLR objects that represent part of the object model are immutable data structures in order to be asynchrony-friendly. Nested Builder objects are used to construct a mutable object as a work-in-progress (e.g. when being constructed from a persistence store) and is then converted to its parent immutable object type.

As for object model logical immutability, data is only ever added to the system in the form of new records (and records can be removed when proven orphaned). Data is never updated in-place except where that object model is considered logically mutable, e.g. a `ref`. As a general rule, all content-addressable objects are immutable and are never updated after their original creation.


Once you fully understand the power of the object model implemented, you can solve all sorts of interesting problems that used to be considered hard, like implementing a content-management system, a remote filesystem synchronization system, a source control system, a document-tracking system, a historical record tracking system, and yes, even a revision controlled native file system driver (although probably not easily achievable via .NET)! The possibilities are nearly endless. What’s apparent to me is that this object model lends itself well to the design of systems that have to deal with revision control or synchronization, or a combination of the two. I’m sure there are many other problems that this can help solve that I’m not aware of.

You don’t even have to expose the gritty internal details of commits, trees, blobs and all that jazz to the end user. You can implement it transparently on their behalf. Then when it comes time to handle that complicated merge operation, you can present the user with “Oh look! I kept track of all your revisions! How would you like me to merge your changes for you?” It’s my opinion that the more a (savvy) user knows how a system is implemented the more he/she can be useful and take advantage of the system.

Distributed Workflow

A distributed workflow model is a virtually “free” benefit of this object model as well. You can have multiple users of your system working simultaneously and independently of one other. The only difficulty is in the implementation of a semantic diff/merge utility specific to the kind of data you’re working with. You will quickly find that you need to provide users the ability to merge their “branches” together to produce one common main-line branch that represents the accepted state of the system. To do that, you’ll need that diff/merge utility to handle combining independent changes. The object model can easily track your merge commit with multiple parent commitids, but how you handle merge the actual content is entirely up to you.


In summary, IVO is a framework for solving a larger problem that wants to handle revision control or synchronization in a powerful and flexible manner. I, myself, am writing a web content management system based off of it named, quite naturally, IVO-CMS. That will be the subject of future blog posts. I felt I should blog about IVO first as a platform for introducing IVO-CMS. I’m implementing and improving the two in tandem so IVO will get more API usability benefits as a result of implementing IVO-CMS along the way.

RabbitMQ .NET client API is terrible (rant)

I’ve seen some pretty poorly-designed APIs in my time, but RabbitMQ’s .NET client API has got to be among the top worst that I’ve seen lately. Specifically, I’m talking about the RabbitMQ.Client library that appears to be officially sanctioned by the RabbitMQ project and “documented”.

The very first problem you’ll quickly see when you get into using this “API” is that everything (e.g. return values and parameters) is typed as a string. Strings are problem enough in and of themselves, so why exacerbate the problem by requiring them everywhere?

Let’s dive in to this API and look at a few examples in the IModel interface:

void ExchangeDeclare(string exchange, string type, bool durable, bool autoDelete, IDictionary arguments);

See that ‘type’ parameter? What does that mean? What is an exchange ‘type’ in this context? Can I create my own types? It turns out, no. This string value is expected to be one of (“direct”, “fanout”, “topic”). How do we know this from the method signature? We don’t! Not a clue. No, we have to pore over the user guide “documentation” to find that we’re expected to use one of the ExchangeType class’s const string members here.

Here would be a perfect candidate for an enum. This is essentially an untyped string “enum”, primarily used when calling ExchangeDeclare function. I haven’t done a full search of the API, but I’d want to say that this is probably the only place these values are used. Not to mention this ExchangeType “static” class isn’t even declared as static. Sure, you can new up an ExchangeType! What would that get you? Nothing. Not only did they do it wrong, they did it wrong the wrong way.

Continuing on, the ExchangeDeclare method returns `void`, like most methods in this API. This is also terrible in that it gives you absolutely no information back as to the success/failure status of the operation; that would be the bare minimum expectation for a reasonably dull API. What would be preferred is a more complex type system that propagates contextual information through its methods. Now there are some good situations to use void-returning methods in an API, but given this context, I don’t think this is one of them.

Why is the void-returning such a big deal? It gives you no information as to what operations should logically follow next after “declaring” an “exchange”. The API designer has expectations on how you, the user, should use this API and has documented elsewhere that the expected pattern of invocation is ExchangeDeclare, QueueDeclare, and QueueBind, but you would never be able to discover this yourself without significant trial-and-error at runtime.

The API itself should expose strong hints as to how it is designed to be used, not the documentation. Documentation should be used to clarify what and/or how a specific piece of functionality works/does. The API should scream at you (via its types) how it is to be used correctly. Documentation should scream at you how it should NEVER be used incorrectly.

What would have been nice is if this ExchangeDeclare method gave back some semblance of an Exchange type that has its own methods, maybe something like `QueueBinding BindQueue(Queue queue, …)`. That also implies there is a Queue type returned from QueueDeclare. All of this typing information should let you logically come to the conclusion that, “Oh, I need both a Queue and an Exchange declared first, and then the Exchange lets me bind queues to it!”

Instead, what we’re given is three seemingly independent methods defined on `IModel` (which is a terrible name for something that’s supposed to mean a channel, BTW) with void-returning semantics that accept only string-typed parameters and the order of operations is left undefined for you to guess at.

There is also absolutely no XML parameter documentation in this API either, which is more-or-less a de-facto standard in the C# world. Now, there are method <summary>s in the XML documentation, but the parameter documentation is entirely missing. Intellisense would have significantly helped out the discoverability of this API has there been any XML documentation on the parameters. Going back to ExchangeDeclare, at least a NOTE saying that this string ‘type’ parameter expects its values to come from the ExchangeType class would have been more useful than saying nothing whatsoever.

string QueueDeclare(string queue, bool durable, bool exclusive, bool autoDelete, IDictionary arguments);

Why would you use the pre-generics IDictionary type with absolutely no explanation as to what types it expects to see in the dictionary, what it is used for, or even if I can safely pass in a null reference if I don’t care right now about extra ‘arguments’? There are MUCH better strongly-typed ways of conveying optional information to an API programmatically than using an IDictionary. This should simply not be done, no matter if it be with the pre-generics framework types or with the generic ones (i.e. IDictionary<TKey, TValue>).

What does this method return? A string? Seriously? What do I do with that? What information is in this string? What other methods are looking for this particular string value? This return value is in its own domain, and I would hope obviously not to be confused with any other `string`-typed parameters in this terrible API. I should NOT have to run the program (in which I don’t even know if I’ve done anything right so far) in order to discover what different domains of string values there are and where each one is expected. This is the entire point of a typing system, whether it be dynamic or static. At least with a dynamic typing system there is some structure around your data. Here we just have strings, the lowest of the low.

void QueueBind(string queue, string exchange, string routingKey);

Now we have a new ‘routingKey’ string parameter. What exactly is the proper formatting of this key? Should I care? Are there any special characters reserved? Why not wrap this up in a RoutingKey struct, at the very least, that’s responsible for constructing only-valid routing keys? Nope, we’re completely on our own to create valid routing key values that should hopefully conform to the established AMQP standard’s definition of a routing key.

string QueueDeclare(string queue, bool durable, bool exclusive, bool autoDelete, IDictionary arguments);

We can declare queues! Great. Any restrictions on the queue name? Is that validated by this API? Again with the IDictionary crap? At least we have some bools now, and they’re not `string`s whose valid set of values would be (“true”, “TRUE”, “false”, “FALSE”) but not “True” or “False”. That last part was a joke, but I certainly wouldn’t be surprised if that were the convention here too. Why not be consistent and just STRING everything? That was also a joke. I hope you’re still with me here. Still, boolean parameters are generally frowned upon.

string QueueDeclarePassive(string queue);

Okay, now apparently there are ‘passive’ queues. I guess that passive queues cannot be defined as durable, exclusive, or autoDelete. What, no extra IDictionary of arguments this time? Does that mean I declare ‘active’ queues with QueueDeclare? What’s the difference?

All these strings everywhere bring me to another point: case-sensitivity. Is “MYQUEUE” different from “myQueue”? Are routing keys case-sensitive? Also, what if I want to use a non-ASCII character in my queue name, like “ಠ_ಠ”? Will that wreak havoc with this system? It seems this is the only face I’m able to make towards this API’s design. Hmm… what if I have some extra whitespace in my string? Are all string values Trim()ed, or just some, or none?

I generally don’t like writing public rants, but it seems no one else has had anything negative to say about this API yet. I feel dumber having used this API. I award the designer no points, and may God have mercy on his/her soul. This makes me not want to use RabbitMQ at all.

RunQuery commandline tool

As a useful companion to LinqFilter (which I have given considerable attention to over the last few weeks), I put together a tool called RunQuery, which, you guessed it, runs SQL queries from the console… BUT (and here’s the kicker) it formats the results in an escaped, TAB-delimited format for direct use by LinqFilter.

For tool interoperability, I created (read: quite possibly reinvented) a useful, near-universal data interchange file format which uses TAB characters as column delimiters but also backslash escapes characters in each column to eliminate the possibility of actual TAB chars in the data interfering with the format. The backslash escaping rules are a subset of C#’s own string escaping rules. Also, null-valued columns are rendered as NUL (\0) chars in the format.

This data interchange format is accessible from LinqFilter via the SplitTabDelimited and JoinTabDelimited static methods. These methods are cloned in the RunQuery tool to ensure parity between the two tools. All of this makes it so we can easily exchange tabular data between tools using simple text pipes and/or files without having to worry about escaping, encoding, etc.

When you use RunQuery with default settings, it outputs two rows of header information. First, the column names, and second, the column types. All subsequent rows are column values. No row count is currently reported. There is no limit to number of rows returned either.

Aside from simply executing static SQL queries, RunQuery has a special “batch” mode which can execute a parameterized query whose parameter values are supplied via the stdin pipe. This mode expects the stdin lines to be fed in a particular order and formatting, namely the TAB-delimited encoding described above.

In this batch mode, the first line of input must be a row of parameter names where each column is a parameter name and must begin with a ‘@’ character. The next line of input is a row of columns that represent parameter types. Acceptable values are the enum members of SqlDbType.

After these first two rows of metadata are consumed and validated, all subsequent rows are treated as columns of parameter values. For each row, the query is executed against the given parameter values until EOF is reached.


$ cat > params

$ RunQuery -E -S myserver -d mydatabase -b -q ‘select @n’ < params
select @n


For example, batch mode may be used for streaming query results from one server to another for something simple and naive like a poor man’s data replication system or cross-database queries whose servers are not linked. One can also stream these parameter inputs from a LinqFilter query which may be processing input from a file or perhaps even consuming data from a web service. The possibilities are endless here.

Running RunQuery with no arguments displays some brief usage text.

LinqFilter Code Injection

Unless you’ve been living under a rock, you may have heard of such things as SQL Injection and XSS (cross-site scripting) attacks, just to name the popular forms of the attack. These types of attacks are language specific forms of the general “Code Injection” attack. These types of attacks happen when developers concatenate source code with unchecked user input and get to the stage where that fully trusted source code is eventually executed.

SQL Injection happens when a developer concatenates user input into a SQL Query but the user input may contain special characters like single-quotes which changes the meaning of the query. You could do this in a safe way by either properly escaping the raw user input or passing it in to the SQL Server out-of-band in a parameterized query. But I’m not here to blather on about SQL Injection attacks.

Today I’d like to talk about C# Code Injection, specifically as it applies to LinqFilter.

In LinqFilter, I naively concatenate what should be untrusted user input LINQ code directly into an implicity trusted C# code template embedded in the program. I invoke the C# compiler provider on that resulting string and execute the code in the compiled assembly. By concatenating unchecked user input into an existing code template, I’ve opened the door to C# Code Injection. The user has numerous ways of defeating the requirement that the query be a valid C# 3.5 expression.

One can easily terminate the expression with a ‘;’ and go on to write regular C# statements. LinqFilter will naively concatenate your code that it assumes is an expression into the code template and compile it. The compiler doesn’t care what happened so long as the code compiles as a valid C# program. This defeats the purpose (somewhat) of the LinqFilter tool so it’s generally not wise to do so and you’d only be hurting yourself, unless of course you’re executing someone else’s malicious LinqFilter scripts.

What I’d really like to do is put in a safety check to validate that the user input is indeed a C# 3.5 expression. This involves getting a parser for the C# 3.5 language, not an easy task. All I need to do is simply validate that the input parses correctly as an expression and only an expression. I don’t need to do any type checking or semantic analysis work; the compiler will handle that. I just need to know that the user can’t “escape out” of the expression boundaries.

Unfortunately, there are no prepackaged C# parsers that I could find. This means using one of the plethora of available parser-generator toolkits or writing my own. Neither options are sounding very attractive to me at the moment so I’ll probably just hold off entirely on the work since it provides no benefit to myself. I know how to use LinqFilter effectively and there’s clearly no community around it (yet) that are sharing scripts so there’s really no security problem. If someone else is concerned enough about the problem, I gladly welcome patches :).

I think I’d also like to figure out how the CAS (code access security) feature works and incorporate that into the generated code so that queries are executed in a safe context and won’t be going out and destroying your files by invoking File.Delete() or something sinister, but this is a low priority as well.

C# Case Expression via Extension Methods

As a veteran C# developer yourself, I’m sure you’re familiar with the `switch` statement. Since it is a statement this means you cannot effectively use this ever-so-useful construct in an expression, such as in a LINQ query. This is a shame, and it irks me greatly that I have to resort to emulating the switch behavior with a series of chained ternary operators (a ? b : c ? d : e ? f : g …) in LINQ. Lucky for you, I am more susceptible to NIH than any man alive. I felt the need to investigate options in making a functional `case` expression for C#, or an equivalent cheap look-alike :).

LINQ is great at expressing basic data transformation operations like joins, groupings, projections, etc., but it’s not so great at conditional processing. A foreach loop with a switch statement would be a better engine for this type of task, but frankly sometimes it’s just too tempting to start off by writing a LINQ query to get the job done. Using LINQ brings you the benefits of not having to worry about implementation details while also not increasing your bug surface area when you implement these basic transformations wrong.

Let’s look at an example naive query that needs to do conditional processing:

from line in lines
where line.Length > 0
let cols = line.Split('\t')
where cols.Length == 2
let row = new {
  // 'A' = added, 'D' = deleted, 'M' = modified
  operation = cols[0].Trim(),
  data = cols[1].Trim()
let added = "added " + + " " + File.ReadAllLines(
let deleted = "deleted " + + " " + File.ReadAllLines(
let modified = "modified " + + " " + File.ReadAllLines(
select (row.operation == "A") ? added
  : (row.operation == "D") ? deleted
  : (row.operation == "M") ? modified
  : String.Empty

This works but is wasteful in terms of processing since we’re always generating the `added`, `deleted`, and `modified` strings regardless of the input condition. The more dependent variables you introduce, the more waste the query has to generate and select out.

What we really want here is a switch statement, but a statement cannot belong in an expression. Expressions may only be composed of other expressions. Let’s see how this query transforms when I introduce my Case() extension method that I wrote for LinqFilter:

from line in lines
where line.Length > 0
let cols = line.Split('\t')
where cols.Length == 2
let row = new {
  // 'A' = added, 'D' = deleted, 'M' = modified
  operation = cols[0].Trim(),
  data = cols[1].Trim()
select row.operation.Case(
  () => String.Empty,                           // default case
  Match.Case("A", () => "added " + + " " + File.ReadAllLines(,
  Match.Case("D", () => "deleted " + + " " + File.ReadAllLines(,
  Match.Case("M", () => "modified " + + " " + File.ReadAllLines(

Through the use of generic extension methods and lambda expressions, we’re able to get a lot of expressibility here. This overload of the Case() extension method accepts first a default case lambda expression which will only be invoked when all other cases fail to match the source value, which in this case is `row.operation`’s value. What follows is a `params CaseMatch<T, U>[]` which is C# syntactic sugar for writing something along the lines of `new CaseMatch<T, U>[] { … }` at the call site.

These `CaseMatch<T, U>`s are small containers that hold the case match value and the lambda expression to invoke to yield the result of the case expression if the match is made. We use lambdas so that the expression to return is not evaluated until the match is made. This prevents unnecessary work from being done or causing side effects. Think of it as passing in a function to be evaluated rather than hard-coding an expression in the parameter to be evaluated at the call site of the Case() extension method. There are two generic arguments used: `T` and `U`. `T` represents the type you are matching on and `U` represents the type you wish to define as the result of the Case() method. Just because you are matching on string values doesn’t mean you always want to return a string value from your case expressions. :)

A small static class named `Match` was created which houses a single static method `Case` in order to shorten the syntax of creating `CaseMatch<T,U>` instances. Since static methods can use type inference to automagically determine your `T` and `U` generic types, this significantly shortens the amount of code you have to write in order to define cases. Otherwise, you would have to write `new CaseMatch<string, string>(“A”, () => “added” +` each time. Which looks shorter/simpler to you?

When you call the Case() extension method, the `CaseMatch<T,U>` params array is processed in order and each test value is compared against the source value which Case() was called on. If there is a match, the method returns the evaluated lambda for that case. There is no checking for non-unique test values, so if you repeat a case only the first case will ever receive the match. It is an O(n) straightforward algorithm and does no precomputation or table lookups.

Another overload of Case() is available for you to provide an `IEqualityComparer<T>` instance. This is a big win over the switch statement IMO, which to the best of my knowledge does not allow custom equality comparers to perform the matching logic and is limited to the behavior set forth by the C# language specification.

With this ability, you could specify `StringComparer.OrdinalIgnoreCase` in order to do case-insensitive string matching, something not easily/safely done with the switch statement. The ability to supply a custom IEqualityComparer<T> also opens up the set of possibilities for doing case matches on non-primitive types, like custom classes and structs that would not normally be able to be used in a switch statement.

In order to play with this extension method for yourselves, either browse my LinqFilter SVN repository and download the code from the LinqFilter.Extensions project (CaseMatch.cs and Extensions/CaseExtensions.cs), or download the LinqFilter release and play with it in your LINQ queries.

LinqFilter: Run LINQ Code From The Command Line Interface!

Having recently acquired a taste for using git on Windows with msysGit, I’ve been getting a lot more productive with my use of bash and other command-line tools in Windows. Shifting data around on the command line gets pretty hairy very quickly. Unfortunately, the basic set of Un*x utilities that process text data is just not powerful/flexible enough and usually each tool has some ridiculous custom syntax to learn, all of them different. I already know a  language powerful enough to process text efficiently, succinctly, and cleanly: LINQ! So I thought to myself, why not take advantage of LINQ to write simple little one-off text-processing scripts? Creating a new console application every time to handle this task becomes arduous, to say the least. Enter: LinqFilter!

For the impatient, you may download the latest release of the tool here (ZIP download). If this tool ever becomes popular enough, I have no problems hosting it elsewhere.

LinqFilter is, in a nutshell, a way to dynamically compile user-supplied C# v3.5 LINQ code and execute it instantly, sending the resulting item strings to Console.Out delimited by newlines or custom delimiters. An input IEnumerable<string> named lines is provided to allow the query to read lines from Console.In. There are many command-line options available to customize how LinqFilter behaves.

Let’s take the following example LINQ query:

LinqFilter -q "from line in lines select line"

This is a simple echo query. It will echo all lines read in from Console.In to Console.Out and it will do so in a streaming fashion. There is no storage of lines read in or written out. As a line comes in, it is run through the query and written out.

The “-q” command-line option appends a line of code to the ((QUERY)) buffer. You could supply multiple lines of code by supplying multiple “-q” options in order.

How does this work? The LinqFilter tool basically concatenates your query code into the following abbreviated class template:

public static class DynamicQuery {
    public static IEnumerable<string> GetQuery(IEnumerable<string> lines, string[] args) {
        IEnumerable<string> query = ((QUERY));
        return query;

The ((QUERY)) token is your query expression code. The ((PRE)) token is replaced with lines of C# code you supply in order to do one-time, pre-query setup and validation work. The ((POST)) token is replaced with lines of C# code you supply that takes effect after the query variable is assigned. This section is rarely used but is there for completeness.

As you can see, the query is enclosed in a simple static method that returns an IEnumerable<string>. The host console application supplies the lines from Console.In, but your query is not required to use that and can source data from somewhere else, or make up its own. :)

The args parameter is used to collect command-line arguments from the “-a <argument>” command-line option so that the query may be stored in a static file yet still use dynamic data passed in from the command line.

Let’s look at an example with a ((PRE)) section:

LinqFilter -pre "if (args.Length == 0) throw new Exception(\"Need an argument!\");" -q "from line in lines where line.StartsWith(args[0]) select line.Substring(args[0].Length)" -a "Hello"

Here we put in a full C# statement in the ((PRE)) section via the “-pre” command-line option to handle validation of arguments. The query itself is a simple filter to only return lines that start with args[0], i.e. “Hello”.

The best feature of the tool is the ability to store your queries off into a separate file and use the “-i” parameter to import them. Let’s leave that for another time.

In the mean time, I encourage you to download the tool and explore its immense usefulness. I must have written 30 or so one-off queries by now. I find new uses for it every day, which makes it a fantastic tool in my opinion and I’m very glad I took the time to write it. I hope you enjoy it and find it just as useful as I have!

P.S. – if you ever get lost, just type LinqFilter –help on the command line with no arguments and a detailed usage text will appear. :)