build.cogs.databasecog.DatabaseCog.db_update() - Code Metrics - Inspection of "Merge pull request #14 from BonHowi/BonHowi" - BonHowi/discordPogmareBot - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 15729a...f64b1b )

by Bartosz

created 2021-09-17 03:38 UTC

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

↳ Parent: build.cogs.databasecog

Complexity

Conditions

Size

Total Lines	14
Code Lines	12

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	12
dl	0
loc	14
rs	9.8
c	0
b	0
f	0
cc	2
nop	1

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


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

BonHowi / discordPogmareBot

Push — main ( 15729a...f64b1b )

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

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like