r1906@llin: dpavlin | 2009-05-30 17:26:17 +0200

 added table name to row normalize command so we can create
 data in different tables directly from normalization file

git-svn-id: svn+ssh://mjesec/home/dpavlin/svn/webpac2/trunk@1210 07558da8-63fa-0310-ba24-9fe276d99e06

diff --git a/conf/isi/cited.sql b/conf/isi/cited.sql
index a3dd9b6..b700927 100644 (file)
--- a/conf/isi/cited.sql
+++ b/conf/isi/cited.sql
@@ -1,9 +1,18 @@
+drop table if exists cited;
+
 create table cited (
        id serial,
-       au text not null,
-       cited text not null
+       cited_au text not null,
+       from_au text not null
+);
+
+drop table if exists authors;
+
+create table authors (
+       id serial,
+       au text not null
 );
 
-create index cited_au on cited(au);
-create index cited_cited on cited(cited);
+-- create index cited_au on cited(au);
+-- create index cited_cited on cited(cited);
 

diff --git a/conf/isi/cu-au-count.pl b/conf/isi/cu-au-count.pl
index 0ffb153..0fa3004 100644 (file)
--- a/conf/isi/cu-au-count.pl
+++ b/conf/isi/cu-au-count.pl
@@ -1,9 +1,14 @@
 
 foreach my $cr ( rec_array 'CR' ) {
+       if ( ! $cr->{author} ) {
+               warn "# cr ",dump( $cr );
+               next;
+       }
        foreach my $au ( rec_array 'AU' ) {
-               row(
-                       cited => $cr->{author},
-                       au => $au,
+               row( 'cited',
+                       cited_au => $cr->{author},
+                       from_au => $au,
                );
+               row( 'authors', au => $au );
        }
 }

diff --git a/lib/WebPAC/Normalize.pm b/lib/WebPAC/Normalize.pm
index 6d11324..49ba7a3 100644 (file)
--- a/lib/WebPAC/Normalize.pm
+++ b/lib/WebPAC/Normalize.pm
@@ -366,9 +366,9 @@ Insert new row of data into output module
 use Data::Dump qw/dump/;
 
 sub row {
-       die "array doesn't have even number of elements but $#_: ",dump( @_ ) if $#_ % 2 != 1;
-
-       push @{ $out->{'_rows'} }, {@_};
+       die "array doesn't have odd number of elements but $#_: ",dump( @_ ) if $#_ % 2 == 1;
+       my $table = shift @_;
+       push @{ $out->{'_rows'}->{$table} }, {@_};
 }
 
 

diff --git a/lib/WebPAC/Output/DBI.pm b/lib/WebPAC/Output/DBI.pm
index 836ef49..d75aff9 100644 (file)
--- a/lib/WebPAC/Output/DBI.pm
+++ b/lib/WebPAC/Output/DBI.pm
@@ -38,7 +38,8 @@ sub init {
 
        $log->info($self->dsn);
 
-       $self->{_rows} = [];
+       $self->{_rows} = {};
+       $self->{_sth} = {};
 
        $self->{_dbh} = DBI->connect( $self->dsn, $self->user, $self->passwd, { RaiseError => 1 } );
 
@@ -69,27 +70,45 @@ sub add {
 
        $id = $self->input . '-' . $id if $self->input;
 
-       my @rows = @{ $ds->{_rows} };
-       foreach my $row ( @rows ) {
+       foreach my $table ( keys %{ $ds->{_rows} } ) {
 
-               my @cols = sort keys %$row;
+               my @rows = @{ $ds->{_rows}->{$table} };
+               foreach my $row ( @rows ) {
 
-               my $sql = join( ''
-                       , 'insert into '
-                       , ( $self->table || $self->input || 'webpac2' )
-                       . ' (' . join(',', @cols), ')'
-                       , ' values ('
-                       , join(',', map { '?' } 0 .. $#cols )
-                       , ')'
-               );
-               warn "# SQL: $sql\n";
-               my $sth = $self->{_dbh}->prepare( $sql );
+                       my @cols = sort keys %$row;
 
-               warn "# row ",dump( $row );
-               $sth->execute( map { $row->{$_} } @cols );
-       }
+                       my $sth_id = $table . ':' . join(',',@cols);
+
+                       my $sth
+                               = $self->{_sth}->{$sth_id}
+                               ;
+                       
+                       if ( ! $sth ) {
+
+                               my $sql = join( ''
+                                       , 'insert into '
+                                       , $table
+                                       . ' (' . join(',', @cols), ')'
+                                       , ' values ('
+                                       , join(',', map { '?' } 0 .. $#cols )
+                                       , ')'
+                               );
+
+                               $log->debug( "SQL $sth_id: $sql" );
 
-       push @{ $self->{_rows} }, $_ foreach @rows;
+                               $sth
+                                       = $self->{_sth}->{$sth_id}
+                                       = $self->{_dbh}->prepare( $sql )
+                                       ;
+                       };
+
+                       $log->debug( "row $table ", sub { dump( $row ) } );
+                       $sth->execute( map { $row->{$_} } @cols );
+       
+                       push @{ $self->{_rows}->{$table} }, $_ foreach @rows;
+
+               }
+       }
 
        return 1;
 }

diff --git a/t/5-output-dbi.t b/t/5-output-dbi.t
index 3aaea66..7c79fb8 100755 (executable)
--- a/t/5-output-dbi.t
+++ b/t/5-output-dbi.t
@@ -19,13 +19,13 @@ ok(my $out = new WebPAC::Output::DBI({
 
 ok( $out->init, 'init' );
 
-my $ds = {
-       '_rows' => [
+my $ds = { '_rows' => {
+       'test' => [
                { foo => 42 },
                { foo => 1, bar => 11 },
                { foo => 99, baz => 'text' },
        ],
-};
+} };
 
 ok( $out->add( 42, $ds ), 'add 42' );
 

diff --git a/t/conf/schema.sql b/t/conf/schema.sql
index e5e9621..7f69f58 100644 (file)
--- a/t/conf/schema.sql
+++ b/t/conf/schema.sql
@@ -1,6 +1,6 @@
--- drop table webpac2;
+drop table if exists test;
 
-create table webpac2 (
+create table test (
        id serial,
        foo int not null,
        bar int,