def __init__(self,
userdb="{0}/users.db".format(
settings.Settings.usersDirectory),
emailSender=None):
self.userdb = userdb
self.emailSender = EmailSender(
) if emailSender is None else emailSender
self.blockList = []
self.blockList.append("u03.gmailmirror.com")
def __init__(self, userdb="{0}/users.db".format(settings.Settings.usersDirectory), emailSender=None):
self.userdb = userdb
self.emailSender = EmailSender() if emailSender is None else emailSender
self.blockList = []
self.blockList.append("u03.gmailmirror.com")
class UserDatabase:
activeStatus = "active"
inactiveStatus = "inactive"
def __init__(self, userdb="{0}/users.db".format(settings.Settings.usersDirectory), emailSender=None):
self.userdb = userdb
self.emailSender = EmailSender() if emailSender is None else emailSender
self.blockList = []
self.blockList.append("u03.gmailmirror.com")
def getConnection(self, externalConn):
if externalConn is not None:
answer = externalConn
else:
answer = sqlite3.connect(self.userdb)
return answer
def closeConnectionIfNecessary(self, externalConn, conn):
if conn is not None and conn is not externalConn:
conn.close()
def login(self, email, password, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
self.clearExpiredSessions(conn)
sql = "select u.id, u.status from user u, password p where u.id = p.id and u.email = ? and p.password = ?"
c.execute(sql, (email, password))
row = c.fetchone()
if row is None:
raise UserException(UserException.emailOrPasswordNotFound)
userId, status = row
if status == UserDatabase.inactiveStatus:
raise UserException(UserException.userNotActive)
token = self.generateToken(userId)
c.execute("delete from session where id = ?", (userId,))
c.execute("insert into session (id, token, expiry) values(?, ?, datetime('now', '+1 day', 'localtime'))", (userId, token))
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error logging in", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return token
def generateToken(self, userid):
randValue = random.randint(0, 0xFFFFFF)
answer = "{0:X}{1:X}".format(userid, randValue)
return answer
def clearExpiredSessions(self, conn):
c = conn.cursor()
c.execute("delete from session where expiry < datetime('now', 'localtime')")
conn.commit()
def checkSessionToken(self, token, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
self.clearExpiredSessions(conn)
if c.execute("select id from session where token = ?", (token,)).fetchone() is None:
raise UserException(UserException.sessionExpired)
except sqlite3.Error as ex:
raise UserException("Error checking session token", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def registerUser(self, email, name, club, password, externalConn=None):
if self.isBlocked(email):
return -1
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
if c.execute("select id from user where email = ?", (email,)).fetchone() is not None:
raise UserException(UserException.emailAlreadyExists)
c.execute("insert into user (name, email, club, status, failurecount) values(?, ?, ?, ?, 0)", (name, email, club, UserDatabase.inactiveStatus))
answer = c.execute("select last_insert_rowid()").fetchone()[0]
c.execute("insert into password(id, password) values(?, ?)", (answer, password))
self.sendActivationEmail(answer, conn)
self.notifyAdmin("register", answer, conn)
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error registering user", ex)
finally:
if not conn is None:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def remindOfPassword(self, email, externalConn=None):
if self.isBlocked(email):
return -1
try:
conn = self.getConnection(externalConn)
try:
row = conn.cursor().execute("select id from user where email = ?", (email,)).fetchone()
if row is None:
raise UserException(UserException.emailNotFound)
userId, = row
self.sendPasswordReminderEmail(userId, email, externalConn=conn)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
except sqlite3.Error as ex:
raise UserException("Error sending password reminder", ex)
def activateUser(self, userId, externalConn=None):
return self.setUserStatus(userId, self.activeStatus, True, externalConn)
def toggleUserStatus(self, userId, currentStatus,externalConn=None):
newStatus = self.activeStatus if currentStatus == self.inactiveStatus else self.inactiveStatus
return self.setUserStatus(userId, newStatus, False, externalConn)
def setUserStatus(self, userId, status, notifyAdmin, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
c.execute("update user set status = ? where id = ?", (status, userId))
if c.rowcount == 0:
raise UserException(UserException.userNotFound)
row = c.execute("select name, email, club from user where id = ?", (userId,)).fetchone()
name, email, club = row
answer = UserDetails(userId, name, email, club)
if notifyAdmin:
self.notifyAdmin("activate", userId, conn)
conn.commit()
return answer
except sqlite3.Error as ex:
raise UserException("Error activating user", ex)
finally:
if not conn is None:
self.closeConnectionIfNecessary(externalConn, conn)
def createDatabase(self, externalConn=None, recreate=False):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
if recreate:
for index in ["email", "token"]:
try:
c.execute("drop index {0}".format(index))
except:
pass
for table in ["user", "password", "session", "role"]:
try:
c.execute("drop table {0}".format(table))
except:
pass
c.execute("create table user (id integer primary key, name text, email text, club text, status text, failurecount integer)")
c.execute("create table password (id integer primary key, password text)")
c.execute("create table session (id integer, token text, expiry timestamp)")
c.execute("create table role (id integer, role text)")
c.execute("create unique index email on user(email asc)")
c.execute("create unique index token on session(token asc)")
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error creating database", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def sendActivationEmail(self, userId, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
row = c.execute("select email from user where id = ?", (userId,)).fetchone()
if row is not None:
email, = row
message = """
Thank you for registering as a user of the South-East Hampshire Indoor Cricket League site.
Your e-mail address (to which this message was sent) is your user name.
Before you can use your account, you must activate it, which you can do by clicking the link below.
http://sehicl.hampshire.org.uk/cgi-bin/page.py?id=activate&user={user}
If you experience any problems with the activation process, please reply to this e-mail stating what problem you saw.
Many thanks,
The Webmaster
""".format(user=userId)
message = string.join([l.strip() for l in re.split("\n", message)], "\n")
self.emailSender.sendMessage([email], "Activate your SEHICL account", message)
except sqlite3.Error as ex:
raise UserException("Error looking up e-mail address", ex)
except SMTPRecipientsRefused as ex:
raise UserException("Unable to send e-mail to specified address", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def sendPasswordReminderEmail(self, userId, email, externalConn=None):
try:
conn = self.getConnection(externalConn)
try:
c = conn.cursor()
row = c.execute("select password from password where id = ?", (userId,)).fetchone()
if row is not None:
password, = row
message = """
This is a password reminder for your account as a registered user of the South-East Hampshire Indoor Cricket League site.
Your e-mail address (to which this message was sent) is your user name. Your password is {password}.
If you did not request this password reminder, please inform the Webmaster.
Many thanks,
The Webmaster
""".format(password=password)
message = string.join([l.strip() for l in re.split("\n", message)], "\n")
self.emailSender.sendMessage([email], "SEHICL password reminder", message)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
except sqlite3.Error as ex:
raise UserException("Error looking up password", ex)
except SMTPRecipientsRefused as ex:
raise UserException("Unable to send e-mail to specified address", ex)
def sessionHasRole(self, token, role, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = "select count(*) from session s, role r where s.id = r.id and s.token = ? and r.role = ?"
count, = c.execute(sql, (token, role)).fetchone()
answer = count != 0
except sqlite3.Error as ex:
raise UserException("Error looking up role for token", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def notifyAdmin(self, action, userId, externalConn=None):
message = """
Account details:
User ID: {userid}
Name: {name}
Club: {club}
Email: {email}
"""
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = "select name, club, email from user where id = ?"
name, club, email = c.execute(sql, (userId,)).fetchone()
clubTxt = "(Not specified)" if club is None else club
msg = message.format(userid=userId, name=name, club=clubTxt, email=email)
msg = string.join([m.strip() for m in string.split(msg, "\n")], "\n")
subject = "User action: {0}".format(action)
self.emailSender.sendMessage([Settings.adminEmail], subject, msg)
except sqlite3.Error as ex:
raise UserException("Error looking up role for token", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def getUserList(self, externalConn=None):
answer = []
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = """
select u.id, u.name, u.club, u.email, r.role, s.token, u.status, u.failurecount
from
user u left outer join role r on u.id = r.id
left outer join session s on u.id = s.id
order by u.id
"""
user = None
for row in c.execute(sql).fetchall():
userid, name, club, email, role, token, status, failurecount = row
if user is None or userid != user:
user = UserDetails(userid, name, email, club, token, status, failurecount)
answer.append(user)
if role is not None:
user.roles.append(role)
except sqlite3.Error as ex:
raise UserException("Error Fetching user list", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def getUserDetails(self, userId, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = "select u.id, u.name, u.club, u.email, r.role from user u left outer join role r on u.id = r.id where u.id = ?"
user = None
for row in c.execute(sql, (userId,)).fetchall():
userid, name, club, email, role = row
if user is None or userid != user:
user = UserDetails(userid, name, email, club)
answer = user
if role is not None:
user.roles.append(role)
except sqlite3.Error as ex:
raise UserException("Error looking up details of user {0}".format(userId), ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def deleteUser(self, userId, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
tables = ["password", "session", "role", "user"]
for t in tables:
sql = "delete from {0} where id = ?".format(t)
c.execute(sql, (userId,))
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error looking up details of user {0}".format(userId), ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def isBlocked(self, email):
answer = False
for b in self.blockList:
answer = string.find(email, b) != -1
if answer:
break
return answer
class UserDatabase:
activeStatus = "active"
inactiveStatus = "inactive"
def __init__(self,
userdb="{0}/users.db".format(
settings.Settings.usersDirectory),
emailSender=None):
self.userdb = userdb
self.emailSender = EmailSender(
) if emailSender is None else emailSender
self.blockList = []
self.blockList.append("u03.gmailmirror.com")
def getConnection(self, externalConn):
if externalConn is not None:
answer = externalConn
else:
answer = sqlite3.connect(self.userdb)
return answer
def closeConnectionIfNecessary(self, externalConn, conn):
if conn is not None and conn is not externalConn:
conn.close()
def login(self, email, password, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
self.clearExpiredSessions(conn)
sql = "select u.id, u.status from user u, password p where u.id = p.id and u.email = ? and p.password = ?"
c.execute(sql, (email, password))
row = c.fetchone()
if row is None:
raise UserException(UserException.emailOrPasswordNotFound)
userId, status = row
if status == UserDatabase.inactiveStatus:
raise UserException(UserException.userNotActive)
token = self.generateToken(userId)
c.execute("delete from session where id = ?", (userId, ))
c.execute(
"insert into session (id, token, expiry) values(?, ?, datetime('now', '+1 day', 'localtime'))",
(userId, token))
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error logging in", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return token
def generateToken(self, userid):
randValue = random.randint(0, 0xFFFFFF)
answer = "{0:X}{1:X}".format(userid, randValue)
return answer
def clearExpiredSessions(self, conn):
c = conn.cursor()
c.execute(
"delete from session where expiry < datetime('now', 'localtime')")
conn.commit()
def checkSessionToken(self, token, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
self.clearExpiredSessions(conn)
if c.execute("select id from session where token = ?",
(token, )).fetchone() is None:
raise UserException(UserException.sessionExpired)
except sqlite3.Error as ex:
raise UserException("Error checking session token", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def registerUser(self, email, name, club, password, externalConn=None):
if self.isBlocked(email):
return -1
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
if c.execute("select id from user where email = ?",
(email, )).fetchone() is not None:
raise UserException(UserException.emailAlreadyExists)
c.execute(
"insert into user (name, email, club, status, failurecount) values(?, ?, ?, ?, 0)",
(name, email, club, UserDatabase.inactiveStatus))
answer = c.execute("select last_insert_rowid()").fetchone()[0]
c.execute("insert into password(id, password) values(?, ?)",
(answer, password))
self.sendActivationEmail(answer, conn)
self.notifyAdmin("register", answer, conn)
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error registering user", ex)
finally:
if not conn is None:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def remindOfPassword(self, email, externalConn=None):
if self.isBlocked(email):
return -1
try:
conn = self.getConnection(externalConn)
try:
row = conn.cursor().execute(
"select id from user where email = ?",
(email, )).fetchone()
if row is None:
raise UserException(UserException.emailNotFound)
userId, = row
self.sendPasswordReminderEmail(userId,
email,
externalConn=conn)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
except sqlite3.Error as ex:
raise UserException("Error sending password reminder", ex)
def activateUser(self, userId, externalConn=None):
return self.setUserStatus(userId, self.activeStatus, True,
externalConn)
def toggleUserStatus(self, userId, currentStatus, externalConn=None):
newStatus = self.activeStatus if currentStatus == self.inactiveStatus else self.inactiveStatus
return self.setUserStatus(userId, newStatus, False, externalConn)
def setUserStatus(self, userId, status, notifyAdmin, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
c.execute("update user set status = ? where id = ?",
(status, userId))
if c.rowcount == 0:
raise UserException(UserException.userNotFound)
row = c.execute("select name, email, club from user where id = ?",
(userId, )).fetchone()
name, email, club = row
answer = UserDetails(userId, name, email, club)
if notifyAdmin:
self.notifyAdmin("activate", userId, conn)
conn.commit()
return answer
except sqlite3.Error as ex:
raise UserException("Error activating user", ex)
finally:
if not conn is None:
self.closeConnectionIfNecessary(externalConn, conn)
def createDatabase(self, externalConn=None, recreate=False):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
if recreate:
for index in ["email", "token"]:
try:
c.execute("drop index {0}".format(index))
except:
pass
for table in ["user", "password", "session", "role"]:
try:
c.execute("drop table {0}".format(table))
except:
pass
c.execute(
"create table user (id integer primary key, name text, email text, club text, status text, failurecount integer)"
)
c.execute(
"create table password (id integer primary key, password text)"
)
c.execute(
"create table session (id integer, token text, expiry timestamp)"
)
c.execute("create table role (id integer, role text)")
c.execute("create unique index email on user(email asc)")
c.execute("create unique index token on session(token asc)")
conn.commit()
except sqlite3.Error as ex:
raise UserException("Error creating database", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def sendActivationEmail(self, userId, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
row = c.execute("select email from user where id = ?",
(userId, )).fetchone()
if row is not None:
email, = row
message = """
Thank you for registering as a user of the South-East Hampshire Indoor Cricket League site.
Your e-mail address (to which this message was sent) is your user name.
Before you can use your account, you must activate it, which you can do by clicking the link below.
http://sehicl.hampshire.org.uk/cgi-bin/page.py?id=activate&user={user}
If you experience any problems with the activation process, please reply to this e-mail stating what problem you saw.
Many thanks,
The Webmaster
""".format(user=userId)
message = string.join(
[l.strip() for l in re.split("\n", message)], "\n")
self.emailSender.sendMessage([email],
"Activate your SEHICL account",
message)
except sqlite3.Error as ex:
raise UserException("Error looking up e-mail address", ex)
except SMTPRecipientsRefused as ex:
raise UserException("Unable to send e-mail to specified address",
ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def sendPasswordReminderEmail(self, userId, email, externalConn=None):
try:
conn = self.getConnection(externalConn)
try:
c = conn.cursor()
row = c.execute("select password from password where id = ?",
(userId, )).fetchone()
if row is not None:
password, = row
message = """
This is a password reminder for your account as a registered user of the South-East Hampshire Indoor Cricket League site.
Your e-mail address (to which this message was sent) is your user name. Your password is {password}.
If you did not request this password reminder, please inform the Webmaster.
Many thanks,
The Webmaster
""".format(password=password)
message = string.join(
[l.strip() for l in re.split("\n", message)], "\n")
self.emailSender.sendMessage([email],
"SEHICL password reminder",
message)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
except sqlite3.Error as ex:
raise UserException("Error looking up password", ex)
except SMTPRecipientsRefused as ex:
raise UserException("Unable to send e-mail to specified address",
ex)
def sessionHasRole(self, token, role, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = "select count(*) from session s, role r where s.id = r.id and s.token = ? and r.role = ?"
count, = c.execute(sql, (token, role)).fetchone()
answer = count != 0
except sqlite3.Error as ex:
raise UserException("Error looking up role for token", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def notifyAdmin(self, action, userId, externalConn=None):
message = """
Account details:
User ID: {userid}
Name: {name}
Club: {club}
Email: {email}
"""
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = "select name, club, email from user where id = ?"
name, club, email = c.execute(sql, (userId, )).fetchone()
clubTxt = "(Not specified)" if club is None else club
msg = message.format(userid=userId,
name=name,
club=clubTxt,
email=email)
msg = string.join([m.strip() for m in string.split(msg, "\n")],
"\n")
subject = "User action: {0}".format(action)
self.emailSender.sendMessage([Settings.adminEmail], subject, msg)
except sqlite3.Error as ex:
raise UserException("Error looking up role for token", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def getUserList(self, externalConn=None):
answer = []
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = """
select u.id, u.name, u.club, u.email, r.role, s.token, u.status, u.failurecount
from
user u left outer join role r on u.id = r.id
left outer join session s on u.id = s.id
order by u.id
"""
user = None
for row in c.execute(sql).fetchall():
userid, name, club, email, role, token, status, failurecount = row
if user is None or userid != user:
user = UserDetails(userid, name, email, club, token,
status, failurecount)
answer.append(user)
if role is not None:
user.roles.append(role)
except sqlite3.Error as ex:
raise UserException("Error Fetching user list", ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def getUserDetails(self, userId, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
sql = "select u.id, u.name, u.club, u.email, r.role from user u left outer join role r on u.id = r.id where u.id = ?"
user = None
for row in c.execute(sql, (userId, )).fetchall():
userid, name, club, email, role = row
if user is None or userid != user:
user = UserDetails(userid, name, email, club)
answer = user
if role is not None:
user.roles.append(role)
except sqlite3.Error as ex:
raise UserException(
"Error looking up details of user {0}".format(userId), ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
return answer
def deleteUser(self, userId, externalConn=None):
try:
conn = None
conn = self.getConnection(externalConn)
c = conn.cursor()
tables = ["password", "session", "role", "user"]
for t in tables:
sql = "delete from {0} where id = ?".format(t)
c.execute(sql, (userId, ))
conn.commit()
except sqlite3.Error as ex:
raise UserException(
"Error looking up details of user {0}".format(userId), ex)
finally:
self.closeConnectionIfNecessary(externalConn, conn)
def isBlocked(self, email):
answer = False
for b in self.blockList:
answer = string.find(email, b) != -1
if answer:
break
return answer
