Custom ContentProvider for complex sql database with multiple tables

Question

I am trying to create a library that includes functionality in an already written app. The old app uses a complex sqlite database. I am trying to reuse the old database as the backend for a content provider and can't figure out what would be "best practice" for what I am trying to do and how to adapt the various examples (this, this, and others) I have found to my situation.

Supoose you have this database structure

+------+-------------+-------------+-------------+
| Root |   Folder    |    Item     |  Subitem   |
+------+-------------+-------------+-------------+
| _id  | _id         | _id         | _id         |
| uuid | parent_uuid | parent_uuid | parent_uuid |
|      | uuid        | uuid        | uuid        |
|      | name        | name        | name        |
|      | data        | data        | data        |
+------+-------------+-------------+-------------+
Root->Folder->Item->Subitem

Before, I used a DbAdapter class where I provided function calls that took parameters such as parent_uuid, handled all the sql query stuff inside the function, then returned a cursor with the results

example function stubs:

• get_items_by_parent_uuid(folder_uuid)
• get_item_by_uuid(uuid)
• same for Subitem also

Complex queries

• get_items_for_root(root_uuid)
  • returns cursor with item uuid, item name, item data, folder name
• get_items_with_subitem_count(folder_uuid)
  • returns cursor with item uuid, item name, item data, count of subitems where subitem.parent_uuid == item.uuid

I can't figure out the best way to provide the functionality above with 1 ContentProvider. I don't need someone to write me tons of code (but if you do I'm ok with that too), I just want someone to help me understand how to modify the above linked examples to do these things, because I do mostly understand the examples, just not enough to translate them to my current needs.

TL;DR; - How do I write a single ContentProvider that handles multiple tables, doesn't depend on _id as the unique identifier, and handles joins along with selects that may have inner-selects/queries (such as select count(*))

Answer 1

How do I write a single ContentProvider that handles multiple tables

Step #1: Design a REST interface for your schema, limiting yourself to a simple JSON object as the data structure (i.e., no nested arrays or objects, just a map of keys to simple values)

Step #2: Convert that design to a ContentProvider, replacing the http://sooperapp.jp36.com/ with content://com.jp36.sooperapp and replacing the JSON with Cursors and ContentValues

So, for example, you might support content://com.jp36.sooperapp/folder and content://com.jp36.sooperapp/item and content://com.jp36.sooperapp/subitem as the basis of retrieving/modifying information about one or more of each of those types.

doesn't depend on _id as the unique identifier

If you plan on using CursorAdapter, and assuming that by uuid you really do mean a UUID (which is typically a string), then you have no choice but to also have _id. If, however, you do not plan on using CursorAdapter, you have no particular need for _id.

and handles joins along with selects that may have inner-selects/queries (such as select count(*))

That's all a matter of your REST interface/ContentProvider design. If you want to have content://com.jp36.sooperapp/folder/count be something you query upon that, behind the scenes, does SELECT COUNT(*) FROM Folder, knock yourself out.

(note: do not literally knock yourself out)

If you want content://com.jp36.sooperapp/omg/omg/omg/this/is/a/long/path to INSERT an Item and 17 Subitems based upon some insert() call to the provider, go right ahead. ContentProvider is merely a facade; it is up to you to define what the Uri means, what the ContentValues mean, what the query() parameters mean, etc.

Personally, I would recommend that you step back and ask yourself why you are bothering with a ContentProvider.