Changeset 32574


Ignore:
Timestamp:
2018-11-05T19:50:10+13:00 (5 years 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.

File:
1 edited

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}
Note: See TracChangeset for help on using the changeset viewer.