root/main/trunk/greenstone2/perllib/plugins/GreenstoneSQLPlugin.pm @ 32577

Revision 32577, 21.4 KB (checked in by ak19, 10 months ago)

Forgot to call superclass in overridden removeall(). Nothing broke so far only because the superclass chain didn't have an actual implementation.

Line 
1###########################################################################
2#
3# GreenstoneSQLPlugin.pm -- reads into doc_obj from SQL db and docsql.xml
4# Metadata and/or fulltext are stored in SQL db, the rest may be stored in
5# the docsql .xml files.
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) 2001 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 GreenstoneSQLPlugin;
29
30
31use strict;
32no strict 'refs'; # allow filehandles to be variables and viceversa
33
34use DBI;
35use docprint; # for new unescape_text() subroutine
36use GreenstoneXMLPlugin;
37use gssql;
38
39
40# TODO:
41# - Run TODOs here, in Plugout and in gssql.pm by Dr Bainbridge.
42# - Have not yet tested writing out just meta or just fulltxt to sql db and reading just that
43# back in from the sql db while the remainder is to be read back in from the docsql .xml files.
44
45# TODO: Add public instructions on using this plugin and its plugout: start with installing mysql binary, changing pwd, running the server (and the client against it for checking: basic cmds like create and drop). Then discuss db name, table names (per coll), db cols and col types, and how the plugout and plugin work.
46# Discuss the plugin/plugout parameters.
47
48# TODO, test on windows and mac.
49# Note: if parsing fails (e.g. using wrong plugout like GS XML plugout, which chokes on args intended for SQL plugout) then SQL plugin init would have already been called and done connection, but disconnect would not have been done because SQL plugin disconnect would not have been called upon parse failure.
50
51# DONE:
52# + TODO: Incremental delete can't work until GSSQLPlugout has implemented build_mode = incremental
53# (instead of tossing away db on every build)
54# + Ask about docsql naming convention adopted to identify OID. Better way?
55# collection names -> table names: it seems hyphens not allowed. Changed to underscores.
56# + Startup parameters (except removeold/build_mode)
57# + how do we detect we're to do removeold during plugout in import.pl phase
58# + incremental building: where do we need to add code to delete rows from our sql table after
59# incrementally importing a coll with fewer docs (for instance)? What about deleted/modified meta?
60# + Ask if I can assume that all SQL dbs (not just MySQL) will preserve the order of inserted nodes
61# (sections) which in this case had made it easy to reconstruct the doc_obj in memory in the correct order.
62# YES: Otherwise for later db types (drivers), can set order by primary key column and then order by did column
63# + NOTTODO: when db is not running GLI is paralyzed -> can we set timeout on DBI connection attempt?
64#   NOT A PROBLEM: Tested to find DBI connection attempt fails immediately when MySQL server not
65# running. The GLI "paralyzing" incident last time was not because of the gs sql connection code,
66# but because my computer was freezing on-and-off.
67# + "Courier" demo documents in lucene-sql collection: character (degree symbol) not preserved in title. Is this because we encode in utf8 when putting into db and reading back in?
68# Test doc with meta and text like macron in Maori text.
69# + TODO Q: During import, the GS SQL Plugin is called before the GS SQL Plugout with undesirable side
70# effect that if the db doesn't exist, gssql::use_db() fails, as it won't create db.
71#   This got fixed when GSSQLPlugin stopped connecting on init().
72#
73#
74#+ TODO: deal with incremental vs removeold. If docs removed from import folder, then import step
75# won't delete it from archives but buildcol step will. Need to implement this with this database plugin or wherever the actual flow is.
76#
77# + TODO Q: is "reindex" = del from db + add to db?
78# - is this okay for reindexing, or will it need to modify existing values (update table)
79# - if it's okay, what does reindex need to accomplish (and how) if the OID changes because hash id produced is different?
80# - delete is accomplished in GS SQL Plugin, during buildcol.pl. When should reindexing take place?
81# during SQL plugout/import.pl or during plugin? If adding is done by GSSQLPlugout, does it need to
82# be reimplemented in GSSQLPlugin to support the adding portion of reindexing.
83#
84# INCREMENTAL REBUILDING IMPLEMENTED CORRECTLY AND WORKS:
85# Overriding plugins' remove_all() method covered removeold.
86# Overriding plugins' remove_one() method is all I needed to do for reindex and deletion
87# (incremental and non-incremental) to work.
88# but doing all this needed an overhaul of gssql.pm and its use by the GS SQL plugin and plugout.
89# - needed to correct plugin.pm::remove_some() to process all files
90# - and needed to correct GreenstoneSQLPlugin::close_document() to setOID() after all
91# All incremental import and buildcol worked after that:
92# - deleting files and running incr-import and incr-buildcol (= "incr delete"),
93# - deleting files and running incr-import and buildcol (="non-incr delete")
94# - modifying meta and doing an incr rebuild
95# - modifying fulltext and doing an incr rebuild
96# - renaming a file forces a reindex: doc is removed from db and added back in, due to remove_one()
97# - tested CSV file: adding some records, changing some records
98#    + CSVPlugin test (collection csvsql)
99#    + MetadataCSVPlugin test (modified collection sqltest to have metadata.csv refer to the
100#      filenames of sqltest's documents)
101#    + shared image test (collection shareimg): if 2 html files reference the same image, the docs
102#      are indeed both reindexed if the image is modified (e.g. I replaced the image with another
103#      of the same name) which in the GS SQL plugin/plugout case is that the 2 docs are deleted
104#      and added in again.
105
106########################################################################################
107
108# GreenstoneSQLPlugin inherits from GreenstoneXMLPlugin so that it if meta or fulltext
109# is still written out to doc.xml (docsql .xml), that will be processed as usual,
110# whereas GreenstoneSQLPlugin will process all the rest (full text and/or meta, whichever
111# is written out by GreenstoneSQLPlugout into the SQL db).
112
113
114sub BEGIN {
115    @GreenstoneSQLPlugin::ISA = ('GreenstoneXMLPlugin');
116}
117
118# This plugin must be in the document plugins pipeline IN PLACE OF GreenstoneXMLPlugin
119# So we won't have a process exp conflict here.
120# The structure of docsql.xml files is identical to doc.xml and the contents are similar except:
121#   - since metadata and/or fulltxt are stored in mysql db instead, just XML comments indicating
122#   this are left inside docsql.xml within the <Description> (for meta) and/or <Content> (for txt)
123#   - the root element Archive now has a docoid attribute: <Archive docoid="OID">
124sub get_default_process_exp {
125    my $self = shift (@_);
126
127    return q^(?i)docsql(-\d+)?\.xml$^; # regex based on this method in GreenstoneXMLPlugin
128    #return q^(?i)docsql(-.+)?\.xml$^; # no longer storing the OID embedded in docsql .xml filename
129}
130
131my $process_mode_list =
132    [ { 'name' => "meta_only",
133        'desc' => "{GreenstoneSQLPlug.process_mode.meta_only}" },     
134      { 'name' => "text_only",
135        'desc' => "{GreenstoneSQLPlug.process_mode.text_only}" },
136      { 'name' => "all",
137        'desc' => "{GreenstoneSQLPlug.process_mode.all}" } ];
138
139my $arguments =
140    [ { 'name' => "process_exp",
141    'desc' => "{BaseImporter.process_exp}",
142    'type' => "regexp",
143    'deft' => &get_default_process_exp(),
144    'reqd' => "no" },
145      { 'name' => "process_mode",
146    'desc' => "{GreenstoneSQLPlug.process_mode}",
147    'type' => "enum",
148    'list' => $process_mode_list,
149    'deft' => "all",
150    'reqd' => "no"},
151      { 'name' => "db_driver",
152    'desc' => "{GreenstoneSQLPlug.db_driver}",
153    'type' => "string",
154    'deft' => "mysql",
155    'reqd' => "yes"},
156      { 'name' => "db_client_user",
157    'desc' => "{GreenstoneSQLPlug.db_client_user}",
158    'type' => "string",
159    'deft' => "root",
160    'reqd' => "yes"},
161      { 'name' => "db_client_pwd",
162    'desc' => "{GreenstoneSQLPlug.db_client_pwd}",
163    'type' => "string",
164    'deft' => "",
165    'reqd' => "yes"}, # pwd required?
166      { 'name' => "db_host",
167    'desc' => "{GreenstoneSQLPlug.db_host}",
168    'type' => "string",
169    'deft' => "127.0.0.1",
170    'reqd' => "yes"},
171    ];
172
173my $options = { 'name'     => "GreenstoneSQLPlugin",
174        'desc'     => "{GreenstoneSQLPlugin.desc}",
175        'abstract' => "no",
176        'inherits' => "yes",
177            'args'     => $arguments };
178
179
180# TODO: For on cancel, add a SIGTERM handler or so to call end()
181# or to explicitly call gs_sql->close_connection if $gs_sql def
182
183sub new {
184    my ($class) = shift (@_);
185    my ($pluginlist,$inputargs,$hashArgOptLists) = @_;
186    push(@$pluginlist, $class);
187
188    push(@{$hashArgOptLists->{"ArgList"}},@{$arguments});
189    push(@{$hashArgOptLists->{"OptList"}},$options);
190
191    my $self = new GreenstoneXMLPlugin($pluginlist, $inputargs, $hashArgOptLists);
192
193   
194    #return bless $self, $class;
195    $self = bless $self, $class;
196    if ($self->{'info_only'}) {
197    # If running pluginfo, we don't need to go further.
198    return $self;
199    }
200
201    # do anything else that needs to be done here when not pluginfo
202   
203    return $self;
204}
205
206# This is called once if removeold is set with import.pl. Most plugins will do
207# nothing but if a plugin does any stuff outside of creating doc obj, then
208# it may need to clear something.
209# In the case of GreenstoneSQL plugs: this is the first time we have a chance
210# to purge the tables of the current collection from the current site's database
211sub remove_all {
212    my $self = shift (@_);
213    my ($pluginfo, $base_dir, $processor, $maxdocs) = @_;
214
215    $self->SUPER::remove_all(@_);
216   
217    print STDERR "   Building with removeold option set, so deleting current collection's tables if they exist\n" if($self->{'verbosity'});
218   
219    # if we're in here, we'd already have run 'use database <site_name>;' during sub init()
220    # so we can go ahead and delete the collection's tables
221    my $gs_sql = $self->{'gs_sql'};
222    $gs_sql->delete_collection_tables(); # will delete them if they exist
223
224    # and recreate tables? No. Tables' existence is ensured in GreenstoneSQLPlugout::begin()
225    my $proc_mode = $self->{'process_mode'};
226    if($proc_mode ne "text_only") {
227    $gs_sql->ensure_meta_table_exists();
228    }
229    if($proc_mode ne "meta_only") {
230    $gs_sql->ensure_fulltxt_table_exists();
231    }
232}
233
234# This is called per document for docs that have been deleted from the
235# collection. Most plugins will do nothing
236# but if a plugin does any stuff outside of creating doc obj, then it may need
237# to clear something.
238# remove the doc(s) denoted by oids from GS SQL db
239# This takes care of incremental deletes (docs marked D by ArchivesInfPlugin when building
240# incrementally) as well as cases of "Non-icremental Delete", see ArchivesInfPlugin.pm
241sub remove_one {
242    my $self = shift (@_);
243   
244    my ($file, $oids, $archivedir) = @_;
245
246    my $rv = $self->SUPER::remove_one(@_);
247   
248    print STDERR "@@@ IN SQLPLUG::REMOVE_ONE: $file\n";
249   
250    #return undef unless $self->can_process_this_file($file); # NO, DON'T DO THIS (inherited remove_one behaviour) HERE:
251           # WE DON'T CARE IF IT'S AN IMAGE FILE THAT WAS DELETED.
252           # WE CARE ABOUT REMOVING THE DOCOID OF THAT IMAGE FILE FROM THE SQL DB
253           # SO DON'T RETURN IF CAN'T_PROCESS_THIS_FILE
254   
255   
256    my $gs_sql = $self->{'gs_sql'} || return 0; # couldn't make the connection or no db etc
257
258    print STDERR "*****************************\nAsked to remove_one oid\n***********************\n";
259    print STDERR "Num oids: " . scalar (@$oids) . "\n";
260   
261    my $proc_mode = $self->{'process_mode'};
262    foreach my $oid (@$oids) { 
263    if($proc_mode eq "all" || $proc_mode eq "meta_only") {
264        print STDERR "@@@@@@@@ Deleting $oid from meta table\n" if $self->{'verbosity'} > 2;
265        $gs_sql->delete_recs_from_metatable_with_docid($oid);
266    }
267    if($proc_mode eq "all" || $proc_mode eq "text_only") {
268        print STDERR "@@@@@@@@ Deleting $oid from fulltxt table\n" if $self->{'verbosity'} > 2;
269        $gs_sql->delete_recs_from_texttable_with_docid($oid);
270    }
271    }
272    return $rv;
273}
274
275
276sub xml_start_tag {
277    my $self = shift(@_);
278    my ($expat, $element) = @_;
279
280    my $outhandle = $self->{'outhandle'};
281   
282    $self->{'element'} = $element;
283    if ($element eq "Archive") { # docsql.xml files contain a OID attribute on Archive element
284    # the element's attributes are in %_ as per ReadXMLFile::xml_start_tag() (while $_
285    # contains the tag)
286
287    # Don't access %_{'docoid'} directly: keep getting a warning message to
288    # use $_{'docoid'} for scalar contexts, but %_ is the element's attr hashmap
289    # whereas $_ has the tag info. So we don't want to do $_{'docoid'}.
290    my %attr_hash = %_; # right way, see OAIPlugin.pm
291    $self->{'doc_oid'} = $attr_hash{'docoid'};
292    ##print STDERR "XXXXXXXXXXXXXX in SQLPlugin::xml_start_tag()\n";
293    print $outhandle "Extracted OID from docsql.xml: ".$self->{'doc_oid'}."\n"
294        if $self->{'verbosity'} > 2;
295
296    }
297    else { # let superclass GreenstoneXMLPlugin continue to process <Section> and <Metadata> elements
298    $self->SUPER::xml_start_tag(@_);
299    }
300}
301
302# TODO Q: Why are there 4 passes when we're only indexing at doc and section level (2 passes)? What's the dummy pass, why is there a pass for infodb?
303
304# We should only ever get here during the buildcol.pl phase
305# At the end of superclass GreenstoneXMLPlugin.pm's close_document() method,
306# the doc_obj in memory is processed (indexed) and then made undef.
307# So we have to work with doc_obj before superclass close_document() is finished.
308sub close_document {
309    my $self = shift(@_);
310
311    ##print STDERR "XXXXXXXXX in SQLPlugin::close_doc()\n";
312   
313    my $gs_sql = $self->{'gs_sql'};
314   
315    my $outhandle = $self->{'outhandle'};
316    my $doc_obj = $self->{'doc_obj'};
317
318    my $oid = $self->{'doc_oid'}; # we stored current doc's OID during sub xml_start_tag()
319    my $proc_mode = $self->{'process_mode'};
320   
321    # For now, we have access to doc_obj (until just before super::close_document() terminates)
322
323    # OID parsed of docsql.xml file does need to be set on $doc_obj, as noticed in this case:
324    # when a doc in import is renamed, and you do incremental import, it is marked for reindexing
325    # (reindexing is implemented by this plugin as a delete followed by add into the sql db).
326    # In that case, UNLESS you set the OID at this stage, the old deleted doc id (for the old doc
327    # name) continues to exist in the index at the end of incremental rebuilding if you were to
328    # browse the rebuilt collection by files/titles. So unless you set the OID here, the deleted
329    # doc oids will still be listed in the index.
330    $self->{'doc_obj'}->set_OID($oid);
331   
332    print STDERR "   GreenstoneSQLPlugin processing doc $oid (reading into docobj from SQL db)\n"
333    if $self->{'verbosity'} > 0;
334   
335    if($proc_mode eq "all" || $proc_mode eq "meta_only") {
336    # read in meta for the collection (i.e. select * from <col>_metadata table
337   
338    my $records = $gs_sql->select_from_metatable_matching_docid($oid, $outhandle);
339   
340    print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 2;
341    # https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/
342
343    foreach my $row (@$records) {
344        #print $outhandle "row: @$row\n";
345        my ($primary_key, $did, $sid, $metaname, $metaval) = @$row;
346       
347        # get rid of the artificial "root" introduced in section id when saving to sql db
348        $sid =~ s@^root@@;
349        $sid = $doc_obj->get_top_section() unless $sid;
350        print $outhandle "### did: $did, sid: |$sid|, meta: $metaname, val: $metaval\n"
351        if $self->{'verbosity'} > 2;
352       
353        # TODO:  we accessed the db in utf8 mode, so, we can call doc_obj->add_utf8_meta directly:
354        $doc_obj->add_utf8_metadata($sid, $metaname, &docprint::unescape_text($metaval));
355    }
356    print $outhandle "----------FIN READING DOC's META FROM SQL DB------------\n"
357        if $self->{'verbosity'} > 2;
358    }
359   
360    if($proc_mode eq "all" || $proc_mode eq "text_only") {
361    # read in fulltxt for the collection (i.e. select * from <col>_fulltxt table
362   
363    my $fulltxt_table = $gs_sql->get_fulltext_table_name();
364   
365   
366    my $records = $gs_sql->select_from_texttable_matching_docid($oid, $outhandle);
367   
368   
369    print $outhandle "----------\nSQL DB contains txt entries for-----------\n"
370        if $self->{'verbosity'} > 2;
371
372    foreach my $row (@$records) {
373        my ($primary_key, $did, $sid, $text) = @$row;
374       
375        # get rid of the artificial "root" introduced in section id when saving to sql db
376        #$sid =~ s@^root@@;
377        $sid = $doc_obj->get_top_section() if ($sid eq "root");
378        print $outhandle "### did: $did, sid: |$sid|, fulltext: <TXT>\n"
379        if $self->{'verbosity'} > 2;
380       
381        # TODO - pass by ref?
382        # TODO: we accessed the db in utf8 mode, so, we can call doc_obj->add_utf8_text directly:
383        my $textref = &docprint::unescape_textref(\$text);
384        $doc_obj->add_utf8_text($sid, $$textref);
385    }   
386    print $outhandle "----------FIN READING DOC's TXT FROM SQL DB------------\n"
387        if $self->{'verbosity'} > 2;
388    }
389   
390    # done reading into docobj from SQL db
391   
392    # don't forget to clean up on close() in superclass
393    # It will get the doc_obj indexed then make it undef
394    $self->SUPER::close_document(@_);
395}
396
397
398# GS SQL Plugin::init() (and deinit()) is called by import.pl and also by buildcol.pl
399# This means it connects and deconnects during import.pl as well. This is okay
400# as removeold, which should drop the collection tables, happens during the import phase,
401# calling GreenstoneSQLPlugin::and therefore also requires a db connection.
402# TODO: Eventually can try moving get_gssql_instance into gssql.pm? That way both GS SQL Plugin
403# and Plugout would be using one connection during import.pl phase when both plugs exist.
404
405# Call init() not begin() because there can be multiple plugin passes and begin() called for
406# each pass (one for doc level and another for section level indexing), whereas init() should
407# be called before any and all passes.
408# This way, we can connect to the SQL database once per buildcol run.
409sub init {
410    my ($self) = shift (@_);
411    ##print STDERR "@@@@@@@@@@ INIT CALLED\n";
412   
413    $self->SUPER::init(@_); # super (GreenstoneXMLPlugin) will not yet be trying to read from doc.xml (docsql .xml) files in init().
414
415    ####################
416#    print "@@@ SITE NAME: ". $self->{'site_name'} . "\n" if defined $self->{'site_name'};
417#    print "@@@ COLL NAME: ". $ENV{'GSDLCOLLECTION'} . "\n";
418
419#    print STDERR "@@@@ db_pwd: " . $self->{'db_client_pwd'} . "\n";
420#    print STDERR "@@@@ user: " . $self->{'db_client_user'} . "\n";
421#    print STDERR "@@@@ db_host: " . $self->{'db_host'} . "\n";
422#    print STDERR "@@@@ db_driver: " . $self->{'db_driver'} . "\n";
423    ####################
424
425    # create gssql object.
426    # collection name will be used for naming tables (site name will be used for naming database)
427    my $gs_sql = new gssql({
428    'collection_name' => $ENV{'GSDLCOLLECTION'},
429    'verbosity' => $self->{'verbosity'}
430               });
431
432    # try connecting to the mysql db, if that fails it will die
433    if(!$gs_sql->connect_to_db({
434    'db_driver' => $self->{'db_driver'},
435    'db_client_user' => $self->{'db_client_user'},
436    'db_client_pwd' => $self->{'db_client_pwd'},
437    'db_host' => $self->{'db_host'}
438                   })
439    )
440    {
441    # This is fatal for the plugout, let's terminate here
442    # PrintError would already have displayed the warning message on connection fail   
443    die("Could not connect to db. Can't proceed.\n");
444    }
445   
446    my $db_name = $self->{'site_name'} || "greenstone2"; # one database per GS3 site, for GS2 the db is called greenstone2
447
448    # Attempt to use the db, create it if it doesn't exist (but don't create the tables yet)
449    # Bail if we can't use the database
450    if(!$gs_sql->use_db($db_name)) {
451   
452    # This is fatal for the plugout, let's terminate here after disconnecting again
453    # PrintError would already have displayed the warning message on load fail
454    $gs_sql->disconnect_from_db()
455        || warn("Unable to disconnect from database.\n");
456    die("Could not use db $db_name. Can't proceed.\n");
457    }
458   
459   
460    # store db handle now that we're connected
461    $self->{'gs_sql'} = $gs_sql;   
462}
463
464
465# This method also runs on import.pl if gs_sql has a value. But we just want to run it on buildcol
466# Call deinit() not end() because there can be multiple plugin passes:
467# one for doc level and another for section level indexing
468# and deinit() should be called before all passes
469# This way, we can close the SQL database once per buildcol run.
470sub deinit {
471    my ($self) = shift (@_);
472   
473    ##print STDERR "@@@@@@@@@@ GreenstoneSQLPlugin::DEINIT CALLED\n";
474   
475    if($self->{'gs_sql'}) { # only want to work with sql db if buildcol.pl, gs_sql won't have
476    # a value except during buildcol, so when processor =~ m/buildproc$/.
477    $self->{'gs_sql'}->disconnect_from_db()
478        || warn("Unable to disconnect from database " . $self->{'site_name'} . "\n");
479
480    # explicitly delete gs_sql key (setting key to undef has a different meaning from deleting)
481    # so all future use has to make the connection again
482    delete $self->{'gs_sql'};
483    }
484
485    $self->SUPER::deinit(@_);
486}
487
488
489
490
Note: See TracBrowser for help on using the browser.