Saturday, March 17, 2012

Introducing MonoTouch.SQLite

I've been working on a personal side-project writing an app for the iPad that makes use of SQLite-Net, displaying that data in a UITableView.

Up until this past week, I had been using Miguel de Icaza's wonderful MonoTouch.Dialog library for displaying my data. Unfortunately, I wanted search filtering to be persistent, which means that I really needed to use Apple's UISearchDisplayController but I couldn't find an easy way to retrofit that onto MonoTouch.Dialog's DialogViewController to replace the simpler UISearchBar API that it currently uses. Since I had to look at creating an alternate solution, I figured I might as well solve the other potential problem I had with MonoTouch.Dialog, which is that my app really needed to be able to handle tables with a massive number of items. This brings us to...


MonoTouch.Dialog really made using UITableViews in iPhone and iPad apps trivial and I wanted to try and repeat at least some of that with MonoTouch.SQLite.

The first thing I had to do was to figure out a way of modeling the data in such a way as to allow a generic class to do most of the work for the developer. After a few sleepless nights of hacking last weekend, I figured out a fairly simple approach that seems to work pretty well. I started off thinking that I wouldn't be able to get around having to have a subclassable model, so I made most everything virtual. This is the public API that I came up with:

public class SQLiteTableModel<T> : IDisposable where T : new ()
    public SQLiteTableModel (SQLiteConnection sqlitedb, int pageSize, SQLiteOrderBy orderBy, string sectionExpr);

    // 2 ways of setting the search criteria
    public SQLiteWhereExpression SearchExpression { get; set; }
    public string SearchText { get; set; }

    // Gets the total number of table rows
    public int Count { get; }

    // Gets the number of table sections
    public int SectionCount { get; }

    // Gets the section titles
    public string[] SectionTitles { get; }

    // Gets the row count for a particular section
    public int GetRowCount (int section);

    // Get the index of an item
    public int IndexOf (T item, IComparer<t> comparer);

    // Convert item index into a section and row
    public bool IndexToSectionAndRow (int index, out int section, out int row);

    // Convert section and row into an item index
    public int SectionAndRowToIndex (int section, int row);

    // 2 ways of getting an item
    public T GetItem (int section, int row);
    public T GetItem (int index);

    // Reset the state of the model
    public void ReloadData ();

You can see the full class implementation here.

It turns out, though, that it really isn't necessary to subclass my model unless you want to have finer control over the specific SQL query commands that it makes (all of those methods are virtual).


As I started porting my iPad app to use my SQLiteTableModel class, I started to realize that I could abstract a lot of my usage of the model into a reusable base class. What I came up with will blow your mind.

Are you ready?

In order to populate a UITableView with the contents of an SQLite table, all you have to do is subclass SQLiteTableViewController<T> and implement 1 method:

protected UITableViewCell GetCell (UITableView tableView, NSIndexPath path, T item)

That's it.

I've written up a sample iPhone app that illustrates just how easy this is.

But wait! There's more!

If you order in the next 30 minutes, you can also get this free complimentary Sham-Wow!

Okay, just kidding about that Sham-Wow! bit, but I wasn't kidding about there being more:

Remember when I said one of the problems I wanted to solve was persistent search filtering? Yea, well, I did it. SQLiteTableViewController handles all of that for you as well. In fact, give searching a try in that sample above.

At this point I bet you're thinking, "wow, how could this get any better?"

I'll tell you. Remember how SQLiteTableModel had 2 methods for setting the search criteria? Well, the one that takes a string parses it to create a SQLiteWhereExpression allowing the user to match against specific fields. For example, if you had the following data item:

