<commit_before>use strict;
use warnings;

package ImportUtils;

use Exporter;

our @ISA = ('Exporter');

our @EXPORT_OK = qw(dumpSQL debug create_and_load load);

our $TMP_DIR = "/ecs2/scratch5/ensembl/mcvicker/dbSNP";
our $TMP_FILE = 'tabledump.txt';

# successive dumping and loading of tables is typical for this process
# dump does effectively a select into outfile without server file system access
sub dumpSQL {
  my $db  = shift;
  my $sql = shift;
  my @vars = shift; #array containing all the bind variables
  local *FH;

  open FH, ">$TMP_DIR/$TMP_FILE";

  my $sth = $db->prepare( $sql, { mysql_use_result => 1 });
  $sth->execute(@vars);
  my $first;
  while ( my $aref = $sth->fetchrow_arrayref() ) {
    my @a = map {defined($_) ? $_ : '\N'} @$aref;
    print FH join("\t", @a), "\n";
  }

  close FH;

  $sth->finish();
}



# load imports a table, optionally not all columns
# if table doesnt exist, create a varchar(255) for each column
sub load {
  my $db = shift;
  my $tablename = shift;
  my @colnames = @_;

  my $cols = join( ",", @colnames );

  rename("$TMP_DIR/$TMP_FILE", "$TMP_DIR/$tablename.txt");

  my $host = $db->host();
  my $user = $db->user();
  my $pass = $db->pass();
  my $port = $db->port();
  my $dbname = $db->dbname();

  my $call = "/usr/local/bin/mysqlimport -c $cols -h $host -u $user " .
    "-p$pass -P$port $dbname $TMP_DIR/$tablename.txt";

  system($call);

  unlink("$TMP_DIR/$tablename.txt");


##### Alternative way of doing same thing
#  my $sql;

#   if ( @colnames ) {

#     $sql = qq{
#               LOAD DATA LOCAL INFILE '$TMP_DIR/$TMP_FILE'
#               INTO TABLE $tablename( $cols )
#              };
#   } else {
#     $sql = qq{
#               LOAD DATA LOCAL INFILE '$TMP_DIR/$TMP_FILE'
#               INTO TABLE $tablename
#              };
#   }

#   $db->do( $sql );

#   unlink( "$TMP_DIR/$TMP_FILE" );
}


#
# creates a table with specified columns and loads data that was dumped
# to a tmp file into the table.
#
# by default all columns are VARCHAR(255), but an 'i' may be added after the
# column name to make it an INT.  Additionally a '*' means add an index to
# the column.
#
# e.g.  create_and_load('mytable', 'col0', 'col1 *', 'col2 i', 'col3 i*');
#
sub create_and_load {
  my $db = shift;
  my $tablename = shift;
  my @cols = @_;

  my $sql = "CREATE TABLE $tablename ( ";

  my @col_defs;
  my @idx_defs;
  my @col_names;

  foreach my $col (@cols) {<commit_msg>added path to mysqlimport execution and removed variables in sql query<commit_after>use strict;
use warnings;

package ImportUtils;

use Exporter;

our @ISA = ('Exporter');

our @EXPORT_OK = qw(dumpSQL debug create_and_load load);

our $TMP_DIR = "/ecs2/scratch3/dani";
our $TMP_FILE = 'tabledump.txt';

# successive dumping and loading of tables is typical for this process
# dump does effectively a select into outfile without server file system access
sub dumpSQL {
  my $db  = shift;
  my $sql = shift;
  local *FH;

  open FH, ">$TMP_DIR/$TMP_FILE";

  my $sth = $db->prepare( $sql);
  $sth->execute();
  my $first;
  while ( my $aref = $sth->fetchrow_arrayref() ) {
    my @a = map {defined($_) ? $_ : '\N'} @$aref;
    print FH join("\t", @a), "\n";
  }

  close FH;

  $sth->finish();
}



# load imports a table, optionally not all columns
# if table doesnt exist, create a varchar(255) for each column
sub load {
  my $db = shift;
  my $tablename = shift;
  my @colnames = @_;

  my $cols = join( ",", @colnames );

  rename("$TMP_DIR/$TMP_FILE", "$TMP_DIR/$tablename.txt");

  my $host = $db->host();
  my $user = $db->user();
  my $pass = $db->pass();
  my $port = $db->port();
  my $dbname = $db->dbname();

  my $call = "mysqlimport -c $cols -h $host -u $user " .
    "-p$pass -P$port $dbname $TMP_DIR/$tablename.txt";

  system($call);

  unlink("$TMP_DIR/$tablename.txt");


##### Alternative way of doing same thing
#  my $sql;

#   if ( @colnames ) {

#     $sql = qq{
#               LOAD DATA LOCAL INFILE '$TMP_DIR/$TMP_FILE'
#               INTO TABLE $tablename( $cols )
#              };
#   } else {
#     $sql = qq{
#               LOAD DATA LOCAL INFILE '$TMP_DIR/$TMP_FILE'
#               INTO TABLE $tablename
#              };
#   }

#   $db->do( $sql );

#   unlink( "$TMP_DIR/$TMP_FILE" );
}


#
# creates a table with specified columns and loads data that was dumped
# to a tmp file into the table.
#
# by default all columns are VARCHAR(255), but an 'i' may be added after the
# column name to make it an INT.  Additionally a '*' means add an index to
# the column.
#
# e.g.  create_and_load('mytable', 'col0', 'col1 *', 'col2 i', 'col3 i*');
#

sub create_and_load {
  my $db = shift;
  my $tablename = shift;
  my @cols = @_;

  my $sql = "CREATE TABLE $tablename ( ";

  my @col_defs;
  my @idx_defs;
  my @col_names;

  foreach my $col (@cols) {
{'fraction_non_alphanumeric': 0.15798180314309346, 'fraction_numerical': 0.005169561621174525, 'mean_word_length': 2.903954802259887, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 26, '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': '6861697', 'n_tokens_mistral': 1835, 'n_tokens_neox': 1783, 'n_words': 553}