예제 #1
0
        public async Task ProcessPreliminaryApplicant(AuthUserEntity user, ICommandContext context = null)
        {
            try
            {
                var group = GetGroupByName(user.GroupName);
                if (group.Value == null)
                {
                    await LogHelper.LogWarning($"Group {user.GroupName} not found for character {user.Data.CharacterName} awaiting auth...");

                    return;
                }

                var rChar = await APIHelper.ESIAPI.GetCharacterData(Reason, user.CharacterId, true);

                if (rChar == null)
                {
                    return;
                }

                if (user.Data.CorporationId != rChar.corporation_id || user.Data.AllianceId != rChar.alliance_id)
                {
                    await user.UpdateData(rChar);

                    await SQLHelper.SaveAuthUser(user);
                }

                // var longCorpId = rChar.corporation_id;
                //var longAllyId = rChar.alliance_id ?? 0;
                if ((await GetAuthRoleEntityById(group, rChar)).RoleEntities.Any())
                {
                    if (group.Value == null)
                    {
                        await LogHelper.LogWarning($"Unable to auth {user.Data.CharacterName}({user.CharacterId}) as its auth group {user.GroupName} do not exist in the settings file!",
                                                   Category);

                        if (Settings.WebAuthModule.AuthReportChannel != 0)
                        {
                            await APIHelper.DiscordAPI.SendMessageAsync(Settings.WebAuthModule.AuthReportChannel,
                                                                        $"{group.Value.DefaultMention} {LM.Get("authUnableToProcessUserGroup", user.Data.CharacterName, user.CharacterId, user.GroupName)}");
                        }
                    }

                    //auth
                    await AuthUser(context, user.RegCode, user.DiscordId);
                }
            }
            catch (Exception ex)
            {
                await LogHelper.LogEx($"Auth check for {user?.Data.CharacterName}", ex, Category);
            }
        }
