build.cogs.databasecog.DatabaseCog.on_member_join() - Code Metrics - Inspection of "cancel loop on cog unload" - BonHowi/discordPogmareBot - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — BonHowi (83c15f)

by Bartosz

created 2021-09-15 23:32 UTC

DatabaseCog.on_member_join() A

↳ Parent: build.cogs.databasecog

Complexity

Conditions

Size

Total Lines	5
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	5
nop	2
dl	0
loc	5
rs	10
c	0
b	0
f	0

import os

from discord.ext import commands, tasks
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
import cogs.cogbase as cogbase
from datetime import datetime
import pandas as pd

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"

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)
                   )

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)
               )


# noinspection PyPropertyAccess
class DatabaseCog(cogbase.BaseCog):
    user = get_settings("DB_U")
    password = get_settings("DB_P")
    engine = create_engine(f"mysql+mysqldb://{user}:{password}@localhost/server_database?charset=utf8mb4")
    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):
        self.db_update_loop.cancel()

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

    # Add or update member in member table
    def db_add_update_member(self, _member):
        self.conn = self.engine.connect()
        stmt = insert(member).values(
            id=_member.id, name=_member.name,
            display_name=_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):
        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()

    # Add or refresh all guild members and spots to database
    async def db_update(self):
        self.conn = self.engine.connect()
        guild = self.bot.get_guild(self.bot.guild[0])
        dt_string = self.bot.get_current_time()
        print(f"({dt_string})\t[{self.__class__.__name__}]: Refreshing member and spots tables")
        for guild_member in guild.members:
            # 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)
        dt_string = self.bot.get_current_time()
        print(f"({dt_string})\t[{self.__class__.__name__}]: Member and spots tables refreshed")
        self.conn.close()

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

    @db_update_loop.before_loop
    async def before_db_update_loop(self):
        dt_string = self.bot.get_current_time()
        print(f'({dt_string})\t[{self.__class__.__name__}]: 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, _member):
        self.db_add_update_member(_member)
        self.db_add_update_spots(spots, _member)
        self.db_add_update_spots(spots_temp, _member)

    # Backup database
    async def db_backup_database(self):
        now = datetime.now()
        cmd = f"mysqldump -u {get_settings('DB_U')} " \
              f"--result-file=database_backup/backup-{now.strftime('%m-%d-%Y')}.sql " \
              f"-p{get_settings('DB_P')} server_database"
        os.system(cmd)
        dt_string = self.bot.get_current_time()
        print(f'({dt_string})\t[{self.__class__.__name__}]: Database backed up')

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

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

    @classmethod
    def db_count_spot_table(cls, table, _id: int, monster_type: str):
        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 = 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.execute(stmt)

    @classmethod
    def db_count_spot_table_event(cls, table, _id, monster_type: str, counter: int):
        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}
        return values

    # Save coords from spotting channels to database
    @classmethod
    async def db_save_coords(cls, _coords: str, _monster_type):
        cls.conn = cls.engine.connect()
        stmt = insert(coords).values(coords=_coords, 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):
        cls.conn = cls.engine.connect()
        stmt = delete(spots_temp)
        cls.conn.execute(stmt)
        cls.conn.close()

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

    # Return all members' spots
    @classmethod
    async def db_get_spots_df(cls):
        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

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

    # Add member's warn to database
    @classmethod
    async def db_add_warn(cls, _member: int, _reason: str):
        cls.conn = cls.engine.connect()
        stmt = insert(warn).values(member_id=_member, 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, _member: int):
        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 == _member)
        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, _member: int):
        cls.conn = cls.engine.connect()
        stmt = delete(warn).where(warn.c.member_id == _member)
        cls.conn.execute(stmt)
        cls.conn.close()

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

    # Return member's spots
    @classmethod
    async def db_get_member_stats(cls, _member: int):
        cls.conn = cls.engine.connect()
        stmt = select(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 == _member)
        df = pd.read_sql(stmt, cls.conn)
        cls.conn.close()
        return df

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

    # Return coords
    @classmethod
    async def db_get_coords(cls):
        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):
    bot.add_cog(DatabaseCog(bot))


