Changeset 32575

Show
Ignore:
Timestamp:
05.11.2018 20:51:24 (2 weeks ago)
Author:
ak19
Message:

1. gssql now does fetching all rows internally upon select. With this the statement and database handles have been hidden away in the gssql.pm class. Hopefully this makes the GreenstoneSQLPlugin and GreenstoneSQLPlugout code easier to read and follow. 2. new method docprint::unescape_textref() takes a textref and returns a ref to the modified text. This method is now used internally by the older docprint::unescape_text() variant of the method. unescape_textref(), like the recently added escape_textref(), should hopefully do what I think it does. Then it can be used to pass large strings, like fulltext in particular, by ref instead of value.

Location:
main/trunk/greenstone2/perllib
Files:
3 modified

Legend:

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

    r32573 r32575  
    129129} 
    130130 
    131 # used by GreenstoneSQLPlugin when reading back from sqldb 
     131# Unescape variants are used by GreenstoneSQLPlugin when reading text back from SQL db 
     132# Pass by ref version 
     133sub unescape_textref { 
     134    my ($textref) = @_; 
     135    # special characters in the xml encoding 
     136    $$textref =~ s/& &/&&/g; 
     137    $$textref =~ s/&/&/g; # this has to be first... 
     138    $$textref =~ s/&lt;/</g; 
     139    $$textref =~ s/&gt;/>/g; 
     140    $$textref =~ s/&quot;/"/g; 
     141 
     142    return $textref; 
     143} 
     144 
     145# Pass by value version 
    132146sub unescape_text { 
    133147    my ($text) = @_; 
    134     # special characters in the xml encoding 
    135     $text =~ s/& &/&&/g; 
    136     $text =~ s/&amp;/&/g; # this has to be first... 
    137     $text =~ s/&lt;/</g; 
    138     $text =~ s/&gt;/>/g; 
    139     $text =~ s/&quot;/"/g; 
    140  
    141     return $text; 
     148    my $textref = &unescape_textref(\$text); 
     149    return $$textref; 
    142150} 
    143151 
  • main/trunk/greenstone2/perllib/gssql.pm

    r32574 r32575  
    424424## The 2 select statements used by GreenstoneSQLPlugin 
    425425 
    426 #https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 
     426# Using fetchall_arrayref on statement handle, to run on prepared and executed stmt 
     427#   https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#fetchall_arrayref 
     428# instead of selectall_arrayref on database handle which will prepare, execute and fetch 
     429#   https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 
     430# 
    427431# Returns the statement handle that prepared and executed 
    428432# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. 
     
    433437sub select_from_metatable_matching_docid { 
    434438    my $self= shift (@_); 
    435     my ($oid) = @_; 
    436      
    437     my $dbh = $self->{'db_handle'}; 
    438     my $meta_table = $self->get_metadata_table_name(); 
    439      
    440     my $sth = $dbh->prepare_cached(qq{SELECT * FROM $meta_table WHERE did = ?}); 
     439    my ($oid, $outhandle) = @_; 
     440     
     441    my $dbh = $self->{'db_handle'}; 
     442    my $tablename = $self->get_metadata_table_name(); 
     443     
     444    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $tablename WHERE did = ?}); 
    441445    $sth->execute( $oid ); # will print msg on fail 
    442      
    443     return $sth; # caller can call fetchrow_array() on returned statement handle, sth 
    444 } 
    445  
    446  
    447 #https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref 
     446 
     447    print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n" 
     448    if ($self->{'verbosity'} > 2); 
     449     
     450    my $rows_ref = $sth->fetchall_arrayref(); 
     451    # "If an error occurs, fetchall_arrayref returns the data fetched thus far, which may be none. 
     452    # You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the 
     453    # data is complete or was truncated due to an error." 
     454    # https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#fetchall_arrayref 
     455    # https://www.oreilly.com/library/view/programming-the-perl/1565926994/ch04s05.html 
     456    warn("Data fetching from $tablename terminated early by error: " . $dbh->err) if $dbh->err; 
     457    return $rows_ref; 
     458} 
     459 
     460 
     461# See select_from_metatable_matching_docid() above. 
    448462# Returns the statement handle that prepared and executed 
    449463# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. 
     
    451465sub select_from_texttable_matching_docid { 
    452466    my $self= shift (@_); 
    453     my ($oid) = @_; 
    454      
    455     my $dbh = $self->{'db_handle'}; 
    456     my $fulltxt_table = $self->get_fulltext_table_name(); 
    457      
    458     my $sth = $dbh->prepare_cached(qq{SELECT * FROM $fulltxt_table WHERE did = ?}); 
     467    my ($oid, $outhandle) = @_; 
     468     
     469    my $dbh = $self->{'db_handle'}; 
     470    my $tablename = $self->get_fulltext_table_name(); 
     471     
     472    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $tablename WHERE did = ?}); 
    459473    $sth->execute( $oid ); # will print msg on fail 
    460474     
    461     return $sth; # caller can call fetchrow_array() on returned statement handle, sth 
     475    print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n" 
     476    if ($self->{'verbosity'} > 2); 
     477     
     478    my $rows_ref = $sth->fetchall_arrayref(); 
     479    # Need explicit warning: 
     480    warn("Data fetching from $tablename terminated early by error: " . $dbh->err) if $dbh->err; 
     481    return $rows_ref; 
     482 
    462483} 
    463484 
  • main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm

    r32571 r32575  
    308308    # read in meta for the collection (i.e. select * from <col>_metadata table 
    309309     
    310     my $sth = $gs_sql->select_from_metatable_matching_docid($oid); 
    311     print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n" 
    312         if $self->{'verbosity'} > 2; 
     310    my $records = $gs_sql->select_from_metatable_matching_docid($oid, $outhandle); 
    313311     
    314312    print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 2; 
    315313    # https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/ 
    316     while( my @row = $sth->fetchrow_array() ) {      
    317         #print $outhandle "row: @row\n"; 
    318         my ($primary_key, $did, $sid, $metaname, $metaval) = @row; 
     314 
     315    foreach my $row (@$records) { 
     316        #print $outhandle "row: @$row\n"; 
     317        my ($primary_key, $did, $sid, $metaname, $metaval) = @$row; 
    319318         
    320319        # get rid of the artificial "root" introduced in section id when saving to sql db 
     
    337336     
    338337     
    339     my $sth = $gs_sql->select_from_texttable_matching_docid($oid); 
    340     print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 2; 
     338    my $records = $gs_sql->select_from_texttable_matching_docid($oid, $outhandle); 
     339     
    341340     
    342341    print $outhandle "----------\nSQL DB contains txt entries for-----------\n" 
    343342        if $self->{'verbosity'} > 2; 
    344     while( my ($primary_key, $did, $sid, $text) = $sth->fetchrow_array() ) {        
     343 
     344    foreach my $row (@$records) { 
     345        my ($primary_key, $did, $sid, $text) = @$row; 
    345346         
    346347        # get rid of the artificial "root" introduced in section id when saving to sql db 
     
    352353        # TODO - pass by ref? 
    353354        # TODO: we accessed the db in utf8 mode, so, we can call doc_obj->add_utf8_text directly: 
    354         $doc_obj->add_utf8_text($sid, &docprint::unescape_text($text)); 
     355        my $textref = &docprint::unescape_textref(\$text); 
     356        $doc_obj->add_utf8_text($sid, $$textref); 
    355357    }    
    356358    print $outhandle "----------FIN READING DOC's TXT FROM SQL DB------------\n"