Korero – Rev 1
?pathlinks?
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Korero.Chat;
using Microsoft.Data.Sqlite;
using Serilog;
namespace Korero.Database
{
public class MessageDatabase : IDisposable
{
#region Static Fields and Constants
private const string CreateSelfMessageTableSql =
"CREATE TABLE IF NOT EXISTS \"SelfMessage\" ( \"FirstName\" TEXT NOT NULL, \"LastName\" TEXT NOT NULL, \"Message\" TEXT NOT NULL, \"Time\" Text NOT NULL, PRIMARY KEY (\"Time\"))";
private const string CreateMessageTableSql =
"CREATE TABLE IF NOT EXISTS \"Message\" ( \"FirstName\" TEXT NOT NULL, \"LastName\" TEXT NOT NULL, \"Message\" TEXT NOT NULL, \"Time\" Text NOT NULL, PRIMARY KEY (\"Time\"))";
private const string CreateMessageGroupTableSql =
"CREATE TABLE IF NOT EXISTS \"MessageGroup\" ( \"FirstName\" TEXT NOT NULL, \"LastName\" TEXT NOT NULL, \"Group\" TEXT NOT NULL, \"Message\" TEXT NOT NULL, \"Time\" Text NOT NULL, PRIMARY KEY (\"Time\"))";
private const string SaveMessageSql =
"INSERT INTO \"Message\" ( \"FirstName\", \"LastName\", \"Message\", \"Time\" ) VALUES ( @firstName, @lastName, @message, @time )";
private const string SaveSelfMessageSql =
"INSERT INTO \"SelfMessage\" ( \"FirstName\", \"LastName\", \"Message\", \"Time\" ) VALUES ( @firstName, @lastName, @message, @time )";
private const string SaveMessageGroupSql =
"INSERT INTO \"MessageGroup\" ( \"FirstName\", \"LastName\", \"Message\", \"Group\", \"Time\" ) VALUES ( @firstName, @lastName, @message, @group, @time )";
private const string LoadMessagesSql =
"SELECT \"FirstName\", \"LastName\", \"Message\", \"Time\" FROM \"Message\" WHERE FirstName = @firstName AND LastName = @lastName ORDER BY \"Time\" DESC LIMIT @limit";
private const string LoadSelfMessagesSql =
"SELECT \"FirstName\", \"LastName\", \"Message\", \"Time\" FROM \"SelfMessage\" WHERE FirstName = @firstName AND LastName = @lastName ORDER BY \"Time\" DESC LIMIT @limit";
private const string LoadMessagesGroupSql =
"SELECT \"FirstName\", \"LastName\", \"Message\", \"Time\", \"Group\" FROM \"MessageGroup\" WHERE \"Group\" = @group ORDER BY \"Time\" DESC LIMIT @limit";
private const string LoadConversationSql =
"SELECT DISTINCT \"FirstName\", \"LastName\" FROM \"Message\"";
private const string LoadSelfConversationSql =
"SELECT DISTINCT \"FirstName\", \"LastName\" FROM \"SelfMessage\"";
private const string LoadGroupConversationSql =
"SELECT DISTINCT \"Group\" FROM \"MessageGroup\"";
private const string CreateConversationSeenTableSql =
"CREATE TABLE IF NOT EXISTS \"ConversationSeen\" ( \"Name\" TEXT NOT NULL, \"Seen\" TEXT DEFAULT '0', PRIMARY KEY (\"Name\"))";
private const string LoadConversationSeenSql =
"SELECT \"Name\", \"Seen\" FROM \"ConversationSeen\" WHERE \"Name\" = @name";
private const string MarkConversationSeenSql =
"REPLACE INTO \"ConversationSeen\" ( \"Name\", \"Seen\" ) VALUES ( @name, @seen )";
#endregion
#region Private Delegates, Events, Enums, Properties, Indexers and Fields
private static string DatabaseDirectory => Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), @"Wizardry and Steamworks", @"Korero");
private static string DatabaseFilePath => Path.Combine(DatabaseDirectory, @"Korero.db");
#endregion
#region Constructors, Destructors and Finalizers
public MessageDatabase()
{
Directory.CreateDirectory(DatabaseDirectory);
#pragma warning disable 4014
CreateTable(CreateSelfMessageTableSql);
#pragma warning restore 4014
#pragma warning disable 4014
CreateTable(CreateMessageTableSql);
#pragma warning restore 4014
#pragma warning disable 4014
CreateTable(CreateMessageGroupTableSql);
#pragma warning restore 4014
#pragma warning disable 4014
CreateTable(CreateConversationSeenTableSql);
#pragma warning restore 4014
}
public void Dispose()
{
}
#endregion
#region Public Methods
public async Task SaveSelfMessage(DatabaseMessage databaseMessage)
{
var parameters = new Dictionary<string, string>
{
{"firstName", databaseMessage.FirstName},
{"lastName", databaseMessage.LastName},
{"message", databaseMessage.Message},
{"time", databaseMessage.Time.ToString(Constants.DateTimeStamp)}
};
await Execute(SaveSelfMessageSql, parameters);
}
public async Task SaveMessage(DatabaseMessage databaseMessage)
{
var parameters = new Dictionary<string, string>
{
{"firstName", databaseMessage.FirstName},
{"lastName", databaseMessage.LastName},
{"message", databaseMessage.Message},
{"time", databaseMessage.Time.ToString(Constants.DateTimeStamp)}
};
await Execute(SaveMessageSql, parameters);
}
public async Task SaveGroupMessage(DatabaseMessageGroup databaseGroupMessage)
{
var parameters = new Dictionary<string, string>
{
{"firstName", databaseGroupMessage.FirstName},
{"lastName", databaseGroupMessage.LastName},
{"message", databaseGroupMessage.Message},
{"group", databaseGroupMessage.Group},
{"time", databaseGroupMessage.Time.ToString(Constants.DateTimeStamp)}
};
await Execute(SaveMessageGroupSql, parameters);
}
public async Task<IEnumerable<ConversationAvatar>> LoadSelfConversations()
{
var queries = new List<string>
{
"FirstName",
"LastName"
};
var result = new List<ConversationAvatar>();
var databaseResults =
await Execute(LoadSelfConversationSql, new Dictionary<string, string>(), queries);
for (var i = 0; i < databaseResults.Count; ++i)
{
var firstName = databaseResults.Columns["FirstName"][i];
var lastName = databaseResults.Columns["LastName"][i];
result.Add(new ConversationAvatar(firstName, lastName, 1));
}
return result;
}
public async Task<IEnumerable<ConversationAvatar>> LoadConversations()
{
var queries = new List<string>
{
"FirstName",
"LastName"
};
var result = new List<ConversationAvatar>();
var databaseResults =
await Execute(LoadConversationSql, new Dictionary<string, string>(), queries);
for (var i = 0; i < databaseResults.Count; ++i)
{
var firstName = databaseResults.Columns["FirstName"][i];
var lastName = databaseResults.Columns["LastName"][i];
var seenDatabaseResults = await Execute(LoadConversationSeenSql, new Dictionary<string, string>
{
{"name", $"{firstName} {lastName}"}
}, new List<string>(new[]
{
"Name",
"Seen"
}));
var seen = 0;
if (seenDatabaseResults.Columns.Count > 0)
{
if (!int.TryParse(seenDatabaseResults.Columns["Seen"].FirstOrDefault(), out seen))
{
seen = 0;
}
}
result.Add(new ConversationAvatar(firstName, lastName, seen));
}
return result;
}
public async Task<IEnumerable<ConversationGroup>> LoadGroupConversations()
{
var queries = new List<string>
{
"Group"
};
var result = new List<ConversationGroup>();
var databaseResults =
await Execute(LoadGroupConversationSql, new Dictionary<string, string>(), queries);
for (var i = 0; i < databaseResults.Count; ++i)
{
var group = databaseResults.Columns["Group"][i];
var seenDatabaseResults = await Execute(LoadConversationSeenSql, new Dictionary<string, string>
{
{"name", $"{group}"}
}, new List<string>(new[]
{
"Name",
"Seen"
}));
var seen = 0;
if (seenDatabaseResults.Columns.Count > 0)
{
if (!int.TryParse(seenDatabaseResults.Columns["Seen"].FirstOrDefault(), out seen))
{
seen = 0;
}
}
result.Add(new ConversationGroup(group, seen));
}
return result;
}
public async Task<IEnumerable<DatabaseMessage>> LoadMessages(string firstName, string lastName, int limit)
{
var parameters = new Dictionary<string, string>
{
{"firstName", firstName},
{"lastName", lastName},
{"limit", $"{limit}"}
};
var queries = new List<string>
{
"Message",
"Time"
};
var result = new List<DatabaseMessage>();
var databaseResults =
await Execute(LoadMessagesSql, parameters, queries);
for (var i = 0; i < databaseResults.Count; ++i)
{
var message = databaseResults.Columns["Message"][i];
var time = databaseResults.Columns["Time"][i];
if (!DateTime.TryParseExact(time, Constants.DateTimeStamp,
CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
{
continue;
}
var databaseMessage =
new DatabaseMessage(firstName, lastName, message, dateTime);
result.Add(databaseMessage);
}
result.Reverse();
return result;
}
public async Task<IEnumerable<DatabaseMessage>> LoadSelfMessages(string firstName, string lastName, int limit)
{
var parameters = new Dictionary<string, string>
{
{"firstName", firstName},
{"lastName", lastName},
{"limit", $"{limit}"}
};
var queries = new List<string>
{
"Message",
"Time"
};
var result = new List<DatabaseMessage>();
var databaseResults =
await Execute(LoadSelfMessagesSql, parameters, queries);
for (var i = 0; i < databaseResults.Count; ++i)
{
var message = databaseResults.Columns["Message"][i];
var time = databaseResults.Columns["Time"][i];
if (!DateTime.TryParseExact(time, Constants.DateTimeStamp,
CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
{
continue;
}
var databaseMessage =
new DatabaseMessage(firstName, lastName, message, dateTime);
result.Add(databaseMessage);
}
result.Reverse();
return result;
}
public async Task<IEnumerable<DatabaseMessageGroup>> LoadMessagesGroup(string group, int limit)
{
var parameters = new Dictionary<string, string>
{
{"group", group},
{"limit", $"{limit}"}
};
var queries = new List<string>
{
"FirstName",
"LastName",
"Message",
"Time"
};
var result = new List<DatabaseMessageGroup>();
var databaseResults =
await Execute(LoadMessagesGroupSql, parameters, queries);
for (var i = 0; i < databaseResults.Count; ++i)
{
var firstName = databaseResults.Columns["FirstName"][i];
var lastName = databaseResults.Columns["LastName"][i];
var message = databaseResults.Columns["Message"][i];
var time = databaseResults.Columns["Time"][i];
if (!DateTime.TryParseExact(time, Constants.DateTimeStamp,
CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
{
continue;
}
var databaseMessageGroup =
new DatabaseMessageGroup(firstName, lastName, message, dateTime);
result.Add(databaseMessageGroup);
}
result.Reverse();
return result;
}
public async Task MarkConversationSeen(string name, bool seen)
{
var parameters = new Dictionary<string, string>
{
{"name", name},
{"seen", seen ? "1" : "0"}
};
await Execute(MarkConversationSeenSql, parameters);
}
#endregion
#region Private Methods
private static async Task Execute(string sql, Dictionary<string, string> parameters)
{
await Execute(sql, parameters, new List<string>());
}
private static async Task<DatabaseResults> Execute(string sql, Dictionary<string, string> parameters,
List<string> queries)
{
var result = new DatabaseResults();
var connectionString = new SqliteConnectionStringBuilder
{
ConnectionString = $"Data Source={DatabaseFilePath};",
Mode = SqliteOpenMode.ReadWriteCreate
};
try
{
using (var sqliteConnection =
new SqliteConnection(connectionString.ConnectionString))
{
await sqliteConnection.OpenAsync();
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
using (var sqliteCommand = new SqliteCommand(sql,
sqliteConnection, dbTransaction))
{
try
{
foreach (var parameter in parameters)
{
var sqliteParameter = new SqliteParameter(parameter.Key, parameter.Value);
sqliteCommand.Parameters.Add(sqliteParameter);
}
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync())
{
while (await sqlDataReader.ReadAsync())
{
foreach (var query in queries)
{
var data = (string) sqlDataReader[query];
if (result.Columns.ContainsKey(query))
{
result.Columns[query].Add(data);
continue;
}
result.Columns.Add(query, new List<string>());
result.Columns[query].Add(data);
}
result.Count++;
}
}
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
catch (Exception ex)
{
Log.Warning(ex, "Unable to execute SQL.");
}
return result;
}
private static async Task CreateTable(string sql)
{
var connectionString = new SqliteConnectionStringBuilder
{
ConnectionString = $"Data Source={DatabaseFilePath};",
Mode = SqliteOpenMode.ReadWriteCreate
};
try
{
using (var sqliteConnection =
new SqliteConnection(connectionString.ConnectionString))
{
await sqliteConnection.OpenAsync();
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Create the table if it does not exist.
using (var sqliteCommand = new SqliteCommand(sql,
sqliteConnection, dbTransaction))
{
try
{
sqliteCommand.ExecuteNonQuery();
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
catch (Exception ex)
{
Log.Warning(ex, "Unable to create database.");
}
}
#endregion
}
}
Generated by GNU Enscript 1.6.5.90.