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_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 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 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 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 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 []