- Timestamp:
- 2018-10-24T20:41:01+13:00 (5 years ago)
- Location:
- main/trunk/greenstone2/perllib
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone2/perllib/gssql.pm
r32536 r32538 97 97 # Database functions that use the perl DBI module (with the DBD driver module for mysql) 98 98 ################# 99 100 ################### BASIC DB OPERATIONS ################## 99 101 100 102 # THE NEW DB FUNCTIONS … … 275 277 } 276 278 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 282 sub 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 297 sub _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 ########################### 277 310 278 311 # USEFUL: https://metacpan.org/pod/DBI … … 326 359 } 327 360 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 370 sub 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 386 sub 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. 403 sub get_db_handle { 404 my $self= shift (@_); 405 return $self->{'db_handle'}; 406 } 407 408 ################ HELPER METHODS ############## 356 409 357 410 # More basic helper methods … … 368 421 my $table_name = $self->{'tablename_prefix'} . "_fulltxt"; 369 422 return $table_name; 370 }371 372 # returns database handle, dbh373 sub get_db_handle {374 my $self= shift (@_);375 return $self->{'db_handle'};376 423 } 377 424 -
main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm
r32537 r32538 124 124 } 125 125 126 # need to store current section127 #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 #}133 126 134 127 # TODO Q: Why are there 3 passes when we're only indexing at doc and section level (2 passes)? … … 145 138 146 139 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" 148 141 if $self->{'verbosity'} > 1; 149 142 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 155 153 156 154 # TODO: This function is called on a per doc.xml file basis … … 164 162 # read in meta for the collection (i.e. select * from <col>_metadata table 165 163 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; 168 168 # 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 fail173 174 print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 1;175 169 while( my @row = $sth->fetchrow_array() ) { 176 170 #print $outhandle "row: @row\n"; … … 196 190 197 191 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; 202 194 203 195 print $outhandle "----------\nSQL DB contains txt entries for-----------\n" … … 226 218 227 219 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 229 221 # call init() not begin() because there can be multiple plugin passes 230 222 # and init() should be called before all passes: 231 223 # one for doc level and another for section level indexing 224 # This way, we can connect to the SQL database once per buildcol run. 232 225 sub init { 233 226 my ($self) = shift (@_); … … 290 283 } 291 284 292 285 # This method also runs on import.pl if gs_sql has a value. But we just want to run it on buildcol 293 286 # Call deinit() not end() because there can be multiple plugin passes: 294 287 # one for doc level and another for section level indexing 295 288 # and deinit() should be called before all passes 289 # This way, we can close the SQL database once per buildcol run. 296 290 sub deinit { 297 291 my ($self) = shift (@_);
Note:
See TracChangeset
for help on using the changeset viewer.