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

Timestamp:

2018-10-24T20:41:01+13:00 (6 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.

File:

• main/trunk/greenstone2/perllib/gssql.pm (modified) (4 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'};
 }