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); } }