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

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

Closed simonw closed 4 years ago

simonw commented 4 years ago

Fetches paginated data from https://api.github.com/repos/simonw/datasette/tags

simonw commented 4 years ago

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 commented 4 years ago 
$ 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 commented 4 years ago

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

simonw commented 4 years ago 
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
dogsheep/healthkit-to-sqlite 0.5 2020-03-28T01:50:51Z 0 https://github.com/dogsheep/healthkit-to-sqlite/compare/0.5...master
dogsheep/inaturalist-to-sqlite 0.2 2020-03-24T00:35:44Z 0 https://github.com/dogsheep/inaturalist-to-sqlite/compare/0.2...master
dogsheep/genome-to-sqlite 0.1 2019-09-19T15:38:10Z 0 https://github.com/dogsheep/genome-to-sqlite/compare/0.1...master
dogsheep/pocket-to-sqlite 0.2 2020-03-27T22:23:16Z 0 https://github.com/dogsheep/pocket-to-sqlite/compare/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