/// <summary> /// Ensure the database schemas are created. /// </summary> /// <remarks> /// This is mostly for the in-memory database used in dev mode, but could be useful for new databases too. /// However, it's important to note that the tables created here may not be optimized. Indicies, foreign keys, etc may be missing. /// </remarks> /// <returns>Async task.</returns> private async Task EnsureDatabaseCreatedAsync(IServiceProvider serviceProvider) { // Handle the EntityFramework tables await serviceProvider.GetService <ApplicationDbContext>().Database.EnsureCreatedAsync(); IDatabaseCommandFactory databaseCommandFactory = serviceProvider.GetService <IDatabaseCommandFactory>(); // Get all existing tables so we know what already exists HashSet <string> existingTables = new(StringComparer.OrdinalIgnoreCase); using (IDatabaseCommand command = databaseCommandFactory.Create("SELECT Name FROM sys.Tables WHERE Type = N'U'")) { using (IDataReader reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { existingTables.Add(reader["Name"].ToString()); } } } // Read sql files and execute their contents in order if required. string[] tables = new[] { "Uploads", "AncientLevels", "ComputedStats", "OutsiderLevels", "UserFollows", "UserSettings", "Clans", "ClanMembers", "GameUsers", }; IEnumerable <string> tableFiles = tables.Select(table => Path.Combine(_environment.ContentRootPath, "Services", "Database", "Schemas", table + ".sql")); foreach (string tableFile in tableFiles) { string tableName = Path.GetFileNameWithoutExtension(tableFile); if (!existingTables.Contains(tableName)) { string tableCreateCommand = File.ReadAllText(tableFile); using (IDatabaseCommand command = databaseCommandFactory.Create(tableCreateCommand)) { await command.ExecuteNonQueryAsync(); } } } }
private static void GetUploadDetails( IDatabaseCommandFactory databaseCommandFactory, int uploadId, out string uploadContent, out string userId, out PlayStyle playStyle) { const string CommandText = @" SELECT UploadContent, UserId, PlayStyle FROM Uploads WHERE Id = @UploadId" ; var commandParameters = new Dictionary <string, object> { { "@UploadId", uploadId }, }; using (var command = databaseCommandFactory.Create( CommandText, commandParameters)) using (var reader = command.ExecuteReader()) { reader.Read(); uploadContent = reader["UploadContent"].ToString(); userId = reader["UserId"].ToString(); if (!Enum.TryParse(reader["PlayStyle"].ToString(), out playStyle)) { playStyle = default(PlayStyle); } } }
public async Task <string> GetClanNameAsync(string userId) { if (userId == null) { return(null); } const string CommandText = @" SELECT ClanName FROM ClanMembers WHERE Id = ( SELECT Id FROM GameUsers WHERE UserId = @UserId );"; Dictionary <string, object> parameters = new() { { "@UserId", userId }, }; using (IDatabaseCommand command = _databaseCommandFactory.Create(CommandText, parameters)) { return((await command.ExecuteScalarAsync())?.ToString()); } }
private static async Task <(string uploadContent, string userId)> GetUploadDetailsAsync( IDatabaseCommandFactory databaseCommandFactory, int uploadId) { const string CommandText = @" SELECT UploadContent, UserId FROM Uploads WHERE Id = @UploadId" ; var commandParameters = new Dictionary <string, object> { { "@UploadId", uploadId }, }; using (var command = databaseCommandFactory.Create( CommandText, commandParameters)) using (var reader = await command.ExecuteReaderAsync()) { reader.Read(); var uploadContent = reader["UploadContent"].ToString(); var userId = reader["UserId"].ToString(); return(uploadContent, userId); } }
/// <summary> /// Creates an <see cref="IDatabaseCommand"/> with the specified parameters. /// </summary> /// <param name="databaseCommandFactory">The command factory</param> /// <param name="commandText">The command text</param> /// <param name="parameters">The parameters to the command</param> /// <returns>An <see cref="IDatabaseCommand"/></returns> public static IDatabaseCommand Create( this IDatabaseCommandFactory databaseCommandFactory, string commandText, IDictionary <string, object> parameters) { var command = databaseCommandFactory.Create(); command.CommandText = commandText; command.Parameters = parameters; return(command); }
public async Task <ActionResult <List <int> > > StaleUploadsAsync() { const string CommandText = @" SELECT Id FROM Uploads WHERE UserId IS NULL AND UploadTime < DATEADD(day, -30, GETDATE())"; List <int> uploadIds = new(); using (IDatabaseCommand command = _databaseCommandFactory.Create(CommandText)) using (IDataReader reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { int uploadId = Convert.ToInt32(reader["Id"]); uploadIds.Add(uploadId); } } return(uploadIds); }
/// <summary> /// Initializes a new instance of the <see cref="CalculatorViewModel"/> class. /// </summary> public CalculatorViewModel( IDatabaseCommandFactory databaseCommandFactory, IUserSettingsProvider userSettingsProvider, int uploadId, ClaimsPrincipal user, UserManager <ApplicationUser> userManager) { var userId = userManager.GetUserId(user); this.UploadId = uploadId; string uploadUserId; var parameters = new Dictionary <string, object> { { "@UploadId", uploadId }, }; const string GetUploadUserIdCommandText = @" SELECT UserId FROM Uploads WHERE Uploads.Id = @UploadId"; using (var command = databaseCommandFactory.Create( GetUploadUserIdCommandText, parameters)) using (var reader = command.ExecuteReader()) { if (reader.Read()) { uploadUserId = reader["UserId"].ToString(); } else { return; } } var uploadUserSettings = userSettingsProvider.Get(uploadUserId); var isUploadAnonymous = string.IsNullOrEmpty(uploadUserId); this.IsOwn = string.Equals(userId, uploadUserId, StringComparison.OrdinalIgnoreCase); this.IsPublic = isUploadAnonymous || uploadUserSettings.AreUploadsPublic; this.IsPermitted = this.IsOwn || this.IsPublic || user.IsInRole("Admin"); this.SuggestedAncientIds = suggestedAncientIds; this.IsValid = true; }
/// <summary> /// Builds a command to bulk copy into the database /// </summary> /// <returns>A bulk copy command builder</returns> public BulkCopyCommandBuilder ForBulkCopy() { return(_commandFactory.Create <BulkCopyCommand>()); }
/// <summary> /// Initializes a new instance of the <see cref="DashboardViewModel"/> class. /// </summary> public DashboardViewModel( GameData gameData, TelemetryClient telemetryClient, IDatabaseCommandFactory databaseCommandFactory, IUserSettingsProvider userSettingsProvider, ClaimsPrincipal user, UserManager <ApplicationUser> userManager) { var userId = userManager.GetUserId(user); var userSettings = userSettingsProvider.Get(userId); var startTime = DateTime.UtcNow.AddDays(-7); var data = new ProgressData( gameData, telemetryClient, databaseCommandFactory, userId, startTime, null); if (!data.IsValid) { return; } var dataSeries = data.SoulsSpentData; if (dataSeries.Count > 0) { this.ProgressSummaryGraph = new GraphData { Chart = new Chart { Type = ChartType.Line, }, Title = new Title { Text = "Souls Spent", }, XAxis = new Axis { TickInterval = 24 * 3600 * 1000, // one day Type = AxisType.Datetime, TickWidth = 0, GridLineWidth = 1, Labels = new Labels { Align = Align.Left, X = 3, Y = -3, Format = "{value:%m/%d}", }, }, YAxis = new Axis { Labels = new Labels { Align = Align.Left, X = 3, Y = 16, Format = "{value:.,0f}", }, ShowFirstLabel = false, Type = GetYAxisType(dataSeries, userSettings), }, Legend = new Legend { Enabled = false, }, Series = new Series[] { new Series { Color = Colors.PrimarySeriesColor, Data = dataSeries .Select(datum => new Point { X = datum.Key.ToJavascriptTime(), Y = datum.Value.ToString("F0"), }) .Concat(new[] { new Point { X = DateTime.UtcNow.ToJavascriptTime(), Y = dataSeries.Last().Value.ToString("F0"), }, }) .ToList(), }, }, }; } this.Follows = new List <string>(); var parameters = new Dictionary <string, object> { { "@UserId", userId }, }; const string GetUserFollowsCommandText = @" SELECT UserName FROM UserFollows INNER JOIN AspNetUsers ON UserFollows.FollowUserId = AspNetUsers.Id WHERE UserId = @UserId ORDER BY UserName ASC" ; using (var command = databaseCommandFactory.Create( GetUserFollowsCommandText, parameters)) using (var reader = command.ExecuteReader()) { while (reader.Read()) { this.Follows.Add(reader["UserName"].ToString()); } } this.IsValid = true; }
public async Task <ActionResult <Upload> > DetailsAsync(int uploadId) { if (uploadId < 0) { return(BadRequest()); } Dictionary <string, object> uploadIdParameters = new() { { "@UploadId", uploadId }, }; string uploadUserId; string uploadContent; Upload upload = new() { Id = uploadId }; const string GetUploadDataCommandText = @" SELECT UserId, UserName, UploadTime, UploadContent, PlayStyle FROM Uploads LEFT JOIN AspNetUsers ON Uploads.UserId = AspNetUsers.Id WHERE Uploads.Id = @UploadId"; using (IDatabaseCommand command = _databaseCommandFactory.Create( GetUploadDataCommandText, uploadIdParameters)) using (IDataReader reader = await command.ExecuteReaderAsync()) { if (reader.Read()) { uploadUserId = reader["UserId"].ToString(); string uploadUserName = reader["UserName"].ToString(); if (!string.IsNullOrEmpty(uploadUserName)) { upload.User = new User { Name = uploadUserName, ClanName = await _clanManager.GetClanNameAsync(uploadUserId), }; } // The DateTime is a datetime2 which has no timezone so comes out as DateTimeKind.Unknown. Se need to specify the kind so it gets serialized correctly. upload.TimeSubmitted = DateTime.SpecifyKind(Convert.ToDateTime(reader["UploadTime"]), DateTimeKind.Utc); uploadContent = reader["UploadContent"].ToString(); upload.PlayStyle = reader["PlayStyle"].ToString().SafeParseEnum <PlayStyle>(); } else { // If we didn't get data, it's an upload that doesn't exist return(NotFound()); } } bool isAdmin = User.IsInRole("Admin"); bool isUploadAnonymous = upload.User == null; bool isOwn = !isUploadAnonymous && string.Equals(_userManager.GetUserId(User), uploadUserId, StringComparison.OrdinalIgnoreCase); // Only return the raw upload content if it's the requesting user's or an admin requested it. if (isOwn || isAdmin) { upload.Content = uploadContent; } else { upload.Content = SavedGame.ScrubIdentity(uploadContent); upload.IsScrubbed = true; } return(upload); }
/// <summary> /// Initializes a new instance of the <see cref="ProgressData"/> class. /// </summary> public ProgressData( GameData gameData, TelemetryClient telemetryClient, IDatabaseCommandFactory databaseCommandFactory, string userId, DateTime?startTime, DateTime?endTime) { var parameters = new Dictionary <string, object> { { "@UserId", userId }, { "@StartTime", startTime }, { "@EndTime", endTime }, }; const string GetProgressDataCommandText = @" -- Create a temp table that scopes the Uploads CREATE TABLE #ScopedUploads ( Id INT NOT NULL, UploadTime DATETIME2(0) NOT NULL, ); -- Populate temp table INSERT INTO #ScopedUploads (Id, UploadTime) SELECT Id, UploadTime FROM Uploads WHERE UserId = @UserId AND UploadTime >= ISNULL(@StartTime, '0001-01-01 00:00:00') AND UploadTime <= ISNULL(@EndTime, '9999-12-31 23:59:59'); -- Computed Stats SELECT #ScopedUploads.UploadTime, ComputedStats.TitanDamage, ComputedStats.SoulsSpent, ComputedStats.HeroSoulsSacrificed, ComputedStats.TotalAncientSouls, ComputedStats.TranscendentPower, ComputedStats.Rubies, ComputedStats.HighestZoneThisTranscension, ComputedStats.HighestZoneLifetime, ComputedStats.AscensionsThisTranscension, ComputedStats.AscensionsLifetime FROM ComputedStats INNER JOIN #ScopedUploads ON ComputedStats.UploadId = #ScopedUploads.Id; -- Ancient Levels SELECT #ScopedUploads.UploadTime, AncientLevels.AncientId, AncientLevels.Level FROM AncientLevels INNER JOIN #ScopedUploads ON AncientLevels.UploadId = #ScopedUploads.Id; -- Outsider Levels SELECT #ScopedUploads.UploadTime, OutsiderLevels.OutsiderId, OutsiderLevels.Level FROM OutsiderLevels INNER JOIN #ScopedUploads ON OutsiderLevels.UploadId = #ScopedUploads.Id; -- Drop the temp table DROP TABLE #ScopedUploads;"; using (var command = databaseCommandFactory.Create( GetProgressDataCommandText, parameters)) using (var reader = command.ExecuteReader()) { this.TitanDamageData = new SortedDictionary <DateTime, BigInteger>(); this.SoulsSpentData = new SortedDictionary <DateTime, BigInteger>(); this.HeroSoulsSacrificedData = new SortedDictionary <DateTime, BigInteger>(); this.TotalAncientSoulsData = new SortedDictionary <DateTime, double>(); this.TranscendentPowerData = new SortedDictionary <DateTime, double>(); this.RubiesData = new SortedDictionary <DateTime, double>(); this.HighestZoneThisTranscensionData = new SortedDictionary <DateTime, double>(); this.HighestZoneLifetimeData = new SortedDictionary <DateTime, double>(); this.AscensionsThisTranscensionData = new SortedDictionary <DateTime, double>(); this.AscensionsLifetimeData = new SortedDictionary <DateTime, double>(); while (reader.Read()) { // The DateTime is a datetime2 which has no timezone so comes out as DateTimeKind.Unknown. Se need to specify the kind so it gets serialized correctly. var uploadTime = DateTime.SpecifyKind(Convert.ToDateTime(reader["UploadTime"]), DateTimeKind.Utc); this.TitanDamageData.AddOrUpdate(uploadTime, reader["TitanDamage"].ToString().ToBigInteger()); this.SoulsSpentData.AddOrUpdate(uploadTime, reader["SoulsSpent"].ToString().ToBigInteger()); this.HeroSoulsSacrificedData.AddOrUpdate(uploadTime, reader["HeroSoulsSacrificed"].ToString().ToBigInteger()); this.TotalAncientSoulsData.AddOrUpdate(uploadTime, Convert.ToDouble(reader["TotalAncientSouls"])); this.TranscendentPowerData.AddOrUpdate(uploadTime, 100 * Convert.ToDouble(reader["TranscendentPower"])); this.RubiesData.AddOrUpdate(uploadTime, Convert.ToDouble(reader["Rubies"])); this.HighestZoneThisTranscensionData.AddOrUpdate(uploadTime, Convert.ToDouble(reader["HighestZoneThisTranscension"])); this.HighestZoneLifetimeData.AddOrUpdate(uploadTime, Convert.ToDouble(reader["HighestZoneLifetime"])); this.AscensionsThisTranscensionData.AddOrUpdate(uploadTime, Convert.ToDouble(reader["AscensionsThisTranscension"])); this.AscensionsLifetimeData.AddOrUpdate(uploadTime, Convert.ToDouble(reader["AscensionsLifetime"])); } if (!reader.NextResult()) { return; } this.AncientLevelData = new SortedDictionary <string, IDictionary <DateTime, BigInteger> >(StringComparer.OrdinalIgnoreCase); while (reader.Read()) { // The DateTime is a datetime2 which has no timezone so comes out as DateTimeKind.Unknown. Se need to specify the kind so it gets serialized correctly. var uploadTime = DateTime.SpecifyKind(Convert.ToDateTime(reader["UploadTime"]), DateTimeKind.Utc); var ancientId = Convert.ToInt32(reader["AncientId"]); var level = reader["Level"].ToString().ToBigInteger(); if (!gameData.Ancients.TryGetValue(ancientId, out var ancient)) { telemetryClient.TrackEvent("Unknown Ancient", new Dictionary <string, string> { { "AncientId", ancientId.ToString() } }); continue; } if (!this.AncientLevelData.TryGetValue(ancient.Name, out var levelData)) { levelData = new SortedDictionary <DateTime, BigInteger>(); this.AncientLevelData.Add(ancient.Name, levelData); } levelData.AddOrUpdate(uploadTime, level); } if (!reader.NextResult()) { return; } this.OutsiderLevelData = new SortedDictionary <string, IDictionary <DateTime, double> >(StringComparer.OrdinalIgnoreCase); while (reader.Read()) { // The DateTime is a datetime2 which has no timezone so comes out as DateTimeKind.Unknown. Se need to specify the kind so it gets serialized correctly. var uploadTime = DateTime.SpecifyKind(Convert.ToDateTime(reader["UploadTime"]), DateTimeKind.Utc); var outsiderId = Convert.ToInt32(reader["OutsiderId"]); var level = Convert.ToDouble(reader["Level"]); if (!gameData.Outsiders.TryGetValue(outsiderId, out var outsider)) { telemetryClient.TrackEvent("Unknown Outsider", new Dictionary <string, string> { { "OutsiderId", outsiderId.ToString() } }); continue; } if (!this.OutsiderLevelData.TryGetValue(outsider.Name, out var levelData)) { levelData = new SortedDictionary <DateTime, double>(); this.OutsiderLevelData.Add(outsider.Name, levelData); } levelData.AddOrUpdate(uploadTime, level); } } this.IsValid = true; }
/// <inheritdoc/> public async Task <UserSettings> GetAsync(string userId) { // If the user isn't logged in, use the default settings if (string.IsNullOrEmpty(userId)) { return(new UserSettings()); } UserSettings userSettings = new(); Dictionary <string, object> parameters = new() { { "@UserId", userId }, }; const string GetUserSettingsCommandText = @" SELECT SettingId, SettingValue FROM UserSettings WHERE UserId = @UserId"; using (IDatabaseCommand command = _databaseCommandFactory.Create( GetUserSettingsCommandText, parameters)) using (System.Data.IDataReader reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { byte settingId = Convert.ToByte(reader["SettingId"]); string settingValue = reader["SettingValue"].ToString(); switch (settingId) { case UserSettingsConstants.PlayStyle: userSettings.PlayStyle = Enum.TryParse(settingValue, out PlayStyle playStyle) ? new PlayStyle?(playStyle) : null; break; case UserSettingsConstants.UseScientificNotation: userSettings.UseScientificNotation = bool.TryParse(settingValue, out bool useScientificNotation) ? new bool?(useScientificNotation) : null; break; case UserSettingsConstants.ScientificNotationThreshold: userSettings.ScientificNotationThreshold = int.TryParse(settingValue, out int scientificNotationThreshold) ? new int?(scientificNotationThreshold) : null; break; case UserSettingsConstants.UseLogarithmicGraphScale: userSettings.UseLogarithmicGraphScale = bool.TryParse(settingValue, out bool useLogarithmicGraphScale) ? new bool?(useLogarithmicGraphScale) : null; break; case UserSettingsConstants.LogarithmicGraphScaleThreshold: userSettings.LogarithmicGraphScaleThreshold = int.TryParse(settingValue, out int logarithmicGraphScaleThreshold) ? new int?(logarithmicGraphScaleThreshold) : null; break; case UserSettingsConstants.HybridRatio: userSettings.HybridRatio = double.TryParse(settingValue, out double hybridRatio) ? new double?(hybridRatio) : null; break; case UserSettingsConstants.Theme: userSettings.Theme = Enum.TryParse(settingValue, out SiteThemeType theme) ? new SiteThemeType?(theme) : null; break; case UserSettingsConstants.ShouldLevelSkillAncients: userSettings.ShouldLevelSkillAncients = bool.TryParse(settingValue, out bool shouldLevelSkillAncients) ? new bool?(shouldLevelSkillAncients) : null; break; case UserSettingsConstants.SkillAncientBaseAncient: userSettings.SkillAncientBaseAncient = int.TryParse(settingValue, out int skillAncientBaseAncient) ? new int?(skillAncientBaseAncient) : null; break; case UserSettingsConstants.SkillAncientLevelDiff: userSettings.SkillAncientLevelDiff = int.TryParse(settingValue, out int skillAncientLevelDiff) ? new int?(skillAncientLevelDiff) : null; break; case UserSettingsConstants.GraphSpacingType: userSettings.GraphSpacingType = Enum.TryParse(settingValue, out GraphSpacingType graphSpacingType) ? new GraphSpacingType?(graphSpacingType) : null; break; } } } return(userSettings); }