issue_comments
4 rows where issue = 660355904
This data as json, CSV (advanced)
Suggested facets: created_at (date), updated_at (date)
| id ▼ | html_url | issue_url | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 660536265 | https://github.com/dogsheep/github-to-sqlite/issues/43#issuecomment-660536265 | https://api.github.com/repos/dogsheep/github-to-sqlite/issues/43 | MDEyOklzc3VlQ29tbWVudDY2MDUzNjI2NQ== | simonw 9599 | 2020-07-18T20:15:12Z | 2020-07-18T20:15:12Z | MEMBER | I want to create a SQL query which shows me all of my repositories that have commits that are NOT in the most recent release. The releases table doesn't have enough information for this because it doesn't tell you the commit hash associated with each release, just the tag: https://github-to-sqlite.dogsheep.net/github/releases | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | github-to-sqlite tags command for fetching tags 660355904 | |
| 660547502 | https://github.com/dogsheep/github-to-sqlite/issues/43#issuecomment-660547502 | https://api.github.com/repos/dogsheep/github-to-sqlite/issues/43 | MDEyOklzc3VlQ29tbWVudDY2MDU0NzUwMg== | simonw 9599 | 2020-07-18T21:50:37Z | 2020-07-18T21:50:37Z | MEMBER | ``` $ github-to-sqlite tags tags.db simonw/datasette dogsheep/github-to-sqlite $ sqlite-utils tables tags.db --counts [{"table": "users", "count": 2}, {"table": "licenses", "count": 1}, {"table": "repos", "count": 2}, {"table": "tags", "count": 76}, {"table": "licenses_fts", "count": 1}, {"table": "licenses_fts_data", "count": 3}, {"table": "licenses_fts_idx", "count": 1}, {"table": "licenses_fts_docsize", "count": 1}, {"table": "licenses_fts_config", "count": 1}, {"table": "repos_fts", "count": 2}, {"table": "repos_fts_data", "count": 3}, {"table": "repos_fts_idx", "count": 1}, {"table": "repos_fts_docsize", "count": 2}, {"table": "repos_fts_config", "count": 1}, {"table": "users_fts", "count": 2}, {"table": "users_fts_data", "count": 3}, {"table": "users_fts_idx", "count": 1}, {"table": "users_fts_docsize", "count": 2}, {"table": "users_fts_config", "count": 1}] $ sqlite-utils rows tags.db tags [{"repo_id": 107914493, "name": "0.45", "sha": "f1f581b7ffcd5d8f3ae6c1c654d813a6641410eb"}, {"repo_id": 107914493, "name": "0.45a5", "sha": "676bb64c877d73f8ff496cef4632f5a8a5a9283c"}, {"repo_id": 107914493, "name": "0.45a4", "sha": "265483173bc8341dc02c8b782b9b59d2ce8bbedb"}, {"repo_id": 107914493, "name": "0.45a3", "sha": "1f55a4a2b68fa65e56a28baeb7f44122fdeca7e7"}, {"repo_id": 107914493, "name": "0.45a2", "sha": "1a5b7d318fa923edfcefd3df8f64dae2e9c49d3f"}, {"repo_id": 107914493, "name": "0.45a1", "sha": "b59b92b1b0517cf18fa748ff9d0a0bf86298dd43"}, {"repo_id": 107914493, "name": "0.45a0", "sha": "dda932d818b34ccab11730a76554f0a3748d8348"}, {"repo_id": 107914493, "name": "0.44", "sha": "b906030235efbdff536405d66078f4868ce0d3bd"}, {"repo_id": 107914493, "name": "0.43", "sha": "d56f402822df102f9cf1a9a056449d01a15e3aae"}, {"repo_id": 107914493, "name": "0.42", "sha": "af6c6c5d6f929f951c0e63bfd1c82e37a071b50f"}, {"repo_id": 107914493, "name": "0.41", "sha": "182e5c8745c94576718315f7596ccc81e5e2417b"}, {"repo_id": 107914493, "name": "0.40", "sha": "8da108193b08ab… | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | github-to-sqlite tags command for fetching tags 660355904 | |
| 660548780 | https://github.com/dogsheep/github-to-sqlite/issues/43#issuecomment-660548780 | https://api.github.com/repos/dogsheep/github-to-sqlite/issues/43 | MDEyOklzc3VlQ29tbWVudDY2MDU0ODc4MA== | simonw 9599 | 2020-07-18T22:02:37Z | 2020-07-18T23:05:56Z | MEMBER | https://github-to-sqlite.dogsheep.net/github/tags?_facet=repo | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | github-to-sqlite tags command for fetching tags 660355904 | |
| 660551397 | https://github.com/dogsheep/github-to-sqlite/issues/43#issuecomment-660551397 | https://api.github.com/repos/dogsheep/github-to-sqlite/issues/43 | MDEyOklzc3VlQ29tbWVudDY2MDU1MTM5Nw== | simonw 9599 | 2020-07-18T22:27:32Z | 2020-07-18T23:05:45Z | MEMBER | ```sql with most_recent_releases as ( with ranked as ( select repo, tag_name, published_at, row_number() OVER ( partition BY repo ORDER BY published_at DESC ) rank FROM releases ) select * from ranked where rank = 1 ) select repos.full_name as repo, most_recent_releases.tag_name as release, commits.committer_date as release_commit_date, ( select count(*) from commits c2 where c2.repo = repos.id and c2.committer_date > commits.committer_date ) as commits_since_release, 'https://github.com/' || repos.full_name || '/compare/' || most_recent_releases.tag_name || '...' || repos.default_branch as view_commits from most_recent_releases join repos on most_recent_releases.repo = repos.id join tags on tags.repo = repos.id and tags.name = most_recent_releases.tag_name join commits on tags.sha = commits.sha order by commits_since_release desc ``` repo | release | release_commit_date | commits_since_release | view_commits -- | -- | -- | -- | -- simonw/datasette | 0.45 | 2020-07-01T21:43:07Z | 9 | https://github.com/simonw/datasette/compare/0.45...master dogsheep/twitter-to-sqlite | 0.21.1 | 2020-04-30T18:20:43Z | 2 | https://github.com/dogsheep/twitter-to-sqlite/compare/0.21.1...master dogsheep/github-to-sqlite | 2.3 | 2020-07-09T23:26:34Z | 2 | https://github.com/dogsheep/github-to-sqlite/compare/2.3...master dogsheep/dogsheep-photos | 0.4.1 | 2020-05-25T20:11:20Z | 2 | https://github.com/dogsheep/dogsheep-photos/compare/0.4.1...master dogsheep/swarm-to-sqlite | 0.3.1 | 2020-03-28T02:29:41Z | 1 | https://github.com/dogsheep/swarm-to-sqlite/compare/0.3.1...master dogsheep/hacker-news-to-sqlite | 0.3.1 | 2020-03-21T22:39:34Z | 1 | https://github.com/dogsheep/hacker-news-to-sqlite/compare/0.3.1...master simonw/sqlite-utils | 2.11 | 2020-07-08T17:36:07Z | 0 | https://github.com/simonw/sqlite-utils/compare/2.11...master dog… | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | github-to-sqlite tags command for fetching tags 660355904 |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [issue_comments] (
[html_url] TEXT,
[issue_url] TEXT,
[id] INTEGER PRIMARY KEY,
[node_id] TEXT,
[user] INTEGER REFERENCES [users]([id]),
[created_at] TEXT,
[updated_at] TEXT,
[author_association] TEXT,
[body] TEXT,
[reactions] TEXT,
[issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
ON [issue_comments] ([user]);