Index: /main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm
===================================================================
--- /main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm	(revision 32523)
+++ /main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm	(revision 32524)
@@ -35,6 +35,9 @@
 use docprint;
 
+use DBI; # the central package for this plugout
+
 use IPC::Open2;
 use POSIX ":sys_wait_h"; # for waitpid, http://perldoc.perl.org/functions/waitpid.html
+
 
 # TODO: SIGTERM rollback and disconnect?
@@ -104,11 +107,13 @@
     $self->{'db_client_user'} = "root";
     $self->{'db_client_pwd'} = "6reenstone3";
+    $self->{'build_mode'} = "removeold";
     #$self->{'db_host'} = "127.0.0.1";
     #$self->{'db_encoding'} = "utf8";
-    #TODO: proc_mode is also a saveas option
+    #TODO: proc_mode is also a saveas option    
     
     ############ LOAD NECESSARY OPTIONS ###########
     print STDERR "########## COLLECTION: ". $ENV{'GSDLCOLLECTION'}."\n";
     $self->{'collection_name'} = $ENV{'GSDLCOLLECTION'};
+    print STDERR "***** MySQLPlugout process mode = \"", $self->{'process_mode'}, "\"\n";
     
     if(!$self->connect_to_db()) {
@@ -117,5 +122,9 @@
 	die("Could not connect to db. Can't proceed.\n");
     }
-    if(!$self->load_db_and_tables()) {
+    
+    my $db_name = $self->{'site_name'} || "localsite"; # one database per GS3 site
+    my $build_mode = $self->{'build_mode'} || "removeold";
+    if(!$self->load_db_and_tables($db_name, $build_mode)) {
+	
 	# This is fatal for the plugout, let's terminate here
 	# PrintError would already have displayed the warning message on connection fail	
@@ -133,4 +142,7 @@
     }
 
+    print STDERR "#### Meta stmt: " . $self->{'metadata_prepared_insert_statement_handle'}->{'Statement'} . "\n";
+    print STDERR "#### Full stmt: " . $self->{'fulltxt_prepared_insert_statement_handle'}->{'Statement'} . "\n";
+    
     # if setting up to work with sql db failed, we'd have terminated and wouldn't come up to here:
     # won't bother preparing GreenstoneXMLPlugout by calling superclass' begin()
@@ -157,4 +169,6 @@
     my ($doc_obj, $doc_dir) = @_;
 
+    print STDERR "\n\n@@@ In saveas\n\n";
+    
     my $proc_mode = $self->{'process_mode'};
     
@@ -216,5 +230,5 @@
     my $fulltxt_table_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
     
-    $self->recursive_write_meta_and_text($doc_obj, $root_section, $metadata_table_sth, $fulltxt_table_sth);
+    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section, $metadata_table_sth, $fulltxt_table_sth);
 }
 
@@ -233,4 +247,6 @@
 
     my $debug_out = $self->{'debug_outhandle'};
+    print STDERR "#### Meta stmt: " . $metadata_table_sth->{'Statement'} . "\n";
+    print STDERR "#### Full stmt: " . $fulltxt_table_sth->{'Statement'} . "\n";
     
     #my $proc_mode = $self->{'process_mode'};
@@ -240,5 +256,5 @@
 	foreach my $data (@{$section_ptr->{'metadata'}}) {
 	    my $meta_name = $data->[0];
-	    my $escaped_meta_value = &escape_text($data->[1]);
+	    my $escaped_meta_value = &docprint::escape_text($data->[1]);
 
 	    # Write out the current section's meta to collection db's METADATA table	    
@@ -249,10 +265,9 @@
 		# just print the statement we were going to execute
 
-		print $debug_out $metadata_table_sth->Statement . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n";
+		print $debug_out $metadata_table_sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n";
 	    }
 	    else {
 	    
-		$metadata_table_sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value); 
-		#|| warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value");
+		$metadata_table_sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value) || warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value");
 		# Execution failure will print out info anyway: since db connection sets PrintError
 	    }
@@ -266,7 +281,7 @@
 	    # just print the statement we were going to execute, minus the fulltxt value
 	    my $txt_repr = $section_ptr->{'text'} ? "<TXT>" : "NULL";
-	    print $debug_out $fulltxt_table_sth->Statement . "($doc_oid, $section_name, $txt_repr)\n";
+	    print $debug_out $fulltxt_table_sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n";
 	} else {
-	    my $section_text = &escape_text($section_ptr->{'text'});
+	    my $section_text = &docprint::escape_text($section_ptr->{'text'});
 	    
 	    # fulltxt column can be SQL NULL. undef value gets written out as NULL:
@@ -274,6 +289,5 @@
 		
 	    # Write out the current section's text to collection db's FULLTeXT table
-	    $fulltxt_table_sth->execute($doc_oid, $section_name, $section_text);
-	    #|| warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name");
+	    $fulltxt_table_sth->execute($doc_oid, $section_name, $section_text) || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name");
 	    # Execution failure will print out info anyway: since db connection sets PrintError
 	}
@@ -337,5 +351,5 @@
 			       PrintError => 1, # on by default, but being explicit
 			       RaiseError => 0, # off by default, but being explicit
-			       AutoCommit => 1, # on by default, but being explicit
+			       AutoCommit => 1 # on by default, but being explicit
 			   });
 
