source: main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm@ 32518

###########################################################################
#
# GreenstoneXMLPlugout.pm -- the plugout module for Greenstone Archives
# A component of the Greenstone digital library software
# from the New Zealand Digital Library Project at the 
# University of Waikato, New Zealand.
#
# Copyright (C) 2006 New Zealand Digital Library Project
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
###########################################################################

package MySQLPlugout;

use strict;
no strict 'refs';
no strict 'subs';

use util;
use FileUtils;
use BasePlugout;
use docprint;

use IPC::Open2;
use POSIX ":sys_wait_h"; # for waitpid, http://perldoc.perl.org/functions/waitpid.html

# this plugout does not output xml to a file, but outputs rows into a mysql table
sub BEGIN {
    @MySQLPlugout::ISA = ('GreenstoneXMLPlugout');
}

# TODO Q: what is "group" in GreenstoneXMLPlugout?

my $process_mode_list =
    [ { 'name' => "meta_only",
        'desc' => "{MySQLPlugout.process_mode.meta_only}" },      
      { 'name' => "text_only",
        'desc' => "{MySQLPlugout.process_mode.text_only}" },
      { 'name' => "all",
        'desc' => "{MySQLPlugout.process_mode.all}" } ];

my $arguments = [ 
       { 'name' => "process_mode", 
     'desc' => "{MySQLPlugout.process_mode}",
     'type' => "enum",
     'list' => $process_mode_list,
     'deft' => "all",
     'reqd' => "no",
     'hiddengli' => "no"} ];

my $options = { 'name'     => "MySQLPlugout",
        'desc'     => "{MySQLPlugout.desc}",
        'abstract' => "no",
        'inherits' => "yes",
        'args'     => $arguments };

sub new {
    my ($class) = shift (@_);
    my ($plugoutlist, $inputargs,$hashArgOptLists) = @_;
    push(@$plugoutlist, $class);

    push(@{$hashArgOptLists->{"ArgList"}},@{$arguments});
    push(@{$hashArgOptLists->{"OptList"}},$options);

    my $self = new GreenstoneXMLPlugout($plugoutlist,$inputargs,$hashArgOptLists);
    
    if ($self->{'info_only'}) { 
        # don't worry about any options etc
        return bless $self, $class;
    }
    print STDERR "***** MySQLPlugout process mode = \"", $self->{'process_mode'}, "\"\n";
    
    return bless $self, $class;
}

# TODO: check arc-inf.db for whether each entry is to be deleted/indexed/reindexed/been indexed
sub saveas {
    my $self = shift (@_);
    my ($doc_obj, $doc_dir) = @_;

    # pre
    my ($docxml_outhandler, $output_file) = $self->GreenstoneXMLPlugout::pre_saveas(@_);

    
    print STDERR "########## COLLECTION: ". $ENV{'GSDLCOLLECTION'}."\n";
    $self->{'collection_name'} = $ENV{'GSDLCOLLECTION'};
    # set up DB and table or connect to DB and access the table here?
    
    my $proc_mode = $self->{'process_mode'};
    my $docxml_output_options = { 'output' => docprint::OUTPUT_NONE };
    if($proc_mode eq "meta_only" ) { # since only meta to go into MySQL db, text will go into docxml
    $docxml_output_options->{'output'} = docprint::OUTPUT_TEXT_ONLY;
    } elsif($proc_mode eq "text_only" ) { # since only full text to go into MySQL db, meta will go into docxml
    $docxml_output_options->{'output'} = docprint::OUTPUT_META_ONLY;
    }

    # now we've prepared to write out whatever is meant to go into docxml
    # and can do actual the steps superclass GreenstoneXMLPlugout carries out to write out docxml
    # So: write out the doc xml file for the current document
    my $section_text = &docprint::get_section_xml($doc_obj, $docxml_output_options);
    print $docxml_outhandler $section_text;    

    # We also write out whatever needs to go into the MySQL database
    $self->write_meta_and_text($doc_obj);

    # post
    $self->GreenstoneXMLPlugout::post_saveas(@_);

    # TODO: close database connection here? Or do something like with groups
    # so we don't open and close over and over during a single build?
}


