CAB/Format/SQLite.pm untested

filter: Statementstmt 0.0% 0 / 224 Branchbran 0.0% 0 / 66 Conditioncond 0.0% 0 / 34 MC/DCmcdc 0.0% 0 / 21 Subroutinesub 0.0% 0 / 37 Podpod 0.0% 0 / 31 totaltotal 0.0% 0 / 413 CC 34 SCAR 49.7
CC
34
Cov
0%
parseDocument
462.0
column_info
30.0
fromFile
20.0
CRAP 143.9
1 ## -*- Mode: CPerl -*-
2 ##
3 ## File: DTA::CAB::Format::SQLite.pm
4 ## Author: Bryan Jurish <moocow@cpan.org>
5 ## Description: Datum parser|formatter: SQLite database (for DTA EvalCorpus)
6  
7 package DTA::CAB::Format::SQLite;
8 use DTA::CAB::Format;
9 use DTA::CAB::Datum ':all';
10 use IO::File;
11 use Carp;
12 use strict;
13  
14 ##==============================================================================
15 ## Globals
16 ##==============================================================================
17  
18 our @ISA = qw(DTA::CAB::Format);
19  
20 BEGIN {
21 DTA::CAB::Format->registerFormat(name=>__PACKAGE__, short=>'sqlite', filenameRegex=>qr/\.(?i:sqlite)(?:\:.*)?$/);
22 }
23  
24 ##==============================================================================
25 ## Constructors etc.
26 ##==============================================================================
27  
28 ## $fmt = CLASS_OR_OBJ->new(%args)
29 ## + object structure: assumed HASH
30 ## (
31 ## ##---- Input
32 ## doc => $doc, ##-- buffered input document
33 ## db_user => $user, ##-- db user (required?)
34 ## db_pass => $pass, ##-- db password (required?)
35 ## db_dsn => $dsn, ##-- db dsn (set by fromFile())
36 ## db_opts => \%dbopts, ##-- additional options for DBI->connect() ; default={sqlite_unicode=>1}
37 ## f_which => $f_which, ##-- restriction (see fromFile())
38 ## f_where => $f_where, ##-- target value for restriction (see fromFile())
39 ## limit => $limit, ##-- sql limit clause (default: undef: none)
40 ## keep_history => $bool, ##-- if true, parse history as well as raw data (default: 1)
41 ## keep_null => $bool, ##-- if true, NULL values from db will be kept as undef (default: false)
42 ## keep_eps => $bool, ##-- if true, empty-string values from db will be kept as undef (default: false)
43 ## keep_temp => $bool, ##-- if true, temporary tables will be kept (default: false)
44 ##
45 ## ##---- Output
46 ## #(disabled)
47 ##
48 ## ##---- Common
49 ## dbh => $dbh, ##-- underlying database handle
50 ## raw => $bool, ##-- if false, will call forceDocument() on doc data
51 ##
52 ## ##---- INHERITED from DTA::CAB::Format
53 ## #utf8 => $bool, ##-- always true
54 ## #level => $formatLevel, ##-- 0:compressed, 1:formatted, ...
55 ## #outbuf => $stringBuffer, ##-- buffered output
56 ## )
57 sub new {
58 my $that = shift;
59 return $that->SUPER::new(
60 ##-- Input
61 #doc => undef,
62 db_user=>undef,
63 db_pass=>undef,
64 db_dsn=>undef,
65 db_opts=>{
66 sqlite_unicode=>1,
67 },
68 f_which=>undef,
69 f_where=>undef,
70 limit=>undef,
71 keep_history=>1,
72 keep_null=>0,
73 keep_eps=>0,
74 keep_temp=>0,
75  
76 ##-- Output
77 #level => 0,
78 #outbuf => '',
79  
80 ##-- common
81 #utf8 => 1,
82 #dbh => undef,
83 #raw => 0,
84  
85 ##-- logging
86 trace_level => 'trace',
87 #trace_level => undef,
88  
89 ##-- user args
90 @_
91 );
92 }
93  
94 ##==============================================================================
95 ## Methods: db stuff
96 ## + mostly lifted from DbCgi.pm (svn+ssh://odo.dwds.de/home/svn/dev/dbcgi/trunk/DbCgi.pm @ 7672)
97 ##==============================================================================
98 our $DBI_INITIALIZED = 0; ##-- package-global sentinel: have we loaded DBI ?
99  
100 ## $class_or_object = $class_or_object->dbi_init();
101 sub dbi_init {
102 return 1 if ($DBI_INITIALIZED);
103 eval 'use DBI;';
104 $_[0]->logconfess("could not 'use DBI': $@") if ($@);
105 return $_[0];
106 }
107  
108  
109 ## $dbh = $fmt->dbh()
110 ## + returns database handle; implicitly calls $fmt->dbconnect() if not already connected
111 sub dbh {
112 my $fmt = shift;
113 return $fmt->{dbh} if (defined($fmt->{dbh}));
114 return $fmt->dbconnect();
115 }
116  
117 ## $fmt = $fmt->dbconnect()
118 ## + (re-)connect to database; sets $fmt->{dbh}
119 sub dbconnect {
120 my $fmt = shift;
121 #print STDERR __PACKAGE__, "::dbconnect(): dsn=$fmt->{db_dsn}; CWD=", getcwd(), "\n";
122 $fmt->dbi_init();
123 my $dbh = $fmt->{dbh} = DBI->connect(@$fmt{qw(db_dsn db_user db_pass)}, {AutoCommit=>1,RaiseError=>1, %{$fmt->{db_opts}||{}}})
124 or $fmt->logconfess("dbconnect(): could not connect to $fmt->{db_dsn}: $!");
125 return $fmt;
126 }
127  
128 ## $fmt = $fmt->dbdisconnect
129 ## + disconnect from database and deletes $fmt->{dbh}
130 sub dbdisconnect {
131 my $fmt = shift;
132 $fmt->{dbh}->disconnect if (UNIVERSAL::can($fmt->{dbh},'disconnect'));
133 delete $fmt->{dbh};
134 return $fmt;
135 }
136  
137 ## $sth = $fmt->execsql($sqlstr)
138 ## $sth = $fmt->execsql($sqlstr,\@params)
139 ## + executes sql with optional bind-paramaters \@params
140 sub execsql {
141 my ($fmt,$sql,$params) = @_;
142 $fmt->vlog($fmt->{trace_level}, "execsql(): $sql\n");
143  
144 my $sth = $fmt->dbh->prepare($sql)
145 or $fmt->logconfess("execsql(): prepare() failed for {$sql}: ", $fmt->dbh->errstr);
146 my $rv = $sth->execute($params ? @$params : qw())
147 or $fmt->logconfess("execsql(): execute() failed for {$sql}: ", $sth->errstr);
148 return $sth;
149 }
150  
151 ## \%name2info = $fmt->column_info( $table)
152 ## \%name2info = $fmt->column_info( $schema, $table)
153 ## \%name2info = $fmt->column_info($catalog, $schema, $table)
154 ## + get column information for table as hashref over COLUMN_NAME; see DBI::column_info()
155 sub column_info {
156 my $fmt = shift;
157 my ($sth);
158 if (@_ >= 3) { $sth=$fmt->dbh->column_info(@_[0..2],undef); }
159 elsif (@_ >= 2) { $sth=$fmt->dbh->column_info(undef,@_[0,1],undef); }
160 else {
161 confess(__PACKAGE__, "::column_info(): no table specified!") if (!$_[0]);
162 $sth=$fmt->dbh->column_info(undef,undef,$_[0],undef);
163 }
164 die(__PACKAGE__, "::column_info(): DBI returned NULL statement handle") if (!$sth);
165 return $sth->fetchall_hashref('COLUMN_NAME');
166 }
167  
168  
169 ## @colnames = $fmt->columns( $table)
170 ## @colnames = $fmt->columns( $schema, $table)
171 ## @colnames = $fmt->columns($catalog, $schema, $table)
172 ## + get column names for $catalog.$schema.$table in db-storage order
173 sub columns {
174 my $fmt = shift;
175 return map {$_->{COLUMN_NAME}} sort {$a->{ORDINAL_POSITION}<=>$b->{ORDINAL_POSITION}} values %{$fmt->column_info(@_)};
176 }
177  
178 ##======================================================================
179 ## DB Stuff: Data Retrieval
180  
181  
182 ## $row_arrayref = $fmt->fetch1row_arrayref($sql)
183 ## $row_arrayref = $fmt->fetch1row_arrayref($sql,\@params)
184 ## + get a single row from the database as an ARRAY-ref
185 *fetch1row = \&fetch1row_arrayref;
186 sub fetch1row_arrayref {
187 my $fmt = shift;
188 my $sth = $fmt->execsql(@_);
189 return $sth->fetchrow_arrayref();
190 }
191  
192 ## @row_array = $fmt->fetch1row_array($sql)
193 ## @row_array = $fmt->fetch1row_array($sql,\@params)
194 ## + get a single row from the database as an array
195 sub fetch1row_array {
196 my $fmt = shift;
197 my $row = $fmt->fetch1row_arrayref(@_);
198 return defined($row) ? @$row : qw();
199 }
200  
201 ## $row_hashref = $fmt->fetch1row_hashref($sql,\@params)
202 ## + get a single row from the database as a hash-ref
203 sub fetch1row_hashref {
204 my $fmt = shift;
205 my $sth = $fmt->execsql(@_);
206 return $sth->fetchrow_hashref();
207 }
208  
209 ## $rows_arrayref_of_arrayrefs = $fmt->fetchall_arrayref($sql)
210 ## $rows_arrayref_of_arrayrefs = $fmt->fetchall_arrayref($sql,\@params)
211 ## + get all rows from the database as an ARRAY-ref
212 *fetchall = \&fetchall_arrayref;
213 sub fetchall_arrayref {
214 my $fmt = shift;
215 my $sth = $fmt->execsql(@_);
216 return $sth->fetchall_arrayref();
217 }
218  
219 ## $rows_arrayref_of_hashrefs = $fmt->fetchall_hashrows($sql,\@params)
220 ## + get all rows from the database as an array-ref of hash-refs
221 sub fetchall_hashrows {
222 my $fmt = shift;
223 my $sth = $fmt->execsql(@_);
224 my $acols = $sth->{NAME};
225 my $arows = $sth->fetchall_arrayref();
226 return $fmt->hashrows($acols,$arows);
227 }
228  
229 ## $hash_of_hashres = $fmt->fetchall_hh($sql,$keyfield,\@params)
230 ## + get all rows from the database as a hash-ref of hash-refs
231 sub fetchall_hh {
232 my ($fmt,$sql,$key) = splice(@_,0,3);
233 my $hrows = $fmt->fetchall_hashrows($sql,@_);
234 return { map {(($_->{$key}||'')=>$_)} @$hrows };
235 }
236  
237 ## \@hashrows = $fmt->hashrows(\@array_row_colnames,\@array_rows)
238 ## + returns ARRAY-ref of HASH-refs for each row of @array_rows rows, keyed by \@array_row_colnames
239 ## + respects $fmt->{keep_null}
240 sub hashrows {
241 my ($fmt,$anames,$arows) = @_;
242 my $hrows = [];
243 my ($arow);
244 no warnings 'uninitialized';
245 foreach $arow (@$arows) {
246 push(@$hrows,{map {($anames->[$_]=>$arow->[$_])}
247 grep {
248 (($fmt->{keep_null} || defined($arow->[$_]))
249 &&
250 ($fmt->{keep_eps} || ($arow->[$_] ne '')))
251 } (0..$#$anames)});
252 }
253 return $hrows;
254 }
255  
256  
257 ##==============================================================================
258 ## Methods: Persistence
259 ##==============================================================================
260  
261 ## @keys = $class_or_obj->noSaveKeys()
262 ## + returns list of keys not to be saved
263 sub noSaveKeys {
264 return ($_[0]->SUPER::noSaveKeys, qw(doc dbh));
265 }
266  
267 ##==============================================================================
268 ## Methods: I/O: generic
269 ##==============================================================================
270  
271 ## @layers = $fmt->iolayers()
272 ## + override returns only ':raw'
273 sub iolayers {
274 return qw(:raw);
275 }
276  
277 ##==============================================================================
278 ## Methods: Input
279 ##==============================================================================
280  
281 ##--------------------------------------------------------------
282 ## Methods: Input: Input selection
283  
284 ## $fmt = $fmt->close($savetmp)
285 ## + close current input source, if any
286 ## + default calls $fmt->{tmpfh}->close() if available and $savetmp is false (default)
287 ## + always deletes $fmt->{fh} and $fmt->{doc}
288 sub close {
289 my $fmt = shift;
290 $fmt->dbdisconnect();
291 return $fmt->SUPER::close();
292 }
293  
294 ## $fmt = $fmt->fromFh($fh)
295 ## + override calls $fmt->fromFh_str
296 sub fromFh {
297 $_[0]->logconfess("fromFh() not supported");
298 }
299  
300 ## $fmt = $fmt->fromString(\$string)
301 sub fromString {
302 $_[0]->logconfess("fromString() not supported");
303 }
304  
305 ## $fmt = $fmt->fromFile($filename)
306 ## + input from an sqlite db file
307 ## + sets $fmt->{db_dsn} and calls $fmt->dbconnect();
308 ## + attempts to parse "$filename" into as "FILE:WHICH=WHERE"
309 ## where "WHICH=WHERE" may be one of:
310 ## all=ALL ##-- full corpus
311 ## doc=DOCID
312 ## dtadir=DOC_DTADIR
313 ## dir=DOC_DTADIR ##-- alias for 'dtadir'
314 ## base=DOC_DTADIR ##-- alias for 'dtadir'
315 ## s=SQL_SENT_QUERY
316 ## w=SQL_TOKEN_QUERY
317 sub fromFile {
318 my ($fmt,$filespec) = @_;
319 $fmt->vlog($fmt->{trace_level}, "filespec=$filespec");
320 $fmt->close();
321 if ($filespec =~ /^([^\:]*)\:([^\=]*)(?:\=(.*))?$/) {
322 @$fmt{qw(file f_which f_where)} = ($1,$2,$3);
323 } else {
324 @$fmt{qw(file f_which f_where)} = ($filespec,'all','1');
325 }
326 $fmt->{db_dsn} = "dbi:SQLite:dbname=$fmt->{file}";
327 $fmt->vlog($fmt->{trace_level}, "db_dsn=$fmt->{db_dsn}");
328 $fmt->vlog($fmt->{trace_level}, "f_which=".(defined($fmt->{f_which}) ? $fmt->{f_which} : '-undef-'));
329 $fmt->vlog($fmt->{trace_level}, "f_where=".(defined($fmt->{f_where}) ? $fmt->{f_where} : '-undef-'));
330 return $fmt->dbconnect();
331 }
332  
333 ##--------------------------------------------------------------
334 ## Methods: Input: Local
335  
336 ## $doc = $fmt->parseDocument()
337 sub parseDocument {
338 my $fmt = shift;
339  
340 ##-- preparations
341 my $dbh = $fmt->dbh() or $fmt->logconfess("no database handle!");
342  
343 ##-- get restrictions -- build @$drows,@$srows,@$wrows, @$shrows,@$whrows
344 my $f_which = $fmt->{f_which} || 'all';
345 my $tmp = $fmt->{keep_temp} ? 'tmp' : "tmp$$";
346 my $tmpkw = $fmt->{keep_temp} ? '' : 'temporary';
347 my $whorder = 'order by rdate desc';
348 my $shorder = 'order by srdate desc';
349 my ($drows,$srows,$wrows, $shrows,$whrows);
350 if (!$f_which || $f_which eq 'all') {
351 $drows = $fmt->fetchall_hashrows("select * from doc;");
352 $srows = $fmt->fetchall_hashrows("select * from sent;");
353 $wrows = $fmt->fetchall_hashrows("select * from ptoken;");
354 if ($fmt->{keep_history}) {
355 $shrows = $fmt->fetchall_hashrows("select * from sent_history $shorder;");
356 $whrows = $fmt->fetchall_hashrows("select * from ptoken_history $whorder;");
357 }
358 }
359 elsif ($f_which =~ m/^(?:all|doc|dtadir|dir|base)$/) {
360 ##-- restrict: doc
361 if ($f_which eq 'doc') {
362 ##-- restrict: doc: by sql id
363 $drows = $fmt->fetchall_hashrows("select * from doc where doc=?",[$fmt->{f_where}]);
364 } else {
365 ##-- restrict: doc: by dtadir
366 $drows = $fmt->fetchall_hashrows("select * from doc where dtadir=?",[$fmt->{f_where}]);
367 }
368 $fmt->logconfess("no document found for $fmt->{f_which}=$fmt->{f_where}") if (!@$drows);
369  
370 $srows = $fmt->fetchall_hashrows("select * from sent where doc=?;",[$drows->[0]{doc}]);
371 $wrows = $fmt->fetchall_hashrows("select * from ptoken where doc=?;",[$drows->[0]{doc}]);
372  
373 if ($fmt->{keep_history}) {
374 $shrows = $fmt->fetchall_hashrows("select * from sent_history where sent in (select sent from sent where doc=?) $shorder;", [$drows->[0]{doc}]);
375 $whrows = $fmt->fetchall_hashrows("select * from ptoken_history where token in (select token from token where doc=?) $whorder;", [$drows->[0]{doc}]);
376 }
377 }
378 elsif ($f_which eq 's') {
379 ##-- restrict: sentence query
380 $fmt->execsql("drop table if exists s_$tmp;");
381 $fmt->execsql("create $tmpkw table s_$tmp (sent integer not null primary key);");
382 $fmt->execsql("insert or ignore into s_$tmp $fmt->{f_where};");
383 $drows = $fmt->fetchall_hashrows("select * from doc where doc in (select doc from s_$tmp natural join sent);");
384 $srows = $fmt->fetchall_hashrows("select * from s_$tmp natural join sent;");
385 $wrows = $fmt->fetchall_hashrows("select * from s_$tmp natural join ptoken;");
386  
387 if ($fmt->{keep_history}) {
388 $shrows = $fmt->fetchall_hashrows("select * from sent_history where sent in (select sent from s_$tmp) $shorder;");
389 $whrows = $fmt->fetchall_hashrows("select * from ptoken_history where token in (select token from s_$tmp natural join token) $whorder;");
390 }
391 }
392 elsif ($f_which eq 'w') {
393 ##-- restrict: token query
394 $fmt->execsql("drop table if exists w_$tmp;");
395 $fmt->execsql("create $tmpkw table w_$tmp (token integer not null primary key);"); ##-- matched tokens only
396 $fmt->execsql("insert or ignore into w_$tmp $fmt->{f_where};");
397 $fmt->execsql("drop table if exists s_$tmp;");
398 $fmt->execsql("create $tmpkw table s_$tmp (sent integer not null primary key);");
399 $fmt->execsql("insert or ignore into s_$tmp select sent from w_$tmp natural join token;");
400 $drows = $fmt->fetchall_hashrows("select * from doc where doc in (select doc from s_$tmp natural join sent);");
401 $srows = $fmt->fetchall_hashrows("select * from s_$tmp natural join sent;");
402 $wrows = $fmt->fetchall_hashrows("select *,(select 1 from w_$tmp wt where wt.token=t.token limit 1) as match from s_$tmp natural join ptoken t;");
403  
404 if ($fmt->{keep_history}) {
405 $shrows = $fmt->fetchall_hashrows("select * from sent_history where sent in (select sent from s_$tmp) $shorder;");
406 $whrows = $fmt->fetchall_hashrows("select * from ptoken_history where token in (select token from s_$tmp natural join token) $whorder;");
407 }
408 }
409 else {
410 $fmt->logconfess("bad restriction \`".($fmt->{f_which}||'')."=".($fmt->{f_where}||'')."'");
411 }
412  
413 ##-- hash result arrays by id
414 my $id2doc = { map {($_->{doc}=>$_)} @$drows };
415 my $id2sent = { map {($_->{sent}=>$_)} @$srows };
416  
417 ##-- parse //s into //doc/body
418 my ($s,$w);
419 foreach $s (@$srows) {
420 push(@{$id2doc->{$s->{doc}}{body}},$s);
421 }
422  
423 ##-- parse //w into //s/tokens
424 foreach $w (@$wrows) {
425 $w->{text} = $w->{wold};
426 $w->{toka} = [split('',$w->{toka})] if ($w->{toka});
427 push(@{$id2sent->{$w->{sent}}{tokens}},$w);
428 }
429  
430 ##-- parse history
431 if ($fmt->{keep_history}) {
432 my ($id2wh,$id2sh) = ({},{});
433 push(@{$id2wh->{$_->{token}}},$_) foreach (@$whrows);
434 push(@{$id2sh->{$_->{sent}}},$_) foreach (@$shrows);
435  
436 my ($h);
437 foreach $w (@$wrows) {
438 $w->{history} = $h if (defined($h=$id2wh->{$w->{token}}));
439 }
440 foreach $s (@$srows) {
441 $s->{shistory} = $h if (defined($h=$id2sh->{$s->{sent}}));
442 }
443 }
444  
445 ##-- build final cab doc structure
446 my ($doc);
447 if (scalar(@$drows)==1) {
448 ##-- single source doc: we've already built it
449 $doc = $drows->[0];
450 $doc->{base} = $doc->{dtadir};
451 } else {
452 ##-- multiple source docs: splice doc attributes into sentences
453 foreach my $drow (@$drows) {
454 foreach $s (@{$drow->{body}}) {
455 $s->{$_}=$drow->{$_} foreach (grep {$_ ne 'body'} keys %$drow);
456 }
457 }
458 $doc = { body=>[map {@{$_->{body}}} @$drows], };
459 }
460  
461 ##-- cleanup & return
462 if (!$fmt->{keep_temp}) {
463 $fmt->execsql("drop table if exists s_$tmp;");
464 $fmt->execsql("drop table if exists w_$tmp;");
465 }
466  
467 $doc = {body=>[]} if (!defined($doc));
468 return $fmt->{raw} ? $doc : $fmt->forceDocument($doc);
469 }
470  
471 ##==============================================================================
472 ## Methods: Output
473 ##==============================================================================
474  
475 ##--------------------------------------------------------------
476 ## Methods: Output: Generic
477  
478 ## $type = $fmt->mimeType()
479 ## + override
480 sub mimeType { return 'application/sqlite'; }
481  
482 ## $ext = $fmt->defaultExtension()
483 ## + returns default filename extension for this format
484 sub defaultExtension { return '.sqlite'; }
485  
486 ## $short = $fmt->formatName()
487 ## + returns "official" short name for this format
488 ## + default just returns package suffix
489 sub shortName {
490 return 'sqlite';
491 }
492  
493  
494 ##--------------------------------------------------------------
495 ## Methods: Output: output selection
496  
497 ## $fmt_or_undef = $fmt->toFh($fh,$formatLevel)
498 ## + select output to filehandle $fh
499 sub toFh {
500 $_[0]->logconfess("toFh() not supported");
501 }
502  
503 ## $fmt_or_undef = $fmt->toFile($filename)
504 sub toFile {
505 $_[0]->logconfess("toFile() not supported");
506 }
507  
508 ## $fmt_or_undef = $fmt->toString(\$str)
509 sub toString {
510 $_[0]->logconfess("toString() not supported");
511 }
512  
513  
514 ##--------------------------------------------------------------
515 ## Methods: Output: Generic API
516 ## + these methods just dump raw json
517 ## + you're pretty much restricted to dumping a single document here
518  
519 ## $fmt = $fmt->putAnything($thingy)
520 ## + just pukes
521 sub putAnything {
522 $_[0]->logconfess("putXYZ() not supported");
523 }
524  
525 ## $fmt = $fmt->putToken($tok)
526 ## $fmt = $fmt->putSentence($sent)
527 ## $fmt = $fmt->putDocument($doc)
528 ## $fmt = $fmt->putData($data)
529 BEGIN {
530 *putToken = \&putRef;
531 *putSentence = \&putRef;
532 *putDocument = \&putRef;
533 *putData = \&putRef;
534 }
535  
536 1; ##-- be happy
537  
538 __END__