Android: SQLite one-to-many design

Question

Anyone has good advise on how to implement one-to-many mapping for SQLite using ContentProvider? If you look at Uri ContentProvider#insert(Uri, ContentValues) you can see that it has ContentValues param that contains data to insert. The problem is that in its current implementation ContentValues does not support put(String, Object) method and class is final so I cannot extend it. Why it is a problem? Here comes my design:

I have 2 tables which are in one-to-many relationship. To represent these in code I have 2 model objects. 1st represents the main record and has a field that is a list of 2nd object instances. Now I have a helper method in the model object #1 which returns ContentValues generated off the current object. It's trivial to populate a primitive fields with ContentValues#put overloaded methods but I'm out of luck for the list. So currently since my 2nd table row is just a single String value I generate a comma delimited String which then I reparse to String[] inside ContentProvider#insert. That feels yucky, so maybe someone can hint how it can be done in cleaner fashion.

Here's some code. First from the model class:

public ContentValues toContentValues() {
    ContentValues values = new ContentValues();
    values.put(ITEM_ID, itemId);
    values.put(NAME, name);
    values.put(TYPES, concat(types));
    return values;
}

private String concat(String[] values) { /* trivial */}

and here's slimmed down version of ContentProvider#insert method

public Uri insert(Uri uri, ContentValues values) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    db.beginTransaction();
    try {
        // populate types
        String[] types = ((String)values.get(Offer.TYPES)).split("|");
        // we no longer need it
        values.remove(Offer.TYPES);
        // first insert row into OFFERS
        final long rowId = db.insert("offers", Offer.NAME, values);
        if (rowId > 0 && types != null) {
            // now insert all types for the row
            for (String t : types) {
                ContentValues type = new ContentValues(8);
                type.put(Offer.OFFER_ID, rowId);
                type.put(Offer.TYPE, t);
                // insert values into second table
                db.insert("types", Offer.TYPE, type);
            }
        }
        db.setTransactionSuccessful();
        return ContentUris.withAppendedId(Offer.CONTENT_URI, rowId);
    } catch (Exception e) {
        Log.e(TAG, "Failed to insert record", e);
    } finally {
        db.endTransaction();
    }

}

Answer 1

I think you're looking at the wrong end of the one-to-many relationship.

Take a look at the ContactsContract content provider, for example. Contacts can have many email addresses, many phone numbers, etc. The way that is accomplished is by doing inserts/updates/deletes on the "many" side. To add a new phone number, you insert a new phone number, providing an ID of the contact for whom the phone number pertains.

You would do the same if you had a plain SQLite database with no content provider. One-to-many relationships in relational databases are achieved via inserts/updates/deletes on a table for the "many" side, each having a foreign key back to the "one" side.

Now, from an OO standpoint, this isn't ideal. You are welcome to create ORM-style wrapper objects (think Hibernate) that allow you to manipulate a collection of children from the "one" side. A sufficiently-intelligent collection class can then turn around and synchronize the "many" table to match. However, these aren't necessarily trivial to implement properly.

Answer 2

You can use ContentProviderOperations for this.

They are basically bulk operations with the ability to back-reference to the identifiers generated for parent rows.

How ContentProviderOperations can be used for a one-to-many design is very well explained in this answer: What are the semantics of withValueBackReference?

Answer 3

So I'm going to answer my own question. I was on the right track with having two tables and two model objects. What was missing and what confused me was that I wanted directly insert complex data through ContentProvider#insert in a single call. This is wrong. ContentProvider should create and maintain these two tables but decision on which table to use should be dictated by Uri parameter of ContentProvider#insert. It is very convenient to use ContentResolver and add methods such as "addFoo" to the model object. Such method would take ContentResolver parameter and at the end here are the sequence to insert a complex record:

1. Insert parent record through ContentProvider#insert and obtain record id
2. Per each child provide parent ID (foregn key) and use ContentProvider#insert with different Uri to insert child records

So the only remaining question is how to envelope the above code in transaction?