SQLite.Net ORM with Task Parallel Library in Xamarin

I've recently had the opportunity to work on a Xamarin.iOS project with Envoy. Having previously built iOS applications with Objective C I've been enjoying the chance to investigate techniques to share code across platforms.

With Xamarin, you can share some common code between Android and iOS projects if you structure it in a certain way. One technique that is commonly used to share code is to use the SQLite.NET ORM with SQLite rather than using Core Data or other device specific data access API's. Xamarin themselves have used this for an application which they've open sourced as example code: MWC 2012 

In that application, they create a subclass of SQLiteConnection with a static constructor that initialises a static variable to hold a reference to itself, as a kind of singleton. I followed this pattern in my code, however I've since discovered that this is perhaps not the best approach in all circumstances.

My application makes use of the Task Parallel Library to do some web service calls and subsequent database updates on a background thread. Much like the MWC sample I wrapped my calls to the database with a lock so that multiple threads won't update the database at once, and the application appeared to work correctly. However at various stages during my testing I came across seemingly random crashes, where the application which had been working fine would crash out of the blue with an error similar to the following:

mono-rt: Stacktrace:

mono-rt: at <0xffffffff>

mono-rt: at (wrapper managed-to-native) SQLite.SQLite3.Prepare2 (intptr,string,int,intptr&,intptr)

mono-rt: at SQLite.SQLite3.Prepare2 (intptr,string)

mono-rt: at SQLite.SQLiteCommand.Prepare ()

mono-rt: at SQLite.SQLiteCommand.ExecuteNonQuery ()

mono-rt: at SQLite.SQLiteConnection.Execute (string,object[])

This isn't particularly helpful and originally made me worried that perhaps the SQLite.NET ORM wasn't stable. However after some more research I've discovered that others were having the same problem and that they believed it was caused by the SQLite connection being accessed on different threads, even if it's not at the same time. When you think about it, of course this is an issue - the subclass of SQLiteConnection shouldn't be a singleton, it's a connection similar to those seen in ADO.NET and should be thread specific and closed when not needed. I believe the MWC application musn't have ran into this issue as they were only doing updates to the database on certain threads, not leaving it up to the Task Parallel Library.

So now, instead of having a singleton in my SQLite Connection, I'm using it in the IDisposable fashion it is designed to be used so that a connection is opened and closed with each query.

using (var db = MyDatabse.NewConnection())
{
    db.Insert(itemToInsert);
}

I've since stopped having the random application crashes. Hopefully this helps someone out who is having the same issue.

Styling Two or More Fusion Tables Layers in Google Maps

The Google Maps API has a feature called 'Fusion Tables Layer' which lets you overlay data from a Google Fusion Tables. Using this feature, you can upload some KML data into a Fusion Table and see it represented on the map (for example, you can highlight an area on a map to represent some sort of data). The Maps API has in built support for using KML directly, however KML files can get quite large for complex data, so using Fusion Tables is a high performance alternative (Google handles large data sets with ease) This is what my exmaple Fusion Table with KML data uploaded looks like:

You can display markers, polygons, and polylines on a layer and can style each of these as you wish using the options supplied to the API call. An example of the code to do this is available here.

If you have multiple bits of data to show, you can add multiple layers from a range of different Fusion Tables. However, there is one limitation, which is pointed out in the documentation:

Styles can only be applied to a single Fusion Tables layer per map. You may apply up to five styles to that layer.

I recently ran into this limitation when trying to overlay two separate (complex) data sets. For my use case, I wanted to show data from two different Fusion Tables with two different styles, which means I was out of luck. I tried a number of workarounds to avoid this (seemingly odd) limitation. I attempted to combine them into the one table, but the queries don't allow OR statements,  I then tried using my own KML file, however that came out larger than 1MB, which loaded very slowly, whereas the Fusion Tables layer handled that data with ease.

I then attempted to add styling information to the KML file before uploading it into the Fusion Table interface (as KML supports this), however the upload process strips that information out. It was while searching for a way to get around this that I found the solution. I discovered that in the Fusion Tables interface you can add your own map, style it there, and it will give you code to easily embed it. This is designed to allow those without coding skills to style and embed a map on a blog or web page. Great! I thought, a bit annoying that I have to store that information in an external dependency instead of in my own code in source control, but at least I can solve this problem (I could perhaps use the Fusion Tables API to set this style from within my application, at the cost of a few HTTP requests).

However when I went to find this map tab that the documentation mentions, it was greyed out...

There's no mention that I could easily find on the page explaining why it was greyed out, but I had a hunch. Given how terribly Google has dealt with Google Apps accounts, I figured that seeing as I was signed in with a Google Apps email, it might have something to do with that. Signing into my personal Gmail revealed that my hunch was indeed correct, I can add a map (you may need to give that account write permissions or copy the table to your personal account).

Sigh. Some searching uncovered some documentation on this. You have to submit a request to Google asking them to turn it on. Would be nice if they just mentioned this in the interface...

Once you've added a map, tools > change map styles will let you style it.

 

You're note quite done yet though, simple embedding this table into the API won't do it, you need to tell the API which style and which template to use in your map. There's some documentation on getting the style number and setting it in the Maps API here. I couldn't find an easy way to get this Id from the API, but it's probably a small number if you just want to try numbers up from 1,2,3,4 etc. Next you need the template ID, which you can find by clicking on the map tab's dropdown and clicking publish. At the end of the URL the querystring will have the template ID as 'tmplt'. 

You then pass these through in your maps API call like so:

 gridLayer = new googleMap.FusionTablesLayer({

                            query: {

                                select: 'geometry',

                                from: fusionTableId,

                            },

                            map: map,

                            templateId: 2,

                            styleId: 2,

                            clickable: false

                        });

You should them see your styled map embedded in your page. Simple... right?