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