DDD East Anglia App, Part 2: The Database

Yesterday I got a basic Android app skeleton, with a customised action bar, set up. Today, I’m moving onto the data layer.

Data

Using the sessions list from the DDD East Anglia website, I compiled two JSON resources – one for speakers, another for sessions.

Speakers.json

{
  “speakers” : [
    {
      “name”: “”,
      “bio”: “”,
      "thumbnail_url”: “”,
      “id”: “1”
    },
    …
  ]
}

Sessions.json

{
  “slots”: [
    {
      “start_time”: “”,
      “end_time”: “”,
      “sessions”: [
        {
          “room”: “1”,
          “title”: “”,
          “description”: “”,
          “speaker_id”: “1”
        }
      ]
    }
  ]
}

The DDD East Anglia schedule list uses a gravatar URL for each speaker who has supplied an image, which I add directly into the JSON. Sessions are arranged into time slots, where each session identifies the room it is in and the id of the speaker running the session. I’ve made the decision to add in registration, lunch and tea breaks as sessions within a time slot, for consistency. In code, I’ll add some functionality to determine whether a session is compulsory (if a slot contains only one session), or optional (ie – an actual tech talk). The res/raw directory can be used to contain resources like JSON files. The system will take the name and compile it into the auto-generated R class. I’ll be able to access the JSON files using R.raw.sessions and R.raw.speakers.

Constructing the Database

The first time my application is launched, I intend to parse the JSON and store it into a SQLiteDatabase. This is a fairly lightweight relational database which any Android application can create.

To create a custom SQLiteDatabase, create a subclass of SQLiteOpenHelper:

public class DatabaseHelper extends SQLiteOpenHelper {
  private static String DATABASE_NAME = “ddd-ea-2013.db”;
  private static int DATABASE_VERSION = 1;

