cfbot_commitfest.py

#!/usr/bin/env python
#
# Poll the Commitfest app to synchronise our local database.  This doesn't
# do any other work, it just updates our "submission" table with information
# about the lastest message for each entry, creating rows as required.

import cfbot_commitfest_rpc
import cfbot_config
import cfbot_util
import json

import logging


# These tasks (jobs) are not interesting to cf app users.
IGNORE_TASK_NAMES = ("Cancel previous runs", "Determine enabled OSes")


def pull_submissions(conn, commitfest_id):
    """Fetch the list of submissions and make sure we have a row for each one.
    Update the last email time according to the Commitfest main page,
    as well as name, status, authors in case they changed."""
    cursor = conn.cursor()
    for submission in cfbot_commitfest_rpc.get_submissions_for_commitfest(
        commitfest_id
    ):
        # avoid writing for nothing by doing a read query first
        cursor.execute(
            """SELECT *
                        FROM submission
                       WHERE commitfest_id = %s
                         AND submission_id = %s
                         AND name = %s
                         AND status = %s
                         AND authors = %s
                         AND last_email_time = %s""",
            (
                commitfest_id,
                submission.id,
                submission.name,
                submission.status,
                submission.authors,
                submission.last_email_time,
            ),
        )
        if cursor.fetchone():
            # no change required
            continue
        # Sending an email to a thread will clear the backoff
        # caused by earlier failures.  That's not quite what we want, we'd
        # rather clear it only when a new patch version is posted!
        cursor.execute(
            """INSERT INTO submission (commitfest_id, submission_id,
                                              name, status, authors,
                                              last_email_time)
                      VALUES (%s, %s, %s, %s, %s, %s)
                 ON CONFLICT (commitfest_id, submission_id) DO
                      UPDATE
                      SET name = EXCLUDED.name,
                          status = EXCLUDED.status,
                          authors = EXCLUDED.authors,
                          last_email_time = EXCLUDED.last_email_time,
                          backoff_until = NULL,
                          last_backoff = NULL""",
            (
                commitfest_id,
                submission.id,
                submission.name,
                submission.status,
                submission.authors,
                submission.last_email_time,
            ),
        )
        conn.commit()


def pull_modified_threads(conn):
    """Check all threads we've never checked before, or whose last_email_time
    has moved.  We want to find the lastest message ID that has attachments
    that we understand, and remember that."""
    cursor = conn.cursor()
    cursor2 = conn.cursor()
    # don't look at threads that have changed in the last minute, because the
    # archives website seems to be a bit "eventually consistent" and it might not
    # yet show a recent message on the "flat" page
    cursor.execute("""SELECT commitfest_id, submission_id, last_email_time
                      FROM submission
                     WHERE last_email_time_checked IS NULL
                        OR (last_email_time_checked != last_email_time AND
                            last_email_time < now() - interval '1 minutes')""")
    for commitfest_id, submission_id, last_email_time in cursor:
        logging.info(
            "checking commitfest %s submission %s" % (commitfest_id, submission_id)
        )
        url = cfbot_commitfest_rpc.get_thread_url_for_submission(
            commitfest_id, submission_id
        )
        if url is None:
            message_id = None
        else:
            message_id, attachments = (
                cfbot_commitfest_rpc.get_latest_patches_from_thread_url(url)
            )
        cursor2.execute(
            """UPDATE submission
                          SET last_email_time_checked = %s,
                              last_message_id = %s
                              --last_branch_message_id = NULL
                        WHERE commitfest_id = %s
                          AND submission_id = %s""",
            (last_email_time, message_id, commitfest_id, submission_id),
        )
        conn.commit()


