Having ordered SQlite database (based in XML feed)

Question

Hey all

I'm having a go at developing my first android application and have never really used databases before. Anyways I need to populate and update a database based on XML files retrieved from the web on a regular basis (say once a day).

Just thinking about it iv already run into some problems...Basically I need to display information about artists and events ordered by date. For this I plan on backing the view by the sqlite database which already has each row sorted by date. So the columns would be "date", "artist", "venue", etc. and I'm thinking I would need a "row ID" column as well as a primary key?

Now the idea I have is to do an insertion sort so that whenever I'm inserting an entry into the database it will be in order and seem fairly simple to implement. I'm basing my DB Helper class from the notepad example here http://developer.android.com/resources/tutorials/notepad/notepad-ex1.html (NotesDbAdapter). But the way they insert new items is by using ContentValues and it just gets inserted as the next item in the database. How would I insert items somewhere in the middle so that it is in order by date? Or is it better to have the database unordered and just order it outside the database when needed?

Also basically I want to download the XML file from say last.fm once a day and then put any new entry into the database...what is the best way to do this? Would rebuilding the database be the way to go?

Thanks

Answer 1

I definitely wouldn't worry about insertion order. You can add an orderBy field to a query so that the database orders the results.

Cursor c = db.query("CONCERTS", new String[]{"venue", "artist", "date"},
     null, null, null, null, "date"/*orderBy*/);

Also. How big are these files? It might be easier just to download the xml each time and not worry about storing in a database.