# write meta and/or text out to DB
sub write_meta_and_text {
    my $self = shift (@_);
    my ($doc_obj) = @_;
    my $root_section = $doc_obj->get_top_section();

    # Do we don't want to open and close a connection per doc?
    # Would we not rather want to open and close per collection rebuild?
    
    #$self->create_db_connection();
    
    $self->recursive_write_meta_and_text($doc_obj, $root_section);

    #$self->close_db_connection();
}
# Perl: Reading or Writing to Another Program
# https://nnc3.com/mags/Perl3/cookbook/ch16_05.htm
sub recursive_write_meta_and_text {
    my ($doc_obj, $section) = @_;
    
    # TODO
    my $db_outhandler = undef;
    binmode($db_outhandler,":utf8");
    # TODO if $self->debug is on
    
    my $section_ptr = $doc_obj->_lookup_section ($section);
    return "" unless defined $section_ptr;

    my $proc_mode = $self->{'process_mode'};

    if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
    foreach my $data (@{$section_ptr->{'metadata'}}) {
        my $escaped_value = &escape_text($data->[1]);
        my $metaval = $data->[0];
        # TODO: write out current section's text to collection db's META table
    }
    }
    if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
    my $section_text = &escape_text($section_ptr->{'text'});
    # TODO: write out current section's text to collection db's TEXT table
    
    }
    
    # output all subsections: RECURSIVE CALL
    foreach my $subsection (@{$section_ptr->{'subsection_order'}}) {
    &recursive_write_meta_and_text($doc_obj, "$section.$subsection");
    }
}

#################################

# Database access related functions
# http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm
# https://www.guru99.com/insert-into.html

# TODO Q: What on cancelling a build: delete table? But what if it was a rebuild and the rebuild is cancelled (not the original build)?
# Do we create a copy of the orig database as backup, then start populating current db, and if cancelled, delete current db and RENAME backup table to current? 
# https://stackoverflow.com/questions/3280006/duplicating-a-mysql-table-indexes-and-data
# BUT what if the table is HUGE? (Think of a collection with millions of docs.) Huge overhead in copying?
# The alternative is we just quit on cancel, but then: cancel could leave the table in a partial committed state, with no way of rolling back.
# Unless they do a full rebuild, which will recreate the tab;e from scratch?
  

# I'm using perl's open2 like in Z3950Download, as opposed to open3 like in WgetDownload
# since I'm assuming each insert statement is atomic: it either does the insertion or fails
# and we (may) get some message back. That means we should hopefully be able to terminate
# as well if we get SIGTERM/SIGKILL: we're not blocking, but are responsive after every INSERT stmt.

# Just runs the command without displaying it
sub run_command_basic
{
    my ($self,$strCMD) = @_; 
    
    
    my $process_instr = $self->{'MYSQL_IN'};

    print $process_instr "$strCMD\n";  
}

sub run_command
{
    my ($self,$strCMD) = @_;    

    print STDERR "Running mysql command: $strCMD\n";
    $self->run_command_basic($strCMD);
    
}

sub response_line_contains {
    my ($self,$expected) = @_;

    my $opening_line = <$out>;
    if ($opening_line =~ m/$expected/i) {
    return 1;
    }
    return 0;
}

sub response_lines_contain {
    my ($self,$expected) = @_;

    my $found_expected = 0;
    while (my $line = <$out>) {
    print STDERR "$line\n";
    if($line =~ m/$expected/){
        #return 1;
        $found_expected = 1; #won't break out of loop: loop will consume all on child out at present
    }   
    }
    return $found_expected;
}

