Plugins without GitHub releases show up at bottom of datasette.io/plugins

[simonw]

Currently: https://datasette.io/plugins sorts by "most recent release" by default, which means that plugins that don't use GitHub releases show up at the bottom of the page:

[simonw]

I can fall back on their releases dates from the PyPI packages table here: https://datasette.io/content/pypi_releases?_sort=md5_digest&package__startswith=datasette-sqlite-

[simonw]

I can fix this SQL view: https://datasette.io/content/plugins

CREATE VIEW plugins AS select
  repos.name as name,
  repos.full_name as full_name,
  users.login as owner,
  repos.description as description,
  plugin_repos.extra_search as extra_search,
  plugin_repos.tags as tags,
  repos.stargazers_count,
  pypi_versions.name as tag_name,
  max(pypi_releases.upload_time) as latest_release_at,
  repos.created_at as created_at,
  datasette_repos.openGraphImageUrl,
  datasette_repos.usesCustomOpenGraphImage,
  (
    select
      sum(downloads)
    from
      stats
    where
      stats.package = repos.name
      and stats.date > date('now', '-7 days')
  ) as downloads_this_week,
  (
    select
      count(*)
    from
      plugin_repos
    where
      repo = repos.full_name
  ) as is_plugin,
  (
    select
      count(*)
    from
      tool_repos
    where
      repo = repos.full_name
  ) as is_tool
from
  datasette_repos
  join repos on datasette_repos.id = repos.node_id
  left join pypi_releases on pypi_releases.package = repos.name
  left join pypi_versions on pypi_releases.version = pypi_versions.id
  join users on users.id = repos.owner
  join plugin_repos on plugin_repos.repo = datasette_repos.nameWithOwner
group by
  repos.id
order by
  latest_release_at desc;

[simonw]

Huh... max(pypi_releases.upload_time) as latest_release_at, - it's using the pypi_releases table already.

Here's the problem:

  left join pypi_releases on pypi_releases.package = repos.name

This fails because in the case of Alex's datasette-sqlite-lines plugin lives in a repo called sqlite-lines.

[simonw]

Yeah, the problem is that the datasette_repos table at https://datasette.io/content/datasette_repos/R_kgDOHPAsug has rows like:

But the package name doesn't match that repo name.

[simonw]

... and the repo name is the thing used in this YAML file: https://github.com/simonw/datasette.io/blob/99430d79e0179fdf2ea510b6afcce1179fd196da/plugin_repos.yml#L355-L384

[simonw]

Two possible solutions:

• Redesign the .yml file to use (or at least include) the package name.
• A nasty hack where we attempt to join on 'datasette-' || package_name as well.

I'm going to try that nasty hack first.

[simonw]

Yes, this fixed it: https://datasette.io/content?sql=select%0D%0A++repos.name+as+name%2C%0D%0A++repos.full_name+as+full_name%2C%0D%0A++users.login+as+owner%2C%0D%0A++repos.description+as+description%2C%0D%0A++plugin_repos.extra_search+as+extra_search%2C%0D%0A++plugin_repos.tags+as+tags%2C%0D%0A++repos.stargazers_count%2C%0D%0A++pypi_versions.name+as+tag_name%2C%0D%0A++max%28pypi_releases.upload_time%29+as+latest_release_at%2C%0D%0A++repos.created_at+as+created_at%2C%0D%0A++datasette_repos.openGraphImageUrl%2C%0D%0A++datasette_repos.usesCustomOpenGraphImage%2C%0D%0A++%28%0D%0A++++select%0D%0A++++++sum%28downloads%29%0D%0A++++from%0D%0A++++++stats%0D%0A++++where%0D%0A++++++stats.package+%3D+repos.name%0D%0A++++++and+stats.date+%3E+date%28%27now%27%2C+%27-7+days%27%29%0D%0A++%29+as+downloads_this_week%2C%0D%0A++%28%0D%0A++++select%0D%0A++++++count%28*%29%0D%0A++++from%0D%0A++++++plugin_repos%0D%0A++++where%0D%0A++++++repo+%3D+repos.full_name%0D%0A++%29+as+is_plugin%2C%0D%0A++%28%0D%0A++++select%0D%0A++++++count%28*%29%0D%0A++++from%0D%0A++++++tool_repos%0D%0A++++where%0D%0A++++++repo+%3D+repos.full_name%0D%0A++%29+as+is_tool%0D%0Afrom%0D%0A++datasette_repos%0D%0A++join+repos+on+datasette_repos.id+%3D+repos.node_id%0D%0A++left+join+pypi_releases+on+%28%0D%0A++++pypi_releases.package+%3D+repos.name+or+pypi_releases.package+%3D+%27datasette-%27+%7C%7C+repos.name%0D%0A++%29%0D%0A++left+join+pypi_versions+on+pypi_releases.version+%3D+pypi_versions.id%0D%0A++join+users+on+users.id+%3D+repos.owner%0D%0A++join+plugin_repos+on+plugin_repos.repo+%3D+datasette_repos.nameWithOwner%0D%0Agroup+by%0D%0A++repos.id%0D%0Aorder+by%0D%0A++latest_release_at+desc%3B

from
  datasette_repos
  join repos on datasette_repos.id = repos.node_id
  left join pypi_releases on (
    pypi_releases.package = repos.name or pypi_releases.package = 'datasette-' || repos.name
  )
  left join pypi_versions on pypi_releases.version = pypi_versions.id
  join users on users.id = repos.owner
  join plugin_repos on plugin_repos.repo = datasette_repos.nameWithOwner