nhardt/github-to-mysql

Synchronizes GitHub data (issues, labels, ...) to a MySQL database

GitHub to MySQL

Synchronizes GitHub data (issues, labels, ...) to a MySQL database.

Features:

• synchronize issues and pull requests
  • support multiple pages of results
• synchronize labels
• synchronize milestones

Getting started

• clone the repository or download a stable release and unzip it
• run composer install
• copy .env.dist to create a .env file
• create the DB tables by running ./github-to-mysql db-init --force You can check which DB queries will be run by removing the --force option (the queries will NOT be run if the option is missing).

You can also simply run ./github-to-mysql without arguments and follow the instructions.

The .env file contains the configuration to connect to the MySQL database as well as the GitHub token. Alternatively to using this file you can set up all the environment variables it contains.

Usage

./github-to-mysql sync user/repository

Use cases

With GitHub data in MySQL, you can extract interesting metrics. Here are a few examples:

• discover labels with the most issues or pull requests

• follow number of issues planned per milestone

• ratio of pull requests VS issues

  SELECT count(*) AS count, IF(is_pull_request, 'Pull request', 'Issue') AS is_pull_request
  FROM github_issues
  GROUP BY is_pull_request
  ORDER BY is_pull_request ASC

  

• average number of days to merge a pull request over the past weeks

  SELECT
      YEARWEEK(IFNULL(closed_at, NOW())) AS week,
      AVG(TIMESTAMPDIFF(DAY, created_at, IFNULL(closed_at, NOW())))
  FROM github_issues
  WHERE is_pull_request = 1
  GROUP BY week;

  

• average number of pull requests open every day

  SELECT
  	DATE(updated_at) AS day,
  	(SELECT COUNT(*) FROM github_issues WHERE is_pull_request = 1 AND created_at < day AND (closed_at >= day OR open = 1)) AS pr_open
  FROM github_issues
  WHERE updated_at IS NOT NULL
  GROUP BY day;

  

• total number of issues

  

Those are just examples to illustrate the possibilities, we hope it will give you some ideas.