def add_perm(uid, course_id, perm):
""" Assign a permission."""
key = "permission-%s-super" % (uid, )
MC.delete(key)
run_sql(
"""INSERT INTO permissions (course, userid, permission)
VALUES (%s, %s, %s) """, (course_id, uid, perm))
def save(self):
""" Save ourselves back to database.
"""
if self.new:
sql = """INSERT INTO userfeeds ("name", "title", "script", "envvar",
"comments", "freq", "status", "error",
"active", "priority", "regex")
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
params = [self.name, self.title, self.script, self.envvar,
self.comments, self.freq, self.status, self.error,
self.active, self.priority, self.regex]
run_sql(sql, params)
self.new = False
return
sql = """UPDATE userfeeds
SET "name" = %s,
"title" = %s,
"script" = %s,
"envvar" = %s,
"comments" = %s,
"freq" = %s,
"status" = %s,
"error" = %s,
"active" = %s,
"priority" = %s,
"regex" = %s
WHERE "id" = %s;"""
params = [self.name, self.title, self.script, self.envvar,
self.comments, self.freq, self.status, self.error,
self.active, self.priority, self.regex, self.id]
run_sql(sql, params)
def save(self):
""" Save ourselves back to database.
"""
if self.new:
sql = """INSERT INTO "lti_consumers" ("title", "shared_secret", "consumer_key", "comments", "active")
VALUES (%s, %s, %s, %s, %s);"""
params = [self.title, self.shared_secret, self.consumer_key,
self.comments, self.active]
run_sql(sql, params)
self.new = False
update_lti_config()
return
sql = """UPDATE "lti_consumers"
SET "title" = %s,
"shared_secret" = %s,
"username_attribute" = %s,
"consumer_key" = %s,
"comments" = %s,
"active" = %s
WHERE "id" = %s;"""
params = [self.title, self.shared_secret, self.username_attribute, self.consumer_key,
self.comments, self.active, self.id]
run_sql(sql, params)
update_lti_config()
def create(uname, passwd, givenname, familyname, acctstatus, studentid,
email=None, expiry=None, source="local",
confirm_code=None, confirm=True, display_name=False):
""" Add a user to the database. """
L.info("Users.py:create(%s)" % uname)
if not confirm_code:
confirm_code = ""
if not display_name:
if givenname or familyname:
display_name = "%s %s" % (givenname, familyname)
elif email:
display_name = email
else:
display_name = "Unknown"
run_sql("""INSERT INTO users (uname, passwd, givenname, familyname,
acctstatus, student_id, email, expiry,
source, confirmation_code, confirmed, display_name)
VALUES (%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s);""",
[uname, passwd, givenname, familyname,
acctstatus, studentid, email, expiry,
source, confirm_code, confirm, display_name])
incr_version()
uid = uid_by_uname(uname)
L.info("User created with uid %d." % uid)
return uid
def find(search, limit=20):
""" return a list of user id's that reasonably match the search term.
Search username then student ID then surname then first name.
Return results in that order.
"""
# exact search first
ret = run_sql("""SELECT id FROM users
WHERE uname = %s
OR student_id = %s
OR email = %s LIMIT %s;""",
[search, search, search, limit])
res = []
if ret:
res = [user[0] for user in ret]
if res:
return res
# Then look for similar
ret = run_sql("""SELECT id FROM users
WHERE LOWER(uname) LIKE LOWER(%s)
OR LOWER(familyname) LIKE LOWER(%s)
OR LOWER(givenname) LIKE LOWER(%s)
OR student_id LIKE %s
OR LOWER(email) LIKE LOWER(%s) LIMIT %s;""",
[search, search, search, search, search, limit])
if ret:
res = [user[0] for user in ret]
return res
def get_courses_dict(only_active=False):
""" Return a summary of all courses, keyed by course id
[id] = { 'id':id, 'name':name, 'title':title }
"""
if only_active:
ret = run_sql(
"""SELECT course, title, description, owner, active, type,
practice_visibility, assess_visibility
FROM courses
WHERE active='1';""")
else:
ret = run_sql(
"""SELECT course, title, description, owner, active, type,
practice_visibility, assess_visibility
FROM courses;""")
cdict = {}
if ret:
for row in ret:
course = {
'id': int(row[0]),
'name': row[1],
'title': row[2],
'owner': row[3],
'active': row[4],
'type': row[5],
'practice_visibility': row[6],
'assess_visibility': row[7]
}
if not course['practice_visibility']:
course['practice_visibility'] = "all"
if not course['assess_visibility']:
course['assess_visibility'] = "all"
cdict[int(row[0])] = course
return cdict
def set_name(course_id, name):
""" Set the name of a course."""
assert isinstance(course_id, int)
assert isinstance(name, str) or isinstance(name, unicode)
run_sql("UPDATE courses SET title = %s WHERE course = %s;", [name, course_id])
key = "course-%s-name" % course_id
MC.delete(key)
def create(uname,
passwd,
givenname,
familyname,
acctstatus,
studentid,
email=None,
expiry=None,
source="local",
confirm_code=None,
confirm=True):
""" Add a user to the database. """
L.info("Users.py:create(%s)" % uname)
if not confirm_code:
confirm_code = ""
run_sql(
"""INSERT INTO users (uname, passwd, givenname, familyname,
acctstatus, student_id, email, expiry,
source, confirmation_code, confirmed)
VALUES (%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s);""",
(uname, passwd, givenname, familyname, acctstatus, studentid, email,
expiry, source, confirm_code, confirm))
incr_version()
uid = uid_by_uname(uname)
L.info("User created with uid %d." % uid)
return uid
def save(self):
""" Save ourselves back to database.
"""
if self.new:
sql = """INSERT INTO feeds ("name", "title", "script", "envvar",
"comments", "freq", "status", "error",
"active")
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);"""
params = (self.name, self.title, self.script, self.envvar,
self.comments, self.freq, self.status, self.error,
self.active)
run_sql(sql, params)
self.new = False
return
sql = """UPDATE feeds
SET name=%s,
title=%s,
script=%s,
envvar=%s,
comments=%s,
freq=%s,
status=%s,
error=%s,
active=%s
WHERE id=%s;"""
params = (self.name, self.title, self.script, self.envvar,
self.comments, self.freq, self.status, self.error,
self.active, self.id)
run_sql(sql, params)
def audit(aclass, instigator, obj, module, message):
"""Record the message in the audit system."""
sql = """INSERT INTO audit ("time", "class", "instigator",
"object", "module", "longmesg")
VALUES (NOW(), %s, %s, %s, %s, %s);"""
params = (aclass, instigator, obj, module, message)
run_sql(sql, params)
def audit(aclass, instigator, obj, module, message):
"""Record the message in the audit system."""
sql = """INSERT INTO audit ("time", "class", "instigator",
"object", "module", "longmesg")
VALUES (NOW(), %s, %s, %s, %s, %s);"""
params = [aclass, instigator, obj, module, message]
run_sql(sql, params)
def set_familyname(uid, name):
""" Update Family Name."""
run_sql("""UPDATE "users" SET familyname=%s WHERE id=%s;""", (
name,
uid,
))
incr_version()
def set_email(uid, email):
""" Update Email."""
run_sql("""UPDATE "users" SET email=%s WHERE id=%s;""", (
email,
uid,
))
incr_version()
def set_givenname(uid, name):
""" Update Given Name."""
run_sql("""UPDATE "users" SET givenname=%s WHERE id=%s;""", (
name,
uid,
))
incr_version()
def set_studentid(uid, stid):
""" Update student ID."""
run_sql("""UPDATE "users" SET student_id=%s WHERE id=%s;""", (
stid,
uid,
))
incr_version()
def set_assess_vis(cid, visibility):
""" Who can do assessments."""
assert isinstance(cid, int)
assert isinstance(visibility, str) or isinstance(visibility, unicode)
run_sql("UPDATE courses SET assess_visibility = %s WHERE course = %s;",
[visibility, cid])
def save(self):
""" Save ourselves back to database.
"""
if self.new:
sql = """INSERT INTO feeds ("name", "title", "script", "envvar",
"comments", "freq", "status", "error",
"active")
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);"""
params = [self.name, self.title, self.script, self.envvar,
self.comments, self.freq, self.status, self.error,
self.active]
run_sql(sql, params)
self.new = False
return
sql = """UPDATE feeds
SET name = %s,
title = %s,
script = %s,
envvar = %s,
comments = %s,
freq = %s,
status = %s,
error = %s,
active = %s
WHERE id = %s;"""
params = [self.name, self.title, self.script, self.envvar,
self.comments, self.freq, self.status, self.error,
self.active, self.id]
run_sql(sql, params)
def set_name(course_id, name):
""" Set the name of a course."""
assert isinstance(course_id, int)
assert isinstance(name, str) or isinstance(name, unicode)
incr_version()
run_sql("UPDATE courses SET title=%s WHERE course=%s;", (name, course_id))
key = "course-%s-name" % course_id
MC.delete(key)
def set_prac_vis(cid, visibility):
""" Who can do practice questions."""
assert isinstance(cid, int)
assert isinstance(visibility, str) or isinstance(visibility, unicode)
run_sql("UPDATE courses SET practice_visibility=%s WHERE course=%s;",
(visibility, cid))
incr_version()
def set_prac_vis(cid, visibility):
""" Who can do practice questions."""
assert isinstance(cid, int)
assert isinstance(visibility, str) or isinstance(visibility, unicode)
run_sql("UPDATE courses SET practice_visibility=%s WHERE course=%s;",
(visibility, cid))
incr_version()
def set_assess_vis(cid, visibility):
""" Who can do assessments."""
assert isinstance(cid, int)
assert isinstance(visibility, str) or isinstance(visibility, unicode)
run_sql("UPDATE courses SET assess_visibility=%s WHERE course=%s;",
(visibility, cid))
incr_version()
def get_topics_all(course, archived=2, numq=True):
""" Return a summary of all topics in the course.
if archived=0, only return non archived courses
if archived=1, only return archived courses
if archived=2, return all courses
if numq is true then include the number of questions in the topic
"""
ret = None
if archived == 0:
ret = run_sql("""SELECT topic, title, position, visibility, archived
FROM topics
WHERE course=%s
AND (archived='0' OR archived IS NULL)
ORDER BY position, topic;""", (course,))
elif archived == 1:
ret = run_sql("""SELECT topic, title, position, visibility, archived
FROM topics
WHERE course=%s
AND archived='1'
ORDER BY position, topic;""", (course,))
elif archived == 2:
ret = run_sql("""SELECT topic, title, position, visibility, 0
FROM topics
WHERE course=%s
ORDER BY position, topic;""", (course,))
info = {}
if ret:
count = 0
for row in ret:
info[count] = {'id': int(row[0]),
'title': row[1],
'position': row[2],
'visibility': row[3],
'archived': row[4]}
if info[count]['position'] is None or info[count]['position'] is "None":
info[count]['position'] = 0
if numq:
info[count]['numquestions'] = Topics.get_num_qs(int(row[0]))
count += 1
else: # we probably don't have the archived flag in the Db yet
ret = run_sql(
"""SELECT topic, title, position, visibility
FROM topics
WHERE course=%s
ORDER BY position, topic;""", (course,))
if ret:
count = 0
for row in ret:
info[count] = {'id': int(row[0]),
'title': row[1],
'position': row[2],
'visibility': row[3]}
if info[count]['position'] is None or info[count]['position'] is "None":
info[count]['position'] = 0
if numq:
info[count]['numquestions'] = Topics.get_num_qs(int(row[0]))
count += 1
return info
def set_title(course_id, title):
# type: (int, str) -> None
""" Set the title of a course."""
assert isinstance(course_id, int)
assert isinstance(title, str) or isinstance(title, unicode)
run_sql("UPDATE courses SET description = %s WHERE course = %s;",
[title, course_id])
key = "course-%s-title" % course_id
MC.delete(key)
def del_group(group_id, course_id):
""" Remove a group from the course."""
assert isinstance(group_id, int)
assert isinstance(course_id, int)
sql = "DELETE FROM groupcourses" \
" WHERE groupid=%s AND course=%s;"
params = (group_id, course_id)
run_sql(sql, params)
def set_title(course_id, title):
""" Set the title of a course."""
assert isinstance(course_id, int)
assert isinstance(title, str) or isinstance(title, unicode)
incr_version()
run_sql("UPDATE courses SET description=%s WHERE course=%s;",
(title, course_id))
key = "course-%s-title" % course_id
MC.delete(key)
def delete_perm(uid, group_id, perm):
"""Remove a permission. """
key = "permission-%s-super" % (uid, )
MC.delete(key)
run_sql(
"""DELETE FROM permissions
WHERE userid=%s
AND course=%s
AND permission=%s""", (uid, group_id, perm))
def del_group(group_id, course_id):
""" Remove a group from the course."""
assert isinstance(group_id, int)
assert isinstance(course_id, int)
sql = "DELETE FROM groupcourses" \
" WHERE groupid=%s AND course=%s;"
params = (group_id, course_id)
run_sql(sql, params)
def set_title(course_id, title):
""" Set the title of a course."""
assert isinstance(course_id, int)
assert isinstance(title, str) or isinstance(title, unicode)
incr_version()
run_sql("UPDATE courses SET description=%s WHERE course=%s;",
(title, course_id))
key = "course-%s-title" % course_id
MC.delete(key)
def delete_perm(uid, group_id, perm):
"""Remove a permission. """
key = "permission-%s-super" % (uid,)
MC.delete(key)
run_sql("""DELETE FROM permissions
WHERE userid=%s
AND course=%s
AND permission=%s""",
[uid, group_id, perm])
def add_member(self, uid):
""" Adds given user to the group."""
if uid in self.members():
return
run_sql(
"""INSERT INTO usergroups (userid, groupid)
VALUES (%s, %s) """, (uid, self.id))
def set_password(user_id, clearpass):
""" Updates a users password. """
hashed = bcrypt.hashpw(clearpass, bcrypt.gensalt())
sql = """UPDATE "users" SET "passwd"=%s WHERE "id"=%s;"""
params = (hashed, user_id)
try:
run_sql(sql, params)
except IOError, err:
log(ERROR, "Error settings password for user %s - %s" % (user_id, err))
raise
def add_member(self, uid):
""" Adds given user to the group."""
if uid in self.members():
return
run_sql(
"""INSERT INTO usergroups (userid, groupid)
VALUES (%s, %s) """,
(uid, self.id))
def set_password(user_id, clearpass):
""" Updates a users password. """
hashed = bcrypt.hashpw(clearpass, bcrypt.gensalt(log_rounds=10))
sql = """UPDATE "users" SET "passwd"=%s WHERE "id"=%s;"""
params = (hashed, user_id)
try:
run_sql(sql, params)
except IOError as err:
L.error("Error settings password for user %s - %s" % (user_id, err))
raise
return True
def set_password(user_id, clearpass):
""" Updates a users password. """
hashed = bcrypt.hashpw(clearpass.encode('utf8'), bcrypt.gensalt(10))
sql = """UPDATE "users" SET "passwd" = %s WHERE "id" = %s;"""
params = [hashed, user_id]
try:
run_sql(sql, params)
except IOError as err:
L.error("Error settings password for user %s - %s" % (user_id, err))
raise
return True
def set_prac_vis(cid, visibility):
""" Who can do practice questions.
:param cid: Course ID
:type cid: int
:param visibility: who can see the course in the practice section
"all", "", "", ""
:type visibility: string
"""
assert isinstance(cid, int)
assert isinstance(visibility, str) or isinstance(visibility, unicode)
run_sql("UPDATE courses SET practice_visibility = %s WHERE course = %s;",
[visibility, cid])
def set_active(course_id, active):
""" Set the active flag of a course."""
assert isinstance(course_id, int)
assert isinstance(active, bool)
if active:
val = 1
else:
val = 0
run_sql("UPDATE courses SET active=%s WHERE course=%s;", (val, course_id))
incr_version()
key = "course-%s-active" % course_id
MC.delete(key)
key = "courses-active"
MC.delete(key)
def set_active(course_id, active):
""" Set the active flag of a course."""
assert isinstance(course_id, int)
assert isinstance(active, bool)
if active:
val = 1
else:
val = 0
run_sql("UPDATE courses SET active=%s WHERE course=%s;", (val, course_id))
incr_version()
key = "course-%s-active" % course_id
MC.delete(key)
key = "courses-active"
MC.delete(key)
def verify_password(uname, clearpass):
""" Confirm the password is correct for the given user name.
We first try bcrypt, if it fails we try md5 to see if they have
an old password, and if so, upgrade the stored password to bcrypt.
"""
sql = """SELECT "id", "passwd" FROM "users" WHERE "uname"=%s;"""
params = (uname,)
ret = run_sql(sql, params)
if not ret:
return False
try:
user_id = int(ret[0][0])
except IOError:
L.error("Error fetching user record %s" % uname)
raise
stored_pw = ret[0][1]
if len(stored_pw) > 40: # it's not MD5
hashed = bcrypt.hashpw(clearpass, stored_pw)
if stored_pw == hashed:
# All good, they matched with bcrypt
return user_id
# Might be an old account, check md5
hashgen = hashlib.md5()
hashgen.update(clearpass)
md5hashed = hashgen.hexdigest()
if stored_pw == md5hashed:
# Ok, now we need to upgrade them to something more secure
set_password(user_id, clearpass)
L.info("Upgrading MD5 password to bcrypt for %s" % uname)
return user_id
return False
def get_courses_dict():
""" Return a summary of all courses, keyed by course id
[id] = { 'id':id, 'name':name, 'title':title }
"""
ret = run_sql("""SELECT course, title, description, owner, active, type,
practice_visibility, assess_visibility
FROM courses;""")
cdict = {}
if ret:
for row in ret:
course = {
'id': int(row[0]),
'name': row[1],
'title': row[2],
'owner': row[3],
'active': row[4],
'type': row[5],
'practice_visibility': row[6],
'assess_visibility': row[7]
}
if not course['practice_visibility']:
course['practice_visibility'] = "all"
if not course['assess_visibility']:
course['assess_visibility'] = "all"
cdict[int(row[0])] = course
return cdict
def get_course(course_id):
""" Return a course dict for the given name, or None
{ 'id':id, 'name':name, 'title':title }
"""
ret = run_sql(
"""SELECT course, title, description, owner, active, type,
practice_visibility, assess_visibility
FROM courses
WHERE course=%s;""", (course_id, ))
course = None
if ret:
row = ret[0]
course = {
'id': int(row[0]),
'name': row[1],
'title': row[2],
'owner': row[3],
'active': row[4],
'type': row[5],
'practice_visibility': row[6],
'assess_visibility': row[7]
}
if not course['practice_visibility']:
course['practice_visibility'] = "all"
if not course['assess_visibility']:
course['assess_visibility'] = "all"
return course
def get_course(course_id):
""" Return a course dict for the given name, or None
{ 'id':id, 'name':name, 'title':title }
"""
ret = run_sql(
"""SELECT course, title, description, owner, active, type,
practice_visibility, assess_visibility
FROM courses
WHERE course=%s;""", (course_id,))
course = None
if ret:
row = ret[0]
course = {
'id': int(row[0]),
'name': row[1],
'title': row[2],
'owner': row[3],
'active': row[4],
'type': row[5],
'practice_visibility': row[6],
'assess_visibility': row[7]
}
if not course['practice_visibility']:
course['practice_visibility'] = "all"
if not course['assess_visibility']:
course['assess_visibility'] = "all"
return course
def _fetch_by_id(self, feed_id):
""" If an existing record exists with this id, load it and
return.
"""
sql = """SELECT name, title, script, envvar, comments, freq,
status, error, active
FROM feeds
WHERE id=%s;"""
params = (feed_id, )
ret = run_sql(sql, params)
if not ret:
raise KeyError("Feed with id '%s' not found" % feed_id)
self.id = feed_id
self.name = ret[0][0]
self.title = ret[0][1]
self.script = ret[0][2]
self.envvar = ret[0][3]
self.comments = ret[0][4]
self.freq = ret[0][5]
self.status = ret[0][6]
self.error = ret[0][7]
self.active = ret[0][8]
self.new = False
if not self.name:
self.name = ""
if not self.title:
self.title = ""
return
def verify_password(uname, clearpass):
""" Confirm the password is correct for the given user name.
We first try bcrypt, if it fails we try md5 to see if they have
an old password, and if so, upgrade the stored password to bcrypt.
"""
sql = """SELECT "id", "passwd" FROM "users" WHERE "uname"=%s;"""
params = (uname, )
ret = run_sql(sql, params)
if not ret:
return False
try:
user_id = int(ret[0][0])
except IOError:
L.error("Error fetching user record %s" % uname)
raise
stored_pw = ret[0][1]
if len(stored_pw) > 40: # it's not MD5
hashed = bcrypt.hashpw(clearpass, stored_pw)
if stored_pw == hashed:
# All good, they matched with bcrypt
return user_id
# Might be an old account, check md5
hashgen = hashlib.md5()
hashgen.update(clearpass)
md5hashed = hashgen.hexdigest()
if stored_pw == md5hashed:
# Ok, now we need to upgrade them to something more secure
set_password(user_id, clearpass)
L.info("Upgrading MD5 password to bcrypt for %s" % uname)
return user_id
return False
def _fetch_by_id(self, feed_id):
""" If an existing record exists with this id, load it and
return.
"""
sql = """SELECT name, title, script, envvar, comments, freq,
status, error, active
FROM feeds
WHERE id=%s;"""
params = [feed_id, ]
ret = run_sql(sql, params)
if not ret:
raise KeyError("Feed with id '%s' not found" % feed_id)
self.id = feed_id
self.name = ret[0][0]
self.title = ret[0][1]
self.script = ret[0][2]
self.envvar = ret[0][3]
self.comments = ret[0][4]
self.freq = ret[0][5]
self.status = ret[0][6]
self.error = ret[0][7]
self.active = ret[0][8]
self.new = False
if not self.name:
self.name = ""
if not self.title:
self.title = ""
return
def check_perm(user_id, group_id, perm):
""" Check to see if the user has the permission on the given course. """
permission = 0
if not isinstance(perm, int): # we have a string name so look it up
if perm in PERMS:
permission = PERMS[perm]
key = "permission-%s-super" % user_id
obj = MC.get(key)
if obj:
return True
# If they're superuser, let em do anything
ret = run_sql("""SELECT "id"
FROM permissions
WHERE userid=%s
AND permission=1;""",
[user_id, ])
if ret:
MC.set(key, True)
return True
# If we're asking for course -1 it means any course will do.
if group_id == -1:
ret = run_sql("""SELECT "id"
FROM permissions
WHERE userid=%s
AND permission=%s;""",
[user_id, permission])
if ret:
return True
# Do they have the permission explicitly?
ret = run_sql("""SELECT "id"
FROM permissions
WHERE course=%s
AND userid=%s
AND permission=%s;""",
[group_id, user_id, permission])
if ret:
return True
# Now check for global override
ret = run_sql("""SELECT "id"
FROM permissions
WHERE course=%s
AND userid=%s
AND permission='0';""",
[group_id, user_id])
if ret:
return True
return False
def check_perm(user_id, group_id, perm):
""" Check to see if the user has the permission on the given course. """
permission = 0
if not isinstance(perm, int): # we have a string name so look it up
if perm in PERMS:
permission = PERMS[perm]
key = "permission-%s-super" % user_id
obj = MC.get(key)
if obj:
return True
# If they're superuser, let em do anything
ret = run_sql(
"""SELECT "id"
FROM permissions
WHERE userid=%s
AND permission=1;""", (user_id, ))
if ret:
MC.set(key, True)
return True
# If we're asking for course -1 it means any course will do.
if group_id == -1:
ret = run_sql(
"""SELECT "id"
FROM permissions
WHERE userid=%s
AND permission=%s;""", (user_id, permission))
if ret:
return True
# Do they have the permission explicitly?
ret = run_sql(
"""SELECT "id"
FROM permissions
WHERE course=%s
AND userid=%s
AND permission=%s;""", (group_id, user_id, permission))
if ret:
return True
# Now check for global override
ret = run_sql(
"""SELECT "id"
FROM permissions
WHERE course=%s
AND userid=%s
AND permission='0';""", (group_id, user_id))
if ret:
return True
return False
def members(self):
""" Return a list of userids in the group. """
ret = run_sql("""SELECT userid FROM usergroups WHERE groupid=%s;""",
(self.id,))
if ret:
users = [int(row[0]) for row in ret]
return users
return []
def members(self):
""" Return a list of userids in the group. """
ret = run_sql("""SELECT userid FROM usergroups WHERE groupid=%s;""",
(self.id, ))
if ret:
users = [int(row[0]) for row in ret]
return users
return []
def set_active(course_id, active):
""" Set the active flag of a course.
:param course_id: the course to alter
:type course_id: int
:param active: whether the course is active or not
:type active: bool
"""
assert isinstance(course_id, int)
assert isinstance(active, bool)
if active:
val = 1
else:
val = 0
run_sql("UPDATE courses SET active = %s WHERE course = %s;", [val, course_id])
key = "course-%s-active" % course_id
MC.delete(key)
key = "courses-active"
MC.delete(key)
def period_name(self):
""" Human name for period
"""
sql = """SELECT name FROM periods WHERE id=%s;"""
params = (self.period, )
ret = run_sql(sql, params)
if not ret:
return 'unknown'
return ret[0][0]
def period_name(self):
""" Human name for period
"""
sql = """SELECT name FROM periods WHERE id=%s;"""
params = (self.period,)
ret = run_sql(sql, params)
if not ret:
return 'unknown'
return ret[0][0]
def get_groups(user):
""" Return a list of groups the user is a member of. """
assert isinstance(user, int)
ret = run_sql("""SELECT groupid FROM usergroups WHERE userid=%s;""",
(user, ))
if ret:
groups = [int(row[0]) for row in ret]
return groups
L.warn("Request for unknown user or user in no groups.")
return []
def get_groups(user):
""" Return a list of groups the user is a member of. """
assert isinstance(user, int)
ret = run_sql("""SELECT groupid FROM usergroups WHERE userid=%s;""",
(user,))
if ret:
groups = [int(row[0]) for row in ret]
return groups
L.warn("Request for unknown user or user in no groups.")
return []
def incr_version():
""" Increment the course table version."""
key = "coursetable-version"
MC.delete(key)
ret = run_sql("SELECT nextval('courses_version_seq');")
if ret:
MC.set(key, int(ret[0][0]))
return int(ret[0][0])
L.error("Error incrementing Courses version.")
return -1
def create(uname, passwd, givenname, familyname, acctstatus, studentid,
email=None, expiry=None, source="local",
confirm_code=None, confirm=True):
""" Add a user to the database. """
L.info("Users.py:create(%s)" % uname)
if not confirm_code:
confirm_code = ""
run_sql("""INSERT INTO users (uname, passwd, givenname, familyname,
acctstatus, student_id, email, expiry,
source, confirmation_code, confirmed)
VALUES (%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s);""",
(uname, passwd, givenname, familyname,
acctstatus, studentid, email, expiry,
source, confirm_code, confirm))
incr_version()
uid = uid_by_uname(uname)
L.info("User created with uid %d." % uid)
return uid
def incr_version():
""" Increment the course table version."""
key = "coursetable-version"
MC.delete(key)
ret = run_sql("SELECT nextval('courses_version_seq');")
if ret:
MC.set(key, int(ret[0][0]))
return int(ret[0][0])
L.error("Error incrementing Courses version.")
return -1
def all_groups():
""" Return a summary of all groups
"""
ret = run_sql("""SELECT "id"
FROM "ugroups";""")
groups = []
if ret:
for row in ret:
groups.append(Group(g_id=row[0]))
return groups
def get_by_name(name):
""" Return (the first) group with the given name
"""
sql = """SELECT "id"
FROM "ugroups"
WHERE name=%s;"""
params = (name, )
ret = run_sql(sql, params)
if not ret:
return 0
return Group(g_id=int(ret[0][0]))
def get_records_by_object(obj_id, start=None, end=None, limit=100, offset=0):
""" Return audit records created for (or on behalf of) the object.
If start is provided, only searches for records after start.
If end is also provided, only searches between start and end.
start and end should be datetime or None
obj_id should be the objects id
limit is the maximum number of rows returned, offset is starting
from result n
"""
obj_id = int(obj_id)
if end:
sql = """SELECT "id", "instigator", "module", "longmesg",
"time", "object", "message"
FROM audit
WHERE ("object" = %s)
AND "time" > %s
AND "time" < %s
ORDER BY "time" DESC
LIMIT %s OFFSET %s;"""
params = (obj_id, start, end, limit, offset)
elif start:
sql = """SELECT "id", "instigator", "module", "longmesg",
"time", "object", "message"
FROM audit
WHERE ("object" = %s or "instigator" = %s)
AND "time" > %s
ORDER BY "time" DESC
LIMIT %s OFFSET %s;"""
params = (obj_id, obj_id, start, limit, offset)
else:
sql = """SELECT "id", "instigator", "module", "longmesg",
"time", "object", "message"
FROM audit
WHERE ("object" = %s)
ORDER BY "time" DESC
LIMIT %s OFFSET %s;"""
params = (obj_id, limit, offset)
ret = run_sql(sql, params)
results = []
if ret:
for row in ret:
entry = {
'id': row[0],
'instigator': row[1],
'module': row[2],
'message': row[3],
'time': row[4],
'object': row[5]
}
if not row[3]:
entry['message'] = row[6]
results.append(entry)
return results
def member_unames(self):
""" Return a list of usernames in the group. """
ret = run_sql(
"""SELECT users.uname
FROM users,usergroups
WHERE usergroups.groupid=%s
AND usergroups.userid=users.id;""", (self.id, ))
if ret:
uids = [row[0] for row in ret]
return uids
return []
