/
db.py
executable file
·206 lines (182 loc) · 7.68 KB
/
db.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
#!/usr/bin/python
import sqlite3
import hashlib
import shutil
from flask import jsonify
import json
from static.User import User
from werkzeug.exceptions import BadRequest
# Database class which will be used to connect or execute commands in the DB
class DB:
# Initializing the class with some class variables
def __init__(self, root):
self.root = root
self.version = sqlite3.version
self.conn = sqlite3.connect(root + "/DATA.db", check_same_thread=False)
print("DB has been opened")
self.cursor = self.conn.cursor()
def displayVersion(self):
print("DB version: ", self.version)
# Sets the information given by the user to the DB
def insertUserData(self, user):
add = "INSERT INTO USERS (name, email, facePath, pin, calendarWidget, twitterWidget, mapWidget, clockWidget, weatherWidget, homeAddess, workAddress) VALUES (?,?,?,?,?,?,?,?,?,?,?)"
self.conn.executemany(add, [(user.name, user.email, "-", user.pin,
user.calendarwidget, user.twitterwidget, user.mapswidget, user.clockwidget,
user.weatherwidget, user.home, user.work)])
self.conn.commit()
# Adds a profile to the DB if it does not exists
def addProfile(self, content):
try:
user = self.createUser(content)
except BadRequest as e:
print(e.description)
raise e
if not self.isUserRegistered(user.email):
self.insertUserData(user)
print("A new profile has been added")
else:
raise BadRequest("A record with that email has already been registered")
def isUserRegistered(self, email):
userByEmail = 'SELECT COUNT(*) FROM USERS WHERE EMAIL= ?'
count = self.conn.execute(userByEmail, [email]).fetchone()[0]
if count > 0:
return True
else:
return False
def createUser(self, content):
print(content)
try:
user = User(content["name"], content["email"], DB.encrypt(content["pin"]))
except KeyError as e:
raise BadRequest("I/O error: {0} was not included".format(e))
try:
user.clockwidget = content["clockwidget"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
try:
user.home = content["homeAddress"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
try:
user.work = content["workAddress"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
try:
user.twitterwidget = content["twitterwidget"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
try:
user.calendarwidget = content["calendarwidget"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
try:
user.mapswidget = content["mapswidget"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
try:
user.weatherwidget = content["weatherwidget"]
except KeyError as e:
print("I/O error: {0} was not included".format(e))
return user
# Hash the pin and compare it with the one on the DB
# If it is the same go ahead and delete it
# If it is not the same throw some error
def deleteUser(self, email, pin):
hasheddpin = self.getHashedPin(email)
isSame = self.isHashSame(hasheddpin, pin)
if isSame:
query = "DELETE FROM USERS WHERE email = ?"
self.conn.execute(query, [email])
self.conn.commit()
print("User "+email+" has been deleted from the database")
events = "DELETE FROM Events WHERE eventID in (SELECT eventID from Participants WHERE email = ?)"
self.conn.execute(events, [email])
eventIDs = "DELETE FROM Participants where email = ?"
self.conn.execute(eventIDs, [email])
print("Deleted the eventIDs belonging to " + email)
print("Events for user " + email + " haven been deleted from the database")
self.conn.commit()
shutil.rmtree(self.root + "/" + email)
print("Images belonging to user " + email + " were deleted")
else:
raise BadRequest("The pin does no match")
def getUser(self, email):
if self.isUserRegistered(email):
query = "SELECT * FROM USERS WHERE email = ?"
userData = self.conn.execute(query, [email]).fetchall()[0]
userData = list(userData)
print(userData)
preferences = {"email": userData[0],
"name": userData[1],
"facepath": "-",
"pin": userData[3],
"calendarWidget": userData[4],
"mapWidget": userData[5],
"twitterWidget": userData[6],
"clockWidget": userData[7],
"weatherWidget": userData[8],
"homeAddress": userData[9],
"workAddress": userData[10]}
return preferences
else:
raise BadRequest("That email is not registered")
def setEvents(self, title, status, startTime, endTime, email):
query = "INSERT INTO Events (title, status, startTime, endTime) VALUES (?,?,?,?) "
self.conn.executemany(query, [(title, status, startTime, endTime)])
self.conn.commit()
eventID = self.conn.execute("SELECT MAX(eventID) FROM Events").fetchall()
eventID = int(eventID[0][0])
query = "INSERT INTO Participants (email, eventID) VALUES (?,?)"
self.conn.executemany(query, [(email, eventID)])
self.conn.commit()
def getHashedPin(self, email):
query = "SELECT * FROM USERS WHERE email = ?"
pin = ''
try:
pin = self.conn.execute(query, [email]).fetchone()[3]
except Exception as e:
raise BadRequest("Could not get user " + email)
return pin
def getEvents(self, email):
query = "SELECT eventID from Participants where email = ?"
eventIDs = self.conn.execute(query, [email]).fetchall()
events = []
sql = "select * from Events WHERE eventID = ?"
for id in eventIDs:
_list = self.conn.execute(sql, [id[0]]).fetchone()
event = {"Title": _list[1],
"startTime": _list[3],
"endTime": _list[4],
"status": _list[2]}
events.append(event)
if len(events) < 1:
return jsonify({"Error": "This user does not have any events saved in Google"})
return jsonify(events)
def getEmail(self):
try:
query = "SELECT email FROM Users"
print(self.root)
emails = self.conn.execute(query)
return emails
except KeyError as e:
print("Null Error: "+e)
def deleteAll(self):
# TODO: Remove all the pictures as well
Users = "DELETE FROM Users"
Events = "DELETE FROM Events"
EventIDs = "Delete FROM Participants"
self.conn.execute(Users)
self.conn.execute(Events)
self.conn.execute(EventIDs)
self.conn.commit()
@staticmethod
def encrypt(pin):
m = hashlib.new('sha512')
m.update(pin.encode('utf8'))
return m.hexdigest()
@staticmethod
def isHashSame(hashed_pin, original_pin):
m = DB.encrypt(original_pin)
if hashed_pin == m:
return True
return False