RSS Feed Aggregator and Blogging Smart Client

// Copyright � 2005 by Omar Al Zabir. All rights are reserved.
// 
// If you like this code then feel free to go ahead and use it.
// The only thing I ask is that you don't remove or alter my copyright notice.
//
// Your use of this software is entirely at your own risk. I make no claims or
// warrantees about the reliability or fitness of this code for any particular purpose.
// If you make changes or additions to this code please mark your code as being yours.
// 
// website http://www.oazabir.com, email OmarAlZabir@gmail.com, msn oazabir@hotmail.com

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;


namespace RSSFeeder.Helpers
{
	using RSSCommon;

	/// <summary>
	/// Database storage helper
	/// </summary>
	public class DatabaseHelper
	{
		#region Constructor

		#endregion

		#region Connection Handling

		private static OleDbConnection __Connection = null;
		private static OleDbConnection _Connection
		{
			get
			{
				if( null == __Connection )
				{
					string connectionString = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};",
						ApplicationSettings.DatabaseFilePath );
					__Connection = new OleDbConnection( connectionString );

					__Connection.Open();
				}
				else
				{
					while( ConnectionState.Executing == __Connection.State || ConnectionState.Fetching == __Connection.State )
					{
						System.Threading.Thread.Sleep( 10 );
					}

					if( ConnectionState.Open != __Connection.State )
						__Connection.Open();
				}

