Changeset 32573 for main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm

Timestamp:

2018-11-05T19:24:47+13:00 (5 years ago)

Author:

ak19

Message:

1. Caching the prepared SQL insert statements inside the gssql class rather than in the GS SQL Plugout. 2. docprint.pm provides an escape_textref function besides the existing escape_text function, to deal with large text (e.g. fulltxt). The old escape_text function has been rewritten to internally use the escape_textref function.

File:

• main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm (modified) (6 diffs)

main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm

@@ -177 +177 @@
     $success = $gs_sql->ensure_fulltxt_table_exists();
     }
-    #if(!$gs_sql->load_db_and_tables($db_name, $proc_mode)) {
+    
     if(!$success) { 
     # This is fatal for the plugout, let's terminate here after disconnecting again
@@ -186 +186 @@
     }
 
-    # prepare the shared/common HANDLES to SQL insert statements that contain placeholders
-    # and which we will reuse repeatedly when actually executing the insert statements
-    
-    if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
-    $self->{'metadata_prepared_insert_statement_handle'} = $gs_sql->prepare_insert_metadata_row_stmthandle();
-    }
-    if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
-    $self->{'fulltxt_prepared_insert_statement_handle'} = $gs_sql->prepare_insert_fulltxt_row_stmthandle();
-    }
-
     # store the DBI wrapper instance
     $self->{'gs_sql'} = $gs_sql;
     
-    ##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:
@@ -300 +288 @@
     my $root_section = $doc_obj->get_top_section();
 
-    # load the prepared INSERT statement handles for both tables (can be undef for any table depending on whether meta_only or txt_only are set)
-    my $metadata_table_sth = $self->{'metadata_prepared_insert_statement_handle'};
-    my $fulltxt_table_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
-    
-    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section, $metadata_table_sth, $fulltxt_table_sth);
+    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section);
 }
 
@@ -311 +295 @@
 sub recursive_write_meta_and_text {
     my $self = shift (@_);
-    my ($doc_obj, $doc_oid, $section, $metadata_table_sth, $fulltxt_table_sth) = @_;    
+    my ($doc_obj, $doc_oid, $section) = @_;    
 
     # If section=ROOT, write "root" as section name into table
@@ -321 +305 @@
 
     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'};
-    #if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
-    if($metadata_table_sth) { # meta insert statement handle will be undef if not writing meta
+
+    my $gs_sql = $self->{'gs_sql'};
+    my $proc_mode = $self->{'process_mode'};
+    if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
     
     foreach my $data (@{$section_ptr->{'metadata'}}) {
@@ -336 +318 @@
         # Write out the current section's meta to collection db's METADATA table        
         
-        # for each set of values to write to meta table, execute the prepared statement, filling in the values
-
-        if($self->{'debug'}) {
-        # 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";
+        # For each set of values to write to meta table, this next method call will
+        # efficiently execute an insert SQL statement (using a prepared insert statement), 
+        # filling in the values
+        # OR if debugging, then it will print the SQL insert statement but not execute it
+
+        $gs_sql->insert_row_into_metadata_table($doc_oid, $section_name, $meta_name, $escaped_meta_value, $self->{'debug'}, $debug_out);        
+
         }
-        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");
-        # Execution failure will print out info anyway: since db connection sets PrintError
-        }
-    }
-    }
-    
-    #if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
-    if($fulltxt_table_sth) { # fulltxt insert statement handle will be undef if not writing fulltxt
-
-    if($self->{'debug'}) {
-        # 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";
-    } else {
-        my $section_text = &docprint::escape_text($section_ptr->{'text'});
-        
-        # fulltxt column can be SQL NULL. undef value gets written out as NULL:
-        # https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string
-        
-        # 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");
-        # Execution failure will print out info anyway: since db connection sets PrintError
-    }
+    }
+    
+    
+    if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
+    
+    my $section_textref = &docprint::escape_textref(\$section_ptr->{'text'});
+    
+    # fulltxt column can be SQL NULL. undef value gets written out as NULL:
+    # https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string
+    # The following will do the SQL insertion
+    # or if debug, the following will print the SQL insert stmt without executing it
+    $gs_sql->insert_row_into_fulltxt_table($doc_oid, $section_name, $section_textref, $self->{'debug'}, $debug_out);
+    
     }
     
     # output all subsections: RECURSIVE CALL
     foreach my $subsection (@{$section_ptr->{'subsection_order'}}) {
-    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection", $metadata_table_sth, $fulltxt_table_sth);
+    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection");
     }
 }