Changeset 38837 for trunk/ippToPsps/jython/ipptopspsdb.py

Timestamp:

Oct 10, 2015, 12:40:44 PM (11 years ago)

Author:

eugene

Message:

make code less forgiving: sql execute aborts on failures; deal with various hidden problems: add IF EXISTS to drop table operations to avoid raising unneeded error, make sure tables exist before trying to add indexes, test for existence of the stack _xsrc, _xfit, etc tables (and skip if missing); fix sqlLine.group to cast inputs to string before performing string ops; getKeyFloat now returns a float value which is safe with sqlLine.group, but can be used in a math expression; use python math. operators as needed (sqrt, fabs); fulltest.sh runs for camera, stack, object stages; update dvoSkyTable to use REGION_ID instead of special word INDEX; add existsClient method (avoid using mysql failure as a test for table existence); no need to test for exceptions to sql.execute since we abort on failure; replace esoteric SQL with dropTable calls; trap errors in stilts.treads in setupScratchdb; drop StackObjecThin and StackDetOffMeta along with other stack scratch tables

File:

• trunk/ippToPsps/jython/ipptopspsdb.py (modified) (5 diffs)

trunk/ippToPsps/jython/ipptopspsdb.py

@@ -914 +914 @@
 
     '''
+    Returns boolean value for this column in clients table
+    '''
+    def existsClient(self, host, pid):
+
+        sql = "SELECT id FROM clients WHERE host = '" + host + "' AND pid = " + str(pid) + " LIMIT 1"
+
+        try:
+            rs = self.executeQuery(sql)
+            if rs.first() is False: return False
+        except:
+            self.logger.exception("Unable to check if " + host + "-" + str(pid) + " is in clients table")
+
+        return True
+
+    '''
     Update clients, or inserts it for the first time
     '''
     def updateClient(self, type, host, pid):
 
-        try:
+        if self.existsClient(host, pid):
+            self.execute("UPDATE clients SET timestamp = now() WHERE host = '" + host + "' AND pid = " + str(pid))
+        else:
             self.insertClient(type, host, pid)
-        except:
-            self.execute("UPDATE clients SET timestamp = now() WHERE host = '" + host + "' AND pid = " + str(pid))
 
     '''
@@ -1337 +1352 @@
 
         for id in ids:
-            sql = "INSERT IGNORE INTO pending \
+            # why is there an IGNORE here? we have deleted old versions already above
+            sql = "INSERT INTO pending \
                (box_id, batch_type, stage_id) \
                VALUES \
                (" + str(box_id) + ", '" + batchType + "', " + str(id) + ")"
 
-            print sql
-            try: self.execute(sql)
-            except:
-                print "failed to insert into pending"
-                print "sql: " + sql
-                raise
+            self.execute(sql)
 
     '''
@@ -1656 +1667 @@
     def storeAllItems(self, rows):
 
-        try:
-            self.execute("DROP TABLE all_pending")
-        except:
-            pass
+        self.dropTable("all_pending")
 
         self.execute("CREATE TEMPORARY TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)")
         for row in rows:
-
             try:
                 sql = "INSERT INTO all_pending (stage_id, ra_bore, dec_bore) \
@@ -1678 +1685 @@
     '''
     def storeAllItemsInDvodb(self, rows, dvo_db, batchType):
-
-        #try:
-        #    self.execute("DROP TABLE all_pending")
-        #except:
-        #    pass
 
         #self.execute("CREATE TEMPORARY TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)")
@@ -1699 +1701 @@
         #self.logger.infoPair("Items written to Db", "%d" % count)
 
-
-
     '''
     Gets all items in the all_pending temporary table within the bounds of this box