root/main/trunk/greenstone2/perllib/plugouts/MySQLPlugout.pm @ 32526

Revision 32526, 24.8 KB (checked in by ak19, 9 months ago)

MySQLPlugout now calls SUPER::method_name() instead of GreenstoneXMLPlugout::method_name(). Debug statements still left in until after MySQLPlugin has been written.

Line 
1###########################################################################
2#
3# MySQLPlugout.pm -- plugout module for writing all or some the Greenstone
4# document format (metadata and/or fulltext) into a MySQL database.
5# The rest is then still written out by GreenstoneXMLPlugout as usual.
6# A component of the Greenstone digital library software
7# from the New Zealand Digital Library Project at the
8# University of Waikato, New Zealand.
9#
10# Copyright (C) 2006 New Zealand Digital Library Project
11#
12# This program is free software; you can redistribute it and/or modify
13# it under the terms of the GNU General Public License as published by
14# the Free Software Foundation; either version 2 of the License, or
15# (at your option) any later version.
16#
17# This program is distributed in the hope that it will be useful,
18# but WITHOUT ANY WARRANTY; without even the implied warranty of
19# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
20# GNU General Public License for more details.
21#
22# You should have received a copy of the GNU General Public License
23# along with this program; if not, write to the Free Software
24# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
25#
26###########################################################################
27
28package MySQLPlugout;
29
30use strict;
31no strict 'refs';
32no strict 'subs';
33
34use util;
35use FileUtils;
36use GreenstoneXMLPlugout;
37use docprint;
38
39use DBI; # the central package for this plugout
40
41use IPC::Open2;
42use POSIX ":sys_wait_h"; # for waitpid, http://perldoc.perl.org/functions/waitpid.html
43
44
45# TODO: SIGTERM rollback and disconnect?
46
47
48# this plugout does not output xml to a file, but outputs rows into a mysql table
49sub BEGIN {
50    @MySQLPlugout::ISA = ('GreenstoneXMLPlugout');
51}
52
53
54# TODO: deal with -removeold and everything? Or type out instructions for user
55
56# TODO Q: what is "group" in GreenstoneXMLPlugout?
57# TODO Q: site_name only exists for GS3. What about GS2?
58
59my $process_mode_list =
60    [ { 'name' => "meta_only",
61        'desc' => "{MySQLPlugout.process_mode.meta_only}" },     
62      { 'name' => "text_only",
63        'desc' => "{MySQLPlugout.process_mode.text_only}" },
64      { 'name' => "all",
65        'desc' => "{MySQLPlugout.process_mode.all}" } ];
66
67my $arguments = [
68       { 'name' => "process_mode",
69     'desc' => "{MySQLPlugout.process_mode}",
70     'type' => "enum",
71     'list' => $process_mode_list,
72     'deft' => "all",
73     'reqd' => "no",
74     'hiddengli' => "no"} ];
75
76my $options = { 'name'     => "MySQLPlugout",
77        'desc'     => "{MySQLPlugout.desc}",
78        'abstract' => "no",
79        'inherits' => "yes",
80        'args'     => $arguments };
81
82sub new {
83    my ($class) = shift (@_);
84    my ($plugoutlist, $inputargs,$hashArgOptLists) = @_;
85    push(@$plugoutlist, $class);
86
87    push(@{$hashArgOptLists->{"ArgList"}},@{$arguments});
88    push(@{$hashArgOptLists->{"OptList"}},$options);
89
90    my $self = new GreenstoneXMLPlugout($plugoutlist,$inputargs,$hashArgOptLists);
91   
92    if ($self->{'info_only'}) {
93        # don't worry about any options etc
94        return bless $self, $class;
95    }
96    print STDERR "***** MySQLPlugout process mode = \"", $self->{'process_mode'}, "\"\n";
97   
98    return bless $self, $class;
99}
100
101# connect here and ensure all tables and databases exist
102sub begin {
103
104    my $self= shift (@_);
105
106    ########### TODO: these should be set from cmdline/GLI options to plugout #########
107    $self->{'db_driver'} = "mysql";
108    $self->{'site_name'} = "localsite";   
109    $self->{'db_client_user'} = "root";
110    $self->{'db_client_pwd'} = "6reenstone3";
111    $self->{'build_mode'} = "removeold";
112    #$self->{'db_host'} = "127.0.0.1";
113    #$self->{'db_encoding'} = "utf8";
114    #TODO: proc_mode is also a saveas option   
115   
116    ############ LOAD NECESSARY OPTIONS ###########
117    print STDERR "########## COLLECTION: ". $ENV{'GSDLCOLLECTION'}."\n";
118    $self->{'collection_name'} = $ENV{'GSDLCOLLECTION'};
119    print STDERR "***** MySQLPlugout process mode = \"", $self->{'process_mode'}, "\"\n";
120   
121    if(!$self->connect_to_db()) {
122    # This is fatal for the plugout, let's terminate here
123    # PrintError would already have displayed the warning message on connection fail   
124    die("Could not connect to db. Can't proceed.\n");
125    }
126   
127    my $db_name = $self->{'site_name'} || "localsite"; # one database per GS3 site
128    my $build_mode = $self->{'build_mode'} || "removeold";
129    if(!$self->load_db_and_tables($db_name, $build_mode)) {
130   
131    # This is fatal for the plugout, let's terminate here
132    # PrintError would already have displayed the warning message on connection fail   
133    die("Could not use db or prepare its tables. Can't proceed.\n");
134    }
135
136    # prepare the shared/common HANDLES to SQL insert statements that contain placeholders
137    # and which we will reuse repeatedly when actually executing the insert statements
138    my $proc_mode = $self->{'process_mode'};
139    if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
140    $self->{'metadata_prepared_insert_statement_handle'} = $self->prepare_insert_metadata_row_stmthandle();
141    }
142    if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
143    $self->{'fulltxt_prepared_insert_statement_handle'} = $self->prepare_insert_fulltxt_row_stmthandle();
144    }
145
146    print STDERR "#### Meta stmt: " . $self->{'metadata_prepared_insert_statement_handle'}->{'Statement'} . "\n";
147    print STDERR "#### Full stmt: " . $self->{'fulltxt_prepared_insert_statement_handle'}->{'Statement'} . "\n";
148   
149    # if setting up to work with sql db failed, we'd have terminated and wouldn't come up to here:
150    # won't bother preparing GreenstoneXMLPlugout by calling superclass' begin()
151    # finally, can call begin on super - important as doc.xml is opened as a group etc
152   
153    $self->SUPER::begin(@_);
154}
155
156# disconnect from database here, see inexport.pm
157sub end
158{
159    my $self = shift(@_);
160
161    # do the superclass stuff first, as any sql db failures should not prevent superclass cleanup
162    $self->SUPER::end(@_);   
163   
164    $self->disconnect_from_db() || warn("Unable to disconnect from database " . $self->{'site_name'} . "\n"); # disconnect_from_db() will also issue a warning, but this may be clearer
165}
166 
167   
168# TODO: check arc-inf.db for whether each entry is to be deleted/indexed/reindexed/been indexed
169sub saveas {
170    my $self = shift (@_);
171    my ($doc_obj, $doc_dir) = @_;
172
173    print STDERR "\n\n@@@ In saveas\n\n";
174   
175    my $proc_mode = $self->{'process_mode'};
176   
177    # 1. pre save out and saving debug handle
178
179    # must call superclass (pre/post) saveas methods, as they handle assoc_files too
180    my ($docxml_outhandler, $output_file) = $self->SUPER::pre_saveas(@_);
181
182    $self->{'debug_outhandle'} = $docxml_outhandler if ($self->{'debug'}); # STDOUT if debug
183
184    # TODO: also set debugging in begin()? Then stmts creating db and tables also sent to debug out and not executed
185
186    # TODO: remove unused old_unused_saveas from GreenstoneXMLPlugout
187   
188   
189    # 2. overriding saving behaviour to do what the superclass does PLUS saving to sql db   
190
191    #NOTE: if proc_mode == all, then "breadcrumbs" go into both meta and txt elements of doc.xml:
192    # statements pointing viewer to the sql db for contents
193   
194    # write the INVERSE into doc.xml as to what is written to the db   
195    my $docxml_output_options = { 'output' => docprint::OUTPUT_NONE };
196    if($proc_mode eq "meta_only" ) { # since only meta to go into MySQL db, text will go into docxml
197    $docxml_output_options->{'output'} = docprint::OUTPUT_TEXT_ONLY;
198    } elsif($proc_mode eq "text_only" ) { # since only full text to go into MySQL db, meta will go into docxml
199    $docxml_output_options->{'output'} = docprint::OUTPUT_META_ONLY;
200    }
201   
202    # now we've prepared to write out whatever is meant to go into docxml
203    # and can do actual the steps superclass GreenstoneXMLPlugout carries out to write out docxml
204    # So: write out the doc xml file for the current document
205    my $section_text = &docprint::get_section_xml($doc_obj, $docxml_output_options);
206    print $docxml_outhandler $section_text;   
207   
208   
209    # We also write out whatever needs to go into the MySQL database
210    $self->write_meta_and_text($doc_obj);
211
212   
213    # 3. post save out
214    #$self->SUPER::post_saveas(@_);
215    $self->SUPER::post_saveas($doc_obj, $doc_dir, $docxml_outhandler, $output_file);
216   
217   
218    # database connection is closed in end() method
219    # so we don't open and close over and over for each doc during a single build
220}
221
222
223# write meta and/or text PER DOC out to DB
224sub write_meta_and_text {
225    my $self = shift (@_);
226    my ($doc_obj) = @_;
227    my $root_section = $doc_obj->get_top_section();
228    my $doc_oid = $doc_obj->get_OID(); # we're processing a single doc at a time, so single OID
229
230    # load the prepared INSERT statement handles for both tables (can be undef for any table depending on whether meta_only or txt_only are set)
231    my $metadata_table_sth = $self->{'metadata_prepared_insert_statement_handle'};
232    my $fulltxt_table_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
233   
234    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section, $metadata_table_sth, $fulltxt_table_sth);
235}
236
237# Perl: Reading or Writing to Another Program
238# https://nnc3.com/mags/Perl3/cookbook/ch16_05.htm
239sub recursive_write_meta_and_text {
240    my $self = shift (@_);
241    my ($doc_obj, $doc_oid, $section, $metadata_table_sth, $fulltxt_table_sth) = @_;   
242
243    # If section=ROOT, write "root" as section name into table
244    # doc->get_top_section() is the name of the doc root section, which is ""
245    my $section_name = ($section eq "") ? "root" : $section;
246   
247    my $section_ptr = $doc_obj->_lookup_section ($section);
248    return "" unless defined $section_ptr;
249
250    my $debug_out = $self->{'debug_outhandle'};
251    print STDERR "#### Meta stmt: " . $metadata_table_sth->{'Statement'} . "\n";
252    print STDERR "#### Full stmt: " . $fulltxt_table_sth->{'Statement'} . "\n";
253   
254    #my $proc_mode = $self->{'process_mode'};
255    #if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
256    if($metadata_table_sth) { # meta insert statement handle will be undef if not writing meta
257   
258    foreach my $data (@{$section_ptr->{'metadata'}}) {
259        my $meta_name = $data->[0];
260        my $escaped_meta_value = &docprint::escape_text($data->[1]);
261
262        # Write out the current section's meta to collection db's METADATA table       
263       
264        # for each set of values to write to meta table, execute the prepared statement, filling in the values
265
266        if($self->{'debug'}) {
267        # just print the statement we were going to execute
268
269        print $debug_out $metadata_table_sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n";
270        }
271        else {
272       
273        $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");
274        # Execution failure will print out info anyway: since db connection sets PrintError
275        }
276    }
277    }
278   
279    #if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
280    if($fulltxt_table_sth) { # fulltxt insert statement handle will be undef if not writing fulltxt
281
282    if($self->{'debug'}) {
283        # just print the statement we were going to execute, minus the fulltxt value
284        my $txt_repr = $section_ptr->{'text'} ? "<TXT>" : "NULL";
285        print $debug_out $fulltxt_table_sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n";
286    } else {
287        my $section_text = &docprint::escape_text($section_ptr->{'text'});
288       
289        # fulltxt column can be SQL NULL. undef value gets written out as NULL:
290        # https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string
291       
292        # Write out the current section's text to collection db's FULLTeXT table
293        $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");
294        # Execution failure will print out info anyway: since db connection sets PrintError
295    }
296    }
297   
298    # output all subsections: RECURSIVE CALL
299    foreach my $subsection (@{$section_ptr->{'subsection_order'}}) {
300    &recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection", $metadata_table_sth, $fulltxt_table_sth);
301    }
302}
303
304#################################
305
306# Database access related functions
307# http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm
308# https://www.guru99.com/insert-into.html
309
310# TODO Q: What on cancelling a build: delete table? But what if it was a rebuild and the rebuild is cancelled (not the original build)?
311# Do we create a copy of the orig database as backup, then start populating current db, and if cancelled, delete current db and RENAME backup table to current?
312# https://stackoverflow.com/questions/3280006/duplicating-a-mysql-table-indexes-and-data
313# BUT what if the table is HUGE? (Think of a collection with millions of docs.) Huge overhead in copying?
314# The alternative is we just quit on cancel, but then: cancel could leave the table in a partial committed state, with no way of rolling back.
315# Unless they do a full rebuild, which will recreate the table from scratch?
316# SOLUTION-> rollback transaction on error, see https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/
317# But then should set AutoCommit to off on connection, and remember to commit every time
318
319#################
320# Database functions that use the perl DBI module (with the DBD driver module for mysql)
321#################
322
323# THE NEW DB FUNCTIONS
324# NOTE: FULLTEXT is a reserved keyword in (My)SQL. So we can't name a table or any of its columns "fulltext".
325# https://dev.mysql.com/doc/refman/5.5/en/keywords.html
326
327# TODO: Consider AutoCommit status (and Autocommit off allowing commit or rollback for GS coll build cancel) later
328
329sub connect_to_db {
330    my $self= shift (@_);
331   
332    my $db_driver = $self->{'db_driver'};
333    my $db_user = $self->{'db_client_user'} || "root";
334    my $db_pwd = $self->{'db_client_pwd'};
335    my $db_host = $self->{'db_host'} || "127.0.0.1";
336    my $db_enc = $self->{'db_encoding'} || "utf8";
337   
338    #my $db_name = $self->{'site_name'};
339   
340    # try connecting to the mysql db, if that fails it will die
341    # so don't bother preparing GreenstoneXMLPlugout by calling superclass' begin()
342
343    # localhost doesn't work for us, but 127.0.0.1 works
344    # https://metacpan.org/pod/DBD::mysql
345    # "The hostname, if not specified or specified as '' or 'localhost', will default to a MySQL server
346    # running on the local machine using the default for the UNIX socket. To connect to a MySQL server
347    # on the local machine via TCP, you must specify the loopback IP address (127.0.0.1) as the host."
348    #my $connect_str = "dbi:$db_driver:database=$db_name;host=$db_host";
349    my $connect_str = "dbi:$db_driver:host=$db_host"; # don't provide db, so we can check the db is there
350    my $dbh = DBI->connect("$connect_str", $db_user, $db_pwd,
351               {
352                   ShowErrorStatement => 1, # more informative as DBI will append failed SQL stmt to error message
353                   PrintError => 1, # on by default, but being explicit
354                   RaiseError => 0, # off by default, but being explicit
355                   AutoCommit => 1 # on by default, but being explicit
356               });
357
358    if(!$dbh) {
359    # NOTE, despite handle dbh being undefined, error code will be in DBI->err
360    return 0;   
361    }
362
363    # set encoding https://metacpan.org/pod/DBD::mysql
364    # https://dev.mysql.com/doc/refman/5.7/en/charset.html
365    # https://dev.mysql.com/doc/refman/5.7/en/charset-conversion.html
366    # Setting the encoding at db server level.
367    # Not sure if this command is mysql specific:
368    my $stmt = "set NAMES '" . $db_enc . "'";
369    $dbh->do($stmt) || warn("Unable to set charset encoding at db server level to: " . $db_enc . "\n");
370   
371    # if we're here, then connection succeeded, store handle
372    $self->{'db_handle'} = $dbh;
373    return 1;
374}
375
376sub load_db_and_tables {
377    my $self= shift (@_);
378    my ($db_name, $build_mode) = @_;
379    my $dbh = $self->{'db_handle'};
380   
381    # perl DBI switch database: https://www.perlmonks.org/?node_id=995434
382    # do() returns undef on error.
383    # connection succeeded, try to load our database. If that didn't work, attempt to create db
384    my $success = $dbh->do("use $db_name");
385   
386    if(!$success && $dbh->err == 1049) { # "Unknown database" error has code 1049 (mysql only?) meaning db doesn't exist yet
387    # attempt to create the db and its tables
388    $self->create_db($db_name) || return 0;
389
390    print STDERR "@@@ CREATED DATABASE $db_name\n";
391   
392    # once more attempt to use db, now that it exists
393    $dbh->do("use $db_name") || return 0;
394    #$dbh->do("use localsite") or die "Error (code" . $dbh->err ."): " . $dbh->errstr . "\n";
395
396    # attempt to create tables in current db
397    $self->create_metadata_table() || return 0;
398    $self->create_fulltext_table() || return 0;
399
400    $success = 1;
401    }
402    elsif($success) { # database existed and loaded successfully, but
403    # before proceeding check that the current collection's tables exist
404
405    print STDERR "@@@ DATABASE $db_name EXISTED\n";
406   
407
408    if($build_mode eq "removeold") {
409        $self->delete_collection_tables();
410    }
411
412    # use existing tables if any
413    # attempt to create tables in current db   
414    if($build_mode eq "removeold" || !$self->table_exists($self->get_metadata_table_name())) {
415        $self->create_metadata_table() || return 0;
416    } else {
417        print STDERR "@@@ Meta table exists\n";
418    }
419    if($build_mode eq "removeold" || !$self->table_exists($self->get_fulltext_table_name())) {
420        $self->create_fulltext_table() || return 0;
421    } else {
422        print STDERR "@@@ Fulltxt table exists\n";
423    }
424   
425    }
426   
427    return $success; # could still return 0, if database failed to load with an error code != 1049
428}
429
430# disconnect from db - https://metacpan.org/pod/DBI#disconnect
431# TODO: make sure to have committed or rolled back before disconnect
432# and that you've call finish() on statement handles if any fetch remnants remain
433sub disconnect_from_db {
434    my $self= shift (@_);   
435    my $dbh = $self->{'db_handle'};
436
437    # make sure any active stmt handles are finished
438    # NO: "When all the data has been fetched from a SELECT statement, the driver will automatically call finish for you. So you should not call it explicitly except when you know that you've not fetched all the data from a statement handle and the handle won't be destroyed soon."
439   
440    #$meta_sth = $self->{'metadata_prepared_insert_statement_handle'};
441    #$txt_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
442    #$meta_sth->finish() if($meta_sth);
443    #$txt_sth->finish() if($txt_sth);
444   
445    my $rc = $dbh->disconnect or warn $dbh->errstr; # The handle is of little use after disconnecting. Possibly PrintError already prints a warning and this duplicates it?
446    return $rc;
447}
448
449sub create_db {
450    my $self= shift (@_);
451    my $db_name = $self->{'site_name'};
452    my $dbh = $self->{'db_handle'};
453   
454    # https://stackoverflow.com/questions/5025768/how-can-i-create-a-mysql-database-from-a-perl-script
455    return $dbh->do("create database $db_name"); # do() will return undef on fail, https://metacpan.org/pod/DBI#do
456}
457
458
459sub create_metadata_table {
460    my $self= shift (@_);
461    my $dbh = $self->{'db_handle'};
462   
463    my $table_name = $self->get_metadata_table_name();
464
465    # If using an auto incremented primary key:
466    my $stmt = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, metaname VARCHAR(127) NOT NULL, metavalue VARCHAR(1023) NOT NULL, PRIMARY KEY(id));";
467    return $dbh->do($stmt);
468}
469
470# TODO: Investigate: https://dev.mysql.com/doc/search/?d=10&p=1&q=FULLTEXT
471# 12.9.1 Natural Language Full-Text Searches
472# to see whether we have to index the 'fulltxt' column of the 'fulltext' tables
473# or let user edit this file, or add it as another option
474sub create_fulltext_table {
475    my $self= shift (@_);
476    my $dbh = $self->{'db_handle'};
477   
478    my $table_name = $self->get_fulltext_table_name();
479
480    # If using an auto incremented primary key:
481    my $stmt = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, fulltxt LONGTEXT, PRIMARY KEY(id));";
482    return $dbh->do($stmt);
483
484}
485
486
487# USEFUL: https://metacpan.org/pod/DBI
488# "Many methods have an optional \%attr parameter which can be used to pass information to the driver implementing the method. Except where specifically documented, the \%attr parameter can only be used to pass driver specific hints. In general, you can ignore \%attr parameters or pass it as undef."
489
490
491# https://www.guru99.com/insert-into.html
492# and https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
493#     for inserting multiple rows at once
494# https://www.perlmonks.org/bare/?node_id=316183
495# https://metacpan.org/pod/DBI#do
496# https://www.quora.com/What-is-the-difference-between-prepare-and-do-statements-in-Perl-while-we-make-a-connection-to-the-database-for-executing-the-query
497# https://docstore.mik.ua/orelly/linux/dbi/ch05_05.htm
498
499# https://metacpan.org/pod/DBI#performance
500# 'The q{...} style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote like qq{...} operator if you want to interpolate variables into the string. See "Quote and Quote-like Operators" in perlop for more details.'
501sub prepare_insert_metadata_row_stmthandle {
502    my $self = shift (@_);   
503    #my ($did, $sid, $metaname, $metavalue) = @_;
504    my $dbh = $self->{'db_handle'};
505   
506    my $tablename = $self->get_metadata_table_name();
507
508    #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
509
510    # using qq{} since we want $tablename placeholder to be filled in
511    # returns Statement Handle object!
512    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
513
514    print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n";
515   
516    return $sth;
517}
518
519sub prepare_insert_fulltxt_row_stmthandle {
520    my $self = shift (@_);
521    #my ($did, $sid, $fulltext) = @_;
522    my $dbh = $self->{'db_handle'};
523   
524    my $tablename = $self->get_fulltext_table_name();
525
526    #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
527
528    # using qq{} since we want $tablename placeholder to be filled in
529    # returns Statement Handle object!
530    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
531   
532    print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n";
533   
534    return $sth;
535}
536
537# "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"
538#MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax
539# https://dev.mysql.com/doc/en/drop-database.html
540sub delete_collection_tables {
541    my $self= shift (@_);
542    my $dbh = $self->{'db_handle'};
543   
544    print STDERR "### Build mode is removeold, so deleting tables for current collection\n";
545   
546    # drop table <tablename>
547    my $table = $self->get_metadata_table_name();
548    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
549    $table = $self->get_fulltext_table_name();
550    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
551}
552
553# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)!
554# this is just for debugging
555sub _delete_database {
556    my $self= shift (@_);
557    my ($db_name) = @_;
558    my $dbh = $self->{'db_handle'};
559   
560    # "drop database dbname"
561    $dbh->do("drop database $db_name") || return 0;
562
563    return 1;
564}
565
566# More basic helper methods
567sub get_metadata_table_name {
568    my $self= shift (@_);
569    my $table_name = $self->{'collection_name'} . "_metadata";
570    return $table_name;
571}
572
573# FULLTEXT is a reserved keyword in (My)SQL. https://dev.mysql.com/doc/refman/5.5/en/keywords.html
574# So we can't name a table or any of its columns "fulltext". We use "fulltxt" instead.
575sub get_fulltext_table_name {
576    my $self= shift (@_);
577    my $table_name = $self->{'collection_name'} . "_fulltxt";
578    return $table_name;
579}
580
581# I can get my version of table_exists to work, but it's not so ideal
582# Interesting that MySQL has non-standard command to CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS,
583# see https://www.perlmonks.org/bare/?node=DBI%20Recipes
584#    The page further has a table_exists function that could work with proper comparison
585# Couldn't get the first solution at https://www.perlmonks.org/bare/?node_id=500050 to work though
586sub table_exists {
587    my $self = shift (@_);
588    my $dbh = $self->{'db_handle'};
589    my ($table_name) = @_;
590
591    my @table_list = $dbh->tables;
592    #my $tables_str = @table_list[0];
593    foreach my $table (@table_list) {
594    return 1 if ($table =~ m/$table_name/);
595    }
596    return 0;
597}
598
5991;
Note: See TracBrowser for help on using the browser.