Changeset 32574

Show
Ignore:
Timestamp:
05.11.2018 19:50:10 (2 weeks ago)
Author:
ak19
Message:

Instead of my our own caching of SQL prepare statements within the gssql object (gssql.pm), there is the handy dbh->prepare_cached() command that encapsulates away the same behaviour for us (if used correctly). Tested this worked. Testing was of regular full rebuilds, incr delete, non-incr delete, reindex upon rename.

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/gssql.pm

    r32573 r32574  
    356356# "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." 
    357357 
     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 
    358363 
    359364# https://www.guru99.com/insert-into.html 
     
    374379    my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_; 
    375380     
    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"); 
    393385 
    394386    # Now we're ready to execute the command, unless we're only debugging 
     
    414406    my $dbh = $self->{'db_handle'}; 
    415407     
    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     
    431411    # Now we're ready to execute the command, unless we're only debugging 
    432412 
     
    444424## The 2 select statements used by GreenstoneSQLPlugin 
    445425 
     426#https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 
    446427# Returns the statement handle that prepared and executed 
    447428# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. 
     
    457438    my $meta_table = $self->get_metadata_table_name(); 
    458439     
    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 = ?}); 
    460441    $sth->execute( $oid ); # will print msg on fail 
    461442     
     
    463444} 
    464445 
     446 
     447#https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 
    465448# Returns the statement handle that prepared and executed 
    466449# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. 
     
    473456    my $fulltxt_table = $self->get_fulltext_table_name(); 
    474457     
    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 = ?}); 
    476459    $sth->execute( $oid ); # will print msg on fail 
    477460     
     
    489472    my $dbh = $self->{'db_handle'}; 
    490473 
    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 = ?}); 
    503476    $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn() 
    504477} 
     
    510483     
    511484    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 = ?}); 
    524488    $sth->execute( $oid ) or warn $dbh->errstr; # dbh set to print errors even without doing warn() 
    525489}