public static UserAccount Create(string username, string password, bool admin)
{
string role = (admin ? "admin" : "user");
if (username.Length > 16 || string.IsNullOrEmpty(password))
{
return(null);
}
if (Find(username) != null)
{
return(null);
}
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("INSERT INTO `users` VALUES(NULL, @username, @password, NOW(), NOW(), @role)", connection);
command.Prepare();
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", Hash(password));
command.Parameters.AddWithValue("@role", role);
command.ExecuteReader();
connection.Close();
return(Find(username));
}
return(null);
}
public void Store(TalkyChannel channel, bool writeToDB)
{
lock (_lock)
{
_channels.Add(channel);
string lobbyString = (channel is LobbyChannel ? "true" : "false");
string lockedString = (channel.Locked ? "true" : "false");
if (true == writeToDB)
{
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("INSERT INTO `channels` VALUES(NULL, @channel_name, @lobby_type, @locked)", connection);
command.Prepare();
command.Parameters.AddWithValue("@channel_name", channel.Name);
command.Parameters.AddWithValue("@lobby_type", lobbyString);
command.Parameters.AddWithValue("@locked", lockedString);
try
{
command.ExecuteReader();
}
catch
{
Console.WriteLine("channels table: could not INSERT " + channel.Name);
}
connection.Close();
}
}
}
}
public bool Exists(string name)
{
bool retVal = false;
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `id` FROM `channels` WHERE `channel_name`=@channel_name ORDER BY `id` ASC LIMIT 1", connection);
command.Prepare();
command.Parameters.AddWithValue("@channel_name", name);
try
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
retVal = true;
}
}
catch
{
Console.WriteLine("channels table: could not SELECT " + name);
}
connection.Close();
}
return(retVal);
}
public bool SetPassword(string password)
{
if (string.IsNullOrEmpty(password) || string.IsNullOrWhiteSpace(password) || password.Length < 6)
{
return(false);
}
MySqlConnection connection = MySqlConnector.GetConnection();
MySqlCommand updateCommand = new MySqlCommand("UPDATE `users` SET `password`=@password WHERE `id`=@id ORDER BY `id` ASC LIMIT 1", connection);
updateCommand.Prepare();
updateCommand.Parameters.AddWithValue("@password", Hash(password));
updateCommand.Parameters.AddWithValue("@id", AccountId);
updateCommand.ExecuteReader();
connection.Close();
return(true);
}
public bool SetRole(string role)
{
if (!(role.Equals("admin") || role.Equals("user")))
{
return(false);
}
MySqlConnection connection = MySqlConnector.GetConnection();
MySqlCommand updateCommand = new MySqlCommand("UPDATE `users` SET `role`=@role WHERE `id`=@id ORDER BY `id` ASC LIMIT 1", connection);
updateCommand.Prepare();
updateCommand.Parameters.AddWithValue("@role", role);
updateCommand.Parameters.AddWithValue("@id", AccountId);
updateCommand.ExecuteReader();
connection.Close();
Role = StringToRole(role);
return(true);
}
public void RestoreFromDB()
{
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `channel_name`, `lobby_type`, `locked` FROM `channels` ORDER BY `id` ASC", connection);
command.Prepare();
try
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string channelName = reader.GetString("channel_name");
string lobbyString = reader.GetString("lobby_type");
string lockedString = reader.GetString("locked");
TalkyChannel restoredChannel;
if (lobbyString.Equals("true"))
{
restoredChannel = new LobbyChannel(channelName);
}
else if (lockedString.Equals("true"))
{
restoredChannel = new SystemChannel(channelName, true);
}
else
{
restoredChannel = new ClientChannel(channelName, true);
}
Store(restoredChannel, false);
}
}
catch
{
Console.WriteLine("channels table: could not SELECT in RestoreFromDB ");
}
connection.Close();
}
}
public bool ComparePassword(string password)
{
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `password` FROM `users` WHERE `id`=@id AND `password`=@password ORDER BY `id` ASC LIMIT 1", connection);
command.Prepare();
command.Parameters.AddWithValue("@id", AccountId);
command.Parameters.AddWithValue("@password", Hash(password));
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
connection.Close();
return(true);
}
}
return(false);
}
public static UserAccount Find(string username)
{
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `id`,`username`,`created_at`,`last_login`,`role` FROM `users` WHERE `username`=@username ORDER BY `id` ASC LIMIT 1", connection);
command.Prepare();
command.Parameters.AddWithValue("@username", username);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
UserAccount account = new UserAccount(reader.GetInt32("id"), reader.GetString("username"), reader.GetString("created_at"), reader.GetString("last_login"), reader.GetString("role"));
connection.Close();
return(account);
}
}
return(null);
}
public void Remove(TalkyChannel channel)
{
lock (_lock)
{
// Remove gets called when there are 0 clients in the channel.
// However, if the channel is just recently restored from recovery and clients have not reconnected yet,
// don't delete the channel! Btw, the InRecovery flag is set back to false as soon as the first client joins.
if (false == (channel.InRecovery))
{
_channels.Remove(channel);
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("DELETE FROM `channels` WHERE channel_name = @channel_name", connection);
command.Prepare();
command.Parameters.AddWithValue("@channel_name", channel.Name);
command.ExecuteReader();
connection.Close();
}
}
}
}
public static UserAccount Attempt(string username, string password)
{
if (username.Length > 16 || string.IsNullOrEmpty(password))
{
return(null);
}
MySqlConnection connection = MySqlConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `id`,`username`,`created_at`,`last_login`,`role` FROM `users` WHERE `username`=@username AND `password`=@password ORDER BY `id` ASC LIMIT 1", connection);
command.Prepare();
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", Hash(password));
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32("id");
UserAccount account = new UserAccount(id, reader.GetString("username"), reader.GetString("created_at"), reader.GetString("last_login"), reader.GetString("role"));
connection.Close();
connection = MySqlConnector.GetConnection();
MySqlCommand updateCommand = new MySqlCommand("UPDATE `users` SET `last_login`=NOW() WHERE `id`=@id ORDER BY `id` ASC LIMIT 1", connection);
updateCommand.Prepare();
updateCommand.Parameters.AddWithValue("@id", id);
updateCommand.ExecuteReader();
connection.Close();
return(account);
}
}
connection.Close();
return(null);
}
