private async Task <OperationStatus> IncludeInternal(Guid groupId, Guid userId)
{
await using var conn = new NpgsqlConnection(ConnectionString);
var conflicted = await conn.QuerySingleOrDefaultAsync <Guid?>(
$@"insert into {PgSchema.GroupMembership} (user_id, group_id)
values (@UserId, @GroupId)
on conflict (user_id) do update set group_id = {PgSchema.GroupMembership}.group_id
returning group_id", new { groupId, userId }).ConfigureAwait(false);
return(conflicted == null || conflicted.Value == groupId
? OperationStatus.Success
: OperationStatus.Fail(OperationStatusCode.Conflict,
"Студент уже числится в другой академической группе. Сначала исключите его из нее."));
}
protected async Task <OperationStatus <T> > Try <T>(Task <OperationStatus <T> > task, string path)
{
try
{
var sw = Stopwatch.StartNew();
var status = await task.ConfigureAwait(false);
sw.Stop();
Console.WriteLine($"{path} {sw.Elapsed}");
return(status);
}
catch (Exception e)
{
Console.WriteLine(e); // logging
return(OperationStatus <T> .Fail(OperationStatusCode.InternalServerError));
}
}
public async Task <OperationStatus <Guid> > SignUp(RegistrationData registrationData)
{
// TODO
var profile = new Profile {
Surname = registrationData.Surname, FirstName = registrationData.FirstName, SecondName = registrationData.SecondName, Group = registrationData.Group
};
var authData = new AuthData {
Email = registrationData.Email, Password = registrationData.Password
};
try
{
profile.InitAsFresh();
await using var conn = new NpgsqlConnection(ConnectionString);
await conn.OpenAsync().ConfigureAwait(false);
await using var transaction = await conn.BeginTransactionAsync().ConfigureAwait(false);
await conn.ExecuteAsync(
@"insert into auth_data (email, password_hash, user_id)
values (@Email, digest(@Password, 'sha256'), @UserId)",
new { authData.Email, authData.Password, UserId = profile.Id }).ConfigureAwait(false);
await conn.ExecuteAsync(
@"insert into profile (id, deleted, version, data)
values (@Id, false, 0, @Profile::jsonb)", new { profile.Id, profile }).ConfigureAwait(false);
await transaction.CommitAsync().ConfigureAwait(false);
return(OperationStatus <Guid> .Success(profile.Id));
}
catch (PostgresException e) when(e.SqlState == "23505")
{
return(OperationStatus <Guid> .Fail(OperationStatusCode.Conflict, "User with such email already exists"));
}
catch (Exception e)
{
Console.WriteLine(e); // TODO logging
return(OperationStatus <Guid> .Fail(OperationStatusCode.InternalServerError));
}
}
public async Task<OperationStatus> Update(TEntity data)
{
try
{
await using var conn = new NpgsqlConnection(ConnectionString);
var version = await conn.QuerySingleOrDefaultAsync<int?>(
$@"select version
from {relationName}
where id = @Id
and not deleted
limit 1", new {data.Id}).ConfigureAwait(false);
if (version == null)
return OperationStatus.Fail(OperationStatusCode.NotFound);
if (data.Version != version)
return OperationStatus.Fail(OperationStatusCode.Conflict, "Your data version is outdated");
data.Version = version.Value + 1;
var updated = await conn.QuerySingleOrDefaultAsync<bool?>(
$@"update {relationName}
set data = @Data::jsonb,
version = version + 1
where id = @Id
and not deleted
and version = @Version
returning true", new {data.Id, version, data}).ConfigureAwait(false);
if (updated == null)
return OperationStatus.Fail(OperationStatusCode.Conflict, "Your data version is outdated");
return OperationStatus.Success;
}
catch (Exception e)
{
Console.WriteLine(e); // TODO logging
return OperationStatus.Fail(OperationStatusCode.InternalServerError);
}
}
public async Task<OperationStatus<TEntity>> Read(Guid id)
{
try
{
await using var conn = new NpgsqlConnection(ConnectionString);
var data = await conn.QuerySingleOrDefaultAsync<TEntity>(
$@"select data
from {relationName}
where id = @Id
and not deleted
limit 1", new {id}).ConfigureAwait(false);
return data != null
? OperationStatus<TEntity>.Success(data)
: OperationStatus<TEntity>.Fail(OperationStatusCode.NotFound);
}
catch (Exception e)
{
Console.WriteLine(e); // TODO logging
return OperationStatus<TEntity>.Fail(OperationStatusCode.InternalServerError);
}
}
public async Task<OperationStatus> Delete(Guid id)
{
try
{
await using var conn = new NpgsqlConnection(ConnectionString);
var deleted = await conn.QuerySingleOrDefaultAsync<bool?>(
$@"update {relationName}
set deleted = true
where id = @Id
returning true", new {id}).ConfigureAwait(false);
if (deleted == null)
return OperationStatus.Fail(OperationStatusCode.NotFound);
return OperationStatus.Success;
}
catch (Exception e)
{
Console.WriteLine(e); // TODO logging
return OperationStatus.Fail(OperationStatusCode.InternalServerError);
}
}
public async Task<OperationStatus<Guid>> Create(TEntity data)
{
try
{
data.InitAsFresh();
await using var conn = new NpgsqlConnection(ConnectionString);
await conn.ExecuteAsync(
$@"insert into {relationName} (id, deleted, version, data)
values (@Id, false, 0, @Data::jsonb)", new {data.Id, data}).ConfigureAwait(false);
return OperationStatus<Guid>.Success(data.Id);
}
catch (PostgresException e) when (e.SqlState == "23505")
{
return OperationStatus<Guid>.Fail(OperationStatusCode.Conflict, "Entity with such ID already exists");
}
catch (Exception e)
{
Console.WriteLine(e); // TODO logging
return OperationStatus<Guid>.Fail(OperationStatusCode.InternalServerError);
}
}
