Android ContentProvider database query multiple tables

Question

Hello all,

I'm writing an RSS reader for Android. I've faced a certain difficulty which the problem I can't resolve since databases aren't my expertise.. So i figured out maybe one of you could help me out! I currently have 3 tables (Categories, links and feeds). My goal is too link a feed to multiple categories. Therefor I'm using a Link table. My databases is an Android ContentProvider (sqlite) and looks like the following:

| Categories |          |  Links  |          | Feeds |
|------------|          |---------|          |-------|
|  _ID       |          | Category|          | _ID   | 
|  Title     |          | Feed    |          | Title |
                                             | URL   |

I currently wrote the following code in my FeedListActivity to retrieve a list of links and their feeds.

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    //gets the intent URI to retrieve the Feeds.
    Intent intent = getIntent();
    if (intent.getData() == null) {
        intent.setData(Feeds.CONTENT_URI);
    }
    // stores the Category id so it knows from what category it came from
    mCatId = intent.getIntExtra("catId", -1);   
    getListView().setOnCreateContextMenuListener(this);
    // gets all Links that have the category placed
    Cursor links = managedQuery(
            Links.CONTENT_URI, 
            NewsReadUtils.LINK_PROJECTION_STRING, 
            Links.CATEGORY+ " = "+ mCatId, null, null);
    String query = "";
    Cursor cursor = null;
    if(links.getCount()>0){
            // if there are links available try to get them and build a query.
        links.moveToFirst();
        do{
            if (links.isFirst()) {
                query = Feeds._ID+ " = "+links.getString(links.getColumnIndex(Links.FEED));                 
            }else{
                query += " OR " +Feeds._ID+ " = "+links.getString(links.getColumnIndex(Links.FEED));

            }               
        }while(links.moveToNext()); 
        cursor = managedQuery(getIntent().getData(), PROJECTION ,query, null,
                Feeds.DEFAULT_SORT_ORDER);
    }
    Cursor cursor = managedQuery(getIntent().getData(), PROJECTION ,Feeds._ID+ " = "+ mCatId, null,
            Feeds.DEFAULT_SORT_ORDER);
    SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, cursor,
            new String[] { Feeds.TITLE }, new int[] { android.R.id.text1 });
    setListAdapter(adapter);       

}

Now my question:

I was wondering how I could optimize this database layout, code or query so I would get my entries in a more efficient way. Because i believe this link table or the query to retrieve links isn't needed! Or am i doing this the correct way?

Thanks,

Antek

Answer 1

My databases is an Android ContentProvider

Why?

I was wondering how I could optimize this database layout, code or query so I would get my entries in a more efficient way.

Directly query the SQLite database, using a suitable JOIN clause in your SQL.

If this is your data, in your application, you should not be going through a ContentProvider to access it. A ContentProvider is only needed for sharing data between applications.

Answer 2

While CommonsWare's answer is right to some degree I find it doesn't fully answer the question.

Normally ContentProviders are a way to share/expose the application's data and in fact this may often be the case. Still, there may arise the situation where one needs to join multiple tables using a ContentProvider.

The great thing about Android is that it is Open Source, so nothing prevents you from going to search in the Android apps for similar scenarios. This is what I usually do in such situations. The ContactsProvider.java is a good example. It's a bit complex but working through it may give some insights on how to use joins within ContentProviders. If you do not want to download the source you may use Google Code Search for browsing it:

http://www.google.com/codesearch/p?hl=en#cbQwy62oRIQ/src/com/android/providers/contacts/ContactsProvider.java&q=ContactsProvider.java&sa=N&cd=1&ct=rc

Good luck :)