public async Task SaveTwitterConfig(LinqToTwitter.Configuration cfg)
{
if (!await Semaphore.WaitAsync(SemaphoreWait))
{
return;
}
try
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "DELETE FROM TwitterConfig;";
await cmd.ExecuteNonQueryAsync();
}
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO TwitterConfig (Data, Expires) VALUES(@json, @expires);";
cmd.AddParameter("json", JsonConvert.SerializeObject(cfg));
cmd.AddParameter("expires",
SqliteHelper.GetDateValue(DateTime.Now.Add(Constants.Cache.TwitterConfigExpiration)));
await cmd.ExecuteNonQueryAsync();
}
}
finally
{
Semaphore.Release();
}
}
private async Task Cleanup()
{
string[] tables =
{
"Users", "TwitterConfig", "Hashtags", "Statuses"
};
ulong now = SqliteHelper.GetDateValue(DateTime.Now);
await Semaphore.WaitAsync(SemaphoreWait);
try
{
using (var tx = new Transaction(Connection))
{
foreach (var table in tables)
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = $"DELETE FROM {table} WHERE Expires < @now;";
cmd.AddParameter("now", now);
await cmd.ExecuteNonQueryAsync();
}
}
tx.Commit();
}
}
finally
{
Semaphore.Release();
}
}
public async Task AddMessages(IList <MessageCacheEntry> messages)
{
if (!await Semaphore.WaitAsync(SemaphoreWait))
{
return;
}
try
{
using (var tx = new Transaction(Connection))
{
int count = messages.Count;
const int batchSize = 100;
int runsNeeded = (int)Math.Ceiling(count / (float)batchSize);
for (int batchIdx = 0; batchIdx < runsNeeded; ++batchIdx)
{
var items = messages.Skip(batchIdx * batchSize).Take(batchSize);
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "INSERT OR REPLACE INTO Messages (Id, Sender, Recipient, Data, Expires) VALUES ";
cmd.CommandText += string.Join(",", items.Select((s, i) =>
{
// ReSharper disable AccessToDisposedClosure
cmd.AddParameter($"id{i}", s.Id);
cmd.AddParameter($"sender{i}", s.Sender);
cmd.AddParameter($"recipient{i}", s.Recipient);
cmd.AddParameter($"json{i}", s.Data);
// ReSharper restore AccessToDisposedClosure
return($"( @id{i}, @sender{i}, @recipient{i}, @json{i}, @expires )");
}));
cmd.AddParameter("expires", SqliteHelper.GetDateValue(DateTime.Now.Add(Constants.Cache.MessageExpiration)));
await cmd.ExecuteNonQueryAsync();
}
}
tx.Commit();
}
}
finally
{
Semaphore.Release();
}
}
public async Task AddStatuses(IList <Status> statuses)
{
if (!await Semaphore.WaitAsync(SemaphoreWait))
{
return;
}
try
{
using (var tx = new Transaction(Connection))
{
int count = statuses.Count;
const int batchSize = 100;
int runsNeeded = (int)Math.Ceiling(count / (float)batchSize);
for (int batchIdx = 0; batchIdx < runsNeeded; ++batchIdx)
{
var items = statuses.Skip(batchIdx * batchSize).Take(batchSize);
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "INSERT OR REPLACE INTO Statuses (Id, UserId, StatusData, Expires) VALUES ";
cmd.CommandText += string.Join(",", items.Select((s, i) =>
{
// ReSharper disable AccessToDisposedClosure
cmd.AddParameter($"id{i}", s.GetStatusId());
cmd.AddParameter($"userid{i}", s.User.GetUserId());
cmd.AddParameter($"json{i}", JsonConvert.SerializeObject(s));
// ReSharper restore AccessToDisposedClosure
return($"( @id{i}, @userid{i}, @json{i}, @expires )");
}));
cmd.AddParameter("expires", SqliteHelper.GetDateValue(DateTime.Now.Add(Constants.Cache.StatusExpiration)));
await cmd.ExecuteNonQueryAsync();
}
}
tx.Commit();
}
}
finally
{
Semaphore.Release();
}
}
public async Task AddUsers(IList <UserCacheEntry> users)
{
int count = users.Count;
const int batchSize = 100;
int runsNeeded = (int)Math.Ceiling(count / (float)batchSize);
await Semaphore.WaitAsync(SemaphoreWait);
try
{
using (var tx = new Transaction(Connection))
{
for (int batchIdx = 0; batchIdx < runsNeeded; ++batchIdx)
{
var items = users.Skip(batchIdx * batchSize).Take(batchSize);
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "INSERT OR REPLACE INTO Users (Id, UserName, UserData, Expires) VALUES ";
cmd.CommandText += string.Join(",", items.Select((u, i) =>
{
// ReSharper disable AccessToDisposedClosure
cmd.AddParameter($"userId{i}", u.UserId);
cmd.AddParameter($"userName{i}", u.UserName);
cmd.AddParameter($"json{i}", u.Data);
// ReSharper restore AccessToDisposedClosure
return($"( @userId{i}, @userName{i}, @json{i}, @expires )");
}));
cmd.AddParameter("expires", SqliteHelper.GetDateValue(DateTime.Now.Add(UserExpiration)));
await cmd.ExecuteNonQueryAsync();
}
}
tx.Commit();
}
}
finally
{
Semaphore.Release();
}
}
public async Task AddHashtags(IList <string> hashTags)
{
if (!hashTags.Any())
{
return;
}
if (!await Semaphore.WaitAsync(SemaphoreWait))
{
return;
}
try
{
using (var tx = new Transaction(Connection))
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Hashtags (Tag, Expires) VALUES ";
cmd.CommandText += string.Join(",", hashTags.Select((h, i) =>
{
// ReSharper disable AccessToDisposedClosure
cmd.AddParameter($"tag{i}", h);
// ReSharper restore AccessToDisposedClosure
return($"( @tag{i}, @expires )");
}));
cmd.AddParameter("expires", SqliteHelper.GetDateValue(DateTime.Now.Add(Constants.Cache.HashtagExpiration)));
await cmd.ExecuteNonQueryAsync();
}
tx.Commit();
}
}
finally
{
Semaphore.Release();
}
}