#!/usr/bin/perl

use DBI;
use strict;
use warnings;


# DEFAULT SCRIPT USER
our $db_user_name = 'root';

# DEFAULT PASSWORD FOR SCRIPT USER
our $db_password = '[mysql_root_password_here]';

our $database = "Bibles";
our $table = "KJV"; 
our $dbfilename = 'KJV_fixed.csv';

our $DEBUG=0;

# Treat all input and output as UTF-8 and set the flags correctly
# Because _everyone_ should be using UTF8 these days!
binmode STDOUT, ":utf8";
binmode STDERR, ":utf8";
binmode STDIN,  ":utf8";


#################
#begin
    
    &initializeDB;
    my @source = &readsource($dbfilename);
    &filltable(@source);
    exit;
    
#end
#################

sub initializeDB {

    print "Creating database: $database\n";
    my $dsn = "DBI:mysql:host=localhost";    
    my $statement = qq| CREATE DATABASE IF NOT EXISTS $database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; |;
    &connectdb($statement,$dsn);
    
} #END SUB initializeDB

sub filltable {
my @source = @_;
my ($booknum, $chapternum, $verse, $text, $other) = ('', '', '', '', '');
my $line = '';
my $count = 0;
my $percent = 0;

    my $statement = qq|create table if not exists $table (RecordNum SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT, Book TINYINT, Chapter SMALLINT, Verse SMALLINT, Text text) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;|;                             
    &connectdb($statement);        
            
    print "Filling the table with the data...\n\nThis could take awhile.\n";
    print "Total lines: ".scalar @source."\n";
    
    foreach $line (@source) {
        $count++;

        if ($count/3110 == int($count/3110)) { 
            $percent++;
            print "${percent}0\% completed...\n";
        } 
        
        chomp $line;
        $line =~ s/'/\\'/g;
        $line =~ s/(\d+),(\d+),(\d+),"(.*?)"/$1\t$2\t$3\t$4/g;
            
        ($booknum, $chapternum, $verse, $text, $other) = split(/\t/, $line);
                
        $text =~ s/^\s+//;
        $text =~ s/\s+$//;
        
        $statement = qq|INSERT INTO $table (RecordNum, Book, Chapter, Verse, Text) VALUES (null, '$booknum', '$chapternum', '$verse', '$text'); |; 
        &connectdb($statement);    
    }
} #END SUB filltable

sub readsource {
my $filename=shift @_;
my @data = ();
    print "\nReading source: $filename...";

    open SOURCE, "<$filename" or die "Cannot open source file $!\n";
    @data = <SOURCE>;
    close SOURCE;
        
    print "Done.\n\n";
    
    return @data;
} #END SUB readsource        


sub connectdb { if ($DEBUG==9) {print "sub connectdb: \n"}; 
my $statement = shift @_;
my $dsn = shift @_ || "DBI:mysql:$database:localhost";    
    
    my $dbh = DBI->connect($dsn, $db_user_name, $db_password, {
                         mysql_enable_utf8 => 1
                    }) or die "Can't connect to the DB: $DBI::errstr\n";  
    my $quest = $dbh->prepare($statement, { RaiseError => 1 }) or die "Cannot prepare statement! $DBI::errstr\n";
    if ($DEBUG>0) {
      $quest->execute() or die qq|CONNECT DATABASE Statement:  \n $statement \n
        \n Error in first database statement! \n $DBI::errstr \n |;
    } else { $quest->execute() };
      
} # END SUB connectdb

##</code><code>##

#!/usr/bin/perl

use DBI;
use strict;
use warnings;


# DEFAULT SCRIPT USER
our $db_user_name = 'root';

# DEFAULT PASSWORD FOR SCRIPT USER
our $db_password = '[mysql_root_password_here]';

our $database = "Bibles";
our $table = "KJV"; 

our $DEBUG=0;

# Treat all input and output as UTF-8 and set the flags correctly
# Because _everyone_ should be using UTF8 these days!
binmode STDOUT, ":utf8";
binmode STDERR, ":utf8";
binmode STDIN,  ":utf8";


#################
#begin
    
    my @results = &read_table_from_DB;
    &processResults(@results);
    exit;
    
#end
#################

sub processResults {
my @data = @_;
my ($record, $booknum, $chapternum, $versenum, $text) = ('', '', '', '', '');

#OPTIONAL FORM OF ITERATION: 
#  JUST KEEP IN MIND THAT ARE FIVE (5)
#  RECORDS FOR EACH "ROW" 

    while (@data) {
        $record = shift @data;
        $booknum = shift @data;
        $chapternum = shift @data;
        $versenum = shift @data;
        $text = shift @data;
        
        #CODE TO ANALYZE AND COMPARE AMONG TEXTS
        #print $record."\n"; #WILL GENERATE CONSIDERABLE TEXT TO SCREEN        
    
    }


} #END SUB processResults

sub read_table_from_DB {
my @results = ();
my ($record, $booknum, $chapternum, $verse, $text, $other) = ('', '', '', '', '', '');
my $line = '';
my $count = 0;

    my $statement = qq|SELECT * FROM $table;|;                             
    @results = &connectdb($statement);        

} #END SUB read_table_from_DB


sub connectdb { if ($DEBUG==9) {print "sub connectdb: \n"}; 
my $statement = shift @_;
my $dsn = shift @_ || "DBI:mysql:$database:localhost";    
my @results = ();
   
    my $dbh = DBI->connect($dsn, $db_user_name, $db_password, {
                         mysql_enable_utf8 => 1
                    }) or die "Can't connect to the DB: $DBI::errstr\n";  
    my $quest = $dbh->prepare($statement, { RaiseError => 1 }) or die "Cannot prepare statement! $DBI::errstr\n";
    if ($DEBUG>0) {
      $quest->execute() or die qq|CONNECT DATABASE Statement:  \n $statement \n
        \n Error in first database statement! \n $DBI::errstr \n |;
    } else { $quest->execute() };
    
    
    while(my @row = $quest->fetchrow_array()) {
        foreach my $item (@row) {
            push @results, $item;
        }
    }

    return @results;
     
} # END SUB connectdb