X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.db-live.git;a=blobdiff_plain;f=yarrg%2FCommodsDatabase.pm;h=999dda779611b441e2131348e4b6efa5acce0228;hp=e14c0eb401e10a0f299b9b2c0564ce69d492266a;hb=8c65bca26f3f2da973f5edf140491d551b50e2ec;hpb=9089113ed14d3a01ebe805ee15bd08705a59f172

diff --git a/yarrg/CommodsDatabase.pm b/yarrg/CommodsDatabase.pm
index e14c0eb..999dda7 100644
--- a/yarrg/CommodsDatabase.pm
+++ b/yarrg/CommodsDatabase.pm
@@ -178,17 +178,23 @@ sub nooutput ($) {
 	if $ekindcount;
 }
 
-sub db_check_referential_integrity () {
+sub db_check_referential_integrity ($) {
+    my ($full) = @_;
+    # non-full is done only for market data updates; it avoids
+    # detecting errors which are essentially missing metadata and
+    # old schemas, etc.
+
     foreach my $bs (qw(buy sell)) {
 	nooutput(<<END);
 
  # Every buy/sell must refer to an entry in commods, islands, and stalls:
- SELECT * FROM $bs NATURAL LEFT JOIN commods WHERE commodname IS NULL;
- SELECT * FROM $bs NATURAL LEFT JOIN islands WHERE islandname IS NULL;
- SELECT * FROM $bs LEFT JOIN STALLS USING (stallid) WHERE stallname IS NULL;
+ SELECT * FROM $bs LEFT JOIN commods USING (commodid) WHERE commodname IS NULL;
+ SELECT * FROM $bs LEFT JOIN islands USING (islandid) WHERE islandname IS NULL;
+ SELECT * FROM $bs LEFT JOIN stalls USING (stallid, islandid)
+                                                      WHERE stallname IS NULL;
 
  # Every buy/sell must be part of an upload:
- SELECT * FROM $bs NATURAL LEFT JOIN uploads WHERE timestamp IS NULL;
+ SELECT * FROM $bs LEFT JOIN uploads USING (islandid) WHERE timestamp IS NULL;
 
  # The islandid in stalls must be the same as the islandid in buy/sell:
  SELECT * FROM $bs JOIN stalls USING (stallid)
@@ -196,32 +202,39 @@ sub db_check_referential_integrity () {
 
 END
     }
-    foreach my $end (qw(aiid biid)) {
-	foreach my $tab (qw(dists routes)) {
-	    nooutput(<<END);
+
+    nooutput(<<END);
+
+ # Every stall and upload must refer to an island:
+ SELECT * FROM stalls LEFT JOIN islands USING (islandid)
+                                        WHERE islandname IS NULL;
+ SELECT * FROM uploads LEFT JOIN islands USING (islandid)
+                                         WHERE islandname IS NULL;
+
+END
+    if ($full) {
+	foreach my $end (qw(aiid biid)) {
+	    foreach my $tab (qw(dists routes)) {
+		nooutput(<<END);
 
  # Every row in dists and routes must refer to two existing rows in islands:
  SELECT * FROM $tab d LEFT JOIN islands ON d.$end=islandid
 	WHERE islandname IS NULL;
 
 END
+	    }
 	}
-    }
-    nooutput(<<END);
+	nooutput(<<END);
 
  # Every pair of islands must have an entry in dists:
  SELECT * FROM islands ia JOIN islands ib LEFT JOIN dists
 	ON ia.islandid=aiid and ib.islandid=biid
 	WHERE dist IS NULL;
 
- # Every stall and upload must refer to an island:
- SELECT * FROM stalls NATURAL LEFT JOIN islands WHERE islandname IS NULL;
- SELECT * FROM uploads NATURAL LEFT JOIN islands WHERE islandname IS NULL;
-
  # Every commod must refers to a commodclass and vice versa:
- SELECT * FROM commods NATURAL LEFT JOIN commodclasses
+ SELECT * FROM commods LEFT JOIN commodclasses USING (commodclassid)
 	WHERE commodclass IS NULL;
- SELECT * FROM commodclasses NATURAL LEFT JOIN commods
+ SELECT * FROM commodclasses LEFT JOIN commods USING (commodclassid)
 	WHERE commodname IS NULL;
 
  # Ordvals which are not commodclass ordvals are unique:
@@ -234,24 +247,26 @@ END
  # For every class, posinclass is dense from 1 to maxposinclass,
  # apart from the commods for which it is zero.
  SELECT commodclass,commodclassid,posinclass,count(*)
-	FROM commods NATURAL JOIN commodclasses
+	FROM commods JOIN commodclasses USING (commodclassid)
 	WHERE posinclass > 0
 	GROUP BY commodclassid,posinclass
 	HAVING count(*) > 1;
  SELECT commodclass,commodclassid,count(*)
-	FROM commods NATURAL JOIN commodclasses
+	FROM commods JOIN commodclasses USING (commodclassid)
 	WHERE posinclass > 0
 	GROUP BY commodclassid
 	HAVING count(*) != maxposinclass;
  SELECT *
-	FROM commods NATURAL JOIN commodclasses
+	FROM commods JOIN commodclasses USING (commodclassid)
 	WHERE posinclass < 0 OR posinclass > maxposinclass;
 
 END
+    }
 }
 
-sub db_chkcommit () {
-    db_check_referential_integrity();
+sub db_chkcommit ($) {
+    my ($full) = @_;
+    db_check_referential_integrity($full);
     $dbh->commit();
 }