Database Implementation

This section discusses the design and implementation of the database.

Data structure

ExerciseEntry is the core data structure of the app. It defines what information a workout entry should have -- we use the term workout and exercise interchangeably in this document. It can be defined as below.

public class ExerciseEntry {

        private Long id;

        private int mInputType;  // Manual, GPS or automatic

        private int mActivityType;     // Running, cycling etc. 

        private Calendar mDateTime;    // When does this entry happen

        private int mDuration;         // Exercise duration in seconds

        private float mDistance;      // Distance traveled. Either in meters or feet.   

        private float mAvgPace;       // Average pace

        private float mAvgSpeed     // Average speed

        private int mCalorie;        // Calories burnt

        private float mClimb;         // Climb. Either in meters or feet.

        private int mHeartRate;       // Heart rate

        private String mComment;       // Comments

        private ArrayList<LatLng> mLocationList; // Location list

    }

You need to implement methods to set/get these attributes. For example, you need to implement methods to convert mLocationList to byte array to save in the database and convert byte array to array list when retrieving the location list. MyRuns3 does not require you to store the location data.

Database Table Schema

There is only one table needed. It can be defined as follow:

    CREATE TABLE IF NOT EXISTS <TABLE_NAME> (

        _id INTEGER PRIMARY KEY AUTOINCREMENT, 

        input_type INTEGER NOT NULL, 

        activity_type INTEGER NOT NULL, 

        date_time DATETIME NOT NULL, 

        duration INTEGER NOT NULL, 

        distance REAL, 

        avg_pace REAL, 

        avg_speed REAL,

        calories INTEGER, 

        climb REAL, 

        heartrate INTEGER, 

        comment TEXT, 

        privacy INTEGER,

        gps_data BLOB );

The _id is the primary key. In database, the primary key uniquely identifies a record. "AUTOINCREMENT" indicates that the value will be set automatically and incrementally. The field gps_data stores all the GPS coordinates. We use BLOB to save all the coordinates. A BLOB value is a blob of data, stored exactly as it was input. (see here) As mentioned in Data structure, you should store the location list in gps_data.

Implement Database Operations

The design principle of the database operations is to hide database operation details from app's other modules. That is, other modules, e.g. history tab, do not need to operate the database directly to get data entries from or save data entries to the database. There is concept called Object-relational mapping which converts relational database operations to object-oriented operations. Hibernate is such platform. We will implement this concept in the simplest form. We define a helper class to encapsulate all the database operations. You can use SQLiteOpenHelper to implement your helper class. All necessary methods are defined as follows.

    // Constructor  

    public ExerciseEntryDbHelper(Context context) {

        // DATABASE_NAME is, of course the name of the database, which is defined as a tring constant

        // DATABASE_VERSION is the version of database, which is defined as an integer constant

        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    // Create table schema if not exists    

    @Override

    public void onCreate(SQLiteDatabase db) {

        db.execSQL(CREATE_TABLE_ENTRIES);

    }

    // Insert a item given each column value

    public long insertEntry(ExerciseEntry entry) {

    }

    // Remove an entry by giving its index

    public void removeEntry(long rowIndex) {

    }

    // Query a specific entry by its index.

    public ExerciseEntry fetchEntryByIndex(long rowId) {

    }

    // Query the entire table, return all rows

    public ArrayList<ExerciseEntry> fetchEntries() {

    }

In each method defined above, you need to get a database object using getReadableDatabase() or getWritableDatabase(), then do the reads/writes. Remember to close database cursors and database object after you are done.

As you can see from the class interface definitions, the input or output of these methods are all ExerciseEntry objects. When the history tab is loaded, it uses fetchEntries() to get the list of all entries. Then the list can be bound to an adapter so that the list view can show the entries. When the user selects an entry in the history tab, the app can use the fetchEntryByIndex() to get the entry’s details and display them either in a display activity (if it is a manual entry) or in the map (if it is not a manual entry). If an entry is generated, you can use insertEntry() to insert it to the database.

Hint:

• Use Sqlite3, which is natively supported by Android
• Implement the helper class by extending SQLiteOpenHelper
• You would save the location trace in either a Location list or a LatLng list. In order to save the trace to the gps_data blob field, you need to convert the list to a byte array when you save the record, and convert the byte array to the list when you read it from the database.

Use Database Helper in Activities

To implement the database in the activities, you should create a database helper object. For example, if you define your helper class as ExerciseEntryDbHelper, you should create the helper object like this:

    ExerciseEntryDbHelper exerciseEntryDbHelper = new ExerciseEntryDbHelper(this);

After this, you can use this object to operate the database. For example, if you want to remove an entry from the database:

    exerciseEntryDbHelper.removeEntry(entryID);