Creating an RSS feed reader in Flex that uses an SQL database

Published Date
20 - Apr - 2011
| Last Updated
20 - Apr - 2011
 
Creating an RSS feed reader in Flex that uses an SQL database

In our previous tutorials we covered how to display RSS feedsin a Flex application, and how to use an SQL database in your AIR application. In this tutorial we will combine both to create an RSS reader application that stores its data in an SQL database.

Our first RSS reader could read a single feed, while our second one could read multiple feeds, however both of them suffered from one grave flaw, any RSS feeds you added would be gone at the next restart, and so would any loaded stories.

To simplify matters, we will take a step back, and use only a single feed that will be hard-coded in the application. We will use this website's feed for example, but you are free to use a different one, and it should work.

To further simplify things we will use an RSS feed library developed by Adobe called as3syndicationlib. To use it with your project, do the following (after creating a new project for this tutorial):

  • Download the as3syndicationlib from its Google Code page
  • Extract the zip file to a known folder
  • Copy the xmlsyndication.swc file to the libs folder of your project (You can directly drag and drop into into the right place in Flash Builder)

Flash Builder projects are configured to load any external libraries placed in this folder by default.

The FeedData Class

Like last time, we will perform all our database interaction via a class for the same. To work with  RSS feeds, we need to be able to do the following things (for a very basic application):

  • Retrieve all feed entries from the database
  • Add a feed item to the database if it doesn't already exist

Our main application will tell the FeedData class to add feed entries one by one, checking each time if the entry exists and ignoring those that do. It will also retrieve the list of stories when the feed content is refreshed to display the latest data.

Here is our FeedData class that has everything we need:

package
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	
	import mx.collections.ArrayCollection;

	public class FeedData
	{
		private var dbConnection:SQLConnection;
		private var getAllFeedItems:SQLStatement;
		private var addFeedItem:SQLStatement;
		private var checkIfFeedItemExists:SQLStatement;
		
		public function FeedData(databaseFile:File)
		{
			dbConnection = new SQLConnection();
			dbConnection.open(databaseFile);
			
			initializeDB();
			
			getAllFeedItems = new SQLStatement();
			getAllFeedItems.sqlConnection = dbConnection;
			getAllFeedItems.text = "SELECT * FROM feed_items;";
			
			addFeedItem = new SQLStatement();
			addFeedItem.sqlConnection = dbConnection;
			addFeedItem.text = "INSERT OR IGNORE INTO feed_items (guid, title, description) VALUES (?, ?, ?);";
			
		}
		
		private function initializeDB():void
		{
			var createStmt:SQLStatement = new SQLStatement();
			createStmt.sqlConnection = dbConnection;
			createStmt.text = "CREATE TABLE IF NOT EXISTS feed_items (guid TEXT PRIMARY KEY, title TEXT, description TEXT);";
			createStmt.execute();
		}
		
		public function getAll():ArrayCollection
		{
			getAllFeedItems.execute();
			return new ArrayCollection(getAllFeedItems.getResult().data);
		}
		
		public function add(guid:String, title:String, description:String):void
		{
			addFeedItem.clearParameters();
			addFeedItem.parameters[0] = guid;
			addFeedItem.parameters[1] = title;
			addFeedItem.parameters[2] = description;
			addFeedItem.execute();
		}
		
	}
}

Here's a look behind the code:

  • The class constructor initializes the database connection to the specified file
  • It runs the initializeDB function that creates a new table if not already present. The GUID field of RSS feeds should be unique and is thus being used as the primary key.
  • The class constructor also initializes the different statements that will be used in the application. Doing so improves performance.
  • The getAll function retrieves all the feed items from the database, wraps them in an ArrayCollection, and returns them
  • The add function adds a feed item entry to the database based on the details provided


Now for the rest of the application code.

Application Code

 

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
    				   xmlns:s="library://ns.adobe.com/flex/spark" 
					   xmlns:mx="library://ns.adobe.com/flex/mx" 
					   creationComplete="windowedapplication1_creationCompleteHandler(event)"
					   width="1024" height="800"
					   >
	<fx:Script>
		<![CDATA[
			import com.adobe.xml.syndication.generic.FeedFactory;
			import com.adobe.xml.syndication.rss.Item20;
			import com.adobe.xml.syndication.rss.RSS20;
			
			import mx.collections.ArrayCollection;
			import mx.events.FlexEvent;
			
			private static const FEED_URL:String = "http://feeds.feedburner.com/thinkdigit/zpZl";
			private var loader:URLLoader;
			private var dbFile:File;
			private var feedData:FeedData;

			protected function windowedapplication1_creationCompleteHandler(event:FlexEvent):void
			{
				dbFile = File.applicationDirectory.resolvePath("dbfile2.db");
				feedData = new FeedData(dbFile);
				refreshFeed();
			}
			
			private function refreshFeed():void
			{
				feedItems.dataProvider = feedData.getAll();
			}
			
			protected function refreshButton_clickHandler(event:MouseEvent):void
			{
				loader = new URLLoader();
				var ureq:URLRequest = new URLRequest(FEED_URL);
				
				loader.addEventListener(Event.COMPLETE, feedLoaded);
				loader.load(ureq);
			}
			
			private function feedLoaded(e:Event):void
			{
				var rssData:String = URLLoader(e.target).data;
				var rss:RSS20 = new RSS20();
				rss.parse(rssData);
				var feedItems:Array = rss.items;
				
				for each(var item:Item20 in feedItems)
				{
					feedData.add(item.guid.id, item.title, item.description);
				}
				
				refreshFeed();
			}
			
		]]>
	</fx:Script>
	<s:layout>
		<s:VerticalLayout  />
	</s:layout>
	<s:Button id="refreshButton" label="Refresh Feed" width="100%" click="refreshButton_clickHandler(event)" />
	<s:List id="feedItems" width="100%" height="100%" labelField="title" />
	<mx:HTML id = "feedDetails"  width="100%" height="100%" htmlText="{feedItems.selectedItem?feedItems.selectedItem.description:' '}" />
