Changeset 32538

Timestamp:

2018-10-24T20:41:01+13:00 (5 years ago)

Author:

ak19

Message:

Previous commit message meant to be: string names of strings shared by GS SQL Plugin and Plugout have been changed in strings.properties to indicate both modules used them. Current commit: Some tidying up the new GreenstoneSQLPlugin and moving the select statements from there into gssql.pm.

Location:

main/trunk/greenstone2/perllib

Files:

• gssql.pm (modified) (4 diffs)
• plugins/GreenstoneSQLPlugin.pm (modified) (6 diffs)

main/trunk/greenstone2/perllib/gssql.pm

@@ -97 +97 @@
 # Database functions that use the perl DBI module (with the DBD driver module for mysql)
 #################
+
+################### BASIC DB OPERATIONS ##################
 
 # THE NEW DB FUNCTIONS
@@ -275 +277 @@
 }
 
+# "IF EXISTS is used to prevent an error from occurring if the database does not exist. ... DROP DATABASE returns the number of tables that were removed. The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation.Jun 20, 2012"
+#MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax
+# https://dev.mysql.com/doc/en/drop-database.html
+sub delete_collection_tables {
+    my $self= shift (@_);
+    my $dbh = $self->{'db_handle'};
+    
+    print STDERR "### Build mode is removeold, so deleting tables for current collection\n";
+    
+    # drop table <tablename>
+    my $table = $self->get_metadata_table_name();
+    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+    $table = $self->get_fulltext_table_name();
+    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+}
+
+# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)!
+# this is just for debugging
+sub _delete_database {
+    my $self= shift (@_);
+    my ($db_name) = @_;
+    my $dbh = $self->{'db_handle'};
+    
+    # "drop database dbname"
+    $dbh->do("drop database $db_name") || return 0;
+
+    return 1;
+}
+
+
+########################### DB STATEMENTS ###########################
 
 # USEFUL: https://metacpan.org/pod/DBI
@@ -326 +359 @@
 }
 
-# "IF EXISTS is used to prevent an error from occurring if the database does not exist. ... DROP DATABASE returns the number of tables that were removed. The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation.Jun 20, 2012"
-#MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax
-# https://dev.mysql.com/doc/en/drop-database.html
-sub delete_collection_tables {
-    my $self= shift (@_);
-    my $dbh = $self->{'db_handle'};
-    
-    print STDERR "### Build mode is removeold, so deleting tables for current collection\n";
-    
-    # drop table <tablename>
-    my $table = $self->get_metadata_table_name();
-    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
-    $table = $self->get_fulltext_table_name();
-    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
-}
-
-# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)!
-# this is just for debugging
-sub _delete_database {
-    my $self= shift (@_);
-    my ($db_name) = @_;
-    my $dbh = $self->{'db_handle'};
-    
-    # "drop database dbname"
-    $dbh->do("drop database $db_name") || return 0;
-
-    return 1;
-}
+
+## The 2 select statements used by GreenstoneSQLPlugin
+
+# Returns the statement handle that prepared and executed
+# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
+# Caller can call fetchrow_array() on returned statement handle, $sth
+# Have to use prepare() and execute() instead of do() since do() does
+# not allow for fetching result set thereafter:
+# do(): "This method  is typically most useful for non-SELECT statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for SELECT statements because it does not return a statement handle (so you can't fetch any data)." https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#do
+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(qq{SELECT * FROM $meta_table WHERE did = ?});
+    $sth->execute( $oid ); # will print msg on fail
+    
+    return $sth; # caller can call fetchrow_array() on returned statement handle, sth
+}
+
+# Returns the statement handle that prepared and executed
+# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
+# Caller can call fetchrow_array() on returned statement handle, $sth
+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(qq{SELECT * FROM $fulltxt_table WHERE did = ?});
+    $sth->execute( $oid ); # will print msg on fail
+    
+    return $sth; # caller can call fetchrow_array() on returned statement handle, sth
+}
+
+
+# Can call this after connection succeeded to get the database handle, dbh,
+# if any specific DB operation (SQL statement, create/delete)
+# needs to be executed that is not already provided as a method of this class.
+sub get_db_handle {
+    my $self= shift (@_);
+    return $self->{'db_handle'};
+}
+
+################ HELPER METHODS ##############
 
 # More basic helper methods
