Changeset 32524 for main/trunk


Ignore:
Timestamp:
2018-10-18T21:40:29+13:00 (6 years 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.

File:
1 edited

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