search

dogsheep / github-to-sqlite

Save data from GitHub to a SQLite database
https://github-to-sqlite.dogsheep.net/
Apache License 2.0
402 stars 43 forks source link

Feature: pull request reviews and comments #46

Open bhrutledge opened 4 years ago

bhrutledge commented 4 years ago

Hi there! I saw your presentation at Boston Python. I'm already a light user of Datasette (thank you!), but wasn't aware of this project.

I've been working on a "pull request dashboard" to get a comprehensive view of the state of open PR's, esp. related to reviews (i.e., pending, approved, changes requested). Currently it's a CLI command, but I thought a Datasette UI might be fun.

I see that PR's are available from the issues command, but I don't see reviews anywhere. From the API docs, it looks like there are separate endpoints for those (as well as pull requests in general). What do you think about adding that? Would you accept a PR? Any sense of the level of effort?

simonw commented 4 years ago

Oh that's interesting - i didn't realize "reviews" were a separate concept. I'd definitely accept a pull request adding those!

simonw commented 3 years ago

Pull request are now added, thanks to @adamjonas.

simonw commented 3 years ago

As for reviews... here's the output of github-to-sqlite get https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48/reviews --accept 'application/vnd.github.v3+json'

[
    {
        "id": 503368921,
        "node_id": "MDE3OlB1bGxSZXF1ZXN0UmV2aWV3NTAzMzY4OTIx",
        "user": {
            "login": "simonw",
            "id": 9599,
            "node_id": "MDQ6VXNlcjk1OTk=",
            "avatar_url": "https://avatars0.githubusercontent.com/u/9599?u=5968723deb1a55b82620e106f5ca58e9b11a0942&v=4",
            "gravatar_id": "",
            "url": "https://api.github.com/users/simonw",
            "html_url": "https://github.com/simonw",
            "followers_url": "https://api.github.com/users/simonw/followers",
            "following_url": "https://api.github.com/users/simonw/following{/other_user}",
            "gists_url": "https://api.github.com/users/simonw/gists{/gist_id}",
            "starred_url": "https://api.github.com/users/simonw/starred{/owner}{/repo}",
            "subscriptions_url": "https://api.github.com/users/simonw/subscriptions",
            "organizations_url": "https://api.github.com/users/simonw/orgs",
            "repos_url": "https://api.github.com/users/simonw/repos",
            "events_url": "https://api.github.com/users/simonw/events{/privacy}",
            "received_events_url": "https://api.github.com/users/simonw/received_events",
            "type": "User",
            "site_admin": false
        },
        "body": "",
        "state": "CHANGES_REQUESTED",
        "html_url": "https://github.com/dogsheep/github-to-sqlite/pull/48#pullrequestreview-503368921",
        "pull_request_url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48",
        "author_association": "MEMBER",
        "_links": {
            "html": {
                "href": "https://github.com/dogsheep/github-to-sqlite/pull/48#pullrequestreview-503368921"
            },
            "pull_request": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48"
            }
        },
        "submitted_at": "2020-10-06T21:28:40Z",
        "commit_id": "4f33b850bd37829262dd29e1c520afffebedc19c"
    },
    {
        "id": 513118561,
        "node_id": "MDE3OlB1bGxSZXF1ZXN0UmV2aWV3NTEzMTE4NTYx",
        "user": {
            "login": "adamjonas",
            "id": 755825,
            "node_id": "MDQ6VXNlcjc1NTgyNQ==",
            "avatar_url": "https://avatars1.githubusercontent.com/u/755825?v=4",
            "gravatar_id": "",
            "url": "https://api.github.com/users/adamjonas",
            "html_url": "https://github.com/adamjonas",
            "followers_url": "https://api.github.com/users/adamjonas/followers",
            "following_url": "https://api.github.com/users/adamjonas/following{/other_user}",
            "gists_url": "https://api.github.com/users/adamjonas/gists{/gist_id}",
            "starred_url": "https://api.github.com/users/adamjonas/starred{/owner}{/repo}",
            "subscriptions_url": "https://api.github.com/users/adamjonas/subscriptions",
            "organizations_url": "https://api.github.com/users/adamjonas/orgs",
            "repos_url": "https://api.github.com/users/adamjonas/repos",
            "events_url": "https://api.github.com/users/adamjonas/events{/privacy}",
            "received_events_url": "https://api.github.com/users/adamjonas/received_events",
            "type": "User",
            "site_admin": false
        },
        "body": "",
        "state": "COMMENTED",
        "html_url": "https://github.com/dogsheep/github-to-sqlite/pull/48#pullrequestreview-513118561",
        "pull_request_url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48",
        "author_association": "CONTRIBUTOR",
        "_links": {
            "html": {
                "href": "https://github.com/dogsheep/github-to-sqlite/pull/48#pullrequestreview-513118561"
            },
            "pull_request": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48"
            }
        },
        "submitted_at": "2020-10-20T20:45:05Z",
        "commit_id": "4f33b850bd37829262dd29e1c520afffebedc19c"
    },
    {
        "id": 513127529,
        "node_id": "MDE3OlB1bGxSZXF1ZXN0UmV2aWV3NTEzMTI3NTI5",
        "user": {
            "login": "adamjonas",
            "id": 755825,
            "node_id": "MDQ6VXNlcjc1NTgyNQ==",
            "avatar_url": "https://avatars1.githubusercontent.com/u/755825?v=4",
            "gravatar_id": "",
            "url": "https://api.github.com/users/adamjonas",
            "html_url": "https://github.com/adamjonas",
            "followers_url": "https://api.github.com/users/adamjonas/followers",
            "following_url": "https://api.github.com/users/adamjonas/following{/other_user}",
            "gists_url": "https://api.github.com/users/adamjonas/gists{/gist_id}",
            "starred_url": "https://api.github.com/users/adamjonas/starred{/owner}{/repo}",
            "subscriptions_url": "https://api.github.com/users/adamjonas/subscriptions",
            "organizations_url": "https://api.github.com/users/adamjonas/orgs",
            "repos_url": "https://api.github.com/users/adamjonas/repos",
            "events_url": "https://api.github.com/users/adamjonas/events{/privacy}",
            "received_events_url": "https://api.github.com/users/adamjonas/received_events",
            "type": "User",
            "site_admin": false
        },
        "body": "",
        "state": "COMMENTED",
        "html_url": "https://github.com/dogsheep/github-to-sqlite/pull/48#pullrequestreview-513127529",
        "pull_request_url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48",
        "author_association": "CONTRIBUTOR",
        "_links": {
            "html": {
                "href": "https://github.com/dogsheep/github-to-sqlite/pull/48#pullrequestreview-513127529"
            },
            "pull_request": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48"
            }
        },
        "submitted_at": "2020-10-20T20:57:33Z",
        "commit_id": "3a0d5c498f9faae4e40aab204cd01b965a4f61f3"
    }
]
simonw commented 3 years ago

