Loading...
Menu

SQLite Database Programming for Xamarin: Cross-platform C# database development

SQLite Database Programming for Xamarin

Anthony Serpico

Table of Contents

About this book

Conventions used in this book

Code formatting

Who this book is for

How to use this book

About the author

Personal note

Feedback

Part 1. Data persistence for mobile apps

The nature of mobile applications

SQLite

Offline, online, and occasionally connected apps

Is this some kind of joke?

Do I really need all of this?

Understanding Where HotRiot fits in

Database design considerations

Designing for SQLite

Primary keys and foreign keys

Structuring your database

Getting SQLite.XM

Creating an application skeleton

Platform specific code

Part 2. Data storage on the device

Describing your apps databases

Schema initialization

Using the SxmTransaction class

Multi-database statement processing

Understanding transactions

Transaction visibility

Performing joins across databases

Locking and concurrency

Serializing transactions using the SxmSTransaction class

Dynamic Queries

Making schema changes

The SxmConnection class

Part 3. Data synchronization to the cloud

Understanding SQLite.XM synchronization

Setting-up a server-side infrastructure

Synchronization details

Synchronizing files

Coordinating synchronization

Customizing synchronization

Synchronizing to your own private back-end server

Appendix A – SQLite Error Codes

SQLite Database Programming for Xamarin

© 2015 Anthony Serpico

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means without the prior written consent of the author, except in the case of short quotations embedded in critical articles or reviews or certain other non-commercial uses permitted by copyright law.

Every effort has been made in the writing of this book to ensure the accuracy of the information presented herein. However, the information in this book is without warranty, either express or implied. Neither the author, publisher, dealers, nor distributors of this book will be held liable for any damages caused or alleged, either directly or indirectly by this book.

About this book

