Android comes with the SQLite database. Although it’s a lighweight DBMS, its authors are sufficiently proud of it that instead of listing what features it has, they prefer to list what features it doesn’t!
In addition to the SQL support we all know and love, Android includes some SQLite features that aren’t really well explained. First and foremost of which is the SQL Query Builder. Although nominally it’s a way to construct SQL without “knowing SQL”, I suspect its more important feature is that it makes it easier to construct SQL from context URIs.
One item, however, which has proven to be really frustrating is the Projection Map. Actually, I’m not even where they pulled the term “projection” from, but Projection Maps have been a real poser for me. I finally broke down and looked at the SQLQueryBuilder source code. A reminder: a ProjectionMap is a Map<String, String>.
Here’s what I found.
If you read the JavaDocs, you’d get the impression that Projection Maps effectively provide a “poor man’s database view” where you can simplify selection request items (projections). In actuality, it’s not so straightforward. Or maybe it is, but the explanation isn’t. It doesn’t help that the literature is rife with projection maps whose sole purpose in life seems to be to convert something to itself: “table1._id” => “table1._id” and so forth.
First of all, you don’t even need a projection map unless you want one. Just don’t set it. If you take that approach, the projection names are processed “as is”, exactly as passed in via the projection array.
Secondly, even the projection array is optional. If you pass null, it generates a “SELECT *” operation. In general, that’s not recommended in SQL, because of the breakage that can occur if columns are added/removed, or re-ordered, but that option is still available if you need it.
Thirdly, the projection map, if supplied applies only to the selection list! In other words, you could map “store_name” => “bookstore.storename”, and it would result in “select bookstore.storename …”, but if you supplied “store_name” in the “order by” query builder parameter or one of the other qualified parameters, you can potentially end up with bad SQL.
Fourth, the projection map can be overruled. If the key part of a projection entry contains an “as” clause, the value part is ignored and the key part is inserted into the resulting SQL. For example “parrot as polly” => “cracker” will generate “SELECT parrot as polly …” and no crackers. The “as” can be either all upper-case or all lower-case (not mixed case) and must have at least one space before and after the word “as”.
Fifth, if you provide a projection map and no projection array, the projection map’s value entries are used to generate the select item list and the projection map’s keys are ignored. In other words, “parrot as polly” => “cracker” will result in “SELECT cracker, but only if no projection array (zero elements or null) is supplied. It’s not a safe thing, however to make a map like “parrot as polly” => “cracker as graham”. At the moment, that would result in “SELECT cracker as graham”, but only if no select array was supplied. But that’s probably an artifact, not an intended behavior.
Sixth, and finally, If you do provide both a projection map and a projection array, each and every element in the array must appear as a key in the map! Any omissions will cause the SQLQueryBuilder to throw an IllegalArgumentException (Invalid column) when it attempts to build the SQL. That one’s mentioned in the JavaDocs, by the way.
Now you know. And so do I.
