public bool TryFindUserByEmail(string email, out IUser user)
{
const string sql = @"
SELECT
user_id as userid,
nickname,
realname,
email,
password_hash as passwordhash,
email_confirmation_code as emailconfirmationcode,
email_confirmation_time as emailconfirmationtime,
join_time as jointime,
region,
default_timezone as defaulttimezone
FROM
public.user
WHERE
email = @Email" ;
using (var connection = AppDataConnection.Create())
{
user = connection.Query <UserRecord>(sql, new { email }).Select(Create).SingleOrDefault();
return(user != null);
}
}
public IReadOnlyList <ITeam> FindTeams(IReadOnlyList <Id <Team> > teamIds)
{
const string sql = @"
SELECT
team_id as teamid,
name,
profile_content as profilecontent,
owner_user_id as owneruserid,
created_time as createdtime
FROM
public.team
WHERE
team_id = any(@TeamIds)" ;
using (var connection = AppDataConnection.Create())
{
var teamMembersByTeamId = new Dictionary <Id <Team>, IReadOnlyList <ITeamMember> >();
var teams = connection
.Query <TeamRecord>(sql, new { teamIds })
.Select(record => CreateTeam(record, () => teamMembersByTeamId[record.TeamId]))
.ToList();
LoadTeamMembersForTeams(teams, teamMembersByTeamId);
return(teams);
}
}
public IReadOnlyList <ILeagueMatch> FindAll(ILeagueTeam leagueTeam)
{
const string sql = @"
SELECT
league_match_id as leaguematchid,
league_id as leagueid,
home_match_team_id as homematchleagueteamid,
away_match_team_id as awaymatchleagueteamid,
match_created_time as matchcreatedtime,
match_start_time as matchstarttime,
match_finish_time as matchfinishtime,
match_results as matchesults,
match_results_reported_time as matchesultsreportedtime
FROM public.league_match
WHERE league_team_id = @LeagueTeamId" ;
using (var connection = AppDataConnection.Create())
{
var records = connection.Query <LeagueMatchRecord>(sql, new { leagueTeam.LeagueTeamId }).ToList();
var leagueTeamIds = records.SelectMany(x => new[] { x.AwayLeagueTeamId, x.HomeLeagueTeamId }).ToList();
var leagueTeamsByLeagueTeamId = new LeagueTeamStore().Find(leagueTeamIds).ToDictionary(x => x.LeagueTeamId, x => x);
return(records.Select(record => Create(record, leagueTeamsByLeagueTeamId)).ToList());
}
}
public IUser CreateNewUser(CreateUserRequest request)
{
const string sql = @"
INSERT INTO public.user
(nickname, realname, email, password_hash, is_admin, join_time, default_timezone, region, email_confirmation_code, email_confirmation_time)
VALUES
(@NickName, @RealName, @Email, @Password, @IsAdmin, @JoinTime, @DefaultTimezone, '', @EmailConfirmationCode, null)
RETURNING user_id;" ;
using (var connection = AppDataConnection.Create())
{
var sqlParams = new
{
request.NickName,
request.RealName,
request.Email,
request.Password,
request.JoinTime,
request.DefaultTimezone,
EmailConfirmationCode = Guid.NewGuid(),
};
var userId = connection.QuerySingle <Id <User> >(sql, sqlParams);
return(FindUsers(new[] { userId }).Single());
}
}
public LinkForwarderService(
ILogger <LinkForwarderService> logger,
ITokenGenerator tokenGenerator,
AppDataConnection connection)
{
_logger = logger;
_tokenGenerator = tokenGenerator;
_connection = connection;
}
public void RemoveInviteToTeam(ITeam team, string email)
{
const string sql = @"
DELETE FROM public.invite_to_team
WHERE team_id = @TeamId AND Email = @Email" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { team.TeamId, email });
}
}
public void EmailConfirmed(IUser user, DateTimeOffset emailConfirmationTime)
{
const string sql = @"
UPDATE public.user
SET email_confirmation_time = @EmailConfirmationTime
WHERE user_id = @UserId;" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { user.UserId, emailConfirmationTime });
}
}
public void UpdatePassword(IUser user, string passwordHash)
{
const string sql = @"
UPDATE public.user
SET password_hash = @PasswordHash
WHERE user_id = @UserId;" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { user.UserId, passwordHash });
}
}
public IReadOnlyList <Id <Team> > FindOpenInvitesForUsers(IUser user)
{
const string sql = @"
SELECT team_id
FROM public.invite_to_team
WHERE email = @Email" ;
using (var connection = AppDataConnection.Create())
{
return(connection.Query <Id <Team> >(sql, new { user.Email }).ToList());
}
}
public void MakeUserAdmin(IUser user, bool isAdmin)
{
const string sql = @"
UPDATE public.user
SET is_admin = @IsAdmin
WHERE user_id = @UserId;" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { user.UserId, isAdmin });
}
}
public void SetMatchTime(Id <LeagueMatch> leagueMatchId, DateTimeOffset matchTime)
{
const string sql = @"
UPDATE public.league_match
SET match_start_time = @MatchTime
WHERE league_match_id = @LeagueMatchId" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { leagueMatchId, matchTime });
}
}
public void SaveInviteToTeam(ITeam team, string email)
{
const string sql = @"
INSERT INTO public.invite_to_team
(team_id, email)
VALUES
(@TeamId, @Email)" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { team.TeamId, email });
}
}
public void JoinTeam(IUser user, ITeam team)
{
const string sql = @"
INSERT INTO public.team_member
(team_id, user_id, nickname, joined_time)
VALUES
(@TeamId, @UserId, @NickName, @JoinedTime)" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { user.UserId, user.NickName, team.TeamId, JoinedTime = user.DefaultTimezone.CurrentTime() });
}
}
public async Task Invoke(HttpContext httpContext,
AppDataConnection dataConnection,
IHostApplicationLifetime appLifetime,
ILogger <FirstRunMiddleware> logger)
{
var initFlag = AppDomain.CurrentDomain.GetData(Token);
if (initFlag is not null)
{
if ((bool)initFlag)
{
await _next(httpContext);
return;
}
}
if (!dataConnection.TestDatabaseConnection(exception =>
{
logger?.LogCritical(exception, $"Error {nameof(dataConnection.TestDatabaseConnection)}, connection string: {dataConnection.ConnectionString}");
}))
{
httpContext.Response.StatusCode = StatusCodes.Status500InternalServerError;
await httpContext.Response.WriteAsync("Database connection failed. Please see error log, fix it and RESTART this application.");
appLifetime?.StopApplication();
}
else
{
if (dataConnection.IsFirstRun())
{
try
{
dataConnection.SetupDatabase();
}
catch (Exception e)
{
logger.LogCritical(e, e.Message);
httpContext.Response.StatusCode = StatusCodes.Status500InternalServerError;
await httpContext.Response.WriteAsync("Error initializing first run, please check error log.");
appLifetime?.StopApplication();
}
}
AppDomain.CurrentDomain.SetData(Token, true);
await _next(httpContext);
}
}
internal static void InitDatabase()
{
switch (UnhandledConfiguration.Current.ConnectionMode)
{
case ConnectionMode.SqlServer:
break;
case ConnectionMode.LocalSql:
default:
AppDataConnection.Init();
break;
}
AppDataConnection.Init();
}
public IGame Save(CreateGameRequest createGameRequest)
{
using (var connection = AppDataConnection.Create())
{
const string sql = @"
INSERT INTO public.game
(name, release_date)
VALUES
(@Name, @ReleaseDate)
RETURNING game_id;" ;
var gameId = connection.Query <Id <Game> >(sql, createGameRequest).Single();
return(FindGamesByGameId(new[] { gameId })[gameId]);
}
}
public void ReportResults(ILeagueMatch match, bool winningLeagueTeamId, string matchResults, DateTimeOffset matchResultsReportedTime)
{
const string sql = @"
UPDATE public.league_match
SET
winning_league_team_id = @WinningLeagueTeamId,
match_results = @MatchResults,
match_results_reported_time = @MatchResultsReportedTime
WHERE
league_match_id = @LeagueMatchId" ;
using (var connection = AppDataConnection.Create())
{
connection.Execute(sql, new { winningLeagueTeamId, matchResults, matchResultsReportedTime, match.LeagueMatchId });
}
}
public IReadOnlyList <ILeague> FindAll()
{
const string sql = @"
SELECT
league_id as leagueid,
name,
uri_path as uripath,
game_id,
created_by_user_id as createdbyuserid,
rules
FROM public.league" ;
using (var connection = AppDataConnection.Create())
{
return(connection.Query <LeagueRecord>(sql).Select((record) => new League(record)).ToList());
}
}
internal static IUnandledDatabase CreateConnection(string spName)
{
IUnandledDatabase instance = null;
switch (UnhandledConfiguration.Current.ConnectionMode)
{
case ConnectionMode.SqlServer:
instance = new AppDataConnection(spName, UnhandledConfiguration.Current.ConnectionStringName);
break;
case ConnectionMode.LocalSql:
default:
instance = new AppDataConnection(spName);
break;
}
return instance;
}
internal static IUnandledDatabase CreateConnection(string spName)
{
IUnandledDatabase instance = null;
switch (UnhandledConfiguration.Current.ConnectionMode)
{
case ConnectionMode.SqlServer:
instance = new AppDataConnection(spName, UnhandledConfiguration.Current.ConnectionStringName);
break;
case ConnectionMode.LocalSql:
default:
instance = new AppDataConnection(spName);
break;
}
return(instance);
}
public IReadOnlyDictionary <Id <Game>, IGame> FindGamesByGameId(params Id <Game>[] gameIds)
{
using (var connection = AppDataConnection.Create())
{
const string sql = @"
SELECT
game_id as gameid,
name,
release_date as releasedate,
FROM public.game
WHERE public.game_id = any(@GameIds)" ;
return(connection
.Query <GameRecord>(sql, new { GameIds = gameIds.ConvertToGuids().ToList() })
.Select(CreateGame)
.ToDictionary(x => x.GameId, x => x));
}
}
public ILeague FindByPath(string path)
{
const string sql = @"
SELECT
league_id as leagueid,
name,
uri_path as uripath,
game_id,
created_by_user_id as createdbyuserid,
rules
FROM public.league
WHERE uri_path = @Path" ;
using (var connection = AppDataConnection.Create())
{
return(connection.Query <LeagueRecord>(sql, new { path }).Select((record) => new League(record)).Single());
}
}
public IReadOnlyList <ILeagueTeam> Find(ILeague league)
{
const string sql = @"
SELECT
league_team_id as leagueteamid,
team_id as teamid,
created_at_time as createdattime,
created_by_user_id as createdbyuserid
FROM public.league_team
WHERE league_id = @LeagueId" ;
using (var connection = AppDataConnection.Create())
{
var records = connection.Query <LeagueTeamRecord>(sql, new { league.LeagueId }).ToList();
var teamByTeamId = _teamStore.FindTeams(records.Select(x => x.TeamId).ToList()).ToDictionary(x => x.TeamId, x => x);
return(records.Select(record => CreateLeagueTeam(record, teamByTeamId[record.TeamId])).ToList());
}
}
public IReadOnlyList <ILeague> FindAll(IGame game)
{
const string sql = @"
SELECT
league_id as leagueid,
name,
uri_path as uripath,
game_id,
created_by_user_id as createdbyuserid,
rules
FROM public.league l
INNER JOIN public.game s
ON l.game_id = s.game_id
WHERE s.game_id = @GameId" ;
using (var connection = AppDataConnection.Create())
{
return(connection.Query <LeagueRecord>(sql, new { game.GameId }).Select((record) => new League(record)).ToList());
}
}
public void Create(CreateMatchRequest createMatchRequest)
{
const string sql = @"
INSERT INTO public.league_match
(league_id, match_league_team_id, match_league_team_id, match_created_time)
VALUES
(@LeagueId, @ChallengerLeagueTeamId, @ChallengedLeagueTeamId, @MatchCreatedTime)" ;
using (var connection = AppDataConnection.Create())
{
var sqlParams = new
{
createMatchRequest.LeagueId,
createMatchRequest.AwayLeagueTeamId,
createMatchRequest.HomeLeagueTeamId,
MatchCreatedTime = DateTimeOffset.Now
};
connection.Execute(sql, createMatchRequest);
}
}
public IReadOnlyDictionary <Id <Team>, IReadOnlyList <ITeamMember> > FindTeamMembers(IReadOnlyList <Id <Team> > teamIds)
{
const string sql = @"
SELECT
team_member_id as teammemberid,
team_id as teamid,
user_id as userid,
nickname,
joined_time as joinedtime
FROM
public.team_member
WHERE
team_id = any(@TeamIds)" ;
using (var connection = AppDataConnection.Create())
{
return(connection
.Query <TeamMemberRecord>(sql, new { teamIds })
.GroupBy(x => x.TeamId, x => x)
.ToDictionary(x => x.Key, x => (IReadOnlyList <ITeamMember>)x.Select(CreateTeamMember).ToList()));
}
}
public ILeague Create(CreateLeagueRequest createLeagueRequest, IUser createdByUser)
{
const string sql = @"
INSERT INTO public.league
(name, uri_path, game_id, rules, created_by_user_id)
VALUES
(@Name, @UriPath, @GameId, @Rules, @CreatedByUserId)
RETURNING league_id;" ;
using (var connection = AppDataConnection.Create())
{
var sqlParams = new
{
createLeagueRequest.Name,
createLeagueRequest.UriPath,
createLeagueRequest.GameId,
createLeagueRequest.Rules,
createdByUser.UserId,
};
return(Find(connection.Query <Id <League> >(sql, sqlParams).Single()));
}
}
public ITeam CreateTeam(CreateTeamRequest request, IUser teamCreator)
{
const string sql = @"
INSERT INTO public.team
(name, owner_user_id, profile_content, owner_user_id, created_time)
VALUES
(@Name, @OwnerUserId, @ProfileContent, @OwnerUserId, @CreatedTime)
RETURNING team_id;" ;
using (var connection = AppDataConnection.Create())
{
var args = new
{
request.Name,
request.ProfileContent,
OwnerUserId = teamCreator.UserId,
CreatedAt = teamCreator.DefaultTimezone.CurrentTime(),
};
var teamId = connection.Query <Id <Team> >(sql, args).Single();
return(TryFindTeam(teamId, out var team) ? team : throw new Exception("Somehow that team you saved doesn't exist now?"));
}
}
public IReadOnlyList <IUser> FindUsersByQuery(string query)
{
const string sql = @"
SELECT
user_id as userid,
nickname,
realname,
email,
password_hash as passwordhash,
email_confirmation_code as emailconfirmationcode,
email_confirmation_time as emailconfirmationtime,
join_time as jointime,
region,
default_timezone as defaulttimezone
FROM
public.user
WHERE
email like @Query OR nickname like @Query" ;
using (var connection = AppDataConnection.Create())
{
return(connection.Query <UserRecord>(sql, new { Query = $"%{query}%" }).Select(Create).ToList());
}
}
public IReadOnlyList <IUser> FindUsers(IReadOnlyList <Id <User> > userIds)
{
const string sql = @"
SELECT
user_id as userid,
nickname,
realname,
email,
password_hash as passwordhash,
email_confirmation_code as emailconfirmationcode,
email_confirmation_time as emailconfirmationtime,
join_time as jointime,
region,
default_timezone as defaulttimezone
FROM
public.user
WHERE
user_id = any(@UserIds)" ;
using (var connection = AppDataConnection.Create())
{
return(connection.Query <UserRecord>(sql, new { UserIds = userIds.ConvertToGuids().ToList() }).Select(Create).ToList());
}
}
public ItemsData(AppDataConnection connection) => _connection = connection;
