<commit_before>
=head1 NAME

delete_genotypes.pl - delete genotypes from a cxgn database

=head1 DESCRIPTION

 perl delete_genotypes.pl -H [host] -D [dbname] -t (for testing) file

 -p protocol_id

If the -t flag is provided, the changes will be rolled back in the database.




=head1 AUTHOR

<NAME> <<EMAIL>>

=cut

use strict;
use Getopt::Std;
use DBI;
use Bio::Chado::Schema;
use CXGN::Phenome::Schema;

our ($opt_H, $opt_D, $opt_t, $opt_p);
getopts('H:D:tp:');

my $file = shift;

print "Password for $opt_H / $opt_D: \n";
my $pw = <>;
chomp($pw);

print STDERR "Connecting to database...\n";
my $dsn = 'dbi:Pg:database='.$opt_D.";host=".$opt_H.";port=5432";

my $dbh = DBI->connect($dsn, "postgres", $pw);

print STDERR "Connecting to DBI schema...\n";
my $bcs_schema = Bio::Chado::Schema->connect($dsn, "postgres", $pw);
my $phenome_schema = CXGN::Phenome::Schema->connect($dsn, "postgres", $pw,  { on_connect_do => ['set search_path to public,phenome;'] });

$dbh->{AutoCommit} = 0;

eval { 
    my $q0 = "SELECT count(*) FROM genotypeprop WHERE genotypeprop_id in (SELECT genotypeprop_id FROM nd_experiment_protocol join nd_experiment_genotype using(nd_experiment_id) JOIN genotypeprop USING(genotype_id) WHERE nd_protocol_id=?)";
    
    my $h0 = $dbh->prepare($q0);
    $h0->execute($opt_p);
    
    my ($count) = $h0->fetchrow_array();
    print "You are about to delete $count genotyping entries. Proceed? Y/n :";
    my $answer = (<>);
    chomp;
    if ($answer !~ m/y|Y/) {
	die "Aborted due to user request.";
    }
    
    print STDERR "Deleteing genotype entries... ";
    my $q1 = "DELETE FROM genotype WHERE genotype_id in (SELECT genotype_id FROM nd_experiment_protocol join nd_experiment_genotype using(nd_experiment_id) JOIN genotype USING(genotype_id) WHERE nd_protocol_id=?)";
    my $h1 = $dbh->prepare($q1);
    $h1->execute($opt_p);
    print STDERR "Done.\n";

    print STDERR "Delete nd_experiment_md_files entries... ";
    my $q8 = "DELETE FROM nd_experiement
    
    print STDERR "Deleting nd_experiment entries... ";
    my $q2 = "DELETE FROM nd_experiment WHERE nd_experiment_id in (SELECT nd_experiment_id FROM nd_experiment_protocol join nd_experiment using(nd_experiment_id) WHERE nd_protocol_id=?)";
    
    my $h2 = $dbh->prepare($q2);
    $h2 ->execute($opt_p);
    print STDERR "Done.\n";
    
    print STDERR "Deleting protocol entry... ";
    my $q3 = "DELETE FROM nd_protocol WHERE nd_protocol_id=?";
    my $h3 = $dbh->prepare($q3);
    $h3->execute($opt_p);
    print STDERR "Done.\n";
};

if ($@) {
    print STDERR "An error occurred $@... Not deleting.\n";
    $dbh->rollback();
}
else {
    print STDERR "Deletion successful. Commit? Y/N : ";
    my $answer = <>;
    if ($answer =~ m/Y|y/) {
	print STDERR "Deleting... ";
	$dbh->commit();
	print STDERR "Done.\n";
    }
}
	
    

<commit_msg>implement genotype deletion script. Should probably be moved to function in CXGN::Genotype object.<commit_after>
=head1 NAME

delete_genotypes.pl - delete genotypes from a cxgn database

=head1 DESCRIPTION

 perl delete_genotypes.pl -H [host] -D [dbname] -t (for testing) file

 -p protocol_id

