fd591cb7db465e8d30fb21b5672550cb93243c42
max
  Thu Jul 6 12:33:25 2017 -0700
Adding GBIB/GBIC support to hgGeneGraph, refs #13634

diff --git src/hg/pyLib/hgLib.py src/hg/pyLib/hgLib.py
index c26511e..5e74625 100644
--- src/hg/pyLib/hgLib.py
+++ src/hg/pyLib/hgLib.py
@@ -12,50 +12,52 @@
 # - never print incoming HTTP argument as raw text. Run it through cgi.escape to 
 #   destroy javascript code in them.
 
 # Non-standard imports. They need to be installed on the machine. 
 # We provide a pre-compiled library as part of our cgi-bin distribution as a
 # fallback in the "pyLib" directory. The idea of having pyLib be the last
 # directory in sys.path is that the system MySQLdb takes precedence.
 try:
     import MySQLdb
 except:
     print "Installation error - could not load MySQLdb for Python. Please tell your system administrator to run " \
         "one of these commands as root: 'yum install MySQL-python', 'apt-get install python-mysqldb' or 'pip install MySQL-python'."
     exit(0)
 
 # Imports from the Python 2.7 standard library
-# Minimize global imports. Each library import can take up to 20msecs.
+# Please minimize global imports. Each library import can take up to 20msecs.
 import os, cgi, sys, logging
 
 from os.path import join, isfile, normpath, abspath, dirname
 from collections import namedtuple
 
 # activate debugging output output only on dev
 import platform
 if "hgwdev" in platform.node():
     import cgitb
     cgitb.enable()
 
 # debug level: a number. the higher, the more debug info is printed
 verboseLevel = None
 
 cgiArgs = None
 
 # like in the kent tree, we keep track of whether we have already output the content-type line
 contentLineDone = False
 
+jksqlTrace = False
+
 def errAbort(msg):
     " show msg and abort. Like errAbort.c "
     if not contentLineDone:
         printContentType()
     print msg
     exit(0)
 
 def debug(level, msg):
     " output debug message with a given verbosity level "
     if level >= verboseLevel:
         print(msg+"<br>")
         sys.stdout.flush()
  
 def parseConf(fname):
     " parse a hg.conf style file, return as dict key -> value (all strings) "
@@ -79,75 +81,146 @@
 # cache of hg.conf contents
 hgConf = None
 
 def parseHgConf():
     """ return hg.conf as dict key:value. """
     global hgConf
     if hgConf is not None:
         return hgConf
 
     hgConf = dict() # python dict = hash table
 
     confDir = os.path.dirname(__file__) # look for hg.conf in parent dir
     fname = os.path.join(confDir, "..", "hg.conf")
     hgConf = parseConf(fname)
 
+    if cfgOptionBoolean("JKSQL_TRACE"):
+        global jksqlTrace
+        jksqlTrace = True
+    
     return hgConf
 
 def cfgOption(name, default=None):
     " return hg.conf option or default "
     return hgConf.get(name, default)
 
+def cfgOptionBoolean(name, default=False):
+    " return True if option is set to 1, on or true, or default if not set "
+    val = hgConf.get(name, default) in [True, "on", "1", "true"]
+    return val
+
 def sqlConnect(db, host=None, user=None, passwd=None):
     """ connect to sql server specified in hg.conf with given db. Like jksql.c. """
     cfg = parseHgConf()
     if host==None:
         host, user, passwd = cfg["db.host"], cfg["db.user"], cfg["db.password"]
     conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
+
+    # we will need this info later
+    conn.failoverConn = None
+    conn.db = db
+    conn.host = host
     return conn
 
 def sqlTableExists(conn, table):
     " return True if table exists. Like jksql.c "
     query = "SHOW TABLES LIKE %s"
     sqlQueryExists(conn, query, table)
 
 def sqlQueryExists(conn, query, args=None):
     " return true if query returns a result. Like jksql.c. No caching for now, unlike hdb.c. "
     cursor = conn.cursor()
     rows = cursor.execute(query, args)
     row = cursor.fetchone()
