Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

github-to-sqlite tags command for fetching tags #43

Closed
simonw opened this issue Jul 18, 2020 · 4 comments
Closed

github-to-sqlite tags command for fetching tags #43

simonw opened this issue Jul 18, 2020 · 4 comments
Labels
enhancement New feature or request

Comments

@simonw
Copy link
Collaborator

simonw commented Jul 18, 2020

Fetches paginated data from https://api.github.com/repos/simonw/datasette/tags
@simonw simonw added the enhancement New feature or request label Jul 18, 2020
@simonw
Copy link
Collaborator Author

simonw commented Jul 18, 2020

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

@simonw
Copy link
Collaborator Author

simonw commented Jul 18, 2020 

$ 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": "8da108193b08abf140716f8ac499f32309dfe9cf"},
 {"repo_id": 107914493, "name": "0.39", "sha": "dedd775512daee49925882654f252df61a9e3b6d"},
 {"repo_id": 107914493, "name": "0.38", "sha": "7e357abbc38dcc9d19a2f1df3252668a48e941e4"},
 {"repo_id": 107914493, "name": "0.37.1", "sha": "be20e6991eac2baa9b43e9b26ae209bae805ede5"},
 {"repo_id": 107914493, "name": "0.37", "sha": "c9e6841482b299fceadc5ad548c2dbf58a8f1227"},
 {"repo_id": 107914493, "name": "0.36", "sha": "b031fe97636b80b05fec409ee1dffb7d044fd4e9"},
 {"repo_id": 107914493, "name": "0.35", "sha": "30b6f71b306a43605c99bef79302ed5cb22d1924"},
 {"repo_id": 107914493, "name": "0.34", "sha": "e7f60d2a9b59752e20de8412f7b0a3e9a5359a31"},
 {"repo_id": 107914493, "name": "0.33", "sha": "59e7014c8a0f4102d7dc79f517540c55c49e1554"},
 {"repo_id": 107914493, "name": "0.32", "sha": "a95bedb9c423fa6d772c93ef47bc40f13a5bea50"},
 {"repo_id": 107914493, "name": "0.31.2", "sha": "b51f258d00bb3c3b401f15d46a1fbd50394dbe1c"},
 {"repo_id": 107914493, "name": "0.31.1", "sha": "a22c7761b61baa61b8e3da7d30887468d61d6b83"},
 {"repo_id": 107914493, "name": "0.31", "sha": "7f89928062b1a1fdb2625a946f7cd5161e597401"},
 {"repo_id": 107914493, "name": "0.30.2", "sha": "2bf7ce5f517d772a16d7855a35a8a75d4456aad7"},
 {"repo_id": 107914493, "name": "0.30.1", "sha": "3ca290e0db03bb4747e24203c445873f74512107"},
 {"repo_id": 107914493, "name": "0.30", "sha": "8050f9e1ece9afd0236ad38c6458c12a4ad917e6"},
 {"repo_id": 107914493, "name": "0.29.3", "sha": "0fc8afde0eb5ef677f4ac31601540d6168c8208d"},
 {"repo_id": 107914493, "name": "0.29.2", "sha": "6abe6faff6b035e9334dd05f8c741ae9b7a47440"},
 {"repo_id": 107914493, "name": "0.29.1", "sha": "2a94f3719fb2c4335fcda374fa92f87272b02d34"},
 {"repo_id": 107914493, "name": "0.29", "sha": "fb7ee8e0ad59a15083234a48e935525f6e7257dd"},
 {"repo_id": 107914493, "name": "0.28", "sha": "e518f76c5f5dd0138032bfb26387f5bb91086a3f"},
 {"repo_id": 107914493, "name": "0.27.1", "sha": "3f3f29ac9afe7c41ffc48a3bd2af473a53eecc8a"},
 {"repo_id": 107914493, "name": "0.27", "sha": "436b8bc1d17c2ab415800ab209204f94e7f7929e"},
 {"repo_id": 107914493, "name": "0.26.2", "sha": "a418c8b44f82d456be523c8690cf7236bb648c22"},
 {"repo_id": 107914493, "name": "0.26.1", "sha": "4722acc73ce761556b18f5dcbe36b7fef2ee2c69"},
 {"repo_id": 107914493, "name": "0.26", "sha": "424e146697309a54c05d5d1ba1f840849ddbafdc"},
 {"repo_id": 107914493, "name": "0.25.2", "sha": "b5128fc53fce6a1bf3b16bad9f318451bc1d1263"},
 {"repo_id": 107914493, "name": "0.25.1", "sha": "3dc0b3fa8c9b9bd81540ffe20c8b7e7a72465274"},
 {"repo_id": 107914493, "name": "0.25", "sha": "57a71377c992753327a16b417daf79df7f506dd1"},
 {"repo_id": 107914493, "name": "0.24", "sha": "28872a1fa789f314b0342f4e6182f1c78d6e2bca"},
 {"repo_id": 107914493, "name": "0.23.2", "sha": "6df6f712b36f0fe75694174906e31242427a8d1d"},
 {"repo_id": 107914493, "name": "0.23.1", "sha": "dea86b9fba78e032ad09673e884e764387daf209"},
 {"repo_id": 107914493, "name": "0.23", "sha": "e04f5b0d348ef7275a0a5ab9eb53527105132885"},
 {"repo_id": 107914493, "name": "0.22.1", "sha": "5d6252788230d168ba09f379d1d2af867e3302ab"},
 {"repo_id": 107914493, "name": "0.22", "sha": "558d9d7bfef3dd633eb16389281b67d42c9bdeef"},
 {"repo_id": 107914493, "name": "0.21", "sha": "403211de632cd15f0820cc9399305fc43c187b47"},
 {"repo_id": 107914493, "name": "0.20", "sha": "3a5d7951ce8f35118ffdd7f8d86e09b909e1218c"},
 {"repo_id": 107914493, "name": "0.19", "sha": "ba9bfa583179c25aaef94b1f44da7eba74620b9a"},
 {"repo_id": 107914493, "name": "0.18", "sha": "43ae15c0d14b3e968e8d5bfef72ac0c39783c3a2"},
 {"repo_id": 107914493, "name": "0.17", "sha": "fb988ace7c7e2bee5ac142a0eab22431d0675a77"},
 {"repo_id": 107914493, "name": "0.16", "sha": "b6539ff04502536bd1fa96e3b1430bdafc456826"},
 {"repo_id": 107914493, "name": "0.15", "sha": "7706fe0c67aba5cfe905c7906cae9e0c43cd75b2"},
 {"repo_id": 107914493, "name": "0.14", "sha": "2edc652df6d786e4f2c3f073e3567002d248be09"},
 {"repo_id": 107914493, "name": "0.13", "sha": "c160f15c3937f8fbe581276f811e8c58f9137bb1"},
 {"repo_id": 107914493, "name": "0.12", "sha": "51bdd67691bd69082ae7690af8b905f06050ee80"},
 {"repo_id": 107914493, "name": "0.11", "sha": "b0f3d4e375655f0764f3137dbcede324f9bbc0cb"},
 {"repo_id": 107914493, "name": "0.10", "sha": "5928c11ee798a232aa4096706cd47e639d1c9fc2"},
 {"repo_id": 107914493, "name": "0.9", "sha": "d75f423b6fcfc074b7c6f8f7679da8876f181edd"},
 {"repo_id": 107914493, "name": "0.8", "sha": "fe279ab7b4ae99dab295d5cf4d39ad06d782997e"},
 {"repo_id": 107914493, "name": "0.7", "sha": "6b3b05b6db0d2a7b7cec8b8dbb4ddc5e12a376b2"},
 {"repo_id": 207052882, "name": "2.3", "sha": "7090e43d804724ef3b31ae5ca9efd6ac05f76cbc"},
 {"repo_id": 207052882, "name": "2.2", "sha": "4fe69783b55465e7692a807d3a02a710f69c9c42"},
 {"repo_id": 207052882, "name": "2.1", "sha": "9d7aed336c8e62bf372caa800cb4aae3985cbae9"},
 {"repo_id": 207052882, "name": "2.0", "sha": "44611df1524a03ce305405e5902c9615e3c73a72"},
 {"repo_id": 207052882, "name": "1.1", "sha": "5cd34bd07d704487d48ac741ee5da5317afe88d2"},
 {"repo_id": 207052882, "name": "1.0.1", "sha": "3b7ab5685de89fcb6fc92d320c0e24b17be05570"},
 {"repo_id": 207052882, "name": "1.0", "sha": "1ea30c8fb1d080bd5e38c577e3ad20bb527a2fe6"},
 {"repo_id": 207052882, "name": "0.7", "sha": "e35eec4343aa560c58c1634cc228d0d46c442304"},
 {"repo_id": 207052882, "name": "0.6", "sha": "9eb737090fafd0e5a7e314be48402374d99e9828"},
 {"repo_id": 207052882, "name": "0.5", "sha": "ae9035f8fe5aff1c54bff4c6b4c2e808a44f0f2a"},
 {"repo_id": 207052882, "name": "0.4", "sha": "8c6251c31a05c58c2bfbef114247642d1b3dbb44"},
 {"repo_id": 207052882, "name": "0.3", "sha": "f697f247468516aa4ee13b1862b59e0dba18d00f"},
 {"repo_id": 207052882, "name": "0.2", "sha": "0fe96bc50fb3d7b1c7e4577db0ddf207eaeebbb9"},
 {"repo_id": 207052882, "name": "0.1.1", "sha": "321e0284c64dc48b2143311009886293c05edb07"},
 {"repo_id": 207052882, "name": "0.1", "sha": "7387c88a3f84704548e81d43b91615c02b61a957"}]

