build.cogs.databasecog.DatabaseCog.db_get_warns() - Code Metrics - Inspection of "Smallfixes for issues from last 3 months (#22)" - BonHowi/discordPogmareBot - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( d8bd61...e8362f )

by Bartosz

created 2022-01-28 01:41 UTC

build.cogs.databasecog.DatabaseCog.db_get_warns() A

↳ Parent: Project

Complexity

Conditions

Size

Total Lines	15
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	15
dl	0
loc	15
rs	9.65
c	0
b	0
f	0
cc	2
nop	2

import os
from datetime import datetime

import discord
import pandas as pd
from discord.ext import commands, tasks
from discord_slash import cog_ext, SlashContext
from modules.get_settings import get_settings
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, \
    BigInteger, update, select, DateTime, delete
from sqlalchemy.dialects.mysql import insert
from sqlalchemy.sql import func
import cogs.cogbase as cogbase
from modules.get_settings import get_settings

metadata_obj = MetaData()

member = Table('member', metadata_obj,
               Column('id', BigInteger, primary_key=True),
               Column('name', String(50), nullable=False),
               Column('display_name', String(50), nullable=False)
               )
fk_member_id = "member.id"

warn = Table('warn', metadata_obj,
             Column('id', Integer, primary_key=True),
             Column('member_id', BigInteger, ForeignKey(fk_member_id)),
             Column('reason', String(120), nullable=False),
             Column('date', DateTime, nullable=False),
             )

coords = Table('coords', metadata_obj,
               Column('id', Integer, primary_key=True),
               Column('coords', String(100), nullable=False),
               Column('monster_type', String(20), nullable=False)
               )

spots = Table('spots', metadata_obj,
              Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
              Column('legendary', Integer, default=0),
              Column('rare', Integer, default=0),
              Column('common', Integer, default=0),
              Column('event1', Integer, default=0),
              Column('event2', Integer, default=0)
              )

spots_temp = Table('spots_temp', metadata_obj,
                   Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
                   Column('legendary', Integer, default=0),
                   Column('rare', Integer, default=0),
                   Column('common', Integer, default=0),
                   Column('event1', Integer, default=0),
                   Column('event2', Integer, default=0)
                   )

spots_lege = Table('spots_lege', metadata_obj,
                   Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
                   Column('AncientLeshen', Integer, default=0),
                   Column('Archgriffin', Integer, default=0),
                   Column('CopperWyvern', Integer, default=0),
                   Column('D\'jinni', Integer, default=0),
                   Column('DungShaelmaar', Integer, default=0),
                   Column('Erynia', Integer, default=0),
                   Column('Frightener', Integer, default=0),
                   Column('GraphiteSlyzard', Integer, default=0),
                   Column('GrimHag', Integer, default=0),
                   Column('Hym', Integer, default=0),
                   Column('IceElemental', Integer, default=0),
                   Column('IceGiant', Integer, default=0),
                   Column('IceTroll', Integer, default=0),
                   Column('Katakan', Integer, default=0),
                   Column('MottledGarkain', Integer, default=0),
                   Column('Penitent', Integer, default=0),
                   Column('PlagueMaiden', Integer, default=0),
                   Column('Sandcrab', Integer, default=0),
                   Column('SilverBasilisk', Integer, default=0),
                   Column('SwampHag', Integer, default=0),
                   Column('TarryChort', Integer, default=0),
                   Column('Thundster', Integer, default=0),
                   Column('Tormented', Integer, default=0),
                   Column('Ulfhedinn', Integer, default=0),
                   Column('UnseenElder', Integer, default=0),
                   Column('WaterDevil', Integer, default=0),
                   Column('WhiteStriga', Integer, default=0)
                   )

spots_rare = Table('spots_rare', metadata_obj,
                   Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
                   Column('Beann\'Shie', Integer, default=0),
                   Column('Beann\'She', Integer, default=0),
                   Column('BlueForktail', Integer, default=0),
                   Column('Bruxa', Integer, default=0),
                   Column('Burier', Integer, default=0),
                   Column('Cockatrice', Integer, default=0),
                   Column('DepthLurker', Integer, default=0),
                   Column('Devourer', Integer, default=0),
                   Column('DrownedDead', Integer, default=0),
                   Column('EndregaCharger', Integer, default=0),
                   Column('EndregaWarrior', Integer, default=0),
                   Column('Farbaut', Integer, default=0),
                   Column('FireElemental', Integer, default=0),
                   Column('GarkainAlpha', Integer, default=0),
                   Column('Gernichora', Integer, default=0),
                   Column('GraySlyzard', Integer, default=0),
                   Column('GreenHarpy', Integer, default=0),
                   Column('Grimnir', Integer, default=0),
                   Column('Grottore', Integer, default=0),
                   Column('Howler', Integer, default=0),
                   Column('IgnisFatuus', Integer, default=0),
                   Column('Katakan', Integer, default=0),
                   Column('KikimoreWarrior', Integer, default=0),
                   Column('Leshen', Integer, default=0),
                   Column('LeshenHound', Integer, default=0),
                   Column('Liho', Integer, default=0),
                   Column('Lycanthrope', Integer, default=0),
                   Column('MagmaTroll', Integer, default=0),
                   Column('NekkerShaman', Integer, default=0),
                   Column('Nightmare', Integer, default=0),
                   Column('NightSuccubus', Integer, default=0),
                   Column('Putrifier', Integer, default=0),
                   Column('RoyalFoglet', Integer, default=0),
                   Column('RoyalNekker', Integer, default=0),
                   Column('RoyalWyvern', Integer, default=0),
                   Column('RussetShaelmaar', Integer, default=0),
                   Column('Scurver', Integer, default=0),
                   Column('Shrieker', Integer, default=0),
                   Column('SpottedAlghoul', Integer, default=0),
                   Column('StoneGolem', Integer, default=0),
                   Column('Stinger', Integer, default=0),
                   Column('Striga', Integer, default=0),
                   Column('SylvanDearg', Integer, default=0),
                   Column('Wailwraith', Integer, default=0),
                   Column('VizimianArchespore', Integer, default=0)
                   Column('Striga', Integer, default=0),
                   Column('SylvanDearg', Integer, default=0),
                   Column('Wailwraith', Integer, default=0)
                   )


