Changeset 33769

Timestamp:

Apr 12, 2012, 11:11:29 AM (14 years ago)

Author:

rhenders

Message:

new, safer, method to get ordered list of filters from DVO Photcodes table; change to JOIN when pulling filter mags from cps table; applying UNIQUE key on Object table after mags and colors populated; reporting number of duplicates found to the log

File:

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

trunk/ippToPsps/jython/objectbatch.py

@@ -78 +78 @@
     def alterPspsTables(self):
 
-        # index objID column
-        self.scratchDb.makeColumnUnique("Object", "objID")
-
         # dec is reserved in MySQL, so STILTS replaces if with dec_, which PSPS doesn't like. so, force it back again using ``
         self.scratchDb.execute("ALTER TABLE Object CHANGE dec_ `dec` double")
@@ -100 +97 @@
     def insertMags(self, cpsTable):
 
-        # list of all filters we are interested in
-        allFilters = ['g', 'r', 'i', 'z', 'y', ]
+        # list of all filters PSPS is interested in
+        interestedFilters = ['g', 'r', 'i', 'z', 'y']
         
-        # this loop looks into the DVO Photcodes tables and gets the 'CODE' for each the filters above that are listed
-        filters = []
-        for filter in allFilters:
-            code = self.scratchDb.getCodeForThisFilter(filter)
-            if code < 0: continue
-            filters.append([code, filter])
+        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
     
         # get a count of the available filters
@@ -119 +111 @@
 
             sql = "UPDATE Object \
-                   JOIN " + cpsTable + " AS cps ON (cps.row = (Object.row*" + str(filterCount) + ")-" + str(filter[0]) + ") \
+                   JOIN " + cpsTable + " AS cps ON (cps.row = (Object.row*" + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
                    SET \
                    n" + filter[1] + " = NCODE \
@@ -128 +120 @@
                    ," + filter[1] + "Max = MAG_80 \
                    "
-
+            
             self.scratchDb.execute(sql)
 
@@ -251 +243 @@
         #if not self.populateObjectCalColorTable(): return False
 
-        return True
-
+        # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number
+        self.logger.infoPair("Forcing uniqueness on", "objID in Object table")
+        rowCountBefore = self.scratchDb.getRowCount("Object")
+        self.scratchDb.execute("ALTER IGNORE TABLE Object ADD UNIQUE INDEX(objID)")
+        rowCountAfter = self.scratchDb.getRowCount("Object")
+        self.logger.infoPair("Number of duplicated objIDs removed", "%d" % (rowCountBefore - rowCountAfter))
+
+        return True
+