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);
@markstos
Copy link

markstos commented Nov 12, 2019

Nice solution.

@ntimo
Copy link

ntimo commented Dec 25, 2019

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

@david-liu-950627
Copy link
Author

david-liu-950627 commented Dec 26, 2019

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.

@sbengo
Copy link

sbengo commented Jan 14, 2020

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

@david-liu-950627
Copy link
Author

david-liu-950627 commented Jan 15, 2020

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

@carthur-fm
Copy link

carthur-fm commented Dec 3, 2020

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

@david-liu-950627
Copy link
Author

david-liu-950627 commented Dec 3, 2020

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.

@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