-
Notifications
You must be signed in to change notification settings - Fork 1
/
database.py
305 lines (254 loc) · 8.92 KB
/
database.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
from hashlib import sha256
from typing import Optional, List, Tuple
from uuid import uuid4
import psycopg2
class User:
_user_id: str
_username: str
_email: str
_last_login_ip: Optional[str]
_registration_ip: Optional[str]
_application_id: str
def __init__(self, username: str, email: str, last_login_ip: Optional[str], registration_ip: Optional[str],
application_id: str, user_id: str):
self._username = username
self._email = email
self._last_login_ip = last_login_ip
self._registration_ip = registration_ip
self._application_id = application_id
self._user_id = user_id
@property
def as_dict(self) -> dict:
return {
"username": self._username,
"email": self._email,
"last_login_ip": self._last_login_ip,
"registration_ip": self._registration_ip,
"application_id": self._application_id,
"user_id": self._user_id
}
@property
def username(self) -> str:
return self._username
@property
def email(self) -> str:
return self._email
@property
def last_login_ip(self) -> Optional[str]:
return self._last_login_ip
@property
def registration_ip(self) -> Optional[str]:
return self._registration_ip
@property
def application_id(self) -> str:
return self._application_id
class DatabaseHandler:
_host: str
_port: int
_database: str
_user: str
_password: str
_connection: psycopg2.extensions.connection
_cursor: psycopg2.extensions.cursor
def __init__(self, host: str, port: int, database: str, user: str, password: str):
self._host = host
self._port = port
self._database = database
self._user = user
self._password = password
def connect(self):
self._connection = psycopg2.connect(
host=self._host,
port=self._port,
database=self._database,
user=self._user,
password=self._password
)
self._cursor = self._connection.cursor()
def disconnect(self):
self._cursor.close()
self._connection.close()
def execute(self, query: str, *args):
self._cursor.execute(query, args)
def fetch(self):
return self._cursor.fetchall()
def commit(self):
self._connection.commit()
def rollback(self):
self._connection.rollback()
# noinspection SqlResolve
class DatabaseWrapper(DatabaseHandler):
def __init__(self, host: str, port: int, database: str, user: str, password: str):
super().__init__(host, port, database, user, password)
def get_user_id(self, username: str) -> str:
self.execute(
"SELECT user_id FROM users WHERE username = %s",
username
)
data = self.fetch()
return data[0][0]
def get_user(self, user_id: str) -> User:
self.execute(
"SELECT * FROM users WHERE user_id = %s",
user_id
)
user = self.fetch()[0]
return User(
user_id=user[0],
username=user[1],
email=user[3],
last_login_ip=user[5],
registration_ip=user[6],
application_id=user[7]
)
def get_users(self, application_id: str) -> List[Tuple[str, str]]:
self.execute(
"SELECT user_id, username FROM users WHERE application_id = %s",
application_id
)
return self.fetch()
def create_application(self, application_name: str) -> str:
application_id = str(uuid4())
self.execute(
"INSERT INTO applications (application_id, name) VALUES (%s, %s)",
application_id, application_name
)
self.commit()
return application_id
def create_license_key(self, application_id: str) -> str:
license_key = str(uuid4())
self.execute(
"INSERT INTO license_keys (license_key_id, application_id) VALUES (%s, %s)",
license_key, application_id
)
self.commit()
return license_key
def create_user(self, username: str, password: str, application_id: str, email: str, registration_ip: str) -> str:
user_id = str(uuid4())
self.execute(
"INSERT INTO users (user_id, username, password, application_id, email, registration_ip) VALUES (%s, %s, %s, %s, %s, %s)",
user_id, username, self.hash_password(password), application_id, email, registration_ip
)
self.commit()
return user_id
def create_admin(self, username: str, password: str, application_id: str, email: str) -> str:
admin_id = str(uuid4())
self.execute(
"INSERT INTO admins (admin_id, username, password, application_id, email) VALUES (%s, %s, %s, %s, %s)",
admin_id, username, self.hash_password(password), application_id, email
)
self.commit()
return admin_id
def delete_license_key(self, license_key: str) -> None:
self.execute(
"DELETE FROM license_keys WHERE license_key_id = %s",
license_key
)
self.commit()
def delete_user(self, user_id: str) -> None:
self.execute(
"DELETE FROM users WHERE user_id = %s",
user_id
)
self.commit()
def get_application_id(self, registration_key: str) -> str:
self.execute(
"SELECT application_id FROM license_keys WHERE license_key_id = %s",
registration_key
)
data = self.fetch()
return data[0][0]
def user_id_from_session(self, session_key: str) -> str:
self.execute(
"SELECT user_id FROM sessions WHERE session_id = %s",
session_key
)
data = self.fetch()
return data[0][0]
def create_session(self, user_id: str) -> str:
session_token = str(uuid4())
self.execute(
"INSERT INTO sessions (session_id, user_id) VALUES (%s, %s)",
session_token, user_id
)
self.commit()
return session_token
def create_admin_session(self, user_id: str) -> str:
session_token = str(uuid4())
self.execute(
"INSERT INTO admin_sessions (session_id, admin_id) VALUES (%s, %s)",
session_token, user_id
)
self.commit()
return session_token
def get_admin_id(self, username: str) -> str:
self.execute(
"SELECT admin_id FROM admins WHERE username = %s",
username
)
data = self.fetch()
return data[0][0]
def delete_session(self, session_token: str):
self.execute(
"DELETE FROM sessions WHERE session_id = %s",
session_token
)
self.commit()
def delete_admin_session(self, session_token: str):
self.execute(
"DELETE FROM admin_sessions WHERE session_id = %s",
session_token
)
self.commit()
def update_password(self, user_id: str, new_password: str) -> None:
self.execute(
"UPDATE users SET password = %s WHERE user_id = %s",
self.hash_password(new_password), user_id
)
self.commit()
def validate_session(self, session_token: str) -> bool:
self.execute(
"SELECT * FROM sessions WHERE session_id = %s",
session_token
)
data = self.fetch()
return len(data) > 0
def validate_admin_session(self, session_token: str) -> bool:
self.execute(
"SELECT * FROM admin_sessions WHERE session_id = %s",
session_token
)
data = self.fetch()
return len(data) > 0
def validate_registration_key(self, registration_key: str) -> bool:
self.execute(
"SELECT * FROM license_keys WHERE license_key_id = %s",
registration_key
)
data = self.fetch()
return len(data) > 0
def validate_credentials(self, username: str, password: str) -> Optional[str]:
self.execute(
"SELECT user_id FROM users WHERE username = %s AND password = %s",
username, self.hash_password(password)
)
data = self.fetch()
if len(data) > 0:
return data[0][0]
def validate_admin_credentials(self, username: str, password: str) -> Optional[str]:
self.execute(
"SELECT admin_id FROM admins WHERE username = %s AND password = %s",
username, self.hash_password(password)
)
data = self.fetch()
if len(data) > 0:
return data[0][0]
def update_user_ip(self, user_id: str, ip: str) -> None:
self.execute(
"UPDATE users SET last_login_ip = %s WHERE user_id = %s",
ip, user_id
)
self.commit()
@staticmethod
def hash_password(password: str) -> str:
return sha256(password.encode()).hexdigest()