Passed
Push — main ( 47b94a...ca5c80 )
by Bartosz
02:27 queued 57s
created

DatabaseCog.db_count_spot_table()   A

Complexity

Conditions 3

Size

Total Lines 16
Code Lines 15

Duplication

Lines 0
Ratio 0 %

Importance

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