|
1
|
|
|
import inspect |
|
2
|
|
|
from discord.ext import commands, tasks |
|
3
|
|
|
from modules.get_settings import get_settings |
|
4
|
|
|
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Date, BigInteger |
|
5
|
|
|
from sqlalchemy import create_engine, UniqueConstraint |
|
6
|
|
|
from sqlalchemy.dialects.mysql import insert |
|
7
|
|
|
import cogs.cogbase as cogbase |
|
8
|
|
|
|
|
9
|
|
|
metadata_obj = MetaData() |
|
10
|
|
|
member = Table('member', metadata_obj, |
|
11
|
|
|
Column('id', BigInteger, primary_key=True), |
|
12
|
|
|
Column('name', String(50), nullable=False), |
|
13
|
|
|
Column('display_name', String(50), nullable=False) |
|
14
|
|
|
) |
|
15
|
|
|
|
|
16
|
|
|
spots = Table('spots', metadata_obj, |
|
17
|
|
|
Column('member_id', BigInteger, ForeignKey("member.id"), primary_key=True), |
|
18
|
|
|
Column('legendary', Integer), |
|
19
|
|
|
Column('rare', Integer), |
|
20
|
|
|
Column('common', Integer) |
|
21
|
|
|
) |
|
22
|
|
|
|
|
23
|
|
|
spots_temp = Table('spots_temp', metadata_obj, |
|
24
|
|
|
Column('member_id', BigInteger, ForeignKey("member.id"), primary_key=True), |
|
25
|
|
|
Column('legendary', Integer), |
|
26
|
|
|
Column('rare', Integer), |
|
27
|
|
|
Column('common', Integer) |
|
28
|
|
|
) |
|
29
|
|
|
|
|
30
|
|
|
warn = Table('warn', metadata_obj, |
|
31
|
|
|
Column('id', Integer, primary_key=True), |
|
32
|
|
|
Column('member_id', BigInteger, ForeignKey("member.id")), |
|
33
|
|
|
Column('warn', String(50), nullable=False), |
|
34
|
|
|
Column('Date', Date, nullable=False), |
|
35
|
|
|
UniqueConstraint("member_id") |
|
36
|
|
|
) |
|
37
|
|
|
|
|
38
|
|
|
coords = Table('coords', metadata_obj, |
|
39
|
|
|
Column('id', Integer, primary_key=True), |
|
40
|
|
|
Column('coords', String(50), nullable=False), |
|
41
|
|
|
) |
|
42
|
|
|
|
|
43
|
|
|
|
|
44
|
|
|
class DatabaseCog(cogbase.BaseCog): |
|
45
|
|
|
def __init__(self, base): |
|
46
|
|
|
super().__init__(base) |
|
47
|
|
|
|
|
48
|
|
|
# Connect to database |
|
49
|
|
|
password = get_settings("DB_PASSWORD") |
|
50
|
|
|
self.engine = create_engine(f"mysql+mysqldb://BonHowi:{password}@localhost/server_database") |
|
51
|
|
|
metadata_obj.create_all(self.engine) |
|
52
|
|
|
self.conn = self.engine.connect() |
|
53
|
|
|
self.db_update_loop.start() |
|
54
|
|
|
|
|
55
|
|
|
# Add or refresh all guild members to database |
|
56
|
|
|
async def db_update(self): |
|
57
|
|
|
print(f"[{self.__class__.__name__}]:\t" |
|
58
|
|
|
f"{inspect.stack()[0][3]}: Refreshing member count") |
|
59
|
|
|
guild = self.bot.get_guild(self.bot.guild[0]) |
|
60
|
|
|
for guild_member in guild.members: |
|
61
|
|
|
stmt = insert(member).values( |
|
62
|
|
|
id=guild_member.id, name=guild_member.name, |
|
63
|
|
|
display_name=guild_member.display_name) |
|
64
|
|
|
do_update_stmt = stmt.on_duplicate_key_update( |
|
65
|
|
|
name=stmt.inserted.name, display_name=stmt.inserted.display_name |
|
66
|
|
|
) |
|
67
|
|
|
self.conn.execute(do_update_stmt) |
|
68
|
|
|
|
|
69
|
|
|
@tasks.loop(hours=12) |
|
70
|
|
|
async def db_update_loop(self): |
|
71
|
|
|
await self.db_update() |
|
72
|
|
|
|
|
73
|
|
|
@db_update_loop.before_loop |
|
74
|
|
|
async def before_db_update(self): |
|
75
|
|
|
print(f'[{self.__class__.__name__}]: Waiting until Bot is ready') |
|
76
|
|
|
await self.bot.wait_until_ready() |
|
77
|
|
|
|
|
78
|
|
|
@db_update_loop.after_loop |
|
79
|
|
|
async def on_db_update_cancel(self): |
|
80
|
|
|
print(f"[{self.__class__.__name__}]: Placeholder for future improvements") |
|
81
|
|
|
|
|
82
|
|
|
# def create_connection(self): |
|
83
|
|
|
# """ create a database connection to a SQLite database """ |
|
84
|
|
|
# conn = None |
|
85
|
|
|
# try: |
|
86
|
|
|
# conn = sqlite3.connect(self.database) |
|
87
|
|
|
# # print(sqlite3.version) |
|
88
|
|
|
# except Error as e: |
|
89
|
|
|
# print(e) |
|
90
|
|
|
# finally: |
|
91
|
|
|
# return conn |
|
92
|
|
|
# |
|
93
|
|
|
# # no idea how to actually use it |
|
94
|
|
|
# def close_connection(self): |
|
95
|
|
|
# if self.connection: |
|
96
|
|
|
# self.connection.close() |
|
97
|
|
|
# print("[INFO]: Database connection closed") |
|
98
|
|
|
# |
|
99
|
|
|
# @staticmethod |
|
100
|
|
|
# def create_table(conn, table_name: str, **kwargs): |
|
101
|
|
|
# """ create a table from the create_table_sql statement |
|
102
|
|
|
# :param table_name: |
|
103
|
|
|
# :type table_name: |
|
104
|
|
|
# :param conn: Connection object |
|
105
|
|
|
# :return: |
|
106
|
|
|
# """ |
|
107
|
|
|
# create_table_sql = f"""CREATE TABLE IF NOT EXISTS {table_name} (\n""" |
|
108
|
|
|
# |
|
109
|
|
|
# try: |
|
110
|
|
|
# c = conn.cursor() |
|
111
|
|
|
# c.execute(create_table_sql) |
|
112
|
|
|
# except Error as e: |
|
113
|
|
|
# print(e) |
|
114
|
|
|
# |
|
115
|
|
|
# @staticmethod |
|
116
|
|
|
# def create_project(conn, project): |
|
117
|
|
|
# """ |
|
118
|
|
|
# Create a new project into the projects table |
|
119
|
|
|
# :param conn: |
|
120
|
|
|
# :param project: |
|
121
|
|
|
# :return: project id |
|
122
|
|
|
# """ |
|
123
|
|
|
# sql = ''' INSERT INTO projects(name,begin_date,end_date) |
|
124
|
|
|
# VALUES(?,?,?) ''' |
|
125
|
|
|
# cur = conn.cursor() |
|
126
|
|
|
# cur.execute(sql, project) |
|
127
|
|
|
# conn.commit() |
|
128
|
|
|
# return cur.lastrowid |
|
129
|
|
|
# |
|
130
|
|
|
# @staticmethod |
|
131
|
|
|
# def update_task(conn, task): |
|
132
|
|
|
# """ |
|
133
|
|
|
# update priority, begin_date, and end date of a task |
|
134
|
|
|
# :param conn: |
|
135
|
|
|
# :param task: |
|
136
|
|
|
# :return: project id |
|
137
|
|
|
# """ |
|
138
|
|
|
# sql = ''' UPDATE tasks |
|
139
|
|
|
# SET priority = ? , |
|
140
|
|
|
# begin_date = ? , |
|
141
|
|
|
# end_date = ? |
|
142
|
|
|
# WHERE id = ?''' |
|
143
|
|
|
# cur = conn.cursor() |
|
144
|
|
|
# cur.execute(sql, task) |
|
145
|
|
|
# conn.commit() |
|
146
|
|
|
# |
|
147
|
|
|
# @staticmethod |
|
148
|
|
|
# def select_all_tasks(conn): |
|
149
|
|
|
# """ |
|
150
|
|
|
# Query all rows in the tasks table |
|
151
|
|
|
# :param conn: the Connection object |
|
152
|
|
|
# :return: |
|
153
|
|
|
# """ |
|
154
|
|
|
# cur = conn.cursor() |
|
155
|
|
|
# cur.execute("SELECT * FROM tasks") |
|
156
|
|
|
# |
|
157
|
|
|
# rows = cur.fetchall() |
|
158
|
|
|
# |
|
159
|
|
|
# for row in rows: |
|
160
|
|
|
# print(row) |
|
161
|
|
|
# |
|
162
|
|
|
# @staticmethod |
|
163
|
|
|
# def delete_task(conn, where): |
|
164
|
|
|
# """ |
|
165
|
|
|
# Delete a task by task id |
|
166
|
|
|
# :param where: |
|
167
|
|
|
# :param where: id of the task |
|
168
|
|
|
# :param conn: Connection to the SQLite database |
|
169
|
|
|
# :return: |
|
170
|
|
|
# """ |
|
171
|
|
|
# sql = 'DELETE FROM tasks WHERE id=?' |
|
172
|
|
|
# cur = conn.cursor() |
|
173
|
|
|
# cur.execute(sql, (where,)) |
|
174
|
|
|
# conn.commit() |
|
175
|
|
|
|
|
176
|
|
|
|
|
177
|
|
|
def setup(bot: commands.Bot): |
|
178
|
|
|
bot.add_cog(DatabaseCog(bot)) |
|
179
|
|
|
|
BonHowi /
discordPogmareBot
