Changeset 32573 for main/trunk/greenstone2
- Timestamp:
- 2018-11-05T19:24:47+13:00 (5 years ago)
- Location:
- main/trunk/greenstone2/perllib
- Files:
-
- 3 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone2/perllib/docprint.pm
r32536 r32573 108 108 } 109 109 110 # pass by ref version: gets a ref to a string and returns ref to the modified string 111 # so use this for large strings (fulltext) 112 sub escape_textref { 113 my ($textref) = @_; 114 # special characters in the xml encoding 115 $$textref =~ s/&&/& &/g; 116 $$textref =~ s/&/&/g; # this has to be first... 117 $$textref =~ s/</</g; 118 $$textref =~ s/>/>/g; 119 $$textref =~ s/\"/"/g; 120 121 return $textref; 122 } 123 124 # pass by value - the behaviour of the escape_text method used so far is to pass copies of strings 110 125 sub escape_text { 111 126 my ($text) = @_; 112 # special characters in the xml encoding 113 $text =~ s/&&/& &/g; 114 $text =~ s/&/&/g; # this has to be first... 115 $text =~ s/</</g; 116 $text =~ s/>/>/g; 117 $text =~ s/\"/"/g; 118 119 return $text; 127 my $textref = &escape_textref(\$text); 128 return $$textref; 120 129 } 121 130 -
main/trunk/greenstone2/perllib/gssql.pm
r32571 r32573 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." 271 271 272 #$meta_sth = $self->{'metadata_prepared_insert_statement_handle'};273 #$txt_sth = $self->{'fulltxt_prepared_insert_statement_handle'};274 #$meta_sth->finish() if($meta_sth);275 #$txt_sth->finish() if($txt_sth);276 277 272 print STDERR "Disconnecting from database\n" if($self->{'verbosity'} > 1); 278 273 … … 372 367 # https://metacpan.org/pod/DBI#performance 373 368 # '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.' 374 sub prepare_insert_metadata_row_stmthandle { 375 my $self = shift (@_); 369 # 370 # This method uses lazy loading to prepare the SQL insert stmt once for a table and store it, 371 # then execute the (stored) statement each time it's needed for that table. 372 sub insert_row_into_metadata_table { 373 my $self = shift (@_); 374 my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_; 375 376 376 #my ($did, $sid, $metaname, $metavalue) = @_; 377 377 my $dbh = $self->{'db_handle'}; 378 379 my $tablename = $self->get_metadata_table_name(); 380 381 #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ? 382 383 # using qq{} since we want $tablename placeholder to be filled in 384 # returns Statement Handle object! 385 my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n"); 386 387 print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2); 388 389 return $sth; 390 } 391 392 sub prepare_insert_fulltxt_row_stmthandle { 378 379 my $sth = $self->{"sth_insert_into_meta_table"}; 380 if(!$sth) { 381 my $tablename = $self->get_metadata_table_name(); 382 383 #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ? 384 385 # using qq{} since we want $tablename placeholder to be filled in 386 # returns Statement Handle object! 387 $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n"); 388 389 print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2); 390 391 $self->{"sth_insert_into_meta_table"} = $sth; # store it for future use 392 } 393 394 # Now we're ready to execute the command, unless we're only debugging 395 396 if($debug_only) { 397 # just print the statement we were going to execute 398 399 print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n"; 400 } else { 401 $sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value) 402 || warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value"); 403 # Execution failure will print out info anyway: since db connection sets PrintError 404 } 405 } 406 407 # As above. Likewise uses lazy loading to prepare the SQL insert stmt once for a table and store it, 408 # then execute the (stored) statement each time it's needed for that table. 409 sub insert_row_into_fulltxt_table { 393 410 my $self = shift (@_); 394 411 #my ($did, $sid, $fulltext) = @_; 395 my $dbh = $self->{'db_handle'}; 396 397 my $tablename = $self->get_fulltext_table_name(); 398 399 #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ? 400 401 # using qq{} since we want $tablename placeholder to be filled in 402 # returns Statement Handle object! 403 my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n"); 404 405 print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2); 406 407 return $sth; 412 my ($doc_oid, $section_name, $section_textref, $debug_only, $debug_out) = @_; 413 414 my $dbh = $self->{'db_handle'}; 415 416 my $sth = $self->{"sth_insert_into_fulltxt_table"}; 417 if(!$sth) { 418 my $tablename = $self->get_fulltext_table_name(); 419 420 #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ? 421 422 # using qq{} since we want $tablename placeholder to be filled in 423 # returns Statement Handle object! 424 $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n"); 425 426 print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2); 427 428 $self->{"sth_insert_into_fulltxt_table"} = $sth; # store it for future use 429 } 430 431 # Now we're ready to execute the command, unless we're only debugging 432 433 if($debug_only) { 434 # just print the statement we were going to execute, minus the fulltxt value 435 my $txt_repr = $$section_textref ? "<TXT>" : "NULL"; 436 print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n"; 437 } else { 438 $sth->execute($doc_oid, $section_name, $$section_textref) 439 || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name"); 440 } 408 441 } 409 442 -
main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm
r32563 r32573 177 177 $success = $gs_sql->ensure_fulltxt_table_exists(); 178 178 } 179 #if(!$gs_sql->load_db_and_tables($db_name, $proc_mode)) {179 180 180 if(!$success) { 181 181 # This is fatal for the plugout, let's terminate here after disconnecting again … … 186 186 } 187 187 188 # prepare the shared/common HANDLES to SQL insert statements that contain placeholders189 # and which we will reuse repeatedly when actually executing the insert statements190 191 if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {192 $self->{'metadata_prepared_insert_statement_handle'} = $gs_sql->prepare_insert_metadata_row_stmthandle();193 }194 if($proc_mode eq "all" || $proc_mode eq "text_only" ) {195 $self->{'fulltxt_prepared_insert_statement_handle'} = $gs_sql->prepare_insert_fulltxt_row_stmthandle();196 }197 198 188 # store the DBI wrapper instance 199 189 $self->{'gs_sql'} = $gs_sql; 200 190 201 ##print STDERR "#### Meta stmt: " . $self->{'metadata_prepared_insert_statement_handle'}->{'Statement'} . "\n";202 ##print STDERR "#### Full stmt: " . $self->{'fulltxt_prepared_insert_statement_handle'}->{'Statement'} . "\n";203 191 204 192 # 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(); 301 289 302 # 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) 303 my $metadata_table_sth = $self->{'metadata_prepared_insert_statement_handle'}; 304 my $fulltxt_table_sth = $self->{'fulltxt_prepared_insert_statement_handle'}; 305 306 $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section, $metadata_table_sth, $fulltxt_table_sth); 290 $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section); 307 291 } 308 292 … … 311 295 sub recursive_write_meta_and_text { 312 296 my $self = shift (@_); 313 my ($doc_obj, $doc_oid, $section , $metadata_table_sth, $fulltxt_table_sth) = @_;297 my ($doc_obj, $doc_oid, $section) = @_; 314 298 315 299 # If section=ROOT, write "root" as section name into table … … 321 305 322 306 my $debug_out = $self->{'debug_outhandle'}; 323 # print STDERR "#### Meta stmt: " . $metadata_table_sth->{'Statement'} . "\n"; 324 # print STDERR "#### Full stmt: " . $fulltxt_table_sth->{'Statement'} . "\n"; 325 326 #my $proc_mode = $self->{'process_mode'}; 327 #if($proc_mode eq "all" || $proc_mode eq "meta_only" ) { 328 if($metadata_table_sth) { # meta insert statement handle will be undef if not writing meta 307 308 my $gs_sql = $self->{'gs_sql'}; 309 my $proc_mode = $self->{'process_mode'}; 310 if($proc_mode eq "all" || $proc_mode eq "meta_only" ) { 329 311 330 312 foreach my $data (@{$section_ptr->{'metadata'}}) { … … 336 318 # Write out the current section's meta to collection db's METADATA table 337 319 338 # for each set of values to write to meta table, execute the prepared statement, filling in the values 339 340 if($self->{'debug'}) { 341 # just print the statement we were going to execute 342 343 print $debug_out $metadata_table_sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n"; 320 # For each set of values to write to meta table, this next method call will 321 # efficiently execute an insert SQL statement (using a prepared insert statement), 322 # filling in the values 323 # OR if debugging, then it will print the SQL insert statement but not execute it 324 325 $gs_sql->insert_row_into_metadata_table($doc_oid, $section_name, $meta_name, $escaped_meta_value, $self->{'debug'}, $debug_out); 326 344 327 } 345 else { 346 347 $metadata_table_sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value) 348 || warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value"); 349 # Execution failure will print out info anyway: since db connection sets PrintError 350 } 351 } 352 } 353 354 #if($proc_mode eq "all" || $proc_mode eq "text_only" ) { 355 if($fulltxt_table_sth) { # fulltxt insert statement handle will be undef if not writing fulltxt 356 357 if($self->{'debug'}) { 358 # just print the statement we were going to execute, minus the fulltxt value 359 my $txt_repr = $section_ptr->{'text'} ? "<TXT>" : "NULL"; 360 print $debug_out $fulltxt_table_sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n"; 361 } else { 362 my $section_text = &docprint::escape_text($section_ptr->{'text'}); 363 364 # fulltxt column can be SQL NULL. undef value gets written out as NULL: 365 # https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string 366 367 # Write out the current section's text to collection db's FULLTeXT table 368 $fulltxt_table_sth->execute($doc_oid, $section_name, $section_text) 369 || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name"); 370 # Execution failure will print out info anyway: since db connection sets PrintError 371 } 328 } 329 330 331 if($proc_mode eq "all" || $proc_mode eq "text_only" ) { 332 333 my $section_textref = &docprint::escape_textref(\$section_ptr->{'text'}); 334 335 # fulltxt column can be SQL NULL. undef value gets written out as NULL: 336 # https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string 337 # The following will do the SQL insertion 338 # or if debug, the following will print the SQL insert stmt without executing it 339 $gs_sql->insert_row_into_fulltxt_table($doc_oid, $section_name, $section_textref, $self->{'debug'}, $debug_out); 340 372 341 } 373 342 374 343 # output all subsections: RECURSIVE CALL 375 344 foreach my $subsection (@{$section_ptr->{'subsection_order'}}) { 376 $self->recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection" , $metadata_table_sth, $fulltxt_table_sth);345 $self->recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection"); 377 346 } 378 347 }
Note:
See TracChangeset
for help on using the changeset viewer.