chiark / gitweb /
Pick correct island's stall record for stallid when inserting (!)
authorIan Jackson <ian@liberator.relativity.greenend.org.uk>
Wed, 19 Aug 2009 22:44:02 +0000 (23:44 +0100)
committerIan Jackson <ian@liberator.relativity.greenend.org.uk>
Wed, 19 Aug 2009 22:44:02 +0000 (23:44 +0100)
yarrg/commod-email-processor

index 0c16cd1..79db83d 100755 (executable)
@@ -123,33 +123,38 @@ sub main () {
             $islandid, $mid,
             map { $md{$_} } (qw(timestamp clientspec serverspec)));
 
-    my (%sth, %sub_cs, %cache_cs, %sth_insert);
+    my (%sth, %sub_cs, %cache_cs, %sth_insert, %sth_lookup);
 
     $sth_insert{'stall'}= $dbh->prepare(
                 "INSERT OR IGNORE
                         INTO stalls
                         (islandid, stallname) VALUES ($islandid, ?)
                 ");
+    $sth_lookup{'stall'}= $dbh->prepare(
+                "SELECT stallid FROM stalls
+                       WHERE islandid == $islandid AND stallname == ?
+                ");
     $sth_insert{'commods'}= $dbh->prepare(
                 "INSERT OR IGNORE
                         INTO commods
                         (commodname) VALUES (?)
                 ");
+    $sth_lookup{'commod'}= $dbh->prepare(
+                "SELECT commodid FROM commods
+                       WHERE commodname == ?
+                ");
 
     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();
+           $sth_lookup{$cs}->execute($name) or die;
+           ($r)= $sth_lookup{$cs}->fetchrow_array();
            if (!defined $r) {
                $sth_insert{$cs}->execute($name);
-               $sth_lookup->execute($name) or die;
-               ($r)= $sth_lookup->fetchrow_array();
+               $sth_lookup{$cs}->execute($name) or die;
+               ($r)= $sth_lookup{$cs}->fetchrow_array();
                die unless defined $r;
            }
            $cache_cs{$cs}{$name}= $r;