<commit_before>=pod

=head1 LICENSE

    Copyright [2009-2014] EMBL-European Bioinformatics Institute

    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

=head1 NAME

    Bio::RNAcentral::SqlldrImportReferences

=head1 DESCRIPTION

    Import literature references into the RNAcentral Oracle database.
    Since some fields (e.g. authors) are longer than 4000 characters, they are stored as clobs.
    Parallel load option is not allowed when loading lob columns (SQL*Loader-971),
    so the data are loaded sequentially.

=cut

package Bio::RNAcentral::SqlldrImportReferences;

use strict;
use warnings;

use File::Spec;

use base ('Bio::RNAcentral::SqlldrImportBase');


sub new {
    my ($class, $opt, $path, $prefix) = @_;

    # run parent constructor
    my $self = $class->SUPER::new($opt, $path, $prefix);

    return $self;
}


=head2 load_all_references

    Main subroutine for loading the references.

=cut

sub load_all {
    my $self = shift;

    $self->{'logger'}->info("Loading references");

    # create one sqlldr control file
    $self->_make_ctl_file();

    # concatenate all csv files
    my $cmd = "cat $self->{'local'}{'path'}/*.csv > $self->{'local'}{'allfile'}";
    $self->{'logger'}->info("Creating a single datafile with references: $cmd");
    system($cmd);

    # clean up old files if present
    $self->_delete_old_log_files();

    # prepare sqlldr command
    $cmd = $self->_get_sqlldr_command($self->{'local'}{'allfile'});

    # run sqlldr
    $self->{'logger'}->info("Importing $self->{'local'}{'allfile'}");
    my $problems = $self->_run_sqlldr($cmd);

    # clean up if no errors and no problems in sqlldr
    unless ( $self->_errors_found() or $problems ) {
        unlink $self->{'local'}{'allfile'}, $self->{'local'}{'logfile'};
    }
}


=head2 _make_ctl_file

    Create a control file used by sqlldr.

=cut

sub _make_ctl_file {
    my $self = shift;

    open my $fh, '>', $self->{'local'}{'ctlfile'} or die $!;

    print $fh <<CTL;
LOAD DATA
INFILE "str '\\n'"
APPEND
INTO TABLE $self->{'opt'}{'references_table'}
FIELDS TERMINATED BY ',' enclosed by '"'
(
    MD5 char,
    ACCESSION char,
    AUTHORS char(1000000),
    LOCATION char(4000),
    TITLE char(4000),
    PMID char,
    DOI char
)
CTL
    close $fh;
}


=head2 update

    Import the data and perform pl/sql update.

=cut

sub update {
    my $self = shift;

    $self->db_oracle_connect();
    $self->truncate_table($self->{'opt'}{'references_table'});
    $self->load_all();
    $self->update_literature_references();
    $self->db_oracle_disconnect();
}

1;
<commit_msg>Performance improvements for reference loading<commit_after>=pod

=head1 LICENSE

    Copyright [2009-2014] EMBL-European Bioinformatics Institute

    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

=head1 NAME

    Bio::RNAcentral::SqlldrImportReferences

=head1 DESCRIPTION

    Import literature references into the RNAcentral Oracle database.
    Since some fields (e.g. authors) are longer than 4000 characters, they are stored as clobs.
    Parallel load option is not allowed when loading lob columns (SQL*Loader-971),
    so the data are loaded sequentially.

=cut

package Bio::RNAcentral::SqlldrImportReferences;

use strict;
use warnings;

use File::Spec;

use base ('Bio::RNAcentral::SqlldrImportBase');


sub new {
    my ($class, $opt, $path, $prefix) = @_;

    # run parent constructor
    my $self = $class->SUPER::new($opt, $path, $prefix);

    return $self;
}


=head2 load_staging_table

    Load the data into the staging table.

=cut

sub load_staging_table {
    my $self = shift;

    $self->{'logger'}->info("Loading references");

    # create one sqlldr control file
    $self->_make_ctl_file();

    # clean up old files if present
    $self->_delete_old_log_files();

    # prepare sqlldr command
    my $cmd = $self->_get_sqlldr_command_list_files();

    # run sqlldr
    my $problems = $self->_run_sqlldr($cmd);

    # clean up if no errors and no problems in sqlldr
    unless ( $self->_errors_found() or $problems ) {
        unlink $self->{'local'}{'logfile'}, $self->{'local'}{'badfile'};
    }
}


=head2 _make_ctl_file

    Create a control file used by sqlldr.

=cut

sub _make_ctl_file {
    my $self = shift;

    open my $fh, '>', $self->{'local'}{'ctlfile'} or die $!;

    print $fh "LOAD DATA\n";
    $self->_list_input_files($fh, $self->{'local'}{'path'}, 'csv');

    print $fh <<CTL;
INTO TABLE $self->{'opt'}{'references_table'} TRUNCATE
FIELDS TERMINATED BY ',' enclosed by '"'
(
    MD5 char,
    ACCESSION char,
    AUTHORS char(1000000),
    LOCATION char(4000),
    TITLE char(4000),
    PMID char,
    DOI char
)
CTL
    close $fh;
}


=head2 update

    Import the data and perform pl/sql update.

=cut

sub update {
    my $self = shift;

    $self->load_staging_table();
    $self->update_literature_references();
}

1;

{'fraction_non_alphanumeric': 0.12088285229202038, 'fraction_numerical': 0.014261460101867572, 'mean_word_length': 3.473044798785118, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 48, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, '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': '6338332', 'n_tokens_mistral': 1989, 'n_tokens_neox': 1831, 'n_words': 608}