SQLite

SQLite is a self-contained SQL engine for use with localized applications. It is generally used when a network connection is not required, otherwise unavailable, or where a more fully featured SQL implementation (e.g., MySQL) would create unnecessary bulk for the project’s scope. The role of SQLite in this project was to maintain a local store of data objects that could be queried for rapid user interface displays, as well as offline application functionality. Data is stored as the user navigates between views. When the user returns to a view, the application checks the cache and immediately displays relevant data if it is found. This provides the user with a fluid user experience, even if the connection to the remote database is lost.

SQLite maintains simple database text-files which are highly portable and allow for a fast, transaction-based queries one might find in a heavier-weight implementa-

45

tion. Due to its small installation size, SQLite is oftentimes favored for embedded applications where storage space is limited. For this project, SQLite took the shape of a Cordova plugin, as most major vendors ship their devices with built-in SQLite support. Cordova provides a transaction-based interface to the existing SQLite software. While the Cordova SQLite implementation lacks several features found in traditional SQLite (most notably foreign key support), the Cordova plugin has the facilities needed by this application in order to implement an effective caching solution.

The SQLite Cordova plugin provides the most crucial functionality of any third- party software included in this project. Functions regarding SQLite database oper- ations appear in a single file - the “CacheDatabase”. The CacheDatabase exposes a public functions which are used to read and manipulate data in the cache. This file contains both DDL and DML queries. It is responsible for creating database tables, dropping tables, and reading data from those tables. In this way, whenever the application is launched, the entry script runs an initialization function to start a Cordova SQLite instance and readies it for further querying. SQLite provides long term data storage on the device and serves two main functions for this project: record caching and action caching. Record caching concerns itself with storing data retrieved from the REST API. This is most useful in readily showing interesting data to the user, as the cache is checked each time the controller makes a request for data. If the cache contains relevant data, then it is given to the controller and immediately displayed to the user. This gives the application a more fluid user experience as local cache queries are oftentimes faster than an API request. Once the API returns the requested data, this newly retrieved data must be compared and merged with data taken from the cache. It is done to ensure that the local SQLite cache is as up-to-date

as possible. Additionally, record caching helps to reduce redundant API calls, as resources (e.g., images) are checked and only requested from the API when they are needed. Furthermore, record caching gives a rudimentary offline viewing experience. If the user loses connection to the Internet, they are still able to view a subset of the available data (i.e., data stored in the local cache). As the Geotagger project largely deals with natural phenomena, members may oftentimes find themselves in locations where a network connection is not available. By locally caching every record the application retrieves, users are provided a useful, limited, experience even when an Internet connection is not available. While record caching deals with storing retrieved data locally, action caching, on the other hand, is more concerned with the modification of the local data.

Action caching enables a mechanism for affecting immediate remote changes if an Internet connection is available or as soon as one is established. Traditional web applications require a constant Internet connection as they are usually interacted with via an Internet browser. However, mobile applications are expected to give some kind of user-experience, even when a network connection is not available. While record caching affords the user the ability to view data they’ve already visited, there is no way to update this data. Action caching provides a way to “cache actions” in order that they may be resolved either immediately or at a later time. In this way, every “action” that the user takes (e.g., update, add, or delete data) is “pushed” onto the action cache and resolved as soon as the device detects an available network connection. When a user makes an action, this action is reflected in the local cache to allow the user to immediately view the changed data. A timestamp and reference to this data is stored in the “action cache”. Once a connection is established, the “ActionService” removes the action, reads the referenced data, and sends an HTTP

47

request to the API so that the change may be affected on the Geotagger server. Once the API responds with the result of the change, the action cache uses the data to synchronize the cache to any changes that took place on the server (e.g., replace the new records old ID value with one assigned to it by the remote database). Indeed, SQLite is an extremely important piece of the application structure which reduces redundant data, allows for fluid online viewing, and affords users an application-like offline experience that is missing in most traditional web applications.