# based on Z3950Download.pm::start_yaz()
# ./PATH/TO/mysql -u root -p
# Returns:
#   - 1 if client already quit after pwd fail (so can't send quit message to stopped client)
#   - 0 if load_db failed (needs to be quit),
#   - 1 if load_db succeeded (mysql client still running)
sub create_db_connection {
    my $self = shift (@_);
    print STDERR "Opening connection to MySQL db\n";

    my $mysql_client = $self->{'client_path'};
    my $client_user = $self->{'client_user'} || "root";
    
    
    my $launch_cmd = "\"./$mysql_client\" -u $clientuser -p";
    my $childpid = open2(*MYSQL_OUT, *MYSQL_IN, $launch_cmd)
    or (print STDERR "Done\n" and die "can't open2 pipe to mysql client: $!");
    
    $self->{'pid'} = $childpid;
    $self->{'MYSQL_OUT'} = *MYSQL_OUT;
    $self->{'MYSQL_IN'} = *MYSQL_IN;
    
    # connect with pwd and load the database for this site
    #my $conn_open = $self->open_connection();  
    #
    #if (!$conn_open) {
    #    print STDERR "Cannot connect to mysql db with $launch_cmd\n"; 
    #    print STDERR "Done\n";  
    #    return 0;
    #}
    
    # connect with pwd
    my $conn_success = $self->send_pwd();    
    if(!$conn_success) {
    return -1; # if pwd failed, then the program already exited by itself
               # (so don't send quit command after process terminated)
    }
    else {
    return $self->load_db();
    }    
    
    return $conn_open; # 1 if client already quit after pwd fail, 0 if load_db failed (needs to be quit), 1 if load_db succeeded (mysql client still running)
}


# Copied from Z3950Download.pm::quit_yaz()
sub close_db_connection {
    my $self = shift (@_);
    
    $self->run_command("quit");
    close($self->{'MYSQL_IN'}); # close the input to yaz. It also flushes quit command to mysql client

    # make sure nothing is being output by mysql client
    # flush the mysql-client process' outputstream, else we'll be stuck in an infinite
    # loop waiting for the process to quit.
    my $output = $self->{'MYSQL_OUT'};
    my $line;
    while (defined ($line = <$output>)) { 
    if($line !~ m/\w/s) { # print anything other than plain whitespace in case it is important
        print STDERR "***### $line";
    }
    }

    close($self->{'MYSQL_OUT'});

    # Is the following necessary? The PerlDoc on open2 (http://perldoc.perl.org/IPC/Open2.html)
    # says that waitpid must be called to "reap the child process", or otherwise it will hang
    # around like a zombie process in the background. Adding it here makes the code work as 
    # before, but it is certainly necessary to call waitpid on wget (see WgetDownload.pm).
    # http://perldoc.perl.org/functions/waitpid.html
    my $kidpid;
    do {
    $kidpid = waitpid($self->{'pid'}, WNOHANG);
    } while $kidpid > 0; # waiting for pid to become -1
}

# should be called only once per site
sub create_database {
    my $self = shift (@_);
    #my $sitename = shift(@_);
    my $sitename = $self->{'site_name'};
    my $cmd = "CREATE DATABASE $sitename;";
    $self->run_command($cmd);
}


sub send_pwd {
    my $self = shift (@_);

    my $client_pwd = $self->{'client_pwd'};
    my $out = $self->{'MYSQL_OUT'};
    
    # if connected, it's prompting for pwd. Write the pwd to the mysql client process:
    $self->run_command_basic($client_pwd);
    
    #my $opening_line = <$out>;
    #if ($opening_line =~ m/Access denied/i) {
    #print STDERR "Password not recognised. Got: $opening_line\n";
    #return 0;
    #}
    
    if($self->response_line_contains("Access denied")) {
    print STDERR "Password not recognised. Got: $opening_line\n";
    return 0;
    }

    return 1;
}


