From Notes

Jump to: navigation, search


The PositDbHelper class manages all interactions between POSIT and its SQL database. It contains Java code to create the POSIT database, which is created anew whenever a new version of POSIT is installed on a phone and removed completely whenever POSIT is uninstalled. This means that any data stored by previous versions of POSIT would be removed if a new version is installed. During development the POSIT instance is merely updated, rather than re-installed, which means that the database persists.

The POSIT Database

The database consists of six tables. The main table is the Finds table, which stores all data associated with a given Find. It is defined in SQL as follows:

       _id integer primary key autoincrement,
       guid text,
       project_id integer DEFAULT 0,
       name text,
       description text,
       latitude double DEFAULT 0,
       longitude double DEFAULT 0,
       timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       modify_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       synced integer default 0,
       revision integer default 1,
       deleted integer default 0,
       is_adhoc integer default 0);

In SQLite, the primary key of a table has the column name _id. For all POSIT tables the primary key is an autoincremented integer. The Find's guid (Globally Unique ID) is a randomly generated string that is used to ensure (but not with 100% probability) that finds gathered by different phones will have different IDs when they are shared on the server.

Whenever a new Find is inserted into the table or an existing Find is modified, the timestamp and modify_time fields are updated, respectively. The synced, deleted, and is_adhoc fields are binary fields that take integer values of 1 or 0. Note that when a Find is deleted from the database, it is simply marked as deleted; it is not actually removed from the table.

The other data associated with a Find are photos. These are stored in the photos table:

       _id integer primary key autoincrement,
       find_id integer DEFAULT 0,
       guid text,
       project_id integer DEFAULT 0,
       identifier integer DEFAULT 0,
       image_uri text,
       thumbnail_uri text,
       mime_type text DEFAULT 'image/jpeg',
       timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);

Rows in the photos table are linked to Finds through the find_idand the guid fields. In other words, all photos associated with a given find will record its _id and its guid. The photos table does not actually store images. Rather it stores a URI, a Uniform Resource Identifier which points to the image or its associated thumbnail. The images are stored on the phone's SD card.

To help manage synchronization of Finds data between the phone and the server, two other tables are used.

CREATE TABLE finds_history(
       _id integer primary key autoincrement,
       timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       guid varchar(50) NOT NULL,
       action varchar(20) NOT NULL);

CREATE TABLE sync_history(
       _id integer primary key autoincrement,
       timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, a
       server varchar(50) NOT NULL );

Whenever a change is made to the Finds table, an entry is created in the finds-history table, recording when (timestamp) and what (action) and to which find (guid) the action occurred. This is used to determine which finds require synchronization. Whenever the Finds are synced with the server, an entry is made in the sync_history table, given the timestamp of the sync action. When the user chooses to sync the finds, all finds that have changed (as shown by the finds_history table) since the last synchronization (as shown by the sync_history table), will be synced.

The remaining two tables, expeditions and points are used to record the phone's movements by POSIT's Tracker.

CREATE TABLE expeditions(
       _id integer primary key autoincrement,
       expedition_number integer DEFAULT 0,
       project_id integer DEFAULT 0,
       expedition_points integer DEFAULT 0,
       expedition_synced integer DEFAULT 0,
       expedition_registered integer DEFAULT 0 );

       _id integer primary key autoincrement,
       expedition integer DEFAULT 0,
       latitude varchar(30) DEFAULT 0,
       longitude varchar(30) DEFAULT 0,
       altitude double DEFAULT 0,
       swath integer DEFAULT 0,
       time long DEFAULT 0,
       synced integer DEFAULT 0 );

A track is known as an Expedition. It is associated with a particular project on the phone (project_id), as well as on the server. The expeditions table records whether the expedition is registered (synced) with the the server (expedition_registere), the number of points in the track (expedition_points), and the number of points that have already been synced with the server (expedition_synced).

The points table stores each individual point that is recorded during tracking. Points are associated with their expedition through the expedition column. In addition to the point's latitude and longitude and altitude, it stores the time the point was recorded, the width of the swath that the user can observe at that location, and whether or not the point has been sent to the server synced.

Personal tools
NSF K-12