Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Keep 1000 builds per repos for DroneCI (sqlite3 version >= 3.25 required)
-- 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);
@carthur-fm
Copy link

carthur-fm commented Dec 3, 2020

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

@carthur-fm
Copy link

carthur-fm commented Dec 3, 2020

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

@david-liu-950627
Copy link
Author

david-liu-950627 commented Dec 4, 2020

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

@carthur-fm
Copy link

carthur-fm commented Dec 4, 2020

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

@nikatjef
Copy link

nikatjef commented Feb 26, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment