/// <summary> /// Returns the most popular command of all time, with how many uses it has. /// </summary> /// <returns></returns> public static Dictionary <string, int> GetMostPopularCommandAsync() { var dic = new Dictionary <string, int>(); using (var db = new KaguyaDb()) { IQueryable <CommandHistory> command = from h in db.GetTable <CommandHistory>() select h; IQueryable <IGrouping <string, CommandHistory> > descending = command.GroupBy(x => x.Command); descending = descending.OrderByDescending(x => x.Count()); string name = descending.FirstOrDefault()?.Key; if (String.IsNullOrWhiteSpace(name)) { return(new Dictionary <string, int>()); } IQueryable <int> count = from c in db.GetTable <CommandHistory>() group c by c.Command into grp orderby grp.Count() descending select grp.Count(); dic.Add(name, count.Select(x => x).First()); return(dic); } }
public static async Task <Server> GetOrCreateServerAsync(ulong id) { using (var db = new KaguyaDb()) { bool exists = db.Servers.Any(x => x.ServerId == id); if (!exists) { await db.InsertAsync(new Server { ServerId = id }); await ConsoleLogger.LogAsync($"Server {id} created.", LogLvl.DEBUG); } return(await db.Servers .LoadWith(x => x.AntiRaid) .LoadWith(x => x.AutoAssignedRoles) .LoadWith(x => x.BlackListedChannels) .LoadWith(x => x.CommandHistory) .LoadWith(x => x.FilteredPhrases) .LoadWith(x => x.Fish) .LoadWith(x => x.MutedUsers) .LoadWith(x => x.Praise) .LoadWith(x => x.Quotes) .LoadWith(x => x.RoleRewards) .LoadWith(x => x.ServerExp) .LoadWith(x => x.WarnedUsers) .LoadWith(x => x.WarnSettings) .Where(s => s.ServerId == id).FirstAsync()); } }
public static async Task <int> SafeAddQuoteAsync(Server server, Quote quote) { using (var db = new KaguyaDb()) { await db.BeginTransactionAsync(); try { int id = await db.Servers .Where(s => s.ServerId == server.ServerId) .Select(s => s.NextQuoteId).FirstOrDefaultAsync(); quote.Id = id; int updateQuote = await db.InsertAsync(quote); IUpdatable <Server> statement = db.Servers .Where(s => s.ServerId == server.ServerId) .Set(i => i.NextQuoteId, id + 1); await db.CommitTransactionAsync(); return(id); } catch (Exception e) { await ConsoleLogger.LogAsync(e); await db.RollbackTransactionAsync(); } } return(-1); }
/// <summary> /// Returns all of the <see cref="T" /> objects from the database that match the given <see cref="Predicate{T}" />, /// limited by the <see cref="limit" /> parameter. The <see cref="selector" /> will determine what variable to order /// the results by. If the <see cref="selector" /> is provided, the results will be ordered based on the /// <see cref="orderByDescending" /> parameter. /// </summary> /// <typeparam name="T">The type of object to return results of.</typeparam> /// <typeparam name="TKey">The <see cref="TKey" /> to order our results by, assuming we want them ordered.</typeparam> /// <param name="predicate"></param> /// <param name="limit"></param> /// <param name="selector"></param> /// <param name="orderByDescending"></param> /// <returns></returns> public static async Task <List <T> > GetLimitAsync <T>(uint limit, Expression <Func <T, bool> > predicate = null, Expression <Func <T, object> > selector = null, bool orderByDescending = false) where T : class, IKaguyaQueryable <T> { // ReSharper disable once PossibleInvalidOperationException using (var db = new KaguyaDb()) { IQueryable <T> baseQuery = db.GetTable <T>().AsQueryable(); baseQuery = predicate == null ? baseQuery : baseQuery.Where(predicate); if (selector != null) { baseQuery = orderByDescending ? baseQuery.OrderByDescending(selector) : baseQuery.OrderBy(selector); return(await baseQuery.Take((int)limit).ToListAsync()); } if (orderByDescending) { throw new InvalidOperationException("Unable to apply descendant ordering with a null selector parameter."); } return(await baseQuery.Take((int)limit).ToListAsync()); } }
/// <summary> /// Returns the most recent set of statistics from the database. /// </summary> /// <returns></returns> public static async Task <KaguyaStatistics> GetMostRecentStatsAsync() { using (var db = new KaguyaDb()) { return(await(from t in db.GetTable <KaguyaStatistics>().OrderByDescending(x => x.TimeStamp) select t).FirstOrDefaultAsync()); } }
/// <summary> /// Bulk copies (inserts) the <see cref="IEnumerable{T}" /> into the database. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="args"></param> /// <returns></returns> public static async Task BulkCopy <T>(IEnumerable <T> args) where T : class, IKaguyaQueryable <T> // IKaguyaUnique<T> may be necessary, but we'll see. { using (var db = new KaguyaDb()) await Task.Run(() => { db.BulkCopy(args); }); }
/// <summary> /// Returns the first or default value from the database of a type that matches this <see cref="Predicate{T}" />. /// </summary> /// <typeparam name="T">The type of <see cref="IKaguyaQueryable{T}" /> object to return</typeparam> /// <param name="predicate"> /// An expression that the returned object must match. /// <code> /// await GetFirstMatchAsync{User}(x => x.UserId == SomeId); /// </code> /// </param> /// <returns></returns> public static async Task <T> GetFirstMatchAsync <T>(Expression <Func <T, bool> > predicate) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) { return(await(from t in db.GetTable <T>().Where(predicate) select t).FirstOrDefaultAsync()); } }
/// <summary> /// Returns a List of Fish that belongs to the user ID that have not been sold. This only returns /// actual fish, not the BAIT_STOLEN event. /// </summary> /// <param name="userId">The ID of the user who we want to get all of the unsold fish from.</param> /// <returns></returns> public static async Task <List <Fish> > GetUnsoldFishForUserAsync(ulong userId) { using (var db = new KaguyaDb()) { return(await(from f in db.Fish where f.UserId == userId && !f.Sold && f.FishType != FishType.BAIT_STOLEN select f).ToListAsync()); } }
/// <summary> /// Returns a <see cref="List{T}" /> that match the given predicate. /// </summary> /// <typeparam name="T">The type of <see cref="List{T}" /> to return.</typeparam> /// <param name="predicate">A condition that each returned object in the <see cref="List{T}" /> must match.</param> /// <param name="limit"> /// The amount of objects to return. If this value is below zero, the function will /// return all objects that match the <see cref="Predicate{T}" /> /// </param> /// <returns></returns> public static async Task <List <T> > GetAllAsync <T>(Expression <Func <T, bool> > predicate) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) { return(await(from t in db.GetTable <T>().Where(predicate) select t).ToListAsync()); } }
/// <summary> /// Returns a List of Fish that belong to the user ID. /// </summary> /// <param name="fishType">The type of fish we are selling.</param> /// <param name="userId">The id of the user who we want to get all of the fish from.</param> /// <returns></returns> public static async Task <List <Fish> > GetFishForUserMatchingTypeAsync(FishType fishType, ulong userId) { using (var db = new KaguyaDb()) { return(await(from f in db.Fish where f.UserId == userId && f.FishType == fishType select f).ToListAsync()); } }
/// <summary> /// Inserts the <see cref="T" /> <see cref="arg" /> into the database and returns the <see cref="T" /> <see cref="arg" /> /// with it's updated auto-incremented identifier. Use this only if the database table has an auto-incremented ID. /// </summary> /// <param name="arg"></param> /// <typeparam name="T"></typeparam> /// <returns>The id of the <see cref="T" />.</returns> public static async Task <int> InsertWithIdentityAsync <T>(T arg) where T : class, IKaguyaQueryable <T>, IKaguyaUnique <T> { using (var db = new KaguyaDb()) { int val = await db.InsertWithInt32IdentityAsync(arg); return(val); } }
/// <summary> /// Returns the FirstOrDefault <see cref="T" /> that matches the provided <see cref="userId" /> and <see cref="serverId" /> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="userId"></param> /// <param name="serverId"></param> /// <returns></returns> public static async Task <T> GetForServerAndUserAsync <T>(ulong userId, ulong serverId) where T : class, IKaguyaQueryable <T>, IUserSearchable <T>, IServerSearchable <T> { using (var db = new KaguyaDb()) { return(await(from t in db.GetTable <T>() where t.UserId == userId && t.ServerId == serverId select t).FirstOrDefaultAsync()); } }
/// <summary> /// Deletes all objects from the database that are specified in <see cref="args" /> /// </summary> /// <typeparam name="T">The type of object we are removing from the database.</typeparam> /// <param name="args">The <see cref="IEnumerable{T}" /> collection of objects to delete.</param> /// <returns></returns> public static async Task DeleteAsync <T>(IEnumerable <T> args) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) { foreach (T arg in args) { await db.DeleteAsync(arg); } } }
/// <summary> /// Deletes all of the <see cref="IServerSearchable{T}" /> objects from the database /// where the serverId matches the provided <see cref="serverId" /> /// </summary> /// <typeparam name="T">The <see cref="IServerSearchable{T}" /> to remove from the database.</typeparam> /// <param name="serverId">The id of the server to clear the objects from.</param> /// <returns></returns> public static async Task DeleteAllForServerAsync <T>(ulong serverId) where T : class, IKaguyaQueryable <T>, IServerSearchable <T> { using (var db = new KaguyaDb()) { await(from t in db.GetTable <T>() where t.ServerId == serverId select t).DeleteAsync(); } }
public static async Task <List <T> > GetAllForUserAsync <T>(ulong userId, Expression <Func <T, bool> > predicate) where T : class, IKaguyaQueryable <T>, IUserSearchable <T> { using (var db = new KaguyaDb()) { return(await(from t in db.GetTable <T>().Where(predicate) where t.UserId == userId select t).ToListAsync()); } }
public static async Task InsertAsync <T>(IEnumerable <T> arg) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) { foreach (T element in arg) { await db.InsertAsync(element); } } }
/// <summary> /// Returns a <see cref="List{T}" /> of objects that match the given <see cref="serverId" />. /// </summary> /// <typeparam name="T">The <see cref="IServerSearchable{T}" /> that we want to get for this <see cref="serverId" />.</typeparam> /// <param name="serverId">The Id of the server we are searching for.</param> /// <returns></returns> public static async Task <List <T> > GetAllForServerAsync <T>(ulong serverId) where T : class, IKaguyaQueryable <T>, IServerSearchable <T> { using (var db = new KaguyaDb()) { return(await(from t in db.GetTable <T>() where t.ServerId == serverId select t).ToListAsync()); } }
/// <summary> /// Sells a <see cref="Fish" /> to the "market", then adds the value of the fish to the /// <see cref="User" />'s points balance. This action will add the points to the user's account. /// This method is synchronous as of v2.5.2 due to errors relating to users not getting points when mass-selling fish. /// </summary> /// <param name="fish">The fish to sell.</param> /// <param name="userId">The ID of the user to add the value of the fish to.</param> /// <returns>The new total amount of points the user has.</returns> public static int SellFish(Fish fish, ulong userId) { User user = GetOrCreateUserAsync(userId).Result; user.Points += Fish.GetPayoutForFish(fish, user.FishExp); fish.Sold = true; using (var db = new KaguyaDb()) { db.UpdateAsync(fish); db.UpdateAsync(user); } return(user.Points); }
/// <summary> /// Inserts the object into the database. If it already exists, an exception will be thrown. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="arg">The object to insert, assuming it doesn't already exist.</param> /// <param name="throwExceptionIfPresent">Whether to throw an exception if the object was found in the database already.</param> /// <returns></returns> public static async Task InsertIfNotExistsAsync <T>(T arg, bool throwExceptionIfPresent = true) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) { if (await db.GetTable <T>().AnyAsync(x => x.Equals(arg))) { if (throwExceptionIfPresent) { throw new Exception("Item already exists in the database."); } } await db.InsertAsync(arg); } }
public static async Task <bool> TestConnectionAsync() { using (var db = new KaguyaDb()) { try { return(db.Connection.State.Equals(ConnectionState.Open)); } catch (Exception e) { await ConsoleLogger.LogAsync($"Failed to establish database connection!! {e.Message}", LogLvl.ERROR); return(false); } } }
public static double GetLastPraiseTime(ulong userId, ulong serverId) { using (var db = new KaguyaDb()) { try { return((from r in db.Praise.OrderByDescending(x => x.TimeGiven) where r.GivenBy == userId && r.ServerId == serverId select r).First().TimeGiven); } catch (InvalidOperationException) { return(0); } } }
public static async Task DeleteAllAsync <T>() where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) { List <T> records = await db.GetTable <T>().ToListAsync(); if (records.Count > 0) { foreach (T record in records) { await db.DeleteAsync(record); } } await ConsoleLogger.LogAsync($"Deleted all records of type {typeof(T)}", LogLvl.WARN); } }
public static async Task <User> GetOrCreateUserAsync(ulong id) { using (var db = new KaguyaDb()) { bool exists = db.Users.Any(x => x.UserId == id); if (!exists) { try { await db.InsertAsync(new User { UserId = id }); } catch (Exception e) { await ConsoleLogger.LogAsync($"Failed to create User with ID {id}\nException: {e}", LogLvl.ERROR); throw; } await ConsoleLogger.LogAsync($"User {id} created.", LogLvl.DEBUG); } return(await db.Users .LoadWith(x => x.Blacklist) .LoadWith(x => x.CommandHistory) .LoadWith(x => x.Fish) .LoadWith(x => x.GambleHistory) .LoadWith(x => x.Quotes) .LoadWith(x => x.Reminders) .LoadWith(x => x.Rep) .LoadWith(x => x.ServerExp) .Where(u => u.UserId == id).FirstAsync()); } }
/// <summary> /// Updates the specified <see cref="IKaguyaUnique{T}" /> <see cref="arg" /> in the database. Item /// must have a primary key in order for this query to execute. /// </summary> /// <typeparam name="T">The type of object we are updating</typeparam> /// <param name="arg">The object to update.</param> /// <returns></returns> public static async Task UpdateAsync <T>(T arg) where T : class, IKaguyaQueryable <T>, IKaguyaUnique <T> { using (var db = new KaguyaDb()) await db.UpdateAsync(arg); }
/// <summary> /// Returns the total amount of points in circulation across all users. /// </summary> /// <returns></returns> public static int GetTotalCurrency() { using (var db = new KaguyaDb()) return(Enumerable.Sum(db.Users, user => user.Points)); }
/// <summary> /// Executes the provided sql query and returns the number of affected rows. /// </summary> /// <param name="sql">The sql to execute asynchronously.</param> /// <returns>The number of affected rows</returns> public static async Task <int> ExecuteSqlAsync(string sql) { using (var db = new KaguyaDb()) return(await db.ExecuteAsync(sql)); }
/// <summary> /// Returns the number of objects that exist of type <see cref="T" /> matching the given <see cref="Predicate{T}" /> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <returns></returns> public static async Task <int> GetCountAsync <T>(Expression <Func <T, bool> > predicate) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) return(await db.GetTable <T>().Where(predicate).CountAsync()); }
/// <summary> /// Returns the number of objects that exist of type <see cref="T" /> /// </summary> /// <typeparam name="T">The type of object to return the number of occurances of.</typeparam> /// <returns></returns> public static async Task <int> GetCountAsync <T>() where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) return(await db.GetTable <T>().CountAsync()); }
/// <summary> /// Returns a <see cref="bool" /> that determines whether the provided <see cref="arg" /> exists in the database. /// If it does, this function will return true. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="arg">The object that we are checking to see exists in the database.</param> /// <returns></returns> public static async Task <bool> ItemExistsAsync <T>(T arg) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) return(await db.GetTable <T>().AnyAsync(x => x.Equals(arg))); }
public static async Task <bool> ItemExistsAsync <T>(IEnumerable <T> args, Expression <Func <T, bool> > predicate) where T : class, IKaguyaQueryable <T> { using (var db = new KaguyaDb()) return(await db.GetTable <T>().Where(predicate).AnyAsync(x => args.Contains(x))); }