-
Notifications
You must be signed in to change notification settings - Fork 0
/
Database.py
328 lines (290 loc) · 11.8 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
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
import sqlite3
import pickle
from datetime import datetime
import os
import Ui
from Game import Game, GameRecord
# The HashTable class enables usernames to be stored at indexes determined by their passwords in a table.
class HashTable:
def __init__(self):
self._SIZE = 50
self._hashTable = [[] for _ in range(self._SIZE)]
# Given a key, performs a hashing algorithm on the key and returns the hashed value.
def __hashFunction(self, key):
total = 0
for char in key:
total += 3*ord(char)
return total % self._SIZE
# Given a username and password, the username is stored in the hash table at the index specified by performing the hashFunction on the password.
def addToTable(self, username, password):
passwordHash = self.__hashFunction(password)
self._hashTable[passwordHash].append(username)
# Given a username and password, returns if the hash table stores the username in the table at the index specified by the hash of the password.
def isInTable(self, username, password):
passwordHash = self.__hashFunction(password)
return username in self._hashTable[passwordHash]
# Returns if the database exists.
def exists():
return os.path.exists("PenteDatabase.db")
# Connects with the database via the sqlite3 connect function.
def connect():
conn = sqlite3.connect("PenteDatabase.db")
c = conn.cursor()
return conn, c
# Closes the connection with the database and commits the last made changes.
def close(conn):
conn.commit()
conn.close()
# Creates a new database.
def createDatabase():
playerSQL = """
CREATE TABLE Player(
username TEXT PRIMARY KEY,
whenSaved BLOB NOT NULL,
numberOfWins INTEGER NOT NULL,
numberOfLosses INTEGER NOT NULL,
numberOfDraws INTEGER NOT NULL,
score INTEGER NOT NULL
);"""
hashtableSQL = """
CREATE TABLE HashTable(
id INTEGER PRIMARY KEY,
hashTable BLOB NOT NULL
);"""
gameSQL = """
CREATE TABLE Game(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
whenSaved BLOB NOT NULL,
game BLOB NOT NULL,
winner INTEGER NOT NULL,
mode BLOB NOT NULL,
compDifficulty INTEGER NOT NULL
);"""
playergameSQL = """
CREATE TABLE PlayerGame(
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
gameId INTEGER NOT NULL,
playerNo INTEGER NOT NULL
);"""
conn, c = connect()
tableSQLDict = {"Player": playerSQL, "HashTable": hashtableSQL, "Game": gameSQL, "PlayerGame": playergameSQL}
for tableName, sql in tableSQLDict.items():
c.execute(sql)
if tableName == "HashTable":
hashtable = pickle.dumps(HashTable())
recordSQL = """
INSERT INTO HashTable(id, hashTable)
VALUES(1, ?);
"""
editTable(recordSQL, (hashtable,))
close(conn)
# The SQL, values to be used in the SQL, and whether the id of the last row added is required to be returned is passed in as parameters.
# THe SQL is executed with the values, and if the id of the last row is required, this is returned. Otherwise, nothing is returned.
def editTable(recordSQL, values, getId=False):
conn, c = connect()
c.execute(recordSQL, values)
if getId:
id = c.lastrowid
else:
id = None
close(conn)
return id
# Given a recordQuery (written in SQL) and values to be used in the SQL, the getRecords function returns the records specified by the recordQuery.
def getRecords(recordQuery, values=()):
conn, c = connect()
c.execute(recordQuery, values)
res = c.fetchall()
close(conn)
return res
# Loads the hash table from the database and returns it.
def loadHashTable():
recordQuery = """
SELECT hashtable
FROM HashTable
WHERE id = 1;
"""
[hashtable] = getRecords(recordQuery, ())[0]
return pickle.loads(hashtable)
# Given a hash table, the saveHashTable function updates the existing hash table in the database with the new one.
def saveHashTable(hashtable):
updateQuery = f"""
UPDATE HashTable
SET hashTable = ?
WHERE id = 1;
"""
editTable(updateQuery, (pickle.dumps(hashtable),))
# Given a username and password, returns whether there is such a match found in the hash table stored in the database.
def checkPassword(username, password):
hashtable = loadHashTable()
return hashtable.isInTable(username, password)
# Given a username and password, the function loads the hash table from the database, adds the new match to the table, and saves it back to the database.
def addPassword(username, password):
hashtable = loadHashTable()
hashtable.addToTable(username, password)
saveHashTable(hashtable)
# Given a username, password, and whenSaved (the datetime the account was created) the username and password is added to the hash table, and a new Player entry is made in the database's Player table.
def savePlayer(username, password, whenSaved):
addPassword(username, password)
whenSaved = pickle.dumps(whenSaved)
recordSQL = """
INSERT INTO Player(username, whenSaved, numberOfWins, numberOfLosses, numberOfDraws, score)
VALUES(?, ?, 0, 0, 0, 0);
"""
editTable(recordSQL, (username, whenSaved))
# Given a username, the details of the Player entry specified by username is returned from the function.
def getPlayer(username):
recordSQL = """
SELECT whenSaved, numberOfWins, numberOfLosses, numberOfDraws, score
FROM Player
WHERE username = ?;
"""
whenSaved, numberOfWins, numberOfLosses, numberOfDraws, score = getRecords(recordSQL, (username,))[0]
whenSaved = pickle.loads(whenSaved)
return [whenSaved, numberOfWins, numberOfLosses, numberOfDraws, score]
# Adds a game result to the player's profile, and updates the player's score depending on the result.
def addPlayerResult(username, didWin):
if didWin == True:
field = "numberOfWins"
scoreAdd = 5
elif didWin == False:
field = "numberOfLosses"
scoreAdd = 1
else:
field = "numberOfDraws"
scoreAdd = 3
selectSQL = f"""
SELECT {field}, score
FROM Player
WHERE username = ?;
"""
updateSQL = f"""
UPDATE Player
SET {field} = ?, score = ?
WHERE username = ?;
"""
num, score = getRecords(selectSQL, (username,))[0]
editTable(updateSQL, (num+1, score+scoreAdd, username))
# Returns the specified player's rank by score amongst other players.
def getPlayerRank(username):
recordSQL = """
SELECT username
FROM Player
ORDER BY score DESC;
"""
playerUsernames = getRecords(recordSQL)
return playerUsernames.index((username,))+1
# Given a username, the function returns if there are any existing Player entries in the Player table with that username.
def isUniqueUsername(username):
recordSQL = """
SELECT username
FROM Player
WHERE username = ?;
"""
players = getRecords(recordSQL, (username,))
return len(players) == 0
# Given the usernames of the players and the game record, the saveGame function saves the game into the Game table, and also associates it with the Player entries in the PlayerGame table.
# The function returns the game id of the game that it has saved
def saveGame(username1, username2, gameRecord):
name = gameRecord.name
whenSaved = pickle.dumps(gameRecord.whenSaved)
game = pickle.dumps(gameRecord.game)
winner = gameRecord.game.winner
mode = pickle.dumps(gameRecord.mode)
compDifficulty = gameRecord.compDifficulty
recordSQL = """
INSERT INTO Game(name, whenSaved, game, winner, mode, compDifficulty)
VALUES(?, ?, ?, ?, ?, ?);
"""
gameId = editTable(recordSQL, (name, whenSaved, game, winner, mode, compDifficulty), getId=True)
invalidUsernames = [Ui.Player.GUEST, Ui.Player.COMP]
if username1 not in invalidUsernames:
savePlayerGame(username1, gameId, Game.P1)
if username2 not in invalidUsernames:
savePlayerGame(username2, gameId, Game.P2)
return gameId
# Given a game record, updates the game with the same id in the Game table with the new game information.
def updateGame(gameRecord):
whenSaved = pickle.dumps(gameRecord.whenSaved)
game = pickle.dumps(gameRecord.game)
winner = gameRecord.game.winner
id = gameRecord.id
recordSQL = """
UPDATE Game
SET whenSaved = ?, game = ?, winner = ?
WHERE id = ?;
"""
editTable(recordSQL, (whenSaved, game, winner, id))
# Given a list of game details, the function converts each game detail into its correct format, before returning them all as part of a single game record.
def parseGames(games):
parsedGames = []
for game in games:
g = list(game) # [id, name, whenSaved, game, winner, mode, compDifficulty]
g[2] = pickle.loads(g[2]) # whenSaved
g[3] = pickle.loads(g[3]) # game
g[5] = pickle.loads(g[5]) # mode
gameRecord = GameRecord(g[0], g[1], g[2], g[3], g[5], g[6])
parsedGames.append(gameRecord)
return parsedGames
# Given a username and a winner, the function returns all games which were played by the player with the username and had the specified winner.
def loadGames(username, winner):
recordSQL = """
SELECT Game.id, Game.name, Game.whenSaved, Game.game, Game.winner, Game.mode, Game.compDifficulty
FROM Game
INNER JOIN PlayerGame ON PlayerGame.gameId = Game.id
WHERE PlayerGame.username = ? AND Game.winner = ?
ORDER BY Game.whenSaved DESC;
"""
games = getRecords(recordSQL, (username, winner))
return parseGames(games)
# Given a username, returns all the games that were played by the player with the username.
def loadAllGames(username):
recordSQL = """
SELECT Game.id, Game.name, Game.whenSaved, Game.game, Game.winner, Game.mode, Game.compDifficulty
FROM Game
INNER JOIN PlayerGame ON PlayerGame.gameId = Game.id
WHERE PlayerGame.username = ?
ORDER BY Game.whenSaved DESC;
"""
games = getRecords(recordSQL, (username,))
return parseGames(games)
# Given an id of a game, returns the information of the game stored in the Game table with that id.
def getGame(id):
recordSQL = """
SELECT Game.id, Game.name, Game.whenSaved, Game.game, Game.winner, Game.mode, Game.compDifficulty
FROM Game
WHERE id = ?;
"""
game = getRecords(recordSQL, (id,))
return parseGames(game)[0]
# Given a username, game id, and a player number, the savePlayerGame creates a new entry in the PlayerGame table which relates a Player entry to a Game entry, and also which player number the player played as in the game.
def savePlayerGame(username, gameId, playerNo):
recordSQL = """
INSERT INTO PlayerGame(username, gameId, playerNo)
VALUES(?, ?, ?)
"""
editTable(recordSQL, (username, gameId, playerNo))
# Given a game id and a player number, returns the username of the player who played as that player number in that game.
def getPlayerGameUsername(gameId, playerNo):
recordSQL = """
SELECT username
FROM PlayerGame
WHERE gameId = ? AND playerNo = ?;
"""
records = getRecords(recordSQL, (gameId, playerNo))
if not records:
return False
return records[0][0]
# Given a game id, the function deletes the game with that game id from the Game table, along with any related PlayerGame entries.
def deleteGame(gameId):
recordSQL = """
DELETE FROM Game
WHERE Game.id = ?;
"""
editTable(recordSQL, (gameId,))
recordSQL = """
DELETE FROM PlayerGame
WHERE PlayerGame.gameId = ?;
"""
editTable(recordSQL, (gameId,))