Storing GPS points in a SQLite DB

Now that I managed to get data from a mock provider, the next task was to store the data in a database, so that I have a rudimentary GPS data logger.

There are two approaches for this: one is to simply write the stuff into a DB (or a file), the other is to implement a ContentProvider. The second approach is a bit more sophisticated, but it is where I will be heading anyway, plus there seem to be more working examples on the net.

Google provides the Notepad application as an example for a ContentProvider and I started with that code, chopping and changing as I went along. There is also a step by step guide in the online documentation.

I left most of the methods of the ContentProvider empty at the moment, only implementing the insert method, so I have a write-only, read-never DB, which is not terribly useful. Getting the data out will be the next step.
This is how the ContentProvider code looks right now:

package com.ucont;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

public class GPSDataContentProvider extends ContentProvider {

    private static final String TAG = "GPSDataContentProvider";

    private static final String DATABASE_NAME = "gpsdata.db";
    private static final int DATABASE_VERSION = 2;
    private static final String POINT_TABLE_NAME = "gpspoints";

    public static final String AUTHORITY = "com.ucont";
    public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/gpspoint");
    
    /**
     * This class helps open, create, and upgrade the database file.
     */
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context, String name) {
            super(context, name, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
         try {
         Log.i(TAG, "Creating table " + POINT_TABLE_NAME);    
            db.execSQL("CREATE TABLE " + POINT_TABLE_NAME + " ("
                     + GPSData.GPSPoint._ID + " INTEGER PRIMARY KEY,"
                     + GPSData.GPSPoint.LATITUDE + " REAL,"
                     + GPSData.GPSPoint.LONGITUDE + " REAL,"
                     + GPSData.GPSPoint.TIME + " INTEGER"                    
                     + ");");
         } catch (SQLiteException e) {
          Log.e(TAG, e.toString());
         }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + POINT_TABLE_NAME);
            onCreate(db);
        }
    }

    private DatabaseHelper mOpenHelper;

    public boolean onCreate() {
        mOpenHelper = new DatabaseHelper(getContext(),DATABASE_NAME);
        return true;
    }


 @Override
 public int delete(Uri arg0, String arg1, String[] arg2) {
  // TODO Auto-generated method stub
  return 0;
 }


 @Override
 public String getType(Uri uri) {
  Log.i(TAG, "getting type for " + uri.toString());
  // TODO Auto-generated method stub
  return null;
 }


 @Override
 public Uri insert(Uri uri, ContentValues values) {
  Log.e(TAG, "inserting value " + values.toString());
  
        SQLiteDatabase db = mOpenHelper.getWritableDatabase();
     long rowId = db.insert(POINT_TABLE_NAME, "", values);
         if (rowId > 0) {
             Uri noteUri = ContentUris.withAppendedId(GPSDataContentProvider.CONTENT_URI, rowId);
             getContext().getContentResolver().notifyChange(noteUri, null);
             return noteUri;
         }

         throw new SQLException("Failed to insert row into " + uri);
 }


 @Override
 public Cursor query(Uri uri, String[] projection, String selection,
   String[] selectionArgs, String sortOrder) {
  // TODO Auto-generated method stub
  return null;
 }


 @Override
 public int update(Uri uri, ContentValues values, String selection,
   String[] selectionArgs) {
  // TODO Auto-generated method stub
  return 0;
 }
}

I was a bit stumped for a while that the onCreate call for the DB, which creates the table was called whenever I started the app. This is a topic also covered in the newsgroups, but I think that debate is a bit a red herring. I have not 100% pinned it down, but I have the feeling that the onCreate is called whenever my app has been recompiled and is in this sense a new app. Now that I am stable with this bit of code, the onCreate call is not fired every time. Nevertheless I have the create call in a try catch block, to trap the recreation of the table.

The other thing I am not 100% sure about is the second parameter to the insert method, a string. I have for now just left it empty, expecting an exception, but I do not get one. There might be something lurking there.

The other class I have is for the table definition, here in a class called GPSData:

package com.ucont;

import android.net.Uri;
import android.provider.BaseColumns;

public final class GPSData {
    public static final String AUTHORITY = "com.ucont";

    // This class cannot be instantiated
    private GPSData() {}
    
    /**
     * GPS data table
     */
    public static final class GPSPoint implements BaseColumns {
        // This class cannot be instantiated
        private GPSPoint() {}

        /**
         * The content:// style URL for this table
         */
        public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/gpspoint");

        /**
         * The MIME type of {@link #CONTENT_URI} providing a track (list of points).
         */
        public static final String CONTENT_TYPE = "mime/text";

        /**
         * The MIME type of a {@link #CONTENT_URI} sub-directory of a single point.
         */
        public static final String CONTENT_ITEM_TYPE = "";

        /**
         * The default sort order for this table
         */
        public static final String DEFAULT_SORT_ORDER = "modified DESC";

        public static final String LONGITUDE = "longitude";
        public static final String LATITUDE = "latitude";
        public static final String TIME = "time";
    }
}

This was mostly copied from the notepad application and as I am not yet taking any data out all the content type definitions are not right. The only stuff used pretty much are the column names, latitude etc.

Now how is all this called?

My old onLocationChange in my main activity now stores the new location like this:

 public void onLocationChanged(Location loc) {
     Log.e(TAG, loc.toString());

     ContentValues values = new ContentValues();
   
     Double lon = loc.getLongitude();
     Long time = loc.getTime();
     values.put(GPSData.GPSPoint.LONGITUDE, loc.getLongitude());
        values.put(GPSData.GPSPoint.LATITUDE, loc.getLatitude());
        values.put(GPSData.GPSPoint.TIME, loc.getTime());
     getContentResolver().insert(GPSDataContentProvider.CONTENT_URI, values);
   }

The next steps will be to get the data out again and to store them in a GPX file on the SD card. Only once that is done can I be sure that the data is actually in the DB.