Changeset 32574 for main/trunk
- Timestamp:
- 2018-11-05T19:50:10+13:00 (5 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone2/perllib/gssql.pm
r32573 r32574 356 356 # "Many methods have an optional \%attr parameter which can be used to pass information to the driver implementing the method. Except where specifically documented, the \%attr parameter can only be used to pass driver specific hints. In general, you can ignore \%attr parameters or pass it as undef." 357 357 358 # More efficient to use prepare() to prepare an SQL statement once and then execute() it many times 359 # (binding different values to placeholders) than running do() which will prepare each time and 360 # execute each time. Also, do() is not useful with SQL select statements as it doesn't fetch rows. 361 # Can prepare and cache prepared statements or retrieve prepared statements if cached in one step: 362 # https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#prepare_cached 358 363 359 364 # https://www.guru99.com/insert-into.html … … 374 379 my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_; 375 380 376 #my ($did, $sid, $metaname, $metavalue) = @_; 377 my $dbh = $self->{'db_handle'}; 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 } 381 my $dbh = $self->{'db_handle'}; 382 383 my $tablename = $self->get_metadata_table_name(); 384 my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n"); 393 385 394 386 # Now we're ready to execute the command, unless we're only debugging … … 414 406 my $dbh = $self->{'db_handle'}; 415 407 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 408 my $tablename = $self->get_fulltext_table_name(); 409 my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n"); 410 431 411 # Now we're ready to execute the command, unless we're only debugging 432 412 … … 444 424 ## The 2 select statements used by GreenstoneSQLPlugin 445 425 426 #https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 446 427 # Returns the statement handle that prepared and executed 447 428 # a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. … … 457 438 my $meta_table = $self->get_metadata_table_name(); 458 439 459 my $sth = $dbh->prepare (qq{SELECT * FROM $meta_table WHERE did = ?});440 my $sth = $dbh->prepare_cached(qq{SELECT * FROM $meta_table WHERE did = ?}); 460 441 $sth->execute( $oid ); # will print msg on fail 461 442 … … 463 444 } 464 445 446 447 #https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 465 448 # Returns the statement handle that prepared and executed 466 449 # a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. … … 473 456 my $fulltxt_table = $self->get_fulltext_table_name(); 474 457 475 my $sth = $dbh->prepare (qq{SELECT * FROM $fulltxt_table WHERE did = ?});458 my $sth = $dbh->prepare_cached(qq{SELECT * FROM $fulltxt_table WHERE did = ?}); 476 459 $sth->execute( $oid ); # will print msg on fail 477 460 … … 489 472 my $dbh = $self->{'db_handle'}; 490 473 491 #my $meta_table = $self->get_metadata_table_name(); 492 ##my $rows_deleted = 493 #$dbh->do(qq{DELETE FROM $meta_table WHERE did = ?}, undef, $oid) or warn $dbh->errstr; 494 495 # If we do many deletes from this table, 496 # it may be more optimal to maintain a delete statement to repeatedly execute 497 my $sth = $self->{"sth_del_from_meta_table"}; 498 if(!$sth) { 499 my $meta_table = $self->get_metadata_table_name(); 500 $sth = $dbh->prepare(qq{DELETE FROM $meta_table WHERE did = ?}); 501 $self->{"sth_del_from_meta_table"} = $sth; 502 } 474 my $tablename = $self->get_metadata_table_name(); 475 my $sth = $dbh->prepare_cached(qq{DELETE FROM $tablename WHERE did = ?}); 503 476 $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn() 504 477 } … … 510 483 511 484 my $dbh = $self->{'db_handle'}; 512 513 #my $fulltxt_table = $self->get_fulltext_table_name(); 514 #$dbh->do(qq{DELETE FROM $fulltxt_table WHERE did = ?}, undef, $oid) or warn $dbh->errstr; 515 516 # If we do many deletes from this table, 517 # it may be more optimal to maintain a delete statement to repeatedly execute 518 my $sth = $self->{"sth_del_from_fulltxt_table"}; 519 if(!$sth) { 520 my $fulltxt_table = $self->get_fulltext_table_name(); 521 $sth = $dbh->prepare(qq{DELETE FROM $fulltxt_table WHERE did = ?}); 522 $self->{"sth_del_from_fulltxt_table"} = $sth; 523 } 485 486 my $tablename = $self->get_fulltext_table_name(); 487 my $sth = $dbh->prepare_cached(qq{DELETE FROM $tablename WHERE did = ?}); 524 488 $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn() 525 489 }
Note:
See TracChangeset
for help on using the changeset viewer.