def add_ssh_key(userid, ssh_key, title): sql = """ INSERT INTO ssh_keys (userid, ssh_key, title) VALUES (?, ?, ?) """ storage.execute(storage.db.cursor(), sql, (userid, ssh_key, title))
def remove(userid): storage.execute( storage.db.cursor(), """DELETE FROM users WHERE id=?""", (userid,), )
def remove_permissions_repository(userid): storage.execute( storage.db.cursor(), """DELETE FROM permissions WHERE subjecttype="user" AND subjectid=?""", (userid,), )
def clear_password_reset_key(userid): storage.execute( storage.db.cursor(), """DELETE FROM password_reset WHERE userid=?""", (userid,), )
def remove_from_groups(userid): storage.execute( storage.db.cursor(), """DELETE FROM group_members WHERE userid=?""", (userid,), )
def remove_permissions_submin(userid): storage.execute( storage.db.cursor(), """DELETE FROM managers WHERE managertype="user" AND managerid=?""", (userid,), )
def remove_notifications(userid): storage.execute( storage.db.cursor(), """DELETE FROM notifications WHERE userid=?""", (userid,), )
def list_permissions(repos, repostype, path): cur = storage.db.cursor() queries = [ """SELECT users.name, subjecttype, type FROM permissions LEFT JOIN users ON permissions.subjectid = users.id WHERE repository = ? AND repositorytype = ? AND path = ? AND subjecttype = 'user'""", """SELECT groups.name, subjecttype, type FROM permissions LEFT JOIN groups ON permissions.subjectid = groups.id WHERE repository = ? AND repositorytype = ? AND path = ? AND subjecttype = 'group'""", """SELECT '*', subjecttype, type FROM permissions WHERE repository = ? AND repositorytype = ? AND path = ? AND subjecttype = 'all'""" ] rows = [] for query in queries: storage.execute(cur, query, (repos, repostype, path)) current_rows = cur.fetchall() if current_rows: rows.extend(current_rows) if not rows: return [] return [{'name': row[0], 'type': row[1], 'permission': row[2]} for row in rows]
def remove_all_ssh_keys(userid): storage.execute( storage.db.cursor(), """DELETE FROM ssh_keys WHERE userid=?""", (userid,), )
def set_md5_password(userid, password): storage.execute( storage.db.cursor(), """UPDATE users SET password=? WHERE id=?""", (password, userid), )
def ssh_keys(userid): cur = storage.db.cursor() storage.execute(cur, "SELECT id, ssh_key, title FROM ssh_keys WHERE userid=?", (userid,)) keys = [] for key in cur: keys.append({"id": key[0], "key": key[1], "title": key[2]}) return keys
def get(key): cur = storage.db.cursor() storage.execute(cur, "SELECT value, expires FROM sessions WHERE key=?", (key,)) row = cur.fetchone() if not row: raise UnknownKeyError(key) return (row[0], row[1])
def set_password_reset_key(userid, key): storage.execute( storage.db.cursor(), """INSERT OR REPLACE INTO password_reset (userid, expires, key) VALUES (?, strftime('%s', 'now', '+1 days'), ?)""", (userid, key), )
def add_permission(repos, repostype, path, subject, subjecttype, perm): cur = storage.db.cursor() subjectid = _subject_to_id(subject, subjecttype) storage.execute(cur, """INSERT INTO permissions (repository, repositorytype, path, subjectid, subjecttype, type) VALUES (?, ?, ?, ?, ?, ?)""", (repos, repostype, path, subjectid, subjecttype, perm))
def value(key): cur = storage.db.cursor() storage.execute(cur, "SELECT value FROM options WHERE key=?", (key,)) row = cur.fetchone() if not row: raise UnknownKeyError(key) return row[0]
def options(): cur = storage.db.cursor() storage.execute(cur, "SELECT key, value FROM options") row = cur.fetchall() if not row: return [] return row
def rename(oldgroupname, newgroupname): if not group_data(oldgroupname): raise UnknownGroupError("Group '%s' does not exist" % oldgroupname) try: storage.execute(storage.db.cursor(), \ "UPDATE groups SET name=? WHERE name=?", (newgroupname, oldgroupname)) except storage.SQLIntegrityError as e: raise GroupExistsError("Group '%s' already exists" % newgroupname)
def list_paths(repository, repostype): cur = storage.db.cursor() storage.execute(cur, """SELECT path FROM permissions WHERE repository = ? AND repositorytype = ? GROUP BY path""", (repository, repostype)) rows = cur.fetchall() if not rows: return [] return [x[0] for x in rows]
def get(key): cur = storage.db.cursor() storage.execute(cur, "SELECT value, expires FROM sessions WHERE key=?", (key, )) row = cur.fetchone() if not row: raise UnknownKeyError(key) return (row[0], row[1])
def check_password(userid, password): cur = storage.db.cursor() storage.execute(cur, "SELECT password FROM users WHERE id=?", (userid,)) row = cur.fetchone() vals = row[0][1:].split('$') if not len(vals) == 3: raise NoMD5PasswordError magic, salt, encrypted = vals return _pw_hash(password, salt, magic) == row[0]
def check_password(userid, password): cur = storage.db.cursor() storage.execute(cur, "SELECT password FROM users WHERE id=?", (userid,)) row = cur.fetchone() vals = row[0][1:].split("$") if not len(vals) == 3: raise NoMD5PasswordError magic, salt, encrypted = vals return _pw_hash(password, salt, magic) == row[0]
def set_field_bool(userid, value): if value == True or value == "true" or value == "1": value = 1 else: value = 0 cur = storage.db.cursor() sql = "UPDATE users SET %s=? WHERE id=?" % field storage.execute(cur, sql, (value, userid))
def set_field_bool(userid, value): if value == True or value == 'true' or value == '1': value = 1 else: value = 0 cur = storage.db.cursor() sql = "UPDATE users SET %s=? WHERE id=?" % field storage.execute(cur, sql, (value, userid))
def set_notification(userid, repository, vcstype, enabled, commit=True): if enabled: storage.execute(storage.db.cursor(), """INSERT OR REPLACE INTO notifications (userid, repository, repositorytype) VALUES (?, ?, ?)""", (userid, repository, vcstype), commit) else: storage.execute(storage.db.cursor(), """DELETE FROM notifications WHERE userid=? AND repository=? AND repositorytype=?""", (userid, repository, vcstype), commit)
def add_member(groupid, userid): try: storage.execute(storage.db.cursor(), """ INSERT INTO group_members (groupid, userid) VALUES (?, ?) """, (groupid, userid)) except sqlite3.IntegrityError: raise MemberExistsError
def jobs(repositorytype, repository, hooktype): cur = storage.db.cursor() storage.execute(cur, """SELECT jobid, content FROM hook_jobs WHERE repositorytype=? AND repository=? AND hooktype=?""", (repositorytype, repository, hooktype)) row = cur.fetchall() if not row: return [] return row
def list(): """Generator for sorted list of groups""" cur = storage.db.cursor() storage.execute(cur, """ SELECT %s FROM groups ORDER BY name ASC """ % all_fields) for x in cur: yield row_dict(cur, x)
def list(): """Generator for sorted list of users""" cur = storage.db.cursor() storage.execute(cur, """ SELECT %s FROM users ORDER BY name ASC """ % all_fields) for x in cur: yield row_dict(cur, x)
def add(username, password): if password: password = _pw_hash(password) else: password = "" cur = storage.db.cursor() try: storage.execute(cur, "INSERT INTO users (name, password) VALUES (?, ?)", (username, password)) except storage.SQLIntegrityError as e: raise UserExistsError("User `%s' already exists" % username)
def group_data(groupname): cur = storage.db.cursor() storage.execute(cur, """ SELECT %s FROM groups WHERE name=?""" % all_fields, (groupname,)) row = cur.fetchone() if not row: return None return row_dict(cur, row)
def cleanup(limit): """Remove stale entries, limiting to *limit*""" try: storage.execute(storage.db.cursor(), """DELETE FROM sessions WHERE expires <= strftime('%s', 'now') LIMIT UP TO ?""", (limit, )) except sqlite3.OperationalError as e: # Assume there is no SQLITE_ENABLE_UPDATE_DELETE_LIMIT support, # retry without LIMIT storage.execute(storage.db.cursor(), """DELETE FROM sessions WHERE expires <= strftime('%s', 'now')""")
def add_member(groupid, userid): try: storage.execute( storage.db.cursor(), """ INSERT INTO group_members (groupid, userid) VALUES (?, ?) """, (groupid, userid)) except sqlite3.IntegrityError: raise MemberExistsError
def user_data(username): cur = storage.db.cursor() storage.execute(cur, """ SELECT %s FROM users WHERE name=?""" % all_fields, (username,)) row = cur.fetchone() if not row: return None return row_dict(cur, row)
def valid_password_reset_key(userid, key): cur = storage.db.cursor() # first delete all expired keys, so we clean up the database # and make it simpler to select keys (because we don't have to check # for expiry) storage.execute(cur, """DELETE FROM password_reset WHERE expires <= strftime('%s', 'now')""",) storage.execute(cur, """SELECT count(key) FROM password_reset WHERE userid=? AND key=?""", (userid, key)) row = cur.fetchone() return (row[0] == 1)
def cleanup(limit): """Remove stale entries, limiting to *limit*""" try: storage.execute( storage.db.cursor(), """DELETE FROM sessions WHERE expires <= strftime('%s', 'now') LIMIT UP TO ?""", (limit, )) except sqlite3.OperationalError as e: # Assume there is no SQLITE_ENABLE_UPDATE_DELETE_LIMIT support, # retry without LIMIT storage.execute( storage.db.cursor(), """DELETE FROM sessions WHERE expires <= strftime('%s', 'now')""")
def notification(userid, repository, vcstype): cur = storage.db.cursor() storage.execute(cur, """ SELECT 1 FROM notifications WHERE userid=? AND repository=? and repositorytype=?""", (userid, repository, vcstype)) row = cur.fetchone() if not row: return False return True
def members(groupid): """Returns a sorted list of usernames, which are members of the group with id <groupid>""" cur = storage.db.cursor() storage.execute(cur, """ SELECT users.name FROM group_members LEFT JOIN users ON group_members.userid = users.id WHERE group_members.groupid = ? ORDER BY users.name ASC """, (groupid,)) for x in cur: yield x[0]
def members(groupid): """Returns a sorted list of usernames, which are members of the group with id <groupid>""" cur = storage.db.cursor() storage.execute( cur, """ SELECT users.name FROM group_members LEFT JOIN users ON group_members.userid = users.id WHERE group_members.groupid = ? ORDER BY users.name ASC """, (groupid, )) for x in cur: yield x[0]
def notification(userid, repository, vcstype): cur = storage.db.cursor() storage.execute( cur, """ SELECT 1 FROM notifications WHERE userid=? AND repository=? and repositorytype=?""", (userid, repository, vcstype), ) row = cur.fetchone() if not row: return False return True
def user_data(username): cur = storage.db.cursor() storage.execute( cur, """ SELECT %s FROM users WHERE name=?""" % all_fields, (username,), ) row = cur.fetchone() if not row: return None return row_dict(cur, row)
def _subject_to_id(subject, subjecttype): cur = storage.db.cursor() tables = {'user': '******', 'group': 'groups', 'all': None} table = tables[subjecttype] if table != None: storage.execute(cur, "SELECT id FROM %s WHERE name = ?" % table, (subject,)) row = cur.fetchone() if not row: raise Exception("Unknown %s: %s" % (subjecttype, subject)) subjectid = row[0] else: subjectid = None return subjectid
def remove_permission(repos, repostype, path, subject, subjecttype): cur = storage.db.cursor() subjectid = _subject_to_id(subject, subjecttype) # testing for 'X = NULL' fails, should use 'X IS NULL' # but if we use ? for that case, we get the following error: # OperationalError: near "?": syntax error # so instead we use this horrid construction test = "subjectid = ?" variables = (repos, repostype, path, subjectid, subjecttype) if not subjectid: test = "subjectid IS NULL" variables = (repos, repostype, path, subjecttype) storage.execute(cur, """DELETE FROM permissions WHERE repository = ? AND repositorytype = ? AND path = ? AND %s AND subjecttype = ?""" % test, variables)
def list_permissions_by_user(username): cur = storage.db.cursor() userid = _subject_to_id(username, 'user') storage.execute(cur, """SELECT p.repository, p.repositorytype, p.path, p.type FROM permissions AS p LEFT JOIN group_members AS gm ON p.subjectid=gm.groupid WHERE (subjecttype = 'group' AND gm.userid = ?) OR (subjecttype = 'user' and p.subjectid = ?)""", (userid, userid)) rows = cur.fetchall() if not rows: return for row in rows: yield { 'repository': row[0], 'vcs': row[1], 'path': row[2], 'permission': row[3] }
def list_permissions_by_group(groupname): cur = storage.db.cursor() groupid = _subject_to_id(groupname, 'group') storage.execute( cur, """SELECT p.repository, p.repositorytype, p.path, p.type FROM permissions AS p LEFT JOIN groups AS g ON p.subjectid=g.id WHERE p.subjecttype = 'group' AND g.id = ? ORDER BY p.repositorytype, p.repository, p.path""", (groupid, )) rows = cur.fetchall() if not rows: return for row in rows: yield { 'repository': row[0], 'vcs': row[1], 'path': row[2], 'permission': row[3] }
def remove(groupid): storage.execute(storage.db.cursor(), """DELETE FROM groups WHERE id=?""", (groupid, ))
def remove_members_from_group(groupid): storage.execute(storage.db.cursor(), """DELETE FROM group_members WHERE groupid=?""", (groupid, ))
def remove_managers(groupid): storage.execute( storage.db.cursor(), """DELETE FROM managers WHERE managertype="group" AND managerid=?""", (groupid, ))
def remove_permissions(groupid): storage.execute( storage.db.cursor(), """DELETE FROM permissions WHERE subjecttype="group" AND subjectid=?""", (groupid, ))
def add(groupname): try: storage.execute(storage.db.cursor(), \ "INSERT INTO groups (name) VALUES (?)", (groupname,)) except storage.SQLIntegrityError as e: raise GroupExistsError("Group '%s' already exists" % groupname)