Keep 1000 builds per repos for DroneCI (sqlite3 version >= 3.25 required)

remove_old_builds.sql

-- Thank @sbengo to figure out foreign_keys constraints is defaults to false in sqlite
-- Enable to delete logs by cascading delete
PRAGMA foreign_keys = ON;

WITH n_build_ids_per_repo as (
  SELECT build_id
  FROM (
    SELECT
      build_id,
      build_repo_id,
      DENSE_RANK() OVER (PARTITION BY build_repo_id ORDER BY build_id DESC) AS rank
    FROM builds
  ) AS t
  WHERE t.rank <= 1000
)
DELETE FROM
  builds 
WHERE 
  builds.build_id NOT IN (SELECT build_id FROM n_build_ids_per_repo);

And run the following command to downsize
sqlite3 /path/to/db/foo.db 'VACUUM;'

https://stackoverflow.com/questions/18126997/how-to-vacuum-sqlite-database