If the -t flag is provided, the changes will be rolled back in the database.




=head1 AUTHOR

<NAME> <<EMAIL>>

=cut

use strict;
use Getopt::Std;
use DBI;
use Bio::Chado::Schema;
use CXGN::Phenome::Schema;

our ($opt_H, $opt_D, $opt_t, $opt_p);
getopts('H:D:tp:');

my $file = shift;

print "Password for $opt_H / $opt_D: \n";
my $pw = <>;
chomp($pw);

print STDERR "Connecting to database...\n";
my $dsn = 'dbi:Pg:database='.$opt_D.";host=".$opt_H.";port=5432";

my $dbh = DBI->connect($dsn, "postgres", $pw);

print STDERR "Connecting to DBI schema...\n";
my $bcs_schema = Bio::Chado::Schema->connect($dsn, "postgres", $pw);
my $phenome_schema = CXGN::Phenome::Schema->connect($dsn, "postgres", $pw,  { on_connect_do => ['set search_path to public,phenome;'] });

$dbh->{AutoCommit} = 0;

eval { 
    my $q0 = "SELECT count(*) FROM genotypeprop WHERE genotypeprop_id in (SELECT genotypeprop_id FROM nd_experiment_protocol join nd_experiment_genotype using(nd_experiment_id) JOIN genotypeprop USING(genotype_id) WHERE nd_protocol_id=?)";
    
    my $h0 = $dbh->prepare($q0);
    $h0->execute($opt_p);
    
    my ($count) = $h0->fetchrow_array();
    print "You are about to delete $count genotyping entries. Proceed? Y/n :";
    my $answer = (<>);
    chomp;
    if ($answer !~ m/y|Y/) {
	die "Aborted due to user request.";
    }
    
    print STDERR "Deleteing genotype entries... ";
    my $q1 = "DELETE FROM genotype WHERE genotype_id in (SELECT genotype_id FROM nd_experiment_protocol join nd_experiment_genotype using(nd_experiment_id) JOIN genotype USING(genotype_id) WHERE nd_protocol_id=?)";
    my $h1 = $dbh->prepare($q1);
    $h1->execute($opt_p);
    print STDERR "Done.\n";

    print STDERR "Delete nd_experiment_md_files entries... ";
    my $q8 = "DELETE FROM phenome.nd_experiment_md_files WHERE nd_experiment_id in (SELECT nd_experiment_id FROM nd_experiment_protocol join nd_experiment using(nd_experiment_id) WHERE nd_protocol_id=?)";
    my $h8 = $dbh->prepare($q8);
    $h8->execute($opt_p);
    
    print STDERR "Deleting nd_experiment entries... ";
    my $q2 = "DELETE FROM nd_experiment WHERE nd_experiment_id in (SELECT nd_experiment_id FROM nd_experiment_protocol join nd_experiment using(nd_experiment_id) WHERE nd_protocol_id=?)";
    
    my $h2 = $dbh->prepare($q2);
    $h2 ->execute($opt_p);
    print STDERR "Done.\n";
    
    print STDERR "Deleting protocol entry... ";
    my $q3 = "DELETE FROM nd_protocol WHERE nd_protocol_id=?";
    my $h3 = $dbh->prepare($q3);
    $h3->execute($opt_p);
    print STDERR "Done.\n";
};

if ($@) {
    print STDERR "An error occurred $@... Not deleting.\n";
    $dbh->rollback();
}
else {
    print STDERR "Deletion successful. Commit? Y/N : ";
    my $answer = <>;
    if ($answer =~ m/Y|y/) {
	print STDERR "Deleting... ";
	$dbh->commit();
	print STDERR "Done.\n";
    }
}
	
    


{'fraction_non_alphanumeric': 0.15186294078509646, 'fraction_numerical': 0.009148369926813041, 'mean_word_length': 3.845286059629331, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 49, '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': '1036841', 'n_tokens_mistral': 2285, 'n_tokens_neox': 2197, 'n_words': 586}