Search code examples
androiddatabasesqliteandroid-contentproviderjunction-table

Android Content Provider SQLite Junction Tables

I'm implementing a Content Provider, which is backed by a fairly complex SQLite DB schema. The database has a few junction tables and I'm unsure whether they should be visible to the user of the Content Provider or not.

The parent tables are exposed via the Contract, each one has its own content URI, etc. Now, when inserting data via ContentResolver#applyBatch() method, I create ContentProviderOperation per each table's content URI. So far everything is clear. But my problem is, how should the junction tables be populated, as they don't have their own content URIs?

To illustrate this, here's an example. I have 2 "parent" tables, Movies and Actors. The relationship between them is many-to-many and therefore I have a junction table called MoviesActors.

To insert at one batch I do the following:

List<ContentProviderOperation> operations = new ArrayList<>;
// movie
operations.add(ContentProviderOperation.newInsert(Contract.Movie.ContentUri).withValue("movie_id", "23asd2kwe0231123sa").build());
// actor
operations.add(ContentProviderOperation.newInsert(Contract.Actor.ContentUri).withValue("actor_id", "89asd02kjlwe081231a").build());
getContentResolver().applyBatch(authority, operations);

The junction table MoviesActors should be inserted with a row containing movie_id and actor_id. How do I take care of the junction table in this situation?

The only thing, which comes to my mind is extend the Contract to have content URI pointing to the junction tables and add another ContentProviderOperation, since otherwise, how do you communicate movie_id and actor_id to ContentProvider#applyBatch()?

I rather not expose the junction table to the user of the ContentProvider, but I might be wrong here... perhaps that's how it should be done on Android?

I've searched this topic for days already and haven't found an answer to that. Any help would be greatly appreciated.

Bonus question:

Is it necessary to expose every single table via the Contract? For instance, when having child tables in one-to-many relationship. I'm specifically referring to Insert/Update/Delete since I know with Query I can simply do a join, but maybe I'm wrong also here.

Thanks a lot!

NOTE: I'm not interested in 3rd party library solutions.

Solution

  • I think you're tackling the problem from the wrong end. You're trying to design an interface to match your database structure, but the interface should come first.

    In the first place, the interface should meet all the requirements of your ContentProvider client. If your ContentProvider client needs access to the junction table you'll have to expose it (in some way, see below), otherwise you don't have to. A good interface hides the actual implementation details, so the ContentProvider client doesn't need to care about whether the ContentProvider is backed by an SQLite database, by a bunch of in-memory maps or even a web-service.

    Also, you should not think of a ContentProvider just as an interface to a database and the Contract as the database schema. A ContentProvider is much more versatile and powerful than that. The major difference is that ContentProviders are addressed by URIs whereas in SQL you just have table names. In contrast to a table name, a URI has a structure. URIs have a path that identifies the object (or directory of objects) that you want to operate on. Also you can add query parameters to a URI to modify the behavior of an operation. In this respect a ContentProvider can be designed much like a RESTful service.

    See below for a concrete (but incomplete) example of a Contract of a simple movie database. This is basically how one would design a RESTful web-service, except for one thing: Just like in your code, movie-id and actor-id are provided by the caller. A real RESTful service would create and assign these automatically and return them to the caller. A ContentProvider can only return long IDs when inserting new objects.

    Insert a new movie

    insert on /movies/

    Values: {"movie_id": <movie-id>, "title": <movie-title>, "year": ...}

    Insert a new actor

    insert on /actors/

    Values: {"actor_id": <actor-id>, "name": <actor-name>, "gender": ...}

    Add an existing actor to a movie

    insert on /movies/movie-id/actors/

    Values: {"actor_id": <actor-id>}

    Add an existing movie to an actor:

    insert on /actors/actor-id/movies/

    Values: {"movie_id": <movie-id>}

    Optional: add a new actor directly to a movie:

    insert on /movies/movie-id/actors/

    Values: {"actor_id": <actor-id>, "name": <actor-name>, "gender": ... }

    If no actor with the given id exists, this operation will create the new actor and link it to the movie in a single step. If an actor with this ID already exists an exception would be thrown. The same could be done the other way round, adding a new movie to an actor.

    Delete an actor from a movie

    delete on /movies/movie-id/actors/actor-id

    or

    delete on /actors/actors-id/movies/movie-id

    Get all movies

    query on /movies/

    Get a specific movie

    query on /movies/movie-id

    Get all actors playing in a specific movie

    query on /movies/movie-id/actors/

    Get all movies a specific actor has played in

    query on /actors/actor-id/movies/

    The optional query selection statement can be used to filter the result. To get movies from the last 10 years a specific actor has played in, you would add the selection movies_year>=2005 to the last query.

    By using a contract like this you wouldn't expose the junction table, instead you provide a REST-like interface to your database.

    The job of the ContentProvider is to map these operations onto the database or any other back-end.