I want to write an Android ListAdapter to serve data from a database that look like this:
CREATE TABLE note (_id integer primary key autoincrement,
content text not null)
CREATE TABLE tag (_id integer primary key autoincrement,
name text not null);
CREATE TABLE relation (_id integer primary key autoincrement,
noteid integer not null,
tagid integer not null,
idx integer not null);
I use this database to store notes and their associated tags as an ordered list. 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 note._id AS noteid,
note.content,
relation.idx AS tagidx,
tag.name
FROM note
LEFT JOIN relation ON (relation.noteid = note._id)
LEFT JOIN tag ON (tag._id = relation.tagid)
ORDER BY noteid, tagidx;
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.
The requery scenario is the actual reason that I needed two cursors in the first place. I have not figured out a way to count the number of notes in a required cursor other than asking the database using another cursor.
This is the best I have so far. I want to check the sanity of this approach with this group. :-) Is this two cursor approach too complicated? Perhaps I have missed some part of the API that solves this nicely for me?