public class Contact {
    public string FirstName;
    public string LastName;
    public string PhoneNumber;
    public string Address;
    public string Comments;

...the user could type:

address:"Newton, MA"

and SQLiteTableModel would construct a query to match "Newton, MA" against only the Address field.

If the user, instead, types:

address:"Newton, MA" firstname:Jane

then the matches that would display in the list would be limited to contacts with a first name of "Jane" who live also in "Newton, MA".

I've also taken the liberty of implementing a SQLiteSearchAliasAttribute that allows you to specify aliases for your fields (or even the same alias to multiple fields!). For example, you could do this:

public class Contact {
    [SQLiteSearchAlias ("first")][SQLiteSearchAlias ("name")]
    public string FirstName;
    [SQLiteSearchAlias ("last")][SQLiteSearchAlias ("name")]
    public string LastName;
    [SQLiteSearchAlias ("phone")]
    public string PhoneNumber;
    public string Address;
    public string Comments;

This would allow your users to use "name" to match against either FirstName or LastName!

It also means they can type "first" instead of "firstname" to match against only the first name, as in the above example.

Where Can I Find This Awesome Library?

Glad you asked! You can find it on my GitHub page: MonoTouch.SQLite.

Well? What are you waiting for? Get hacking!


Anonymous said...

hi, thanks your so good demo.

but how can i add UINavigationBar in your demo?

Anonymous said...

Excellent info. Love it when things work out. I'll definitely share this with one of our developers who is a big SQLite fan.

Jonathan Allen said...

Wow, this is pretty amazing stuff. When I was trying to write the news report for InfoQ I kept getting distracted by thinking of ways I can use your ideas in my Silverlight application.

Jonathan Allen

Hugo Logmans said...

Nice work, could implement this quite easily. But now I need to add a fixed where-clause (get all items with a certain parent_id) I don't understand where to start...

Jeffrey Stedfast said...

Hi Hugo,

You have a couple of options:

1. You could use the search API that sets the where-clause directly instead of using the SearchText property. You would have to do your own parsing of the search text, but that might not be hard, depending on your needs.

2. You could override (or just modify the original SQLiteTableModel code directly, since it's open source) SQLiteTableModel to generate your own commands with a fixed where-clause in there.

3. You could cheat by taking the user-entered text and append or prepend: "parent:id" where "id" is replaced with whatever parent id you need. This is the equivalent of tacking on an 'and' expression to the overall 'where' expression.

Hope that helps.

Hugo Logmans said...

Thanks Jeff. Imho option 2 was the best, and I implemented that just by adding a "fixedWhereClause" property (string). This is non-intrusive because it is fully optional to use it. Next I will add the possibility to set a table name overriding the detection. This way any sqlite-view can be used. Combined with the where-clause even complex queries can be performed using your flexible code. Want me to provide the code?

Hugo Logmans said...

Is this code affected by the MT4112 problem?

"Note that generic subclasses of exported types is not a supported scenario, and
may fail/crash randomly.". I get those crashed. MT 6.0.2 gave an error about this. I fixed it, but it still seems to be unsupported...

The problem is the SQLiteTableViewController class, because it is a generic subclass of UITableViewController...

Jeffrey Stedfast said...

Hi Hugo,

I haven't had the MT error, so I don't know. It looks like that is just a warning in the latest MonoTouch 6.0.3+ releases.

JeffScanco said...

Awesome controller! It make using sqlite so easy, especially for large datasets. However, I'm running into a crash issue (SIGSEGV) the second time I open my SQLIteTableViewController:
mono-rt: Stacktrace:

mono-rt: at <0xffffffff>

mono-rt: at (wrapper managed-to-native) MonoTouch.Foundation.NSObject.monotouch_release_managed_ref (intptr)

mono-rt: at MonoTouch.Foundation.NSObject.ReleaseManagedRef () [0x00000] in /Developer/MonoTouch/Source/monotouch/src/Foundation/NSObject.cs:98

mono-rt: at MonoTouch.Foundation.NSObject/NSObject_Disposer.Drain (MonoTouch.Foundation.NSObject) [0x00062] in /Developer/MonoTouch/Source/monotouch/src/shared/Foundation/NSObject2.cs:545

mono-rt: at (wrapper runtime-invoke) .runtime_invoke_void_object (object,intptr,intptr,intptr)

mono-rt: at <0xffffffff>

mono-rt: at (wrapper managed-to-native) MonoTouch.UIKit.UIApplication.UIApplicationMain (int,string[],intptr,intptr)

mono-rt: at MonoTouch.UIKit.UIApplication.Main (string[],string,string) [0x0004c] in /Developer/MonoTouch/Source/monotouch/src/UIKit/UIApplication.cs:38

mono-rt: at RSO.MT.Application.Main (string[]) [0x00000] in /Users/TechMac6/Projects/Sales/RSOTouch/Main.cs:17

mono-rt: at (wrapper runtime-invoke) .runtime_invoke_void_object (object,intptr,intptr,intptr)

Native stacktrace:

Got a SIGSEGV while executing native code. This usually indicates
a fatal error in the mono runtime or one of the native libraries
used by your application.
I was able to reproduce the crash in your sample by pushing a UITableViewController first with a single UIButton that pushes an instance of the SQLiteTableViewController. press the back button and try loading the SQLIteTableViewContrller again and sqlite aborts. It seems to be a memory issue. Any ideas?

Code Snippet Licensing

All code posted to this blog is licensed under the MIT/X11 license unless otherwise stated in the post itself.