Changeset 32575

Timestamp:

2018-11-05T20:51:24+13:00 (5 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:

• docprint.pm (modified) (1 diff)
• gssql.pm (modified) (3 diffs)
• plugins/GreenstoneSQLPlugin.pm (modified) (3 diffs)

main/trunk/greenstone2/perllib/docprint.pm

@@ -129 +129 @@
 }
 
-# used by GreenstoneSQLPlugin when reading back from sqldb
+# Unescape variants are used by GreenstoneSQLPlugin when reading text back from SQL db
+# Pass by ref version
+sub unescape_textref {
+    my ($textref) = @_;
+    # special characters in the xml encoding
+    $$textref =~ s/& &/&&/g;
+    $$textref =~ s/&/&/g; # this has to be first...
+    $$textref =~ s/&lt;/</g;
+    $$textref =~ s/&gt;/>/g;
+    $$textref =~ s/&quot;/"/g;
+
+    return $textref;
+}
+
+# Pass by value version
 sub unescape_text {
     my ($text) = @_;
-    # special characters in the xml encoding
-    $text =~ s/& &/&&/g;
-    $text =~ s/&amp;/&/g; # this has to be first...
-    $text =~ s/&lt;/</g;
-    $text =~ s/&gt;/>/g;
-    $text =~ s/&quot;/"/g;
-
-    return $text;
+    my $textref = &unescape_textref(\$text);
+    return $$textref;
 }
 

main/trunk/greenstone2/perllib/gssql.pm

@@ -424 +424 @@
 ## The 2 select statements used by GreenstoneSQLPlugin
 
-#https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref
+# Using fetchall_arrayref on statement handle, to run on prepared and executed stmt
+#   https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#fetchall_arrayref
+# instead of selectall_arrayref on database handle which will prepare, execute and fetch
+#   https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref
+#
 # Returns the statement handle that prepared and executed
 # a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
@@ -433 +437 @@
 sub select_from_metatable_matching_docid {
     my $self= shift (@_);
-    my ($oid) = @_;
-    
-    my $dbh = $self->{'db_handle'};
-    my $meta_table = $self->get_metadata_table_name();
-    
-    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $meta_table WHERE did = ?});
+    my ($oid, $outhandle) = @_;
+    
+    my $dbh = $self->{'db_handle'};
+    my $tablename = $self->get_metadata_table_name();
+    
+    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $tablename WHERE did = ?});
     $sth->execute( $oid ); # will print msg on fail
-    
-    return $sth; # caller can call fetchrow_array() on returned statement handle, sth
-}
-
-
-#https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#selectall_arrayref
+
+    print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n"
+    if ($self->{'verbosity'} > 2);
+    
+    my $rows_ref = $sth->fetchall_arrayref();
+    # "If an error occurs, fetchall_arrayref returns the data fetched thus far, which may be none.
+    # You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the
+    # data is complete or was truncated due to an error."
+    # https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#fetchall_arrayref
+    # https://www.oreilly.com/library/view/programming-the-perl/1565926994/ch04s05.html
+    warn("Data fetching from $tablename terminated early by error: " . $dbh->err) if $dbh->err;
+    return $rows_ref;
+}
+
+
+# See select_from_metatable_matching_docid() above.
 # Returns the statement handle that prepared and executed
 # a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
@@ -451 +465 @@
 sub select_from_texttable_matching_docid {
     my $self= shift (@_);
-    my ($oid) = @_;
-    
-    my $dbh = $self->{'db_handle'};
-    my $fulltxt_table = $self->get_fulltext_table_name();
-    
-    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $fulltxt_table WHERE did = ?});
+    my ($oid, $outhandle) = @_;
+    
+    my $dbh = $self->{'db_handle'};
+    my $tablename = $self->get_fulltext_table_name();
+    
+    my $sth = $dbh->prepare_cached(qq{SELECT * FROM $tablename WHERE did = ?});
     $sth->execute( $oid ); # will print msg on fail
     
-    return $sth; # caller can call fetchrow_array() on returned statement handle, sth
+    print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n"
+    if ($self->{'verbosity'} > 2);
+    
+    my $rows_ref = $sth->fetchall_arrayref();
+    # Need explicit warning:
+    warn("Data fetching from $tablename terminated early by error: " . $dbh->err) if $dbh->err;
+    return $rows_ref;
+
 }
 

main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm

@@ -308 +308 @@
     # read in meta for the collection (i.e. select * from <col>_metadata table
     
-    my $sth = $gs_sql->select_from_metatable_matching_docid($oid);
-    print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n"
-        if $self->{'verbosity'} > 2;
+    my $records = $gs_sql->select_from_metatable_matching_docid($oid, $outhandle);
     
     print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 2;
     # https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/
-    while( my @row = $sth->fetchrow_array() ) {     
-        #print $outhandle "row: @row\n";
-        my ($primary_key, $did, $sid, $metaname, $metaval) = @row;
+
+    foreach my $row (@$records) {
+        #print $outhandle "row: @$row\n";
+        my ($primary_key, $did, $sid, $metaname, $metaval) = @$row;
         
         # get rid of the artificial "root" introduced in section id when saving to sql db
@@ -337 +336 @@
     
     
-    my $sth = $gs_sql->select_from_texttable_matching_docid($oid);
-    print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 2;
+    my $records = $gs_sql->select_from_texttable_matching_docid($oid, $outhandle);
+    
     
     print $outhandle "----------\nSQL DB contains txt entries for-----------\n"
         if $self->{'verbosity'} > 2;
-    while( my ($primary_key, $did, $sid, $text) = $sth->fetchrow_array() ) {       
+
+    foreach my $row (@$records) {
+        my ($primary_key, $did, $sid, $text) = @$row;
         
         # get rid of the artificial "root" introduced in section id when saving to sql db
@@ -352 +353 @@
         # TODO - pass by ref?
         # TODO: we accessed the db in utf8 mode, so, we can call doc_obj->add_utf8_text directly:
-        $doc_obj->add_utf8_text($sid, &docprint::unescape_text($text));
+        my $textref = &docprint::unescape_textref(\$text);
+        $doc_obj->add_utf8_text($sid, $$textref);
     }   
     print $outhandle "----------FIN READING DOC's TXT FROM SQL DB------------\n"