@simonw simonw closed this as completed in b368cde Jul 18, 2020
simonw added a commit that referenced this issue Jul 18, 2020
@simonw
Added tags to demo, refs #43
05238b1
@simonw
Copy link
Collaborator Author

simonw commented Jul 18, 2020 •
edited
Loading

https://github-to-sqlite.dogsheep.net/github/tags?_facet=repo

@simonw
Copy link
Collaborator Author

simonw commented Jul 18, 2020 •
edited
Loading 

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 simonw/datasette@0.45...master
dogsheep/twitter-to-sqlite 0.21.1 2020-04-30T18:20:43Z 2 dogsheep/twitter-to-sqlite@0.21.1...master
dogsheep/github-to-sqlite 2.3 2020-07-09T23:26:34Z 2 2.3...master
dogsheep/dogsheep-photos 0.4.1 2020-05-25T20:11:20Z 2 dogsheep/dogsheep-photos@0.4.1...master
dogsheep/swarm-to-sqlite 0.3.1 2020-03-28T02:29:41Z 1 dogsheep/swarm-to-sqlite@0.3.1...master
dogsheep/hacker-news-to-sqlite 0.3.1 2020-03-21T22:39:34Z 1 dogsheep/hacker-news-to-sqlite@0.3.1...master
simonw/sqlite-utils 2.11 2020-07-08T17:36:07Z 0 simonw/sqlite-utils@2.11...master
dogsheep/healthkit-to-sqlite 0.5 2020-03-28T01:50:51Z 0 dogsheep/healthkit-to-sqlite@0.5...master
dogsheep/inaturalist-to-sqlite 0.2 2020-03-24T00:35:44Z 0 dogsheep/inaturalist-to-sqlite@0.2...master
dogsheep/genome-to-sqlite 0.1 2019-09-19T15:38:10Z 0 dogsheep/genome-to-sqlite@0.1...master
dogsheep/pocket-to-sqlite 0.2 2020-03-27T22:23:16Z 0 dogsheep/pocket-to-sqlite@0.2...master

