Changeset 32580

Show
Ignore:
Timestamp:
07.11.2018 18:39:13 (13 days ago)
Author:
ak19
Message:

1. support for port param when connecting to SQL DB. 2. GS SQL Plugout now also uses the verbosity member variable when instantiating the gssql object. 3. Since the DBI object has PrintError? set to 1 on connection (and ShowErrorStatement? set to 1 to for more verbosity), which means an informative message is always printed on error or warning, there's no need for me to right warning statements everywhere when a db statement/call fails. Removed these redundant warnings. 4. Don't want GS XML Plugout's debug outhandle passed to the two gssql::insert methods, as we don't want them to write debug information to the debug handle. That should only be for the XML stuff (whether groups on or not), and the debug outhandle can moreover be set to the XSLT writer, concerning which makes it makes even less sense for gssql to output info and error debug statements into there. gssql now sticks to STDERR for debug information.

Location:
main/trunk/greenstone2/perllib
Files:
3 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/gssql.pm

    r32579 r32580  
    3333use DBI; # the central package for this module used by GreenstoneSQL Plugout and Plugin 
    3434 
     35############################## 
     36 
     37# TODO: add infrastructure for db_port, AutoCommit etc 
     38# For port, see https://stackoverflow.com/questions/2248665/perl-script-to-connect-to-mysql-server-port-3307 
     39 
     40# + TODO: remove unnecessary warn() since PrintError is active 
     41# https://perldoc.perl.org/perlobj.html#Destructors 
     42 
     43# TODO: drop table if exists and create table if exists are available in MySQL. Use those cmds 
     44# instead of always first checking for existence ourselves? 
     45############################## 
     46 
    3547# singleton connection 
    3648my $_dbh_instance = undef; # calls undef() function. See https://perlmaven.com/undef-and-defined-in-perl 
     
    4961# Parameterise (one or more methods may use them): 
    5062# - db_name (which is the GS3 sitename) 
    51  
    52 # TODO: add infrastructure for db_port, AutoCommit etc 
    53 # For port, see https://stackoverflow.com/questions/2248665/perl-script-to-connect-to-mysql-server-port-3307 
    54  
    55 # TODO: remove unnecessary warn() since PrintError is active 
    56 # https://perldoc.perl.org/perlobj.html#Destructors 
    57  
    58 # TODO: drop table if exists and create table if exists are available in MySQL. Use those cmds 
    59 # instead of always first checking for existence ourselves? 
    6063 
    6164sub new 
     
    160163    my $db_driver = $params_map->{'db_driver'} || "mysql"; 
    161164    my $db_user = $params_map->{'db_client_user'} || "root"; 
    162     my $db_pwd = $params_map->{'db_client_pwd'}; # even if undef, we'll see a sensible error message 
    163                                            # when connect fails 
     165    my $db_pwd = $params_map->{'db_client_pwd'}; # even if undef and password was necessary, 
     166                                     # we'll see a sensible error message when connect fails 
    164167    my $db_host = $params_map->{'db_host'} || "127.0.0.1"; 
    165     # localhost doesn't work for us, but 127.0.0.1 works 
    166     # https://metacpan.org/pod/DBD::mysql 
    167     # "The hostname, if not specified or specified as '' or 'localhost', will default to a MySQL server 
    168     # running on the local machine using the default for the UNIX socket. To connect to a MySQL server 
    169     # on the local machine via TCP, you must specify the loopback IP address (127.0.0.1) as the host." 
     168        # localhost doesn't work for us, but 127.0.0.1 works 
     169        # https://metacpan.org/pod/DBD::mysql 
     170        # "The hostname, if not specified or specified as '' or 'localhost', will default to a MySQL server 
     171        # running on the local machine using the default for the UNIX socket. To connect to a MySQL server 
     172        # on the local machine via TCP, you must specify the loopback IP address (127.0.0.1) as the host." 
     173    my $db_port = $params_map->{'db_port'}; # leave as undef if unspecified, 
     174                 # as our tests never used port anyway (must have internally 
     175                 # defaulted to whatever default port is used for MySQL) 
     176 
     177     
    170178    #my $connect_str = "dbi:$db_driver:database=$db_name;host=$db_host"; 
    171     my $connect_str = "dbi:$db_driver:host=$db_host"; # don't provide db - allows checking the db exists later when loading the db 
     179    # But don't provide db now - this allows checking the db exists later when loading the db 
     180    my $connect_str = "dbi:$db_driver:host=$db_host"; 
     181    $connect_str .= ";port=$db_port" if $db_port; 
    172182 
    173183    if($params_map->{'verbosity'}) { 
     
    375385} 
    376386 
     387 
     388sub delete_collection_tables { 
     389    my $self= shift (@_); 
     390    my $dbh = $self->{'db_handle'}; 
     391 
     392    # drop table <tablename> 
     393    my $table = $self->get_metadata_table_name(); 
     394    if($self->table_exists($table)) { 
     395    $dbh->do("drop table $table");# || warn("@@@ Couldn't delete $table"); 
     396    } 
     397    $table = $self->get_fulltext_table_name(); 
     398    if($self->table_exists($table)) { 
     399    $dbh->do("drop table $table");# || warn("@@@ Couldn't delete $table"); 
     400    } 
     401} 
     402 
     403# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)! 
     404# This method is just here for debugging (for testing creating a database when there is none) 
     405# 
    377406# "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" 
    378407# MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax 
    379408# https://dev.mysql.com/doc/en/drop-database.html 
    380 sub delete_collection_tables { 
    381     my $self= shift (@_); 
    382     my $dbh = $self->{'db_handle'}; 
    383      
    384     # drop table <tablename> 
    385     my $table = $self->get_metadata_table_name(); 
    386     if($self->table_exists($table)) { 
    387     $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
    388     } 
    389     $table = $self->get_fulltext_table_name(); 
    390     if($self->table_exists($table)) { 
    391     $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
    392     } 
    393 } 
    394  
    395 # Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)! 
    396 # This method is just here for debugging (for testing creating a database when there is none) 
    397409sub _delete_database { 
    398410    my $self= shift (@_); 
     
    436448sub insert_row_into_metadata_table { 
    437449    my $self = shift (@_); 
    438     my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_; 
     450    my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only) = @_; 
    439451     
    440452    my $dbh = $self->{'db_handle'}; 
    441453     
    442454    my $tablename = $self->get_metadata_table_name(); 
    443     my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n"); 
     455    my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)});# || warn("Could not prepare insert statement for metadata table\n"); 
    444456 
    445457    # Now we're ready to execute the command, unless we're only debugging 
     
    447459    if($debug_only) { 
    448460    # just print the statement we were going to execute 
    449     print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n"; 
     461    print STDERR $sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n"; 
    450462    } 
    451463    else { 
     
    463475    my $self = shift (@_); 
    464476    #my ($did, $sid, $fulltext) = @_; 
    465     my ($doc_oid, $section_name, $section_textref, $debug_only, $debug_out) = @_; 
     477    my ($doc_oid, $section_name, $section_textref, $debug_only) = @_; 
    466478     
    467479    my $dbh = $self->{'db_handle'}; 
    468480     
    469481    my $tablename = $self->get_fulltext_table_name(); 
    470     my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n"); 
     482    my $sth = $dbh->prepare_cached(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)});# || warn("Could not prepare insert statement for fulltxt table\n"); 
    471483     
    472484    # Now we're ready to execute the command, unless we're only debugging 
    473485 
    474     # just print the statement we were going to execute, minus the fulltxt value 
     486    # don't display the fulltxt value as it could be too long 
    475487    my $txt_repr = $$section_textref ? "<TXT>" : "NULL";     
    476     if($debug_only) { 
    477     print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n"; 
     488    if($debug_only) { # only print statement, don't execute it 
     489    print STDERR $sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n"; 
    478490    } 
    479491    else {   
     
    481493     
    482494    $sth->execute($doc_oid, $section_name, $$section_textref) 
    483         || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name"); 
     495        || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name"); # Execution failure will print out info anyway: since db connection sets PrintError 
    484496    } 
    485497} 
  • main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm

    r32578 r32580  
    427427    my $gs_sql = new gssql({ 
    428428    'collection_name' => $ENV{'GSDLCOLLECTION'}, 
    429     'verbosity' => $self->{'verbosity'} 
     429    'verbosity' => $self->{'verbosity'} || 0 
    430430               }); 
    431431 
  • main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm

    r32578 r32580  
    4242# TODO Q: what about verbosity for debugging, instead of current situation of printing out upon debug set at the expense of writing to db 
    4343# TODO Q: introduced site_name param to plugins and plugouts. Did I do it right? And should they have hiddengli = "yes" 
     44# Did I do the pass by ref in docprint's escape and unescape textref functions correctly, and how they're called here? 
     45#   Any more optimisation I can do around this? 
    4446 
    4547# this plugout does not output the metadata and/or fulltxt xml to a file, 
     
    4951} 
    5052 
    51 # NOTTODO: die() statements need to be replaced with premature_termination 
     53# + NOTTODO: die() statements need to be replaced with premature_termination 
    5254# which should ensure the GreenstoneXMLPlugin (group)'s stuff is closed and cleaned up SOMEHOW 
    5355# It's fine: the die() stmts all take place before setting up the super class' begin 
    5456 
    55 # TODO Q: about build_mode: how to detect removeold. Now handled by  
     57# + TODO Q: about build_mode: how to detect removeold. Now handled by  
    5658#   GreenstoneSQLPlugout::remove_all(), which is inherited from a base plugin. 
    57 # TODO: deal with -removeold and everything? Or type out instructions for user 
    58  
    59 # TODO Q: what is "group" in GreenstoneXMLPlugout? 
     59# + TODO: deal with -removeold and everything? Or type out instructions for user 
     60 
     61# + TODO Q: what is "group" in GreenstoneXMLPlugout? 
    6062 
    6163my $process_mode_list = 
     
    127129 
    128130    my $self= shift (@_); 
    129  
     131     
    130132    # The saveas.options 
    131133    #print STDERR "@@@@ PLUGOUT db_pwd: " . $self->{'db_client_pwd'} . "\n"; 
     
    143145    my $db_params = { 
    144146    'collection_name' => $ENV{'GSDLCOLLECTION'}, 
    145     'verbosity' => 1 
     147    'verbosity' => $self->{'verbosity'} || 0 
    146148    }; 
    147149 
     
    226228} 
    227229  
    228 # TODO: check arc-inf.db for whether each entry is to be deleted/indexed/reindexed/been indexed? 
     230# + X TODO: check arc-inf.db for whether each entry is to be deleted/indexed/reindexed/been indexed? 
    229231# That's only for indexing, not for this step which only generates the content in archives dir 
    230232sub saveas { 
     
    315317        # from db (unlike for reading back in from doc.xml) 
    316318        my $escaped_meta_value = &docprint::escape_text($data->[1]); 
    317  
     319         
    318320        # Write out the current section's meta to collection db's METADATA table         
    319321         
     
    322324        # filling in the values 
    323325        # OR if debugging, then it will print the SQL insert statement but not execute it 
    324  
    325         $gs_sql->insert_row_into_metadata_table($doc_oid, $section_name, $meta_name, $escaped_meta_value, $self->{'debug'}, $debug_out);         
    326  
    327         } 
     326         
     327        $gs_sql->insert_row_into_metadata_table($doc_oid, $section_name, $meta_name, $escaped_meta_value, $self->{'debug'}); 
     328    } 
    328329    } 
    329330     
     
    337338    # The following will do the SQL insertion 
    338339    # or if debug, the following will print the SQL insert stmt without executing it 
    339     $gs_sql->insert_row_into_fulltxt_table($doc_oid, $section_name, $section_textref, $self->{'debug'}, $debug_out); 
     340    $gs_sql->insert_row_into_fulltxt_table($doc_oid, $section_name, $section_textref, $self->{'debug'}); 
    340341     
    341342    }