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);

was able to make it work. vaccum; didnt seem to recliam the space. maybe a reboot might. thanks a lot @msglight4874

Silly question, but can this be done while Drone server is running, or should we stop it before truncating the table (Sqlite3)? I am assuming we do not, but I am not real familiar with how Drone handles Sqlite3's DB.

Hi @nikatjef ,
It can be done while the server is running. That's fine.