h2<>{color:#2e74b5;}.
Conventions used in this book

The following typographical conventions are used.

Fixed width: Used for code listings, including SQL statements. Also used inside paragraphs when referring to program elements such as function names, variable names, environment settings, and keywords.

Bold: Is used (sparingly) to highlight or emphasize some important point or to enhance readability.

Fixed width italic: Used to show text that is to be replaced with user supplied values.

An example code listing is shown below.

public void executeQuery (string command, ArrayList parameterValues)
p.  {
p.    try
p.    {
p.      if (command == null)
p.        throw new Exception (ErrorMessages.error [“missingSQL”]);

      if (connCommand == null)
p.          connCommand = dbConn.CreateCommand ();
p.      else
p.          releaseDataReader();

1     connCommand.CommandText = command;
connCommand.CommandType = System.Data.CommandType.Text;
2     addCommandParameters (parameterValues);
3     connDataReader = connCommand.ExecuteReader ();
p.    }
p.    catch (System.Exception ex) 
p.    {
p.      logger.log (ex, System.Reflection.MethodBase.GetCurrentMethod ().ToString ());
p.    }
p. }

Code listings are shown with the default color coding used by Xamarin Studio at the time of writing. Paragraphs that explain listings will reference line numbers that are shown in the left column of the listing. Referencing line numbers do not necessarily start on the first line of code. Code listings are mostly devoid of basic error checking, error processing, rudimentary optimizations or anything else that might increase their length or otherwise detract from their primary purpose.

h2<>{color:#2e74b5;}.
Code formatting

I’ve worked hard to make the code examples as easy to read as possible. Most are very short, so that helps a lot. Unfortunately, the myriad of screen sizes and devices for eBooks makes formatting uncertain at best. Code that displays nicely for one configuration may prove unreadable for others.

It has been my experience that reading this book from a tablet in landscape orientation provides the best view. If you find the code examples difficult to follow due to formatting, you can try changing the font size to see if that helps. Often times, this is enough to set things right.

h2<>{color:#2e74b5;}.
Who this book is for

This book is designed to be read by mobile app developers that are using or intend to use the Xamarin framework and want to learn how to use the SQLite.XM library. In order to get the most out of this book, you’ll need a basic understanding of C# and have at least some general knowledge of SQL. There is no complex SQL or C# in this book, so a basic understanding of each is sufficient to get you through. Of course, the more you know the better. If you’re a Java developer, you should have no problem with the C# examples. Having a basic understanding of Android or iOS development concepts will also be helpful. Again, there isn’t any complex Android or iOS employed by any of the sample code. For the most part, it’s pretty simple stuff. I try and stay on point and focused on SQLite, SQLite.XM and the best database practices for mobile development.

h2<>{color:#2e74b5;}.
How to use this book

This book is intended to be more than a simple guide to using SQLite.XM. I try and give advice and make suggestions, and, where appropriate, provide you with a rational for making certain engineering choices. Every project is different, and the choices you make may very well differ from my advice. My hope is that by providing insight into my thinking, I will help you with questioning your own decisions. Sometimes you’ll agree with my advice and suggestions and other times you won’t. Either way, it doesn’t really matter, so long as I promote critical thinking in weighing your own design options, I will have done my job. There is rarely one right way when it comes to coding.

In Part 1 of this book, I spend some time talking in general terms about data persistence along with issues specific to mobile app development. I also spend a bit of time going over the various technologies used in the book to build our data persistence layer. I then go on to discuss database design and challenges specific to designing for mobile apps.

Part 2 is dedicated to explaining how to use SQLite.XM for data persistence on the device. This is the heart of the book. If you have some experience with mobile app development, and know a bit about relational database design, you may choose to skip Part 1 and jump to this section to get right into the specifics of SQLite.XM. If you do decide to skip ahead, be sure to first read the section ‘Platform specific code’ as it has some instructions that you need to be aware of.

Part 3 is where I explain SQLite.XM and cloud replication; how to set up a server-side database and how to get data moved, or copied, from the device to your cloud server. If you don’t need to replicate to a server, you can skip this section.

h2<>{color:#2e74b5;}.
About the author

I have been a professional software developer since 1986 doing work at IBM, The Nuclear Regulatory Commission, for Bell Laboratories and at a host of smaller firms, including the startup Sezme. I have had the opportunity to work a broad spectrum of computing, from low level embedded systems development in assembler to JVM and .net CLR virtual machine programming in Java and C#. I even had the opportunity to dabble in microcode for a brief period of time. My early career was focused on computer graphics, including OpenGL, digital image processing, and image compression. I developed a proprietary spatial domain compression algorithm intended to be used where traditional cosine transforms proved computationally too expensive. A bit later I was the lead developer for SPG in the creation of the image editor ColorWorks, which was delivered on the OS/2 and Windows platforms. ColorWorks was one of NASAs favored tools for color correcting/enhancing images taken with the Hubble telescope. I later focused my attention on database and Web work for the creation of HotRiot. HotRiot is an SaaS that provides back-end services for mobile and Web applications. I am currently the technical lead in the development of a mobile messaging app for Android and iOS using Xamarin and SignalR.

h2<>{color:#2e74b5;}.
Personal note

This is my first foray into publishing. I opted to self-publish because I wanted to provide detailed subject matter coverage beyond what is possible in a blog or other shorter venue, but still offer my writing free of charge. This would not have been possible with a publisher. I have been asked a number of times; why free? It’s true that writing a book, even a shorter one such as this, take a considerable amount of time and energy. Not to mention the work put in by proof readers and tech reviewers. I was surprised by how hard it was to get to a first revision. I have a new appreciation for what authors go through in getting their works published. But I wanted to contribute back into the tech community. Over the years, I have benefited from countless resources made available by others; including blogs, programming help, and open source code. This was an opportunity for me to make a contribution back. If this book is well received, I will follow-up with another. I have learned a lot about mobile messaging with SignalR that I would like to share. If I publish again, I will try to continue to offer my books for free. Thanks for choosing to read this book and I hope you learn and enjoy.

h2<>{color:#2e74b5;}.
Feedback

If you have any feedback related to this book, for example, errors, suggestions etc., please let me know. I will continuously update the online manuscript as I make edits, this should improve the experience for future readers. Your help with this is greatly appreciated. I’m always happy to assist with SQLite.XM. If you need some help or advice or need something clarified, please drop me a line I’ll do my best to help.

[email protected]

Part 1. Data persistence for mobile apps

h2<>{color:#2e74b5;}.
The nature of mobile applications

In traditional client-server computing, a client normally connects to a central database server, often through a front-end proxy, for data storage/processing services. If that connection cannot be established, the application is normally unable to run. For the most part, this is okay. The way these applications are designed, packaged, and distributed, this makes sense. This traditional model relies on a continuous connection to a middle tier. For the type of tasks these applications perform, this is all well enough and it helps to simplify their design. Think of any Web based application of significance or any typical enterprise application. It’s true that HTML 5 offers a number of offline storage options, but these are not widely used. The storage technologies that are available are somewhat limited in their capabilities and browser support is non-uniform. Having to deal with the myriad of browser combinations is enough to dissuade most developers from using one of the structured storage solutions offered in HTML.

For mobile applications, a more flexible model is needed. First, network connectivity for mobile devices is much less certain. Not only do connections go in-and-out with much greater frequency, their quality and speed can vary greatly. So, relying on an external database can be sketchy. Also, the type of tasks that mobile apps are called on to perform, along with user expectations, require that they have much more autonomy than their Web or enterprise counterparts. Users will not tolerate spotty service from their mobile apps. With this in mind, mobile applications should be designed to operate without a network connection to the fullest extent possible. This will almost always require some kind of structured data storage solution on the device. Fortunately, a database infrastructure is present on the most popular mobile operating systems. Both iOS and Android ship with a relational database pre-installed: SQLite.

SQLite

SQLite is the most widely deployed relational database in the world, by far. It is included in virtually every iOS and Android device. If you count the number of active devices as measured by Google and Apple, this adds up to several billion. No other database comes close.

Unless the data storage requirements for your app are extremely simple, you’re going to need a storage solution beyond what you can easily implement yourself in a simple key/value structure or basic properties file. SQLite is a very attractive option as there’s no setup required for iOS or Android and its presence is guaranteed on both OS’. This simplifies packaging, deployment and installation for your app, removing any friction that might be caused by installing and managing a separate, embedded database server. Also, knowing that SQLite will be available, allows you to write a common, shared data persistence layer that will work across platforms when using Xamarin. While it is unfortunate that Microsoft doesn’t include SQLite as part of Windows Phone, it is, nonetheless, easy enough to install using NuGet.

Now, you might get the sense that I’m enthusiastic about SQLite simply because it’s convenient. While it is convenient to use, SQLite qualifies as full featured relational database, putting all of the power of a traditional transactional database engine at the disposal of your app. You would be hard pressed to find a better datastore for your mobile application.

I’m not going to go into detail about SQLite beyond what is needed to use our persistence layer, SQLite.XM. However, I encourage you to explore SQLite in more detail. Their website, sqlite.org, has a wealth of information.

Offline, online, and occasionally connected apps

Mobile applications generally follow one of three models:

*
p<>{color:#000;}. Pure Offline Application

*
p<>{color:#000;}. Pure Online Application

*
p<>{color:#000;}. Occasionally Connected Application (OCA)

Pure offline applications don’t require any network connectivity in order to operate. The datastore is local on the device and all data and application logic resides locally. Data that’s generated by the application is not synched with an external or back-end server. These type of mobile application are entirely self-contained and self-supported, and are a bit rare. It is somewhat unusual to see pure offline mobile applications.

In contrast, pure online applications require a network connection in order to function. These apps may include a local database, but this is not a requirement. Even if a local datastore is used, it’s generally limited to such things as configuration information or other metadata. These apps establish regular or continuous communications with a remote server that may, in addition to providing database services, also provide supporting application logic.

An Occasionally Connected Application (OCA) represents a middle ground between the offline and online model. These apps can operate for extended periods without a network connection but eventually need connectivity in order to complete their processing cycle. They almost always include a local database and normally synch their local data with a back-end server. Even though these apps synch their local data, OCAs don’t rely on a remote datastore in order to function. These types of apps are designed to provide a base level of functionality when disconnected with full functionality made available when connectivity is established. OCAs may also rely on a back-end server for supporting application logic. Of the three models, OCA is the most flexible and complex to design and code.

With respect to which of the three models you should choose, the answer is easy if your application truly qualifies as an offline app. In this case, simply use the offline model. This is the easiest of the three to develop and there is little benefit to complicating things. Consider yourself lucky, you don’t need to deal with intermittent connectivity issues, remote communications, synching or any of a host of problems that come along with client server computing.

Unfortunately, it is much more likely that you’re creating an app that follows the online or OCA model. In this case, things can get a bit murky. In most instances, an online mobile application can be designed as an OCA, and vice versa. Actually, I think it would be more accurate to say that most online mobile apps are really misidentified OCAs.

Let us look at an example. Imagine you are creating an app for a specialty merchant that’s similar to the Amazon price comparison application. The app searches for items in inventory via a barcode scan and displays price comparisons. For this example, let’s say this is the only feature in our application. This would seem to be an online application, as the app needs to communicate with a back-end server in order to locate the scanned product information. This is true. But what are you going to do if there is no connectivity? You could simply error out of a price check request by displaying some status message to the user. Not such a great outcome. The other option is to offer to save the request locally until network connectivity is reestablished, at which time, the app will perform the search and present the results to the user. Much better! Our seemingly online application, when thought through, is really an OCA. When there is no network connectivity, it still allows users to perform product scans. The app completes its processing cycle, searching and displaying price comparisons, when connectivity is reestablished.

As a general rule, it’s better to choose the OCA model whenever possible. If you think your app qualifies as a pure online application, think again. Really think through the various use case scenarios. In most instances, you will find your application is better designed as an OCA. While this does add complexity, it also provides for the best user experience.

Is this some kind of joke?

A complete cross-platform data storage solution for a typical OCA requires a number of technologies working together. The reason for this traces back to the need for OCAs to function offline and online and to be able to synch. This requires an infrastructure that can support a “store and forward” architecture for the application data. This, in turn, necessitates a database on the device, a server-side database, and a middleware tier to link the two. In our case, we want our solution to be cross platform, so we’ll also need a cross platform development framework. Admittedly, the reasons for choosing a cross-platform solution goes beyond the data layer, but this is nonetheless a requirement. It would also be nice to have an abstraction layer that simplifies working with SQLite and that include the logic for performing synching. If you put this all together, you end up with a list that looks like the one below

*
p<>{color:#000;}. SQLite: Our local device datastore.

*
p<>{color:#000;}. SQLite.XM: Our SQLite abstraction layer, which includes support for data synching.

*
p<>{color:#000;}. A server-side middle tier: For the examples in this book, we’re using HotRiot.com.

*
p<>{color:#000;}. A server side database: Either a traditional relational database, for example, MySQL, or a NoSQL document datastore, such as MongoDB.

*
p<>{color:#000;}. Xamarin: Our cross-platform framework.

The list above is quite a combination of technologies. It includes a device-side and server-side database, a cross-platform development framework, a SQLite abstraction layer with synching logic, and a server-side middle tier. That might seem like a bit much just to support data persistence. But it turns out, to fully implement a cross-platform OCA, they are all needed.

Let’s have a look at where each of these components fits in.

SQLite is light weight, server-less traditional relational database. By “traditional”, I mean a database that supports transactions with ACID compliance, joins, triggers, implements standard SQL, etc. As the name implies, SQLite is a database implementation designed as a light weight solution with a small footprint intended to run on devices with limited resources.

iOS and Android both have a SQLite database engine built in as part of their platform. So there’s nothing you need to do to make SQLite available to your application for these two OS’. Windows Phone offers SQLite as an extension that’s simple to install from Visual Studio. Most mobile applications need to save at least some data locally. SQLite is a natural choice as it is readily available for mobile and very capable.

SQLite.XM is a SQLite abstraction layer written in C# and designed to be used with apps developed using Xamarin. It makes using SQLite with Xamarin a snap. It shields your application from the details of working directly with SQLite. Instead, you app interfaces with a high level, cross-platform layer that provides database CRUD functionality. In addition to providing an abstraction layer that greatly simplifies database searching and record retrieval. SQLite.XM also takes care of organizing your SQL, initializing your apps schema, performing schema updates, and synchronizing data to a server-side database.

At this point, the obvious question is: where does SQLite.XM synch device data? SQLite.XM comes out of the box ready to synch with HotRiot.com. HotRiot is a hosted server-side platform for mobile application; see the next section for more details. SQLite.XM is also capable of communicating with any server for the purpose of data synching. It allows you to insert your own delegate method into the synchronization processing chain for the purpose of writing an interface to any cloud based server of your choosing. I go into detail explaining this is part 3 of the book; “Data synchronization to the cloud.”

A server-side middle tier: An OCA needs to synch its device data to a server side datastore. This is taken care of by SQLite.XM on the device side. However, a middleware server is needed in order to actually write the data into to the database. As a general rule, you never let the device connect directly to the server database. Instead, requests are made to a middle tier which then performs the database operation on behalf of the client. However, there is more to this. We don’t just synch data to the server for the sake of synching. We do it to get information over to the server in order to support functionality needed by the app, which requires the support of a back-end.

Just reading and writing to a back-end database such as MySQL or Mongo will require middleware support. But there is a host of other services that many apps also require, for instance:

*
p<>{color:#000;}. Push notifications through Apple Push Notification Service or Google Cloud Messaging.

*
p<>{color:#000;}. Provide a central repository for apps that need to share data generated between devices’

*
p<>{color:#000;}. Document management support. Such as generating thumbnails or moving file attachments to a key/value store such as AWS S3.

*
p<>{color:#000;}. Support for event triggers. For example, contacting a user when a record is stored in the database that includes some information of interest.

*
p<>{color:#000;}. Bulk email processing for contacting all of users by email.

*
p<>{color:#000;}. Managing permissions for security.

*
p<>{color:#000;}. Sending email confirmations, for example when a user registers with an application.

This is not an exhaustive list by any means but it highlights a number of services requiring a back-end infrastructure. Now, there are several ways to approach this. You could build out your own server-side infrastructure. However, this is a lot of work to do correctly and adds little to differentiate your application. The back-end, while critical, is largely invisible to end users. Your users are never going to love your app because you did a great job developing the server-side. They will only notice when it doesn’t perform. It is a ton of work to write the code, deploy servers and provide ongoing support. Not to mention all of the time this adds to your development cycle. This can really get expensive. There is a better way. Find a good hosted service and use it!

SQLite.XM comes ready to work with HotRiot.com. To reiterate, HotRiot is a hosted server-side platform for mobile applications. It includes support for all of the bulleted items listed above and more. The HotRiot C# Xamarin API is built into SQLite.XM, so there is nothing you need to do in order to use SQLite.XM with HotRiot. Of course, SQLite.XM doesn’t just work with HotRiot, it is designed to work with any back-end. I show you how to do this in part 3 of the book.
[
A server-side database:] We are going to need a server-side database where we can synchronize the local device data. This can be either a traditional relational database or a NoSQL document database. You are probably wondering why I specifically state that the server datastore should be either a relational or a NoSQL document database. The reason is simple. SQLite is a traditional relational database. Synching with another relational database or a NoSQL document database such as MongoDB, will provide the best device-to-server translation. While SQLite.XM will support synching to any back-end, a server-side database whose data model is significantly different from the data model on the device can make synching difficult. It is not unusual to have a schema on the server that is different from the schema on the device, and SQLite.XM fully supports synching to different schemas. However, when the fundamental data models between the device and the server get too far apart, it becomes difficult to perform a rational, workable translation. For example, as a generally rule, it would be less than ideal to try and synch your apps data with a key/value datastore. Of course, depending on the particulars of your app and what you intend to with your data once it is synched, there are may be legitimate reasons for breaking this rule. But it would be unusual.

HotRiot includes support for MySQL and MongoDB.

It would be fair to ask why include two server-side database options when they perform a similar function. The short answer is that each of these two databases have strengths and weaknesses. To only support a relational database or only a NoSQL document database would limit us to trying to fit a solution where it might not be best suited.

Xamarin is our cross platform development framework of choice. It allows us to create native Android, iOS, and Windows apps using a mostly shared C# code base. Xamarin lets you to write the vast majority of the non UI application logic using shared code. The platform specific layer is typically much smaller and generally encompasses the user interface code. According to Xamarin, the average application written using Xamarin shares about 65% to 70% of the app code across all three development platforms. This greatly reduces the programming effort needed to support the most popular mobile devices. Given these strengths, Xamarin is a natural choice for creating a cross platform persistence layer. The other option is to create a separate data layer for each of the platforms we wish to support. That would require coding in Objective-C, Java and C# in order to support iOS, Android and Windows. Using one language to create a single shared persistence layer allows us to focus on the problem we wish to solve, without having to spend too much time on platform related issues.

This is not a book intended to detail Xamarin development. There are other good resources for that. I explain how to use Xamarin to the extent needed to create our data persistence layer. Beyond that, Xamarin is largely ignored. This allows us to stay focused on our data persistence solution. You don’t need to be a Xamarin developer to follow along. We will explain enough Xamarin to get you through.

Do I really need all of this?

The short answer is; probably. If you are developing an OCA, then definitely. For a pure offline application, you may only need local database support, and depending on what you build, you may not even need that. For pure online applications, you will probably need the entire stack except the synchronization logic. However this is all highly dependent on what you’re building.

Understanding Where HotRiot fits in

We are going to be synching local data generated by our application to a central back-end database provided to us through HotRiot. This database could be used for a variety of purposes, but this really needs to be thought of in broader terms. When you synch, whether to HotRiot or some other back-end, you’re not just synching to a database, what you’re really doing is synching with a server-side platform that provides support services to your application.

As stated earlier, HotRiot provides mobile applications with a server-side platform in the cloud. HotRiot includes a set of graphical tools that are used to create an infrastructure for the server side portion of your application, making it a breeze to create tables or collections, design queries, set-up permissions, create triggers, etc.

For the moment, the most interesting aspect of our choice for a back-end server solution is the fact that all HotRiot accounts come with a provisioned SQL (MySQL) and NoSQL (MongoDB) database. This allows you to choose which type of server-side datastore best fits your needs. Another interesting feature of HotRiot is that it can use your own personal MySQL or Mongo database for storing your data; this is instead of using a HotRiot managed database server. More on this later.

Database design considerations

The database on the local device is SQLite. This is a traditional relational database. From a data modeling perspective, the most influential feature of a relational database is their support for joins. This single feature, more than any other, dictates the fundamental structure of the data for a relational database. The device side database should be designed independent from the database used on the back-end server where the local data will be synched. But this presents a potential problem. If we wish to use MongoDB or some other NoSQL database as our back-end datastore, instead of relational database, how do we deal with the fact that most NoSQL databases do not support joins. And, as a result, require a fundamentally different structure from our local database?

Unfortunately, there is no easy answer but that doesn’t mean the problem cannot be managed. One of the factors that works in our favor is that the local datastore for mobile apps tend to be relatively simple when compared to larger enterprise applications. Although this is not always the case this does generally hold true, and can help to make the translation from a relational model to a document model easier.

Let’s take a moment to discuss MongoDB, MySQL, and HotRiot.

When you create your back-end database in HotRiot for the purposes of synching, you are given the option to use either MySQL or MongoDB for the underlying storage engine. MySQL and MongoDB are both very capable databases, each offers features that might make one or the other more appropriate in any particular circumstance. HotRiot allows you to use both Mongo and MySQL simultaneously, although I generally don’t recommend this. While HotRiot has done a lot of work to smooth out the differences between these databases so that you have a very similar user experience, these are, nonetheless, different database technologies. Thus, intermingling them within the same application is generally not recommended.

MySQL and Mongo are fundamentally different, one being a document store and the other using the traditional relational model. Even so, for the vast majority of mobile application, either one is fully capable of meeting design requirements. While there are many subtle and not so subtle differences between MySQL and Mongo, most of them are not significant to the point where they render one or the other unusable for most mobile apps. However, there are few significant differentiators that should carefully be considered when deciding which one to use. I highlight the ones I think are important, this list is surprisingly short.

Transactions: If your application requires transactions, then MySQL is realistically your only choice. This is a true requirement less often than you might think. However, for money transfers and some other complex operations, transactions can be indispensable.

Joins: If you think your data model can benefit from joins, then MySQL is your better choice. Mongo does not support joins and performing them in your code is not recommended. Do not attempt to do the job of the database, instead, select right database. I know that joins have fallen out of favor lately, especially with the rise in popularity of NoSQL databases. I will tell you without hesitation, I really like the capabilities that joins bring to the table, especially the flexibility in data modeling that joins permit. The problem with joins is in how they are used, not with joins themselves. When used correctly, joins are performant and flexible.

Scalability: If you’re expecting to generate huge amounts of data that needs to be part of the apps transactional data set, then Mongo may be your better choice. Mongo is designed from the ground up to support horizontal scaling. If you expect your data set to grow beyond what can be supported by a single database server, then Mongo could have some features that tip in its favor. But this is not the usual case. I know that it is popular these days to talk of supporting “Web scale,” but when looked at objectively, only a very small percentage of apps require anything near this level of scalability. You would be amazed at what a single, powerful database server can handle. For example, I have a good friend that is part owner of one of the more popular dating sites on the net. At any one time, his site can have tens of thousands of users online. Even with a site this large, his database hovers around 80 GB, not including file attachments, which are stored outside the database. Now, he doesn’t allow junk to accumulate in his database, otherwise it would grow to an unmanageable size. So he regularly culls the data in order to keep the working set down. This is smart. There is no point in keeping garbage in your transactional datastore; either delete it or move it to offline storage. His site uses MySQL hosted on AWS.

Here is my advice: Use MySQL unless you have a specific, articulable reason for using Mongo or some other NoSQL solution, for example, you expect to need horizontal scaling. Traditional relational databases are unmatched in their power and flexibility.

Designing for SQLite

Okay, let us get back to discussing how to design your local database. I will show you how to do a design that can be used for synching to either a traditional SQL database or a NoSQL database.

Any application of significance is going to require a database design that is composed of multiple tables. Remember, were designing for the device, which uses a traditional SQL database. The information in different tables is usually related in some way and needs to be linked in order to effectively process the data produced and consumed by your application. The term relational, the way it is being used here, refers to the fact that the information between tables is connected in some way. The data in one record can be used to find additional related information in other records that exist in other tables. This is usually done using key fields. This allows you to create complex interrelationships between tables that can be used to accommodate a wide variety of requirements.

[] Primary keys and foreign keys

Before we go any further, we need to explain the concept of primary keys and foreign keys. A primary key is a field which uniquely identifies a record in a table. A foreign key is a field which identifies a record in some other table. A foreign key field contains the primary key value of the record it references in the “foreign” table, thus providing a direct link to the record. A table can have multiple foreign keys but only one primary key.

For example, consider the two tables below:

*
p<>{color:#000;}. Registration Table

*
p<>{color:#000;}. Job Posting Table

Both the Registration and Job Posting tables contain primary key fields. In this example, the primary keys are named ‘Registration PK’ and ‘Job Posting PK.’ I like to use a naming convention where I append ‘PK’ to a primary key field name. This makes it easy to identify the field. The job posting table also contains a foreign key named ‘Registration FK.’ As with primary key fields, I like to append ‘FK’ to the name of a foreign key field. This field is the key that connects the records in each table. A record in the registration table can be used to find all jobs posted by a company by searching the job posting table on the Registration FK field. Conversely, you can locate the registration profile associated with a job record by searching the registration table on the Registration PK field.

Let’s have a look at the internals of what these two tables look like:

As you can see in the tables above, the values in the ‘Registration FK’ column of the job postings database correspond to the ‘Registration PK’ values in the registration table. This primary key / foreign key relationship provides a bi-directional link between the records in the two tables, allowing a lookup of one from the other.

We don’t want to get too far ahead of ourselves. The purpose is to get you to consider ways in which you can structure your tables, and how you can use primary keys and foreign keys to establish relationships.

[] Structuring your database

There is no clear cut correct way to structure a database. However, there are some general guidelines you should consider. Let’s take a look at an example database for a simple job search app.

In the illustration below, we have defined six tables that comprise a simple job search application. Notice each table contains a primary key field, which is used to uniquely identify records. In addition, most of the tables below also contain foreign keys, which are used to link records between the tables. For example, the Job Postings table contains the foreign key ‘Co Registration FK.’ This can be used to easily locate all jobs posted by a particular company by simply searching the Job Postings table on this field. Alternatively, this can be used to look up the registration details of the company that posted the job by searching the company registration table on the ‘Co Registration PK’ field.

As you can see from the diagram above, things can start to get complicated as you create more complex interrelationships. Therefore, you need to take time to properly plan things out. Nonetheless, linking tables through primary key/foreign key relationships allows you to create sophisticated and flexible designs with relative ease.

At this point, do not concern yourself with the mechanics of how you are going to create the tables. Right now, just think about the database structure in a broad sense. On paper, write out the name of each table and the information it will store. Try to follow our example above — identify the tables you will need, and include a couple of the fields they will contain. Follow the general guidelines below.

*
p<>{color:#000;}. Each table should only store related information. For example, it would be a mistake to include job posting information in the Company Registration table. It is far better to simply create another table to hold the job posting information and then link the two tables using key fields.

*
p<>{color:#000;}. Include a primary key field in every table you create, even if you think it will never get used. You should almost never break this rule. It is better to have a primary key and not use it, than to need it and not have it.

*
p<>{color:#000;}. When records in two tables are related, link them using a primary key / foreign key relationship. This is the foundation upon which relational databases are built. Sometimes a table is linked to more than one other table, as in the ‘Job Seeker Favorites’ above. As you can see, the records in this table include three foreign keys that link to records in three table, including:

The Job Seeker Registration Table
The Company Registration Table
The Job Postings Table

When you link related tables in this way, you greatly enhance your ability to create searches that can locate related records. For example, the ‘Co Registration FK’ field in the Job Seeker Favorites table can be used to create a search that can locate the registration information of the company that posted the job. Similarly, the ‘Job Posting FK’ link can be used to create a search that can locate the complete job listing. This is the real power behind using linked key fields.

Let’s take a closer look at the three basic principles described above.

Defining fields
Once you have identified the tables required by your application, the next step is to decide the specific information each table will store. This should be fairly easy. For example, if your site includes a registration table, you must decide the information you would like to collect as part of the registration process. This can be whatever you want. However, in defining your table fields, consider the three guidelines below:

*
p<>{color:#000;}. Only include fields you really need. You do not want to carry around useless information in your tables; it only serves to bog things down. Don’t worry if you miss some fields in your initial design. You can always go back and add them.

*
p<>{color:#000;}. Each table should only store related information. We mentioned this earlier, however it is worth reiterating.

*
p<>{color:#000;}. Registration tables represent a special case — these tables often include an email address field and a password field. Together, these fields represent a user’s login credentials. You can use some other combination of fields if appropriate, which, when combined are unique. Normally, applications that have a registration will also include a login; this allows registered users returning to the application to enter. You can set up a registration in any number of ways, but this is one typical way of doing this.

[
Including primary keys]
It is almost universally accepted that it is good database design to include a primary key field in all tables, even if you are not sure that you will ever use the key. Experience has shown that primary keys wind up being used extensively in most applications, especially those that include registrations. And creating a primary key is easy! The rule here is: it is better to have a primary key and not need it, than need it and not have it.

Linking tables[
**]The ability to link related tables is the payoff you get for properly designing your database and including key fields. Let’s go back and use the job search site example to help us understand how databases are linked. Below is copy of the job search database we presented earlier.

This is a database for an imaginary job search Application. This site allows registered companies to post job listings in the job postings database. Registered job seekers can search the job postings table. When they find a job they are interested in, they can save a record of the job in the job seeker favorites table. This allows them to easily go back and find all jobs in which they were previously interested. Job seekers can also send emails to a company through the company email database. This functionality would have been difficult if not impossible provide if we had not created primary keys and foreign keys that link these related tables.

Let us go step by step and see how we linked these tables. Let’s start with the company registration table. This table includes a primary key named ‘Co Registration PK,’ which is automatically created when a company registers with our application. At some point, a registered company is going to post a job to the ‘Job Postings’ table. Naturally, we will want to include the primary key value from their registration, as a foreign key, in the job postings record. In keeping with database design principal #2, our job postings table also includes a primary key field named ‘Job Postings PK.’

Now let’s look at how job seekers interact with our site. Job seekers must register before they can search the job postings table. Naturally, our registration table includes a primary key. This key is stored in the ‘JS Registration PK’ field. Once job seekers have submitted their registrations, they can start searching the job postings table. One of the features of our application is that we allow job seekers to maintain records of their favorite jobs.

As you can see from the diagram above, the job seeker favorites’ table includes three foreign key fields:

*
p<>{color:#000;}. JS Registration FK

*
p<>{color:#000;}. Co Registration FK

*
p<>{color:#000;}. Job Postings FK

These foreign keys reference records in their respective tables, which is what creates the link between records in the ‘Job Seeker Favorites’ table and records in the three foreign tables.

Searching a relational database[
**]We are going to get into this in detail later, but let’s just touch on it. Another benefit you get from a good database design is flexibility in searching. The critical feature of the design in the diagram above is the fact that it contains key fields that link related tables to each other. This greatly enhances your ability to create secondary searches that can locate related records. For example, the ‘Co Registration FK’ field in the ‘Job Seeker Favorites’ table can be used to create a search that can locate the registration information of the company that posted the job. Similarly, the ‘Job Posting FK’ key can be used to create a search that can locate the complete job listing from the job posting table. Along with maintainability, this is the real power behind a good design.

Another option, is to use the more advanced technique of searching by using joins. A join can be thought of as a multi-table or multi-database search. It allows you to perform searches that include record data from two or more tables, including tables in different databases. This is covered in more detail later. For more information, see the section ’Performing joins across databases.’

Designing for Mongo synching[
**]Earlier, I said I was going to show you how to design a local database schema for the device which would work well even if you intend to synch with NoSQL datastore on the back-end. Well, if you look at the database we designed for our example job posting application, what you see is a VERY traditional relational schema. Even our design guidelines are really targeted towards a traditional relational database model. This is all just fine when synching with MySQL, but what about when synching to a NoSQL datastore, what gives? It turns out that trying to create a schema that will work for both a traditional relational database and a NoSQL database is a fool’s folly. You will almost certainly end up with a design that works well with neither database. The answer to this problem is to create a traditional relational design on the device and create a flattened, denormalized design on the Mongo back-end, performing the required translation in code when synching. However, in order for this to work, the device schema must be done right, especially with respect to creating primary keys and foreign keys. It is these keys that are used to perform the translation from the relational schema to a document schema. We cover this in detail later when we discuss synching.

Suggestions for designing your SQL[
**]I have some thoughts and suggestions I would like to share with you regarding SQL for mobile apps.

Generally speaking, the rules and principals that you follow in order to write good SQL for non-mobile apps also apply to mobile applications. So, there really isn’t a bunch of new stuff that needs to be learned. The major differentiator is the environment and context in which SQLite runs is quite different from a traditional database server. As a result, some well-known and accepted rules are less applicable when using SQLite in a mobile environment. SQLite is a server-less database that runs in the address space of the application, or, at the very least, on the same CPU. Either way, the affect is the same.

There are different schools of thought over whether to use fewer complex queries as opposed to more simple queries in order to accomplish a given task. I have heard compelling arguments to support both. And to be perfectly candid, I’m not really sure that I have formulated a strong opinion of my own on this. Although, I seem to gravitate towards writing simpler queries. In any case, when writing SQL for SQLite on mobile, I believe that as a general rule, simpler queries are better. Let me explain why.

There are four primary reasons for writing complex SQL, including stored procedures.

*
p<>{color:#000;}. Stored procedures are often written in order to centralize business logic. Due to their very nature, these queries tend to be complex. While there is nothing that requires a stored procedure to be complex, they nonetheless encourage complexity due to the flexibility they provide.

*
p<>{color:#000;}. Writing complex SQL, including stored procedures, allows some processing to be offloaded to the database server. Sharing load in this way can provide performance benefits.

*
p<>{color:#000;}. Fewer complex queries can significantly reduce the number of over-the-wire trips that need to be made to the database. Network traffic is often times more of a performance issue that processing power.

*
p<>{color:#000;}. A single complex SQL statement is found to be more efficient than multiple simple statements.

None of these, except the last one, apply to SQLite in a mobile environment. There are no stored procedures, no offloading of processing to a separate server, and no over-the-wire network communication between the app and the database. Gains offered by the first three are largely nullified by the context in which SQLite runs.

Simple right? Well, as is usually the case with software engineering, nothing is that simple. While it is true that the first three reasons don’t really apply to SQLite, it is possible that the fourth might be more or less applicable than usual. Simpler queries might be more appropriate for apps that are multithreaded and requires concurrent database access; for more details, see the section ‘Locking and concurrency.’ Also, because there is no over-the-wire traffic generated by SQLite, the normal issues associated with higher velocity database workloads, such as network latency and traffic bottlenecks, don’t exist. If your apps read load is much higher than its write load, you might choose fewer complex queries, even if your database access is highly concurrent. The reason for this is reads execute with the highest level of concurrency in SQLite. I realize that this just muddies the waters, so I am going to give you a concrete recommendation.

If you have the choice to use a single complex query or multiple simple queries, and don’t want to go through the work of performance profiling, go with multiple simple queries.

Storing dates and times in SQLite[
**]“SQLite does not have a storage class set aside for storing dates and/or times.“ That sentence was copied directly from the SQLite documentation. In SQLite, dates and times are stored in the following formats.

*
p<>{color:#000;}. TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).

*
p<>{color:#000;}. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

*
p<>{color:#000;}. REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

You can then use the built in SQLite date and time functions to perform conversions. If you don’t need to synchronize the device database to a server-side datastore, then it really doesn’t matter which format you select. Just choose the one you are most comfortable working with in your app. Otherwise, it’s easiest to choose a format that will be readily accepted by the target replication server. For example, SQLite.XM’s default replication synchronizes with HotRiot. HotRiot has a dedicated date and time data type that accepts a date/time in the ISO8601format, so the text format would work just fine. This also has the advantage of storing the date/time on the server using an actual date/time field. HotRiot also includes both integer and real number data types, so using an integer or real for date/time would also be okay. Of course, you would need to perform a conversion in order to print the output in a human readable form.

What if you want to use one format for storing dates/times on the device database and a different format for storing them on the server? This is not a problem, but it requires extra work on your part. Synchronization includes the ability for you modify record data before sending it up to the server. You could use this capability to convert from one format to another. This feature is known as pre-synchronization processing and is covered in detail in the section ‘Customizing synchronization’.

Getting SQLite.XM

SQLite.XM is available on GitHub, here is a direct link: https://github.com/HotRiot/SQLite.XM-Xamarin. Just include the SQLite.XM project into your solution. If you’re not sure how this is done, I go through an example in the next section.

For creating iOS based applications, SQLite.XM requires Xamarin iOS 5.2 or later and an iOS version that supports multithreading. It is very unlikely that you wouldn’t meet these minimum requirements. You would have to indeed be working with an old version of Xamarin and testing with a very outdated version of iOS.

Creating an application skeleton

In this section I’ll explain how to create an application skeleton that includes SQLite.XM. If your comfortable doing this on your own, just skip ahead to the next section. The examples in the book do not depend on your having a project that exactly matches the one I create here. Just as long as you have some project with SQLite.XM, you’ll be fine.

I decided to use Xamarin Studio as it provides a fairly consistent and uniform interface between Windows and OSX. If you haven’t already done so, go to the following URI to install Xamarin: http://xamarin.com/download.

Once installed, start Xamarin Studio and select New Solution on the opening screen, or select File->New->Solution. This will open the New Solutions dialog, see the screenshot below. We’re going to start by creating a basic Android application, that is, an Android project with a single activity.

In the New Solutions dialog, select Android then Android Application. I named the project JSearch, short for Job Search.

Once created, our solution should show up in Xamarin Studio similar to the screenshot below.

Now, we are going to add SQLite.XM to our newly created solution. SQLite.XM is a shared project that can be used by all platforms targeted by Xamarin, including iOS, Android, and Windows. Right click on the JSearch solution and navigate to the Add->Add Existing Project, see the screenshot below.

This will bring up the open project dialog, see the next screenshot. Navigate to the folder where you placed the SQLite.XM project that you downloaded from GitHub and add it to the solution.

Next, we need to add several references which are required by SQLite.XM. Right click on the References folder listed under your project heading. This will display the references menu, see the screenshot below. Select the Edit References menu item.

The Edit Referenced dialog will display, see the next screenshot. Begin by selecting the Packages tab, then add the following four packages.

*
p<>{color:#000;}. Mono.Data.Sqlite

*
p<>{color:#000;}. System.Data

*
p<>{color:#000;}. System.Web.Services

*
p<>{color:#000;}. System.Xml.Linq

Next, select the Projects tab of the edit References dialog, then select the SQLite.XM project. See the screenshot below. Select the OK button to add the references.

Next, select Project->Add NuGet Package.

This will open the Add Packages dialog; see the next screenshot.

Add the package Json.NET to your project. At this point, we have fully created our project skeleton and we’re ready to get started!

h2<>{color:#2e74b5;}.

Platform specific code

SQLite.XM was designed to be a cross-platform solution for your iOS and Android applications. There is almost no platform specific code. At worst, you will need to add one additional line of code to the Android SQLite.XM initialization sequence that is not needed in iOS. I will show you this latter. That’s all the platform specific code you will need to deal with, everything else in SQLite.XM is consistent between Android and iOS.

Internally however, SQLite.XM has some platform specific code. Two files in SQLite.XM include conditional compilation directives used for selecting the correct code depending on the target platform for which you are compiling. At the top of the following two files are two #ifdef statements, one for Android and one for iOS.

*
p<>{color:#000;}. HotRiot->HotRiot_CS.cs

*
p<>{color:#000;}. Synchronize.cs

Uncomment the statement that references the platform for which you are compiling. A screenshot showing the top of the Synchronize.cs file is shown below, it has the Android directive uncommented.

Part 2. Data storage on the device

h2<>{color:#2e74b5;}.
Describing your apps databases

The first step to building our data persistence layer is to create a descriptor for each database used by our application. Descriptors provide basic information about the databases our app will be using. Below is a partial listing of the database descriptor class.

public class DatabaseDescriptor
{   
private string databaseName;[
__]    private Environment.SpecialFolder databaseFolder;
p.    public Environment.SpecialFolder DatabaseFolder
p.    {
p.        get { return databaseFolder; }
p.    }

public bool noLog = false;
p.    public string logfileName;
p.    public int logfileMaxSize = 1024 * 1024;[
__]    public Environment.SpecialFolder logfileFolder =
Environment.SpecialFolder.Personal; 

   + public DatabaseDescriptor(string databaseName,
Environment.SpecialFolder databaseFolder = Environment.SpecialFolder.ApplicationData)…
p.    + public static DatabaseDescriptor getDescriptor(string databaseName)…
+ public static ArrayList getDatabaseNames()…
}

The DatabaseDescriptor constructor takes a single required parameter; the name of the database you wish to create/connect. It also accepts an optional folder location for the database. By default, databases are located in the ApplicationData folder. This path is the value of the environment variable XDG_CONFIG_HOME, if it is set, otherwise, it is in the ‘.config’ directory of the current user’s home directory, which, in Android, translates to the following location:

/data/data//files/.config

and in iOS:

/documents/

DO NOT name your database ‘main’ or ‘temp’, these names are used by SQLite internally and using them may cause problems.

The DatabaseDescriptor class has reasonable defaults for its control parameters. Of course, if you don’t like any of the default values, you can change them. In the example below, the location where the database will be created is set to the Personal folder.

try
{
p.    new DatabaseDescriptor(“jSearch”, Environment.SpecialFolder.Personal); 
}
catch( System.Exception ex)
{
p.    string message = ex.Message;
}

Once created, the database name and folder location in the descriptor cannot be changed. This is reasonable; it’s hard to imagine a circumstance where this would make sense. Anyway, changing either of these effectively changes the database being referenced by the descriptor. Each database descriptor also includes properties for a log file. What this means is, each database can, and should, have its own separate log. This may seem like overkill; why not simply share a log file between all databases. The reason is twofold; this provides flexibility in managing logs and visually parsing a single file shared between databases is more difficult than having separate database logs.

The log file is automatically assigned the same name as the database with the extension ‘.log’ and its default size is 1MB. By default, log files are placed in the Personal folder. This is the personal or home directory for the current user, which, in Android, translates to the following location:

/data/data//files

Of course, you can change any of these defaults. In the example below, we change the maximum log file size from its default 1MB to 5MB and the log file name to jSearch-log.

try
{
p.    DatabaseDescriptor dd = new DatabaseDescriptor(“jSearch”);
dd.logfileMaxSize = 1024 * 1024 * 5;
dd.logfileName = “jSearch-log”;
}
catch( System.Exception ex)
{
p.    string message = ex.Message;
}

When a log file reaches its maximum size, it is renamed from <logfile name>.log to <logfile name>.old.log and a new log file is started. If there was an existing old log file, it is replaced. If you don’t want logging, set noLog to false.

Database descriptors are used by the persistence layer as needed; this is all automatic and under the covers. As part of your apps initialization, you must create a descriptor for each database. Only create one per database. SQLite.XM will quietly ignore attempts to initialize a descriptor for the same database more than once. You do not need to keep the instances of the descriptor objects that you create since the persistence layer automatically manages descriptors. Once created, descriptors can safely be forgotten about. However, if you need to get a descriptor, use the static method:

public static DatabaseDescriptor getDescriptor(string databaseName)

This method will return the descriptor for the named database, or null of the descriptor could not be found.

In the example below, we create three database descriptors. The first two use the defaults for all of the control parameters. In the third descriptor, logging is turned off.

try
{
p.    new DatabaseDescriptor(“user”);
new DatabaseDescriptor(“records”);
DatabaseDescriptor dd = new DatabaseDescriptor(“report”);
dd.noLog = true;
}
catch( System.Exception ex)
{
p.    string message = ex.Message;
}

While you can create as many databases as your app requires, as a practical matter, the vast majority of mobile applications really only need a single database. This is the simplest approach and works well in most cases. I would recommend this as the default unless you have a compelling reason to do otherwise. Multiple databases can add complexity. For example, joins across databases require that the joined databases be attached using the attachDatabase method; explained in detail later. This introduces potential concurrency issues, such as when trying to attach a currently locked database. There is also additional system overhead when working with multiple databases. While this generally not a serious concern, mobile devices are limited in resources and efficiency should always be considered in your design. That being said, there are times when multiple databases are the right choice.

h2<>{color:#2e74b5;}.

Schema initialization

Once the database descriptors have been initialized, the next step is to create the tables and indexes required by your schema.

One feature not present in SQLite, is stored procedures, so writing common callable SQL that is stored in the database is not an option. Which is well enough, it doesn’t really seem to make much sense to support stored procedures in a server-less, embedded database like SQLite. So, we have to decide where to put our SQL statements. We have a few choices:

*
p<>{color:#000;}. Put the SQL statements in the database.

*
p<>{color:#000;}. Imbed the SQL statements in the code.

*
p<>{color:#000;}. Place the SQL statements in a properties file.

When not using stored procedures, I generally prefer putting SQL statements in a centralized datastore, usually the database. It organizes the SQL in one location and makes it easy to find, read and modify independent of the code base. I prefer this solution when building applications that use a traditional database server, such as MySQL or SQL Server. In these cases, the database is almost always running on a server managed by a DBA. But for SQLite, this doesn’t seem to make much sense given how it would need to be implemented.

We could place the SQL directly in the code, in a single class. This is probably is the most efficient solution but it requires users to make code modifications. We definitely want to keep that to a minimum, even if only entering SQL text. The other possibility is to embed the SQL statements directly in the SQLite.XM database calls. I really don’t like peppering SQL throughout the code base. It is just a mess. It makes it more difficult to locate your SQL, which can cause other problems; for example, missing indexing opportunities or increasing the likelihood that you will duplicate code.

The best solution is to place our SQL statements in a properties file. This allows us to keep our SQL in a central location that makes it easy to read and modify without messing with the code. This is not the most efficient solution, as it requires the SQL statements properties file to be read and processed when initializing the app. But that really shouldn’t cause a significant delay and the tradeoff seems worth it. This solution is implemented slightly different on Android and iOS. So we need to deal with some application specific issues. As with most engineering design decisions, selecting one solution over another is really a balancing of tradeoffs.

Including a SQL statements properties file in Android
All Android applications include an Assets folder. This is the location where you place arbitrary files that need to be processed as raw data by your app, for example, the SQL statements properties file. You can find this folder in your project, it is created automatically by Xamarin. Create an empty file and place it in this folder.

You can name the file whatever you like, for the examples in this book, we call it SqlStatements.txt. This will be our SQL statements properties file. This is all that’s necessary to include the statements file in an Android project. Later on, I’ll show you how to access the file.

Including a SQL statements properties file in iOS
Xamarin based iOS application include a Resources folder. This is the location where you place arbitrary files that need to be processed by your app, for example, the SQL statements properties file. You can find this folder in your project, it is created automatically by Xamarin. Create an empty file and place it in this folder. If you like, you can place it within a subfolder of the Resources folder. This can help to organize things even further.

You can name the file whatever you like. In the screenshot above and for the iOS examples later in the book, we call it SqlStatements.txt and we placed in in the subfolder sqlProperties. This will be our SQL statements properties file. This is all that’s necessary to include the statements file in an iOS project. Later on, I’ll show you how to access the file.

As a side note, iOS requires that application resource files be placed in the application’s root directory. Unfortunately, this can get a bit messy. To clean things up, Xamarin has this notion of a BundledResource build action, which lets you put files in the Resources directory instead of in the root. The contents of the Resources directory gets mapped to the root of the application during the build process. This is done automatically by Xamarin. I find this to be a really nice feature that helps to organize your project a bit better.

Parameterized queries
Before explaining the structural details of the SQL statements properties file, we need to understand how queries should be written for SQLite.XM. All queries that require user supplied data must be written as a parameterized query. Parameterized queries are also known as prepared statements. A parameterized query is a query that is written where place holders are put in the query where user supplied data would normally appear; see the example below.

INSERT INTO companyReg (companyRegPK, coName, hrContact, email, password) VALUES

This query uses question marks at the location in the query where the record data would normally go. We do it this way for a number of reasons. First, parameterized queries are much easier to work with in code. Manually assembling a query that contains the actual record data is quite a bit more work than using the parameterized form. Parameterized queries are virtually immune to SQL injection attacks. SQL injection is a type of malicious hack that can be used to access or destroy your database. Finally, parameterized queries can me more performant. The way parameterized queries are implemented, the database can skip several complex preparatory steps when the same statement is used more than once. This can amount to a significant time savings for certain queries that are used repeatedly.

Let’s get a better understanding of the performance benefits offered by parameterized statements. Prepared statements provide the best performance gains when being used to execute a large number of identical queries (the user supplied data can vary). The performance gains will be particularly significant when executing complex queries. If a statement is relatively simple, the performance advantage of prepared statements will be less noticeable.

Parameterized statements only last for the life of a connection. When a connection ends, all associated prepared statements are also terminated, so they must be recreated for any new connections. This also means that a prepared statement cannot be used by multiple connections; however, each connection can create their own prepared statements.

Understanding the SQL statements properties file
Let’s start by having a look at the structure of the SQL statements properties file.

[
**]

The SQL statements properties file is divided into seven sections:

*
p<>{color:#000;}. TABLE

*
p<>{color:#000;}. INDEX

*
p<>{color:#000;}. INSERT

*
p<>{color:#000;}. SELECT

*
p<>{color:#000;}. UPDATE

*
p<>{color:#000;}. ALTER

*
p<>{color:#000;}. DELETE

Each section starts with a header name that is placed between opening and closing brackets and ends with an empty set of brackets. These are the opening and closing markers for a section. The sections can be in any order, however, they all must be present in the file, even sections that are empty. Anything that is not between opening and closing brackets is considered a comment and is ignored by SQLite.XM. Do not use the brackets symbol in any of your comments; these are used exclusively as a delimiter. The SQL statements for each section are placed between the opening and closing section markers. Each section should only include statements relevant to the section.

The TABLE section should only include create/drop table SQL statements.
The INDEX section should only include create/drop index SQL statements.
The INSERT section should only include record insert SQL statements.
The SELECT section should only include select SQL statements.
The UPDATE section should only include table update SQL statements.
The ALTER section should only include alter table statements
The DELETE sections should only include record delete SQL statements.

Let’s take a closer look at each section of the SQL statements properties file.

Defining table statements
The TABLE section of the SQL statements file should only include create table or drop table statements. Putting any other Data Definition Language (DDL) or Data Manipulation Language (DML) statements in this section will result in a runtime error. Each create table statement is composed of three fields.

[database_name*.*table_name] [create/drop table statement] [synch_key]

Each field is placed inside opening and closing brackets. The fields must appear in the order shown above. However, you can format them on separate lines or include white spaces in order to enhance readability. The first field is the fully qualified name of the table being created, that is, the database name followed by a period followed by the table name. The second field is the actual create table SQL statement. The last field is the synchronization key. This tells SQLite.XM how to synchronize the table data. There are currently three options.

*
p<>{color:#000;}. no_synch : Do not synchronize the table data.

*
p<>{color:#000;}. synch : Synchronize the table data with the server.

*
p<>{color:#000;}. copy : Copy the table data to the server. However, do not execute record deletes on the server.

This field is not case sensitive. Below is an example TABLE section with two create table statements and a comment.

[[TABLE]
Create the database tables “People” and “Time”.]
[jSearch.People] [CREATE TABLE People (id INTEGER PRIMARY KEY AUTOINCREMENT, FName TEXT, LName TEXT)] [synch]

[jSearch.Places] [CREATE TABLE Places (id INTEGER PRIMARY KEY AUTOINCREMENT, loc TEXT, reason TEXT)] [synch]
[]

SQLite.XM adds a text field to every table used for coordinating synching. The field name is systemSynchID. This field is completely managed by the SQLite.XM code, so it can safely be ignored. That being said, this is a reserved name so you cannot use this field name in any of your tables.

[
Defining insert statements]
The INSERT section of the SQL statements file should only include record insert DML statements. Each insert record statement is composed of three fields.

[statement name] [table name] [insert record statement]

Each field is placed inside opening and closing brackets. The fields must appear in the order shown above. However, you can format them on separate lines or include white spaces in order to enhance readability. The first field is the name of the insert statement. This can be any name of your choosing, however, it must be unique from all other insert statement names. We suggest you choose a name that is short but descriptive. The second field is the name of the insert table. The last field is the actual SQL insert statement.

Below is an example INSERT section with two insert statements.

[[INSERT]
**][registerCompany]
[companyReg] 
[INSERT INTO companyReg (companyRegPK, coName, hrContact, email, password) VALUES]

[insertJobPosting] 
[jobPosting] 
[INSERT INTO jobPosting (jobPostingPK, companyRegFK, jobTitle, description, pay) VALUES]
[]

Earlier, I mentioned that SQLite.XM adds a field named systemSynchID to every table and that this field can safely be ignored as it is managed by the system automatically. All that is true, however, there is one side effect caused by this that must be taken into consideration when writing INSERT statements.

There are two basic forms for the INSERT INTO statement:

*
p<>{color:#000;}. INSERT INTO table_name (column1, column2, column3, …columnN) VALUES (?, ?, ?, …?)

*
p<>{color:#000;}. INSERT INTO table_name VALUES (?, ?, ?, …?)

In the first form, the column names and the corresponding values being inserted are explicitly delineated. For example, column1 will get the value from parameter 1, column2 will get value from parameter 2, and so forth. In the second form, the columns being inserted are implied by the SQL statement. For example, the first column in the table will get value from parameter 1, the second column in the table will get value from parameter 2, and so forth.
[
**]When writing your INSERT statements, you must use the first form. The reason for this is when a table is first created, the systemSynchID field is known to be the last column in the table, as in the sample registration table below.

However, if you alter a table by adding fields, the new fields will now come after the systemSynchID field.

In this case, trying to perform updates using the implicit insert syntax may have problems. This is due to the fact that the systemSynchID field will now be located somewhere in the middle of the table.

[
Defining alter table statements]
The ALTER section of the SQL statements file should only include alter table statements. The alter table statement in SQLite supports a limited subset of commands. You can rename a table or you can add a new column to an existing table. DO NOT rename tables. The reason for this is when a table is created, SQLite.XM creates corresponding triggers needed for synchronization. When a table is renamed, the triggers do not automatically update. This will render the triggers inoperative and synchronization will start to fail. A future version of SQLite.XM may support table renames by manually dropping and recreating triggers, but for now, do not rename tables.

Each alter table statement is composed of three fields.

[database_name*.*table_name] [column name] [alter table statement]

Each field is placed inside opening and closing brackets. The fields must appear in the order shown above. However, you can format them on separate lines or include white spaces in order to enhance readability. The first field is the fully qualified name of the table being altered, that is, the database name followed by a period followed by the table name. The second field is the name of the column being added to the table. The last field is the actual SQL alter table statement.

Below is an example ALTER section with one alter statement.

[ALTER]
[jsearch.companyReg] [president] [ALTER TABLE companyReg ADD president TEXT]
[]

[
Defining index statements]
Unlike some other databases, index definitions are not part of the SQLite create table syntax. Therefore, indexes need to be created independently. This is not problem and in no way puts SQLite at a disadvantage, it’s just how they do things. The INDEX section of the SQL statements file should only include CREATE INDEX or DROP INDXED statements. Each index statement is composed of three fields.

[database_name*.*table_name] [index name] [index statement]

Each field is placed inside opening and closing brackets. The fields must appear in the order shown above. However, you can format them on separate lines or include white spaces in order to enhance readability. The first field is the fully qualified name of the table being indexed, that is, the database name followed by a period followed by the table name. The second field is the name of the index that is being created or dropped. The last field is the actual SQL index statement.

Below is an example INDEX section with two index statements.

[INDEX]
[jsearch.companyReg] [coNameIDX] [DROP INDEX coNameIDX]
[jsearch.companyReg] [newCoNameIDX] [CREATE INDEX newCoNameIDX ON companyReg (hrContact, coName)]
[[]
**]
Drop index and create index statements can appear in any order, intermingling them is okay. Having more than one drop or one create for the same index will cause an error. However, you can have one create and one drop statement for the same index.

[
Defining select, update, and delete statements]
The SELECT, UPDATE and DELETE sections of the SQL statements file should only include their respective select, update, or delete SQL statements. Each of these statements is composed of two fields.

[statement name] [SQL statement]

Each field is placed inside opening and closing brackets. The fields must appear in the order shown above. However, you can format them on separate lines or include white spaces in order to enhance readability. The first field is the name of the statement. This can be any name of your choosing, however, it must be unique from all other statement names in the same section. We suggest you choose a name that is short but descriptive. The second field is the actual SQL statement.

Below are several example statements.

[SELECT]
[findCompany] [SELECT * FROM companyReg WHERE coName = ?]
[]

[UPDATE]
[updateCompanyReg] [UPDATE companyReg SET hrContact = ? WHERE coName = ?]
[]

[DELETE]
[deleteCompany] [DELETE companyReg WHERE coName = ?]
[]

Creating tables
Now that we understand the structure of the SQL statement properties file and how to include it in a project, the next step is to populate the TABLE section. Below is the table portion of our example job search site.

[[TABLE]
**]Create the company registration database.
[jsearch.companyReg] 
[CREATE TABLE companyReg (id INTEGER PRIMARY KEY AUTOINCREMENT
companyRegPK TEXT, coName TEXT, hrContact TEXT, email TEXT, password TEXT)] 
[synch]

Create the job postings database.
[jsearch.jobPosting] 
[CREATE TABLE jobPosting (id INTEGER PRIMARY KEY AUTOINCREMENT,
jobPostingPK TEXT, companyRegFK TEXT, jobTitle TEXT, description TEXT, pay INTEGER)]
[synch]

Create the company email database.
[jsearch.companyEmail] 
[CREATE TABLE companyEmail (id INTEGER PRIMARY KEY AUTOINCREMENT,
companyEmailPK TEXT, jobPostingFK TEXT, companyRegFK TEXT, subject TEXT, body INTEGER)]
[synch]

Create the job seeker registration.
[jsearch.seekerReg] 
[CREATE TABLE seekerReg (id INTEGER PRIMARY KEY AUTOINCREMENT,
seekerRegPK TEXT, fName TEXT, lName TEXT, email TEXT, password INTEGER)]
[synch]

Create the job seeker favorites database.
[jsearch.seekerFavorites] 
[CREATE TABLE seekerFavorites (id INTEGER PRIMARY KEY AUTOINCREMENT,
seekerFavoritesPK TEXT, seekerRegFK TEXT, companyRegFK TEXT, jobPostingFK TEXT, password INTEGER)]
[synch]

Create the job seeker email database.
[jsearch.seekerEmail] 
[CREATE TABLE seekerEmail (id INTEGER PRIMARY KEY AUTOINCREMENT,
seekerEmailPK TEXT, seekerRegFK TEXT, companyRegFK TEXT, subject TEXT, body INTEGER)]
[synch]
[]

There are a few things worth noting in the example above. First, the use of comments. Anything not between brackets is considered a comment. I recommend you include a many comments as necessary in order to clarify what you are doing. Also, notice how the statements are formatted for readability. Finally, you will notice that every table includes an id field, which is defined as follows:

*
p<>{color:#333;}. id INTEGER PRIMARY KEY AUTOINCREMENT

Every table you create must include this field. Other than these basic formatting options and this one structural requirement, you are free to create your tables however you like. In the example above, I have made extensive use of primary keys and foreign keys. I suggest you do the same.

If you look closely at the primary key fields and foreign key fields you will notice they are all defined as type text. While this is not unheard of, it is a bit unusual. Key fields are normally defined as a number, or, more specifically, as a 64 bit long. However, when you define your tables, all key fields must also be created as text fields, similar to the way I have done in the example above.

Why is this so?

The answer is simple. As I stated above, key fields are typically defined as 64 bit auto increment values. However, this poses a problem when synchronizing data from multiple devices with each running an independent database instance. In this case, you are sure to have duplicate key values generated on the different devices. When you then try an synchronize, you will encounter either duplicate key errors or simply create other problems due to the fact that you have duplicate keys, which, by definition, are expected to be unique. The graphic below depicts this condition, we see that records from two different devices with the same record ID (27) are being synchronized to the server database. This, of course, is unacceptable. Fortunately, the solution to this problem is fairly simple.

Instead of creating a key field as a traditional auto increment value, you define it as a text field. You then populate the field with a Globally Unique Identifier (GUID) sometimes referred to as a Universally Unique Identifier (UUID). Put simply, a GUID can be thought of as a random number that is virtually guaranteed to be unique from all other GUIDs. It would be fair to ask how we can be so sure that we won’t have the same duplicate key problem with GUIDs that we would have with auto increment keys. I won’t go into details, but given how GUIDs are generated and their enormous size (typically 128 bits), it is generally accepted that it’s safe to rely on them being unique. Later, I will show you how to generate a GUID.

Initializing the database
We now have everything in place to successfully initialize our database. So let us take a look at an example that puts all of the pieces together. Below is a code snippet for an Android main activity class. In the example below, it is assumed that you have already created a SQL statements properties file by the name SqlStatements.txt and have populated it with create table statements, and, optionally, any required index statements.

public class MainActivity : Activity
{
p.    protected override void OnCreate (Bundle bundle)
p.    {
p.      base.OnCreate (bundle);
p.      SetContentView (Resource.Layout.Main);
p.        
p.      try{
1      new DatabaseDescriptor(“jsearch”);
p.   [// Read the SQL statements properties file that we placed in the Assets folder.
__]2       using (StreamReader sr = new StreamReader (Assets.Open (“SqlStatements.txt”))){
3        ProcessSQLStatements.Parse (sr);
p.     }

4      SxmInit.initialize();
5      using (SxmTransaction sxmTrans = new SxmTransaction(“jsearch”))
{
}
}
catch( System.Exception ex){
p.    string message = ex.Message;
}
}
}

The first step in initializing the database is to create a database descriptor for each database needed by your app, see line 1. Our example app only requires one database, this is typical for mobile applications. Once this is done, the next step is to process the SQL statements properties file. Have a look at lines 2 and 3. Line 2 creates a new stream reader for the file. The file stream is then processed on line 3. Once these two steps are complete, it’s time to actually perform the initialization. This is done on line 4. Line 5 creates a transaction for the newly initialized database. This step is not actually part of the initialization process and is only shown here for illustration. I explain transactions in detail in a later section. It doesn’t really matter when you actually initialize the database. In our example, we are doing it in the main activity. The only rule is that you must initialize the database before trying to establish a connection or create a transaction object. This seems obvious enough.

Below is an example of how this initialization might be done for iOS.

 public class Application {
p.   // This is the main entry point of the application.
p.    static void Main (string[] args)
p.    {
p.      Thread synchThread = new Thread(new ThreadStart(initDB));
p.      synchThread.Start ();

      [_// If you want to use a different Application Delegate _]
p.      [// class from “AppDelegate” you can specify it here.
__]      UIApplication.Main (args, null, “AppDelegate”);
p.    }

   private static void initDB ()
p.    {
p.      new DatabaseDescriptor (“jsearch”, System.Environment.SpecialFolder.Personal);
p.      [// Read the SQL statements properties file that we placed in the Resources folder.
__]      using (StreamReader sr = new StreamReader (“sqlProperties/SqlStatements.txt”)){
p.        ProcessSQLStatements.Parse (sr);
p.      }

     SxmInit.initialize ();

   }
}  

As you can see, the Android initialization and the iOS initialization are virtually identical. The main difference is we performed the iOS initialization on a separate thread. Strictly speaking, this is probably not necessary and you could always do it this way for Android also. The other, minor difference, is the way we pass the path for the SQL statements properties file to the StreamReader. This is done differently to account for the fact that in Android the SQL statements properties file is placed in the Assets folder and in iOS it is placed in the Resources folder.

Now, you might look at the initialization sequence and think that it is rather inefficient to do this for each startup cycle. However, looking closely at the code, you see that it’s really quite efficient. Parsing the SQL statements properties file is pretty light weight, and besides, even a file with a lot of statements is not going to be very large, possibly 100 kB. The create schema code is also very efficient. For each database used by your app, typically one, a single query is made to get the names of the existing tables. The create table statements are compared to this list and those already existing in the database are skipped. The alter table statements are only executed against tables that already exist in the database. This allows you to make schema changes to tables without having to execute both create and alter SQL commands. Index and alter table statements are only executed after their corresponding index or table field is first checked to verify that the statement needs to run. More about this later.

One more important point. It is perfectly safe to execute the initialization sequence multiple times for each run of an application, the sequence is idempotent. This might, under certain circumstances, ease dealing with application state transitions. For example, this would be perfectly fine.

[
**]  for(int i=0; i<4; i++)
p.  {
p.    new DatabaseDescriptor(“jsearch”);
using (StreamReader sr = new StreamReader (Assets.Open (“SqlStatements.txt”))){
p.    ProcessSQLStatements.Parse (sr);
}

   SxmInit.initialize();
}

While this is not something that you would necessarily want to do, no harm will come if you do.

Using the SxmTransaction class

Once the database has been fully initialized, it is ready to be used. The SxmTransaction class is the easiest and most direct way to interact with your database. It dynamically creates connections to the database and manages all associated resources automatically. For many applications, this is the best way to work with the database; you just instantiate an instance of the class inside a using statement and use it. All of the resource cleanup is taken care of automatically. Your other option is to manually create your database connections, reusing them as needed. I explain this in a later section. For more information, see the section ‘The SxmConnection class.’

Although the SxmTransaction class is a bit less efficient than managing connections manually, for many apps, the performance difference is not significant enough to warrant the extra effort of dealing with connections directly. Other than the cost of establishing new connections, the main performance disadvantage of the SxmTransaction class is that prepared statements are lost when the transaction object is destroyed. With that said, I would like to point out that these are not really as costly as you might think.

When interacting with a traditional database server, developers usually make an effort to minimize connection workload. This makes sense, since connecting to a database typically requires a remote network connection to a database server. This is costly and should be kept to a minimum. Connection reuse through pooling is the strategy most used to do this. But requirements are different for mobile apps. The cost of establishing a new connection to a local SQLite database is very low. There is no over-the-wire network involved. Mobile environments have limited resources, so managing a pool of open connections is probably worse than just creating and tearing down connections as needed. Also, while SQLite does have good concurrency, it is not up to the level of a full-fledged database server. Fewer active connections minimizes the potential for conflicts due to locking.

Prepared statements offer their greatest benefit when executing complex queries repetitively. This condition is the exception rather than the rule for mobile applications. For most mobile apps, queries tend to be simple and are generally not executed so often that the cost savings of prepared statements is so significant. It is important to understand that the advantage of prepared statements are not entirely lost when using the SxmTransaction class. The SxmTransaction class does create and use prepared statements for the life of a transaction, which can span many statement executions. However, all prepared statement that are created are lost when a transaction is completed, therefore they cannot be carried over and reused by another transaction object.

Inserting records into the database
Let’s start with a simple example that posts a record to the database. Below is a screenshot of the company registration page of our job search application. This is a very basic input form used to register a company with our app. When the submit button is selected, the registration is processed.

Below is the company registration activity class for our registration form. It’s pretty simple, it submits a registration record to the database. We are most interested in the processSubmitHandler method. This method is called when the submit registration button is selected, it is responsible for inserting the registration record. Before we pick this method apart, let’s have a look at the insert statement in the SQL statements properties file that is used by this method to actually save the registration record.

[registerCompany]
[companyReg]
[INSERT INTO companyReg (companyRegPK, coName, hrContact, email, password) VALUES]

The first field in the definition is the name of the statement; registerCompany. The second field is the name of the table being inserted by the insert statement; companyReg. And lastly, we have the actual insert statement itself. This is the statement that is used by the processSubmitHandler method that actually saves the registration. As you can see, it is a simple parameterized insert statement. Okay, back to the code below.

[Activity (Label = “CoRegistrationActivity”)]            
p.  public class CoRegistrationActivity : Activity
p.  {
p.    protected override void OnCreate (Bundle bundle)
p.    {
p.      base.OnCreate (bundle);
p.      SetContentView (Resource.Layout.CoRegistration);

     Button submitCoRegButton = FindViewById