I recently developed a feature that grabbed some json from the server, synced it to a local sqlite database and then notified the UI to display the updates (Sqlbrite ftw). This particular piece of data had a field isRead that will be marked as true if the user dismissed it in the UI, and this tells the app never to show that data again.

It's a lightweight solution to tell the app to show the user a generic message.

Let's say the data model looked like this

public class Alert {  
    private final String alertId;
    private final String alertTitle;
    private final String alertText;
    private final String alertActionText;
    private final String alertActionUrl;
    private final Boolean isRead;

    ...

}

And the database looked like this

private static final String TABLE_CREATE_ALERTS = "CREATE TABLE "  
        + ALERTS + "(" 
        + ALERT_ID + " text UNIQUEE, " 
        + ALERT_TITLE + " text, " 
        + ALERT_TEXT + " text, " 
        + ALERT_ACTION_TEXT + " text, " 
        + ALERT_ACTION_URL + " text, " 
        + ALERT_IS_READ + " integer default 0);";

After making a request to the server you might expect to sync the Alert's returned like so

@Override
public Observable<Alert> syncAlerts(final List<Alert> alerts) {

    Observable<Alert> observable = Observable.from(alerts)
        .doOnNext(new Action1<Alert>() {
            @Override
            public void call(Alert alert) {

                Cursor doesExistCursor = null;
                BriteDatabase.Transaction transaction = db.newTransaction();
                try {
                    doesExistCursor = db.query(AlertQueries.byAlertIdQuery(alert.getAlertId()));

                    //then the row exists and we shouldn't insert
                    if (doesExistCursor != null && doesExistCursor.moveToNext()) {

                        return;
                    }

                    ContentValues values = new AlertsContentValuesBuilder()
                            .withAlertId(alert.getAlertId())
                            .withAlertTitle(alert.getAlertTitle())
                            .withAlertText(alert.getAlertText())
                            .withActionText(alert.getActionText())
                            .withActionUrl(alert.getActionUrl())
                            .build();

                    db.insert(MyOpenHelper.TABLE_ALERTS, values, SQLiteDatabase.CONFLICT_IGNORE);
                    transaction.markSuccessful();
                } finally {
                    transaction.end();
                    if (doesExistCursor != null) {
                        doesExistCursor.close();
                    }
                }

            }
        });
    return observable;
}

Basically, this snippet queries to see if the Alert is already in our local database, and if not, inserts it.

There are two shortcomings in this code (and probably more, so... use comments section)

  1. We are running multiple trips to the database (select and insert)
  2. If the Alert exists we don't update the fields that might have changed since the last server call
ON CONFLICT REPLACE

The solution we came up with to these two problems was to add the ON CONFLICT REPLACE to the alertId field

private static final String TABLE_CREATE_ALERTS = "CREATE TABLE "  
        + ALERTS + "(" 
        + ALERT_ID + " text UNIQUE ON CONFLICT REPLACE, " 
        + ALERT_TITLE + " text, " 
        + ALERT_TEXT + " text, " 
        + ALERT_ACTION_TEXT + " text, " 
        + ALERT_ACTION_URL + " text, " 
        + ALERT_IS_READ + " integer default 0);";

...and then create a query to utilize it

private static final String ALERT_INSERT_OR_REPLACE = "INSERT OR REPLACE INTO " +  
        MyOpenHelper.TABLE_ALERTS +" ( "  +
        MyOpenHelper.ALERT_ID + " , " +
        MyOpenHelper.ALERT_TITLE + " , " +
        MyOpenHelper.ALERT_TEXT + " , " +
        MyOpenHelper.ALERT_ACTION_TEXT + " , " +
        MyOpenHelper.ALERT_ACTION_URL + " , " +
        MyOpenHelper.ALERT_IS_READ +  ") VALUES (?, ?, ?, ?, ?, COALESCE((SELECT " +
        MyOpenHelper.ALERT_IS_READ + " FROM " +
        MyOpenHelper.TABLE_ALERTS + " WHERE " +
        MyOpenHelper.ALERT_ID + " = ?), 0));";

This is a little hard to read, but here is what is happening

  1. We are using the INSERT OR REPLACE which tells the database that if the insert conflicts then to replace with the new values. We key off of the alertId in the table definition.
  2. We are also ensuring that the sync doesn't overwrite the isRead field. We do this by selecting the current value out of the field, and if it doesn't exist then we COALESCE a default value of 0.

The updated syncAlerts() now looks like (we changed the signature also since we really didn't need to observe the change).

@Override
public void syncAlerts(final List<Alert> alerts) {

    for (Alert alert: alerts) {
        BriteDatabase.Transaction transaction = db.newTransaction();
        try {
            db.executeAndTrigger(MyOpenHelper.TABLE_ALERTS, 
                ALERT_INSERT_OR_REPLACE, 
                alert.getAlertId(), 
                alert.getAlertTitle(), 
                alert.getAlertText(), 
                alert.getActionText(), 
                alert.getActionUrl(), 
                alert.getAlertId());
            transaction.markSuccessful();
        } catch (Exception e) {
            Logger.errorNotify(e);
        } finally {
            transaction.end();
        }
    }
}

*One last thing to note is that you should call BriteDatabase.executeAndTrigger(...) instead of just BriteDatabase.execute(...) if you want your queries to be notified of a change.

So, by slightly complicating our insert query, we're able to leverage Sqlite's INSERT OR REPLACE INTO to considerably uncomplicate our code and make it betterâ„¢.

Thanks for reading, please feel free to comment on how I might improve.