It seems like there's a lot missing from that - those aren't particularly interesting given the data that is returned.

From the docs at https://docs.github.com/en/free-pro-team@latest/rest/reference/pulls#reviews it looks like each review consists of multiple comments, and the comments are where the useful material is - https://docs.github.com/en/free-pro-team@latest/rest/reference/pulls#list-comments-for-a-pull-request-review

github-to-sqlite get https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48/reviews/503368921/comments --accept 'application/vnd.github.v3+json'

[
    {
        "id": 500603838,
        "node_id": "MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDUwMDYwMzgzOA==",
        "url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/comments/500603838",
        "pull_request_review_id": 503368921,
        "diff_hunk": "@@ -0,0 +1,370 @@\n+[\n+    {\n+        \"url\": \"https://api.github.com/repos/simonw/datasette/pulls/571\",\n+        \"id\": 313384926,\n+        \"node_id\": \"MDExOlB1bGxSZXF1ZXN0MzEzMzg0OTI2\",\n+        \"html_url\": \"https://github.com/simonw/datasette/pull/571\",\n+        \"diff_url\": \"https://github.com/simonw/datasette/pull/571.diff\",\n+        \"patch_url\": \"https://github.com/simonw/datasette/pull/571.patch\",\n+        \"issue_url\": \"https://api.github.com/repos/simonw/datasette/issues/571\",\n+        \"number\": 571,\n+        \"state\": \"closed\",\n+        \"locked\": false,\n+        \"title\": \"detect_fts now works with alternative table escaping\",\n+        \"user\": {\n+          \"login\": \"simonw\",\n+          \"id\": 9599,\n+          \"node_id\": \"MDQ6VXNlcjk1OTk=\",\n+          \"avatar_url\": \"https://avatars0.githubusercontent.com/u/9599?v=4\",\n+          \"gravatar_id\": \"\",\n+          \"url\": \"https://api.github.com/users/simonw\",\n+          \"html_url\": \"https://github.com/simonw\",\n+          \"followers_url\": \"https://api.github.com/users/simonw/followers\",\n+          \"following_url\": \"https://api.github.com/users/simonw/following{/other_user}\",\n+          \"gists_url\": \"https://api.github.com/users/simonw/gists{/gist_id}\",\n+          \"starred_url\": \"https://api.github.com/users/simonw/starred{/owner}{/repo}\",\n+          \"subscriptions_url\": \"https://api.github.com/users/simonw/subscriptions\",\n+          \"organizations_url\": \"https://api.github.com/users/simonw/orgs\",\n+          \"repos_url\": \"https://api.github.com/users/simonw/repos\",\n+          \"events_url\": \"https://api.github.com/users/simonw/events{/privacy}\",\n+          \"received_events_url\": \"https://api.github.com/users/simonw/received_events\",\n+          \"type\": \"User\",\n+          \"site_admin\": false\n+        },\n+        \"body\": \"Fixes #570\",\n+        \"created_at\": \"2019-09-03T00:23:39Z\",\n+        \"updated_at\": \"2019-09-03T00:32:28Z\",\n+        \"closed_at\": \"2019-09-03T00:32:28Z\",\n+        \"merged_at\": \"2019-09-03T00:32:28Z\",\n+        \"merge_commit_sha\": \"2dc5c8dc259a0606162673d394ba8cc1c6f54428\",\n+        \"assignee\": null,\n+        \"assignees\": [\n+\n+        ],\n+        \"requested_reviewers\": [\n+\n+        ],\n+        \"requested_teams\": [\n+\n+        ],\n+        \"labels\": [\n+\n+        ],\n+        \"milestone\": null,\n+        \"draft\": false,\n+        \"commits_url\": \"https://api.github.com/repos/simonw/datasette/pulls/571/commits\",\n+        \"review_comments_url\": \"https://api.github.com/repos/simonw/datasette/pulls/571/comments\",\n+        \"review_comment_url\": \"https://api.github.com/repos/simonw/datasette/pulls/comments{/number}\",\n+        \"comments_url\": \"https://api.github.com/repos/simonw/datasette/issues/571/comments\",\n+        \"statuses_url\": \"https://api.github.com/repos/simonw/datasette/statuses/a85239f69261c10f1a9f90514c8b5d113cb94585\",\n+        \"head\": {\n+          \"label\": \"simonw:detect-fts\",\n+          \"ref\": \"detect-fts\",\n+          \"sha\": \"a85239f69261c10f1a9f90514c8b5d113cb94585\",\n+          \"user\": {\n+            \"login\": \"simonw\",\n+            \"id\": 9599,\n+            \"node_id\": \"MDQ6VXNlcjk1OTk=\",\n+            \"avatar_url\": \"https://avatars0.githubusercontent.com/u/9599?v=4\",\n+            \"gravatar_id\": \"\",\n+            \"url\": \"https://api.github.com/users/simonw\",\n+            \"html_url\": \"https://github.com/simonw\",\n+            \"followers_url\": \"https://api.github.com/users/simonw/followers\",\n+            \"following_url\": \"https://api.github.com/users/simonw/following{/other_user}\",\n+            \"gists_url\": \"https://api.github.com/users/simonw/gists{/gist_id}\",\n+            \"starred_url\": \"https://api.github.com/users/simonw/starred{/owner}{/repo}\",\n+            \"subscriptions_url\": \"https://api.github.com/users/simonw/subscriptions\",\n+            \"organizations_url\": \"https://api.github.com/users/simonw/orgs\",\n+            \"repos_url\": \"https://api.github.com/users/simonw/repos\",\n+            \"events_url\": \"https://api.github.com/users/simonw/events{/privacy}\",\n+            \"received_events_url\": \"https://api.github.com/users/simonw/received_events\",\n+            \"type\": \"User\",\n+            \"site_admin\": false\n+          },\n+          \"repo\": {\n+            \"id\": 107914493,\n+            \"node_id\": \"MDEwOlJlcG9zaXRvcnkxMDc5MTQ0OTM=\",\n+            \"name\": \"datasette\",\n+            \"full_name\": \"simonw/datasette\",\n+            \"private\": false,\n+            \"owner\": {\n+              \"login\": \"simonw\",\n+              \"id\": 9599,\n+              \"node_id\": \"MDQ6VXNlcjk1OTk=\",\n+              \"avatar_url\": \"https://avatars0.githubusercontent.com/u/9599?v=4\",\n+              \"gravatar_id\": \"\",\n+              \"url\": \"https://api.github.com/users/simonw\",\n+              \"html_url\": \"https://github.com/simonw\",\n+              \"followers_url\": \"https://api.github.com/users/simonw/followers\",\n+              \"following_url\": \"https://api.github.com/users/simonw/following{/other_user}\",\n+              \"gists_url\": \"https://api.github.com/users/simonw/gists{/gist_id}\",\n+              \"starred_url\": \"https://api.github.com/users/simonw/starred{/owner}{/repo}\",\n+              \"subscriptions_url\": \"https://api.github.com/users/simonw/subscriptions\",\n+              \"organizations_url\": \"https://api.github.com/users/simonw/orgs\",\n+              \"repos_url\": \"https://api.github.com/users/simonw/repos\",\n+              \"events_url\": \"https://api.github.com/users/simonw/events{/privacy}\",\n+              \"received_events_url\": \"https://api.github.com/users/simonw/received_events\",\n+              \"type\": \"User\",\n+              \"site_admin\": false\n+            },\n+            \"html_url\": \"https://github.com/simonw/datasette\",\n+            \"description\": \"An open source multi-tool for exploring and publishing data\",\n+            \"fork\": false,\n+            \"url\": \"https://api.github.com/repos/simonw/datasette\",\n+            \"forks_url\": \"https://api.github.com/repos/simonw/datasette/forks\",\n+            \"keys_url\": \"https://api.github.com/repos/simonw/datasette/keys{/key_id}\",\n+            \"collaborators_url\": \"https://api.github.com/repos/simonw/datasette/collaborators{/collaborator}\",\n+            \"teams_url\": \"https://api.github.com/repos/simonw/datasette/teams\",\n+            \"hooks_url\": \"https://api.github.com/repos/simonw/datasette/hooks\",\n+            \"issue_events_url\": \"https://api.github.com/repos/simonw/datasette/issues/events{/number}\",\n+            \"events_url\": \"https://api.github.com/repos/simonw/datasette/events\",\n+            \"assignees_url\": \"https://api.github.com/repos/simonw/datasette/assignees{/user}\",\n+            \"branches_url\": \"https://api.github.com/repos/simonw/datasette/branches{/branch}\",\n+            \"tags_url\": \"https://api.github.com/repos/simonw/datasette/tags\",\n+            \"blobs_url\": \"https://api.github.com/repos/simonw/datasette/git/blobs{/sha}\",\n+            \"git_tags_url\": \"https://api.github.com/repos/simonw/datasette/git/tags{/sha}\",\n+            \"git_refs_url\": \"https://api.github.com/repos/simonw/datasette/git/refs{/sha}\",\n+            \"trees_url\": \"https://api.github.com/repos/simonw/datasette/git/trees{/sha}\",\n+            \"statuses_url\": \"https://api.github.com/repos/simonw/datasette/statuses/{sha}\",\n+            \"languages_url\": \"https://api.github.com/repos/simonw/datasette/languages\",\n+            \"stargazers_url\": \"https://api.github.com/repos/simonw/datasette/stargazers\",\n+            \"contributors_url\": \"https://api.github.com/repos/simonw/datasette/contributors\",\n+            \"subscribers_url\": \"https://api.github.com/repos/simonw/datasette/subscribers\",\n+            \"subscription_url\": \"https://api.github.com/repos/simonw/datasette/subscription\",\n+            \"commits_url\": \"https://api.github.com/repos/simonw/datasette/commits{/sha}\",\n+            \"git_commits_url\": \"https://api.github.com/repos/simonw/datasette/git/commits{/sha}\",\n+            \"comments_url\": \"https://api.github.com/repos/simonw/datasette/comments{/number}\",\n+            \"issue_comment_url\": \"https://api.github.com/repos/simonw/datasette/issues/comments{/number}\",\n+            \"contents_url\": \"https://api.github.com/repos/simonw/datasette/contents/{+path}\",\n+            \"compare_url\": \"https://api.github.com/repos/simonw/datasette/compare/{base}...{head}\",\n+            \"merges_url\": \"https://api.github.com/repos/simonw/datasette/merges\",\n+            \"archive_url\": \"https://api.github.com/repos/simonw/datasette/{archive_format}{/ref}\",\n+            \"downloads_url\": \"https://api.github.com/repos/simonw/datasette/downloads\",\n+            \"issues_url\": \"https://api.github.com/repos/simonw/datasette/issues{/number}\",\n+            \"pulls_url\": \"https://api.github.com/repos/simonw/datasette/pulls{/number}\",\n+            \"milestones_url\": \"https://api.github.com/repos/simonw/datasette/milestones{/number}\",\n+            \"notifications_url\": \"https://api.github.com/repos/simonw/datasette/notifications{?since,all,participating}\",\n+            \"labels_url\": \"https://api.github.com/repos/simonw/datasette/labels{/name}\",\n+            \"releases_url\": \"https://api.github.com/repos/simonw/datasette/releases{/id}\",\n+            \"deployments_url\": \"https://api.github.com/repos/simonw/datasette/deployments\",\n+            \"created_at\": \"2017-10-23T00:39:03Z\",\n+            \"updated_at\": \"2020-07-27T20:42:15Z\",\n+            \"pushed_at\": \"2020-07-26T01:21:05Z\",\n+            \"git_url\": \"git://github.com/simonw/datasette.git\",\n+            \"ssh_url\": \"git@github.com:simonw/datasette.git\",\n+            \"clone_url\": \"https://github.com/simonw/datasette.git\",\n+            \"svn_url\": \"https://github.com/simonw/datasette\",\n+            \"homepage\": \"http://datasette.readthedocs.io/\",\n+            \"size\": 3487,\n+            \"stargazers_count\": 3642,\n+            \"watchers_count\": 3642,\n+            \"language\": \"Python\",\n+            \"has_issues\": true,\n+            \"has_projects\": false,\n+            \"has_downloads\": true,\n+            \"has_wiki\": true,\n+            \"has_pages\": false,\n+            \"forks_count\": 206,\n+            \"mirror_url\": null,\n+            \"archived\": false,\n+            \"disabled\": false,\n+            \"open_issues_count\": 190,\n+            \"license\": {\n+              \"key\": \"apache-2.0\",\n+              \"name\": \"Apache License 2.0\",\n+              \"spdx_id\": \"Apache-2.0\",\n+              \"url\": \"https://api.github.com/licenses/apache-2.0\",\n+              \"node_id\": \"MDc6TGljZW5zZTI=\"\n+            },\n+            \"forks\": 206,\n+            \"open_issues\": 190,\n+            \"watchers\": 3642,\n+            \"default_branch\": \"master\"\n+          }\n+        },\n+        \"base\": {\n+          \"label\": \"simonw:master\",\n+          \"ref\": \"master\",\n+          \"sha\": \"f04deebec4f3842f7bd610cd5859de529f77d50e\",\n+          \"user\": {\n+            \"login\": \"simonw\",\n+            \"id\": 9599,\n+            \"node_id\": \"MDQ6VXNlcjk1OTk=\",\n+            \"avatar_url\": \"https://avatars0.githubusercontent.com/u/9599?v=4\",\n+            \"gravatar_id\": \"\",\n+            \"url\": \"https://api.github.com/users/simonw\",\n+            \"html_url\": \"https://github.com/simonw\",\n+            \"followers_url\": \"https://api.github.com/users/simonw/followers\",\n+            \"following_url\": \"https://api.github.com/users/simonw/following{/other_user}\",\n+            \"gists_url\": \"https://api.github.com/users/simonw/gists{/gist_id}\",\n+            \"starred_url\": \"https://api.github.com/users/simonw/starred{/owner}{/repo}\",\n+            \"subscriptions_url\": \"https://api.github.com/users/simonw/subscriptions\",\n+            \"organizations_url\": \"https://api.github.com/users/simonw/orgs\",\n+            \"repos_url\": \"https://api.github.com/users/simonw/repos\",\n+            \"events_url\": \"https://api.github.com/users/simonw/events{/privacy}\",\n+            \"received_events_url\": \"https://api.github.com/users/simonw/received_events\",\n+            \"type\": \"User\",\n+            \"site_admin\": false\n+          },\n+          \"repo\": {\n+            \"id\": 107914493,\n+            \"node_id\": \"MDEwOlJlcG9zaXRvcnkxMDc5MTQ0OTM=\",\n+            \"name\": \"datasette\",\n+            \"full_name\": \"simonw/datasette\",\n+            \"private\": false,\n+            \"owner\": {\n+              \"login\": \"simonw\",\n+              \"id\": 9599,\n+              \"node_id\": \"MDQ6VXNlcjk1OTk=\",\n+              \"avatar_url\": \"https://avatars0.githubusercontent.com/u/9599?v=4\",\n+              \"gravatar_id\": \"\",\n+              \"url\": \"https://api.github.com/users/simonw\",\n+              \"html_url\": \"https://github.com/simonw\",\n+              \"followers_url\": \"https://api.github.com/users/simonw/followers\",\n+              \"following_url\": \"https://api.github.com/users/simonw/following{/other_user}\",\n+              \"gists_url\": \"https://api.github.com/users/simonw/gists{/gist_id}\",\n+              \"starred_url\": \"https://api.github.com/users/simonw/starred{/owner}{/repo}\",\n+              \"subscriptions_url\": \"https://api.github.com/users/simonw/subscriptions\",\n+              \"organizations_url\": \"https://api.github.com/users/simonw/orgs\",\n+              \"repos_url\": \"https://api.github.com/users/simonw/repos\",\n+              \"events_url\": \"https://api.github.com/users/simonw/events{/privacy}\",\n+              \"received_events_url\": \"https://api.github.com/users/simonw/received_events\",\n+              \"type\": \"User\",\n+              \"site_admin\": false\n+            },\n+            \"html_url\": \"https://github.com/simonw/datasette\",\n+            \"description\": \"An open source multi-tool for exploring and publishing data\",\n+            \"fork\": false,\n+            \"url\": \"https://api.github.com/repos/simonw/datasette\",\n+            \"forks_url\": \"https://api.github.com/repos/simonw/datasette/forks\",\n+            \"keys_url\": \"https://api.github.com/repos/simonw/datasette/keys{/key_id}\",\n+            \"collaborators_url\": \"https://api.github.com/repos/simonw/datasette/collaborators{/collaborator}\",\n+            \"teams_url\": \"https://api.github.com/repos/simonw/datasette/teams\",\n+            \"hooks_url\": \"https://api.github.com/repos/simonw/datasette/hooks\",\n+            \"issue_events_url\": \"https://api.github.com/repos/simonw/datasette/issues/events{/number}\",\n+            \"events_url\": \"https://api.github.com/repos/simonw/datasette/events\",\n+            \"assignees_url\": \"https://api.github.com/repos/simonw/datasette/assignees{/user}\",\n+            \"branches_url\": \"https://api.github.com/repos/simonw/datasette/branches{/branch}\",\n+            \"tags_url\": \"https://api.github.com/repos/simonw/datasette/tags\",\n+            \"blobs_url\": \"https://api.github.com/repos/simonw/datasette/git/blobs{/sha}\",\n+            \"git_tags_url\": \"https://api.github.com/repos/simonw/datasette/git/tags{/sha}\",\n+            \"git_refs_url\": \"https://api.github.com/repos/simonw/datasette/git/refs{/sha}\",\n+            \"trees_url\": \"https://api.github.com/repos/simonw/datasette/git/trees{/sha}\",\n+            \"statuses_url\": \"https://api.github.com/repos/simonw/datasette/statuses/{sha}\",\n+            \"languages_url\": \"https://api.github.com/repos/simonw/datasette/languages\",\n+            \"stargazers_url\": \"https://api.github.com/repos/simonw/datasette/stargazers\",\n+            \"contributors_url\": \"https://api.github.com/repos/simonw/datasette/contributors\",\n+            \"subscribers_url\": \"https://api.github.com/repos/simonw/datasette/subscribers\",\n+            \"subscription_url\": \"https://api.github.com/repos/simonw/datasette/subscription\",\n+            \"commits_url\": \"https://api.github.com/repos/simonw/datasette/commits{/sha}\",\n+            \"git_commits_url\": \"https://api.github.com/repos/simonw/datasette/git/commits{/sha}\",\n+            \"comments_url\": \"https://api.github.com/repos/simonw/datasette/comments{/number}\",\n+            \"issue_comment_url\": \"https://api.github.com/repos/simonw/datasette/issues/comments{/number}\",\n+            \"contents_url\": \"https://api.github.com/repos/simonw/datasette/contents/{+path}\",\n+            \"compare_url\": \"https://api.github.com/repos/simonw/datasette/compare/{base}...{head}\",\n+            \"merges_url\": \"https://api.github.com/repos/simonw/datasette/merges\",\n+            \"archive_url\": \"https://api.github.com/repos/simonw/datasette/{archive_format}{/ref}\",\n+            \"downloads_url\": \"https://api.github.com/repos/simonw/datasette/downloads\",\n+            \"issues_url\": \"https://api.github.com/repos/simonw/datasette/issues{/number}\",\n+            \"pulls_url\": \"https://api.github.com/repos/simonw/datasette/pulls{/number}\",\n+            \"milestones_url\": \"https://api.github.com/repos/simonw/datasette/milestones{/number}\",\n+            \"notifications_url\": \"https://api.github.com/repos/simonw/datasette/notifications{?since,all,participating}\",\n+            \"labels_url\": \"https://api.github.com/repos/simonw/datasette/labels{/name}\",\n+            \"releases_url\": \"https://api.github.com/repos/simonw/datasette/releases{/id}\",\n+            \"deployments_url\": \"https://api.github.com/repos/simonw/datasette/deployments\",\n+            \"created_at\": \"2017-10-23T00:39:03Z\",\n+            \"updated_at\": \"2020-07-27T20:42:15Z\",\n+            \"pushed_at\": \"2020-07-26T01:21:05Z\",\n+            \"git_url\": \"git://github.com/simonw/datasette.git\",\n+            \"ssh_url\": \"git@github.com:simonw/datasette.git\",\n+            \"clone_url\": \"https://github.com/simonw/datasette.git\",\n+            \"svn_url\": \"https://github.com/simonw/datasette\",\n+            \"homepage\": \"http://datasette.readthedocs.io/\",\n+            \"size\": 3487,\n+            \"stargazers_count\": 3642,\n+            \"watchers_count\": 3642,\n+            \"language\": \"Python\",\n+            \"has_issues\": true,\n+            \"has_projects\": false,\n+            \"has_downloads\": true,\n+            \"has_wiki\": true,\n+            \"has_pages\": false,\n+            \"forks_count\": 206,\n+            \"mirror_url\": null,\n+            \"archived\": false,\n+            \"disabled\": false,\n+            \"open_issues_count\": 190,\n+            \"license\": {\n+              \"key\": \"apache-2.0\",\n+              \"name\": \"Apache License 2.0\",\n+              \"spdx_id\": \"Apache-2.0\",\n+              \"url\": \"https://api.github.com/licenses/apache-2.0\",\n+              \"node_id\": \"MDc6TGljZW5zZTI=\"\n+            },\n+            \"forks\": 206,\n+            \"open_issues\": 190,\n+            \"watchers\": 3642,\n+            \"default_branch\": \"master\"\n+          }\n+        },\n+        \"_links\": {\n+          \"self\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/pulls/571\"\n+          },\n+          \"html\": {\n+            \"href\": \"https://github.com/simonw/datasette/pull/571\"\n+          },\n+          \"issue\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/issues/571\"\n+          },\n+          \"comments\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/issues/571/comments\"\n+          },\n+          \"review_comments\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/pulls/571/comments\"\n+          },\n+          \"review_comment\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/pulls/comments{/number}\"\n+          },\n+          \"commits\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/pulls/571/commits\"\n+          },\n+          \"statuses\": {\n+            \"href\": \"https://api.github.com/repos/simonw/datasette/statuses/a85239f69261c10f1a9f90514c8b5d113cb94585\"\n+          }\n+        },\n+        \"author_association\": \"OWNER\",\n+        \"active_lock_reason\": null,\n+        \"merged\": true,\n+        \"mergeable\": null,\n+        \"rebaseable\": null,\n+        \"mergeable_state\": \"unknown\",\n+        \"merged_by\": {",
        "path": "tests/pull_requests.json",
        "position": 342,
        "original_position": 342,
        "commit_id": "3a0d5c498f9faae4e40aab204cd01b965a4f61f3",
        "user": {
            "login": "simonw",
            "id": 9599,
            "node_id": "MDQ6VXNlcjk1OTk=",
            "avatar_url": "https://avatars0.githubusercontent.com/u/9599?u=5968723deb1a55b82620e106f5ca58e9b11a0942&v=4",
            "gravatar_id": "",
            "url": "https://api.github.com/users/simonw",
            "html_url": "https://github.com/simonw",
            "followers_url": "https://api.github.com/users/simonw/followers",
            "following_url": "https://api.github.com/users/simonw/following{/other_user}",
            "gists_url": "https://api.github.com/users/simonw/gists{/gist_id}",
            "starred_url": "https://api.github.com/users/simonw/starred{/owner}{/repo}",
            "subscriptions_url": "https://api.github.com/users/simonw/subscriptions",
            "organizations_url": "https://api.github.com/users/simonw/orgs",
            "repos_url": "https://api.github.com/users/simonw/repos",
            "events_url": "https://api.github.com/users/simonw/events{/privacy}",
            "received_events_url": "https://api.github.com/users/simonw/received_events",
            "type": "User",
            "site_admin": false
        },
        "body": "Running this should create a `merged_by` column on the `pull_requests` table which is a foreign key to the `users` table.",
        "created_at": "2020-10-06T21:22:47Z",
        "updated_at": "2020-10-20T20:56:33Z",
        "html_url": "https://github.com/dogsheep/github-to-sqlite/pull/48#discussion_r500603838",
        "pull_request_url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48",
        "author_association": "MEMBER",
        "_links": {
            "self": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/comments/500603838"
            },
            "html": {
                "href": "https://github.com/dogsheep/github-to-sqlite/pull/48#discussion_r500603838"
            },
            "pull_request": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48"
            }
        },
        "original_commit_id": "4f33b850bd37829262dd29e1c520afffebedc19c"
    },
    {
        "id": 500606198,
        "node_id": "MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDUwMDYwNjE5OA==",
        "url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/comments/500606198",
        "pull_request_review_id": 503368921,
        "diff_hunk": "@@ -0,0 +1,124 @@\n+from github_to_sqlite import utils\n+import pytest\n+import pathlib\n+import sqlite_utils\n+from sqlite_utils.db import ForeignKey\n+import json\n+\n+\n+@pytest.fixture\n+def pull_requests():\n+    return json.load(open(pathlib.Path(__file__).parent / \"pull_requests.json\"))\n+\n+\n+@pytest.fixture\n+def db(pull_requests):\n+    db = sqlite_utils.Database(memory=True)\n+    db[\"repos\"].insert(\n+        {\"id\": 1},\n+        pk=\"id\",\n+        columns={\"organization\": int, \"topics\": str, \"name\": str, \"description\": str},\n+    )\n+    utils.save_pull_requests(db, pull_requests, {\"id\": 1})\n+    return db\n+\n+\n+def test_tables(db):\n+    assert {\"pull_requests\", \"users\", \"repos\", \"milestones\"} == set(\n+        db.table_names()\n+    )\n+    assert {\n+        ForeignKey(\n+            table=\"pull_requests\", column=\"repo\", other_table=\"repos\", other_column=\"id\"\n+        ),\n+        ForeignKey(\n+            table=\"pull_requests\",\n+            column=\"milestone\",\n+            other_table=\"milestones\",\n+            other_column=\"id\",\n+        ),\n+        ForeignKey(\n+            table=\"pull_requests\", column=\"assignee\", other_table=\"users\", other_column=\"id\"\n+        ),\n+        ForeignKey(\n+            table=\"pull_requests\", column=\"user\", other_table=\"users\", other_column=\"id\"\n+        ),\n+    } == set(db[\"pull_requests\"].foreign_keys)\n+\n+\n+def test_pull_requests(db):\n+    pull_request_rows = list(db[\"pull_requests\"].rows)\n+    assert [\n+        {\n+            'id': 313384926,\n+            'node_id': 'MDExOlB1bGxSZXF1ZXN0MzEzMzg0OTI2',\n+            'number': 571,\n+            'state': 'closed',\n+            'locked': 0,\n+            'title': 'detect_fts now works with alternative table escaping',\n+            'user': 9599,\n+            'body': 'Fixes #570',\n+            'created_at': '2019-09-03T00:23:39Z',\n+            'updated_at': '2019-09-03T00:32:28Z',\n+            'closed_at': '2019-09-03T00:32:28Z',\n+            'merged_at': '2019-09-03T00:32:28Z',\n+            'merge_commit_sha': '2dc5c8dc259a0606162673d394ba8cc1c6f54428',\n+            'assignee': None,\n+            'milestone': None,\n+            'draft': 0,\n+            'head': 'a85239f69261c10f1a9f90514c8b5d113cb94585',\n+            'base': 'f04deebec4f3842f7bd610cd5859de529f77d50e',\n+            'author_association': 'OWNER',\n+            'merged': 1,\n+            'mergeable': None,\n+            'rebaseable': None,\n+            'mergeable_state': 'unknown',\n+            'merged_by': '{\"login\": \"simonw\", \"id\": 9599, \"node_id\": \"MDQ6VXNlcjk1OTk=\", \"avatar_url\": \"https://avatars0.githubusercontent.com/u/9599?v=4\", \"gravatar_id\": \"\", \"url\": \"https://api.github.com/users/simonw\", \"html_url\": \"https://github.com/simonw\", \"followers_url\": \"https://api.github.com/users/simonw/followers\", \"following_url\": \"https://api.github.com/users/simonw/following{/other_user}\", \"gists_url\": \"https://api.github.com/users/simonw/gists{/gist_id}\", \"starred_url\": \"https://api.github.com/users/simonw/starred{/owner}{/repo}\", \"subscriptions_url\": \"https://api.github.com/users/simonw/subscriptions\", \"organizations_url\": \"https://api.github.com/users/simonw/orgs\", \"repos_url\": \"https://api.github.com/users/simonw/repos\", \"events_url\": \"https://api.github.com/users/simonw/events{/privacy}\", \"received_events_url\": \"https://api.github.com/users/simonw/received_events\", \"type\": \"User\", \"site_admin\": false}',",
        "path": "tests/test_pull_requests.py",
        "position": null,
        "original_position": 76,
        "commit_id": "3a0d5c498f9faae4e40aab204cd01b965a4f61f3",
        "user": {
            "login": "simonw",
            "id": 9599,
            "node_id": "MDQ6VXNlcjk1OTk=",
            "avatar_url": "https://avatars0.githubusercontent.com/u/9599?u=5968723deb1a55b82620e106f5ca58e9b11a0942&v=4",
            "gravatar_id": "",
            "url": "https://api.github.com/users/simonw",
            "html_url": "https://github.com/simonw",
            "followers_url": "https://api.github.com/users/simonw/followers",
            "following_url": "https://api.github.com/users/simonw/following{/other_user}",
            "gists_url": "https://api.github.com/users/simonw/gists{/gist_id}",
            "starred_url": "https://api.github.com/users/simonw/starred{/owner}{/repo}",
            "subscriptions_url": "https://api.github.com/users/simonw/subscriptions",
            "organizations_url": "https://api.github.com/users/simonw/orgs",
            "repos_url": "https://api.github.com/users/simonw/repos",
            "events_url": "https://api.github.com/users/simonw/events{/privacy}",
            "received_events_url": "https://api.github.com/users/simonw/received_events",
            "type": "User",
            "site_admin": false
        },
        "body": "See above - this should be 9599, an integer reference to the row in the users table.",
        "created_at": "2020-10-06T21:27:43Z",
        "updated_at": "2020-10-20T20:56:33Z",
        "html_url": "https://github.com/dogsheep/github-to-sqlite/pull/48#discussion_r500606198",
        "pull_request_url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48",
        "author_association": "MEMBER",
        "_links": {
            "self": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/comments/500606198"
            },
            "html": {
                "href": "https://github.com/dogsheep/github-to-sqlite/pull/48#discussion_r500606198"
            },
            "pull_request": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48"
            }
        },
        "original_commit_id": "4f33b850bd37829262dd29e1c520afffebedc19c"
    },
    {
        "id": 500606665,
        "node_id": "MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDUwMDYwNjY2NQ==",
        "url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/comments/500606665",
        "pull_request_review_id": 503368921,
        "diff_hunk": "@@ -0,0 +1,124 @@\n+from github_to_sqlite import utils\n+import pytest\n+import pathlib\n+import sqlite_utils\n+from sqlite_utils.db import ForeignKey\n+import json\n+\n+\n+@pytest.fixture\n+def pull_requests():\n+    return json.load(open(pathlib.Path(__file__).parent / \"pull_requests.json\"))\n+\n+\n+@pytest.fixture\n+def db(pull_requests):\n+    db = sqlite_utils.Database(memory=True)\n+    db[\"repos\"].insert(\n+        {\"id\": 1},\n+        pk=\"id\",\n+        columns={\"organization\": int, \"topics\": str, \"name\": str, \"description\": str},\n+    )\n+    utils.save_pull_requests(db, pull_requests, {\"id\": 1})\n+    return db\n+\n+\n+def test_tables(db):\n+    assert {\"pull_requests\", \"users\", \"repos\", \"milestones\"} == set(\n+        db.table_names()\n+    )\n+    assert {\n+        ForeignKey(\n+            table=\"pull_requests\", column=\"repo\", other_table=\"repos\", other_column=\"id\"\n+        ),\n+        ForeignKey(\n+            table=\"pull_requests\",\n+            column=\"milestone\",\n+            other_table=\"milestones\",\n+            other_column=\"id\",\n+        ),\n+        ForeignKey(\n+            table=\"pull_requests\", column=\"assignee\", other_table=\"users\", other_column=\"id\"\n+        ),\n+        ForeignKey(\n+            table=\"pull_requests\", column=\"user\", other_table=\"users\", other_column=\"id\"\n+        ),\n+    } == set(db[\"pull_requests\"].foreign_keys)\n+\n+\n+def test_pull_requests(db):\n+    pull_request_rows = list(db[\"pull_requests\"].rows)\n+    assert [\n+        {\n+            'id': 313384926,",
        "path": "tests/test_pull_requests.py",
        "position": null,
        "original_position": 53,
        "commit_id": "3a0d5c498f9faae4e40aab204cd01b965a4f61f3",
        "user": {
            "login": "simonw",
            "id": 9599,
            "node_id": "MDQ6VXNlcjk1OTk=",
            "avatar_url": "https://avatars0.githubusercontent.com/u/9599?u=5968723deb1a55b82620e106f5ca58e9b11a0942&v=4",
            "gravatar_id": "",
            "url": "https://api.github.com/users/simonw",
            "html_url": "https://github.com/simonw",
            "followers_url": "https://api.github.com/users/simonw/followers",
            "following_url": "https://api.github.com/users/simonw/following{/other_user}",
            "gists_url": "https://api.github.com/users/simonw/gists{/gist_id}",
            "starred_url": "https://api.github.com/users/simonw/starred{/owner}{/repo}",
            "subscriptions_url": "https://api.github.com/users/simonw/subscriptions",
            "organizations_url": "https://api.github.com/users/simonw/orgs",
            "repos_url": "https://api.github.com/users/simonw/repos",
            "events_url": "https://api.github.com/users/simonw/events{/privacy}",
            "received_events_url": "https://api.github.com/users/simonw/received_events",
            "type": "User",
            "site_admin": false
        },
        "body": "Minor detail: I use Black for this repo, which requires double quotes - running \"black .\" in the root directory (with the latest version of Black) should handle this for you.",
        "created_at": "2020-10-06T21:28:31Z",
        "updated_at": "2020-10-20T20:56:33Z",
        "html_url": "https://github.com/dogsheep/github-to-sqlite/pull/48#discussion_r500606665",
        "pull_request_url": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48",
        "author_association": "MEMBER",
        "_links": {
            "self": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/comments/500606665"
            },
            "html": {
                "href": "https://github.com/dogsheep/github-to-sqlite/pull/48#discussion_r500606665"
            },
            "pull_request": {
                "href": "https://api.github.com/repos/dogsheep/github-to-sqlite/pulls/48"
            }
        },
        "original_commit_id": "4f33b850bd37829262dd29e1c520afffebedc19c"
    }
]

That's a lot more interesting.

simonw commented 3 years ago

So it looks like anything that pulls reviews needs to pull each review, then for each one pull the comments.

I'm going to consider this blocked on smarter rate limit handling in #51.

choldgraf commented 1 year ago

Just a quick +1 to this one from me - I would like to do a better job of tracking who is reviewing one another's pull requests in repositories, since this is a specific kind of maintenance work that I think often goes unrewarded. I can't seem to figure this out just by looking at the pull_request or issue_comments tables, so I think it would be helpful to support PR reviews natively (even if just for summary statistics). Alternatively if there is a way in the API to tell if an issue comment is part of a review, then perhaps you could quickly calculate the number of unique reviews that an author performed. But that was beyond my SQL-foo :-)