Skip to content

Instantly share code, notes, and snippets.

@YouSysAdmin

YouSysAdmin/remove_old_builds.sql

Forked from david-zw-liu/remove_old_builds.sql
Created March 28, 2021 07:34
Download ZIP

Revisions

  1. David Liu revised this gist Jan 15, 2020. No changes.
  2. David Liu revised this gist Jan 15, 2020. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions remove_old_builds.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,7 @@
    -- 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 (
  3. David Liu revised this gist Sep 17, 2019. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion remove_old_builds.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -12,4 +12,4 @@ WITH n_build_ids_per_repo as (
    DELETE FROM
    builds
    WHERE
    builds.build_id NOT IN (SELECT build_id FROM n_build_ids_per_repo)
    builds.build_id NOT IN (SELECT build_id FROM n_build_ids_per_repo);
  4. David Liu revised this gist Aug 27, 2019. No changes.
  5. David Liu created this gist Aug 27, 2019.
    15 changes: 15 additions & 0 deletions remove_old_builds.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,15 @@
    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)