예제 #2
0
        public static async Task <bool> Upgrade()
        {
            var version = await Query <string>("cache_data", "data", "name", "version") ?? await Query <string>("cacheData", "data", "name", "version");

            var isNew = string.IsNullOrEmpty(version) || SettingsManager.IsNew;

            var vDbVersion = isNew ? new Version(Program.VERSION) : new Version(version);

            try
            {
                var firstUpdate = new Version(MajorVersionUpdates[0]);
                if (vDbVersion < firstUpdate)
                {
                    await LogHelper.LogError("Your database version is below the required minimum for an upgrade. You have to do clean install without the ability to migrate your data. Consult GitHub WIKI or reach @panthernet#1659 on Discord group for assistance.");

                    return(false);
                }

                foreach (var update in MajorVersionUpdates)
                {
                    var v = new Version(update);
                    if (vDbVersion >= v)
                    {
                        continue;
                    }

                    switch (update)
                    {
                    case "1.0.1":
                        await RunCommand("DELETE FROM cacheData where name='version'");
                        await RunCommand("CREATE UNIQUE INDEX cacheData_name_uindex ON cacheData (name)");
                        await RunCommand("CREATE TABLE `killFeedCache` ( `type` text NOT NULL, `id` text NOT NULL, `lastId` TEXT)");
                        await RunCommand("CREATE UNIQUE INDEX killFeedCache_type_id_uindex ON killFeedCache (type, id)");
                        await RunCommand("delete from cache");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.0.7":
                        await RunCommand("CREATE TABLE `timersAuth` ( `id` text UNIQUE PRIMARY KEY NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);");
                        await RunCommand(
                            "CREATE TABLE `timers` ( `id` INTEGER PRIMARY KEY NOT NULL, `timerType` int NOT NULL, `timerStage` int NOT NULL,`timerLocation` text NOT NULL, `timerOwner` text NOT NULL, `timerET` timestamp NOT NULL,`timerNotes` text, `timerChar` text NOT NULL, `announce` int NOT NULL DEFAULT 0);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.0.8":
                        await RunCommand("ALTER TABLE refreshTokens ADD mail TEXT NULL;");
                        await RunCommand("CREATE TABLE `mail` ( `id` text UNIQUE PRIMARY KEY NOT NULL, `mailId` int DEFAULT 0);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.1.3":
                        await RunCommand("CREATE TABLE `fleetup` ( `id` text UNIQUE PRIMARY KEY NOT NULL, `announce` int NOT NULL DEFAULT 0);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.1.4":
                        await RunCommand("DROP TABLE notificationsList;");
                        await RunCommand("DROP TABLE notifications;");
                        await RunCommand("CREATE TABLE `notificationsList` ( groupName TEXT NOT NULL, filterName TEXT NOT NULL,`id` int NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.1.5":
                        await RunCommand("CREATE TABLE `incursions` ( `constId` int UNIQUE PRIMARY KEY NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.1.6":
                        await RunCommand("CREATE TABLE `nullCampaigns` ( `groupKey` text NOT NULL, `campaignId` INTEGER NOT NULL, `time` timestamp NOT NULL, `data` TEXT NOT NULL, `lastAnnounce` INTEGER NOT NULL DEFAULT 0);");
                        await RunCommand("CREATE INDEX nullCampaigns_groupKey_uindex ON nullCampaigns (groupKey);");
                        await RunCommand("CREATE UNIQUE INDEX nullCampaigns_groupKey_campaignId_uindex ON nullCampaigns (groupKey, campaignId);");

                        //https://www.fuzzwork.co.uk/dump/latest/
                        if (await RunScript(Path.Combine(SettingsManager.RootDirectory, "Content", "SQL", "1.1.6.sql")))
                        {
                            await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");
                        }
                        else
                        {
                            await LogHelper.LogError($"Upgrade to DB version {update} FAILED! Script not found!");
                        }
                        break;

                    case "1.1.8":
                        await RunCommand(
                            "CREATE TABLE `userTokens` ( `characterID` INT UNIQUE NOT NULL, `characterName` TEXT NOT NULL, `discordUserId` INT NOT NULL DEFAULT 0, `refreshToken` TEXT NOT NULL, `groupName` TEXT NOT NULL DEFAULT 'DEFAULT', `permissions` TEXT NOT NULL, `authState` INT NOT NULL DEFAULT 0);");

                        await LogHelper.LogWarning("Step 1 finished...");
                        await RunCommand("DELETE FROM `pendingUsers`;");
                        await RunCommand("CREATE UNIQUE INDEX ux_pendingUsers_characterID ON `pendingUsers`(`characterID`);;");

                        await LogHelper.LogWarning("Step 2 finished...");
                        await RunCommand("ALTER TABLE `pendingUsers` ADD COLUMN `discordID` INT NOT NULL DEFAULT 0;");

                        await LogHelper.LogWarning("Step 3 finished...");
                        await RunCommand("CREATE TABLE `hrmAuth` ( `id` text UNIQUE PRIMARY KEY NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `code` TEXT NOT NULL);");

                        await LogHelper.LogWarning("Step 4 finished...");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.2":
                        await BackupDatabase();
                        await RunCommand("CREATE TABLE invTypes(typeID INTEGER PRIMARY KEY NOT NULL,groupID INTEGER,typeName VARCHAR(100),description TEXT,mass FLOAT,volume FLOAT,capacity FLOAT,portionSize INTEGER,raceID INTEGER,basePrice DECIMAL(19,4),published BOOLEAN,marketGroupID INTEGER,iconID INTEGER,soundID INTEGER,graphicID INTEGER);");
                        await RunCommand("CREATE INDEX ix_invTypes_groupID ON invTypes (groupID);");
                        await RunCommand("CREATE TABLE mapConstellations(regionID INTEGER,constellationID INTEGER PRIMARY KEY NOT NULL,constellationName VARCHAR(100),x FLOAT,y FLOAT,z FLOAT,xMin FLOAT,xMax FLOAT,yMin FLOAT,yMax FLOAT,zMin FLOAT,zMax FLOAT,factionID INTEGER,radius FLOAT);");
                        await RunCommand("CREATE TABLE mapRegions(regionID INTEGER PRIMARY KEY NOT NULL,regionName VARCHAR(100),x FLOAT,y FLOAT,z FLOAT,xMin FLOAT,xMax FLOAT,yMin FLOAT,yMax FLOAT,zMin FLOAT,zMax FLOAT,factionID INTEGER,radius FLOAT);");
                        await RunCommand("CREATE TABLE invGroups(groupID INTEGER PRIMARY KEY NOT NULL,categoryID INTEGER,groupName VARCHAR(100),iconID INTEGER,useBasePrice BOOLEAN,anchored BOOLEAN,anchorable BOOLEAN,fittableNonSingleton BOOLEAN,published BOOLEAN);");
                        await RunCommand("CREATE INDEX ix_invGroups_categoryID ON invGroups (categoryID);");
                        await RunCommand("DELETE FROM `cache`;");

                        if (!await CopyTableDataFromDefault("invTypes", "invGroups", "mapConstellations", "mapRegions", "mapSolarSystems"))
                        {
                            await RestoreDatabase();

                            return(false);
                        }

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.6":
                        await BackupDatabase();
                        await RunCommand("ALTER TABLE `refreshTokens` ADD COLUMN `ctoken` TEXT;");
                        await RunCommand("CREATE TABLE contracts(`characterID` INTEGER PRIMARY KEY NOT NULL,`type` INTEGER NOT NULL,`data` TEXT NOT NULL);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.7":
                        await BackupDatabase();
                        await RunCommand("CREATE TABLE contracts(`characterID` INTEGER PRIMARY KEY NOT NULL,`type` INTEGER NOT NULL,`data` TEXT NOT NULL);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.8":
                        await BackupDatabase();
                        await RunCommand("DROP TABLE `contracts`;");
                        await RunCommand("CREATE TABLE contracts(`characterID` INTEGER PRIMARY KEY NOT NULL,`data` TEXT, `corpdata` TEXT);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.10":
                        await BackupDatabase();
                        await RunCommand("CREATE TABLE standAuth(`characterID` INTEGER PRIMARY KEY NOT NULL, `token` TEXT, `personalStands` TEXT, `corpStands` TEXT, `allianceStands` TEXT);");

                        break;

                    case "1.2.14":
                        await BackupDatabase();

                        await LogHelper.LogWarning("Upgrading DB! Please wait...");

                        var users = await GetAuthUsersEx();

                        var tokens = await UserTokensGetAllEntriesEx();
                        await RunCommand("DROP TABLE `authUsers`;");
                        await RunCommand("CREATE TABLE authUsers(`Id` INTEGER PRIMARY KEY NOT NULL, `characterID` INTEGER NOT NULL, `discordID` INTEGER, `groupName` TEXT, `refreshToken` TEXT, `authState` INTEGER NOT NULL DEFAULT 0, `data` TEXT);");
                        await RunCommand("CREATE INDEX ix_authUsers_characterID ON authUsers (characterID);");
                        await RunCommand("CREATE INDEX ix_authUsers_discordID ON authUsers (discordID);");

                        await users.ParallelForEachAsync(async user =>
                        {
                            var t                   = tokens.FirstOrDefault(a => a.CharacterId == user.CharacterId);
                            user.AuthState          = t?.AuthState ?? (user.IsActive ? 2 : 0);
                            user.GroupName          = user.Group;
                            user.DiscordId          = user.DiscordId == 0 ? (t?.DiscordUserId ?? 0) : user.DiscordId;
                            user.RefreshToken       = t?.RefreshToken;
                            user.Data.Permissions   = t?.Permissions;
                            user.Data.CharacterName = user.EveName;

                            var cData = await APIHelper.ESIAPI.GetCharacterData("DB_UPGRADE", user.CharacterId);
                            if (cData != null)
                            {
                                var corp = await APIHelper.ESIAPI.GetCorporationData("DB_UPGRADE", cData.corporation_id);
                                user.Data.CorporationName   = corp?.name;
                                user.Data.CorporationTicker = corp?.ticker;
                                user.Data.CorporationId     = cData.corporation_id;
                                var ally = cData.alliance_id.HasValue ? await APIHelper.ESIAPI.GetAllianceData("DB_UPGRADE", cData.alliance_id) : null;
                                user.Data.AllianceName   = ally?.name;
                                user.Data.AllianceTicker = ally?.ticker;
                                user.Data.AllianceId     = cData.alliance_id ?? 0;
                            }
                        }, 10);

                        var cUsers  = new ConcurrentBag <AuthUserEntity>(users);
                        var lTokens = tokens.Where(a => users.All(b => b.CharacterId != a.CharacterId));
                        await lTokens.ParallelForEachAsync(async token =>
                        {
                            var item = new AuthUserEntity
                            {
                                CharacterId  = token.CharacterId,
                                DiscordId    = token.DiscordUserId,
                                GroupName    = token.GroupName,
                                AuthState    = token.AuthState,
                                RefreshToken = token.RefreshToken,
                                Data         = { CharacterName = token.CharacterName, Permissions = token.Permissions }
                            };
                            var cData = await APIHelper.ESIAPI.GetCharacterData("DB_UPGRADE", token.CharacterId);
                            if (cData != null)
                            {
                                var corp = await APIHelper.ESIAPI.GetCorporationData("DB_UPGRADE", cData.corporation_id);
                                item.Data.CorporationName   = corp?.name;
                                item.Data.CorporationId     = cData.corporation_id;
                                item.Data.CorporationTicker = corp?.ticker;
                                var ally = cData.alliance_id.HasValue ? await APIHelper.ESIAPI.GetAllianceData("DB_UPGRADE", cData.alliance_id) : null;
                                item.Data.AllianceName   = ally?.name;
                                item.Data.AllianceId     = cData.alliance_id ?? 0;
                                item.Data.AllianceTicker = ally?.ticker;
                            }
                            cUsers.Add(item);
                        }, 10);


                        var oUsers = cUsers.ToList();
                        oUsers.ToList().Select(a => a.DiscordId).Distinct().ToList().ForEach(item =>
                        {
                            if (item == 0)
                            {
                                return;
                            }
                            var l = oUsers.Where(a => a.DiscordId == item).ToList();
                            if (l.Count > 1)
                            {
                                var pending = l.Where(a => a.IsPending).ToList();
                                if (pending.Count == l.Count)
                                {
                                    l.Remove(pending[0]);
                                    oUsers.Remove(pending[0]);
                                    pending.RemoveAt(0);
                                }

                                pending.ForEach(d =>
                                {
                                    l.Remove(d);
                                    oUsers.Remove(d);
                                });
                                if (l.Count > 1)
                                {
                                    l.RemoveAt(0);
                                    l.ForEach(d => { oUsers.Remove(d); });
                                }
                            }
                        });

                        foreach (var a in oUsers)
                        {
                            a.Id = 0;
                            await SaveAuthUserEx(a, true);
                        }

                        await RunCommand("DROP TABLE `userTokens`;");

                        await LogHelper.LogWarning("Step 1 finished...");

                        //text fixes
                        await RunCommand("DROP TABLE `hrmAuth`;");
                        await RunCommand("CREATE TABLE `hrmAuth` ( `id` int UNIQUE PRIMARY KEY NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `code` TEXT NOT NULL);");
                        await RunCommand("DROP TABLE `fleetup`;");
                        await RunCommand("CREATE TABLE `fleetup` ( `id` int UNIQUE PRIMARY KEY NOT NULL, `announce` int NOT NULL DEFAULT 0);");
                        await RunCommand("DROP TABLE `mail`;");
                        await RunCommand("CREATE TABLE `mail` ( `id` int UNIQUE PRIMARY KEY NOT NULL, `mailId` int DEFAULT 0);");
                        await RunCommand("DROP TABLE `timersAuth`;");
                        await RunCommand("CREATE TABLE `timersAuth` ( `id` int UNIQUE PRIMARY KEY NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);");

                        await LogHelper.LogWarning("Step 2 finished...");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.15":
                        if (SettingsManager.Settings.Database.DatabaseProvider == "sqlite")
                        {
                            await RunCommand("drop table killFeedCache;");

                            await RunCommand("alter table authUsers rename to auth_users;");
                            await RunCommand("alter table cacheData rename to cache_data;");
                            await RunCommand("alter table hrmAuth rename to hrm_auth;");
                            await RunCommand("alter table invGroups rename to inv_groups;");
                            await RunCommand("alter table invTypes rename to inv_types;");
                            await RunCommand("alter table mapConstellations rename to map_constellations;");
                            await RunCommand("alter table mapRegions rename to map_regions;");
                            await RunCommand("alter table mapSolarSystems rename to map_solar_systems;");
                            await RunCommand("alter table notificationsList rename to notifications_list;");
                            await RunCommand("alter table nullCampaigns rename to null_campaigns;");
                            await RunCommand("alter table pendingUsers rename to pending_users;");
                            await RunCommand("alter table refreshTokens rename to refresh_tokens;");
                            await RunCommand("alter table standAuth rename to stand_auth;");
                            await RunCommand("alter table timersAuth rename to timers_auth;");
                        }
                        if (SettingsManager.Settings.Database.DatabaseProvider == "mysql")
                        {
                            await RunCommand("drop table killfeedcache;");

                            await RunCommand("alter table authusers rename to auth_users;");
                            await RunCommand("alter table cachedata rename to cache_data;");
                            await RunCommand("alter table hrmauth rename to hrm_auth;");
                            await RunCommand("alter table invgroups rename to inv_groups;");
                            await RunCommand("alter table invtypes rename to inv_types;");
                            await RunCommand("alter table mapconstellations rename to map_constellations;");
                            await RunCommand("alter table mapregions rename to map_regions;");
                            await RunCommand("alter table mapsolarsystems rename to map_solar_systems;");
                            await RunCommand("alter table notificationslist rename to notifications_list;");
                            await RunCommand("alter table nullcampaigns rename to null_campaigns;");
                            await RunCommand("alter table pendingusers rename to pending_users;");
                            await RunCommand("alter table refreshtokens rename to refresh_tokens;");
                            await RunCommand("alter table standauth rename to stand_auth;");
                            await RunCommand("alter table timersauth rename to timers_auth;");

                            if (!string.IsNullOrEmpty(SettingsManager.Settings.Database.DatabaseName))
                            {
                                await RunCommand($"ALTER DATABASE `{SettingsManager.Settings.Database.DatabaseName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;");
                            }
                        }

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.2.16":
                        await BackupDatabase();

                        var pUsers = await GetPendingUsersEx();

                        await RunCommand("drop table pending_users;");
                        await RunCommand("ALTER TABLE `auth_users` ADD COLUMN `reg_code` TEXT;");
                        await RunCommand("ALTER TABLE `auth_users` ADD COLUMN `reg_date` timestamp;");

                        foreach (var user in pUsers.Where(a => a.Active))
                        {
                            var dbentry = await GetAuthUserByCharacterId(user.CharacterId);

                            if (dbentry != null)
                            {
                                dbentry.RegCode    = user.AuthString;
                                dbentry.CreateDate = user.CreateDate;
                                await SaveAuthUser(dbentry);
                            }
                            else
                            {
                                var au = new AuthUserEntity
                                {
                                    CharacterId = user.CharacterId,
                                    DiscordId   = 0,
                                    RegCode     = user.AuthString,
                                    AuthState   = 0,
                                    CreateDate  = user.CreateDate,
                                    Data        = new AuthUserData()
                                };
                                await au.UpdateData();
                                await SaveAuthUser(au);
                            }
                        }

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    //MYSQL HAS BEEN ADDED HERE
                    case "1.2.19":
                        await Delete("notifications_list", "id", 999990000);

                        break;

                    case "1.3.1":
                        await RunCommand("ALTER TABLE `auth_users` ADD COLUMN `dump_date` timestamp NULL;");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.3.2":
                        if (SettingsManager.Settings.Database.DatabaseProvider == "sqlite")
                        {
                            await RunCommand("CREATE TABLE `sovIndexTracker` ( `groupName` TEXT UNIQUE NOT NULL, `data` TEXT NOT NULL);");
                        }
                        else
                        {
                            await RunCommand("CREATE TABLE `sovIndexTracker` ( `groupName` VARCHAR(100) UNIQUE NOT NULL, `data` TEXT NOT NULL);");
                        }
                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.3.4":
                        await RunCommand("ALTER TABLE `auth_users` ADD COLUMN `main_character_id` bigint NULL;");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.3.10":
                        await RunCommand("CREATE TABLE `web_editor_auth` ( `id` int UNIQUE PRIMARY KEY NOT NULL, `code` TEXT NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    case "1.3.16":
                        await RunCommand("ALTER TABLE `refresh_tokens` ADD COLUMN `indtoken` bigint NULL;");
                        await RunCommand("CREATE TABLE `industry_jobs` (`character_id` bigint UNIQUE NOT NULL, `personal_jobs` TEXT NULL, `corporate_jobs` TEXT NULL);");
                        await RunCommand("DELETE FROM `contracts`;");

                        await LogHelper.LogWarning($"Upgrade to DB version {update} is complete!");

                        break;

                    default:
                        continue;
                    }
                }

                //update version in DB
                InsertOrUpdate("cache_data", new Dictionary <string, object>
                {
                    { "name", "version" },
                    { "data", Program.VERSION }
                }).GetAwaiter().GetResult();


                return(true);
            }
            catch (Exception ex)
            {
                await LogHelper.LogEx("Upgrade", ex, LogCat.Database);

                return(false);
            }
            finally
            {
                SettingsManager.IsNew = false;
            }
        }