https://github-to-sqlite.dogsheep.net/github?sql=with+most_recent_releases+as+%28%0D%0A++with+ranked+as+%28%0D%0A++++select%0D%0A++++++repo%2C%0D%0A++++++tag_name%2C%0D%0A++++++published_at%2C%0D%0A++++++row_number%28%29+OVER+%28%0D%0A++++++++partition+BY+repo%0D%0A++++++++ORDER+BY%0D%0A++++++++++published_at+DESC%0D%0A++++++%29+rank%0D%0A++++FROM%0D%0A++++++releases%0D%0A++%29%0D%0A++select%0D%0A++++*%0D%0A++from%0D%0A++++ranked%0D%0A++where%0D%0A++++rank+%3D+1%0D%0A%29%0D%0Aselect%0D%0A++repos.full_name+as+repo%2C%0D%0A++most_recent_releases.tag_name+as+release%2C%0D%0A++commits.committer_date+as+release_commit_date%2C%0D%0A++%28%0D%0A++++select%0D%0A++++++count%28*%29%0D%0A++++from%0D%0A++++++commits+c2%0D%0A++++where%0D%0A++++++c2.repo+%3D+repos.id%0D%0A++++++and+c2.committer_date+%3E+commits.committer_date%0D%0A++%29+as+commits_since_release%2C%0D%0A++%27https%3A%2F%2Fgithub.com%2F%27+%7C%7C+repos.full_name+%7C%7C+%27%2Fcompare%2F%27+%7C%7C+most_recent_releases.tag_name+%7C%7C+%27...%27+%7C%7C+repos.default_branch+as+view_commits%0D%0Afrom%0D%0A++most_recent_releases%0D%0A++join+repos+on+most_recent_releases.repo+%3D+repos.id%0D%0A++join+tags+on+tags.repo+%3D+repos.id%0D%0A++and+tags.name+%3D+most_recent_releases.tag_name%0D%0A++join+commits+on+tags.sha+%3D+commits.sha%0D%0Aorder+by%0D%0A++commits_since_release+desc

simonw added a commit that referenced this issue Jul 18, 2020
@simonw
Release 2.4
4ae4aa6 
Refs #43, #44
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@simonw