From 19669922dd2393c23371ee1532824e528b897de0 Mon Sep 17 00:00:00 2001
From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Wed, 15 Sep 2010 21:12:49 +0100
Subject: [PATCH] yarrg database code: eliminate all NATURAL JOINs and turn
 them into JOIN USING

---
 yarrg/CommodsDatabase.pm     | 25 ++++++++++++++-----------
 yarrg/commod-email-processor |  1 +
 yarrg/web/query_age          |  2 +-
 yarrg/web/query_commod       |  2 +-
 yarrg/web/routetrade         |  4 ++--
 5 files changed, 19 insertions(+), 15 deletions(-)

diff --git a/yarrg/CommodsDatabase.pm b/yarrg/CommodsDatabase.pm
index 3cb543d..999dda7 100644
--- a/yarrg/CommodsDatabase.pm
+++ b/yarrg/CommodsDatabase.pm
@@ -188,12 +188,13 @@ sub db_check_referential_integrity ($) {
 	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)
@@ -205,8 +206,10 @@ END
     nooutput(<<END);
 
  # 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;
+ 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) {
@@ -229,9 +232,9 @@ END
 	WHERE dist 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:
@@ -244,17 +247,17 @@ 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
diff --git a/yarrg/commod-email-processor b/yarrg/commod-email-processor
index a75864a..e65b2b6 100755
--- a/yarrg/commod-email-processor
+++ b/yarrg/commod-email-processor
@@ -197,6 +197,7 @@ sub main () {
 #    print "\n";
     db_chkcommit(0);
 
+    # Warning!  Below runes are bogus.  Do not use NATURAL JOIN!
     # select * from ((buy natural join commods) natural join stalls) natural join islands;
     # select * from ((sell natural join commods) natural join stalls) natural join islands;
 
diff --git a/yarrg/web/query_age b/yarrg/web/query_age
index 4f8ba81..a115bbd 100644
--- a/yarrg/web/query_age
+++ b/yarrg/web/query_age
@@ -47,7 +47,7 @@ $dbh
 my $now= time;
 
 my $sth= $dbh->prepare("SELECT archipelago, islandid, islandname, timestamp
-				FROM uploads NATURAL JOIN islands
+				FROM uploads JOIN islands USING (islandid)
 				ORDER BY archipelago, islandname");
 $sth->execute();
 
diff --git a/yarrg/web/query_commod b/yarrg/web/query_commod
index 8d55f26..c801a63 100644
--- a/yarrg/web/query_commod
+++ b/yarrg/web/query_commod
@@ -99,7 +99,7 @@ foreach my $bs (split /_/, $ARGS{BuySell}) {
 
 	my $offers= $dbh->prepare(
 	"SELECT stallname, price, qty
-		FROM $bs NATURAL JOIN stalls
+		FROM $bs JOIN stalls USING (stallid, islandid)
 		WHERE commodid = ? AND islandid = ?
 		ORDER BY price $ascdesc"
 		);
diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade
index 03ef3e1..c62ca60 100644
--- a/yarrg/web/routetrade
+++ b/yarrg/web/routetrade
@@ -1010,12 +1010,12 @@ END
 <%perl>
 	my $sth_i= $dbh->prepare(<<END);
 		SELECT archipelago, islandid, islandname, timestamp
-			FROM uploads NATURAL JOIN islands
+			FROM uploads JOIN islands USING (islandid)
 			WHERE islandid = ?
 END
 	my $sth_a= $dbh->prepare(<<END);
 		SELECT archipelago, islandid, islandname, timestamp
-			FROM uploads NATURAL JOIN islands
+			FROM uploads JOIN islands USING (islandid)
 			WHERE archipelago = ?
 			ORDER BY islandname
 END
-- 
2.30.2