Index: main/trunk/greenstone2/perllib/docprint.pm
===================================================================
--- main/trunk/greenstone2/perllib/docprint.pm	(revision 32514)
+++ main/trunk/greenstone2/perllib/docprint.pm	(revision 32518)
@@ -67,4 +67,6 @@
     $all_text .= "  <Description>\n";
 
+    # scalar comparisons on a constant is allowed (but not string evaluation of scalars)
+    # https://www.perlmonks.org/?node_id=559456
     if($options->{'output'} == OUTPUT_ALL || $options->{'output'} == OUTPUT_META_ONLY) {
 	# output metadata
Index: main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm
===================================================================
--- main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm	(revision 32518)
+++ main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm	(revision 32518)
@@ -0,0 +1,478 @@
+###########################################################################
+#
+# 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;
Index: main/trunk/greenstone2/perllib/strings.properties
===================================================================
--- main/trunk/greenstone2/perllib/strings.properties	(revision 32514)
+++ main/trunk/greenstone2/perllib/strings.properties	(revision 32518)
@@ -417,4 +417,7 @@
 
 export.saveas.GreenstoneXML:Greenstone XML Archive format
+export.saveas.MySQL:Save metadata and text in MySQL database
+export.saveas.MySQL.meta_only:Store metadata in MySQL database (and full text in Greenstone XML Archive format)
+export.saveas.MySQL.text_only:Store full text in MySQL database (and metadata in Greenstone XML Archive format)
 
 export.saveas.MARCXML:MARC XML format (an XML version of MARC 21)
@@ -1440,4 +1443,11 @@
 METSPlugout.xslt_mets:Transform a mets's docmets.xml with the XSLT in the named file.  
 
+MySQLPlugout.desc:Output metadata and/or full text to a MySQL database instead of doc.xml.
+MySQLPlugout.process_mode:Setting determines whether full text and/or metadata will be output to a MySQL database instead of to doc.xml during import.
+MySQLPlugout.process_mode.all:Import stage outputs the full text and metadata to a MySQL database instead of to doc.xml.
+MySQLPlugout.process_mode.meta_only:Import stage outputs the metadata to a MySQL database and any text to doc.xml.
+MySQLPlugout.process_mode.text_only:Import stage outputs the full text to a MySQL database and any metadata to doc.xml.
+
+
 #
 # Perl module strings