  public DatabaseHelper(Context c) {
    super(c, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    // Insert here
  }
}

Remember to declare in your manifest file that you’d like to write to storage:

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

Next, it’s time to formalise the structure of the database. I’ll have a table for speakers, another for slots, and another for individual sessions. It’s important to give each table a primary key with the name _ID, using the system String BaseColumns._ID. This will allow me to easily create foreign key references, but also bind a database row to a list view later on. In my sessions table, speaker_id and slot_id are foreign key references to the speakers and slots table.

Speakers

• _ID: integer primary key autoincrement
• name: text not null
• bio: text
• thumbail_url: text

Slots

• _ID: integer primary key autoincrement
• start_time: text not null
• end_time: text not null

Sessions

• _ID: integer primary key autoincrement
• location: text not null
• title: text not null
• description: text not null
• speaker_id: integer, foreign key references speakers(_ID)
• slot_id: integer, foreign key references slots(_ID)

Session_Selections

• _ID: integer primary key autoincrement
• slot_id: integer, foreign key references slots(_ID), unique on conflict replace
• session_id: integer, foreign key references sessions(_ID)

That Session_Selections table could do with some explaining. It is used to track any sessions that a user has added to their schedule. For each slot a user can choose at most one session. The UNIQUE constraint will trigger a conflict if the same slot_id is inserted a second time into the database. With SQLite you can choose what happens on conflicts. Selecting ON CONFLICT REPLACE will delete the existing entry for that slot_id, and replace it with the new entry.

The onCreate call in your SQLiteOpenHelper subclass is a good opportunity to create your tables. I also use this function to load up the JSON files and parse them into the database. SQLiteDatabase has an execSQL method which you can use to create the tables. As this deals with Strings, I decided to formalise my table and column names using interface fields. This means I can access them in code and not have to worry about spelling mistakes causing a SQL syntax error:

public interface Tables {
  String SPEAKERS = "speakers";
  String SLOTS = "slots";
  String SESSIONS = "sessions";
  String SESSION_SELECTIONS = "session_selections";
}

public interface SpeakersColumns {
  String SPEAKER_NAME = "name";
  String SPEAKER_BIO = "bio";
  String THUMBNAIL_URL = "thumbnail_url";
}

An example of the SQL create table syntax:

db.execSQL("CREATE TABLE " + Tables.SPEAKERS + " ("
              + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
              + SpeakersColumns.SPEAKER_NAME + " TEXT NOT NULL,"
    	      + SpeakersColumns.SPEAKER_BIO + " TEXT,"
    	      + SpeakersColumns.THUMBNAIL_URL + " TEXT)");

Parsing the JSON

The Android SDK includes the org.json JSON parsing library, and for a relatively-small set of JSON files, this is more than acceptable to use as a parser. A word of caution – this library builds an in-memory representation fo a JSON file, so for larger files could be a bit cumbersome. Either way, it’s important to do this loading on a background thread. Keeping I/O off the main thread is a good idea – if the main thread ‘hangs’ for more than 5 seconds, Android will terminate your application with an ‘Application Not Responding’ message. To parse the speakers data, first I need to create a SQL String which will be executed for each row of data I’d like to insert:

String sql = "INSERT INTO " + Tables.SPEAKERS + " ( "
  + SpeakersColumns.SPEAKER_NAME + ", "
  + SpeakersColumns.SPEAKER_BIO + ", "
  + SpeakersColumns.THUMBNAIL_URL +
  " ) VALUES (?, ?, ?)";

As I’m inserting multiple rows, I’d like to wrap all of this up into a transaction. This makes the entire procedure atomic, in the sense of it fails or it doesn’t. A transaction can be started using database.beginTransaction(); When the database operation is complete, call database.setTransactionSuccessful(); If something bad happens, then a SQLException will be thrown. Therefore, it’s important to call database.endTransaction(); in a finally block. If the end transaction call occurs without setTransactionSuccesful(); being called first, the entire operation will be rolled back:

database.beginTransaction();
SQLiteStatement s = database.compileStatement(s);
try {
  // do database operations
  database.setTransactionSuccessful();
} finally {
  database.endTransaction();
}

Firstly, I’ll read the JSON data from the raw resource into a String by constructing an InputStream:

database.beginTransaction();
SQLiteStatement s = database.compileStatement(s);
InputStream is = ctx.getResources().openRawResource(R.raw.speakers);
  try {
    BufferedReader reader = new BufferedReader(new InputStreamReader(is));
    final StringBuilder builder = new StringBuilder();
    for (String line = null; (line = reader.readLine()) != null;) {
      builder.append(line);
    }

    String json = builder.toString();

    // Do database operations
    database.setTransactionSuccessful();
  } catch (IOException ex) {
    Log.d("IO", "Failed to read R.raw.speakers");
  } finally {
    try {
      is.close();
    } catch (IOException ex) {
    } finally {
      database.endTransaction();
    }
}

It’s worth noting that you should always close your InputStream in a finally block, and that this itself can throw an IOException!

Now that we have a String representation of the JSON, it’s time to do the parsing and insert each speaker into the database! The org.json library makes this fairly easy – each JSON object corresponds to a JSONObject instance. Elements inside an object can be accessed by name and it’s possible to iterate over a JSONArray. For each speaker, I use the compiled SQL statement and call a bindX() method to bind a database column to the appropriate value. When I’m done, I call execute on the SQLStatement. A word of warning – SQLiteStatement starts its index from 1, not 0! This is in contrast with Cursor, which is used to retrieve data, which indexes from 0. Sigh.

database.beginTransaction();
SQLiteStatement s = database.compileStatement(s);
InputStream is = ctx.getResources().openRawResource(R.raw.speakers);
  try {
    BufferedReader reader = new BufferedReader(new InputStreamReader(is));
    final StringBuilder builder = new StringBuilder();
    for (String line = null; (line = reader.readLine()) != null;) {
      builder.append(line);
    }

    String json = builder.toString();
    if (json != null) {
    JSONObject obj = new JSONObject(json);
    JSONArray speakers = obj.getJSONArray("speakers");
    JSONObject speaker;
    for (int i = 0; i < speakers.length(); i++) {
      speaker = speakers.getJSONObject(i);
      s.clearBindings();
      s.bindString(1, speaker.getString("name"));
      s.bindString(2, speaker.has("bio") ? speaker.getString("bio"): "");
      s.bindString(3, speaker.has("thumbnail_url") ? speaker.getString("thumbnail_url") : "");
      s.execute();
    }

    // Do database operations
    database.setTransactionSuccessful();
  } catch (IOException ex) {
    Log.d("IO", "Failed to read R.raw.speakers");
  } finally {
    try {
      is.close();
    } catch (IOException ex) {
    } finally {
      database.endTransaction();
    }
}

The method for parsing sessions and slots is similar, but slightly more advanced. As each slot contains an array of sessions, I basically add a slot into the database using a different call, which returns the primary key of an newly-inserted row:

int slotId = database.executeInsert();

This is needed because a session requires a foreign key reference to the slot it is in. After a slot insertion, I iterate over the array of sessions for that slot, and insert those into the database normally. If a slot only has one session - such as a lunch break, I’ll map this slot_id and session_id as a user-selection in the session_selections table. If there’s more than one session for a slot, I don’t add anything into this table - I’ll leave that up to the user to decide!

Upgrading your database

You can increment the version number of your database as part of an application update, and your SQLiteOpenHelper will handle this by calling onUpgrade. Should this happen, I opt to dump the database and re-build it. To the organisers of DDD East Anglia – don’t go changing any sessions!

@Overridepublic void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
  database.execSQL("DROP TABLE IF EXISTS " + Tables.SPEAKERS);
  database.execSQL("DROP TABLE IF EXISTS " + Tables.SLOTS);
  database.execSQL("DROP TABLE IF EXISTS " + Tables.SESSIONS);
  database.execSQL("DROP TABLE IF EXISTS " + Tables.SESSION_SELECTIONS);
  onCreate(database);
}

Now I have an SQLite database populated with data from some JSON files. I’m still debating on the best way to retrieve this data from the database. Shall I deal with my database directly, or indirectly through a ContentProvider?