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

Revision 32560, 18.6 KB (checked in by ak19, 9 months ago)

gssql constructor accepts a verbosity parameter

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# - "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?
43# Test doc with meta and text like macron in Maori text.
44# - Have not yet tested writing out just meta or just fulltxt to sql db and reading just that
45# back in from the sql db while the remainder is to be read back in from the docsql .xml files.
46
47# TODO: deal with incremental vs removeold. If docs removed from import folder, then import step
48# won't delete it from archives but buildcol step will. Need to implement this with this database plugin or wherever the actual flow is
49
50# 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.
51# Discuss the plugin/plugout parameters.
52
53# + NOTTODO: when db is not running GLI is paralyzed -> can we set timeout on DBI connection attempt?
54# NOT A PROBLEM: Tested to find DBI connection attempt fails immediately when MySQL server not
55# running. The GLI "paralyzing" incident last time was not because of the gs sql connection code,
56# but because my computer was freezing on-and-off.
57
58# TODO Q: is "reindex" = del from db + add to db?
59# - is this okay for reindexing, or will it need to modify existing values (update table)
60# - if it's okay, what does reindex need to accomplish (and how) if the OID changes because hash id produced is different?
61# - delete is accomplished in GS SQL Plugin, during buildcol.pl. When should reindexing take place?
62# during SQL plugout/import.pl or during plugin? If adding is done by GSSQLPlugout, does it need to
63# be reimplemented in GSSQLPlugin to support the adding portion of reindexing.
64
65
66# TODO Q: During import, the GS SQL Plugin is called before the GS SQL Plugout with undesirable side
67# effect that if the db doesn't exist, gssql::use_db() fails, as it won't create db.
68
69
70# + TODO: Incremental delete can't work until GSSQLPlugout has implemented build_mode = incremental
71# (instead of tossing away db on every build)
72# + Ask about docsql naming convention adopted to identify OID. Better way?
73# collection names -> table names: it seems hyphens not allowed. Changed to underscores.
74# + Startup parameters (except removeold/build_mode)
75# + how do we detect we're to do removeold during plugout in import.pl phase
76# + incremental building: where do we need to add code to delete rows from our sql table after
77# incrementally importing a coll with fewer docs (for instance)? What about deleted/modified meta?
78# + Ask if I can assume that all SQL dbs (not just MySQL) will preserve the order of inserted nodes
79# (sections) which in this case had made it easy to reconstruct the doc_obj in memory in the correct order.
80# YES: Otherwise for later db types (drivers), can set order by primary key column and then order by did column
81
82
83########################################################################################
84
85# GreenstoneSQLPlugin inherits from GreenstoneXMLPlugin so that it if meta or fulltext
86# is still written out to doc.xml (docsql .xml), that will be processed as usual,
87# whereas GreenstoneSQLPlugin will process all the rest (full text and/or meta, whichever
88# is written out by GreenstoneSQLPlugout into the SQL db).
89
90
91sub BEGIN {
92    @GreenstoneSQLPlugin::ISA = ('GreenstoneXMLPlugin');
93}
94
95# This plugin must be in the document plugins pipeline IN PLACE OF GreenstoneXMLPlugin
96# So we won't have a process exp conflict here.
97# The structure of docsql.xml files is identical to doc.xml and the contents are similar except:
98#   - since metadata and/or fulltxt are stored in mysql db instead, just XML comments indicating
99#   this are left inside docsql.xml within the <Description> (for meta) and/or <Content> (for txt)
100#   - the root element Archive now has a docoid attribute: <Archive docoid="OID">
101sub get_default_process_exp {
102    my $self = shift (@_);
103
104    return q^(?i)docsql(-\d+)?\.xml$^; # regex based on this method in GreenstoneXMLPlugin
105    #return q^(?i)docsql(-.+)?\.xml$^; # no longer storing the OID embedded in docsql .xml filename
106}
107
108my $process_mode_list =
109    [ { 'name' => "meta_only",
110        'desc' => "{GreenstoneSQLPlug.process_mode.meta_only}" },     
111      { 'name' => "text_only",
112        'desc' => "{GreenstoneSQLPlug.process_mode.text_only}" },
113      { 'name' => "all",
114        'desc' => "{GreenstoneSQLPlug.process_mode.all}" } ];
115
116my $arguments =
117    [ { 'name' => "process_exp",
118    'desc' => "{BaseImporter.process_exp}",
119    'type' => "regexp",
120    'deft' => &get_default_process_exp(),
121    'reqd' => "no" },
122      { 'name' => "process_mode",
123    'desc' => "{GreenstoneSQLPlug.process_mode}",
124    'type' => "enum",
125    'list' => $process_mode_list,
126    'deft' => "all",
127    'reqd' => "no"},
128      { 'name' => "db_driver",
129    'desc' => "{GreenstoneSQLPlug.db_driver}",
130    'type' => "string",
131    'deft' => "mysql",
132    'reqd' => "yes"},
133      { 'name' => "db_client_user",
134    'desc' => "{GreenstoneSQLPlug.db_client_user}",
135    'type' => "string",
136    'deft' => "root",
137    'reqd' => "yes"},
138      { 'name' => "db_client_pwd",
139    'desc' => "{GreenstoneSQLPlug.db_client_pwd}",
140    'type' => "string",
141    'deft' => "",
142    'reqd' => "yes"}, # pwd required?
143      { 'name' => "db_host",
144    'desc' => "{GreenstoneSQLPlug.db_host}",
145    'type' => "string",
146    'deft' => "127.0.0.1",
147    'reqd' => "yes"},
148    ];
149
150my $options = { 'name'     => "GreenstoneSQLPlugin",
151        'desc'     => "{GreenstoneSQLPlugin.desc}",
152        'abstract' => "no",
153        'inherits' => "yes",
154            'args'     => $arguments };
155
156
157# TODO: For on cancel, add a SIGTERM handler or so to call end()
158# or to explicitly call gs_sql->close_connection if $gs_sql def
159
160sub new {
161    my ($class) = shift (@_);
162    my ($pluginlist,$inputargs,$hashArgOptLists) = @_;
163    push(@$pluginlist, $class);
164
165    push(@{$hashArgOptLists->{"ArgList"}},@{$arguments});
166    push(@{$hashArgOptLists->{"OptList"}},$options);
167
168    my $self = new GreenstoneXMLPlugin($pluginlist, $inputargs, $hashArgOptLists);
169
170   
171    #return bless $self, $class;
172    $self = bless $self, $class;
173    if ($self->{'info_only'}) {
174    # If running pluginfo, we don't need to go further.
175    return $self;
176    }
177
178    # do anything else that needs to be done here when not pluginfo
179    #$self->{'delete_docids'} = (); # list of doc oids to delete during deinit()
180   
181    return $self;
182}
183
184sub xml_start_tag {
185    my $self = shift(@_);
186    my ($expat, $element) = @_;
187
188    my $outhandle = $self->{'outhandle'};
189   
190    $self->{'element'} = $element;
191    if ($element eq "Archive") { # docsql.xml files contain a OID attribute on Archive element
192    # the element's attributes are in %_ as per ReadXMLFile::xml_start_tag() (while $_
193    # contains the tag)
194
195    # Don't access %_{'docoid'} directly: keep getting a warning message to
196    # use $_{'docoid'} for scalar contexts, but %_ is the element's attr hashmap
197    # whereas $_ has the tag info. So we don't want to do $_{'docoid'}.
198    my %attr_hash = %_; # right way, see OAIPlugin.pm
199    $self->{'doc_oid'} = $attr_hash{'docoid'};
200    print STDERR "XXXXXXXXXXXXXX in SQLPlugin::xml_start_tag()\n";
201    print $outhandle "Extracted OID from docsql.xml: ".$self->{'doc_oid'}."\n"
202        if $self->{'verbosity'} > 2;
203
204    }
205    else { # let superclass GreenstoneXMLPlugin continue to process <Section> and <Metadata> elements
206    $self->SUPER::xml_start_tag(@_);
207    }
208}
209
210# 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?
211
212# At the end of superclass GreenstoneXMLPlugin.pm's close_document() method,
213# the doc_obj in memory is processed (indexed) and then made undef.
214# So we have to work with doc_obj before superclass close_document() is finished.
215sub close_document {
216    my $self = shift(@_);
217
218    print STDERR "XXXXXXXXX in SQLPlugin::close_doc()\n";
219   
220    my $gs_sql = $self->get_gssql_instance();
221   
222    my $outhandle = $self->{'outhandle'};
223    my $doc_obj = $self->{'doc_obj'};
224
225    my $build_proc_mode = $self->{'processor'}->get_mode(); # can be "text" as per basebuildproc or infodb
226    my $oid = $self->{'doc_oid'}; # we stored current doc's OID during sub xml_start_tag()
227    my $proc_mode = $self->{'process_mode'};
228   
229    print $outhandle "++++ OID of document (meta|text) to be del or read in from DB: ".$self->{'doc_oid'}."\n"
230    if $self->{'verbosity'} > 2;
231   
232    # For now, we have access to doc_obj (until just before super::close_document() terminates)
233   
234    # no need to call $self->{'doc_obj'}->set_OID($oid);
235    # because either the OID is stored in the SQL db as meta 'Identifier' alongside other metadata
236    # or it's stored in the doc.xml as metadata 'Identifier' alongside other metadata
237    # Either way, Identifier meta will be read into the docobj automatically with other meta.
238   
239    if ($self->{'verbosity'} > 2) {
240    print STDERR "+++++++++++ buildproc_mode: $build_proc_mode\n";
241    print STDERR "+++++++++++ SQLPlug proc_mode: $proc_mode\n";
242    }
243
244    # TODO: where does reindexing take place, GreenstoneSQL -Plugout or -Plugin?
245    #if($build_proc_mode =~ m/(delete|reindex)$/) { # doc denoted by current OID has been marked for deletion or reindexing (=delete + add)
246    if($build_proc_mode =~ m/(delete)$/) { # doc denoted by current OID has been marked for deletion or reindexing (=delete + add)
247   
248          # build_proc_mode could be "(infodb|text)(delete|reindex)"
249          # "...delete" or "...reindex" as per ArchivesInfPlugin
250   
251    print STDERR "@@@@ DELETING DOC FROM SQL DB\n";
252   
253    if($proc_mode eq "all" || $proc_mode eq "meta_only") {
254        print STDERR "@@@@@@@@ Deleting $oid from meta table\n" if $self->{'verbosity'} > 2;
255        $gs_sql->delete_recs_from_metatable_with_docid($oid);
256    }
257    if($proc_mode eq "all" || $proc_mode eq "text_only") {
258        print STDERR "@@@@@@@@ Deleting $oid from fulltxt table\n" if $self->{'verbosity'} > 2;
259        $gs_sql->delete_recs_from_texttable_with_docid($oid);
260    }
261   
262    # If we're reindexing the current doc, we will we want to continue: which
263    # will add this doc ID back into the db with the new meta/full txt values
264    # But if we're deleting, then we're done processing the document, so set doc_oid to undef
265    # to prevent adding it back into db
266    #undef $self->{'doc_oid'} if($build_proc_mode =~ m/delete$/);   
267   
268    } # done deleting doc from SQL db   
269   
270    else {#if($self->{'doc_oid'}) { # if loading doc from SQL db
271    print STDERR "@@@@ LOADING DOC FROM SQL DB\n"; 
272   
273    if($proc_mode eq "all" || $proc_mode eq "meta_only") {
274        # read in meta for the collection (i.e. select * from <col>_metadata table
275       
276        my $sth = $gs_sql->select_from_metatable_matching_docid($oid);
277        print $outhandle "### SQL select stmt: ".$sth->{'Statement'}."\n"
278        if $self->{'verbosity'} > 2;
279       
280        print $outhandle "----------SQL DB contains meta-----------\n" if $self->{'verbosity'} > 2;
281        # https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/
282        while( my @row = $sth->fetchrow_array() ) {     
283        #print $outhandle "row: @row\n";
284        my ($primary_key, $did, $sid, $metaname, $metaval) = @row;
285       
286        # get rid of the artificial "root" introduced in section id when saving to sql db
287        $sid =~ s@^root@@;
288        $sid = $doc_obj->get_top_section() unless $sid;
289        print $outhandle "### did: $did, sid: |$sid|, meta: $metaname, val: $metaval\n"
290            if $self->{'verbosity'} > 2;
291       
292        # TODO:  we accessed the db in utf8 mode, so, we can call doc_obj->add_utf8_meta directly:
293        $doc_obj->add_utf8_metadata($sid, $metaname, &docprint::unescape_text($metaval));
294        }
295        print $outhandle "----------FIN READING DOC's META FROM SQL DB------------\n"
296        if $self->{'verbosity'} > 2;
297    }
298   
299    if($proc_mode eq "all" || $proc_mode eq "text_only") {
300        # read in fulltxt for the collection (i.e. select * from <col>_fulltxt table
301       
302        my $fulltxt_table = $gs_sql->get_fulltext_table_name();
303       
304   
305        my $sth = $gs_sql->select_from_texttable_matching_docid($oid);
306        print $outhandle "### stmt: ".$sth->{'Statement'}."\n" if $self->{'verbosity'} > 2;
307       
308        print $outhandle "----------\nSQL DB contains txt entries for-----------\n"
309        if $self->{'verbosity'} > 2;
310        while( my ($primary_key, $did, $sid, $text) = $sth->fetchrow_array() ) {       
311       
312        # get rid of the artificial "root" introduced in section id when saving to sql db
313        #$sid =~ s@^root@@;
314        $sid = $doc_obj->get_top_section() if ($sid eq "root");
315        print $outhandle "### did: $did, sid: |$sid|, fulltext: <TXT>\n"
316            if $self->{'verbosity'} > 2;
317       
318        # TODO - pass by ref?
319        # TODO: we accessed the db in utf8 mode, so, we can call doc_obj->add_utf8_text directly:
320        $doc_obj->add_utf8_text($sid, &docprint::unescape_text($text));
321        }   
322        print $outhandle "----------FIN READING DOC's TXT FROM SQL DB------------\n"
323        if $self->{'verbosity'} > 2;
324    }
325   
326    } # done reading into docobj from SQL db
327   
328    # don't forget to clean up on close() in superclass
329    # It will get the doc_obj indexed then make it undef
330    $self->SUPER::close_document(@_);
331}
332
333
334# We want SQLPlugin to connect to db only during buildcol.pl phase, not during import.pl
335# This works out okay, as close_document() (called by read()) is only invoked during buildcol.pl
336#
337# Further, we want a single db connection for the GS SQL Plugin to be used for
338# the multiple plugin passes: for "dummy" pass, and for doc level and for section level indexing
339# By calling the lazy loading get_sql_instance() from close_document(),
340# we connect to the SQL database once per GSSQLPlugin and only during the buildcol phase.
341#
342# get_gssql_instance() is a lazy loading method that returns singleton db connection for a GreenstoneSQLPlugin object. ("Code pattern" get instance vs singleton.)
343# One instance of db connection that can be used for all the many doc_objects processed by this plugin
344#
345# Except in methods get_gssql_instance() and deinit(), don't access self->{'_gs_sql'} directly.
346# Instead, call method get_gssql_instance() and store return value in a local variable, my $gs_sql
347#
348sub get_gssql_instance
349{   
350    my $self = shift(@_);
351
352    # if we failed to successfully connect once before, don't bother attempting to connect again
353    #return undef if(defined $self->{'failed'}); # plugin/process would have terminated with die()
354                                  # if we couldn't succeed connecting on any connection attempt
355   
356    return $self->{'_gs_sql'} if($self->{'_gs_sql'});
357
358    # assume we'll fail to connect
359    $self->{'failed'} = 1;
360
361    print STDERR "@@@@@@@@@@ LAZY CONNECT CALLED\n";
362   
363    ####################
364#    print "@@@ SITE NAME: ". $self->{'site_name'} . "\n" if defined $self->{'site_name'};
365#    print "@@@ COLL NAME: ". $ENV{'GSDLCOLLECTION'} . "\n";
366
367#    print STDERR "@@@@ db_pwd: " . $self->{'db_client_pwd'} . "\n";
368#    print STDERR "@@@@ user: " . $self->{'db_client_user'} . "\n";
369#    print STDERR "@@@@ db_host: " . $self->{'db_host'} . "\n";
370#    print STDERR "@@@@ db_driver: " . $self->{'db_driver'} . "\n";
371    ####################
372
373    # create gssql object.
374    # collection name will be used for naming tables (site name will be used for naming database)
375    my $gs_sql = new gssql({
376    'collection_name' => $ENV{'GSDLCOLLECTION'},
377    'verbosity' => $self->{'verbosity'}
378               });
379
380    # try connecting to the mysql db, if that fails it will die
381    if(!$gs_sql->connect_to_db({
382    'db_driver' => $self->{'db_driver'},
383    'db_client_user' => $self->{'db_client_user'},
384    'db_client_pwd' => $self->{'db_client_pwd'},
385    'db_host' => $self->{'db_host'}
386                   })
387    )
388    {
389    # This is fatal for the plugout, let's terminate here
390    # PrintError would already have displayed the warning message on connection fail   
391    die("Could not connect to db. Can't proceed.\n");
392    }
393   
394    my $db_name = $self->{'site_name'} || "greenstone2"; # one database per GS3 site, for GS2 the db is called greenstone2
395    #my $build_mode = $self->{'build_mode'} || "removeold";
396
397    # the db and its tables should exist. Attempt to use the db:
398    if(!$gs_sql->use_db($db_name)) {
399   
400    # This is fatal for the plugout, let's terminate here after disconnecting again
401    # PrintError would already have displayed the warning message on load fail
402    $gs_sql->disconnect_from_db()
403        || warn("Unable to disconnect from database.\n");
404    die("Could not use db $db_name. Can't proceed.\n");
405    }
406
407    #undef $self->{'failed'};
408   
409    # store db handle now that we're connected
410    $self->{'_gs_sql'} = $gs_sql;
411    return $gs_sql;
412   
413}
414
415# This method also runs on import.pl if gs_sql has a value. But we just want to run it on buildcol
416# Call deinit() not end() because there can be multiple plugin passes:
417# one for doc level and another for section level indexing
418# and deinit() should be called before all passes
419# This way, we can close the SQL database once per buildcol run.
420sub deinit {
421    my ($self) = shift (@_);
422   
423    print STDERR "@@@@@@@@@@ GreenstoneSQLPlugin::DEINIT CALLED\n";
424   
425    if($self->{'_gs_sql'}) { # only want to work with sql db if buildcol.pl, gs_sql won't have
426    # a value except during buildcol, so when processor =~ m/buildproc$/.
427    $self->{'_gs_sql'}->disconnect_from_db()
428        || warn("Unable to disconnect from database " . $self->{'site_name'} . "\n");
429
430    # explicitly set to undef so all future use has to make the connection again
431    undef $self->{'_gs_sql'};
432    }
433
434    $self->SUPER::deinit(@_);
435}
436
437
438
439
Note: See TracBrowser for help on using the browser.