c8e1dc987ba5532c65be54d8f43af1ec4337fb10
angie
  Sat Nov 21 09:52:07 2015 -0800
Back-end implementation of Data Integrator's support for related tables and
fields using all.joiner.  Most joins are implemented using a new module,
hashJoin.c; but SQL joins are used in certain cases when hash joins are
impractical and SQL joins are actually faster.  A new module joinMixer
determines which joins should be implemented by hashJoin vs SQL, and computes
row indices for hashJoin objects to find keys (from SQL or other hashJoins)
and store results.  The SQL join info from joinMixer is translated into SQL
queries in annoStreamDb.  annoStreamDb also generates its own autoSql asObject,
adding the fields from related tables after the fields of the main track table.

Main changes:

- annoStreamDb.c
- main table SQL query now uses <table>.<field> instead of just <field> to
avoid clashes with same field name in different tables
- SQL joins return multiple rows for a single main table row when there are
multiple matching rows in a related table; these rows need to be squashed
into one row with the multiple matches comma-separated, both to match
hgTables behavior and to avoid overflow of rowBuf.  (glomSqlDup)
- as mentioned above, generate joining SQL queries when necessary and
generate own asObj including selected fields from related tables.
- parse JSON config object with relatedTables spec from UI via hgi_querySpec

hashJoin basically slurps a related table into a big hash of keys to values,
perform lookups (possibly of multiple keys), and formats each column's results.
It includes a lot of tweaks to match hgTables/joining.c output char-for-char:
collapse adjacent duplicate matches, commas at end of matches from multiple
key lookups, reversed order of multiple match values.  hgTables/joining.c uses
arrays of slNames, but in order to avoid all that allocation I'm just glomming
into an array of reused dyStrings.

joinMixer takes a list of fields to include in output, gets a list of joins
to be performed (from joinerRouteThroughAll), applies some simple rough
heuristics to guess whether a join is practical in SQL, and decides which joins
to do by SQL and which to do by hashJoin.  It plans a row format with several
groups of fields in this order: main table fields, related table fields to
appear in the output, related table fields needed by hashJoins, hashJoin
result fields needed by other hashJoins, and hashJoin result fields to appear
in output.  It initializes hashJoins with precomputed row indexes and also
provides a mapping from big-row columns to the columns that appear in output.

Thanks to Matt for testing on demo6 during development.

refs #15544

diff --git src/hg/inc/hashJoin.h src/hg/inc/hashJoin.h
new file mode 100644
index 0000000..9a60b66
--- /dev/null
+++ src/hg/inc/hashJoin.h
@@ -0,0 +1,42 @@
+/* hashJoin - join one or more columns of a hashed database table to an externally provided
+ * char **row that contains the key and empty slot(s) for the column value(s) */
+
+/* Copyright (C) 2015 The Regents of the University of California 
+ * See README in this or parent directory for licensing information. */
+
+#ifndef HASHJOIN_H
+#define HASHJOIN_H
+
+#include "dystring.h"
+#include "hash.h"
+#include "joiner.h"
+#include "localmem.h"
+
+struct hashJoin;
+
+struct hashJoin *hashJoinNew(struct joinerDtf *keyDtf, uint extRowKeyIx,
+                             struct joinerDtf *valDtfs, uint *extRowValIxs,
+                             struct joinerField *jfA, struct joinerField *jfB,
+                             boolean naForMissing);
+/* Return a new hashJoin.  extRowKeyIx is the index in an external row of the key
+ * to use in the join.  extRowValIxs[valCount] contains each hash val column's index
+ * into an external row.  jfA and jfB are optional; if given, then jfA's separator,
+ * chopBefore and chopAfter will be applied to each key retrieved from the external row
+ * and jfB's separator, chopBefore and chopAfter will be applied to each hash key.
+ * If naForMissing is TRUE then the result columns will contain "n/a" when there is
+ * no match in the hash. */
+
+struct hashJoin *hashJoinNext(struct hashJoin *el);
+/* Get the next hashJoin in a list of hashJoins. */
+
+void hashJoinOneRow(struct hashJoin *self, char **extRow);
+/* Look up some column of extRow in hash and place result(s) in other columns of extRow.
+ * Don't call this again until done with extRow -- column value storage is reused. */
+
+void hashJoinFree(struct hashJoin **pSelf);
+/* Free hashJoin unless already NULL. */
+
+void hashJoinFreeList(struct hashJoin **pList);
+/* Free a list of hashJoins. */
+
+#endif//ndef HASHJOIN_H