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

Nice solution.

Hey I just tried your sql file by using sqlite3 database.sqlite < delete.sql but the database does not get any smaller. Even when I set the amount t.rank <= 10 or 1. Do you have any idea why?

sqlite version:
3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2

Hey I just tried your sql file by using sqlite3 database.sqlite < delete.sql but the database does not get any smaller. Even when I set the amount t.rank <= 10 or 1. Do you have any idea why?

sqlite version:
3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2

Hi ntimo,

This script prevents your database file from getting larger only.
To reduce your database file size, try to use vacuum command of sqlite.

The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.

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, 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.