0

I want to cache RSS feeds into SQLite database to improve performance of the app.
I am only concerned with the very basic of the RSS feed namely the title, description, URL guid and the pubDate. As of now, I can process them and store them into Vector<Feed> where Feed is my custom class.

There area few things which I find mind-boggling as a newbie to Android.
Say I have feed items whose titles are as below: 

1. Cat Kills the Dog
2. Curosity Killed the Cat in Revenge
3. Y U NO leave Cat alone ?

In the first run, getting the row count of the table will return zero which means that I have to add all the feeds into the database. No problems.
The problem comes when the feeds are updated. Here is the updated feed: 

*. Quick Brown Fox Jumps Over The Lazy Dog. Dog Dies.
1. Cat Kills the Dog
2. Curosity Killed the Cat in Revenge
3. Y U NO leave Cat alone ?

The logic is:
1. Start parsing the RSS feeds.
2. Read the title of the newly fetched RSS feed.
3. Does it match the title of the first row of the data base?
3.1. No, then keep parsing and add this feed to the database.
3.2. Yes, then stop parsing and add this feed to the database.

Problem is, the row added now will be the LAST ROW of the table while it should be, as in the RSS XML feed, the first row so that subsequent comparison becomes easier.

The thing is to maintain the order of rows similar to that in the feed XML.
How do I maintain that order ?

Or, is there a better way to organize and store the feeds ?

An SO User
  • 24,612
  • 35
  • 133
  • 221

1 Answers1

1

First off, do not rely on the titles being unique. Instead, use the guid field which is meant to be the unique identifier for the item (see the RSS specification).

Updating feed

Go through each item in the retrieved feed and see if the guid already exists in the database, if so, skip it.

If it does not exist, create an entry.

So, revising your logic:

  1. Start parsing the RSS feeds.
  2. Read the guid of the newly fetched RSS feed.
  3. Does it match the guid of any row in the database? 3.1. No, then keep parsing and add this feed to the database. 3.2. Yes, then stop parsing and add this feed to the database.

Order of the items

There is also a field called pubDate which indicates when the item was published. Simply store the pubDate for each item in the database and use it in an ORDER BY clause when retrieveing:

SELECT * FROM items ORDER BY pubDate DESC

That will give you the items in order of original publication.

UPDATE: Since SQLite does not have a native data type for dates, you could either utilize the time/date helper functions or convert pubDate to epoch format in your code before you insert it into the database.

Community
  • 1
  • 1
Niklas Lindblad
  • 1,031
  • 6
  • 9
  • `pubDate` comes in this format `Fri, 09 Aug 2013 22:46:12` Do I directly store it in the table ? – An SO User Aug 11 '13 at 18:05
  • Apparently SQLite stores dates as TEXT (http://www.sqlite.org/datatype3.html#datetime). The easiest way would be to store the `pubDate`as an INTEGER and convert it to UNIX epoch (http://stackoverflow.com/questions/6687433/convert-date-format-to-epoch). – Niklas Lindblad Aug 11 '13 at 18:11
  • And the reverse process at the time of retrieval so that I can convert to `Feed` objects ?? :) – An SO User Aug 11 '13 at 18:15
  • @LittleChild http://stackoverflow.com/questions/535004/unix-epoch-time-to-java-date-object – Niklas Lindblad Aug 11 '13 at 19:29
  • Another situation is where the parsing of feeds stops abruptly either because user pressed stop or because the internet connection was loss. Say only 60 / 100 feeds were processed. How do I manage that ? One is to maintain another table that knows whether errors occurred or not – An SO User Aug 12 '13 at 00:18
  • @LittleChild You might want to look into transactions in SQLite (http://www.sqlite.org/transactional.html): " All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by a program crash, an operating system crash, or a power failure." – Niklas Lindblad Aug 12 '13 at 12:01
  • NO, not that !! My app has the option whereby the user can stop processing the feeds in which case he/she will only see the processed feeds on the screen. :) – An SO User Aug 12 '13 at 13:17
  • 1
    Then start a transaction each item you do an `INSERT` and it will "clean up" if the user pressed quit before that `INSERT` was complete. – Niklas Lindblad Aug 12 '13 at 14:01
  • The thing is , how do I keep track of where the user cancelled ? :) – An SO User Aug 12 '13 at 14:44
  • If you only add items where the `guid` does not already exist (as suggested), there is no need to keep track. – Niklas Lindblad Aug 12 '13 at 16:18