+
     res = (row!=None)
     cursor.close()
     return res
 
+def _sqlConnectFailover(conn):
+    " connect the failover connection of a connection "
+    cfg = parseHgConf()
+    if "slow-db.host" not in cfg:
+        return
+
+    host, user, passwd = cfg["slow-db.host"], cfg["slow-db.user"], cfg["slow-db.password"]
+    sys.stderr.write("SQL_CONNECT 0 %s %s %s\n" % (host, user, passwd))
+    db = conn.db
+    failoverConn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
+    conn.failoverConn = failoverConn
+
+def _timeDeltaSeconds(time1, time2):
+    " convert time difference to total seconds for python 2.6 "
+    td = time1 - time2
+    return (td.microseconds + (td.seconds + td.days * 24 * 3600) * 10**6) / 10**6
+
 def sqlQuery(conn, query, args=None):
     """ Return all rows for query, placeholders can be used, args is a list to
     replace placeholders, to prevent Mysql injection.  Never do replacement
     with %s yourself, unless the value is coming from inside the program. This
     is called a "parameterized query". There is only %s, %d does not work.
 
     example:
     query = "SELECT contents FROM table WHERE id=%(id)s and name=%(name)s;"
     rows = sqlQuery(conn, query, {"id":1234, "name":"hiram"})
     """
     cursor = conn.cursor()
+
+    if jksqlTrace:
+        from datetime import datetime
+        sys.stderr.write("SQL_QUERY 0 %s %s %s %s\n" % (conn.host, conn.db, query, args))
+        startTime = datetime.now()
+
+    try:
+        rows = cursor.execute(query, args)
+
+        if jksqlTrace:
+            timeDiff = _timeDeltaSeconds(datetime.now(), startTime)
+            sys.stderr.write("SQL_TIME 0 %s %s %.3f\n" % (conn.host, conn.db, timeDiff))
+
+    except MySQLdb.Error, errObj:
+        # on table not found, try the secondary mysql connection, "slow-db" in hg.conf
+        errCode, errDesc = errObj
+        if errCode!=1146: # "table not found" error
+            raise
+
+        if conn.failoverConn == None:
+            _sqlConnectFailover(conn)
+
+        if not conn.failoverConn:
+            raise
+
+        # stay compatible with the jksql.c JKSQL_TRACE output format
+        if jksqlTrace:
+            sys.stderr.write("SQL_FAILOVER 0 %s %s db -> slow-db | %s %s\n" % \
+                    (conn.host, conn.db, query, args))
+        cursor = conn.failoverConn.cursor()
         rows = cursor.execute(query, args)
+
+    if jksqlTrace:
+        startTime = datetime.now()
+
     data = cursor.fetchall()
+    cursor.close()
+
+    if jksqlTrace:
+        timeDiff = _timeDeltaSeconds(datetime.now(), startTime)
+        sys.stderr.write("SQL_FETCH 0 %s %s %.3f\n" % (conn.host, conn.db, timeDiff))
+
     colNames = [desc[0] for desc in cursor.description]
     Rec = namedtuple("MysqlRow", colNames)
     recs = [Rec(*row) for row in data]
-    cursor.close()
     return recs
 
 def htmlPageEnd(oldJquery=False):
     " close html body/page "
     print "</body>"
     print "</html>"
 
 def printMenuBar(oldJquery=False):
     baseDir = "../"
     " print the menubar. Mostly copied from src/hg/hgMenuBar.c "
 
     print ("<noscript><div class='noscript'><div class='noscript-inner'><p><b>JavaScript is disabled in your web browser</b></p>")
     print ("<p>You must have JavaScript enabled in your web browser to use the Genome Browser</p></div></div></noscript>\n")
 
     menuPath = "../htdocs/inc/globalNavBar.inc"