X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.db-live.git;a=blobdiff_plain;f=pctb%2Fcommod-email-processor;h=d2f0e819f6ef8c0f41626424fe13beb624d4f088;hp=1205e79a0d2373274e109f9d35773cba3588bf90;hb=b9cce976550d000f15e5a8f2b690740bdae1e468;hpb=8651c66dd983df3a05eff010cef635e641a41d8f

diff --git a/pctb/commod-email-processor b/pctb/commod-email-processor
index 1205e79..d2f0e81 100755
--- a/pctb/commod-email-processor
+++ b/pctb/commod-email-processor
@@ -134,12 +134,39 @@ sub main () {
 	     $islandid, $mid,
 	     map { $md{$_} } (qw(timestamp clientspec serverspec)));
 
-    my $sth_stall= $dbh->prepare(
+    my (%sth, %sub_cs, %cache_cs, %sth_insert);
+
+    $sth_insert{'stall'}= $dbh->prepare(
                 "INSERT OR IGNORE
                         INTO stalls
-                        (islandid, stallname) VALUES (?, ?)
+                        (islandid, stallname) VALUES ($islandid, ?)
+                ");
+    $sth_insert{'commods'}= $dbh->prepare(
+                "INSERT OR IGNORE
+                        INTO commods
+                        (commodname) VALUES (?)
                 ");
 
+    foreach my $cs (qw(stall commod)) {
+	my $sth_lookup= $dbh->prepare(
+                "SELECT ${cs}id FROM ${cs}s WHERE ${cs}name == ?;
+                ");
+	$sub_cs{$cs}= sub {
+	    my ($name)= @_;
+	    my $r= $cache_cs{$cs}{$name};
+	    return $r if defined $r;
+	    $sth_lookup->execute($name) or die;
+	    ($r)= $sth_lookup->fetchrow_array();
+	    if (!defined $r) {
+		$sth_insert{$cs}->execute($name);
+		$sth_lookup->execute($name) or die;
+		($r)= $sth_lookup->fetchrow_array();
+		die unless defined $r;
+	    }
+	    $cache_cs{$cs}{$name}= $r;
+	    return $r;
+	};
+    }
     my @v;
 
     my %sub_bs;
@@ -147,25 +174,23 @@ sub main () {
 	my $sth= $dbh->prepare(
 	       "INSERT INTO $bs
                        (commodid, stallid, islandid, price, qty)
-                       VALUES (
-		         (SELECT commodid FROM commods WHERE commodname = ?),
-		         (SELECT stallid  FROM stalls  WHERE stallname =  ?),
-		         ?, ?, ?
-                       );
+                       VALUES (?,?,?,?,?);
                ");
 	$sub_bs{$bs}= sub {
 	    my ($priceix) = @_;
 	    my $price= $v[$priceix];  return if !length $price;
 	    my $qty= $v[$priceix+1];
 	    $qty++ if $qty =~ s/^\>//;
-	    $sth->execute(@v[0..1],$islandid,$price,$qty);
-	}	    
+	    $sth->execute($sub_cs{'commod'}($v[0]),
+			  $sub_cs{'stall'}($v[1]),
+			  $islandid,$price,$qty);
+	};	    
     }
 
     while (<$pt>) {
 	@v= check_tsv_line($_, \&bad_data_fail);
-
-	$sth_stall->execute($islandid, $v[1]);
+#	chomp;
+#	@v= split /\t/, $_, -1;
 
 	&{$sub_bs{'buy'}}(2);
 	&{$sub_bs{'sell'}}(4);