chiark / gitweb /
Do referential integrity check on every incoming update
[ypp-sc-tools.db-live.git] / yarrg / commod-email-processor
index ec4f222..8acf4c1 100755 (executable)
 # are used without permission.  This program is not endorsed or
 # sponsored by Three Rings.
 
-# Emails are:
-#  multipart/mixed, containing
-#   text/plain; name="metadata"; charset="utf-8"
-#   Content-Disposition: inline; filename="metadata"
-#     ocean\t<ocean>           canonical mixed case
-#     island\t<island>         canonical mixed case
-#     timestamp\t<digits>      time_t (non-leap secs since start of 1970 UTC)
-#     clientname\t<cname>      may contain spaces
-#     clientversion\t<cversion>        may contain spaces
-#     clientfixes\t<cfixes>    space-delimited list
-#     clientspec\t<cspec>      <cname>\t<cversion>\t<cfixes>
-#     servername\t<sname>      may contain spaces
-#     serverversion\t<sversion>        may contain spaces
-#     serverfixes\t<sfixes>    space-delimited list
-#     serverspec\t<sspec>      <sname>\t<serverversion>\t<serverfixes>
-#   application/octet-stream; name="deduped.tsv.gz"
-#   Content-Disposition: attachment; filename="deduped.tsv.gz"
-#     <base64>
-
 use strict (qw(vars));
 
 use POSIX;
@@ -58,7 +39,7 @@ BEGIN {
 use Commods;
 use CommodsDatabase;
 
-setlocale(LC_CTYPE, "en_GB.UTF-8");
+set_ctype_utf8();
 my $parser= new MIME::Parser;
 our $entity;
 
@@ -142,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_insert{'commods'}= $dbh->prepare(
+    $sth_lookup{'stall'}= $dbh->prepare(
+                "SELECT stallid FROM stalls
+                       WHERE islandid == $islandid AND stallname == ?
+                ");
+    $sth_insert{'commod'}= $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;
@@ -208,8 +194,8 @@ sub main () {
 
     pipethrough_run_finish($pt, 'gunzip <$deduped_tsv.gz');
 
-    print "\n";
-    $dbh->commit();
+#    print "\n";
+    db_chkcommit();
 
     # select * from ((buy natural join commods) natural join stalls) natural join islands;
     # select * from ((sell natural join commods) natural join stalls) natural join islands;