Skip to content

Instantly share code, notes, and snippets.

@nikeasyanzi
Forked from david-zw-liu/remove_old_builds.sql
Created August 20, 2021 17:43
Show Gist options
  • Save nikeasyanzi/9a42cbf09e8f05b40642295d0e1026f2 to your computer and use it in GitHub Desktop.
Save nikeasyanzi/9a42cbf09e8f05b40642295d0e1026f2 to your computer and use it in GitHub Desktop.
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);
@nikeasyanzi
Copy link
Author

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

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

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