ListAdapter design advice

Question

I want to write a ListAdapter to serve data from a database that look like this:

CREATE TABLE notes (_id integer primary key, 
                    content text);
CREATE TABLE tags (_id integer primary key,
                   name text,
                   pos integer
                   noteid integer,
                   foreign key(noteid) references notes(_id));

I use this database to store notes and their associated tags. One requirement of the ListAdapter is that it must be able to update the ListView contents if the underlying data changes. I can query all the notes in the database with this query:

select notes._id as id, notes.content, tags.pos, tags.name 
  from notes left join tags on id = tags.noteid
  order by id, tags.pos;

Which will give me results looking like this (null values shown for clarity):

0|foo bar baz|0|x
1|hello world|null|null
2|one more nn|0|yy
2|one more nn|1|y

As you can see, a note with more than one tag will be located on more than one row in the result. This means that I can't look at the cursor size to determine the number of notes, I need to traverse the entire Cursor to get a count. I don't want to do that.

The solution I have come up with so far is to use two cursors: one with the query mentioned above and one with a query containing the number of rows in the notes table (select count(*) from notes). In the constructor I call intializeCursors():

private void initializeCursors() {
    notesCursor.moveToFirst();
    countCursor.moveToFirst();
    count = countCursor.getInt(0);
}

I have implemented getItem() like this:

public Note getItem(int position) {
    // notes is a List of notes that we have already read.
    if (position < notes.size()) { 
        return notes.get(position);
    }

    int cursorPosition = notes.size();
    while (cursorPosition <= position) {
        // Creates a note by reading the correct number of rows.
        Note note = NotesDb.noteFrom(notesCursor); 
        notes.add(note);
        ++cursorPosition;
    }
    return notes.get(position);
}

The adapter assumes that the cursors are being managed by some activity that has called startManagingCursor() on them.

So far so good, I guess. The problem is now how to handle the cursor being requeried. Since I have two cursors I need to register listeners for both of them and when I have received onChange() for both of them I can initializeCursors() and notify any listeners registered to my ListAdapter of a change in the its data.

This is the best I have so far. I want to check the sanity of this approach with this group. :-) Is this way to complicated? Perhaps I have missed some part of the API that solves this nicely for me?

Thanks in advance!

Answer 1

Depending on how your ContentProvider is implemented this functionality might be handled for you automatically if you chose to extend a CursorAdapter instead of a ListAdapter.

If you, in your ContentProvider, call something like:

getContext().getContentResolver().notifyChange(uri, myObserver);

whenever you change something (insert, delete, update), then (according to the documentation on the 'notifyChange()' function: http://developer.android.com/reference/android/content/ContentResolver.html) CursorAdapters will get this notification by default.

Finally I will take me the liberty of commenting a bit on your database structure (please don't feel offended, I don't mean to be rude, I just want to help).

NOTE #1 Many database architects state that it's "de facto standard" to name tables in singular, hence your two tables would maybe look a bit more professional if they were named "note" and "tag".

NOTE #2 It also seems like there is a many-to-many relation between your notes and tags (one note can have many tags, but one tag can also belong to many notes). It is then preferred, in order to avoid duplicate data in your "tag" table, to separate the "notes" and "tags" completely from each other and introduce a third table, say, "relation", which will describe the relation between your notes and your tags. You would then have three tables like:

CREATE TABLE note (_id INTEGER PRIMARY KEY, 
                   content TEXT);

CREATE TABLE tag (_id INTEGER PRIMARY KEY,
                  name TEXT,
                  pos INTEGER);

CREATE TABLE relation (_id INTEGER PRIMARY KEY,
                       note_id INTEGER,
                       tag_id INTEGER);

This architectural change would add complexity to your database but it would also make it more fault tolerant (especially to changes), faster from some perspectives (the database wouldn't need to test for all those duplicates when filtering for a query), and less space consuming. These "new features" become even more interesting as your database grows.

A typical SELECT query where everything is selected would then look something like this:

SELECT note._id AS noteid, note.text, tag.name, tag.pos FROM note
LEFT JOIN relation ON (relation.note_id = note._id)
LEFT JOIN tag ON (tag._id = relation.tag_id);

NOTE #3: This brings me to my final point. I don't really know what your application looks like, so I might very well be wrong here, but nevertheless: Do you really need to get all info from both tables (currently "notes" and "tags") joined together at once? Is it possible to only fetch all "notes" alone and display them in some sort of list or other GUI constellation and fetching the corresponding tags only when editing or viewing a single note? This way you would get rid of your extra "count" cursor.

A SELECT query for fetching all tags belonging to a given note would look something like this:

SELECT tag.* FROM relation
LEFT JOIN tag ON (tag._id = relation.tag_id)
WHERE relation.note_id=12;

You would of course replace '12' with whatever id the current note has.

I hope you can make some use of of my (very long :-) answer.