14 files changed, 1639 insertions, 0 deletions
diff --git a/aurweb/__init__.py b/aurweb/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/aurweb/__init__.py
diff --git a/aurweb/config.py b/aurweb/config.py
new file mode 100644
index 0000000..a52d942
--- /dev/null
+++ b/aurweb/config.py
@@ -0,0 +1,30 @@
+import configparser
+import os
+
+_parser = None
+
+
+def _get_parser():
+    global _parser
+
+    if not _parser:
+        _parser = configparser.RawConfigParser()
+        if 'AUR_CONFIG' in os.environ:
+            path = os.environ.get('AUR_CONFIG')
+        else:
+            path = "/etc/aurweb/config"
+        _parser.read(path)
+
+    return _parser
+
+
+def get(section, option):
+    return _get_parser().get(section, option)
+
+
+def getboolean(section, option):
+    return _get_parser().getboolean(section, option)
+
+
+def getint(section, option):
+    return _get_parser().getint(section, option)
diff --git a/aurweb/db.py b/aurweb/db.py
new file mode 100644
index 0000000..0b58197
--- /dev/null
+++ b/aurweb/db.py
@@ -0,0 +1,51 @@
+import mysql.connector
+import sqlite3
+
+import aurweb.config
+
+
+class Connection:
+    _conn = None
+    _paramstyle = None
+
+    def __init__(self):
+        aur_db_backend = aurweb.config.get('database', 'backend')
+
+        if aur_db_backend == 'mysql':
+            aur_db_host = aurweb.config.get('database', 'host')
+            aur_db_name = aurweb.config.get('database', 'name')
+            aur_db_user = aurweb.config.get('database', 'user')
+            aur_db_pass = aurweb.config.get('database', 'password')
+            aur_db_socket = aurweb.config.get('database', 'socket')
+            self._conn = mysql.connector.connect(host=aur_db_host,
+                                                 user=aur_db_user,
+                                                 passwd=aur_db_pass,
+                                                 db=aur_db_name,
+                                                 unix_socket=aur_db_socket,
+                                                 buffered=True)
+            self._paramstyle = mysql.connector.paramstyle
+        elif aur_db_backend == 'sqlite':
+            aur_db_name = aurweb.config.get('database', 'name')
+            self._conn = sqlite3.connect(aur_db_name)
+            self._paramstyle = sqlite3.paramstyle
+        else:
+            raise ValueError('unsupported database backend')
+
+    def execute(self, query, params=()):
+        if self._paramstyle in ('format', 'pyformat'):
+            query = query.replace('%', '%%').replace('?', '%s')
+        elif self._paramstyle == 'qmark':
+            pass
+        else:
+            raise ValueError('unsupported paramstyle')
+
+        cur = self._conn.cursor()
+        cur.execute(query, params)
+
+        return cur
+
+    def commit(self):
+        self._conn.commit()
+
+    def close(self):
+        self._conn.close()
diff --git a/aurweb/git/__init__.py b/aurweb/git/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/aurweb/git/__init__.py
diff --git a/aurweb/git/auth.py b/aurweb/git/auth.py
new file mode 100755
index 0000000..022b0ff
--- /dev/null
+++ b/aurweb/git/auth.py
@@ -0,0 +1,62 @@
+#!/usr/bin/python3
+
+import shlex
+import re
+import sys
+
+import aurweb.config
+import aurweb.db
+
+
+def format_command(env_vars, command, ssh_opts, ssh_key):
+    environment = ''
+    for key, var in env_vars.items():
+        environment += '{}={} '.format(key, shlex.quote(var))
+
+    command = shlex.quote(command)
+    command = '{}{}'.format(environment, command)
+
+    # The command is being substituted into an authorized_keys line below,
+    # so we need to escape the double quotes.
+    command = command.replace('"', '\\"')
+    msg = 'command="{}",{} {}'.format(command, ssh_opts, ssh_key)
+    return msg
+
+
+def main():
+    valid_keytypes = aurweb.config.get('auth', 'valid-keytypes').split()
+    username_regex = aurweb.config.get('auth', 'username-regex')
+    git_serve_cmd = aurweb.config.get('auth', 'git-serve-cmd')
+    ssh_opts = aurweb.config.get('auth', 'ssh-options')
+
+    keytype = sys.argv[1]
+    keytext = sys.argv[2]
+    if keytype not in valid_keytypes:
+        exit(1)
+
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT Users.Username, Users.AccountTypeID FROM Users "
+                       "INNER JOIN SSHPubKeys ON SSHPubKeys.UserID = Users.ID "
+                       "WHERE SSHPubKeys.PubKey = ? AND Users.Suspended = 0",
+                       (keytype + " " + keytext,))
+
+    row = cur.fetchone()
+    if not row or cur.fetchone():
+        exit(1)
+
+    user, account_type = row
+    if not re.match(username_regex, user):
+        exit(1)
+
+    env_vars = {
+        'AUR_USER': user,
+        'AUR_PRIVILEGED': '1' if account_type > 1 else '0',
+    }
+    key = keytype + ' ' + keytext
+
+    print(format_command(env_vars, git_serve_cmd, ssh_opts, key))
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/git/serve.py b/aurweb/git/serve.py
new file mode 100755
index 0000000..476aea8
--- /dev/null
+++ b/aurweb/git/serve.py
@@ -0,0 +1,451 @@
+#!/usr/bin/python3
+
+import os
+import re
+import shlex
+import subprocess
+import sys
+import time
+
+import aurweb.config
+import aurweb.db
+
+notify_cmd = aurweb.config.get('notifications', 'notify-cmd')
+
+repo_path = aurweb.config.get('serve', 'repo-path')
+repo_regex = aurweb.config.get('serve', 'repo-regex')
+git_shell_cmd = aurweb.config.get('serve', 'git-shell-cmd')
+git_update_cmd = aurweb.config.get('serve', 'git-update-cmd')
+ssh_cmdline = aurweb.config.get('serve', 'ssh-cmdline')
+
+enable_maintenance = aurweb.config.getboolean('options', 'enable-maintenance')
+maintenance_exc = aurweb.config.get('options', 'maintenance-exceptions').split()
+
+
+def pkgbase_from_name(pkgbase):
+    conn = aurweb.db.Connection()
+    cur = conn.execute("SELECT ID FROM PackageBases WHERE Name = ?", [pkgbase])
+
+    row = cur.fetchone()
+    return row[0] if row else None
+
+
+def pkgbase_exists(pkgbase):
+    return pkgbase_from_name(pkgbase) is not None
+
+
+def list_repos(user):
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    userid = cur.fetchone()[0]
+    if userid == 0:
+        die('{:s}: unknown user: {:s}'.format(action, user))
+
+    cur = conn.execute("SELECT Name, PackagerUID FROM PackageBases " +
+                       "WHERE MaintainerUID = ?", [userid])
+    for row in cur:
+        print((' ' if row[1] else '*') + row[0])
+    conn.close()
+
+
+def create_pkgbase(pkgbase, user):
+    if not re.match(repo_regex, pkgbase):
+        die('{:s}: invalid repository name: {:s}'.format(action, pkgbase))
+    if pkgbase_exists(pkgbase):
+        die('{:s}: package base already exists: {:s}'.format(action, pkgbase))
+
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    userid = cur.fetchone()[0]
+    if userid == 0:
+        die('{:s}: unknown user: {:s}'.format(action, user))
+
+    now = int(time.time())
+    cur = conn.execute("INSERT INTO PackageBases (Name, SubmittedTS, " +
+                       "ModifiedTS, SubmitterUID, MaintainerUID) VALUES " +
+                       "(?, ?, ?, ?, ?)", [pkgbase, now, now, userid, userid])
+    pkgbase_id = cur.lastrowid
+
+    cur = conn.execute("INSERT INTO PackageNotifications " +
+                       "(PackageBaseID, UserID) VALUES (?, ?)",
+                       [pkgbase_id, userid])
+
+    conn.commit()
+    conn.close()
+
+
+def pkgbase_adopt(pkgbase, user, privileged):
+    pkgbase_id = pkgbase_from_name(pkgbase)
+    if not pkgbase_id:
+        die('{:s}: package base not found: {:s}'.format(action, pkgbase))
+
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT ID FROM PackageBases WHERE ID = ? AND " +
+                       "MaintainerUID IS NULL", [pkgbase_id])
+    if not privileged and not cur.fetchone():
+        die('{:s}: permission denied: {:s}'.format(action, user))
+
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    userid = cur.fetchone()[0]
+    if userid == 0:
+        die('{:s}: unknown user: {:s}'.format(action, user))
+
+    cur = conn.execute("UPDATE PackageBases SET MaintainerUID = ? " +
+                       "WHERE ID = ?", [userid, pkgbase_id])
+
+    cur = conn.execute("SELECT COUNT(*) FROM PackageNotifications WHERE " +
+                       "PackageBaseID = ? AND UserID = ?",
+                       [pkgbase_id, userid])
+    if cur.fetchone()[0] == 0:
+        cur = conn.execute("INSERT INTO PackageNotifications " +
+                           "(PackageBaseID, UserID) VALUES (?, ?)",
+                           [pkgbase_id, userid])
+    conn.commit()
+
+    subprocess.Popen((notify_cmd, 'adopt', str(pkgbase_id), str(userid)))
+
+    conn.close()
+
+
+def pkgbase_get_comaintainers(pkgbase):
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT UserName FROM PackageComaintainers " +
+                       "INNER JOIN Users " +
+                       "ON Users.ID = PackageComaintainers.UsersID " +
+                       "INNER JOIN PackageBases " +
+                       "ON PackageBases.ID = PackageComaintainers.PackageBaseID " +
+                       "WHERE PackageBases.Name = ? " +
+                       "ORDER BY Priority ASC", [pkgbase])
+
+    return [row[0] for row in cur.fetchall()]
+
+
+def pkgbase_set_comaintainers(pkgbase, userlist, user, privileged):
+    pkgbase_id = pkgbase_from_name(pkgbase)
+    if not pkgbase_id:
+        die('{:s}: package base not found: {:s}'.format(action, pkgbase))
+
+    if not privileged and not pkgbase_has_full_access(pkgbase, user):
+        die('{:s}: permission denied: {:s}'.format(action, user))
+
+    conn = aurweb.db.Connection()
+
+    userlist_old = set(pkgbase_get_comaintainers(pkgbase))
+
+    uids_old = set()
+    for olduser in userlist_old:
+        cur = conn.execute("SELECT ID FROM Users WHERE Username = ?",
+                           [olduser])
+        userid = cur.fetchone()[0]
+        if userid == 0:
+            die('{:s}: unknown user: {:s}'.format(action, user))
+        uids_old.add(userid)
+
+    uids_new = set()
+    for newuser in userlist:
+        cur = conn.execute("SELECT ID FROM Users WHERE Username = ?",
+                           [newuser])
+        userid = cur.fetchone()[0]
+        if userid == 0:
+            die('{:s}: unknown user: {:s}'.format(action, user))
+        uids_new.add(userid)
+
+    uids_add = uids_new - uids_old
+    uids_rem = uids_old - uids_new
+
+    i = 1
+    for userid in uids_new:
+        if userid in uids_add:
+            cur = conn.execute("INSERT INTO PackageComaintainers " +
+                               "(PackageBaseID, UsersID, Priority) " +
+                               "VALUES (?, ?, ?)", [pkgbase_id, userid, i])
+            subprocess.Popen((notify_cmd, 'comaintainer-add', str(pkgbase_id),
+                              str(userid)))
+        else:
+            cur = conn.execute("UPDATE PackageComaintainers " +
+                               "SET Priority = ? " +
+                               "WHERE PackageBaseID = ? AND UsersID = ?",
+                               [i, pkgbase_id, userid])
+        i += 1
+
+    for userid in uids_rem:
+            cur = conn.execute("DELETE FROM PackageComaintainers " +
+                               "WHERE PackageBaseID = ? AND UsersID = ?",
+                               [pkgbase_id, userid])
+            subprocess.Popen((notify_cmd, 'comaintainer-remove',
+                              str(pkgbase_id), str(userid)))
+
+    conn.commit()
+    conn.close()
+
+
+def pkgreq_by_pkgbase(pkgbase_id, reqtype):
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT PackageRequests.ID FROM PackageRequests " +
+                       "INNER JOIN RequestTypes ON " +
+                       "RequestTypes.ID = PackageRequests.ReqTypeID " +
+                       "WHERE PackageRequests.Status = 0 " +
+                       "AND PackageRequests.PackageBaseID = ?" +
+                       "AND RequestTypes.Name = ?", [pkgbase_id, reqtype])
+
+    return [row[0] for row in cur.fetchall()]
+
+
+def pkgreq_close(reqid, reason, comments, autoclose=False):
+    statusmap = {'accepted': 2, 'rejected': 3}
+    if reason not in statusmap:
+        die('{:s}: invalid reason: {:s}'.format(action, reason))
+    status = statusmap[reason]
+
+    conn = aurweb.db.Connection()
+
+    if autoclose:
+        userid = 0
+    else:
+        cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+        userid = cur.fetchone()[0]
+        if userid == 0:
+            die('{:s}: unknown user: {:s}'.format(action, user))
+
+    conn.execute("UPDATE PackageRequests SET Status = ?, ClosureComment = ? " +
+                 "WHERE ID = ?", [status, comments, reqid])
+    conn.commit()
+    conn.close()
+
+    subprocess.Popen((notify_cmd, 'request-close', str(userid), str(reqid),
+                      reason)).wait()
+
+
+def pkgbase_disown(pkgbase, user, privileged):
+    pkgbase_id = pkgbase_from_name(pkgbase)
+    if not pkgbase_id:
+        die('{:s}: package base not found: {:s}'.format(action, pkgbase))
+
+    initialized_by_owner = pkgbase_has_full_access(pkgbase, user)
+    if not privileged and not initialized_by_owner:
+        die('{:s}: permission denied: {:s}'.format(action, user))
+
+    # TODO: Support disowning package bases via package request.
+
+    # Scan through pending orphan requests and close them.
+    comment = 'The user {:s} disowned the package.'.format(user)
+    for reqid in pkgreq_by_pkgbase(pkgbase_id, 'orphan'):
+        pkgreq_close(reqid, 'accepted', comment, True)
+
+    comaintainers = []
+    new_maintainer_userid = None
+
+    conn = aurweb.db.Connection()
+
+    # Make the first co-maintainer the new maintainer, unless the action was
+    # enforced by a Trusted User.
+    if initialized_by_owner:
+        comaintainers = pkgbase_get_comaintainers(pkgbase)
+        if len(comaintainers) > 0:
+            new_maintainer = comaintainers[0]
+            cur = conn.execute("SELECT ID FROM Users WHERE Username = ?",
+                               [new_maintainer])
+            new_maintainer_userid = cur.fetchone()[0]
+            comaintainers.remove(new_maintainer)
+
+    pkgbase_set_comaintainers(pkgbase, comaintainers, user, privileged)
+    cur = conn.execute("UPDATE PackageBases SET MaintainerUID = ? " +
+                       "WHERE ID = ?", [new_maintainer_userid, pkgbase_id])
+
+    conn.commit()
+
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    userid = cur.fetchone()[0]
+    if userid == 0:
+        die('{:s}: unknown user: {:s}'.format(action, user))
+
+    subprocess.Popen((notify_cmd, 'disown', str(pkgbase_id), str(userid)))
+
+    conn.close()
+
+
+def pkgbase_set_keywords(pkgbase, keywords):
+    pkgbase_id = pkgbase_from_name(pkgbase)
+    if not pkgbase_id:
+        die('{:s}: package base not found: {:s}'.format(action, pkgbase))
+
+    conn = aurweb.db.Connection()
+
+    conn.execute("DELETE FROM PackageKeywords WHERE PackageBaseID = ?",
+                 [pkgbase_id])
+    for keyword in keywords:
+        conn.execute("INSERT INTO PackageKeywords (PackageBaseID, Keyword) " +
+                     "VALUES (?, ?)", [pkgbase_id, keyword])
+
+    conn.commit()
+    conn.close()
+
+
+def pkgbase_has_write_access(pkgbase, user):
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT COUNT(*) FROM PackageBases " +
+                       "LEFT JOIN PackageComaintainers " +
+                       "ON PackageComaintainers.PackageBaseID = PackageBases.ID " +
+                       "INNER JOIN Users " +
+                       "ON Users.ID = PackageBases.MaintainerUID " +
+                       "OR PackageBases.MaintainerUID IS NULL " +
+                       "OR Users.ID = PackageComaintainers.UsersID " +
+                       "WHERE Name = ? AND Username = ?", [pkgbase, user])
+    return cur.fetchone()[0] > 0
+
+
+def pkgbase_has_full_access(pkgbase, user):
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT COUNT(*) FROM PackageBases " +
+                       "INNER JOIN Users " +
+                       "ON Users.ID = PackageBases.MaintainerUID " +
+                       "WHERE Name = ? AND Username = ?", [pkgbase, user])
+    return cur.fetchone()[0] > 0
+
+
+def die(msg):
+    sys.stderr.write("{:s}\n".format(msg))
+    exit(1)
+
+
+def die_with_help(msg):
+    die(msg + "\nTry `{:s} help` for a list of commands.".format(ssh_cmdline))
+
+
+def warn(msg):
+    sys.stderr.write("warning: {:s}\n".format(msg))
+
+
+def usage(cmds):
+    sys.stderr.write("Commands:\n")
+    colwidth = max([len(cmd) for cmd in cmds.keys()]) + 4
+    for key in sorted(cmds):
+        sys.stderr.write("  " + key.ljust(colwidth) + cmds[key] + "\n")
+    exit(0)
+
+
+def main():
+    user = os.environ.get('AUR_USER')
+    privileged = (os.environ.get('AUR_PRIVILEGED', '0') == '1')
+    ssh_cmd = os.environ.get('SSH_ORIGINAL_COMMAND')
+    ssh_client = os.environ.get('SSH_CLIENT')
+
+    if not ssh_cmd:
+        die_with_help("Interactive shell is disabled.")
+    cmdargv = shlex.split(ssh_cmd)
+    action = cmdargv[0]
+    remote_addr = ssh_client.split(' ')[0] if ssh_client else None
+
+    if enable_maintenance:
+        if remote_addr not in maintenance_exc:
+            die("The AUR is down due to maintenance. We will be back soon.")
+
+    if action == 'git' and cmdargv[1] in ('upload-pack', 'receive-pack'):
+        action = action + '-' + cmdargv[1]
+        del cmdargv[1]
+
+    if action == 'git-upload-pack' or action == 'git-receive-pack':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing path".format(action))
+
+        path = cmdargv[1].rstrip('/')
+        if not path.startswith('/'):
+            path = '/' + path
+        if not path.endswith('.git'):
+            path = path + '.git'
+        pkgbase = path[1:-4]
+        if not re.match(repo_regex, pkgbase):
+            die('{:s}: invalid repository name: {:s}'.format(action, pkgbase))
+
+        if action == 'git-receive-pack' and pkgbase_exists(pkgbase):
+            if not privileged and not pkgbase_has_write_access(pkgbase, user):
+                die('{:s}: permission denied: {:s}'.format(action, user))
+
+        os.environ["AUR_USER"] = user
+        os.environ["AUR_PKGBASE"] = pkgbase
+        os.environ["GIT_NAMESPACE"] = pkgbase
+        cmd = action + " '" + repo_path + "'"
+        os.execl(git_shell_cmd, git_shell_cmd, '-c', cmd)
+    elif action == 'set-keywords':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing repository name".format(action))
+        pkgbase_set_keywords(cmdargv[1], cmdargv[2:])
+    elif action == 'list-repos':
+        if len(cmdargv) > 1:
+            die_with_help("{:s}: too many arguments".format(action))
+        list_repos(user)
+    elif action == 'setup-repo':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing repository name".format(action))
+        if len(cmdargv) > 2:
+            die_with_help("{:s}: too many arguments".format(action))
+        warn('{:s} is deprecated. '
+             'Use `git push` to create new repositories.'.format(action))
+        create_pkgbase(cmdargv[1], user)
+    elif action == 'restore':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing repository name".format(action))
+        if len(cmdargv) > 2:
+            die_with_help("{:s}: too many arguments".format(action))
+
+        pkgbase = cmdargv[1]
+        if not re.match(repo_regex, pkgbase):
+            die('{:s}: invalid repository name: {:s}'.format(action, pkgbase))
+
+        if pkgbase_exists(pkgbase):
+            die('{:s}: package base exists: {:s}'.format(action, pkgbase))
+        create_pkgbase(pkgbase, user)
+
+        os.environ["AUR_USER"] = user
+        os.environ["AUR_PKGBASE"] = pkgbase
+        os.execl(git_update_cmd, git_update_cmd, 'restore')
+    elif action == 'adopt':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing repository name".format(action))
+        if len(cmdargv) > 2:
+            die_with_help("{:s}: too many arguments".format(action))
+
+        pkgbase = cmdargv[1]
+        pkgbase_adopt(pkgbase, user, privileged)
+    elif action == 'disown':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing repository name".format(action))
+        if len(cmdargv) > 2:
+            die_with_help("{:s}: too many arguments".format(action))
+
+        pkgbase = cmdargv[1]
+        pkgbase_disown(pkgbase, user, privileged)
+    elif action == 'set-comaintainers':
+        if len(cmdargv) < 2:
+            die_with_help("{:s}: missing repository name".format(action))
+
+        pkgbase = cmdargv[1]
+        userlist = cmdargv[2:]
+        pkgbase_set_comaintainers(pkgbase, userlist, user, privileged)
+    elif action == 'help':
+        cmds = {
+            "adopt <name>": "Adopt a package base.",
+            "disown <name>": "Disown a package base.",
+            "help": "Show this help message and exit.",
+            "list-repos": "List all your repositories.",
+            "restore <name>": "Restore a deleted package base.",
+            "set-comaintainers <name> [...]": "Set package base co-maintainers.",
+            "set-keywords <name> [...]": "Change package base keywords.",
+            "setup-repo <name>": "Create a repository (deprecated).",
+            "git-receive-pack": "Internal command used with Git.",
+            "git-upload-pack": "Internal command used with Git.",
+        }
+        usage(cmds)
+    else:
+        die_with_help("invalid command: {:s}".format(action))
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/git/update.py b/aurweb/git/update.py
new file mode 100755
index 0000000..3b84eb5
--- /dev/null
+++ b/aurweb/git/update.py
@@ -0,0 +1,423 @@
+#!/usr/bin/python3
+
+import os
+import pygit2
+import re
+import subprocess
+import sys
+import time
+
+import srcinfo.parse
+import srcinfo.utils
+
+import aurweb.config
+import aurweb.db
+
+notify_cmd = aurweb.config.get('notifications', 'notify-cmd')
+
+repo_path = aurweb.config.get('serve', 'repo-path')
+repo_regex = aurweb.config.get('serve', 'repo-regex')
+
+max_blob_size = aurweb.config.getint('update', 'max-blob-size')
+
+
+def size_humanize(num):
+    for unit in ['B', 'KiB', 'MiB', 'GiB', 'TiB', 'PiB', 'EiB', 'ZiB']:
+        if abs(num) < 2048.0:
+            if isinstance(num, int):
+                return "{}{}".format(num, unit)
+            else:
+                return "{:.2f}{}".format(num, unit)
+        num /= 1024.0
+    return "{:.2f}{}".format(num, 'YiB')
+
+
+def extract_arch_fields(pkginfo, field):
+    values = []
+
+    if field in pkginfo:
+        for val in pkginfo[field]:
+            values.append({"value": val, "arch": None})
+
+    for arch in ['i686', 'x86_64']:
+        if field + '_' + arch in pkginfo:
+            for val in pkginfo[field + '_' + arch]:
+                values.append({"value": val, "arch": arch})
+
+    return values
+
+
+def parse_dep(depstring):
+    dep, _, desc = depstring.partition(': ')
+    depname = re.sub(r'(<|=|>).*', '', dep)
+    depcond = dep[len(depname):]
+
+    if (desc):
+        return (depname + ': ' + desc, depcond)
+    else:
+        return (depname, depcond)
+
+
+def create_pkgbase(conn, pkgbase, user):
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    userid = cur.fetchone()[0]
+
+    now = int(time.time())
+    cur = conn.execute("INSERT INTO PackageBases (Name, SubmittedTS, " +
+                       "ModifiedTS, SubmitterUID, MaintainerUID) VALUES " +
+                       "(?, ?, ?, ?, ?)", [pkgbase, now, now, userid, userid])
+    pkgbase_id = cur.lastrowid
+
+    cur = conn.execute("INSERT INTO PackageNotifications " +
+                       "(PackageBaseID, UserID) VALUES (?, ?)",
+                       [pkgbase_id, userid])
+
+    conn.commit()
+
+    return pkgbase_id
+
+
+def save_metadata(metadata, conn, user):
+    # Obtain package base ID and previous maintainer.
+    pkgbase = metadata['pkgbase']
+    cur = conn.execute("SELECT ID, MaintainerUID FROM PackageBases "
+                       "WHERE Name = ?", [pkgbase])
+    (pkgbase_id, maintainer_uid) = cur.fetchone()
+    was_orphan = not maintainer_uid
+
+    # Obtain the user ID of the new maintainer.
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    user_id = int(cur.fetchone()[0])
+
+    # Update package base details and delete current packages.
+    now = int(time.time())
+    conn.execute("UPDATE PackageBases SET ModifiedTS = ?, " +
+                 "PackagerUID = ?, OutOfDateTS = NULL WHERE ID = ?",
+                 [now, user_id, pkgbase_id])
+    conn.execute("UPDATE PackageBases SET MaintainerUID = ? " +
+                 "WHERE ID = ? AND MaintainerUID IS NULL",
+                 [user_id, pkgbase_id])
+    for table in ('Sources', 'Depends', 'Relations', 'Licenses', 'Groups'):
+        conn.execute("DELETE FROM Package" + table + " WHERE EXISTS (" +
+                     "SELECT * FROM Packages " +
+                     "WHERE Packages.PackageBaseID = ? AND " +
+                     "Package" + table + ".PackageID = Packages.ID)",
+                     [pkgbase_id])
+    conn.execute("DELETE FROM Packages WHERE PackageBaseID = ?", [pkgbase_id])
+
+    for pkgname in srcinfo.utils.get_package_names(metadata):
+        pkginfo = srcinfo.utils.get_merged_package(pkgname, metadata)
+
+        if 'epoch' in pkginfo and int(pkginfo['epoch']) > 0:
+            ver = '{:d}:{:s}-{:s}'.format(int(pkginfo['epoch']),
+                                          pkginfo['pkgver'],
+                                          pkginfo['pkgrel'])
+        else:
+            ver = '{:s}-{:s}'.format(pkginfo['pkgver'], pkginfo['pkgrel'])
+
+        for field in ('pkgdesc', 'url'):
+            if field not in pkginfo:
+                pkginfo[field] = None
+
+        # Create a new package.
+        cur = conn.execute("INSERT INTO Packages (PackageBaseID, Name, " +
+                           "Version, Description, URL) " +
+                           "VALUES (?, ?, ?, ?, ?)",
+                           [pkgbase_id, pkginfo['pkgname'], ver,
+                            pkginfo['pkgdesc'], pkginfo['url']])
+        conn.commit()
+        pkgid = cur.lastrowid
+
+        # Add package sources.
+        for source_info in extract_arch_fields(pkginfo, 'source'):
+            conn.execute("INSERT INTO PackageSources (PackageID, Source, " +
+                         "SourceArch) VALUES (?, ?, ?)",
+                         [pkgid, source_info['value'], source_info['arch']])
+
+        # Add package dependencies.
+        for deptype in ('depends', 'makedepends',
+                        'checkdepends', 'optdepends'):
+            cur = conn.execute("SELECT ID FROM DependencyTypes WHERE Name = ?",
+                               [deptype])
+            deptypeid = cur.fetchone()[0]
+            for dep_info in extract_arch_fields(pkginfo, deptype):
+                depname, depcond = parse_dep(dep_info['value'])
+                deparch = dep_info['arch']
+                conn.execute("INSERT INTO PackageDepends (PackageID, " +
+                             "DepTypeID, DepName, DepCondition, DepArch) " +
+                             "VALUES (?, ?, ?, ?, ?)",
+                             [pkgid, deptypeid, depname, depcond, deparch])
+
+        # Add package relations (conflicts, provides, replaces).
+        for reltype in ('conflicts', 'provides', 'replaces'):
+            cur = conn.execute("SELECT ID FROM RelationTypes WHERE Name = ?",
+                               [reltype])
+            reltypeid = cur.fetchone()[0]
+            for rel_info in extract_arch_fields(pkginfo, reltype):
+                relname, relcond = parse_dep(rel_info['value'])
+                relarch = rel_info['arch']
+                conn.execute("INSERT INTO PackageRelations (PackageID, " +
+                             "RelTypeID, RelName, RelCondition, RelArch) " +
+                             "VALUES (?, ?, ?, ?, ?)",
+                             [pkgid, reltypeid, relname, relcond, relarch])
+
+        # Add package licenses.
+        if 'license' in pkginfo:
+            for license in pkginfo['license']:
+                cur = conn.execute("SELECT ID FROM Licenses WHERE Name = ?",
+                                   [license])
+                row = cur.fetchone()
+                if row:
+                    licenseid = row[0]
+                else:
+                    cur = conn.execute("INSERT INTO Licenses (Name) " +
+                                       "VALUES (?)", [license])
+                    conn.commit()
+                    licenseid = cur.lastrowid
+                conn.execute("INSERT INTO PackageLicenses (PackageID, " +
+                             "LicenseID) VALUES (?, ?)",
+                             [pkgid, licenseid])
+
+        # Add package groups.
+        if 'groups' in pkginfo:
+            for group in pkginfo['groups']:
+                cur = conn.execute("SELECT ID FROM Groups WHERE Name = ?",
+                                   [group])
+                row = cur.fetchone()
+                if row:
+                    groupid = row[0]
+                else:
+                    cur = conn.execute("INSERT INTO Groups (Name) VALUES (?)",
+                                       [group])
+                    conn.commit()
+                    groupid = cur.lastrowid
+                conn.execute("INSERT INTO PackageGroups (PackageID, "
+                             "GroupID) VALUES (?, ?)", [pkgid, groupid])
+
+    # Add user to notification list on adoption.
+    if was_orphan:
+        cur = conn.execute("SELECT COUNT(*) FROM PackageNotifications WHERE " +
+                           "PackageBaseID = ? AND UserID = ?",
+                           [pkgbase_id, user_id])
+        if cur.fetchone()[0] == 0:
+            conn.execute("INSERT INTO PackageNotifications " +
+                         "(PackageBaseID, UserID) VALUES (?, ?)",
+                         [pkgbase_id, user_id])
+
+    conn.commit()
+
+
+def update_notify(conn, user, pkgbase_id):
+    # Obtain the user ID of the new maintainer.
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
+    user_id = int(cur.fetchone()[0])
+
+    # Execute the notification script.
+    subprocess.Popen((notify_cmd, 'update', str(user_id), str(pkgbase_id)))
+
+
+def die(msg):
+    sys.stderr.write("error: {:s}\n".format(msg))
+    exit(1)
+
+
+def warn(msg):
+    sys.stderr.write("warning: {:s}\n".format(msg))
+
+
+def die_commit(msg, commit):
+    sys.stderr.write("error: The following error " +
+                     "occurred when parsing commit\n")
+    sys.stderr.write("error: {:s}:\n".format(commit))
+    sys.stderr.write("error: {:s}\n".format(msg))
+    exit(1)
+
+
+def main():
+    repo = pygit2.Repository(repo_path)
+
+    user = os.environ.get("AUR_USER")
+    pkgbase = os.environ.get("AUR_PKGBASE")
+    privileged = (os.environ.get("AUR_PRIVILEGED", '0') == '1')
+    warn_or_die = warn if privileged else die
+
+    if len(sys.argv) == 2 and sys.argv[1] == "restore":
+        if 'refs/heads/' + pkgbase not in repo.listall_references():
+            die('{:s}: repository not found: {:s}'.format(sys.argv[1],
+                pkgbase))
+        refname = "refs/heads/master"
+        branchref = 'refs/heads/' + pkgbase
+        sha1_old = sha1_new = repo.lookup_reference(branchref).target
+    elif len(sys.argv) == 4:
+        refname, sha1_old, sha1_new = sys.argv[1:4]
+    else:
+        die("invalid arguments")
+
+    if refname != "refs/heads/master":
+        die("pushing to a branch other than master is restricted")
+
+    conn = aurweb.db.Connection()
+
+    # Detect and deny non-fast-forwards.
+    if sha1_old != "0" * 40 and not privileged:
+        walker = repo.walk(sha1_old, pygit2.GIT_SORT_TOPOLOGICAL)
+        walker.hide(sha1_new)
+        if next(walker, None) is not None:
+            die("denying non-fast-forward (you should pull first)")
+
+    # Prepare the walker that validates new commits.
+    walker = repo.walk(sha1_new, pygit2.GIT_SORT_TOPOLOGICAL)
+    if sha1_old != "0" * 40:
+        walker.hide(sha1_old)
+
+    # Validate all new commits.
+    for commit in walker:
+        for fname in ('.SRCINFO', 'PKGBUILD'):
+            if fname not in commit.tree:
+                die_commit("missing {:s}".format(fname), str(commit.id))
+
+        for treeobj in commit.tree:
+            blob = repo[treeobj.id]
+
+            if isinstance(blob, pygit2.Tree):
+                die_commit("the repository must not contain subdirectories",
+                           str(commit.id))
+
+            if not isinstance(blob, pygit2.Blob):
+                die_commit("not a blob object: {:s}".format(treeobj),
+                           str(commit.id))
+
+            if blob.size > max_blob_size:
+                die_commit("maximum blob size ({:s}) exceeded".format(
+                           size_humanize(max_blob_size)), str(commit.id))
+
+        metadata_raw = repo[commit.tree['.SRCINFO'].id].data.decode()
+        (metadata, errors) = srcinfo.parse.parse_srcinfo(metadata_raw)
+        if errors:
+            sys.stderr.write("error: The following errors occurred "
+                             "when parsing .SRCINFO in commit\n")
+            sys.stderr.write("error: {:s}:\n".format(str(commit.id)))
+            for error in errors:
+                for err in error['error']:
+                    sys.stderr.write("error: line {:d}: {:s}\n".format(
+                                     error['line'], err))
+            exit(1)
+
+        metadata_pkgbase = metadata['pkgbase']
+        if not re.match(repo_regex, metadata_pkgbase):
+            die_commit('invalid pkgbase: {:s}'.format(metadata_pkgbase),
+                       str(commit.id))
+
+        for pkgname in set(metadata['packages'].keys()):
+            pkginfo = srcinfo.utils.get_merged_package(pkgname, metadata)
+
+            for field in ('pkgver', 'pkgrel', 'pkgname'):
+                if field not in pkginfo:
+                    die_commit('missing mandatory field: {:s}'.format(field),
+                               str(commit.id))
+
+            if 'epoch' in pkginfo and not pkginfo['epoch'].isdigit():
+                die_commit('invalid epoch: {:s}'.format(pkginfo['epoch']),
+                           str(commit.id))
+
+            if not re.match(r'[a-z0-9][a-z0-9\.+_-]*$', pkginfo['pkgname']):
+                die_commit('invalid package name: {:s}'.format(
+                           pkginfo['pkgname']), str(commit.id))
+
+            max_len = {'pkgname': 255, 'pkgdesc': 255, 'url': 8000}
+            for field in max_len.keys():
+                if field in pkginfo and len(pkginfo[field]) > max_len[field]:
+                    die_commit('{:s} field too long: {:s}'.format(field,
+                               pkginfo[field]), str(commit.id))
+
+            for field in ('install', 'changelog'):
+                if field in pkginfo and not pkginfo[field] in commit.tree:
+                    die_commit('missing {:s} file: {:s}'.format(field,
+                               pkginfo[field]), str(commit.id))
+
+            for field in extract_arch_fields(pkginfo, 'source'):
+                fname = field['value']
+                if len(fname) > 8000:
+                    die_commit('source entry too long: {:s}'.format(fname),
+                               str(commit.id))
+                if "://" in fname or "lp:" in fname:
+                    continue
+                if fname not in commit.tree:
+                    die_commit('missing source file: {:s}'.format(fname),
+                               str(commit.id))
+
+    # Display a warning if .SRCINFO is unchanged.
+    if sha1_old not in ("0000000000000000000000000000000000000000", sha1_new):
+        srcinfo_id_old = repo[sha1_old].tree['.SRCINFO'].id
+        srcinfo_id_new = repo[sha1_new].tree['.SRCINFO'].id
+        if srcinfo_id_old == srcinfo_id_new:
+            warn(".SRCINFO unchanged. "
+                 "The package database will not be updated!")
+
+    # Read .SRCINFO from the HEAD commit.
+    metadata_raw = repo[repo[sha1_new].tree['.SRCINFO'].id].data.decode()
+    (metadata, errors) = srcinfo.parse.parse_srcinfo(metadata_raw)
+
+    # Ensure that the package base name matches the repository name.
+    metadata_pkgbase = metadata['pkgbase']
+    if metadata_pkgbase != pkgbase:
+        die('invalid pkgbase: {:s}, expected {:s}'.format(metadata_pkgbase,
+                                                          pkgbase))
+
+    # Ensure that packages are neither blacklisted nor overwritten.
+    pkgbase = metadata['pkgbase']
+    cur = conn.execute("SELECT ID FROM PackageBases WHERE Name = ?", [pkgbase])
+    row = cur.fetchone()
+    pkgbase_id = row[0] if row else 0
+
+    cur = conn.execute("SELECT Name FROM PackageBlacklist")
+    blacklist = [row[0] for row in cur.fetchall()]
+
+    cur = conn.execute("SELECT Name, Repo FROM OfficialProviders")
+    providers = dict(cur.fetchall())
+
+    for pkgname in srcinfo.utils.get_package_names(metadata):
+        pkginfo = srcinfo.utils.get_merged_package(pkgname, metadata)
+        pkgname = pkginfo['pkgname']
+
+        if pkgname in blacklist:
+            warn_or_die('package is blacklisted: {:s}'.format(pkgname))
+        if pkgname in providers:
+            warn_or_die('package already provided by [{:s}]: {:s}'.format(
+                        providers[pkgname], pkgname))
+
+        cur = conn.execute("SELECT COUNT(*) FROM Packages WHERE Name = ? " +
+                           "AND PackageBaseID <> ?", [pkgname, pkgbase_id])
+        if cur.fetchone()[0] > 0:
+            die('cannot overwrite package: {:s}'.format(pkgname))
+
+    # Create a new package base if it does not exist yet.
+    if pkgbase_id == 0:
+        pkgbase_id = create_pkgbase(conn, pkgbase, user)
+
+    # Store package base details in the database.
+    save_metadata(metadata, conn, user)
+
+    # Create (or update) a branch with the name of the package base for better
+    # accessibility.
+    branchref = 'refs/heads/' + pkgbase
+    repo.create_reference(branchref, sha1_new, True)
+
+    # Work around a Git bug: The HEAD ref is not updated when using
+    # gitnamespaces. This can be removed once the bug fix is included in Git
+    # mainline. See
+    # http://git.661346.n2.nabble.com/PATCH-receive-pack-Create-a-HEAD-ref-for-ref-namespace-td7632149.html
+    # for details.
+    headref = 'refs/namespaces/' + pkgbase + '/HEAD'
+    repo.create_reference(headref, sha1_new, True)
+
+    # Send package update notifications.
+    update_notify(conn, user, pkgbase_id)
+
+    # Close the database.
+    cur.close()
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/scripts/__init__.py b/aurweb/scripts/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/aurweb/scripts/__init__.py
diff --git a/aurweb/scripts/aurblup.py b/aurweb/scripts/aurblup.py
new file mode 100755
index 0000000..1b6de2f
--- /dev/null
+++ b/aurweb/scripts/aurblup.py
@@ -0,0 +1,55 @@
+#!/usr/bin/python3
+
+import pyalpm
+import re
+
+import aurweb.config
+import aurweb.db
+
+db_path = aurweb.config.get('aurblup', 'db-path')
+sync_dbs = aurweb.config.get('aurblup', 'sync-dbs').split(' ')
+server = aurweb.config.get('aurblup', 'server')
+
+
+def main():
+    blacklist = set()
+    providers = set()
+    repomap = dict()
+
+    h = pyalpm.Handle("/", db_path)
+    for sync_db in sync_dbs:
+        repo = h.register_syncdb(sync_db, pyalpm.SIG_DATABASE_OPTIONAL)
+        repo.servers = [server.replace("%s", sync_db)]
+        t = h.init_transaction()
+        repo.update(False)
+        t.release()
+
+        for pkg in repo.pkgcache:
+            blacklist.add(pkg.name)
+            [blacklist.add(x) for x in pkg.replaces]
+            providers.add((pkg.name, pkg.name))
+            repomap[(pkg.name, pkg.name)] = repo.name
+            for provision in pkg.provides:
+                provisionname = re.sub(r'(<|=|>).*', '', provision)
+                providers.add((pkg.name, provisionname))
+                repomap[(pkg.name, provisionname)] = repo.name
+
+    conn = aurweb.db.Connection()
+
+    cur = conn.execute("SELECT Name, Provides FROM OfficialProviders")
+    oldproviders = set(cur.fetchall())
+
+    for pkg, provides in providers.difference(oldproviders):
+        repo = repomap[(pkg, provides)]
+        conn.execute("INSERT INTO OfficialProviders (Name, Repo, Provides) "
+                     "VALUES (?, ?, ?)", [pkg, repo, provides])
+    for pkg, provides in oldproviders.difference(providers):
+        conn.execute("DELETE FROM OfficialProviders "
+                     "WHERE Name = ? AND Provides = ?", [pkg, provides])
+
+    conn.commit()
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/scripts/mkpkglists.py b/aurweb/scripts/mkpkglists.py
new file mode 100755
index 0000000..8a0f2e9
--- /dev/null
+++ b/aurweb/scripts/mkpkglists.py
@@ -0,0 +1,38 @@
+#!/usr/bin/python3
+
+import datetime
+import gzip
+
+import aurweb.config
+import aurweb.db
+
+packagesfile = aurweb.config.get('mkpkglists', 'packagesfile')
+pkgbasefile = aurweb.config.get('mkpkglists', 'pkgbasefile')
+
+
+def main():
+    conn = aurweb.db.Connection()
+
+    datestr = datetime.datetime.utcnow().strftime("%a, %d %b %Y %H:%M:%S GMT")
+    pkglist_header = "# AUR package list, generated on " + datestr
+    pkgbaselist_header = "# AUR package base list, generated on " + datestr
+
+    with gzip.open(packagesfile, "w") as f:
+        f.write(bytes(pkglist_header + "\n", "UTF-8"))
+        cur = conn.execute("SELECT Packages.Name FROM Packages " +
+                           "INNER JOIN PackageBases " +
+                           "ON PackageBases.ID = Packages.PackageBaseID " +
+                           "WHERE PackageBases.PackagerUID IS NOT NULL")
+        f.writelines([bytes(x[0] + "\n", "UTF-8") for x in cur.fetchall()])
+
+    with gzip.open(pkgbasefile, "w") as f:
+        f.write(bytes(pkgbaselist_header + "\n", "UTF-8"))
+        cur = conn.execute("SELECT Name FROM PackageBases " +
+                           "WHERE PackagerUID IS NOT NULL")
+        f.writelines([bytes(x[0] + "\n", "UTF-8") for x in cur.fetchall()])
+
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/scripts/notify.py b/aurweb/scripts/notify.py
new file mode 100755
index 0000000..ddd6e49
--- /dev/null
+++ b/aurweb/scripts/notify.py
@@ -0,0 +1,455 @@
+#!/usr/bin/python3
+
+import email.mime.text
+import subprocess
+import sys
+import textwrap
+
+import aurweb.config
+import aurweb.db
+
+aur_location = aurweb.config.get('options', 'aur_location')
+aur_request_ml = aurweb.config.get('options', 'aur_request_ml')
+
+sendmail = aurweb.config.get('notifications', 'sendmail')
+sender = aurweb.config.get('notifications', 'sender')
+reply_to = aurweb.config.get('notifications', 'reply-to')
+
+
+def headers_cc(cclist):
+    return {'Cc': str.join(', ', cclist)}
+
+
+def headers_msgid(thread_id):
+    return {'Message-ID': thread_id}
+
+
+def headers_reply(thread_id):
+    return {'In-Reply-To': thread_id, 'References': thread_id}
+
+
+def send_notification(to, subject, body, refs, headers={}):
+    wrapped = ''
+    for line in body.splitlines():
+        wrapped += textwrap.fill(line, break_long_words=False) + '\n'
+    if refs:
+        body = wrapped + '\n' + refs
+    else:
+        body = wrapped
+
+    for recipient in to:
+        msg = email.mime.text.MIMEText(body, 'plain', 'utf-8')
+        msg['Subject'] = subject
+        msg['From'] = sender
+        msg['Reply-to'] = reply_to
+        msg['To'] = recipient
+
+        for key, value in headers.items():
+            msg[key] = value
+
+        p = subprocess.Popen([sendmail, '-t', '-oi'], stdin=subprocess.PIPE)
+        p.communicate(msg.as_bytes())
+
+
+def username_from_id(conn, uid):
+    cur = conn.execute('SELECT UserName FROM Users WHERE ID = ?', [uid])
+    return cur.fetchone()[0]
+
+
+def pkgbase_from_id(conn, pkgbase_id):
+    cur = conn.execute('SELECT Name FROM PackageBases WHERE ID = ?',
+                       [pkgbase_id])
+    return cur.fetchone()[0]
+
+
+def pkgbase_from_pkgreq(conn, reqid):
+    cur = conn.execute('SELECT PackageBaseID FROM PackageRequests ' +
+                       'WHERE ID = ?', [reqid])
+    return cur.fetchone()[0]
+
+
+def get_user_email(conn, uid):
+    cur = conn.execute('SELECT Email FROM Users WHERE ID = ?', [uid])
+    return cur.fetchone()[0]
+
+
+def get_maintainer_email(conn, pkgbase_id):
+    cur = conn.execute('SELECT Users.Email FROM Users ' +
+                       'INNER JOIN PackageBases ' +
+                       'ON PackageBases.MaintainerUID = Users.ID WHERE ' +
+                       'PackageBases.ID = ?', [pkgbase_id])
+    return cur.fetchone()[0]
+
+
+def get_recipients(conn, pkgbase_id, uid):
+    cur = conn.execute('SELECT DISTINCT Users.Email FROM Users ' +
+                       'INNER JOIN PackageNotifications ' +
+                       'ON PackageNotifications.UserID = Users.ID WHERE ' +
+                       'PackageNotifications.UserID != ? AND ' +
+                       'PackageNotifications.PackageBaseID = ?',
+                       [uid, pkgbase_id])
+    return [row[0] for row in cur.fetchall()]
+
+
+def get_comment_recipients(conn, pkgbase_id, uid):
+    cur = conn.execute('SELECT DISTINCT Users.Email FROM Users ' +
+                       'INNER JOIN PackageNotifications ' +
+                       'ON PackageNotifications.UserID = Users.ID WHERE ' +
+                       'Users.CommentNotify = 1 AND ' +
+                       'PackageNotifications.UserID != ? AND ' +
+                       'PackageNotifications.PackageBaseID = ?',
+                       [uid, pkgbase_id])
+    return [row[0] for row in cur.fetchall()]
+
+
+def get_update_recipients(conn, pkgbase_id, uid):
+    cur = conn.execute('SELECT DISTINCT Users.Email FROM Users ' +
+                       'INNER JOIN PackageNotifications ' +
+                       'ON PackageNotifications.UserID = Users.ID WHERE ' +
+                       'Users.UpdateNotify = 1 AND ' +
+                       'PackageNotifications.UserID != ? AND ' +
+                       'PackageNotifications.PackageBaseID = ?',
+                       [uid, pkgbase_id])
+    return [row[0] for row in cur.fetchall()]
+
+
+def get_ownership_recipients(conn, pkgbase_id, uid):
+    cur = conn.execute('SELECT DISTINCT Users.Email FROM Users ' +
+                       'INNER JOIN PackageNotifications ' +
+                       'ON PackageNotifications.UserID = Users.ID WHERE ' +
+                       'Users.OwnershipNotify = 1 AND ' +
+                       'PackageNotifications.UserID != ? AND ' +
+                       'PackageNotifications.PackageBaseID = ?',
+                       [uid, pkgbase_id])
+    return [row[0] for row in cur.fetchall()]
+
+
+def get_request_recipients(conn, reqid):
+    cur = conn.execute('SELECT DISTINCT Users.Email FROM PackageRequests ' +
+                       'INNER JOIN PackageBases ' +
+                       'ON PackageBases.ID = PackageRequests.PackageBaseID ' +
+                       'INNER JOIN Users ' +
+                       'ON Users.ID = PackageRequests.UsersID ' +
+                       'OR Users.ID = PackageBases.MaintainerUID ' +
+                       'WHERE PackageRequests.ID = ?', [reqid])
+    return [row[0] for row in cur.fetchall()]
+
+
+def get_tu_vote_reminder_recipients(conn, vote_id):
+    cur = conn.execute('SELECT Users.Email FROM Users ' +
+                       'WHERE AccountTypeID = 2 ' +
+                       'EXCEPT SELECT Users.Email FROM Users ' +
+                       'INNER JOIN TU_Votes ' +
+                       'ON TU_Votes.UserID = Users.ID ' +
+                       'WHERE TU_Votes.VoteID = ?', [vote_id])
+    return [row[0] for row in cur.fetchall()]
+
+
+def get_comment(conn, comment_id):
+    cur = conn.execute('SELECT Comments FROM PackageComments WHERE ID = ?',
+                       [comment_id])
+    return cur.fetchone()[0]
+
+
+def get_flagger_comment(conn, pkgbase_id):
+    cur = conn.execute('SELECT FlaggerComment FROM PackageBases WHERE ID = ?',
+                       [pkgbase_id])
+    return cur.fetchone()[0]
+
+
+def get_request_comment(conn, reqid):
+    cur = conn.execute('SELECT Comments FROM PackageRequests WHERE ID = ?',
+                       [reqid])
+    return cur.fetchone()[0]
+
+
+def get_request_closure_comment(conn, reqid):
+    cur = conn.execute('SELECT ClosureComment FROM PackageRequests ' +
+                       'WHERE ID = ?', [reqid])
+    return cur.fetchone()[0]
+
+
+def send_resetkey(conn, uid):
+    cur = conn.execute('SELECT UserName, Email, ResetKey FROM Users ' +
+                       'WHERE ID = ?', [uid])
+    username, to, resetkey = cur.fetchone()
+
+    subject = 'AUR Password Reset'
+    body = 'A password reset request was submitted for the account %s ' \
+           'associated with your email address. If you wish to reset your ' \
+           'password follow the link [1] below, otherwise ignore this ' \
+           'message and nothing will happen.' % (username)
+    refs = '[1] ' + aur_location + '/passreset/?resetkey=' + resetkey
+
+    send_notification([to], subject, body, refs)
+
+
+def welcome(conn, uid):
+    cur = conn.execute('SELECT UserName, Email, ResetKey FROM Users ' +
+                       'WHERE ID = ?', [uid])
+    username, to, resetkey = cur.fetchone()
+
+    subject = 'Welcome to the Arch User Repository'
+    body = 'Welcome to the Arch User Repository! In order to set an initial ' \
+           'password for your new account, please click the link [1] below. ' \
+           'If the link does not work, try copying and pasting it into your ' \
+           'browser.'
+    refs = '[1] ' + aur_location + '/passreset/?resetkey=' + resetkey
+
+    send_notification([to], subject, body, refs)
+
+
+def comment(conn, uid, pkgbase_id, comment_id):
+    user = username_from_id(conn, uid)
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = get_comment_recipients(conn, pkgbase_id, uid)
+    text = get_comment(conn, comment_id)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Comment for %s' % (pkgbase)
+    body = '%s [1] added the following comment to %s [2]:' % (user, pkgbase)
+    body += '\n\n' + text + '\n\n'
+    body += 'If you no longer wish to receive notifications about this ' \
+            'package, please go to the package page [2] and select "%s".' % \
+            ('Disable notifications')
+    refs = '[1] ' + user_uri + '\n'
+    refs += '[2] ' + pkgbase_uri
+    thread_id = '<pkg-notifications-' + pkgbase + '@aur.archlinux.org>'
+    headers = headers_reply(thread_id)
+
+    send_notification(to, subject, body, refs, headers)
+
+
+def update(conn, uid, pkgbase_id):
+    user = username_from_id(conn, uid)
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = get_update_recipients(conn, pkgbase_id, uid)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Package Update: %s' % (pkgbase)
+    body = '%s [1] pushed a new commit to %s [2].' % (user, pkgbase)
+    body += '\n\n'
+    body += 'If you no longer wish to receive notifications about this ' \
+            'package, please go to the package page [2] and select "%s".' % \
+            ('Disable notifications')
+    refs = '[1] ' + user_uri + '\n'
+    refs += '[2] ' + pkgbase_uri
+    thread_id = '<pkg-notifications-' + pkgbase + '@aur.archlinux.org>'
+    headers = headers_reply(thread_id)
+
+    send_notification(to, subject, body, refs, headers)
+
+
+def flag(conn, uid, pkgbase_id):
+    user = username_from_id(conn, uid)
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = [get_maintainer_email(conn, pkgbase_id)]
+    text = get_flagger_comment(conn, pkgbase_id)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Out-of-date Notification for %s' % (pkgbase)
+    body = 'Your package %s [1] has been flagged out-of-date by %s [2]:' % \
+           (pkgbase, user)
+    body += '\n\n' + text
+    refs = '[1] ' + pkgbase_uri + '\n'
+    refs += '[2] ' + user_uri
+
+    send_notification(to, subject, body, refs)
+
+
+def adopt(conn, pkgbase_id, uid):
+    user = username_from_id(conn, uid)
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = get_ownership_recipients(conn, pkgbase_id, uid)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Ownership Notification for %s' % (pkgbase)
+    body = 'The package %s [1] was adopted by %s [2].' % (pkgbase, user)
+    refs = '[1] ' + pkgbase_uri + '\n'
+    refs += '[2] ' + user_uri
+
+    send_notification(to, subject, body, refs)
+
+
+def disown(conn, pkgbase_id, uid):
+    user = username_from_id(conn, uid)
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = get_ownership_recipients(conn, pkgbase_id, uid)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Ownership Notification for %s' % (pkgbase)
+    body = 'The package %s [1] was disowned by %s [2].' % (pkgbase, user)
+    refs = '[1] ' + pkgbase_uri + '\n'
+    refs += '[2] ' + user_uri
+
+    send_notification(to, subject, body, refs)
+
+
+def comaintainer_add(conn, pkgbase_id, uid):
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = [get_user_email(conn, uid)]
+
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Co-Maintainer Notification for %s' % (pkgbase)
+    body = 'You were added to the co-maintainer list of %s [1].' % (pkgbase)
+    refs = '[1] ' + pkgbase_uri + '\n'
+
+    send_notification(to, subject, body, refs)
+
+
+def comaintainer_remove(conn, pkgbase_id, uid):
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = [get_user_email(conn, uid)]
+
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = 'AUR Co-Maintainer Notification for %s' % (pkgbase)
+    body = ('You were removed from the co-maintainer list of %s [1].' %
+            (pkgbase))
+    refs = '[1] ' + pkgbase_uri + '\n'
+
+    send_notification(to, subject, body, refs)
+
+
+def delete(conn, uid, old_pkgbase_id, new_pkgbase_id=None):
+    user = username_from_id(conn, uid)
+    old_pkgbase = pkgbase_from_id(conn, old_pkgbase_id)
+    if new_pkgbase_id:
+        new_pkgbase = pkgbase_from_id(conn, new_pkgbase_id)
+    to = get_recipients(conn, old_pkgbase_id, uid)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + old_pkgbase + '/'
+
+    subject = 'AUR Package deleted: %s' % (old_pkgbase)
+    if new_pkgbase_id:
+        new_pkgbase_uri = aur_location + '/pkgbase/' + new_pkgbase + '/'
+        body = '%s [1] merged %s [2] into %s [3].\n\n' \
+               'If you no longer wish receive notifications about the new ' \
+               'package, please go to [3] and click "%s".' %\
+               (user, old_pkgbase, new_pkgbase, 'Disable notifications')
+        refs = '[1] ' + user_uri + '\n'
+        refs += '[2] ' + pkgbase_uri + '\n'
+        refs += '[3] ' + new_pkgbase_uri
+    else:
+        body = '%s [1] deleted %s [2].\n\n' \
+               'You will no longer receive notifications about this ' \
+               'package.' % (user, old_pkgbase)
+        refs = '[1] ' + user_uri + '\n'
+        refs += '[2] ' + pkgbase_uri
+
+    send_notification(to, subject, body, refs)
+
+
+def request_open(conn, uid, reqid, reqtype, pkgbase_id, merge_into=None):
+    user = username_from_id(conn, uid)
+    pkgbase = pkgbase_from_id(conn, pkgbase_id)
+    to = [aur_request_ml]
+    cc = get_request_recipients(conn, reqid)
+    text = get_request_comment(conn, reqid)
+
+    user_uri = aur_location + '/account/' + user + '/'
+    pkgbase_uri = aur_location + '/pkgbase/' + pkgbase + '/'
+
+    subject = '[PRQ#%d] %s Request for %s' % \
+              (int(reqid), reqtype.title(), pkgbase)
+    if merge_into:
+        merge_into_uri = aur_location + '/pkgbase/' + merge_into + '/'
+        body = '%s [1] filed a request to merge %s [2] into %s [3]:' % \
+               (user, pkgbase, merge_into)
+        body += '\n\n' + text
+        refs = '[1] ' + user_uri + '\n'
+        refs += '[2] ' + pkgbase_uri + '\n'
+        refs += '[3] ' + merge_into_uri
+    else:
+        body = '%s [1] filed a %s request for %s [2]:' % \
+               (user, reqtype, pkgbase)
+        body += '\n\n' + text
+        refs = '[1] ' + user_uri + '\n'
+        refs += '[2] ' + pkgbase_uri + '\n'
+    thread_id = '<pkg-request-' + reqid + '@aur.archlinux.org>'
+    # Use a deterministic Message-ID for the first email referencing a request.
+    headers = headers_msgid(thread_id)
+    headers.update(headers_cc(cc))
+
+    send_notification(to, subject, body, refs, headers)
+
+
+def request_close(conn, uid, reqid, reason):
+    to = [aur_request_ml]
+    cc = get_request_recipients(conn, reqid)
+    text = get_request_closure_comment(conn, reqid)
+
+    subject = '[PRQ#%d] Request %s' % (int(reqid), reason.title())
+    if int(uid):
+        user = username_from_id(conn, uid)
+        user_uri = aur_location + '/account/' + user + '/'
+        body = 'Request #%d has been %s by %s [1]' % (int(reqid), reason, user)
+        refs = '[1] ' + user_uri
+    else:
+        body = 'Request #%d has been %s automatically by the Arch User ' \
+               'Repository package request system' % (int(reqid), reason)
+        refs = None
+    if text.strip() == '':
+        body += '.'
+    else:
+        body += ':\n\n' + text
+    thread_id = '<pkg-request-' + reqid + '@aur.archlinux.org>'
+    headers = headers_reply(thread_id)
+    headers.update(headers_cc(cc))
+
+    send_notification(to, subject, body, refs, headers)
+
+
+def tu_vote_reminder(conn, vote_id):
+    to = get_tu_vote_reminder_recipients(conn, vote_id)
+
+    vote_uri = aur_location + '/tu/?id=' + vote_id
+
+    subject = 'TU Vote Reminder: Proposal %d' % (int(vote_id))
+    body = 'Please remember to cast your vote on proposal %d [1]. ' \
+           'The voting period ends in less than 48 hours.' % (int(vote_id))
+    refs = '[1] ' + vote_uri
+
+    send_notification(to, subject, body, refs)
+
+
+def main():
+    action = sys.argv[1]
+    action_map = {
+        'send-resetkey': send_resetkey,
+        'welcome': welcome,
+        'comment': comment,
+        'update': update,
+        'flag': flag,
+        'adopt': adopt,
+        'disown': disown,
+        'comaintainer-add': comaintainer_add,
+        'comaintainer-remove': comaintainer_remove,
+        'delete': delete,
+        'request-open': request_open,
+        'request-close': request_close,
+        'tu-vote-reminder': tu_vote_reminder,
+    }
+
+    conn = aurweb.db.Connection()
+
+    action_map[action](conn, *sys.argv[2:])
+
+    conn.commit()
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/scripts/pkgmaint.py b/aurweb/scripts/pkgmaint.py
new file mode 100755
index 0000000..3ad9ed8
--- /dev/null
+++ b/aurweb/scripts/pkgmaint.py
@@ -0,0 +1,20 @@
+#!/usr/bin/python3
+
+import time
+
+import aurweb.db
+
+
+def main():
+    conn = aurweb.db.Connection()
+
+    limit_to = int(time.time()) - 86400
+    conn.execute("DELETE FROM PackageBases WHERE " +
+                 "SubmittedTS < ? AND PackagerUID IS NULL", [limit_to])
+
+    conn.commit()
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/scripts/popupdate.py b/aurweb/scripts/popupdate.py
new file mode 100755
index 0000000..58cd018
--- /dev/null
+++ b/aurweb/scripts/popupdate.py
@@ -0,0 +1,26 @@
+#!/usr/bin/python3
+
+import time
+
+import aurweb.db
+
+
+def main():
+    conn = aurweb.db.Connection()
+
+    conn.execute("UPDATE PackageBases SET NumVotes = (" +
+                 "SELECT COUNT(*) FROM PackageVotes " +
+                 "WHERE PackageVotes.PackageBaseID = PackageBases.ID)")
+
+    now = int(time.time())
+    conn.execute("UPDATE PackageBases SET Popularity = (" +
+                 "SELECT COALESCE(SUM(POWER(0.98, (? - VoteTS) / 86400)), 0.0) " +
+                 "FROM PackageVotes WHERE PackageVotes.PackageBaseID = " +
+                 "PackageBases.ID AND NOT VoteTS IS NULL)", [now])
+
+    conn.commit()
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/aurweb/scripts/tuvotereminder.py b/aurweb/scripts/tuvotereminder.py
new file mode 100755
index 0000000..97b1d12
--- /dev/null
+++ b/aurweb/scripts/tuvotereminder.py
@@ -0,0 +1,28 @@
+#!/usr/bin/python3
+
+import subprocess
+import time
+
+import aurweb.config
+import aurweb.db
+
+notify_cmd = aurweb.config.get('notifications', 'notify-cmd')
+
+
+def main():
+    conn = aurweb.db.Connection()
+
+    now = int(time.time())
+    filter_from = now + 500
+    filter_to = now + 172800
+
+    cur = conn.execute("SELECT ID FROM TU_VoteInfo " +
+                       "WHERE End >= ? AND End <= ?",
+                       [filter_from, filter_to])
+
+    for vote_id in [row[0] for row in cur.fetchall()]:
+        subprocess.Popen((notify_cmd, 'tu-vote-reminder', str(vote_id))).wait()
+
+
+if __name__ == '__main__':
+    main()