def make_branch_status_message(conn, branch_id=None, build_id=None, commit_id=None):
    assert branch_id or commit_id or build_id

    if branch_id:
        filter_column = "id"
    elif build_id:
        filter_column = "build_id"
    elif commit_id:
        filter_column = "commit_id"

    cursor = conn.cursor()
    # LEFT JOIN because a branch doesn't have a build until CI reports one
    cursor.execute(
        f"""SELECT branch.id, branch.build_id, branch.commit_id, submission_id,
                            url, branch.status, branch.created, branch.modified,
                            version, patch_count,
                            first_additions, first_deletions,
                            all_additions, all_deletions,
                            build.html_url
                      FROM branch
                 LEFT JOIN build USING (build_id)
                     WHERE branch.{filter_column} = %s""",
        (branch_id or build_id or commit_id,),
    )
    if row := cursor.fetchone():
        (
            branch_id,
            build_id,
            commit_id,
            submission_id,
            url,
            status,
            created,
            modified,
            version,
            patch_count,
            first_additions,
            first_deletions,
            all_additions,
            all_deletions,
            build_url,
        ) = row
    else:
        # for postgres/postgres webhooks, we won't find a branch.  the cfapp
        # doesn't want to hear about those anyway, so we'll skip them
        return None
    message = {
        "submission_id": submission_id,
        "branch_name": "cf/%d" % submission_id,
        "branch_id": branch_id,
        "build_id": build_id,
        "build_url": build_url,
        "commit_id": commit_id,
        "apply_url": url,
        "status": status,
        "created": created.isoformat(),
        "modified": modified.isoformat(),
        "version": version,
        "patch_count": patch_count,
        "first_additions": first_additions,
        "first_deletions": first_deletions,
        "all_additions": all_additions,
        "all_deletions": all_deletions,
    }
    return message


def make_task_status_message(conn, task_id):
    cursor = conn.cursor()
    cursor.execute(
        """SELECT build_id, build.commit_id, task.task_name, task.position, task.status, task.html_url, task.created, task.modified
                      FROM task
                      JOIN build USING (build_id)
                     WHERE task.task_id = %s""",
        (task_id,),
    )
    (
        build_id,
        commit_id,
        task_name,
        position,
        status,
        task_url,
        created,
        modified,
    ) = cursor.fetchone()
    message = {
        "build_id": build_id,
        "task_id": task_id,
        "commit_id": commit_id,
        "task_name": task_name,
        "position": position,
        "status": status,
        "task_url": task_url,
        "created": created.isoformat(),
        "modified": modified.isoformat(),
    }
    return message


def make_task_update_message(conn, task_id):
    task_status = make_task_status_message(conn, task_id)

    if task_status["task_name"] in IGNORE_TASK_NAMES:
        return None

    branch_status = make_branch_status_message(conn, build_id=task_status["build_id"])
    if not branch_status:
        logging.info(
            "task %s build %s is not from a branch that is interesting for the cfapp, skipping",
            task_id,
            task_status["build_id"],
        )
        # branch row not found, expected for postgres/postgres (master,
        # REL_...) branches
        return None
    message = {
        "shared_secret": cfbot_config.COMMITFEST_SHARED_SECRET,
        "task_status": task_status,
        "branch_status": branch_status,
    }
    return message


def make_branch_update_message(conn, branch_id):
    branch_status = make_branch_status_message(conn, branch_id=branch_id)
    message = {
        "shared_secret": cfbot_config.COMMITFEST_SHARED_SECRET,
        "branch_status": branch_status,
    }
    return message


# Handler for "post-branch-status" work_queue jobs.
def post_branch_status(conn, branch_id):
    message = make_branch_update_message(conn, int(branch_id))
    if cfbot_config.COMMITFEST_POST_URL:
        cfbot_util.post(cfbot_config.COMMITFEST_POST_URL, message)
    else:
        logging.info("would post to cf app: " + json.dumps(message))


# Handler for "post-task-status" work_queue jobs.
def post_task_status(conn, task_id):
    message = make_task_update_message(conn, task_id)
    if not message:
        return
    if cfbot_config.COMMITFEST_POST_URL:
        cfbot_util.post(cfbot_config.COMMITFEST_POST_URL, message)
    else:
        logging.info("would post to cf app: " + json.dumps(message))


if __name__ == "__main__":
    with cfbot_util.db() as conn:
        post_task_status(conn, "5200442562445312")