public static Article LoadByUri(Uri u)
{
Article result = null;
String sqlArticles = @"
SELECT *
FROM Articles
WHERE uri = $uri;
";
using (SQLiteConnection m_dbConnection = new SQLiteConnection(Repository.ConnectionString))
{
m_dbConnection.Open();
SQLiteCommand command = new SQLiteCommand(sqlArticles, m_dbConnection);
command.Parameters.AddWithValue("$uri", u.ToString());
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result = new Article();
result.Location = new Uri(reader["uri"].ToString());
result.PublishDate = DateTime.Parse(reader["published_date"].ToString());
result.Title = reader["title"].ToString();
result.Unread = !(reader["unread"].ToString().Equals("0"));
}
}
}
return result;
}
public static List<Article> LoadByFeed(Feed f)
{
List<Article> results = new List<Article>();
String sqlArticles = @"
SELECT *
FROM Articles
WHERE feed_id = (
SELECT feed_id
FROM Feeds
WHERE uri = $uri
);
";
using (SQLiteConnection m_dbConnection = new SQLiteConnection(Repository.ConnectionString))
{
m_dbConnection.Open();
SQLiteCommand command = new SQLiteCommand(sqlArticles, m_dbConnection);
command.Parameters.AddWithValue("$uri", f.Location.ToString());
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Article art = new Article();
art.Location = new Uri(reader["uri"].ToString());
art.PublishDate = DateTime.Parse(reader["published_date"].ToString());
art.Title = reader["title"].ToString();
art.Unread = !(reader["unread"].ToString().Equals("0"));
art.ParentFeed = f;
results.Add(art);
}
}
}
return results.ToList();
}
public static void UpdateAll(int retentionDays)
{
List<Feed> feeds = LoadAll();
HashSet<Uri> existingArticles = new HashSet<Uri>();
foreach(Feed f in feeds)
{
foreach(Article a in f.Articles)
{
existingArticles.Add(a.Location);
}
}
List<Task> TaskList = new List<Task>();
foreach (Feed feed in feeds)
{
var LastTask = new Task(() => {
feed.Articles.Clear();
try
{
feed.UpdateFromUri(false, retentionDays);
}
catch(Exception e)
{
//TODO: log error somewhere
Debug.Print(e.Message);
}
});
LastTask.Start();
TaskList.Add(LastTask);
}
Task.WaitAll(TaskList.ToArray());
foreach(Feed feed in feeds)
{
foreach (Model.Article a in feed.Articles)
{
if(!existingArticles.Contains(a.Location))
{
Article art = new Article(a);
art.Save();
}
}
feed.Save();
}
}
public void Save(Boolean SaveArticles = false)
{
String sqlInsertHosts = @"
INSERT INTO Hosts (uri, zoom)
SELECT $uri, 100
WHERE NOT EXISTS (
SELECT *
FROM Hosts
WHERE uri = $uri
);
";
String sqlGetHost = @"
SELECT host_id
FROM Hosts
WHERE uri = $uri
";
String sqlInsertFeeds = @"
INSERT INTO Feeds (
uri,
last_update,
title,
host_id
)
SELECT
$uri,
$last_update,
$title,
$host_id
WHERE NOT EXISTS (
SELECT *
FROM Feeds
WHERE uri = $uri
);
";
String sqlUpdateFeed = @"
UPDATE Feeds
SET last_update = $last_update,
title = $title
WHERE uri = $uri;
";
String sqlGetFeed = @"
SELECT feed_id
FROM Feeds
WHERE uri = $uri
";
if (Host == null)
Host = Location.Host;
if (Title == null)
Title = Location.ToString();
using (SQLiteConnection m_dbConnection = new SQLiteConnection(Repository.ConnectionString))
{
m_dbConnection.Open();
SQLiteTransaction tran = m_dbConnection.BeginTransaction();
try
{
SQLiteCommand command = new SQLiteCommand(sqlInsertHosts, m_dbConnection);
command.Parameters.AddWithValue("$uri", Host);
command.ExecuteNonQuery();
command = new SQLiteCommand(sqlGetHost, m_dbConnection);
command.Parameters.AddWithValue("$uri", Host);
var host_id = command.ExecuteScalar();
command = new SQLiteCommand(sqlInsertFeeds, m_dbConnection);
command.Parameters.AddWithValue("$uri", Location.ToString());
command.Parameters.AddWithValue("$last_update", LastUpdate.ToString("s"));
command.Parameters.AddWithValue("$title", Title);
command.Parameters.AddWithValue("$host_id", host_id);
int inserted = command.ExecuteNonQuery();
if(inserted <= 0)
{
command = new SQLiteCommand(sqlUpdateFeed, m_dbConnection);
command.Parameters.AddWithValue("$uri", Location.ToString());
command.Parameters.AddWithValue("$last_update", LastUpdate.ToString("s"));
command.Parameters.AddWithValue("$title", Title);
command.ExecuteNonQuery();
}
command = new SQLiteCommand(sqlGetFeed, m_dbConnection);
command.Parameters.AddWithValue("$uri", Location.ToString());
Id = (long)command.ExecuteScalar();
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
if (SaveArticles)
{
foreach(Model.Article a in Articles)
{
Article dataArt = new Article(a);
dataArt.Save();
}
}
}