class DatabaseCog(cogbase.BaseCog):
    user = get_settings("DB_U")
    password = get_settings("DB_P")
    conn_string = f"mysql+mysqldb://{user}:{password}@localhost/server_database?charset=utf8mb4"
    engine = create_engine(conn_string, pool_recycle=3600)
    metadata_obj.create_all(engine)
    conn = None

    def __init__(self, base):
        super().__init__(base)

        # Connect to database
        self.engine = DatabaseCog.engine
        metadata_obj.create_all(self.engine)
        self.db_update_loop.start()
        self.conn = DatabaseCog.conn

    def cog_unload(self) -> None:
        self.db_update_loop.cancel()

    # ----- BASE DATABASE OPERATIONS -----

    # Add or update member in member table
    def db_add_update_member(self, guild_member) -> None:
        self.conn = self.engine.connect()
        stmt = insert(member).values(
            id=guild_member.id, name=guild_member.name,
            display_name=guild_member.display_name)
        do_update_stmt = stmt.on_duplicate_key_update(
            name=stmt.inserted.name, display_name=stmt.inserted.display_name
        )
        self.conn.execute(do_update_stmt)
        self.conn.close()

    # Add or update spots in spots table
    def db_add_update_spots(self, spots_table, guild_member) -> None:
        self.conn = self.engine.connect()
        stmt = insert(spots_table).values(
            member_id=guild_member.id)
        do_update_stmt = stmt.on_duplicate_key_update(member_id=stmt.inserted.member_id)
        self.conn.execute(do_update_stmt)
        self.conn.close()

    def add_update_member(self, guild_member):
        # Member tables
        self.db_add_update_member(guild_member)
        # Spots tables
        self.db_add_update_spots(spots, guild_member)
        self.db_add_update_spots(spots_temp, guild_member)
        self.db_add_update_spots(spots_lege, guild_member)
        self.db_add_update_spots(spots_rare, guild_member)

    # Add or refresh all guild members and spots to database
    async def db_update(self) -> None:
        self.conn = self.engine.connect()
        guild = self.bot.get_guild(self.bot.guild[0])
        self.create_log_msg("Refreshing member and spots tables")
        for guild_member in guild.members:
            self.add_update_member(guild_member)
        self.create_log_msg("Member and spots tables refreshed")
        self.conn.close()

    @tasks.loop(hours=12)
    async def db_update_loop(self) -> None:
        await self.db_update()
        await self.db_backup_database()

    @db_update_loop.before_loop
    async def before_db_update_loop(self) -> None:
        self.create_log_msg("Waiting until Bot is ready")
        await self.bot.wait_until_ready()

    # Add member to database on member join
    @commands.Cog.listener()
    async def on_member_join(self, guild_member) -> None:
        self.add_update_member(guild_member)

    # Backup database
    async def db_backup_database(self) -> None:
        now = datetime.now()
        backup_name = f"backup-{now.strftime('%m-%d-%Y')}"
        # Save backup to file
        backup_query = f"mysqldump -u {get_settings('DB_U')} " \
                       f"--result-file=database_backup/{backup_name}.sql " \
                       f"-p{get_settings('DB_P')} server_database"
        os.system(backup_query)

        zip_query = f"zip database_backup/{backup_name}.zip database_backup/{backup_name}.sql"
        os.system(zip_query)

        # Delete backup file
        rm_query = f"rm database_backup/{backup_name}.sql"
        os.system(rm_query)

        # # Send backup trough e-mail
        # mail_to = ""
        # mail_query = f"mail -a database_backup/{backup_name}.zip -s \"Backup {backup_name}\" {mail_to} <<< \" \""
        # os.system(mail_query)

        # Send backup to google drive
        try:
            drive_query = f"gdrive upload -p 1nXYNibvd4u-nWfj1tvIm7sz3Udh1ZD1k database_backup/{backup_name}.zip"
            os.system(drive_query)
        except Exception as e:
            pass

        self.create_log_msg("Database backed up")

    # ----- SPOTTING OPERATIONS -----

    # Update spots tables
    @classmethod
    async def db_count_spot(cls, _id: int, monster_type: str, monster_name: str) -> None:
        cls.conn = cls.engine.connect()
        cls.db_count_spot_table(spots, _id, monster_type, monster_name, False)
        cls.db_count_spot_table(spots_temp, _id, monster_type, monster_name, True)
        cls.conn.close()

    @classmethod
    def db_count_spot_table(cls, table, _id: int, monster_type: str, monster_name: str,
                            temp_table: bool = True) -> None:
        cls.conn = cls.engine.connect()
        stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
                      table.c.event1,
                      table.c.event2).where(
            table.c.member_id == _id)
        result = cls.conn.execute(stmt)
        cls.conn.close()
        counter = 0
        for nr_of_kills in result.columns(monster_type, 'legendary'):
            counter = nr_of_kills[0]
        if monster_type == "event1":
            values = cls.db_count_spot_table_event(table, _id, monster_type, counter)
        else:
            values = {f"{monster_type}": counter + 1}
        stmt = update(table).where(table.c.member_id == _id).values(values)
        cls.conn = cls.engine.connect()
        cls.conn.execute(stmt)
        cls.conn.close()
        if not temp_table:
            cls.db_count_monster_spot(_id, monster_type, monster_name)

    @classmethod
    def db_count_monster_spot(cls, _id: int, monster_type: str, monster_name: str) -> None:
        bot_id = 881167775635234877
        if monster_type == "legendary":
            values_lege_member = cls.db_count_spot_table_monster(spots_lege, _id, monster_name)
            stmt = update(spots_lege).where(spots_lege.c.member_id == _id).values(values_lege_member)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()
            values_lege_total = cls.db_count_spot_table_monster(spots_lege, bot_id, monster_name)
            stmt = update(spots_lege).where(spots_lege.c.member_id == bot_id).values(values_lege_total)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()
        elif monster_type == "rare":
            values_rare_member = cls.db_count_spot_table_monster(spots_rare, _id, monster_name)
            stmt = update(spots_rare).where(spots_rare.c.member_id == _id).values(values_rare_member)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()
            values_rare_total = cls.db_count_spot_table_monster(spots_rare, bot_id, monster_name)
            stmt = update(spots_rare).where(spots_rare.c.member_id == bot_id).values(values_rare_total)
            cls.conn = cls.engine.connect()
            cls.conn.execute(stmt)
            cls.conn.close()

    @classmethod
    def db_count_spot_table_event(cls, table, _id, monster_type: str, counter: int) -> dict:
        cls.conn = cls.engine.connect()
        stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
                      table.c.event1,
                      table.c.event2).where(
            table.c.member_id == _id)
        result = cls.conn.execute(stmt)
        counter_leg = 0
        for nr_of_kills_leg in result.columns('legendary'):
            counter_leg = nr_of_kills_leg[0]
        values = {f"{monster_type}": counter + 1, "legendary": counter_leg + 1}
        cls.conn.close()
        return values

    @classmethod
    def db_count_spot_table_monster(cls, table, guild_member_id: int, monster_name: str) -> dict:
        cls.conn = cls.engine.connect()
        stmt = select(table).where(table.c.member_id == guild_member_id)
        result = cls.conn.execute(stmt)
        counter = 0
        for nr_of_kills_leg in result.columns(f"{monster_name}"):
            counter = nr_of_kills_leg[0]
        values = {f"{monster_name}": counter + 1}
        cls.conn.close()
        return values

    # Save coords from spotting channels to database
    @classmethod
    async def db_save_coords(cls, coord: str, monster_type: str) -> None:
        cls.conn = cls.engine.connect()
        stmt = insert(coords).values(coords=coord, monster_type=monster_type)
        cls.conn.execute(stmt)
        cls.conn.close()

    # Clear data from spots_temp table(for events etc)
    @classmethod
    async def db_clear_spots_temp_table(cls) -> None:
        cls.conn = cls.engine.connect()
        stmt = delete(spots_temp)
        cls.conn.execute(stmt)
        cls.conn.close()

    # ----- LEADERBOARD OPERATIONS -----

    # Return total spotting stats
    @classmethod
    async def db_get_total_spots_df(cls, member_id: int, leaderboard_type: int) -> pd.DataFrame:
        df = pd.DataFrame
        cls.conn = cls.engine.connect()
        if leaderboard_type == 1:
            stmt = select(spots_lege)
            df = pd.read_sql(stmt, cls.conn)
        elif leaderboard_type == 0:
            stmt = select(spots_rare)
            df = pd.read_sql(stmt, cls.conn)
        df = df.loc[df['member_id'] == member_id]
        cls.conn.close()
        return df

    # Return all members' spots
    @classmethod
    async def db_get_spots_df(cls) -> pd.DataFrame:
        cls.conn = cls.engine.connect()
        stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare,
                      spots.c.common, spots.c.event1, spots.c.event2
                      ).select_from(member
                                    ).join(spots, member.c.id == spots.c.member_id)
        cls.conn.execute(stmt)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df

    @classmethod
    async def db_get_common_sum(cls) -> int:
        cls.conn = cls.engine.connect()
        stmt = select(func.sum(spots.c.common).label("sum"))
        result = cls.conn.execute(stmt)
        sum_common = 0
        for nr_of_kills_leg in result.columns("sum"):
            sum_common = nr_of_kills_leg[0]
        cls.conn.close()
        return sum_common

    @classmethod
    async def db_get_monster_spots_df(cls) -> pd.DataFrame:
        # TODO: Why does tables join not work?
        cls.conn = cls.engine.connect()
        stmt = select(spots_lege)
        cls.conn.execute(stmt)
        df_lege = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        cls.conn = cls.engine.connect()
        stmt = select(spots_rare)
        cls.conn.execute(stmt)
        df_rare = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return pd.merge(df_lege, df_rare, on=["member_id"])

    @classmethod
    async def db_get_member_names(cls) -> pd.DataFrame:
        cls.conn = cls.engine.connect()
        stmt = select(member.c.id.label("member_id"), member.c.display_name)
        cls.conn.execute(stmt)
        df_member_names = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df_member_names

    # ----- WARN OPERATIONS -----

    # Add member's warn to database
    @classmethod
    async def db_add_warn(cls, guild_member_id: int, reason: str) -> None:
        cls.conn = cls.engine.connect()
        stmt = insert(warn).values(member_id=guild_member_id, reason=reason, date=datetime.now())
        cls.conn.execute(stmt)
        cls.conn.close()

    # Get member's warns from database
    @classmethod
    async def db_get_warns(cls, guild_member_id: int) -> tuple:
        cls.conn = cls.engine.connect()
        stmt = select(warn.c.reason, warn.c.date).select_from(member).join(warn, member.c.id == warn.c.member_id).where(
            member.c.id == guild_member_id)
        result = cls.conn.execute(stmt)
        date_warn = []
        counter = 0
        for warns in result.columns("reason", "date"):
            reason_with_date = [warns[1], warns[0]]
            date_warn.append(reason_with_date)
            counter += 1
        warns_list = [': \t'.join(str(elem) for elem in sublist) for sublist in date_warn]

        cls.conn.close()
        return warns_list, counter

    # Remove all member's warns
    @classmethod
    async def db_remove_warns(cls, guild_member: int) -> None:
        cls.conn = cls.engine.connect()
        stmt = delete(warn).where(warn.c.member_id == guild_member)
        cls.conn.execute(stmt)
        cls.conn.close()

    # ----- MEMBER OPERATIONS -----

    # Return member's spots
    @classmethod
    async def db_get_member_stats(cls, guild_member: int) -> pd.DataFrame:
        cls.conn = cls.engine.connect()
        stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare, spots.c.common
                      ).select_from(member).join(spots,
                                                 member.c.id == spots.c.member_id) \
            .where(spots.c.member_id == guild_member)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df

    @classmethod
    async def db_get_member_monsters(cls, guild_member: int) -> pd.DataFrame:
        cls.conn = cls.engine.connect()
        stmt = select(spots_lege).where(spots_lege.c.member_id == guild_member)
        cls.conn.execute(stmt)
        df_lege = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        cls.conn = cls.engine.connect()
        stmt = select(spots_rare).where(spots_rare.c.member_id == guild_member)
        cls.conn.execute(stmt)
        df_rare = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return pd.merge(df_lege, df_rare, on=["member_id"])

    @cog_ext.cog_slash(name="changeMemberSpots", guild_ids=cogbase.GUILD_IDS,
                       description="Change member spotting stats",
                       default_permission=False,
                       permissions=cogbase.PERMISSION_ADMINS)
    async def change_member_spots(self, ctx: SlashContext, user: discord.Member, spot_type: str, number: int) -> None:
        self.conn = self.engine.connect()
        stmt = f"""UPDATE server_database.spots SET {spot_type} = {number} """ \
               f"""WHERE (member_id = {user.id});"""
        self.conn.execute(stmt)
        await ctx.send(f"{user.display_name} spots changed", hidden=True)
        self.conn.close()

    # ----- COORDS OPERATIONS -----

    # Return coords
    @classmethod
    async def db_get_coords(cls) -> pd.DataFrame:
        cls.conn = cls.engine.connect()
        stmt = select(coords.c.id, coords.c.coords, coords.c.monster_type).select_from(coords)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df


