Why Should My Phone Store Data Locally When it’s all Just Up in the Cloud?
by Joe Kratzat
More and more people are using mobile devices to view information, but as many people will tell you, mobile devices don’t always have good connectivity. In some cases there is no connectivity. This makes it hard for users of a mobile device to use an application that is dependent on information in the cloud. In this article we're going to look at some reasons why you can't always assume a mobile device will have the ‘knowledge’ of the cloud at its fingertips. We will even look at an example from a recent project where we decided to use a mobile database, and how we went about updating that database from our server.
The case for a local database
Let's first look at the example application. We recently built an application for Indiana wineries. The locations of many wineries don't allow for great Internet connectivity from a mobile device. If the mobile user is at one of these locations the application is useless unless they can still access information about the winery.
The need for information without Internet led to a team decision to have a fully populated database setup on the mobile device at install time. The application, on startup, checks with the server for an updated version of the mobile database. However, even without the Internet the application can still display information for the user by making queries to a local database.
So we have the information on the phone, but what if we need to update some of that information? This is where the server-side database and versioned-mobile database comes in. Having a versioned database allows the phone to download the most recent version when an Internet connection is available.
We originally targeted the iPhone platform for our application, which means we used SQLite3 as our mobile database. The phone application comes pre-populated with data in the database. We could have required the user to download the newest database the first time, but this way if the user doesn't run the application right away it is still useful in an offline location.
To download the database to the phone we setup two calls back to the server, 1) to get the newest version number and 2) to download the newest mobile database. Once the mobile database is downloaded the new version of the database will be used to overwrite the current version.
The server the phone calls for information updates has three major roles. First, it allows the client (in this case Indiana wineries) to update their information via a web interface and store it in our server-side database. Second, the server allows us to generate a mobile version generated from the server-side database. Finally, an API is exposed allowing the mobile device to download the newest version of the mobile database.
Implementing database versioning for the phone
Okay that's kind of a high-level view. The real question is how did we accomplish this?
For the server we used the Ruby On Rails framework with a MySQL and SQLite3 database. Creating a SQLite3 database with Rails is pretty straightforward, so we won’t dig into that. However, what we think is interesting is the approach used to version our mobile database for the phone.
First, we setup the server-side database with all of our needed tables and columns. We then added a table called databases, which stores the version number and path for any generated mobile database. Rails checks against this table when the phone starts requesting new versions.
Ruby on Rails gives us a nice little utility called “rake” which allows us to a predefined set of code instructions. Using rake we created an “Indiana_wineries:db:generate” task. This handles all of the versioning of the mobile database. It can be run manually or automatically. We could setup our rake task to use crontab to run every so many hours. Cron is a time-based scheduler in the Unix based systems.
The rake task works by getting the latest version number from the databases table. If there are other versions it will increment the version number, otherwise starts at version one. Next it will generate the file system path to location the new version of the database will be stored. It does this by adding the version number to the file name (example: database.3.db). With the path it creates a new SQLite3 database file, adding a set of tables and saving data to those tables. The information being added to the SQLite3 database is pulled from the server side database and saves the newly created mobile database. Lastly it saves the new version information to the database table.
Below is an example of what we have done. Some of the code has been removed to make it easier to read.
desc "Generate the Indiana Wineries database"
task :generate, :needs => [:environment] do
puts "Generating Indiana Wineries database"
#get the latest version
current_database = Database.find(:first,
:order => 'version DESC')
#set the version and path to something default
path = Rails.root.join('mobile', 'db', 'indiana_wineries','indiana_wineries.1.db').to_s
version = 1
#if there are older versions update the number and path
version = current_database.version + 1
path = path.gsub(/\.\d+\.db/, '.' + version.to_s + '.db')
db = SQLite3::Database.new(new_database.path)
new_database = Database.new( :version => version, :path => path);
db = SQLite3::Database.new(new_database.path)
#Here we would create the tables and populate
#Finally save the changes to the databases table.
Listing 1: The simplified rake task to generate the mobile database
After the mobile database is generated and the database table is updated, all the mobile application has to do is call back to the server. The application requests the latest version number of the database from the server. It then makes a comparison of the two version numbers on the phone and pulls down the new database if it needs updating and overwrites the existing database. Once that is completed the phone is able to fully function in an offline environment.
After setting up and deploying this configuration we found that it not only helps with offline functionality but also works really well when the phone is on a slower Internet connection. Our application design is based around the critical need to have functionality with or without Internet access. This design allows us to have a functioning application at the time of install, and still have the ability to update on the go.
For more information on getting Rails to talk to SQLite3, check out Alex Brie’s article titled Setting up Rails and SQLITE. To learn more about Rake and how to use it read this rake tutorial written by Jason Seifer. Finally, the following is a quick reference guide to Crontab.
About the author
Joe Kratzat is a Software Developer at Developer Town, a venture development company. He has worked on many projects ranging from small to enterprise level applications. If you'd like to checkout the iPhone application referenced in this article, you can download it for free.