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