public IReadOnlyCollection <T> FindMany(IDatabaseCommand command) => Find(cmd => cmd.FindMany(command.CommandText, command.CommandType, _mapper.Map, command.CommandParameters).ToList());
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>
/// Executes a database command as non query.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the affected rows quantity.</param>
public static void ExecuteNonQuery(this IDatabaseConnector connector, IDatabaseCommand command, Action <int> action)
{
connector.OpenMySqlConnectionWhenClose();
action(command.ExecuteNonQuery());
}
/// <summary>
/// Executes a database command as scalar.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the scalar value.</param>
public static void ExecuteScalar(this IDatabaseConnector connector, IDatabaseCommand command, Action <object> action)
{
connector.OpenMySqlConnectionWhenClose();
action(command.ExecuteScalar());
}
/// <summary>
/// Executes a database command as data reader.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the database reader.</param>
public static async Task ExecuteReaderAsync(this IDatabaseConnector connector, IDatabaseCommand command, Action <IDatabaseReader> action)
{
await connector.OpenMySqlConnectionWhenCloseAsync();
using (var reader = await command.ExecuteReaderAsync())
{
action(reader);
}
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
this.Command?.Dispose();
this.Command = null;
this.Connector = null;
}
private DbCommand CreateDbCommnad(IDatabaseCommand databaseCommand)
{
databaseCommand.DbCommand.Connection = OpenConnection();
databaseCommand.DbCommand.Transaction = _DbTransaction;
return(databaseCommand.DbCommand);
}
/// <summary>
/// Executes a database command as scalar.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the scalar value.</param>
public static async Task ExecuteScalarAsync(this IDatabaseConnector connector, IDatabaseCommand command, Action <object> action)
{
await connector.OpenMySqlConnectionWhenCloseAsync();
action(await command.ExecuteScalarAsync());
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseCommandException"/> class.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="innerException">Inner exception.</param>
public DatabaseCommandException(IDatabaseCommand command, Exception innerException) : base(DefaultMessage, innerException)
{
this.Command = command ?? throw new ArgumentNullException(nameof(command));
}
public T GetScalar <T>(IDatabaseCommand dataCommand)
{
return((T)CreateDbCommnad(dataCommand).ExecuteScalar());
}
/// <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);
}
/// <inheritdoc/>
public async Task PatchAsync(string userId, UserSettings userSettings)
{
if (string.IsNullOrEmpty(userId))
{
throw new ArgumentNullException(nameof(userId));
}
if (userSettings == null)
{
throw new ArgumentNullException(nameof(userSettings));
}
/* Build a query that looks like this:
* MERGE INTO UserSettings WITH (HOLDLOCK)
* USING
* (VALUES (@UserId, 1, @Value1), (@UserId, 2, @Value2), ... )
* AS Input(UserId, SettingId, SettingValue)
* ON UserSettings.UserId = Input.UserId
* AND UserSettings.SettingId = Input.SettingId
* WHEN MATCHED THEN
* UPDATE
* SET
* SettingValue = Input.SettingValue
* WHEN NOT MATCHED THEN
* INSERT (UserId, SettingId, SettingValue)
* VALUES (Input.UserId, Input.SettingId, Input.SettingValue);");
*/
StringBuilder setUserSettingsCommandText = new();
Dictionary <string, object> parameters = new()
{
{ "@UserId", userId },
};
bool isFirst = true;
setUserSettingsCommandText.Append(@"
MERGE INTO UserSettings WITH (HOLDLOCK)
USING
( VALUES ");
void AppendSetting(byte settingId, string settingValue)
{
if (string.IsNullOrEmpty(settingValue))
{
return;
}
if (!isFirst)
{
setUserSettingsCommandText.Append(',');
}
// No need to sanitize settingId as it's just a number
setUserSettingsCommandText.Append("(@UserId,");
setUserSettingsCommandText.Append(settingId);
setUserSettingsCommandText.Append(",@Value");
setUserSettingsCommandText.Append(settingId);
setUserSettingsCommandText.Append(')');
parameters.Add("@Value" + settingId, settingValue);
isFirst = false;
}
AppendSetting(UserSettingsConstants.PlayStyle, userSettings.PlayStyle?.ToString());
AppendSetting(UserSettingsConstants.UseScientificNotation, userSettings.UseScientificNotation?.ToString());
AppendSetting(UserSettingsConstants.ScientificNotationThreshold, userSettings.ScientificNotationThreshold?.ToString());
AppendSetting(UserSettingsConstants.UseLogarithmicGraphScale, userSettings.UseLogarithmicGraphScale?.ToString());
AppendSetting(UserSettingsConstants.LogarithmicGraphScaleThreshold, userSettings.LogarithmicGraphScaleThreshold?.ToString());
AppendSetting(UserSettingsConstants.HybridRatio, userSettings.HybridRatio?.ToString());
AppendSetting(UserSettingsConstants.Theme, userSettings.Theme?.ToString());
AppendSetting(UserSettingsConstants.ShouldLevelSkillAncients, userSettings.ShouldLevelSkillAncients?.ToString());
AppendSetting(UserSettingsConstants.SkillAncientBaseAncient, userSettings.SkillAncientBaseAncient?.ToString());
AppendSetting(UserSettingsConstants.SkillAncientLevelDiff, userSettings.SkillAncientLevelDiff?.ToString());
AppendSetting(UserSettingsConstants.GraphSpacingType, userSettings.GraphSpacingType?.ToString());
// If no settings were appended, just short-circuit.
if (isFirst)
{
return;
}
setUserSettingsCommandText.Append(@"
)
AS Input(UserId, SettingId, SettingValue)
ON UserSettings.UserId = Input.UserId
AND UserSettings.SettingId = Input.SettingId
WHEN MATCHED THEN
UPDATE
SET
SettingValue = Input.SettingValue
WHEN NOT MATCHED THEN
INSERT (UserId, SettingId, SettingValue)
VALUES (Input.UserId, Input.SettingId, Input.SettingValue);");
using (IDatabaseCommand command = _databaseCommandFactory.Create(setUserSettingsCommandText.ToString(), parameters))
{
await command.ExecuteNonQueryAsync();
}
}
public Program(string[] args, IDatabaseCommand databaseCommand) : this(args)
{
_databaseCommand = databaseCommand;
}
/// <summary>
/// Executes a database command as non query.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
public static async Task ExecuteNonQueryAsync(this IDatabaseConnector connector, IDatabaseCommand command)
{
await connector.OpenMySqlConnectionWhenCloseAsync();
await command.ExecuteNonQueryAsync();
}
public int ExecuteNonQuery(IDatabaseCommand dataCommand)
{
return(CreateDbCommnad(dataCommand).ExecuteNonQuery());
}
/// <summary>
/// Executes a database command as non query.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the affected rows quantity.</param>
public static async Task <T> ExecuteNonQueryAsync <T>(this IDatabaseConnector connector, IDatabaseCommand command, Func <int, T> action)
{
await connector.OpenMySqlConnectionWhenCloseAsync();
return(action(await command.ExecuteNonQueryAsync()));
}
/// <summary>
/// Initializes a new instance of DatabaseRetryExceptions
/// </summary>
/// <param name="command"></param>
internal DatabaseRetry(IDatabaseCommand command)
{
_command = command;
}
/// <summary>
/// Executes a database command as scalar.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the scalar value.</param>
public static async Task <T> ExecuteScalarAsync <T>(this IDatabaseConnector connector, IDatabaseCommand command, Func <object, Task <T> > action)
{
await connector.OpenMySqlConnectionWhenCloseAsync();
return(await action(await command.ExecuteScalarAsync()));
}
public void Command(IDatabaseCommand command)
{
command.Execute(SessionManager);
}
/// <summary>
/// Executes a database command as data reader.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the database reader.</param>
public static async Task <T> ExecuteReaderAsync <T>(this IDatabaseConnector connector, IDatabaseCommand command, Func <IDatabaseReader, T> action)
{
await connector.OpenMySqlConnectionWhenCloseAsync();
using (var reader = await command.ExecuteReaderAsync())
{
return(action(reader));
}
}
public T Command <T>(IDatabaseCommand <T> command)
{
return(command.Execute(SessionManager));
}
/// <summary>
/// Initializes this instance.
/// </summary>
private void Initialize()
{
this.Command = this.Connector.CreateCommand("SELECT LAST_INSERT_ID()");
}
public async Task <ActionResult <int> > AddAsync([FromForm] UploadRequest uploadRequest)
{
// Only associate it with the user if they requested that it be added to their progress.
string userId = uploadRequest.AddToProgress && User.Identity.IsAuthenticated
? _userManager.GetUserId(User)
: null;
SavedGame savedGame = SavedGame.Parse(uploadRequest.EncodedSaveData);
if (savedGame == null)
{
// Not a valid save
return(BadRequest());
}
// Kick off a clan update in parallel
string gameUserId = savedGame.Object.Value <string>("uniqueId");
string passwordHash = savedGame.Object.Value <string>("passwordHash");
Task updateClanTask = _clanManager.UpdateClanAsync(userId, gameUserId, passwordHash);
PlayStyle playStyle;
if (uploadRequest.PlayStyle.HasValue)
{
playStyle = uploadRequest.PlayStyle.Value;
}
else
{
Website.Models.Api.Users.UserSettings userSettings = await _userSettingsProvider.GetAsync(userId);
playStyle = userSettings.PlayStyle.GetValueOrDefault(PlayStyle.Hybrid);
}
// unixTimestamp is in milliseconds instead of seconds
DateTime saveTime = (savedGame.Object.Value <double>("unixTimestamp") / 1000).UnixTimeStampToDateTime();
AncientLevelsModel ancientLevels = new(
_gameData,
savedGame);
OutsiderLevelsModel outsiderLevels = new(
_gameData,
savedGame);
ComputedStats computedStats = new(savedGame);
int uploadId;
using (IDatabaseCommand command = _databaseCommandFactory.Create())
{
await command.BeginTransactionAsync();
// Insert Upload
command.CommandText = @"
INSERT INTO Uploads(UserId, UploadContent, PlayStyle, SaveTime)
VALUES(@UserId, @UploadContent, @PlayStyle, @SaveTime);
SELECT SCOPE_IDENTITY();";
command.Parameters = new Dictionary <string, object>
{
{ "@UserId", userId },
{ "@UploadContent", uploadRequest.EncodedSaveData },
{ "@PlayStyle", playStyle.ToString() },
{ "@SaveTime", saveTime },
};
uploadId = Convert.ToInt32(await command.ExecuteScalarAsync());
// Insert computed stats
command.CommandText = @"
INSERT INTO ComputedStats(
UploadId,
TitanDamage,
SoulsSpent,
HeroSoulsSacrificed,
TotalAncientSouls,
TranscendentPower,
Rubies,
HighestZoneThisTranscension,
HighestZoneLifetime,
AscensionsThisTranscension,
AscensionsLifetime)
VALUES(
@UploadId,
@TitanDamage,
@SoulsSpent,
@HeroSoulsSacrificed,
@TotalAncientSouls,
@TranscendentPower,
@Rubies,
@HighestZoneThisTranscension,
@HighestZoneLifetime,
@AscensionsThisTranscension,
@AscensionsLifetime);";
command.Parameters = new Dictionary <string, object>
{
{ "@UploadId", uploadId },
{ "@TitanDamage", computedStats.TitanDamage },
{ "@SoulsSpent", computedStats.HeroSoulsSpent },
{ "@HeroSoulsSacrificed", computedStats.HeroSoulsSacrificed },
{ "@TotalAncientSouls", computedStats.TotalAncientSouls },
{ "@TranscendentPower", computedStats.TranscendentPower },
{ "@Rubies", computedStats.Rubies },
{ "@HighestZoneThisTranscension", computedStats.HighestZoneThisTranscension },
{ "@HighestZoneLifetime", computedStats.HighestZoneLifetime },
{ "@AscensionsThisTranscension", computedStats.AscensionsThisTranscension },
{ "@AscensionsLifetime", computedStats.AscensionsLifetime },
};
await command.ExecuteNonQueryAsync();
// Insert ancient levels
StringBuilder ancientLevelsCommandText = new("INSERT INTO AncientLevels(UploadId, AncientId, Level) VALUES");
Dictionary <string, object> ancientLevelsParameters = new()
{
{ "@UploadId", uploadId },
};
bool isFirstAncient = true;
foreach (KeyValuePair <int, BigInteger> pair in ancientLevels.AncientLevels)
{
if (!isFirstAncient)
{
ancientLevelsCommandText.Append(',');
}
// Looks like: (@UploadId, @AncientId{AncientId}, @AncientLevel{AncientId})
string idParamName = "@AncientId" + pair.Key;
string levelParamName = "@AncientLevel" + pair.Key;
ancientLevelsCommandText.Append("(@UploadId,");
ancientLevelsCommandText.Append(idParamName);
ancientLevelsCommandText.Append(',');
ancientLevelsCommandText.Append(levelParamName);
ancientLevelsCommandText.Append(')');
ancientLevelsParameters.Add(idParamName, pair.Key);
ancientLevelsParameters.Add(levelParamName, pair.Value.ToTransportableString());
isFirstAncient = false;
}
command.CommandText = ancientLevelsCommandText.ToString();
command.Parameters = ancientLevelsParameters;
await command.ExecuteNonQueryAsync();
// Insert outsider levels
StringBuilder outsiderLevelsCommandText = new("INSERT INTO OutsiderLevels(UploadId, OutsiderId, Level) VALUES");
Dictionary <string, object> outsiderLevelsParameters = new()
{
{ "@UploadId", uploadId },
};
bool isFirstOutsider = true;
foreach (KeyValuePair <int, long> pair in outsiderLevels.OutsiderLevels)
{
if (!isFirstOutsider)
{
outsiderLevelsCommandText.Append(',');
}
// Looks like: (@UploadId, @OutsiderId{OutsiderId}, @Level{OutsiderId})
string idParamName = "@OutsiderId" + pair.Key;
string levelParamName = "@OutsiderLevel" + pair.Key;
outsiderLevelsCommandText.Append("(@UploadId,");
outsiderLevelsCommandText.Append(idParamName);
outsiderLevelsCommandText.Append(',');
outsiderLevelsCommandText.Append(levelParamName);
outsiderLevelsCommandText.Append(')');
outsiderLevelsParameters.Add(idParamName, pair.Key);
outsiderLevelsParameters.Add(levelParamName, pair.Value);
isFirstOutsider = false;
}
command.CommandText = outsiderLevelsCommandText.ToString();
command.Parameters = outsiderLevelsParameters;
await command.ExecuteNonQueryAsync();
command.CommitTransaction();
}
// Wait for the task to finish, but don't fail the request if it fails
try
{
await updateClanTask;
}
catch (Exception e)
{
Dictionary <string, string> properties = new()
{
{ "UploadId", uploadId.ToString() },
};
_telemetryClient.TrackException(e, properties);
}
return(uploadId);
}
/// <summary>
/// Executes a database command as non query.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the affected rows quantity.</param>
public static T ExecuteNonQuery <T>(this IDatabaseConnector connector, IDatabaseCommand command, Func <int, T> action)
{
connector.OpenMySqlConnectionWhenClose();
return(action(command.ExecuteNonQuery()));
}
public async Task <ActionResult> DeleteAsync(int uploadId)
{
Dictionary <string, object> parameters = new()
{
{ "@UploadId", uploadId },
};
// First make sure the upload exists and belongs to the user
const string GetUploadUserCommandText = @"
SELECT UserId
FROM Uploads
WHERE Id = @UploadId";
using (IDatabaseCommand command = _databaseCommandFactory.Create(
GetUploadUserCommandText,
parameters))
using (IDataReader reader = await command.ExecuteReaderAsync())
{
if (reader.Read())
{
string uploadUserId = reader["UserId"].ToString();
string userId = _userManager.GetUserId(User);
bool isAdmin = User.IsInRole("Admin");
if (!string.Equals(uploadUserId, userId, StringComparison.OrdinalIgnoreCase) && !isAdmin)
{
// Not this user's, so not allowed
return(Forbid());
}
}
else
{
// If we didn't get data, it's an upload that doesn't exist
return(NotFound());
}
}
// Perform the deletion from all tables
const string DeleteUploadCommandText = @"
DELETE
FROM AncientLevels
WHERE UploadId = @UploadId;
DELETE
FROM OutsiderLevels
WHERE UploadId = @UploadId;
DELETE
FROM ComputedStats
WHERE UploadId = @UploadId;
DELETE
FROM Uploads
WHERE Id = @UploadId;";
using (IDatabaseCommand command = _databaseCommandFactory.Create(
DeleteUploadCommandText,
parameters))
{
await command.ExecuteNonQueryAsync();
}
return(Ok());
}
/// <summary>
/// Executes a database command as scalar.
/// </summary>
/// <param name="connector">The database connector.</param>
/// <param name="command">The database command.</param>
/// <param name="action">A callback action to process the scalar value.</param>
public static T ExecuteScalar <T>(this IDatabaseConnector connector, IDatabaseCommand command, Func <object, T> action)
{
connector.OpenMySqlConnectionWhenClose();
return(action(command.ExecuteScalar()));
}
public T?FindOne(IDatabaseCommand command) => Find(cmd => cmd.FindOne(command.CommandText, command.CommandType, _mapper.Map, command.CommandParameters));
