public static async Task <Species[]> GetSpeciesAsync(UserInfo userInfo, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
{
string query = userInfo.Id == UserInfo.NullId ?
"SELECT * FROM Species WHERE owner = $owner" :
"SELECT * FROM Species WHERE user_id = $user_id";
if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
{
query = "SELECT * FROM Species WHERE owner = $owner OR user_id = $user_id";
}
List <Species> result = new List <Species>();
using (SQLiteCommand cmd = new SQLiteCommand(query)) {
cmd.Parameters.AddWithValue("$owner", userInfo.Username);
cmd.Parameters.AddWithValue("$user_id", userInfo.Id);
using (DataTable rows = await Database.GetRowsAsync(cmd)) {
foreach (DataRow row in rows.Rows)
{
result.Add(await SpeciesUtils.SpeciesFromDataRow(row));
}
result.Sort((lhs, rhs) => lhs.ShortName.CompareTo(rhs.ShortName));
}
}
return(result.ToArray());
}
public static async Task <UserInfo> GetUserInfoAsync(string username, ulong userId, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
{
// Note that we may have a null username or a null user ID.
// At least one of them has to be non-null.
UserInfo result = null;
if (username != UserInfo.NullUsername || userId != UserInfo.NullId)
{
// If we've been given a user ID, get all species records where that user is the owner.
// If we've just been given a username, we'll go by username instead.
string query;
if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id OR owner = $owner";
}
else
{
if (userId != UserInfo.NullId)
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id";
}
else
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE owner = $owner COLLATE NOCASE";
}
}
using (SQLiteCommand cmd = new SQLiteCommand(query)) {
cmd.Parameters.AddWithValue("$user_id", userId);
cmd.Parameters.AddWithValue("$owner", username);
using (DataTable table = await Database.GetRowsAsync(cmd))
if (table.Rows.Count > 0)
{
userId = table.Rows.Cast <DataRow>()
.Select(x => x.IsNull("user_id") ? UserInfo.NullId : (ulong)x.Field <long>("user_id"))
.FirstOrDefault(x => x != UserInfo.NullId);
username = table.Rows.Cast <DataRow>()
.Select(x => x.IsNull("owner") ? UserInfo.NullUsername : x.Field <string>("owner"))
.FirstOrDefault(x => x != UserInfo.NullUsername);
result = new UserInfo {
Id = userId == default(ulong) ? UserInfo.NullId : userId,
Username = username == default(string) ? UserInfo.NullUsername : username,
SubmissionCount = table.Rows.Count,
FirstSubmissionTimestamp = table.Rows.Cast <DataRow>().Select(x => (long)x.Field <decimal>("timestamp")).OrderBy(x => x).FirstOrDefault(),
LastSubmissionTimestamp = table.Rows.Cast <DataRow>().Select(x => (long)x.Field <decimal>("timestamp")).OrderByDescending(x => x).FirstOrDefault()
};
}
}
}
return(result);
}
public static async Task <ICreator> GetCreatorAsync(this SQLiteDatabase database, ICreator creator, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
{
ICreator result = null;
// Note that we may have a null username or a null user ID.
// At least one of them has to be non-null.
if (!string.IsNullOrEmpty(creator.Name) || creator.UserId.HasValue)
{
// If we've been given a user ID, get all species records where that user is the owner.
// If we've just been given a username, we'll go by username instead.
string query;
if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id OR owner = $owner";
}
else
{
if (creator.UserId.HasValue)
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id";
}
else
{
query = "SELECT owner, user_id, timestamp FROM Species WHERE owner = $owner COLLATE NOCASE";
}
}
using (SQLiteCommand cmd = new SQLiteCommand(query)) {
cmd.Parameters.AddWithValue("$user_id", creator.UserId);
cmd.Parameters.AddWithValue("$owner", creator.Name);
IEnumerable <DataRow> rows = await database.GetRowsAsync(cmd);
if (rows.Count() > 0)
{
ulong?userId = rows.Select(row => row.IsNull("user_id") ? null : (ulong?)row.Field <long>("user_id"))
.FirstOrDefault(id => id.HasValue);
string username = rows.Select(row => row.IsNull("owner") ? string.Empty : row.Field <string>("owner"))
.FirstOrDefault(name => !string.IsNullOrEmpty(name));
if (userId.HasValue || !string.IsNullOrEmpty(username))
{
long firstSpeciesTimestamp = rows.Select(row => (long)row.Field <decimal>("timestamp")).OrderBy(timestamp => timestamp).FirstOrDefault();
long lastSpeciesTimestamp = rows.Select(rowx => (long)rowx.Field <decimal>("timestamp")).OrderByDescending(timestamp => timestamp).FirstOrDefault();
result = new Creator(userId, username)
{
SpeciesCount = rows.Count(),
FirstSpeciesDate = firstSpeciesTimestamp > 0 ? DateUtilities.GetDateFromTimestamp(firstSpeciesTimestamp) : default(DateTimeOffset?),
LastSpeciesDate = lastSpeciesTimestamp > 0 ? DateUtilities.GetDateFromTimestamp(lastSpeciesTimestamp) : default(DateTimeOffset?)
};
}
}
}
}
return(result);
}
public static async Task <UserRank> GetRankAsync(UserInfo userInfo, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
{
return((await GetRanksAsync())
.Where(x => x.User.Id == userInfo.Id || ((flags.HasFlag(UserInfoQueryFlags.MatchEither) || x.User.Id == UserInfo.NullId) && x.User.Username == userInfo.Username))
.FirstOrDefault());
}
public static async Task <UserRank> GetRankAsync(this SQLiteDatabase database, ICreator creator, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
{
return((await database.GetRanksAsync())
.Where(x => x.User.Id == creator.UserId || ((flags.HasFlag(UserInfoQueryFlags.MatchEither) || x.User.Id == UserInfo.NullId) && x.User.Username == creator.Name))
.FirstOrDefault());
}