def setup(bot: commands.Bot) -> None:
    bot.add_cog(DatabaseCog(bot))


1			import os
2			from datetime import datetime
3
4			import discord
5			import pandas as pd
6			from discord.ext import commands, tasks
7			from discord_slash import cog_ext, SlashContext
8			from modules.get_settings import get_settings
9			from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, \
10			BigInteger, update, select, DateTime, delete
11			from sqlalchemy.dialects.mysql import insert
12			from sqlalchemy.sql import func
13			import cogs.cogbase as cogbase
14			from modules.get_settings import get_settings
15
16			metadata_obj = MetaData()
17
18			member = Table('member', metadata_obj,
19			Column('id', BigInteger, primary_key=True),
20			Column('name', String(50), nullable=False),
21			Column('display_name', String(50), nullable=False)
22			)
23			fk_member_id = "member.id"
24
25			warn = Table('warn', metadata_obj,
26			Column('id', Integer, primary_key=True),
27			Column('member_id', BigInteger, ForeignKey(fk_member_id)),
28			Column('reason', String(120), nullable=False),
29			Column('date', DateTime, nullable=False),
30			)
31
32			coords = Table('coords', metadata_obj,
33			Column('id', Integer, primary_key=True),
34			Column('coords', String(100), nullable=False),
35			Column('monster_type', String(20), nullable=False)
36			)
37
38			spots = Table('spots', metadata_obj,
39			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
40			Column('legendary', Integer, default=0),
41			Column('rare', Integer, default=0),
42			Column('common', Integer, default=0),
43			Column('event1', Integer, default=0),
44			Column('event2', Integer, default=0)
45			)
46
47			spots_temp = Table('spots_temp', metadata_obj,
48			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
49			Column('legendary', Integer, default=0),
50			Column('rare', Integer, default=0),
51			Column('common', Integer, default=0),
52			Column('event1', Integer, default=0),
53			Column('event2', Integer, default=0)
54			)
55
56			spots_lege = Table('spots_lege', metadata_obj,
57			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
58			Column('AncientLeshen', Integer, default=0),
59			Column('Archgriffin', Integer, default=0),
60			Column('CopperWyvern', Integer, default=0),
61			Column('D\'jinni', Integer, default=0),
62			Column('DungShaelmaar', Integer, default=0),
63			Column('Erynia', Integer, default=0),
64			Column('Frightener', Integer, default=0),
65			Column('GraphiteSlyzard', Integer, default=0),
66			Column('GrimHag', Integer, default=0),
67			Column('Hym', Integer, default=0),
68			Column('IceElemental', Integer, default=0),
69			Column('IceGiant', Integer, default=0),
70			Column('IceTroll', Integer, default=0),
71			Column('Katakan', Integer, default=0),
72			Column('MottledGarkain', Integer, default=0),
73			Column('Penitent', Integer, default=0),
74			Column('PlagueMaiden', Integer, default=0),
75			Column('Sandcrab', Integer, default=0),
76			Column('SilverBasilisk', Integer, default=0),
77			Column('SwampHag', Integer, default=0),
78			Column('TarryChort', Integer, default=0),
79			Column('Thundster', Integer, default=0),
80			Column('Tormented', Integer, default=0),
81			Column('Ulfhedinn', Integer, default=0),
82			Column('UnseenElder', Integer, default=0),
83			Column('WaterDevil', Integer, default=0),
84			Column('WhiteStriga', Integer, default=0)
85			)
86
87			spots_rare = Table('spots_rare', metadata_obj,
88			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
89			Column('Beann\'Shie', Integer, default=0),
90			Column('Beann\'She', Integer, default=0),
91			Column('BlueForktail', Integer, default=0),
92			Column('Bruxa', Integer, default=0),
93			Column('Burier', Integer, default=0),
94			Column('Cockatrice', Integer, default=0),
95			Column('DepthLurker', Integer, default=0),
96			Column('Devourer', Integer, default=0),
97			Column('DrownedDead', Integer, default=0),
98			Column('EndregaCharger', Integer, default=0),
99			Column('EndregaWarrior', Integer, default=0),
100			Column('Farbaut', Integer, default=0),
101			Column('FireElemental', Integer, default=0),
102			Column('GarkainAlpha', Integer, default=0),
103			Column('Gernichora', Integer, default=0),
104			Column('GraySlyzard', Integer, default=0),
105			Column('GreenHarpy', Integer, default=0),
106			Column('Grimnir', Integer, default=0),
107			Column('Grottore', Integer, default=0),
108			Column('Howler', Integer, default=0),
109			Column('IgnisFatuus', Integer, default=0),
110			Column('Katakan', Integer, default=0),
111			Column('KikimoreWarrior', Integer, default=0),
112			Column('Leshen', Integer, default=0),
113			Column('LeshenHound', Integer, default=0),
114			Column('Liho', Integer, default=0),
115			Column('Lycanthrope', Integer, default=0),
116			Column('MagmaTroll', Integer, default=0),
117			Column('NekkerShaman', Integer, default=0),
118			Column('Nightmare', Integer, default=0),
119			Column('NightSuccubus', Integer, default=0),
120			Column('Putrifier', Integer, default=0),
121			Column('RoyalFoglet', Integer, default=0),
122			Column('RoyalNekker', Integer, default=0),
123			Column('RoyalWyvern', Integer, default=0),
124			Column('RussetShaelmaar', Integer, default=0),
125			Column('Scurver', Integer, default=0),
126			Column('Shrieker', Integer, default=0),
127			Column('SpottedAlghoul', Integer, default=0),
128			Column('StoneGolem', Integer, default=0),
129			Column('Stinger', Integer, default=0),
130			Column('Striga', Integer, default=0),
131			Column('SylvanDearg', Integer, default=0),
132			Column('Wailwraith', Integer, default=0),
133			Column('VizimianArchespore', Integer, default=0)
134			Column('Striga', Integer, default=0),
135			Column('SylvanDearg', Integer, default=0),
136			Column('Wailwraith', Integer, default=0)
137			)
138
139
140			class DatabaseCog(cogbase.BaseCog):
141			user = get_settings("DB_U")
142			password = get_settings("DB_P")
143			conn_string = f"mysql+mysqldb://{user}:{password}@localhost/server_database?charset=utf8mb4"
144			engine = create_engine(conn_string, pool_recycle=3600)
145			metadata_obj.create_all(engine)
146			conn = None
147
148			def __init__(self, base):
149			super().__init__(base)
150
151			# Connect to database
152			self.engine = DatabaseCog.engine
153			metadata_obj.create_all(self.engine)
154			self.db_update_loop.start()
155			self.conn = DatabaseCog.conn
156
157			def cog_unload(self) -> None:
158			self.db_update_loop.cancel()
159
160			# ----- BASE DATABASE OPERATIONS -----
161
162			# Add or update member in member table
163			def db_add_update_member(self, guild_member) -> None:
164			self.conn = self.engine.connect()
165			stmt = insert(member).values(
166			id=guild_member.id, name=guild_member.name,
167			display_name=guild_member.display_name)
168			do_update_stmt = stmt.on_duplicate_key_update(
169			name=stmt.inserted.name, display_name=stmt.inserted.display_name
170			)
171			self.conn.execute(do_update_stmt)
172			self.conn.close()
173
174			# Add or update spots in spots table
175			def db_add_update_spots(self, spots_table, guild_member) -> None:
176			self.conn = self.engine.connect()
177			stmt = insert(spots_table).values(
178			member_id=guild_member.id)
179			do_update_stmt = stmt.on_duplicate_key_update(member_id=stmt.inserted.member_id)
180			self.conn.execute(do_update_stmt)
181			self.conn.close()
182
183			def add_update_member(self, guild_member):
184			# Member tables
185			self.db_add_update_member(guild_member)
186			# Spots tables
187			self.db_add_update_spots(spots, guild_member)
188			self.db_add_update_spots(spots_temp, guild_member)
189			self.db_add_update_spots(spots_lege, guild_member)
190			self.db_add_update_spots(spots_rare, guild_member)
191
192			# Add or refresh all guild members and spots to database
193			async def db_update(self) -> None:
194			self.conn = self.engine.connect()
195			guild = self.bot.get_guild(self.bot.guild[0])
196			self.create_log_msg("Refreshing member and spots tables")
197			for guild_member in guild.members:
198			self.add_update_member(guild_member)
199			self.create_log_msg("Member and spots tables refreshed")
200			self.conn.close()
201
202			@tasks.loop(hours=12)
203			async def db_update_loop(self) -> None:
204			await self.db_update()
205			await self.db_backup_database()
206
207			@db_update_loop.before_loop
208			async def before_db_update_loop(self) -> None:
209			self.create_log_msg("Waiting until Bot is ready")
210			await self.bot.wait_until_ready()
211
212			# Add member to database on member join
213			@commands.Cog.listener()
214			async def on_member_join(self, guild_member) -> None:
215			self.add_update_member(guild_member)
216
217			# Backup database
218			async def db_backup_database(self) -> None:
219			now = datetime.now()
220			backup_name = f"backup-{now.strftime('%m-%d-%Y')}"
221			# Save backup to file
222			backup_query = f"mysqldump -u {get_settings('DB_U')} " \
223			f"--result-file=database_backup/{backup_name}.sql " \
224			f"-p{get_settings('DB_P')} server_database"
225			os.system(backup_query)
226
227			zip_query = f"zip database_backup/{backup_name}.zip database_backup/{backup_name}.sql"
228			os.system(zip_query)
229
230			# Delete backup file
231			rm_query = f"rm database_backup/{backup_name}.sql"
232			os.system(rm_query)
233
234			# # Send backup trough e-mail
235			# mail_to = ""
236			# mail_query = f"mail -a database_backup/{backup_name}.zip -s \"Backup {backup_name}\" {mail_to} <<< \" \""
237			# os.system(mail_query)
238
239			# Send backup to google drive
240			try:
241			drive_query = f"gdrive upload -p 1nXYNibvd4u-nWfj1tvIm7sz3Udh1ZD1k database_backup/{backup_name}.zip"
242			os.system(drive_query)
243			except Exception as e:
244			pass
245
246			self.create_log_msg("Database backed up")
247
248			# ----- SPOTTING OPERATIONS -----
249
250			# Update spots tables
251			@classmethod
252			async def db_count_spot(cls, _id: int, monster_type: str, monster_name: str) -> None:
253			cls.conn = cls.engine.connect()
254			cls.db_count_spot_table(spots, _id, monster_type, monster_name, False)
255			cls.db_count_spot_table(spots_temp, _id, monster_type, monster_name, True)
256			cls.conn.close()
257
258			@classmethod
259			def db_count_spot_table(cls, table, _id: int, monster_type: str, monster_name: str,
260			temp_table: bool = True) -> None:
261			cls.conn = cls.engine.connect()
262			stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
263			table.c.event1,
264			table.c.event2).where(
265			table.c.member_id == _id)
266			result = cls.conn.execute(stmt)
267			cls.conn.close()
268			counter = 0
269			for nr_of_kills in result.columns(monster_type, 'legendary'):
270			counter = nr_of_kills[0]
271			if monster_type == "event1":
272			values = cls.db_count_spot_table_event(table, _id, monster_type, counter)
273			else:
274			values = {f"{monster_type}": counter + 1}
275			stmt = update(table).where(table.c.member_id == _id).values(values)
276			cls.conn = cls.engine.connect()
277			cls.conn.execute(stmt)
278			cls.conn.close()
279			if not temp_table:
280			cls.db_count_monster_spot(_id, monster_type, monster_name)
281
282			@classmethod
283			def db_count_monster_spot(cls, _id: int, monster_type: str, monster_name: str) -> None:
284			bot_id = 881167775635234877
285			if monster_type == "legendary":
286			values_lege_member = cls.db_count_spot_table_monster(spots_lege, _id, monster_name)
287			stmt = update(spots_lege).where(spots_lege.c.member_id == _id).values(values_lege_member)
288			cls.conn = cls.engine.connect()
289			cls.conn.execute(stmt)
290			cls.conn.close()
291			values_lege_total = cls.db_count_spot_table_monster(spots_lege, bot_id, monster_name)
292			stmt = update(spots_lege).where(spots_lege.c.member_id == bot_id).values(values_lege_total)
293			cls.conn = cls.engine.connect()
294			cls.conn.execute(stmt)
295			cls.conn.close()
296			elif monster_type == "rare":
297			values_rare_member = cls.db_count_spot_table_monster(spots_rare, _id, monster_name)
298			stmt = update(spots_rare).where(spots_rare.c.member_id == _id).values(values_rare_member)
299			cls.conn = cls.engine.connect()
300			cls.conn.execute(stmt)
301			cls.conn.close()
302			values_rare_total = cls.db_count_spot_table_monster(spots_rare, bot_id, monster_name)
303			stmt = update(spots_rare).where(spots_rare.c.member_id == bot_id).values(values_rare_total)
304			cls.conn = cls.engine.connect()
305			cls.conn.execute(stmt)
306			cls.conn.close()
307
308			@classmethod
309			def db_count_spot_table_event(cls, table, _id, monster_type: str, counter: int) -> dict:
310			cls.conn = cls.engine.connect()
311			stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
312			table.c.event1,
313			table.c.event2).where(
314			table.c.member_id == _id)
315			result = cls.conn.execute(stmt)
316			counter_leg = 0
317			for nr_of_kills_leg in result.columns('legendary'):
318			counter_leg = nr_of_kills_leg[0]
319			values = {f"{monster_type}": counter + 1, "legendary": counter_leg + 1}
320			cls.conn.close()
321			return values
322
323			@classmethod
324			def db_count_spot_table_monster(cls, table, guild_member_id: int, monster_name: str) -> dict:
325			cls.conn = cls.engine.connect()
326			stmt = select(table).where(table.c.member_id == guild_member_id)
327			result = cls.conn.execute(stmt)
328			counter = 0
329			for nr_of_kills_leg in result.columns(f"{monster_name}"):
330			counter = nr_of_kills_leg[0]
331			values = {f"{monster_name}": counter + 1}
332			cls.conn.close()
333			return values
334
335			# Save coords from spotting channels to database
336			@classmethod
337			async def db_save_coords(cls, coord: str, monster_type: str) -> None:
338			cls.conn = cls.engine.connect()
339			stmt = insert(coords).values(coords=coord, monster_type=monster_type)
340			cls.conn.execute(stmt)
341			cls.conn.close()
342
343			# Clear data from spots_temp table(for events etc)
344			@classmethod
345			async def db_clear_spots_temp_table(cls) -> None:
346			cls.conn = cls.engine.connect()
347			stmt = delete(spots_temp)
348			cls.conn.execute(stmt)
349			cls.conn.close()
350
351			# ----- LEADERBOARD OPERATIONS -----
352
353			# Return total spotting stats
354			@classmethod
355			async def db_get_total_spots_df(cls, member_id: int, leaderboard_type: int) -> pd.DataFrame:
356			df = pd.DataFrame
357			cls.conn = cls.engine.connect()
358			if leaderboard_type == 1:
359			stmt = select(spots_lege)
360			df = pd.read_sql(stmt, cls.conn)
361			elif leaderboard_type == 0:
362			stmt = select(spots_rare)
363			df = pd.read_sql(stmt, cls.conn)
364			df = df.loc[df['member_id'] == member_id]
365			cls.conn.close()
366			return df
367
368			# Return all members' spots
369			@classmethod
370			async def db_get_spots_df(cls) -> pd.DataFrame:
371			cls.conn = cls.engine.connect()
372			stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare,
373			spots.c.common, spots.c.event1, spots.c.event2
374			).select_from(member
375			).join(spots, member.c.id == spots.c.member_id)
376			cls.conn.execute(stmt)
377			df = pd.read_sql(stmt, cls.conn)
378			cls.conn.close()
379			return df
380
381			@classmethod
382			async def db_get_common_sum(cls) -> int:
383			cls.conn = cls.engine.connect()
384			stmt = select(func.sum(spots.c.common).label("sum"))
385			result = cls.conn.execute(stmt)
386			sum_common = 0
387			for nr_of_kills_leg in result.columns("sum"):
388			sum_common = nr_of_kills_leg[0]
389			cls.conn.close()
390			return sum_common
391
392			@classmethod
393			async def db_get_monster_spots_df(cls) -> pd.DataFrame:
394			# TODO: Why does tables join not work?
395			cls.conn = cls.engine.connect()
396			stmt = select(spots_lege)
397			cls.conn.execute(stmt)
398			df_lege = pd.read_sql(stmt, cls.conn)
399			cls.conn.close()
400			cls.conn = cls.engine.connect()
401			stmt = select(spots_rare)
402			cls.conn.execute(stmt)
403			df_rare = pd.read_sql(stmt, cls.conn)
404			cls.conn.close()
405			return pd.merge(df_lege, df_rare, on=["member_id"])
406
407			@classmethod
408			async def db_get_member_names(cls) -> pd.DataFrame:
409			cls.conn = cls.engine.connect()
410			stmt = select(member.c.id.label("member_id"), member.c.display_name)
411			cls.conn.execute(stmt)
412			df_member_names = pd.read_sql(stmt, cls.conn)
413			cls.conn.close()
414			return df_member_names
415
416			# ----- WARN OPERATIONS -----
417
418			# Add member's warn to database
419			@classmethod
420			async def db_add_warn(cls, guild_member_id: int, reason: str) -> None:
421			cls.conn = cls.engine.connect()
422			stmt = insert(warn).values(member_id=guild_member_id, reason=reason, date=datetime.now())
423			cls.conn.execute(stmt)
424			cls.conn.close()
425
426			# Get member's warns from database
427			@classmethod
428			async def db_get_warns(cls, guild_member_id: int) -> tuple:
429			cls.conn = cls.engine.connect()
430			stmt = select(warn.c.reason, warn.c.date).select_from(member).join(warn, member.c.id == warn.c.member_id).where(
431			member.c.id == guild_member_id)
432			result = cls.conn.execute(stmt)
433			date_warn = []
434			counter = 0
435			for warns in result.columns("reason", "date"):
436			reason_with_date = [warns[1], warns[0]]
437			date_warn.append(reason_with_date)
438			counter += 1
439			warns_list = [': \t'.join(str(elem) for elem in sublist) for sublist in date_warn]
440
441			cls.conn.close()
442			return warns_list, counter
443
444			# Remove all member's warns
445			@classmethod
446			async def db_remove_warns(cls, guild_member: int) -> None:
447			cls.conn = cls.engine.connect()
448			stmt = delete(warn).where(warn.c.member_id == guild_member)
449			cls.conn.execute(stmt)
450			cls.conn.close()
451
452			# ----- MEMBER OPERATIONS -----
453
454			# Return member's spots
455			@classmethod
456			async def db_get_member_stats(cls, guild_member: int) -> pd.DataFrame:
457			cls.conn = cls.engine.connect()
458			stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare, spots.c.common
459			).select_from(member).join(spots,
460			member.c.id == spots.c.member_id) \
461			.where(spots.c.member_id == guild_member)
462			df = pd.read_sql(stmt, cls.conn)
463			cls.conn.close()
464			return df
465
466			@classmethod
467			async def db_get_member_monsters(cls, guild_member: int) -> pd.DataFrame:
468			cls.conn = cls.engine.connect()
469			stmt = select(spots_lege).where(spots_lege.c.member_id == guild_member)
470			cls.conn.execute(stmt)
471			df_lege = pd.read_sql(stmt, cls.conn)
472			cls.conn.close()
473			cls.conn = cls.engine.connect()
474			stmt = select(spots_rare).where(spots_rare.c.member_id == guild_member)
475			cls.conn.execute(stmt)
476			df_rare = pd.read_sql(stmt, cls.conn)
477			cls.conn.close()
478			return pd.merge(df_lege, df_rare, on=["member_id"])
479
480			@cog_ext.cog_slash(name="changeMemberSpots", guild_ids=cogbase.GUILD_IDS,
481			description="Change member spotting stats",
482			default_permission=False,
483			permissions=cogbase.PERMISSION_ADMINS)
484			async def change_member_spots(self, ctx: SlashContext, user: discord.Member, spot_type: str, number: int) -> None:
485			self.conn = self.engine.connect()
486			stmt = f"""UPDATE server_database.spots SET {spot_type} = {number} """ \
487			f"""WHERE (member_id = {user.id});"""
488			self.conn.execute(stmt)
489			await ctx.send(f"{user.display_name} spots changed", hidden=True)
490			self.conn.close()
491
492			# ----- COORDS OPERATIONS -----
493
494			# Return coords
495			@classmethod
496			async def db_get_coords(cls) -> pd.DataFrame:
497			cls.conn = cls.engine.connect()
498			stmt = select(coords.c.id, coords.c.coords, coords.c.monster_type).select_from(coords)
499			df = pd.read_sql(stmt, cls.conn)
500			cls.conn.close()
501			return df
502
503
504			def setup(bot: commands.Bot) -> None:
505			bot.add_cog(DatabaseCog(bot))
506

BonHowi / discordPogmareBot

Push — main ( d8bd61...e8362f )

build.cogs.databasecog.DatabaseCog.db_get_warns() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like