Changeset 32524

Show
Ignore:
Timestamp:
18.10.2018 21:40:29 (5 weeks ago)
Author:
ak19
Message:

Creating db, creating tables and populating both coll_metadata and coll_fulltxt tables works. Added functions for deleting and recreating tables on currently hardcoded removeold build_mode param, also tested. Still lots of TODO instructions that I need to complete. But will first move on to build stage to read back in. Still to test is whether the doc.xml files got created with the breadcrumbs values pointing the user to view the database for values. Not yet tested is also meta_only and fulltext_only modes.

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm

    r32523 r32524  
    3535use docprint; 
    3636 
     37use DBI; # the central package for this plugout 
     38 
    3739use IPC::Open2; 
    3840use POSIX ":sys_wait_h"; # for waitpid, http://perldoc.perl.org/functions/waitpid.html 
     41 
    3942 
    4043# TODO: SIGTERM rollback and disconnect? 
     
    104107    $self->{'db_client_user'} = "root"; 
    105108    $self->{'db_client_pwd'} = "6reenstone3"; 
     109    $self->{'build_mode'} = "removeold"; 
    106110    #$self->{'db_host'} = "127.0.0.1"; 
    107111    #$self->{'db_encoding'} = "utf8"; 
    108     #TODO: proc_mode is also a saveas option 
     112    #TODO: proc_mode is also a saveas option     
    109113     
    110114    ############ LOAD NECESSARY OPTIONS ########### 
    111115    print STDERR "########## COLLECTION: ". $ENV{'GSDLCOLLECTION'}."\n"; 
    112116    $self->{'collection_name'} = $ENV{'GSDLCOLLECTION'}; 
     117    print STDERR "***** MySQLPlugout process mode = \"", $self->{'process_mode'}, "\"\n"; 
    113118     
    114119    if(!$self->connect_to_db()) { 
     
    117122    die("Could not connect to db. Can't proceed.\n"); 
    118123    } 
    119     if(!$self->load_db_and_tables()) { 
     124     
     125    my $db_name = $self->{'site_name'} || "localsite"; # one database per GS3 site 
     126    my $build_mode = $self->{'build_mode'} || "removeold"; 
     127    if(!$self->load_db_and_tables($db_name, $build_mode)) { 
     128     
    120129    # This is fatal for the plugout, let's terminate here 
    121130    # PrintError would already have displayed the warning message on connection fail     
     
    133142    } 
    134143 
     144    print STDERR "#### Meta stmt: " . $self->{'metadata_prepared_insert_statement_handle'}->{'Statement'} . "\n"; 
     145    print STDERR "#### Full stmt: " . $self->{'fulltxt_prepared_insert_statement_handle'}->{'Statement'} . "\n"; 
     146     
    135147    # if setting up to work with sql db failed, we'd have terminated and wouldn't come up to here: 
    136148    # won't bother preparing GreenstoneXMLPlugout by calling superclass' begin() 
     
    157169    my ($doc_obj, $doc_dir) = @_; 
    158170 
     171    print STDERR "\n\n@@@ In saveas\n\n"; 
     172     
    159173    my $proc_mode = $self->{'process_mode'}; 
    160174     
     
    216230    my $fulltxt_table_sth = $self->{'fulltxt_prepared_insert_statement_handle'}; 
    217231     
    218     $self->recursive_write_meta_and_text($doc_obj, $root_section, $metadata_table_sth, $fulltxt_table_sth); 
     232    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section, $metadata_table_sth, $fulltxt_table_sth); 
    219233} 
    220234 
     
    233247 
    234248    my $debug_out = $self->{'debug_outhandle'}; 
     249    print STDERR "#### Meta stmt: " . $metadata_table_sth->{'Statement'} . "\n"; 
     250    print STDERR "#### Full stmt: " . $fulltxt_table_sth->{'Statement'} . "\n"; 
    235251     
    236252    #my $proc_mode = $self->{'process_mode'}; 
     
    240256    foreach my $data (@{$section_ptr->{'metadata'}}) { 
    241257        my $meta_name = $data->[0]; 
    242         my $escaped_meta_value = &escape_text($data->[1]); 
     258        my $escaped_meta_value = &docprint::escape_text($data->[1]); 
    243259 
    244260        # Write out the current section's meta to collection db's METADATA table         
     
    249265        # just print the statement we were going to execute 
    250266 
    251         print $debug_out $metadata_table_sth->Statement . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n"; 
     267        print $debug_out $metadata_table_sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n"; 
    252268        } 
    253269        else { 
    254270         
    255         $metadata_table_sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value);  
    256         #|| warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value"); 
     271        $metadata_table_sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value) || warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value"); 
    257272        # Execution failure will print out info anyway: since db connection sets PrintError 
    258273        } 
     
    266281        # just print the statement we were going to execute, minus the fulltxt value 
    267282        my $txt_repr = $section_ptr->{'text'} ? "<TXT>" : "NULL"; 
    268         print $debug_out $fulltxt_table_sth->Statement . "($doc_oid, $section_name, $txt_repr)\n"; 
     283        print $debug_out $fulltxt_table_sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n"; 
    269284    } else { 
    270         my $section_text = &escape_text($section_ptr->{'text'}); 
     285        my $section_text = &docprint::escape_text($section_ptr->{'text'}); 
    271286         
    272287        # fulltxt column can be SQL NULL. undef value gets written out as NULL: 
     
    274289         
    275290        # Write out the current section's text to collection db's FULLTeXT table 
    276         $fulltxt_table_sth->execute($doc_oid, $section_name, $section_text); 
    277         #|| warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name"); 
     291        $fulltxt_table_sth->execute($doc_oid, $section_name, $section_text) || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name"); 
    278292        # Execution failure will print out info anyway: since db connection sets PrintError 
    279293    } 
     
    337351                   PrintError => 1, # on by default, but being explicit 
    338352                   RaiseError => 0, # off by default, but being explicit 
    339                    AutoCommit => 1, # on by default, but being explicit 
     353                   AutoCommit => 1 # on by default, but being explicit 
    340354               }); 
    341355 
     
    360374sub load_db_and_tables { 
    361375    my $self= shift (@_); 
    362     my $db_name = $self->{'site_name'}; # one database per GS3 site 
     376    my ($db_name, $build_mode) = @_; 
    363377    my $dbh = $self->{'db_handle'}; 
    364378     
     
    372386    $self->create_db($db_name) || return 0; 
    373387 
     388    print STDERR "@@@ CREATED DATABASE $db_name\n"; 
     389     
    374390    # once more attempt to use db, now that it exists 
    375391    $dbh->do("use $db_name") || return 0; 
     
    384400    elsif($success) { # database existed and loaded successfully, but 
    385401    # before proceeding check that the current collection's tables exist 
     402 
     403    print STDERR "@@@ DATABASE $db_name EXISTED\n"; 
    386404     
    387     # attempt to create tables in current db 
    388     if(!$self->table_exists($self->{'collection_name'} . "metadata")) { 
     405 
     406    if($build_mode eq "removeold") { 
     407        $self->delete_collection_tables(); 
     408    } 
     409 
     410    # use existing tables if any 
     411    # attempt to create tables in current db     
     412    if($build_mode eq "removeold" || !$self->table_exists($self->get_metadata_table_name())) { 
    389413        $self->create_metadata_table() || return 0; 
     414    } else { 
     415        print STDERR "@@@ Meta table exists\n"; 
    390416    } 
    391     if(!$self->table_exists($self->{'collection_name'} .  "fulltxt")) { 
     417    if($build_mode eq "removeold" || !$self->table_exists($self->get_fulltext_table_name())) { 
    392418        $self->create_fulltext_table() || return 0; 
     419    } else { 
     420        print STDERR "@@@ Fulltxt table exists\n"; 
    393421    } 
     422     
    394423    } 
    395424     
     
    425454} 
    426455 
     456 
    427457sub create_metadata_table { 
    428458    my $self= shift (@_); 
    429459    my $dbh = $self->{'db_handle'}; 
    430460     
    431     my $table_name = $self->{'collection_name'} . "metadata"; 
     461    my $table_name = $self->get_metadata_table_name(); 
    432462 
    433463    # If using an auto incremented primary key: 
     
    444474    my $dbh = $self->{'db_handle'}; 
    445475     
    446     my $table_name = $self->{'collection_name'} . "fulltxt"; 
     476    my $table_name = $self->get_fulltext_table_name(); 
    447477 
    448478    # If using an auto incremented primary key: 
     
    469499sub prepare_insert_metadata_row_stmthandle { 
    470500    my $self = shift (@_);     
    471     my ($did, $sid, $metaname, $metavalue) = @_; 
    472     my $dbh = $self->{'db_handle'}; 
    473      
    474     my $tablename = $self->{'colname'}."_metadata"; 
     501    #my ($did, $sid, $metaname, $metavalue) = @_; 
     502    my $dbh = $self->{'db_handle'}; 
     503     
     504    my $tablename = $self->get_metadata_table_name(); 
    475505 
    476506    #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ? 
     
    478508    # using qq{} since we want $tablename placeholder to be filled in 
    479509    # returns Statement Handle object! 
    480     my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)"}) || warn("Could not prepare insert statement for metadata table\n"); 
     510    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n"); 
     511 
     512    print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n"; 
    481513     
    482514    return $sth; 
     
    485517sub prepare_insert_fulltxt_row_stmthandle { 
    486518    my $self = shift (@_); 
    487     my ($did, $sid, $fulltext) = @_; 
    488     my $dbh = $self->{'db_handle'}; 
    489      
    490     my $tablename = $self->{'colname'}."_fulltxt"; 
     519    #my ($did, $sid, $fulltext) = @_; 
     520    my $dbh = $self->{'db_handle'}; 
     521     
     522    my $tablename = $self->get_fulltext_table_name(); 
    491523 
    492524    #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ? 
     
    494526    # using qq{} since we want $tablename placeholder to be filled in 
    495527    # returns Statement Handle object! 
    496     my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)"}) || warn("Could not prepare insert statement for fulltxt table\n"); 
     528    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n"); 
     529     
     530    print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n"; 
    497531     
    498532    return $sth; 
     533} 
     534 
     535# "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" 
     536#MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax 
     537# https://dev.mysql.com/doc/en/drop-database.html 
     538sub delete_collection_tables { 
     539    my $self= shift (@_); 
     540    my $dbh = $self->{'db_handle'}; 
     541     
     542    print STDERR "### Build mode is removeold, so deleting tables for current collection\n"; 
     543     
     544    # drop table <tablename> 
     545    my $table = $self->get_metadata_table_name(); 
     546    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
     547    $table = $self->get_fulltext_table_name(); 
     548    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
     549} 
     550 
     551# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)! 
     552# this is just for debugging 
     553sub _delete_database { 
     554    my $self= shift (@_); 
     555    my ($db_name) = @_; 
     556    my $dbh = $self->{'db_handle'}; 
     557     
     558    # "drop database dbname" 
     559    $dbh->do("drop database $db_name") || return 0; 
     560 
     561    return 1; 
     562} 
     563 
     564# More basic helper methods 
     565sub get_metadata_table_name { 
     566    my $self= shift (@_); 
     567    my $table_name = $self->{'collection_name'} . "_metadata"; 
     568    return $table_name; 
     569} 
     570 
     571# FULLTEXT is a reserved keyword in (My)SQL. https://dev.mysql.com/doc/refman/5.5/en/keywords.html 
     572# So we can't name a table or any of its columns "fulltext". We use "fulltxt" instead. 
     573sub get_fulltext_table_name { 
     574    my $self= shift (@_); 
     575    my $table_name = $self->{'collection_name'} . "_fulltxt"; 
     576    return $table_name; 
    499577} 
    500578 
     
    505583# Couldn't get the first solution at https://www.perlmonks.org/bare/?node_id=500050 to work though 
    506584sub table_exists { 
    507     my ($dbh,$table_name) = @_; 
     585    my $self = shift (@_); 
     586    my $dbh = $self->{'db_handle'}; 
     587    my ($table_name) = @_; 
    508588 
    509589    my @table_list = $dbh->tables;