Example #1
0
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)
Example #2
0
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))
Example #3
0
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)
Example #4
0
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)
Example #5
0
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)
Example #6
0
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])
Example #7
0
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))
Example #8
0
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)
Example #9
0
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
Example #10
0
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
Example #11
0
def incr_version():
    """ Increment the user table version.
    """
    key = "userstable-version"
    MC.delete(key)
    ret = run_sql("SELECT nextval('users_version_seq');")
    if ret:
        MC.set(key, int(ret[0][0]))
        return int(ret[0][0])
    log(ERROR, "Error incrementing version.")
    return -1
Example #12
0
def uid_by_uname(uname):
    """ Lookup the users internal ID number given their login name. """
    key = "user-%s-unametouid" % (uname,)
    obj = MC.get(key)
    if obj is not None:
        return obj
    ret = run_sql("""SELECT id FROM "users" WHERE uname=%s;""", (uname,))
    if ret:
        MC.set(key, ret[0][0])
        return ret[0][0]
    return None
Example #13
0
def uid_by_uname(uname):
    """ Lookup the users internal ID number given their login name. """
    key = "user-%s-unametouid" % (uname, )
    obj = MC.get(key)
    if obj is not None:
        return obj
    ret = run_sql("""SELECT id FROM "users" WHERE uname=%s;""", (uname, ))
    if ret:
        MC.set(key, ret[0][0])
        return ret[0][0]
    return None
Example #14
0
def uid_by_email(email):
    """ Lookup the users internal ID number given their email address. """
    key = "user-%s-emailtouid" % (email,)
    obj = MC.get(key)
    if obj is not None:
        return obj
    ret = run_sql("""SELECT id FROM "users" WHERE email=%s;""", [email, ])
    if ret:
        MC.set(key, ret[0][0])
        return ret[0][0]
    return None
Example #15
0
def get_user_record(user_id):
    """ Fetch info about the user
        returns  {'id', 'uname', 'givenname', 'lastname', 'fullname'}
    """
    # TODO: clear the cache entry when we change any of these values
    key = "user-%s-record" % (user_id, )
    obj = MC.get(key)
    if obj:
        return json.loads(obj)
    sql = """SELECT id, uname, givenname, familyname, student_id,
                    acctstatus, email, expiry, source, confirmed
                    FROM users
                    WHERE id=%s"""
    params = (user_id, )
    ret = run_sql(sql, params)
    if ret:
        row = ret[0]
        if row[1]:
            uname = unicode(row[1], 'utf-8')
        else:
            uname = u""
        if row[2]:
            givenname = unicode(row[2], 'utf-8')
        else:
            givenname = u""
        if row[3]:
            familyname = unicode(row[3], 'utf-8')
        else:
            familyname = u""
        user_rec = {
            'id': user_id,
            'uname': uname,
            'givenname': givenname,
            'familyname': familyname,
            'fullname': u"%s %s" % (givenname, familyname),
            'student_id': ret[0][4],
            'acctstatus': ret[0][5],
            'email': ret[0][6],
            'expiry': ret[0][7],
            'source': ret[0][8],
            'confirmed': ret[0][9]
        }
        if ret[0][9] is True \
                or ret[0][9] == "true" \
                or ret[0][9] == "TRUE" \
                or ret[0][9] == "" \
                or ret[0][9] is None:

            user_rec['confirmed'] = True
        else:
            user_rec['confirmed'] = False
        MC.set(key, json.dumps(user_rec))
        return user_rec
Example #16
0
def get_user_record(user_id):
    """ Fetch info about the user
        returns  {'id', 'uname', 'givenname', 'lastname', 'fullname'}
    """
    # TODO: clear the cache entry when we change any of these values
    key = "user-%s-record" % (user_id,)
    obj = MC.get(key)
    if obj:
        return json.loads(obj)
    sql = """SELECT id, uname, givenname, familyname, student_id,
                    acctstatus, email, expiry, source, confirmed
                    FROM users
                    WHERE id=%s"""
    params = (user_id,)
    ret = run_sql(sql, params)
    if ret:
        row = ret[0]
        if row[1]:
            uname = unicode(row[1], 'utf-8')
        else:
            uname = u""
        if row[2]:
            givenname = unicode(row[2], 'utf-8')
        else:
            givenname = u""
        if row[3]:
            familyname = unicode(row[3], 'utf-8')
        else:
            familyname = u""
        user_rec = {'id': user_id,
                    'uname': uname,
                    'givenname': givenname,
                    'familyname': familyname,
                    'fullname': u"%s %s" % (givenname, familyname),
                    'student_id': ret[0][4],
                    'acctstatus': ret[0][5],
                    'email': ret[0][6],
                    'expiry': ret[0][7],
                    'source': ret[0][8],
                    'confirmed': ret[0][9]
        }
        if ret[0][9] is True \
            or ret[0][9] == "true" \
            or ret[0][9] == "TRUE" \
            or ret[0][9] == "" \
            or ret[0][9] is None:

            user_rec['confirmed'] = True
        else:
            user_rec['confirmed'] = False
        MC.set(key, json.dumps(user_rec))
        return user_rec
