Changeset 32580 for main/trunk/greenstone2/perllib/gssql.pm
- Timestamp:
- 2018-11-07T18:39:13+13:00 (5 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone2/perllib/gssql.pm
r32579 r32580 33 33 use DBI; # the central package for this module used by GreenstoneSQL Plugout and Plugin 34 34 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 35 47 # singleton connection 36 48 my $_dbh_instance = undef; # calls undef() function. See https://perlmaven.com/undef-and-defined-in-perl … … 49 61 # Parameterise (one or more methods may use them): 50 62 # - db_name (which is the GS3 sitename) 51 52 # TODO: add infrastructure for db_port, AutoCommit etc53 # For port, see https://stackoverflow.com/questions/2248665/perl-script-to-connect-to-mysql-server-port-330754 55 # TODO: remove unnecessary warn() since PrintError is active56 # https://perldoc.perl.org/perlobj.html#Destructors57 58 # TODO: drop table if exists and create table if exists are available in MySQL. Use those cmds59 # instead of always first checking for existence ourselves?60 63 61 64 sub new … … 160 163 my $db_driver = $params_map->{'db_driver'} || "mysql"; 161 164 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 message163 #when connect fails165 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 164 167 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 170 178 #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; 172 182 173 183 if($params_map->{'verbosity'}) { … … 375 385 } 376 386 387 388 sub 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 # 377 406 # "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" 378 407 # MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax 379 408 # 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)397 409 sub _delete_database { 398 410 my $self= shift (@_); … … 436 448 sub insert_row_into_metadata_table { 437 449 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) = @_; 439 451 440 452 my $dbh = $self->{'db_handle'}; 441 453 442 454 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"); 444 456 445 457 # Now we're ready to execute the command, unless we're only debugging … … 447 459 if($debug_only) { 448 460 # 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"; 450 462 } 451 463 else { … … 463 475 my $self = shift (@_); 464 476 #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) = @_; 466 478 467 479 my $dbh = $self->{'db_handle'}; 468 480 469 481 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"); 471 483 472 484 # Now we're ready to execute the command, unless we're only debugging 473 485 474 # just print the statement we were going to execute, minus the fulltxt value486 # don't display the fulltxt value as it could be too long 475 487 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"; 478 490 } 479 491 else { … … 481 493 482 494 $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 484 496 } 485 497 }
Note:
See TracChangeset
for help on using the changeset viewer.