@@ -360,5 +374,5 @@
 sub load_db_and_tables {
     my $self= shift (@_);
-    my $db_name = $self->{'site_name'}; # one database per GS3 site
+    my ($db_name, $build_mode) = @_;
     my $dbh = $self->{'db_handle'};
     
@@ -372,4 +386,6 @@
 	$self->create_db($db_name) || return 0;
 
+	print STDERR "@@@ CREATED DATABASE $db_name\n";
+	
 	# once more attempt to use db, now that it exists
 	$dbh->do("use $db_name") || return 0;
@@ -384,12 +400,25 @@
     elsif($success) { # database existed and loaded successfully, but
 	# before proceeding check that the current collection's tables exist
+
+	print STDERR "@@@ DATABASE $db_name EXISTED\n";
 	
-	# attempt to create tables in current db
-	if(!$self->table_exists($self->{'collection_name'} . "metadata")) {
+
+	if($build_mode eq "removeold") {
+	    $self->delete_collection_tables();
+	}
+
+	# use existing tables if any
+	# attempt to create tables in current db	
+	if($build_mode eq "removeold" || !$self->table_exists($self->get_metadata_table_name())) {
 	    $self->create_metadata_table() || return 0;
+	} else {
+	    print STDERR "@@@ Meta table exists\n";
 	}
-	if(!$self->table_exists($self->{'collection_name'} .  "fulltxt")) {
+	if($build_mode eq "removeold" || !$self->table_exists($self->get_fulltext_table_name())) {
 	    $self->create_fulltext_table() || return 0;
+	} else {
+	    print STDERR "@@@ Fulltxt table exists\n";
 	}
+	
     }
     
@@ -425,9 +454,10 @@
 }
 
+
 sub create_metadata_table {
     my $self= shift (@_);
     my $dbh = $self->{'db_handle'};
     
-    my $table_name = $self->{'collection_name'} . "metadata";
+    my $table_name = $self->get_metadata_table_name();
 
     # If using an auto incremented primary key:
@@ -444,5 +474,5 @@
     my $dbh = $self->{'db_handle'};
     
-    my $table_name = $self->{'collection_name'} . "fulltxt";
+    my $table_name = $self->get_fulltext_table_name();
 
     # If using an auto incremented primary key:
@@ -469,8 +499,8 @@
 sub prepare_insert_metadata_row_stmthandle {
     my $self = shift (@_);    
-    my ($did, $sid, $metaname, $metavalue) = @_;
-    my $dbh = $self->{'db_handle'};
-    
-    my $tablename = $self->{'colname'}."_metadata";
+    #my ($did, $sid, $metaname, $metavalue) = @_;
+    my $dbh = $self->{'db_handle'};
+    
+    my $tablename = $self->get_metadata_table_name();
 
     #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
@@ -478,5 +508,7 @@
     # using qq{} since we want $tablename placeholder to be filled in
     # returns Statement Handle object!
-    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)"}) || warn("Could not prepare insert statement for metadata table\n");
+    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
+
+    print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n";
     
     return $sth;
@@ -485,8 +517,8 @@
 sub prepare_insert_fulltxt_row_stmthandle {
     my $self = shift (@_);
-    my ($did, $sid, $fulltext) = @_;
-    my $dbh = $self->{'db_handle'};
-    
-    my $tablename = $self->{'colname'}."_fulltxt";
+    #my ($did, $sid, $fulltext) = @_;
+    my $dbh = $self->{'db_handle'};
+    
+    my $tablename = $self->get_fulltext_table_name();
 
     #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
@@ -494,7 +526,53 @@
     # using qq{} since we want $tablename placeholder to be filled in
     # returns Statement Handle object!
-    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)"}) || warn("Could not prepare insert statement for fulltxt table\n");
+    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
+    
+    print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n";
     
     return $sth;
+}
+
+# "IF EXISTS is used to prevent an error from occurring if the database does not exist. ... DROP DATABASE returns the number of tables that were removed. The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation.Jun 20, 2012"
+#MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax
+# https://dev.mysql.com/doc/en/drop-database.html
+sub delete_collection_tables {
+    my $self= shift (@_);
+    my $dbh = $self->{'db_handle'};
+    
+    print STDERR "### Build mode is removeold, so deleting tables for current collection\n";
+    
+    # drop table <tablename>
+    my $table = $self->get_metadata_table_name();
+    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+    $table = $self->get_fulltext_table_name();
+    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+}
+
+# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)!
+# this is just for debugging
+sub _delete_database {
+    my $self= shift (@_);
+    my ($db_name) = @_;
+    my $dbh = $self->{'db_handle'};
+    
+    # "drop database dbname"
+    $dbh->do("drop database $db_name") || return 0;
+
+    return 1;
+}
+
+# More basic helper methods
+sub get_metadata_table_name {
+    my $self= shift (@_);
+    my $table_name = $self->{'collection_name'} . "_metadata";
+    return $table_name;
+}
+
+# FULLTEXT is a reserved keyword in (My)SQL. https://dev.mysql.com/doc/refman/5.5/en/keywords.html
+# So we can't name a table or any of its columns "fulltext". We use "fulltxt" instead.
+sub get_fulltext_table_name {
+    my $self= shift (@_);
+    my $table_name = $self->{'collection_name'} . "_fulltxt";
+    return $table_name;
 }
 
@@ -505,5 +583,7 @@
 # Couldn't get the first solution at https://www.perlmonks.org/bare/?node_id=500050 to work though
 sub table_exists {
-    my ($dbh,$table_name) = @_;
+    my $self = shift (@_);
+    my $dbh = $self->{'db_handle'};
+    my ($table_name) = @_;
 
     my @table_list = $dbh->tables;