Example #17
0
def get_active(course_id):
    """ Fetch the active flag"""
    assert isinstance(course_id, int)
    key = "course-%s-active" % course_id
    obj = MC.get(key)
    if obj is not None:
        return obj
    ret = run_sql("SELECT active FROM courses WHERE course=%s;", (course_id,))
    if ret:
        MC.set(key, ret[0][0])
        return ret[0][0]
    L.error("Request for active flag of unknown course %s." % course_id)
    return None
Example #18
0
def get_active(course_id):
    """ Fetch the active flag"""
    assert isinstance(course_id, int)
    key = "course-%s-active" % course_id
    obj = MC.get(key)
    if obj is not None:
        return obj
    ret = run_sql("SELECT active FROM courses WHERE course=%s;", (course_id, ))
    if ret:
        MC.set(key, ret[0][0])
        return ret[0][0]
    L.error("Request for active flag of unknown course %s." % course_id)
    return None
Example #19
0
def create(name, description, owner, coursetype):
    """ Add a course to the database."""
    res = run_sql("""INSERT INTO courses (title, description, owner, type)
                     VALUES (%s, %s, %s, %s) RETURNING course;""",
                  (name, description, owner, coursetype))
    incr_version()
    key = "courses-active"
    MC.delete(key)
    key = "courses-all"
    MC.delete(key)
    if res:
        return int(res[0][0])
    L.error("create('%s','%s',%d,%d) Fail" % (name, description, owner, coursetype))
    return 0
Example #20
0
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)
Example #21
0
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)
Example #22
0
def get_topics(cid):
    """ Return a list of all topics in the course."""
    key = "course-%s-topics" % cid
    obj = MC.get(key)
    if obj:
        return obj
    sql = "SELECT topic FROM topics WHERE course=%s ORDER BY position;"
    params = (cid, )
    ret = run_sql(sql, params)
    if ret:
        topics = [row[0] for row in ret]
        MC.set(key, topics)
        return topics
    MC.set(key, [])
    return []
Example #23
0
def get_topics(cid):
    """ Return a list of all topics in the course."""
    key = "course-%s-topics" % cid
    obj = MC.get(key)
    if obj:
        return obj
    sql = "SELECT topic FROM topics WHERE course=%s ORDER BY position;"
    params = (cid,)
    ret = run_sql(sql, params)
    if ret:
        topics = [row[0] for row in ret]
        MC.set(key, topics)
        return topics
    MC.set(key, [])
    return []
Example #24
0
def create(name, description, owner, coursetype):
    """ Add a course to the database."""
    res = run_sql(
        """INSERT INTO courses (title, description, owner, type)
                     VALUES (%s, %s, %s, %s) RETURNING course;""",
        (name, description, owner, coursetype))
    incr_version()
    key = "courses-active"
    MC.delete(key)
    key = "courses-all"
    MC.delete(key)
    if res:
        return int(res[0][0])
    L.error("create('%s','%s',%d,%d) Fail" %
            (name, description, owner, coursetype))
    return 0
Example #25
0
def get_version():
    """ Fetch the current version of the course table.
        This will be incremented when anything in the courses table is changed.
        The idea is that while the version hasn't changed, course information
        can be cached in memory.
    """
    key = "coursetable-version"
    obj = MC.get(key)
    if obj:
        return int(obj)
    ret = run_sql("SELECT last_value FROM courses_version_seq;")
    if ret:
        MC.set(key, int(ret[0][0]))
        return int(ret[0][0])
    L.error("Error fetching Courses version.")
    return -1
Example #26
0
def get_version():
    """ Fetch the current version of the course table.
        This will be incremented when anything in the courses table is changed.
        The idea is that while the version hasn't changed, course information
        can be cached in memory.
    """
    key = "coursetable-version"
    obj = MC.get(key)
    if obj:
        return int(obj)
    ret = run_sql("SELECT last_value FROM courses_version_seq;")
    if ret:
        MC.set(key, int(ret[0][0]))
        return int(ret[0][0])
    L.error("Error fetching Courses version.")
    return -1
