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