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

Hi, thanks for this!
Just tried your sql file with a t.rank <= 100 and then made a vacuum on DB, but the database keeps with the same size (18GB). See edit

Did the vacuum command work for you? Could you reduce your db size?

Edit:
As I have seen, it only deleted selected builds but it didn't expand to other tables due to missing (defaulted to false) PRAGMA foreign_keys = ON;. Setting on the top of the SQL worked as expected and, with a t.rank <= 100 and vacuum reduced DB from 18GB to 2,1GB

Worked as expected!

Thanks,
Regards

Hi sbengo,

Sorry for replying to you now. I didn't notice that PRAGMA foreign_keys is defaulted to false.
So i thought sqlite will perform cascading delete as normal relational database, but I was wrong.
Thank for your help. I will add it to top of script

Thanks,
Regards

Hello @msglight4874, I tried connecting to the shell of my Drone docker instance to run your script but wasnt able to.
any advice on which to run this script and how?
Do i run it on drone server or the runner and how do i connect to run this script. thanks in advance

Hello @msglight4874, I tried connecting to the shell of my Drone docker instance to run your script but wasnt able to.
any advice on which to run this script and how?
Do i run it on drone server or the runner and how do i connect to run this script. thanks in advance

Hi @carthur-fm,

I save this SQL query to a file, and write another shell script to run sql command on drone database by cron-job.

#!/bin/bash

set -e

VOLUME_PATH=$(docker volume inspect  --format '{{ .Mountpoint }}' drone_data)
DATABASE_PATH="${VOLUME_PATH}/database.sqlite"

/usr/local/bin/sqlite3 $DATABASE_PATH < remove_old_builds.sql

FYI.

thanks a lot! and where do i use the vacuum command ? add it to the cron job script?

Also i got the error, Error: No such volume: drone_data. could it be that drone has make some renaming ?

Hi @carthur-fm ,

thanks a lot! and where do i use the vacuum command ? add it to the cron job script?

change drone_data to your drone CI docker volume name.

Also i got the error, Error: No such volume: drone_data. could it be that drone has make some renaming ?

You can use vacuum in sqlite CLI.

sqlite3 ./database.sqlite # to get into CLI

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.