<commit_before>#!/usr/bin/env perl
use strict;
use warnings;
use JSON::XS;
use Storable qw(thaw);
use DBI;
use VRPipe;

# we have for example over 40million File rows, but only 7million
# stepoutputfiles and 2million persistentarraymember files (probably datasource
# input files), and it might take days to do db_pre_upgrade if we don't first
# delete all the extraneous rows we've built up over time.

warn "I will alter this database: ", VRPipe::Persistent::SchemaBase->get_dsn, " [ctrl-c now to abort]\n";
sleep(3);

my $json  = JSON::XS->new->utf8->canonical;
my $dbh   = DBI->connect(VRPipe::Persistent::SchemaBase->get_dsn, VRPipe::Persistent::SchemaBase->get_user, VRPipe::Persistent::SchemaBase->get_password, { RaiseError => 1, AutoCommit => 0 }) or die $DBI::errstr;
my $limit = 10000;

warn "\n ## I'll delete File rows that aren't needed, even while the database is being used in production\n\n";
# first get the id of the most recent file row, so we won't delete any files
# created after we started this script
my $select_max = $dbh->prepare("SELECT id FROM file ORDER BY id DESC LIMIT 1");
$select_max->execute;
my $max_row = $select_max->fetch;
my ($max_id) = @$max_row;
warn "I won't delete any files with an id higher than $max_id (the current highest)\n";

my %needed_files;
warn "Will find datasource source files\n";
foreach my $ds (VRPipe::DataSource->search({})) {
    my $instance = $ds->_source_instance;
    next unless $instance->can('source_file');
    # skip bad source files
    my $source = $ds->source;
    next unless $source =~ /^\S+/;
    my $has_ps = VRPipe::PipelineSetup->search({ datasource => $ds->id });
    next unless $has_ps;
    $needed_files{ $instance->source_file->id } = 1;
}

warn "Will find datasource input files\n";
my $pager = VRPipe::PersistentArrayMember->get_column_values_paged('class_id', { class => 'VRPipe::File' });
while (my $fids = $pager->next) {
    foreach my $fid (@$fids) {
        $needed_files{$fid} = 1;
    }
}

warn "Will find step output files\n";
$pager = VRPipe::StepOutputFile->get_column_values_paged('file', {}); # we can't skip temp files or this table will reference non-existent files
while (my $fids = $pager->next) {
    foreach my $fid (@$fids) {
        $needed_files{$fid} = 1;
    }
}

my $num_needed = keys %needed_files;
warn "Will find the chain of files leading to or from our needed files ($num_needed)\n";
my %chained_files;
my $chained = 0;
while (my ($fid) = each %needed_files) {
    add_chained_ids($fid);
    $chained++;
    if ($chained % $limit == 0) {
        warn " found chain for $chained files so far\n";
    }
}