</s:WindowedApplication>


Here's a breakdown:

Initialization code

private static const FEED_URL:String = "http://feeds.feedburner.com/thinkdigit/zpZl";
private var loader:URLLoader;
private var dbFile:File;
private var feedData:FeedData;

protected function windowedapplication1_creationCompleteHandler(event:FlexEvent):void
{
	dbFile = File.applicationDirectory.resolvePath("dbfile2.db");
	feedData = new FeedData(dbFile);
	refreshFeed();
}

private function refreshFeed():void
{
	feedItems.dataProvider = feedData.getAll();
}


This first function runs when the application can completed opening, and initializes the database file and the class we use to manipulate it. After this we are calling the getAll() function to receive all entries already stored in it, making them the source of data for the list of RSS feed entries. This will ensure that the application displays at-least all the old RSS entries.

Feed refresh code

protected function refreshButton_clickHandler(event:MouseEvent):void
{
	loader = new URLLoader();
	var ureq:URLRequest = new URLRequest(FEED_URL);
	
	loader.addEventListener(Event.COMPLETE, feedLoaded);
	loader.load(ureq);
}

private function feedLoaded(e:Event):void
{
	var rssData:String = URLLoader(e.target).data;
	var rss:RSS20 = new RSS20();
	rss.parse(rssData);
	var feedItems:Array = rss.items;
	
	for each(var item:Item20 in feedItems)
	{
		feedData.add(item.guid.id, item.title, item.description);
	}
	
	refreshFeed();
}

 

To keep things simple, the application doesn't refresh automatically every few minutes as it should. Instead it waits for the user to click the refresh button.

When the user manually refreshes the app, it loads the hard-coded feed and uses the RSS20 class from the as3syndicationlib to parse it. This class creates an array of all the feed entries making it easier to manipulate the feed. An advantage of the as3syndicationlib  library is that it can let you make a feed-agnostic application, i.e. an application that works with RSS, and Atom, including different versions of the same. Right now though we are focusing only on RSS.

We take each feed item received from the feed and use the FeedData class's add feature, which we coded, to add it to our database. This adds the feed entries in a manner such that any duplicate entries are simply ignored, so if there are common items in between multiple refreshes, they will not appear multiple times.

At the end of refreshing, parsing and updating the database, we call the refresh function again so that the UI is updated with the latest feeds.

UI Code

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
    				   xmlns:s="library://ns.adobe.com/flex/spark" 
					   xmlns:mx="library://ns.adobe.com/flex/mx" 
					   creationComplete="windowedapplication1_creationCompleteHandler(event)"
					   width="1024" height="800"
					   >
	<fx:Script>
		<![CDATA[
...
		]]>
	</fx:Script>
	<s:layout>
		<s:VerticalLayout  />
	</s:layout>
	<s:Button id="refreshButton" label="Refresh Feed" width="100%" click="refreshButton_clickHandler(event)" />
	<s:List id="feedItems" width="100%" height="100%" labelField="title" />
	<mx:HTML id = "feedDetails"  width="100%" height="100%" htmlText="{feedItems.selectedItem?feedItems.selectedItem.description:' '}" />
</s:WindowedApplication>

The button  is simply labeled "Refresh Feed" and is given a width of 100% so it spans across the UI. Clickin it calls the refresh functions we just described.

The feedItems list too is set to expand to the available area. It has the labelField set to title so that the list displays the correct data from the feed items. Without this it will simply show something like "[object Object]".

The HTML element embeds the WebKit browser included in AIR inside your application. we use it to display the description of the feed item, which is usually a snippet of the full article (sometimes the full article itself) in HTML. This will ensure that it is displayed it its original rich format.

We are taking advantage of data binding support in Flex to automatically update the description panel when the selection in the list changes. We do this via the expression inside braces specified for htmlText:

feedItems.selectedItem?feedItems.selectedItem.description:' '

This checks if there is a selected item, and in case there is, it returns its description property. Otherwise it returns some blank text, since the htmlText property has to have a value.

Conclusion

Even with the little we have done you will see an application that can do the following:

  • downloads RSS feed entries from hard-coded feed when the user clicks refresh
  • stores these into an SQL database
  • loads the entires form the database to display them on the UI on startup
  • updates the UI as new entries are downloaded

To test it out properly, you might want to use an RSS feed that changes very fast. or this you could use the RSS feeds generated for a trending Twitter search via http://search.twitter.com/search.rss?q=<search term>

This application is just a basic example though, and misses a large portion of the functionality that other RSS readers provide. For one the feed entries wont even be sorted properly and there is no way to change that.

Here is how you can extend this application:

  • Add support for more than just RSS 2.0 feeds
  • Add support for multiple feeds, with an additional table for storing the feed list.
  • Add support for making a feed item as read
  • Add support for categories, tags, authors etc

There are many ways to go from here! <