Changeset 32575


Ignore:
Timestamp:
11/05/18 20:51:24 (2 years 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 edited

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