1			import os
2
3			from discord.ext import commands, tasks
4			from modules.get_settings import get_settings
5			from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, \
6			BigInteger, update, select, DateTime, delete
7			from sqlalchemy.dialects.mysql import insert
8			import cogs.cogbase as cogbase
9			from datetime import datetime
10			import pandas as pd
11
12			metadata_obj = MetaData()
13			member = Table('member', metadata_obj,
14			Column('id', BigInteger, primary_key=True),
15			Column('name', String(50), nullable=False),
16			Column('display_name', String(50), nullable=False)
17			)
18			fk_member_id = "member.id"
19
20			spots = Table('spots', metadata_obj,
21			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
22			Column('legendary', Integer, default=0),
23			Column('rare', Integer, default=0),
24			Column('common', Integer, default=0),
25			Column('event1', Integer, default=0),
26			Column('event2', Integer, default=0)
27			)
28
29			spots_temp = Table('spots_temp', metadata_obj,
30			Column('member_id', BigInteger, ForeignKey(fk_member_id), primary_key=True),
31			Column('legendary', Integer, default=0),
32			Column('rare', Integer, default=0),
33			Column('common', Integer, default=0),
34			Column('event1', Integer, default=0),
35			Column('event2', Integer, default=0)
36			)
37
38			warn = Table('warn', metadata_obj,
39			Column('id', Integer, primary_key=True),
40			Column('member_id', BigInteger, ForeignKey(fk_member_id)),
41			Column('reason', String(120), nullable=False),
42			Column('date', DateTime, nullable=False),
43			)
44
45			coords = Table('coords', metadata_obj,
46			Column('id', Integer, primary_key=True),
47			Column('coords', String(100), nullable=False),
48			Column('monster_type', String(20), nullable=False)
49			)
50
51
52			# noinspection PyPropertyAccess
53			class DatabaseCog(cogbase.BaseCog):
54			user = get_settings("DB_U")
55			password = get_settings("DB_P")
56			engine = create_engine(f"mysql+mysqldb://{user}:{password}@localhost/server_database?charset=utf8mb4")
57			metadata_obj.create_all(engine)
58			conn = None
59
60			def __init__(self, base):
61			super().__init__(base)
62
63			# Connect to database
64			self.engine = DatabaseCog.engine
65			metadata_obj.create_all(self.engine)
66			self.db_update_loop.start()
67			self.conn = DatabaseCog.conn
68
69			def cog_unload(self):
70			self.db_update_loop.cancel()
71
72			# ----- BASE DATABASE OPERATIONS -----
73
74			# Add or update member in member table
75			def db_add_update_member(self, _member):
76			self.conn = self.engine.connect()
77			stmt = insert(member).values(
78			id=_member.id, name=_member.name,
79			display_name=_member.display_name)
80			do_update_stmt = stmt.on_duplicate_key_update(
81			name=stmt.inserted.name, display_name=stmt.inserted.display_name
82			)
83			self.conn.execute(do_update_stmt)
84			self.conn.close()
85
86			# Add or update spots in spots table
87			def db_add_update_spots(self, spots_table, guild_member):
88			self.conn = self.engine.connect()
89			stmt = insert(spots_table).values(
90			member_id=guild_member.id)
91			do_update_stmt = stmt.on_duplicate_key_update(member_id=stmt.inserted.member_id)
92			self.conn.execute(do_update_stmt)
93			self.conn.close()
94
95			# Add or refresh all guild members and spots to database
96			async def db_update(self):
97			self.conn = self.engine.connect()
98			guild = self.bot.get_guild(self.bot.guild[0])
99			dt_string = self.bot.get_current_time()
100			print(f"({dt_string})\t[{self.__class__.__name__}]: Refreshing member and spots tables")
101			for guild_member in guild.members:
102			# Member tables
103			self.db_add_update_member(guild_member)
104			# Spots tables
105			self.db_add_update_spots(spots, guild_member)
106			self.db_add_update_spots(spots_temp, guild_member)
107			dt_string = self.bot.get_current_time()
108			print(f"({dt_string})\t[{self.__class__.__name__}]: Member and spots tables refreshed")
109			self.conn.close()
110
111			@tasks.loop(hours=12)
112			async def db_update_loop(self):
113			await self.db_update()
114			await self.db_backup_database()
115
116			@db_update_loop.before_loop
117			async def before_db_update_loop(self):
118			dt_string = self.bot.get_current_time()
119			print(f'({dt_string})\t[{self.__class__.__name__}]: Waiting until Bot is ready')
120			await self.bot.wait_until_ready()
121
122			# Add member to database on member join
123			@commands.Cog.listener()
124			async def on_member_join(self, _member):
125			self.db_add_update_member(_member)
126			self.db_add_update_spots(spots, _member)
127			self.db_add_update_spots(spots_temp, _member)
128
129			# Backup database
130			async def db_backup_database(self):
131			now = datetime.now()
132			cmd = f"mysqldump -u {get_settings('DB_U')} " \
133			f"--result-file=database_backup/backup-{now.strftime('%m-%d-%Y')}.sql " \
134			f"-p{get_settings('DB_P')} server_database"
135			os.system(cmd)
136			dt_string = self.bot.get_current_time()
137			print(f'({dt_string})\t[{self.__class__.__name__}]: Database backed up')
138
139			# ----- SPOTTING OPERATIONS -----
140
141			# Update spots tables
142			@classmethod
143			async def db_count_spot(cls, _id: int, monster_type: str):
144			cls.conn = cls.engine.connect()
145			cls.db_count_spot_table(spots, _id, monster_type)
146			cls.db_count_spot_table(spots_temp, _id, monster_type)
147			cls.conn.close()
148
149			@classmethod
150			def db_count_spot_table(cls, table, _id: int, monster_type: str):
151			stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
152			table.c.event1,
153			table.c.event2).where(
154			table.c.member_id == _id)
155			result = cls.conn.execute(stmt)
156			counter = 0
157			for nr_of_kills in result.columns(monster_type, 'legendary'):
158			counter = nr_of_kills[0]
159			if monster_type == "event1":
160			values = cls.db_count_spot_table_event(table, _id, monster_type, counter)
161			else:
162			values = {f"{monster_type}": counter + 1}
163			stmt = update(table).where(table.c.member_id == _id).values(values)
164			cls.conn.execute(stmt)
165
166			@classmethod
167			def db_count_spot_table_event(cls, table, _id, monster_type: str, counter: int):
168			stmt = select(table.c.member_id, table.c.legendary, table.c.rare, table.c.common,
169			table.c.event1,
170			table.c.event2).where(
171			table.c.member_id == _id)
172			result = cls.conn.execute(stmt)
173			counter_leg = 0
174			for nr_of_kills_leg in result.columns('legendary'):
175			counter_leg = nr_of_kills_leg[0]
176			values = {f"{monster_type}": counter + 1, "legendary": counter_leg + 1}
177			return values
178
179			# Save coords from spotting channels to database
180			@classmethod
181			async def db_save_coords(cls, _coords: str, _monster_type):
182			cls.conn = cls.engine.connect()
183			stmt = insert(coords).values(coords=_coords, monster_type=_monster_type)
184			cls.conn.execute(stmt)
185			cls.conn.close()
186
187			# Clear data from spots_temp table(for events etc)
188			@classmethod
189			async def db_clear_spots_temp_table(cls):
190			cls.conn = cls.engine.connect()
191			stmt = delete(spots_temp)
192			cls.conn.execute(stmt)
193			cls.conn.close()
194
195			# ----- LEADERBOARD OPERATIONS -----
196
197			# Return all members' spots
198			@classmethod
199			async def db_get_spots_df(cls):
200			cls.conn = cls.engine.connect()
201			stmt = select(spots.c.member_id, member.c.display_name, spots.c.legendary, spots.c.rare,
202			spots.c.common, spots.c.event1, spots.c.event2
203			).select_from(member
204			).join(spots, member.c.id == spots.c.member_id)
205			cls.conn.execute(stmt)
206			df = pd.read_sql(stmt, cls.conn)
207			cls.conn.close()
208			return df
209
210			# ----- WARN OPERATIONS -----
211
212			# Add member's warn to database
213			@classmethod
214			async def db_add_warn(cls, _member: int, _reason: str):
215			cls.conn = cls.engine.connect()
216			stmt = insert(warn).values(member_id=_member, reason=_reason, date=datetime.now())
217			cls.conn.execute(stmt)
218			cls.conn.close()
219
220			# Get member's warns from database
221			@classmethod
222			async def db_get_warns(cls, _member: int):
223			cls.conn = cls.engine.connect()
224			stmt = select(warn.c.reason, warn.c.date).select_from(member).join(warn, member.c.id == warn.c.member_id).where(
225			member.c.id == _member)
226			result = cls.conn.execute(stmt)
227			date_warn = []
228			counter = 0
229			for warns in result.columns("reason", "date"):
230			reason_with_date = [warns[1], warns[0]]
231			date_warn.append(reason_with_date)
232			counter += 1
233			warns_list = [': \t'.join([str(elem) for elem in sublist]) for sublist in date_warn]
234			cls.conn.close()
235			return warns_list, counter
236
237			# Remove all member's warns
238			@classmethod
239			async def db_remove_warns(cls, _member: int):
240			cls.conn = cls.engine.connect()
241			stmt = delete(warn).where(warn.c.member_id == _member)
242			cls.conn.execute(stmt)
243			cls.conn.close()
244
245			# ----- MEMBER OPERATIONS -----
246
247			# Return member's spots
248			@classmethod
249			async def db_get_member_stats(cls, _member: int):
250			cls.conn = cls.engine.connect()
251			stmt = select(member.c.display_name, spots.c.legendary, spots.c.rare, spots.c.common).select_from(member).join(
252			spots,
253			member.c.id == spots.c.member_id).where(spots.c.member_id == _member)
254			df = pd.read_sql(stmt, cls.conn)
255			cls.conn.close()
256			return df
257
258			# ----- COORDS OPERATIONS -----
259
260			# Return coords
261			@classmethod
262			async def db_get_coords(cls):
263			cls.conn = cls.engine.connect()
264			stmt = select(coords.c.id, coords.c.coords, coords.c.monster_type).select_from(coords)
265			df = pd.read_sql(stmt, cls.conn)
266			cls.conn.close()
267			return df
268
269
270			def setup(bot: commands.Bot):
271			bot.add_cog(DatabaseCog(bot))
272

BonHowi / discordPogmareBot

Branch — BonHowi (83c15f)

DatabaseCog.on_member_join() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like