				return __Connection;
			}
		}

		public static void Close()
		{
			if( null != __Connection )
				if( ConnectionState.Closed != __Connection.State )
				{
					__Connection.Close();
					__Connection.Dispose();
				}
		}

		#endregion

		#region Channel

		public static void GetStatistics( out int channelCount, out int totalUnreadCount )
		{
			using( OleDbCommand cmd = new OleDbCommand( "SELECT Count(*) FROM Channels", _Connection ) )
			{
				channelCount = (int)cmd.ExecuteScalar();
				
				cmd.CommandText = "SELECT Count(*) FROM RssFeeds WHERE IsRead = 0";
				totalUnreadCount = (int)cmd.ExecuteScalar();
			}
		}

		private static Channel MakeChannel( OleDbDataReader reader )
		{
			int id = (int)reader[Channel.Properties.Id];
			string title = reader[Channel.Properties.Title] as string;
			string feedUrl = reader[Channel.Properties.FeedURL] as string;
			string folderPath = reader[Channel.Properties.FolderPath] as string;
			string xslPath = reader[Channel.Properties.XSLPath] as string;
			int itemCount = (int)reader[Channel.Properties.ItemCount];
			int unreadCount = (int)reader[Channel.Properties.UnreadCount];
			Channel.FolderCreationTypeEnum type = (Channel.FolderCreationTypeEnum)reader[Channel.Properties.FolderType];
			string outlookViewXmlPath = (string)reader[Channel.Properties.OutlookViewXmlPath];
			string outlookXSL = (string)reader[Channel.Properties.OutlookXSL];

			TimeSpan frequency = TimeSpan.FromMinutes( (int)reader[ Channel.Properties.Frequency ] );
					
			DateTime lastUpdated = (DateTime)reader[Channel.Properties.LastUpdated];
			DateTime lastUpdatedInOutlook = (DateTime)reader[Channel.Properties.LastUpdatedInOutlook];
			DateTime nextUpdate = (DateTime)reader[Channel.Properties.NextUpdate];

			bool showInNewsPaper = (bool)reader[Channel.Properties.ShowInNewspaper];

			Channel channel = new Channel( id, title, new Uri( feedUrl ), frequency, lastUpdated,
				nextUpdate, lastUpdatedInOutlook, folderPath, xslPath, 
				showInNewsPaper, itemCount, unreadCount, type, outlookViewXmlPath, outlookXSL );

			return channel;
		}

		public static Channel GetChannel( int channelId )
		{
			using( OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Channels ORDER BY Title", _Connection ) )
			{
				using( OleDbDataReader reader = cmd.ExecuteReader( ) )
				{
					if( reader.Read() )
					{
						return MakeChannel( reader );
					}
					else
					{
						return null;
					}
				}
			}
			
		}
		public static IList GetChannels()
		{
			ArrayList channels = new ArrayList();

			using( OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Channels ORDER BY Title", _Connection ) )
			{
				using( OleDbDataReader reader = cmd.ExecuteReader( ) )
				{
					while( reader.Read() )
					{
						Channel channel = MakeChannel( reader );
						channels.Add( channel );
					}
					reader.Close();
				}					
			}

			foreach( Channel channel in channels )
				RefreshChannelCounts( channel.Id );

			channels.Clear();

			using( OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Channels ORDER BY Title", _Connection ) )
			{
				using( OleDbDataReader reader = cmd.ExecuteReader(  ) )
				{
					while( reader.Read() )
					{
						Channel channel = MakeChannel( reader );
						channels.Add( channel );
					}
					reader.Close();
				}
			}

			return channels;
		}

		public static void RefreshChannelCounts( int channelID )
		{
			using( OleDbCommand cmd = new OleDbCommand( "", _Connection ) )
			{
				// Get item count
				string itemCountQuery = string.Format( "SELECT Count(*) FROM RssFeeds WHERE ChannelID = {0}", channelID );				
				cmd.CommandText = itemCountQuery;
				int itemCount = (int)cmd.ExecuteScalar();

				// Get unread count
				string unreadCountQuery = string.Format( "SELECT Count(*) FROM RssFeeds WHERE ChannelID = {0} AND isRead = 0", channelID );
				cmd.CommandText = unreadCountQuery;
				int unreadCount = (int)cmd.ExecuteScalar();

				string sql = string.Format( 
					"UPDATE Channels SET ItemCount = {0}" +
					", UnreadCount = {1} " +
					" WHERE Id = {2}", itemCount, unreadCount, channelID );
				cmd.CommandText = sql;
				cmd.ExecuteNonQuery();

			}
			
		}

		protected static void PrepareCommandFromChannel( Channel channel, OleDbCommand cmd )
		{
			cmd.Parameters.Add(Channel.Properties.Title, OleDbType.VarChar).Value = channel.Title;
			cmd.Parameters.Add(Channel.Properties.FeedURL, OleDbType.LongVarWChar).Value = channel.FeedURL.ToString();
			cmd.Parameters.Add(Channel.Properties.Frequency, OleDbType.Integer).Value = Convert.ToInt32( channel.Frequency.TotalMinutes );
			cmd.Parameters.Add(Channel.Properties.LastUpdated, OleDbType.Date).Value = channel.LastUpdated;
			cmd.Parameters.Add(Channel.Properties.NextUpdate, OleDbType.Date).Value = channel.NextUpdate;
			cmd.Parameters.Add(Channel.Properties.LastUpdatedInOutlook, OleDbType.Date).Value = channel.LastUpdatedInOutlook;
			cmd.Parameters.Add(Channel.Properties.FolderPath, OleDbType.LongVarWChar).Value = channel.FolderPath;
			cmd.Parameters.Add(Channel.Properties.XSLPath, OleDbType.LongVarWChar).Value = channel.XSLPath;
			cmd.Parameters.Add(Channel.Properties.ShowInNewspaper, OleDbType.Boolean).Value = channel.ShowInNewspaper;
			cmd.Parameters.Add(Channel.Properties.ItemCount, OleDbType.Integer).Value = channel.ItemCount;
			cmd.Parameters.Add(Channel.Properties.UnreadCount, OleDbType.Integer).Value = channel.UnreadCount;
			cmd.Parameters.Add(Channel.Properties.FolderType, OleDbType.Integer).Value = (int)channel.FolderType;
			cmd.Parameters.Add(Channel.Properties.OutlookViewXmlPath, OleDbType.LongVarWChar).Value = channel.OutlookViewXmlPath;
			cmd.Parameters.Add(Channel.Properties.OutlookXSL, OleDbType.LongVarWChar).Value = channel.OutlookXSL;
		}

		public static bool AddNewChannel( ref Channel channel )
		{
			using( OleDbCommand cmd = new OleDbCommand( "INSERT INTO Channels (Title, FeedURL, Frequency, " +
					   "LastUpdated, NextUpdate, LastUpdatedInOutlook, FolderPath, XSLPath, ShowInNewspaper, " +
					   "ItemCount, UnreadCount, FolderType, OutlookViewXmlPath, OutlookXSL) VALUES (@Title, @FeedURL, @Frequency, " +
					   "@LastUpdated, @NextUpdate, @LastUpdatedInOutlook, @FolderPath, @XSLPath, " +
					   "@ShowInNewspaper, @ItemCount, @UnreadCount, @FolderType, @OutlookViewXmlPath, @OutlookXSL )", 
					   _Connection ) )
			{

				PrepareCommandFromChannel( channel, cmd );
				
				try
				{
					// Perform insert
					cmd.ExecuteNonQuery();

					// Get the recent ID
					cmd.CommandText = "SELECT MAX(ID) FROM Channels";

					using( OleDbDataReader reader = cmd.ExecuteReader() )
					{
						if( reader.Read() )
							channel.Id = Convert.ToInt32( reader[0].ToString() );						
						else
							return false;
							
					}

					return true;
				}
				catch( OleDbException x )
				{
					throw x;
				}
			}
		}

		public static bool UpdateChannel( Channel channel )
		{
			using( OleDbCommand cmd = new OleDbCommand( "UPDATE Channels SET [Title] = @Title, " +
					   "[FeedURL] = @FeedURL, [Frequency] = @Frequency, [LastUpdated] = @LastUpdated, " +
					   "[NextUpdate] = @NextUpdate, [LastUpdatedInOutlook] = @LastUpdatedInOutlook, " +
					   "[FolderPath] = @FolderPath, [XSLPath] = @XSLPath, [ShowInNewspaper] = @ShowInNewspaper, " +
					   "[ItemCount] = @ItemCount, [UnreadCount] = @UnreadCount, [FolderType] = @FolderType, " +
					   "[OutlookViewXmlPath] = @OutlookViewXmlPath, [OutlookXSL] = @OutlookXSL " +
					   " WHERE [ID]=" 
					   + channel.Id.ToString(), _Connection ) )
			{
				try
				{
					PrepareCommandFromChannel( channel, cmd );
				
					return (1 == cmd.ExecuteNonQuery());
				}
				catch( OleDbException x )
				{
					throw x;
				}
			}
		}

		public static void DeleteAllChannels()
		{
			using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM Channels", _Connection ) )
			{
				cmd.ExecuteNonQuery();
			}
		}

		public static void DeleteChannel( int channelID )
		{
			DeleteFeedsFromChannel( channelID );

			using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM Channels WHERE ID = " + channelID.ToString(), 
					   _Connection) )
			{
				cmd.ExecuteNonQuery();
			}
			
		}

		#endregion

		#region RSS

		public static IList GetRssItems( int channelID, DateTime pubDateAfter )
		{
			ArrayList items = new ArrayList();

			using( OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds WHERE [ChannelID]=@ChannelID AND [PubDate]>=@PubDate ORDER BY [PubDate] DESC", _Connection ) )
			{
				
				command.Parameters.Add("ChannelID", channelID );
				command.Parameters.Add("PubDate", pubDateAfter );

				using( OleDbDataReader reader = command.ExecuteReader(  ) )
				{
					while( reader.Read() )
					{
						RssFeed feed = MakeFeed( reader );
						items.Add( feed );
					}
				}
			}

			return items;
		}

		public static IList SearchFeed( string searchWords )
		{
			ArrayList items = new ArrayList();

			searchWords = searchWords.Replace("'", "''");
			string [] words = searchWords.Split(' ');

			string likeExpr = "LIKE '%" + string.Join( "%' OR XML LIKE '%", words ) + "%'";

			OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds WHERE XML " + likeExpr, _Connection );
				
			using( OleDbDataReader reader = command.ExecuteReader(  ) )
			{
				while( reader.Read() )
				{
					RssFeed feed = MakeFeed( reader );
					items.Add( feed );
				}
			}
			
			return items;
		}

		public static IList GetRssItemsPendingForOutlook( int channelID )
		{
			ArrayList items = new ArrayList();

			OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds WHERE [ChannelID]=@ChannelID AND [IsInOutlook]=0  ORDER BY [PubDate] DESC", _Connection );
				
			command.Parameters.Add("ChannelID", channelID );
				
			using( OleDbDataReader reader = command.ExecuteReader(  ) )
			{
				while( reader.Read() )
				{
					RssFeed feed = MakeFeed( reader );
					items.Add( feed );
				}
			}
			
			return items;
		}

		public static IList GetTopRssItems( int channelID, int top, bool isRead )
		{
			ArrayList items = new ArrayList();

			using( OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds " 
					   + "WHERE [ChannelID]=@ChannelID "
					   + "AND [IsRead] = " + (isRead ? "1" : "0") 
					   + " ORDER BY [PubDate] DESC", _Connection ) )
			{
				
				command.Parameters.Add("ChannelID", channelID );

				using( OleDbDataReader reader = command.ExecuteReader(  ) )
				{
					while( reader.Read() && top-- > 0 )
					{
						RssFeed feed = MakeFeed( reader );
						items.Add( feed );
					}

					reader.Close();
				}
			}
			
			return items;
		}

		protected static RssFeed MakeFeed( OleDbDataReader reader )
		{
			string guid = reader["Guid"] as string;
			string title = reader["Title"] as string;
			string xml = reader["XML"] as string;
			DateTime pubDate = (DateTime)reader["PubDate"];
			bool isRead = (bool)reader["IsRead"];
			bool isInOutlook = (bool)reader["IsInOutlook"];
			int channelID = (int)reader["ChannelID"];

			RssFeed feed = new RssFeed( title, guid, channelID, xml, pubDate, isRead, isInOutlook );

			return feed;
		}

		protected static void PrepareCommandForRssFeed( RssFeed item, OleDbCommand cmd )
		{
			cmd.Parameters.Add("Guid", OleDbType.WChar).Value = item.Guid;
			cmd.Parameters.Add("Title", OleDbType.LongVarWChar).Value = item.Title;
			cmd.Parameters.Add("XML", OleDbType.LongVarWChar).Value = item.XML;
			cmd.Parameters.Add("PubDate", OleDbType.Date).Value = item.PublishDate;
			cmd.Parameters.Add("IsRead", OleDbType.Boolean).Value = item.IsRead;
			cmd.Parameters.Add("IsInOutlook", OleDbType.Boolean).Value = item.IsInOutlook;
		}

		public static bool AddNewFeed( RssFeed item, bool isRefreshChannelCount )
		{
			try
			{
				string sql = string.Format( "INSERT INTO RssFeeds ([Guid], [ChannelID], [Title], [XML], [PubDate], [IsRead], [IsInOutlook]) VALUES (@Guid, {0}, @Title, @XML, @PubDate, @IsRead, @IsInOutlook)", item.ChannelID );

				using( OleDbCommand cmd = new OleDbCommand( sql, _Connection ) )
				{
					PrepareCommandForRssFeed( item, cmd );

					cmd.ExecuteNonQuery();

					if( isRefreshChannelCount )
					{
						RefreshChannelCounts( item.ChannelID );
					}
				}

				return true;
			}
			catch( OleDbException x )
			{
				if( x.ErrorCode == 	-2147467259 )
				{
					// duplicate insert
					//UpdateFeed( item );
					return false;
				}
				else
				{
					throw x;
				}
			}
		}

		public static void MarkAsRead( bool isRead, string guid, int channelID, bool isRefreshChannelCounts )
		{
			using( OleDbCommand cmd = new OleDbCommand( "UPDATE RssFeeds SET [IsRead] = " + (isRead ? "1" : "0") + " WHERE [Guid]= '" + guid + "'", 
					   _Connection ) )
			{
				cmd.ExecuteNonQuery();
			}

			if( isRefreshChannelCounts )
			{
				RefreshChannelCounts( channelID );
			}
		}

		public static void DeleteFeedsFromChannel( int channelID )
		{
			using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM RssFeeds WHERE [ChannelID] = " + channelID.ToString(), 
					   _Connection ) )
			{
				cmd.ExecuteNonQuery();
			}

			RefreshChannelCounts( channelID );
		}

		public static void DeleteFeed( string guid, int channelID, bool isRefreshChannelCount )
		{
			using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM RssFeeds WHERE [Guid] = '" + guid + "'", 
					   _Connection ) )
			{
				cmd.ExecuteNonQuery();
			}

			if( isRefreshChannelCount )
			{
				RefreshChannelCounts( channelID );
			}
		}

		/// <summary>
		/// Mark this channel as all items are in outlook now
		/// </summary>
		/// <param name="channelID"></param>
		public static void RssInOutlook( int channelID)
		{
			using( OleDbCommand cmd = new OleDbCommand( "UPDATE RssFeeds SET IsInOutlook=1 WHERE [ChannelID] = " + channelID.ToString(), 
					   _Connection ) )
			{
				cmd.ExecuteNonQuery();
			}
		}


		#endregion
	}
}