async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
try
{
JArray speciesArray = new JArray();
using (var cmd = npgConn.CreateCommand())
{
cmd.CommandText = "SELECT species_id, en_us, ko_kr FROM animal_species;";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
string enUs = reader.GetString(1);
string koKr = reader.GetString(2);
var species = new Species(id, enUs, koKr);
speciesArray.Add(species.ToJson());
}
}
}
await conn.SendByteAsync((byte)EResponse.Ok);
await conn.SendTextAsync(speciesArray.ToString());
}
catch (NpgsqlException)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string token = conn.TextMessage;
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, token))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
try
{
cmd.CommandText = $"DELETE FROM account WHERE id={id};";
cmd.ExecuteNonQuery();
}
catch (NpgsqlException)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
return;
}
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long userId = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
string groupName = conn.TextMessage;
await conn.ReceiveAsync();
string password = Account.Hash.Password(conn.Int32Message.Value.ToString());
try
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, userId, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
int groupId;
cmd.CommandText = $"INSERT INTO animal_group (owner_id, name, passwd) VALUES({userId}, '{groupName}', '{password}') RETURNING id;";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
groupId = reader.GetInt32(0);
}
else
{
await conn.SendByteAsync((byte)EResponse.ServerError);
return;
}
}
cmd.CommandText = $"INSERT INTO participates VALUES({groupId}, {userId});";
cmd.ExecuteNonQuery();
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
long animalId = conn.Int64Message.Value;
await conn.ReceiveAsync();
string memoJson = conn.TextMessage;
Memo memo = new Memo();
memo.ReadJson(JObject.Parse(memoJson));
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, id, animalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
// Insert new memo on the database
if (memo.PhotoString == null)
{
cmd.CommandText = $"INSERT INTO memo (animal_id, date_time, title, content) VALUES({animalId}, {memo.When}, '{memo.Title}', '{memo.Content}');";
}
else
{
cmd.CommandText = $"INSERT INTO memo (animal_id, date_time, title, content, images) VALUES({animalId}, {memo.When}, '{memo.Title}', '{memo.Content}', '{memo.PhotoString}');";
}
if (cmd.ExecuteNonQuery() == 0)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
}
else
{
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string hash = conn.TextMessage;
try
{
using (var cmd = npgConn.CreateCommand())
{
string email = null;
string name = null;
cmd.CommandText = $"SELECT email FROM verifying_hash WHERE hash='{hash}';";
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
email = reader.GetString(0);
}
}
if (email == null)
{
await conn.SendByteAsync((byte)EResponse.UnknownHash);
}
else
{
cmd.CommandText = $"SELECT name FROM account WHERE email='{email}';";
using (var reader = cmd.ExecuteReader())
{
reader.Read();
name = reader.GetString(0);
}
cmd.CommandText =
$"DELETE FROM verifying_hash WHERE email='{email}';" +
$"UPDATE account SET verified=TRUE WHERE email='{email}';";
cmd.ExecuteNonQuery();
await conn.SendByteAsync((byte)EResponse.Ok);
await conn.SendTextAsync(name);
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string hash = conn.TextMessage;
try
{
using (var cmd = npgConn.CreateCommand())
{
string email = null;
long expireDue = -1;
bool bUsed = false;
cmd.CommandText = $"SELECT email, expire_due, used FROM reset_password WHERE hash='{hash}';";
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
email = reader.GetString(0);
expireDue = reader.GetInt64(1);
bUsed = reader.GetBoolean(2);
}
}
if (email == null)
{
await conn.SendByteAsync((byte)EResponse.UnknownHash);
}
else if (bUsed)
{
await conn.SendByteAsync((byte)EResponse.Used);
}
else if (DateTime.Now.Ticks >= expireDue)
{
await conn.SendByteAsync((byte)EResponse.Expired);
}
else
{
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string email = conn.TextMessage;
DateTime due = DateTime.Today.AddDays(7);
string hash = Account.Hash.ResetPasswordKey(email, due);
try
{
using (var cmd = npgConn.CreateCommand())
{
string name = null;
cmd.CommandText = $"SELECT name FROM account WHERE email='{email}';";
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
name = reader.GetString(0);
}
}
if (name == null)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
}
else
{
// Insert if not exists, update otherwise
cmd.CommandText =
$"INSERT INTO reset_password VALUES ('{email}', {due.Ticks}, '{hash}') " +
$"ON CONFLICT (email) DO " +
$"UPDATE SET hash='{hash}', expire_due={due.Ticks};";
cmd.ExecuteNonQuery();
await MailSender.SendResetPasswordMailAsync(Startup.MailApiKey, email, name, hash);
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
long animalId = conn.Int64Message.Value;
try
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, id, animalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
cmd.CommandText =
$"DELETE FROM weights WHERE pet_id={animalId};" +
$"DELETE FROM managed WHERE pet_id={animalId};" +
$"DELETE FROM animal WHERE id={animalId};";
cmd.ExecuteNonQuery();
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
long animalId = conn.Int64Message.Value;
await conn.ReceiveAsync();
long memoId = conn.Int64Message.Value;
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, id, animalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
// Actual deletion work
cmd.CommandText = $"DELETE FROM memo WHERE id={memoId};";
if (cmd.ExecuteNonQuery() == 0)
{
// On DB error
await conn.SendByteAsync((byte)EResponse.ServerError);
}
else
{
// Success
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string email = conn.TextMessage;
try
{
using (var cmd = npgConn.CreateCommand())
{
cmd.CommandText = $"SELECT verified FROM account WHERE email='{email}';";
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
bool bVerified = reader.GetBoolean(0);
if (bVerified)
{
await conn.SendByteAsync((byte)EResponse.Verified);
}
else
{
await conn.SendByteAsync((byte)EResponse.NotVerified);
}
}
else
{
await conn.SendByteAsync((byte)EResponse.Unknown);
}
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string email = conn.TextMessage;
await conn.ReceiveAsync();
string hashedPassword = Account.Hash.Password(conn.TextMessage);
await conn.ReceiveAsync();
string name = conn.TextMessage;
string hash = Account.Hash.VerificationKey(email);
try
{
using (var cmd = npgConn.CreateCommand())
{
cmd.CommandText =
$"INSERT INTO account (email, pw, name) VALUES ('{email}', '{hashedPassword}', '{name}');" +
$"INSERT INTO verifying_hash VALUES ('{email}', '{hash}');";
cmd.ExecuteNonQuery();
await MailSender.SendVerificationMailAsync(Startup.MailApiKey, email, name, hash);
}
await conn.SendByteAsync((byte)EResponse.Ok);
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string email = conn.TextMessage;
await conn.ReceiveAsync();
string password = conn.TextMessage;
string hashedPassword = Account.Hash.Password(password);
try
{
using (var cmd = npgConn.CreateCommand())
{
cmd.CommandText = $"SELECT pw, id, name, weights_alert, birth_alert, event_alert, com_comment FROM account WHERE email='{email}';";
Account account = null;
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
string readPassword = reader.GetString(0);
if (hashedPassword == readPassword)
{
string authToken = Account.Hash.AuthenticationToken(email, password);
account = new Account(
id: reader.GetInt64(1),
name: reader.GetString(2),
email: email,
authToken: authToken,
settings: new Settings(
weightAlert: reader.GetBoolean(3),
birthdayAlert: reader.GetBoolean(4),
eventAlert: reader.GetBoolean(5),
commentAlert: reader.GetBoolean(6)));
}
else
{
await conn.SendByteAsync((byte)EResponse.WrongPassword);
}
}
else
{
await conn.SendByteAsync((byte)EResponse.UnknownEmail);
}
}
if (account != null) // on login success
{
cmd.CommandText = $"UPDATE account SET auth_token='{account.AuthenticationToken}' WHERE email='{email}';";
cmd.ExecuteNonQuery();
string jsonString = account.ToJson().ToString();
await conn.SendByteAsync((byte)EResponse.Ok);
await conn.SendTextAsync(jsonString);
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
long animalId = conn.Int64Message.Value;
await conn.ReceiveAsync();
string password = conn.TextMessage;
await conn.ReceiveAsync();
string name = conn.TextMessage;
await conn.ReceiveAsync();
ulong birthday = conn.UInt64Message.Value;
await conn.ReceiveAsync();
short sex = conn.Int16Message.Value;
await conn.ReceiveAsync();
long species = conn.Int64Message.Value;
try
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, id, animalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
int groupId;
string hashedPassword = Account.Hash.Password(password);
cmd.CommandText = $"SELECT group_id FROM managed WHERE pet_id={animalId};";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
groupId = reader.GetInt32(0);
}
else
{
await conn.SendByteAsync((byte)EResponse.UnknownAnimal);
return;
}
}
cmd.CommandText = $"SELECT passwd FROM animal_group WHERE id={groupId};";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
string passwd = reader.GetString(0);
if (passwd != hashedPassword)
{
await conn.SendByteAsync((byte)EResponse.PasswordError);
return;
}
}
else
{
await conn.SendByteAsync((byte)EResponse.UnknownAnimal);
return;
}
}
cmd.CommandText = $"UPDATE animal SET species={species}, name='{name}', birth={birthday}, sex={sex} WHERE id={animalId};";
if (cmd.ExecuteNonQuery() == 0)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
}
else
{
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
int groupId = conn.Int32Message.Value;
await conn.ReceiveAsync();
string password = conn.TextMessage;
await conn.ReceiveAsync();
string name = conn.TextMessage;
await conn.ReceiveAsync();
ulong birthday = conn.UInt64Message.Value;
await conn.ReceiveAsync();
short sex = conn.Int16Message.Value;
await conn.ReceiveAsync();
long species = conn.Int64Message.Value;
await conn.ReceiveAsync();
double weight = double.Parse(conn.TextMessage);
await conn.ReceiveAsync();
ulong today = conn.UInt64Message.Value;
try
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnGroup(cmd, id, groupId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
string hashedPassword = Account.Hash.Password(password);
cmd.CommandText = $"SELECT passwd FROM animal_group WHERE id={groupId};";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
string passwd = reader.GetString(0);
if (passwd != hashedPassword)
{
await conn.SendByteAsync((byte)EResponse.PasswordError);
return;
}
}
else
{
await conn.SendByteAsync((byte)EResponse.UnknownGroup);
return;
}
}
long animalId;
cmd.CommandText = $"INSERT INTO animal (species, name, birth, sex) VALUES({species}, '{name}', {birthday}, {sex}) RETURNING id;";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
animalId = reader.GetInt64(0);
}
else
{
await conn.SendByteAsync((byte)EResponse.ServerError);
return;
}
}
cmd.CommandText = $"INSERT INTO managed VALUES({groupId}, {animalId});";
cmd.ExecuteNonQuery();
cmd.CommandText = $"INSERT INTO weights VALUES({animalId}, {today}, {weight});";
cmd.ExecuteNonQuery();
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
string hash = conn.TextMessage;
await conn.ReceiveAsync();
string password = conn.TextMessage;
try
{
using (var cmd = npgConn.CreateCommand())
{
string email = null;
long expireDue = -1;
bool bUsed = false;
cmd.CommandText = $"SELECT email, expire_due, used FROM reset_password WHERE hash='{hash}';";
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
email = reader.GetString(0);
expireDue = reader.GetInt64(1);
bUsed = reader.GetBoolean(2);
}
}
if (email == null)
{
await conn.SendByteAsync((byte)EResponse.UnknownHash);
}
else if (bUsed)
{
await conn.SendByteAsync((byte)EResponse.Used);
}
else if (DateTime.Now.Ticks <= expireDue)
{
await conn.SendByteAsync((byte)EResponse.Expired);
}
else if (!Regex.IsMatch(password, "((?=.*[a-z])(?=.*[0-9])(?=.*[!@#$%^&*])(?=.*[A-Z]).{8,})"))
{
await conn.SendByteAsync((byte)EResponse.InvalidPattern);
}
else
{
cmd.CommandText = $"UPDATE account SET password='******' WHERE email='{email}';";
bool bSucceed = cmd.ExecuteNonQuery() != 0;
if (bSucceed)
{
cmd.CommandText = $"UPDATE reset_password SET used=TRUE WHERE email='{email}';";
cmd.ExecuteNonQuery();
await conn.SendByteAsync((byte)EResponse.Ok);
}
else
{
await conn.SendByteAsync((byte)EResponse.ServerError);
}
}
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
try
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
LinkedList <int> groupIdList = new LinkedList <int>();
cmd.CommandText = $"SELECT group_id FROM participates WHERE account_id={id};";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int groupId = reader.GetInt32(0);
groupIdList.AddLast(groupId);
}
}
var groups = new List <Group>();
var idEntries = new Dictionary <Group, LinkedList <long> >();
foreach (var groupId in groupIdList)
{
Group group;
cmd.CommandText = $"SELECT owner_id, name FROM animal_group WHERE id={groupId};";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
long owner = reader.GetInt64(0);
string name = reader.GetString(1);
group = new Group(groupId, owner, name);
groups.Add(group);
}
else
{
continue;
}
}
var animalIdList = new LinkedList <long>();
cmd.CommandText = $"SELECT pet_id FROM managed WHERE group_id={groupId};";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long animalId = reader.GetInt64(0);
animalIdList.AddLast(animalId);
}
}
idEntries.Add(group, animalIdList);
}
var entries = new Dictionary <Group, LinkedList <Animal> >();
foreach (var group in groups)
{
var animals = new LinkedList <Animal>();
var idEntry = idEntries[group];
foreach (var animalId in idEntry)
{
var weights = new SortedDictionary <ulong, double>();
cmd.CommandText = $"SELECT measured, weights FROM weights WHERE pet_id={animalId};";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ulong when = checked ((ulong)reader.GetInt64(0));
double weight = reader.GetDouble(1);
weights.Add(when, weight);
}
}
cmd.CommandText = $"SELECT species, name, birth, sex FROM animal WHERE id={animalId};";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
long species = reader.GetInt64(0);
string name = reader.GetString(1);
ulong birthday = checked ((ulong)reader.GetInt64(2));
short sex = reader.GetInt16(3);
var animal = new Animal(animalId, species, birthday, name, (Sex)sex, weights);
animals.AddLast(animal);
}
else
{
continue;
}
}
}
entries.Add(group, animals);
}
await conn.SendByteAsync((byte)EResponse.Ok);
foreach (Group group in groups)
{
await conn.SendByteAsync((byte)EResponse.BeginGroup);
await conn.SendTextAsync(group.ToJson().ToString());
foreach (Animal animal in entries[group])
{
await conn.SendTextAsync(animal.ToJson().ToString());
}
await conn.SendByteAsync((byte)EResponse.EndOfGroup);
}
await conn.SendByteAsync((byte)EResponse.EndOfList);
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
long animalId = conn.Int64Message.Value;
await conn.ReceiveAsync();
string commitJson = conn.TextMessage;
Memo.Commit commit = new Memo.Commit();
commit.ReadJson(JObject.Parse(commitJson));
if (commit.HasChange)
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, id, animalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
// Update memo on the database
var cmdBuilder = new StringBuilder("UPDATE memo SET ");
var changeQueryList = new List <string>();
if (commit.Title != null)
{
changeQueryList.Add($"title='{commit.Title}'");
}
if (commit.Content != null)
{
changeQueryList.Add($"content='{commit.Content}'");
}
if (commit.PhotoString != null)
{
changeQueryList.Add($"images='{commit.PhotoString}'");
}
cmdBuilder.Append(string.Join(", ", changeQueryList));
cmdBuilder.Append($" WHERE id={commit.Id};");
cmd.CommandText = cmdBuilder.ToString();
if (cmd.ExecuteNonQuery() == 0)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
}
else
{
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
}
else
{
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
int groupId = conn.Int32Message.Value;
await conn.ReceiveAsync();
int password = conn.Int32Message.Value;
try
{
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
string hashedPassword = Account.Hash.Password(password.ToString());
cmd.CommandText = $"SELECT passwd FROM animal_group WHERE id={groupId};";
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
string passwd = reader.GetString(0);
if (passwd != hashedPassword)
{
await conn.SendByteAsync((byte)EResponse.PasswordError);
return;
}
}
else
{
await conn.SendByteAsync((byte)EResponse.UnknownGroup);
return;
}
}
cmd.CommandText = $"INSERT INTO participates VALUES({groupId}, {id});";
cmd.ExecuteNonQuery();
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
catch (NpgsqlException e)
{
await conn.SendByteAsync((byte)EResponse.ServerError);
throw e;
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long accountId = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
long animalId = conn.Int64Message.Value;
await conn.ReceiveAsync();
ulong when = conn.UInt64Message.Value;
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, accountId, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, accountId, animalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
// Send list of memo
LinkedList <Memo> memoList = new LinkedList <Memo>();
cmd.CommandText = $"SELECT id, title, content, images FROM memo WHERE animal_id={animalId} AND date_time={when};";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
string title = reader.GetString(1);
string content = reader.GetString(2);
string photoString;
if (reader.IsDBNull(3))
{
photoString = null;
}
else
{
photoString = reader.GetString(3);
}
memoList.AddLast(new Memo(id, when, title, content, photoString));
}
}
await conn.SendByteAsync((byte)EResponse.BeginList);
foreach (var memo in memoList)
{
await conn.SendTextAsync(memo.ToJson().ToString());
}
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
async Task IResponse.Response(HttpContext context, WebSocketConnection conn, NpgsqlConnection npgConn)
{
await conn.ReceiveAsync();
long id = conn.Int64Message.Value;
await conn.ReceiveAsync();
string authToken = conn.TextMessage;
await conn.ReceiveAsync();
string commitJson = conn.TextMessage;
var commit = new WeightCommit();
if (!commit.ReadJson(JObject.Parse(commitJson)))
{
await conn.SendByteAsync((byte)EResponse.ServerError);
return;
}
using (var cmd = npgConn.CreateCommand())
{
if (!AuthorizationChecker.ValidateToken(cmd, id, authToken))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
if (!AuthorizationChecker.CheckAuthorizationOnAnimal(cmd, id, commit.AnimalId))
{
await conn.SendByteAsync((byte)EResponse.AccountError);
return;
}
// Actual update work
foreach (var pair in commit.Changes)
{
var change = pair.Value;
switch (change.ChangeType)
{
case WeightCommit.Change.Type.Upsert:
cmd.CommandText =
$"INSERT INTO weights (pet_id, measured, weights) " + // insert
$"VALUES({commit.AnimalId}, {change.Date}, {change.Weight}) " + // values
$"ON CONFLICT (pet_id, measured) DO " + // on conflict
$"UPDATE SET weights={change.Weight};"; // update
break;
case WeightCommit.Change.Type.Delete:
cmd.CommandText =
$"DELETE FROM weights " +
$"WHERE pet_id={commit.AnimalId} " +
$"AND measured={change.Date};";
break;
default:
cmd.CommandText = "";
break;
}
if (cmd.CommandText.Length != 0)
{
int trial = 0;
int affectionCount;
do
{
if (trial++ == 3) // Max of 3 trials
{
// Do not increase successCount
goto UPDATE_FAILURE;
}
affectionCount = cmd.ExecuteNonQuery();
} while (affectionCount == 0);
}
UPDATE_FAILURE : { }
}
await conn.SendByteAsync((byte)EResponse.Ok);
}
}