Example #27
0
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
Example #28
0
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
Example #29
0
def get_all(only_active=True):
    """ Return a list of all courses in the system."""
    if only_active:
        sql = """SELECT course FROM courses WHERE active=1 ORDER BY title;"""
        key = "courses-active"
    else:
        sql = """SELECT course FROM courses ORDER BY title;"""
        key = "courses-all"
    obj = MC.get(key)
    if obj:
        return obj
    ret = run_sql(sql)
    if ret:
        courses = [int(row[0]) for row in ret]
        MC.set(key, courses)
        return courses
    return []
Example #30
0
def get_all(only_active=True):
    """ Return a list of all courses in the system."""
    if only_active:
        sql = """SELECT course FROM courses WHERE active=1 ORDER BY title;"""
        key = "courses-active"
    else:
        sql = """SELECT course FROM courses ORDER BY title;"""
        key = "courses-all"
    obj = MC.get(key)
    if obj:
        return obj
    ret = run_sql(sql)
    if ret:
        courses = [int(row[0]) for row in ret]
        MC.set(key, courses)
        return courses
    return []
Example #31
0
def get_version():
    """ Fetch the current version of the user table.
        This will be incremented when anything in the users table is changed.
        The idea is that while the version hasn't changed, user information
        can be cached in memory.
    """
    key = "userstable-version"
    obj = MC.get(key)
    if not obj is None:
        return int(obj)

    ret = run_sql("SELECT last_value FROM users_version_seq;")
    if ret:
        MC.set(key, int(ret[0][0]))
        return int(ret[0][0])
    log(ERROR, "Error fetching version.")
    return -1
Example #32
0
def create(name, description, owner, coursetype):
    """ Add a course to the database."""
    conn = dbpool.begin()
    conn.run_sql("""INSERT INTO courses (title, description, owner, type)
                    VALUES (%s, %s, %s, %s);""",
                    (name, description, owner, coursetype))
    res = conn.run_sql("SELECT currval('courses_course_seq')")
    dbpool.commit(conn)
    incr_version()
    key = "courses-active"
    MC.delete(key)
    key = "courses-all"
    MC.delete(key)
    if res:
        return int(res[0][0])
    log(ERROR,
        "create('%s','%s',%d,%d) Fail" % (name, description, owner, coursetype))
    return 0
Example #33
0
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)
Example #34
0
def get_user_record(user_id):
    """ Fetch info about the user
        returns  {'id', 'uname', 'givenname', 'lastname', 'fullname', and more!}
    """
    # TODO: clear the cache entry when we change any of these values
    key = "user-%s-record" % (user_id,)
    obj = MC.get(key)
    if obj:
        return json.loads(obj)
    sql = """SELECT id, uname, givenname, familyname, student_id,
                    acctstatus, email, expiry, source, confirmed, display_name
                    FROM users
                    WHERE id=%s"""
    params = [user_id, ]
    ret = run_sql(sql, params)
    if ret:
        fullname = ""
        display_name = ""
        row = ret[0]
        if row[1]:
            uname = unicode(row[1], 'utf-8')
        else:
            uname = u""
        if row[2]:
            givenname = unicode(row[2], 'utf-8')
        else:
            givenname = u""
        if row[3]:
            familyname = unicode(row[3], 'utf-8')
        else:
            familyname = u""
        if row[10]:
            display_name = unicode(row[10], 'utf-8')
        if len(givenname)>0 or len(familyname) > 0:
            fullname = u"%s %s" % (givenname, familyname)

        user_rec = {'id': user_id,
                    'uname': uname,
                    'givenname': givenname,
                    'familyname': familyname,
                    'fullname': fullname,
                    'student_id': ret[0][4],
                    'acctstatus': ret[0][5],
                    'email': ret[0][6],
                    'expiry': ret[0][7],
                    'source': ret[0][8],
                    'confirmed': ret[0][9],
                    'display_name': display_name
                    }
        if ret[0][9] is True \
                or ret[0][9] == "true" \
                or ret[0][9] == "TRUE" \
                or ret[0][9] == "" \
                or ret[0][9] is None:

            user_rec['confirmed'] = True
        else:
            user_rec['confirmed'] = False

        if not display_name:
            if len(fullname) > 0:
                user_rec['display_name'] = fullname
            elif len(user_rec['email']) > 0:
                user_rec['display_name'] = user_rec['email']
            else:
                user_rec['display_name'] = "Unknown"

        MC.set(key, json.dumps(user_rec))
        return user_rec
Example #35
0
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])