- Timestamp:
- 2018-11-07T18:39:13+13:00 (5 years ago)
- Location:
- main/trunk/greenstone2/perllib
- Files:
-
- 3 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 } -
main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm
r32578 r32580 427 427 my $gs_sql = new gssql({ 428 428 'collection_name' => $ENV{'GSDLCOLLECTION'}, 429 'verbosity' => $self->{'verbosity'} 429 'verbosity' => $self->{'verbosity'} || 0 430 430 }); 431 431 -
main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm
r32578 r32580 42 42 # TODO Q: what about verbosity for debugging, instead of current situation of printing out upon debug set at the expense of writing to db 43 43 # 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? 44 46 45 47 # this plugout does not output the metadata and/or fulltxt xml to a file, … … 49 51 } 50 52 51 # NOTTODO: die() statements need to be replaced with premature_termination53 # + NOTTODO: die() statements need to be replaced with premature_termination 52 54 # which should ensure the GreenstoneXMLPlugin (group)'s stuff is closed and cleaned up SOMEHOW 53 55 # It's fine: the die() stmts all take place before setting up the super class' begin 54 56 55 # TODO Q: about build_mode: how to detect removeold. Now handled by57 # + TODO Q: about build_mode: how to detect removeold. Now handled by 56 58 # GreenstoneSQLPlugout::remove_all(), which is inherited from a base plugin. 57 # TODO: deal with -removeold and everything? Or type out instructions for user58 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? 60 62 61 63 my $process_mode_list = … … 127 129 128 130 my $self= shift (@_); 129 131 130 132 # The saveas.options 131 133 #print STDERR "@@@@ PLUGOUT db_pwd: " . $self->{'db_client_pwd'} . "\n"; … … 143 145 my $db_params = { 144 146 'collection_name' => $ENV{'GSDLCOLLECTION'}, 145 'verbosity' => 1147 'verbosity' => $self->{'verbosity'} || 0 146 148 }; 147 149 … … 226 228 } 227 229 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? 229 231 # That's only for indexing, not for this step which only generates the content in archives dir 230 232 sub saveas { … … 315 317 # from db (unlike for reading back in from doc.xml) 316 318 my $escaped_meta_value = &docprint::escape_text($data->[1]); 317 319 318 320 # Write out the current section's meta to collection db's METADATA table 319 321 … … 322 324 # filling in the values 323 325 # 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 } 328 329 } 329 330 … … 337 338 # The following will do the SQL insertion 338 339 # 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'}); 340 341 341 342 }
Note:
See TracChangeset
for help on using the changeset viewer.