@@ -368 +421 @@
     my $table_name = $self->{'tablename_prefix'} . "_fulltxt";
     return $table_name;
-}
-
-# returns database handle, dbh
-sub get_db_handle {
-    my $self= shift (@_);
-    return $self->{'db_handle'};
 }
 

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

@@ -124 +124 @@
 }
 
-# need to store current section 
-#sub xml_start_tag {
-#    my $self = shift(@_);
-#    $self->SUPER::xml_start_tag(@_);
-#    $self->{'stored_section'} = $self->{'section'};
-#    $self->{'stored_section_level'} = $self->{'section_level'}
-#}
 
 # TODO Q: Why are there 3 passes when we're only indexing at doc and section level (2 passes)?
@@ -145 +138 @@
 
     my $oid = $self->{'doc_oid'}; # we stored current doc's OID during sub read() 
-    print $outhandle "@@@@@@ OID of document (meta|text) to be read in from DB: $oid\n"
+    print $outhandle "==== OID of document (meta|text) to be read in from DB: $oid\n"
     if $self->{'verbosity'} > 1;
 
-#    print STDERR "@@@@ section: |" . $self->{'stored_section'} . "|\n";
-#    print STDERR "@@@@ section_level: " . $self->{'stored_section_level'} . "\n";
-    
-    # for now, we have access to doc_obj (until just before super::close_document() terminates)
-    #$self->{'doc_obj'}->set_OID($oid);
+    
+    # For now, we have access to doc_obj (until just before super::close_document() terminates)
+    
+    $self->{'doc_obj'}->set_OID($oid); # complex method. Is this necessary, since we just want to write meta and txt for the docobj to index?
+    
+    # checking that complicated looking method set_OID() hasn't modified oid
+    if($oid ne $self->{'doc_obj'}->get_OID()) {
+    print STDERR "@@@@ WARNING: OID after setting on doc_obj = " . $self->{'doc_obj'}->get_OID() . " and is not the same as original OID $oid from docsqloid.xml filename\n";
+    }
+
     
     # TODO: This function is called on a per doc.xml file basis
@@ -164 +162 @@
     # read in meta for the collection (i.e. select * from <col>_metadata table
 
-    my $meta_table = $gs_sql->get_metadata_table_name();
-    
+    my $sth = $gs_sql->select_from_metatable_matching_docid($oid);  
+    print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 1;
+
+    print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 1;
     # https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/
-    my $sth = $gs_sql->get_db_handle()->prepare(qq{SELECT * FROM $meta_table WHERE did = ?});
-    print $outhandle "### stmt: ".$sth->{'Statement'}."\n"
-        if $self->{'verbosity'} > 1;
-    $sth->execute( $oid ); # will print msg on fail
-
-    print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 1;
     while( my @row = $sth->fetchrow_array() ) {     
         #print $outhandle "row: @row\n";
@@ -196 +190 @@
     
     
-    my $sth = $gs_sql->get_db_handle()->prepare(qq{SELECT * FROM $fulltxt_table WHERE did = ?});
-    print $outhandle "### stmt: ".$sth->{'Statement'}."\n"
-        if $self->{'verbosity'} > 1;
-    $sth->execute( $oid ); # will print msg on fail
+    my $sth = $gs_sql->select_from_texttable_matching_docid($oid);
+    print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 1;
 
     print $outhandle "----------\nSQL DB contains txt entries for-----------\n"
@@ -226 +218 @@
 
 
-# TODO: only want to work with sql db if buildcol.pl
+# TODO: only want to work with sql db if buildcol.pl. Unfortunately, also runs on import.pl
 # call init() not begin() because there can be multiple plugin passes
 # and init() should be called before all passes:
 # one for doc level and another for section level indexing
+# This way, we can connect to the SQL database once per buildcol run.
 sub init {
     my ($self) = shift (@_);
@@ -290 +283 @@
 }
 
-
+# This method also runs on import.pl if gs_sql has a value. But we just want to run it on buildcol
 # Call deinit() not end() because there can be multiple plugin passes:
 # one for doc level and another for section level indexing
 # and deinit() should be called before all passes
+# This way, we can close the SQL database once per buildcol run.
 sub deinit {
     my ($self) = shift (@_);