sub load_db {
    my $self = shift (@_);

    # attempt to load the db
    # use the database
    my $db_name = $self->{'site_name'}; # TODO Q: site_name only exists for GS3. What about GS2?
    $self->run_command("use " . $db_name . ";");
    
    my $db_found = 0;
    while (my $line = <$out>) {
    print STDERR "$line\n";
    if($line =~ m/Database changed/){
        # return 1; # TODO Q: consume all output of running command
        $db_found = 1;
    }
    elsif($line =~ m/Unknown database/){
        $db_found = -1;
    }
    }

    if($db_found == 1) { 
    return $db_found; 
    }
    elsif ($db_found == -1) { # a db for the current sitename didn't exist, create it
    $self->create_database();

    # attempt to load the newly created db
    if($self->_load_db()) {
        return 1;
    }
    #my $opening_line = <$out>;
    #if ($opening_line !~ m/Query OK/) {
    if(!$self->response_line_contains("Query OK")) {
        print STDERR "Could not create db\n";
        return 0; # couldn't even create the db
    } else { # success creating db
        # so let's create the metadata and fulltxt tables for the current coll while we're at it
        if($self->create_meta_table()) {        
        return $self->create_fulltxt_table();
        }
    }
    }
    else { # unknown error trying to load db, bail
    return 0;
    }
}


sub create_meta_table {
    my $self = shift (@_);
    my $table_name = $self->{'collection_name'} . "metadata";
    #my $cmd = "CREATE TABLE $table_name (id VARCHAR(255) NOT NULL UNIQUE, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, metaname VARCHAR(127) NOT NULL, metavalue VARCHAR(1023) NOT NULL, PRIMARY KEY(id));";

    # If using an auto incremented primary key:
    my $cmd = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, metaname VARCHAR(127) NOT NULL, metavalue VARCHAR(1023) NOT NULL, PRIMARY KEY(id));";

    $self->run_command($cmd);
    
    if(!$self->response_lines_contain("Query OK")) {
    print STDERR "Could not create metadata table\n";
    return 0;
    } else {
    return 1;
    }
}

sub create_fulltxt_table {
    my $self = shift (@_);
    my $table_name = $self->{'collection_name'} . "fulltxt";
    #my $cmd = "CREATE TABLE $table_name (id VARCHAR(255) NOT NULL UNIQUE, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, fulltxt LONGTEXT, PRIMARY KEY(id));";

    # If using an auto incremented primary key:
    my $cmd = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, fulltxt LONGTEXT, PRIMARY KEY(id));";

    $self->run_command($cmd);
    
    if(!$self->response_lines_contain("Query OK")) {
    print STDERR "Could not create table\n";
    return 0;
    } else {
    return 1;
    }
}

# https://www.guru99.com/insert-into.html
# and https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
#     for inserting multiple rows at once
sub get_cmd_insert_new_row_into_meta_table {
    my $self = shift (@_);
    my ($did, $sid, $metaname, $metavalue) = @_;
    my $tablename = $self->{'colname'}_"metadata";

    my $cmd = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES\n";
    $cmd = "('$did', '$sid', '$metaname', '$metavalue');\n";
    return $cmd;
}

sub get_cmd_insert_new_row_into_txt_table {
    my $self = shift (@_);
    my ($did, $sid, $fulltext) = @_;
    my $tablename = $self->{'colname'}_"fulltxt";

    my $cmd = "INSERT INTO $tablename (did, sid, fulltxt) VALUES\n";
    $cmd = "('$did', '$sid', '$fulltext');\n";
    return $cmd;
}

# TODO: later add edit and delete (and nothing for "been indexed" status?)

################## 

## UNUSED
sub X_open_connection()
{
    # connect with pwd
    my $conn_success = $self->send_pwd();
    if(!$conn_success) {
    return -1; # if pwd failed, then the program already exists by itself (so don't send quit command after process terminated)
    }
    
    if($conn_success) {
    return $self->load_db();
    } else {
    return $conn_sucess;
    }
}

#################

1;