Changeset 32580


Ignore:
Timestamp:
2018-11-07T18:39:13+13:00 (5 years 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 edited

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    }
Note: See TracChangeset for help on using the changeset viewer.