yarrg database code: eliminate all NATURAL JOINs and turn them into JOIN USING

diff --git a/yarrg/CommodsDatabase.pm b/yarrg/CommodsDatabase.pm
index 3cb543dc43b3866971cff2cb28e39de7f6061631..999dda779611b441e2131348e4b6efa5acce0228 100644 (file)
--- 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 a75864a57e2ff4f0d3d48514697f78d900a14d24..e65b2b6a91fcfe2237f6d8c123e0891a0ec5cebd 100755 (executable)
--- 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 4f8ba814b7f1e5ddf2b0ce0fc22549ad560b728d..a115bbdd562dd53286be49ff175f5519f366cd28 100644 (file)
--- 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 8d55f2686130568ba92d2c578dd787d2362e368e..c801a631e65855d4e762aa247bb702bf883ecfc1 100644 (file)
--- 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 03ef3e1e8dc4ab99bfc8b29cfc8b69750268845c..c62ca60b7f89a7956008cacc7c8998ae944a72c7 100644 (file)
--- 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