flashwave
5a7756894b
Reintroduces separate contexts for users, channels, connections (now split into sessions and connections) and user-channel associations. It builds which is as much assurance as I can give about the stability of this commit, but its also the bare minimum of what i like to commit sooooo A lot of things still need to be broadcast through events throughout the application in order to keep states consistent but we'll cross that bridge when we get to it. I really need to stop using that phrase thingy, I'm overusing it.
218 lines
10 KiB
C#
218 lines
10 KiB
C#
using Microsoft.Extensions.Logging;
|
|
using MySqlConnector;
|
|
using SharpChat.Data;
|
|
using SharpChat.Messages;
|
|
using SharpChat.Users;
|
|
using System.Data.Common;
|
|
using System.Text;
|
|
using System.Text.Json;
|
|
using ZLogger;
|
|
|
|
namespace SharpChat.MariaDB;
|
|
|
|
public class MariaDBMessageStorage(MariaDBStorage storage, ILogger logger) : MessageStorage {
|
|
public async Task LogMessage(Message msg) {
|
|
try {
|
|
using MariaDBConnection conn = await storage.CreateConnection();
|
|
await conn.RunCommand(
|
|
"INSERT IGNORE INTO sqc_events (event_id, event_type, event_channel, event_data"
|
|
+ ", event_sender, event_sender_name, event_sender_colour, event_sender_rank, event_sender_nick, event_sender_perms"
|
|
+ ", event_created, event_deleted)"
|
|
+ " VALUES (@id, @type, @channel, @data"
|
|
+ ", @sender, @sender_name, @sender_colour, @sender_rank, @sender_nick, @sender_perms"
|
|
+ ", FROM_UNIXTIME(@created), FROM_UNIXTIME(@deleted))",
|
|
new MySqlParameter("id", msg.Id),
|
|
new MySqlParameter("type", msg.Type),
|
|
new MySqlParameter("channel", string.IsNullOrWhiteSpace(msg.ChannelName) ? null : msg.ChannelName),
|
|
new MySqlParameter("data", JsonSerializer.SerializeToUtf8Bytes(msg.Data)),
|
|
new MySqlParameter("sender", long.TryParse(msg.SenderId, out long senderId64) && senderId64 > 0 ? senderId64 : null),
|
|
new MySqlParameter("sender_name", msg.SenderName),
|
|
new MySqlParameter("sender_colour", msg.SenderColour.ToMisuzu()),
|
|
new MySqlParameter("sender_rank", msg.SenderRank),
|
|
new MySqlParameter("sender_nick", string.IsNullOrWhiteSpace(msg.SenderNickName) ? null : msg.SenderNickName),
|
|
new MySqlParameter("sender_perms", MariaDBUserPermissionsConverter.To(msg.SenderPermissions)),
|
|
new MySqlParameter("created", msg.Created.ToUnixTimeSeconds()),
|
|
new MySqlParameter("deleted", msg.Deleted?.ToUnixTimeSeconds())
|
|
);
|
|
} catch(MySqlException ex) {
|
|
logger.ZLogError($"Error in LogMessage(Message): {ex}");
|
|
}
|
|
}
|
|
|
|
public async Task LogMessage(
|
|
long id,
|
|
string type,
|
|
string channelName,
|
|
string senderId,
|
|
string senderName,
|
|
ColourInheritable senderColour,
|
|
int senderRank,
|
|
string senderNick,
|
|
UserPermissions senderPerms,
|
|
object? data = null
|
|
) {
|
|
try {
|
|
using MariaDBConnection conn = await storage.CreateConnection();
|
|
await conn.RunCommand(
|
|
"INSERT INTO sqc_events (event_id, event_created, event_type, event_channel, event_data"
|
|
+ ", event_sender, event_sender_name, event_sender_colour, event_sender_rank, event_sender_nick, event_sender_perms)"
|
|
+ " VALUES (@id, NOW(), @type, @channel, @data"
|
|
+ ", @sender, @sender_name, @sender_colour, @sender_rank, @sender_nick, @sender_perms)",
|
|
new MySqlParameter("id", id),
|
|
new MySqlParameter("type", type),
|
|
new MySqlParameter("channel", string.IsNullOrWhiteSpace(channelName) ? null : channelName),
|
|
new MySqlParameter("data", data == null ? "{}" : JsonSerializer.SerializeToUtf8Bytes(data)),
|
|
new MySqlParameter("sender", long.TryParse(senderId, out long senderId64) && senderId64 > 0 ? senderId64 : null),
|
|
new MySqlParameter("sender_name", senderName),
|
|
new MySqlParameter("sender_colour", senderColour.ToMisuzu()),
|
|
new MySqlParameter("sender_rank", senderRank),
|
|
new MySqlParameter("sender_nick", string.IsNullOrWhiteSpace(senderNick) ? null : senderNick),
|
|
new MySqlParameter("sender_perms", MariaDBUserPermissionsConverter.To(senderPerms))
|
|
);
|
|
} catch(MySqlException ex) {
|
|
logger.ZLogError($"Error in LogMessage(long, ...): {ex}");
|
|
}
|
|
}
|
|
|
|
public async Task DeleteMessage(Message msg) {
|
|
try {
|
|
using MariaDBConnection conn = await storage.CreateConnection();
|
|
await conn.RunCommand(
|
|
"UPDATE IGNORE sqc_events SET event_deleted = NOW() WHERE event_id = @id AND event_deleted IS NULL",
|
|
new MySqlParameter("id", msg.Id)
|
|
);
|
|
} catch(MySqlException ex) {
|
|
logger.ZLogError($"Error in DeleteMessage(): {ex}");
|
|
}
|
|
}
|
|
|
|
private static Message ReadMessage(DbDataReader reader) {
|
|
using Stream data = reader.GetStream(reader.GetOrdinal("event_data"));
|
|
return new Message(
|
|
reader.GetInt64(reader.GetOrdinal("event_id")),
|
|
reader.GetString(reader.GetOrdinal("event_type")),
|
|
reader.IsDBNull(reader.GetOrdinal("event_sender")) ? null : reader.GetString(reader.GetOrdinal("event_sender")),
|
|
reader.IsDBNull(reader.GetOrdinal("event_sender_name")) ? string.Empty : reader.GetString(reader.GetOrdinal("event_sender_name")),
|
|
ColourInheritable.FromMisuzu(reader.GetInt32(reader.GetOrdinal("event_sender_colour"))),
|
|
reader.GetInt32(reader.GetOrdinal("event_sender_rank")),
|
|
MariaDBUserPermissionsConverter.From((MariaDBUserPermissions)reader.GetInt32(reader.GetOrdinal("event_sender_perms"))),
|
|
reader.IsDBNull(reader.GetOrdinal("event_sender_nick")) ? string.Empty : reader.GetString(reader.GetOrdinal("event_sender_nick")),
|
|
DateTimeOffset.FromUnixTimeSeconds(reader.GetInt32(reader.GetOrdinal("event_created"))),
|
|
reader.IsDBNull(reader.GetOrdinal("event_deleted")) ? null : DateTimeOffset.FromUnixTimeSeconds(reader.GetInt32(reader.GetOrdinal("event_deleted"))),
|
|
reader.IsDBNull(reader.GetOrdinal("event_channel")) ? null : reader.GetString(reader.GetOrdinal("event_channel")),
|
|
JsonDocument.Parse(data)
|
|
);
|
|
}
|
|
|
|
public async Task<Message?> GetMessage(long id) {
|
|
try {
|
|
using MariaDBConnection conn = await storage.CreateConnection();
|
|
using MySqlDataReader? reader = await conn.RunQuery(
|
|
"SELECT event_id, event_type, event_data, event_channel"
|
|
+ ", event_sender, event_sender_name, event_sender_colour, event_sender_rank, event_sender_nick, event_sender_perms"
|
|
+ ", UNIX_TIMESTAMP(event_created) AS event_created"
|
|
+ ", UNIX_TIMESTAMP(event_deleted) AS event_deleted"
|
|
+ " FROM sqc_events"
|
|
+ " WHERE event_id = @id",
|
|
new MySqlParameter("id", id)
|
|
);
|
|
|
|
if(reader is null)
|
|
return null;
|
|
|
|
while(reader.Read()) {
|
|
Message evt = ReadMessage(reader);
|
|
if(evt != null)
|
|
return evt;
|
|
}
|
|
} catch(MySqlException ex) {
|
|
logger.ZLogError($"Error in GetMessage(): {ex}");
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public async Task<long> CountMessages(
|
|
string? channelName = null,
|
|
bool includeDeleted = false
|
|
) {
|
|
List<MySqlParameter> parameters = [];
|
|
bool firstParam = true;
|
|
StringBuilder qb = new();
|
|
qb.Append("SELECT COUNT(*) FROM sqc_events");
|
|
|
|
if(!includeDeleted) {
|
|
firstParam = false;
|
|
qb.Append(" WHERE event_deleted IS NULL");
|
|
}
|
|
|
|
if(!string.IsNullOrEmpty(channelName)) {
|
|
qb.AppendFormat(" {0} (event_channel = @channel OR event_channel IS NULL)", firstParam ? "WHERE" : "AND");
|
|
parameters.Add(new MySqlParameter("channel", channelName));
|
|
}
|
|
|
|
try {
|
|
using MariaDBConnection conn = await storage.CreateConnection();
|
|
return await conn.RunQueryValue<long>(qb.ToString(), [.. parameters]);
|
|
} catch(MySqlException ex) {
|
|
logger.ZLogError($"Error in CountMessages({channelName}, {includeDeleted}): {ex}");
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
public async Task<IEnumerable<Message>> GetMessages(
|
|
string? channelName = null,
|
|
int? take = 20,
|
|
long? beforeId = null,
|
|
bool includeDeleted = false
|
|
) {
|
|
List<MySqlParameter> parameters = [];
|
|
bool firstParam = true;
|
|
StringBuilder qb = new();
|
|
qb.Append("SELECT event_id, event_type, event_data, event_channel");
|
|
qb.Append(", event_sender, event_sender_name, event_sender_colour, event_sender_rank, event_sender_nick, event_sender_perms");
|
|
qb.Append(", UNIX_TIMESTAMP(event_created) AS event_created");
|
|
qb.Append(", UNIX_TIMESTAMP(event_deleted) AS event_deleted");
|
|
qb.Append(" FROM sqc_events");
|
|
|
|
if(!includeDeleted) {
|
|
firstParam = false;
|
|
qb.Append(" WHERE event_deleted IS NULL");
|
|
}
|
|
|
|
if(!string.IsNullOrEmpty(channelName)) {
|
|
qb.AppendFormat(" {0} (event_channel = @channel OR event_channel IS NULL)", firstParam ? "WHERE" : "AND");
|
|
parameters.Add(new MySqlParameter("channel", channelName));
|
|
firstParam = false;
|
|
}
|
|
|
|
if(beforeId.HasValue) {
|
|
qb.AppendFormat(" {0} event_id < @before", firstParam ? "WHERE" : "AND");
|
|
parameters.Add(new MySqlParameter("before", beforeId.Value));
|
|
}
|
|
|
|
qb.Append(" ORDER BY event_id DESC");
|
|
|
|
if(take.HasValue) {
|
|
qb.Append(" LIMIT @take");
|
|
parameters.Add(new MySqlParameter("take", take.Value));
|
|
}
|
|
|
|
string query = string.Format("SELECT * FROM ({0}) AS _ ORDER BY event_id ASC", qb);
|
|
|
|
try {
|
|
MariaDBConnection conn = await storage.CreateConnection();
|
|
DbDataReader? reader = await conn.RunQuery(query, [.. parameters]);
|
|
|
|
if(reader is null) {
|
|
conn.Dispose();
|
|
return [];
|
|
}
|
|
|
|
return new DbObjectEnumerable<Message>(reader, ReadMessage, () => conn.Dispose());
|
|
} catch(MySqlException ex) {
|
|
logger.ZLogError($"Error in GetMessages({channelName}, {take}, {beforeId}, {includeDeleted}): {ex}");
|
|
return [];
|
|
}
|
|
}
|
|
}
|