Changeset 32573 for main/trunk/greenstone2/perllib/gssql.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/gssql.pm (modified) (2 diffs)

main/trunk/greenstone2/perllib/gssql.pm

@@ -270 +270 @@
     # NO: "When all the data has been fetched from a SELECT statement, the driver will automatically call finish for you. So you should not call it explicitly except when you know that you've not fetched all the data from a statement handle and the handle won't be destroyed soon."
     
-    #$meta_sth = $self->{'metadata_prepared_insert_statement_handle'};
-    #$txt_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
-    #$meta_sth->finish() if($meta_sth);
-    #$txt_sth->finish() if($txt_sth);
-
     print STDERR "Disconnecting from database\n" if($self->{'verbosity'} > 1);
     
@@ -372 +367 @@
 # https://metacpan.org/pod/DBI#performance
 # 'The q{...} style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote like qq{...} operator if you want to interpolate variables into the string. See "Quote and Quote-like Operators" in perlop for more details.'
-sub prepare_insert_metadata_row_stmthandle {
-    my $self = shift (@_);    
+#
+# This method uses lazy loading to prepare the SQL insert stmt once for a table and store it,
+# then execute the (stored) statement each time it's needed for that table.
+sub insert_row_into_metadata_table {
+    my $self = shift (@_);
+    my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_;
+    
     #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');"; # ?, ?, ?, ?
-
-    # 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");
-
-    print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
-    
-    return $sth;
-}
-
-sub prepare_insert_fulltxt_row_stmthandle {
+
+    my $sth = $self->{"sth_insert_into_meta_table"};
+    if(!$sth) {
+    my $tablename = $self->get_metadata_table_name();
+    
+    #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
+    
+    # using qq{} since we want $tablename placeholder to be filled in
+    # returns Statement Handle object!
+    $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" if($self->{'verbosity'} > 2);
+    
+    $self->{"sth_insert_into_meta_table"} = $sth; # store it for future use
+    }
+
+    # Now we're ready to execute the command, unless we're only debugging
+    
+    if($debug_only) {
+    # just print the statement we were going to execute
+    
+    print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n";
+    } else {
+    $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
+    }
+}
+
+# As above. Likewise uses lazy loading to prepare the SQL insert stmt once for a table and store it,
+# then execute the (stored) statement each time it's needed for that table.
+sub insert_row_into_fulltxt_table {
     my $self = shift (@_);
     #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');"; ?, ?, ?
-
-    # 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");
-    
-    print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
-    
-    return $sth;
+    my ($doc_oid, $section_name, $section_textref, $debug_only, $debug_out) = @_;
+    
+    my $dbh = $self->{'db_handle'};
+    
+    my $sth = $self->{"sth_insert_into_fulltxt_table"};
+    if(!$sth) {
+    my $tablename = $self->get_fulltext_table_name();
+
+    #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
+    
+    # using qq{} since we want $tablename placeholder to be filled in
+    # returns Statement Handle object!
+    $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" if($self->{'verbosity'} > 2);
+
+    $self->{"sth_insert_into_fulltxt_table"} = $sth; # store it for future use
+    }
+
+    # Now we're ready to execute the command, unless we're only debugging
+
+    if($debug_only) {
+    # just print the statement we were going to execute, minus the fulltxt value
+    my $txt_repr = $$section_textref ? "<TXT>" : "NULL";
+    print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n";
+    } else {
+    $sth->execute($doc_oid, $section_name, $$section_textref)
+        || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name");
+    }
 }