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;