sub add_chained_ids {
    my $fid = shift;
    my $pm = VRPipe::File->get_column_values(['parent', 'moved_to'], { id => $fid }, { rows => 1 });
    my ($parent, $moved_to) = @{ $pm->[0] };
    if ($parent) {
        $chained_files{$parent} = 1;
        add_chained_ids($parent);
    }
    if ($moved_to) {
        $chained_files{$moved_to} = 1;
        while (1) {
            ($moved_to) = VRPipe::File->get_column_values('moved_to', { id => $moved_to }, { rows => 1 });
            $moved_to || last;
            $chained_files{$moved_to} = 1;
        }
    }
    
    my $current_id = $fid;
    while (1) {
        my ($moved_from) = VRPipe::File->get_column_values('id', { moved_to => $current_id }, { rows => 1 });
        $moved_from || last;
        $chained_files{$moved_from} = 1;
        $current_id = $moved_from;
    }<commit_msg>fix infinite loop possibility when files have been moved back to themselves<commit_after>#!/usr/bin/env perl
use strict;
use warnings;
use JSON::XS;
use Storable qw(thaw);
use DBI;
use VRPipe;

# we have for example over 40million File rows, but only 7million
# stepoutputfiles and 2million persistentarraymember files (probably datasource
# input files), and it might take days to do db_pre_upgrade if we don't first
# delete all the extraneous rows we've built up over time.

warn "I will alter this database: ", VRPipe::Persistent::SchemaBase->get_dsn, " [ctrl-c now to abort]\n";
sleep(3);

my $json  = JSON::XS->new->utf8->canonical;
my $dbh   = DBI->connect(VRPipe::Persistent::SchemaBase->get_dsn, VRPipe::Persistent::SchemaBase->get_user, VRPipe::Persistent::SchemaBase->get_password, { RaiseError => 1, AutoCommit => 0 }) or die $DBI::errstr;
my $limit = 10000;

warn "\n ## I'll delete File rows that aren't needed, even while the database is being used in production\n\n";
# first get the id of the most recent file row, so we won't delete any files
# created after we started this script
my $select_max = $dbh->prepare("SELECT id FROM file ORDER BY id DESC LIMIT 1");
$select_max->execute;
my $max_row = $select_max->fetch;
my ($max_id) = @$max_row;
warn "I won't delete any files with an id higher than $max_id (the current highest)\n";

my %needed_files;
warn "Will find datasource source files\n";
foreach my $ds (VRPipe::DataSource->search({})) {
    my $instance = $ds->_source_instance;
    next unless $instance->can('source_file');
    # skip bad source files
    my $source = $ds->source;
    next unless $source =~ /^\S+/;
    my $has_ps = VRPipe::PipelineSetup->search({ datasource => $ds->id });
    next unless $has_ps;
    $needed_files{ $instance->source_file->id } = 1;
}

warn "Will find datasource input files\n";
my $pager = VRPipe::PersistentArrayMember->get_column_values_paged('class_id', { class => 'VRPipe::File' });
while (my $fids = $pager->next) {
    foreach my $fid (@$fids) {
        $needed_files{$fid} = 1;
    }
}

warn "Will find step output files\n";
$pager = VRPipe::StepOutputFile->get_column_values_paged('file', {}); # we can't skip temp files or this table will reference non-existent files
while (my $fids = $pager->next) {
    foreach my $fid (@$fids) {
        $needed_files{$fid} = 1;
    }
}

my $num_needed = keys %needed_files;
warn "Will find the chain of files leading to or from our needed files ($num_needed)\n";
my %chained_files;
my $chained = 0;
foreach my $fid (sort keys %needed_files) {
    add_chained_ids($fid);
    $chained++;
    if ($chained % $limit == 0) {
        warn " found chain for $chained files so far\n";
    }
}

sub add_chained_ids {
    my $fid = shift;
    my $pm = VRPipe::File->get_column_values(['parent', 'moved_to'], { id => $fid }, { rows => 1 });
    my ($parent, $moved_to) = @{ $pm->[0] };
    if ($parent) {
        $chained_files{$parent} = 1;
        add_chained_ids($parent);
    }
    if ($moved_to) {
        $chained_files{$moved_to} = 1;
        my %in_loop;
        while (1) {
            ($moved_to) = VRPipe::File->get_column_values('moved_to', { id => $moved_to }, { rows => 1 });
            $moved_to || last;
            last if exists $in_loop{$moved_to}; # break infinite loop
            $chained_files{$moved_to} = 1;
            $in_loop{$moved_to}       = 1;
        }
    }
    
    my $current_id = $fid;
    while (1) {
        my ($moved_from) = VRPipe::File->get_column_values('id', { moved_to => $current_id }, { rows => 1 });
        $moved_from || last;
        $chained_files{$moved_from} = 1;
        $current_id = $moved_from;
    }
{'fraction_non_alphanumeric': 0.14774800056124596, 'fraction_numerical': 0.008278377999158131, 'mean_word_length': 3.560460652591171, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 77, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3665755', 'n_tokens_mistral': 2557, 'n_tokens_neox': 2448, 'n_words': 725}