Changeset 32575 for main/trunk/greenstone2/perllib/gssql.pm

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.

File:

• main/trunk/greenstone2/perllib/gssql.pm (modified) (3 diffs)

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;
+
 }