Changeset 32538

Show
Ignore:
Timestamp:
24.10.2018 20:41:01 (4 weeks ago)
Author:
ak19
Message:

Previous commit message meant to be: string names of strings shared by GS SQL Plugin and Plugout have been changed in strings.properties to indicate both modules used them. Current commit: Some tidying up the new GreenstoneSQLPlugin and moving the select statements from there into gssql.pm.

Location:
main/trunk/greenstone2/perllib
Files:
2 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/gssql.pm

    r32536 r32538  
    9797# Database functions that use the perl DBI module (with the DBD driver module for mysql) 
    9898################# 
     99 
     100################### BASIC DB OPERATIONS ################## 
    99101 
    100102# THE NEW DB FUNCTIONS 
     
    275277} 
    276278 
     279# "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" 
     280#MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax 
     281# https://dev.mysql.com/doc/en/drop-database.html 
     282sub delete_collection_tables { 
     283    my $self= shift (@_); 
     284    my $dbh = $self->{'db_handle'}; 
     285     
     286    print STDERR "### Build mode is removeold, so deleting tables for current collection\n"; 
     287     
     288    # drop table <tablename> 
     289    my $table = $self->get_metadata_table_name(); 
     290    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
     291    $table = $self->get_fulltext_table_name(); 
     292    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
     293} 
     294 
     295# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)! 
     296# this is just for debugging 
     297sub _delete_database { 
     298    my $self= shift (@_); 
     299    my ($db_name) = @_; 
     300    my $dbh = $self->{'db_handle'}; 
     301     
     302    # "drop database dbname" 
     303    $dbh->do("drop database $db_name") || return 0; 
     304 
     305    return 1; 
     306} 
     307 
     308 
     309########################### DB STATEMENTS ########################### 
    277310 
    278311# USEFUL: https://metacpan.org/pod/DBI 
     
    326359} 
    327360 
    328 # "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" 
    329 #MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax 
    330 # https://dev.mysql.com/doc/en/drop-database.html 
    331 sub delete_collection_tables { 
    332     my $self= shift (@_); 
    333     my $dbh = $self->{'db_handle'}; 
    334      
    335     print STDERR "### Build mode is removeold, so deleting tables for current collection\n"; 
    336      
    337     # drop table <tablename> 
    338     my $table = $self->get_metadata_table_name(); 
    339     $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
    340     $table = $self->get_fulltext_table_name(); 
    341     $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 
    342 } 
    343  
    344 # Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)! 
    345 # this is just for debugging 
    346 sub _delete_database { 
    347     my $self= shift (@_); 
    348     my ($db_name) = @_; 
    349     my $dbh = $self->{'db_handle'}; 
    350      
    351     # "drop database dbname" 
    352     $dbh->do("drop database $db_name") || return 0; 
    353  
    354     return 1; 
    355 } 
     361 
     362## The 2 select statements used by GreenstoneSQLPlugin 
     363 
     364# Returns the statement handle that prepared and executed 
     365# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. 
     366# Caller can call fetchrow_array() on returned statement handle, $sth 
     367# Have to use prepare() and execute() instead of do() since do() does 
     368# not allow for fetching result set thereafter: 
     369# do(): "This method  is typically most useful for non-SELECT statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for SELECT statements because it does not return a statement handle (so you can't fetch any data)." https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#do 
     370sub select_from_metatable_matching_docid { 
     371    my $self= shift (@_); 
     372    my ($oid) = @_; 
     373     
     374    my $dbh = $self->{'db_handle'}; 
     375    my $meta_table = $self->get_metadata_table_name(); 
     376     
     377    my $sth = $dbh->prepare(qq{SELECT * FROM $meta_table WHERE did = ?}); 
     378    $sth->execute( $oid ); # will print msg on fail 
     379     
     380    return $sth; # caller can call fetchrow_array() on returned statement handle, sth 
     381} 
     382 
     383# Returns the statement handle that prepared and executed 
     384# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement. 
     385# Caller can call fetchrow_array() on returned statement handle, $sth 
     386sub select_from_texttable_matching_docid { 
     387    my $self= shift (@_); 
     388    my ($oid) = @_; 
     389     
     390    my $dbh = $self->{'db_handle'}; 
     391    my $fulltxt_table = $self->get_fulltext_table_name(); 
     392     
     393    my $sth = $dbh->prepare(qq{SELECT * FROM $fulltxt_table WHERE did = ?}); 
     394    $sth->execute( $oid ); # will print msg on fail 
     395     
     396    return $sth; # caller can call fetchrow_array() on returned statement handle, sth 
     397} 
     398 
     399 
     400# Can call this after connection succeeded to get the database handle, dbh, 
     401# if any specific DB operation (SQL statement, create/delete) 
     402# needs to be executed that is not already provided as a method of this class. 
     403sub get_db_handle { 
     404    my $self= shift (@_); 
     405    return $self->{'db_handle'}; 
     406} 
     407 
     408################ HELPER METHODS ############## 
    356409 
    357410# More basic helper methods 
     
    368421    my $table_name = $self->{'tablename_prefix'} . "_fulltxt"; 
    369422    return $table_name; 
    370 } 
    371  
    372 # returns database handle, dbh 
    373 sub get_db_handle { 
    374     my $self= shift (@_); 
    375     return $self->{'db_handle'}; 
    376423} 
    377424 
  • main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm

    r32537 r32538  
    124124} 
    125125 
    126 # need to store current section  
    127 #sub xml_start_tag { 
    128 #    my $self = shift(@_); 
    129 #    $self->SUPER::xml_start_tag(@_); 
    130 #    $self->{'stored_section'} = $self->{'section'}; 
    131 #    $self->{'stored_section_level'} = $self->{'section_level'} 
    132 #} 
    133126 
    134127# TODO Q: Why are there 3 passes when we're only indexing at doc and section level (2 passes)? 
     
    145138 
    146139    my $oid = $self->{'doc_oid'}; # we stored current doc's OID during sub read()  
    147     print $outhandle "@@@@@@ OID of document (meta|text) to be read in from DB: $oid\n" 
     140    print $outhandle "==== OID of document (meta|text) to be read in from DB: $oid\n" 
    148141    if $self->{'verbosity'} > 1; 
    149142 
    150 #    print STDERR "@@@@ section: |" . $self->{'stored_section'} . "|\n"; 
    151 #    print STDERR "@@@@ section_level: " . $self->{'stored_section_level'} . "\n"; 
    152      
    153     # for now, we have access to doc_obj (until just before super::close_document() terminates) 
    154     #$self->{'doc_obj'}->set_OID($oid); 
     143     
     144    # For now, we have access to doc_obj (until just before super::close_document() terminates) 
     145     
     146    $self->{'doc_obj'}->set_OID($oid); # complex method. Is this necessary, since we just want to write meta and txt for the docobj to index? 
     147     
     148    # checking that complicated looking method set_OID() hasn't modified oid 
     149    if($oid ne $self->{'doc_obj'}->get_OID()) { 
     150    print STDERR "@@@@ WARNING: OID after setting on doc_obj = " . $self->{'doc_obj'}->get_OID() . " and is not the same as original OID $oid from docsqloid.xml filename\n"; 
     151    } 
     152 
    155153     
    156154    # TODO: This function is called on a per doc.xml file basis 
     
    164162    # read in meta for the collection (i.e. select * from <col>_metadata table 
    165163 
    166     my $meta_table = $gs_sql->get_metadata_table_name(); 
    167      
     164    my $sth = $gs_sql->select_from_metatable_matching_docid($oid);   
     165    print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 1; 
     166 
     167    print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 1; 
    168168    # https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/ 
    169     my $sth = $gs_sql->get_db_handle()->prepare(qq{SELECT * FROM $meta_table WHERE did = ?}); 
    170     print $outhandle "### stmt: ".$sth->{'Statement'}."\n" 
    171         if $self->{'verbosity'} > 1; 
    172     $sth->execute( $oid ); # will print msg on fail 
    173  
    174     print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 1; 
    175169    while( my @row = $sth->fetchrow_array() ) {      
    176170        #print $outhandle "row: @row\n"; 
     
    196190     
    197191     
    198     my $sth = $gs_sql->get_db_handle()->prepare(qq{SELECT * FROM $fulltxt_table WHERE did = ?}); 
    199     print $outhandle "### stmt: ".$sth->{'Statement'}."\n" 
    200         if $self->{'verbosity'} > 1; 
    201     $sth->execute( $oid ); # will print msg on fail 
     192    my $sth = $gs_sql->select_from_texttable_matching_docid($oid); 
     193    print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 1; 
    202194 
    203195    print $outhandle "----------\nSQL DB contains txt entries for-----------\n" 
     
    226218 
    227219 
    228 # TODO: only want to work with sql db if buildcol.pl 
     220# TODO: only want to work with sql db if buildcol.pl. Unfortunately, also runs on import.pl 
    229221# call init() not begin() because there can be multiple plugin passes 
    230222# and init() should be called before all passes: 
    231223# one for doc level and another for section level indexing 
     224# This way, we can connect to the SQL database once per buildcol run. 
    232225sub init { 
    233226    my ($self) = shift (@_); 
     
    290283} 
    291284 
    292  
     285# This method also runs on import.pl if gs_sql has a value. But we just want to run it on buildcol 
    293286# Call deinit() not end() because there can be multiple plugin passes: 
    294287# one for doc level and another for section level indexing 
    295288# and deinit() should be called before all passes 
     289# This way, we can close the SQL database once per buildcol run. 
    296290sub deinit { 
    297291    my ($self) = shift (@_);