1
2
3 """
4 Common database queries used by many CUs.
5
6 @note: Please put all newly added functions in alphabetical order for ease
7 of maintenance.
8
9 @author: N.C. Hambly & I.A. Bond
10 @org: WFAU, IfA, University of Edinburgh
11
12 @newfield contributors: Contributors, Contributors (Alphabetical Order)
13 @contributors: J. Bryant, R.S. Collins, N.J.G. Cross, E. Sutorius
14 """
15
16 from __future__ import division, print_function
17 from future_builtins import map, zip
18
19 from collections import defaultdict
20 import numpy
21
22 import wsatools.Astrometry as astro
23 import wsatools.DataFactory as df
24 import wsatools.DbConnect.DbConstants as dbc
25 from wsatools.DbConnect.DbSession import DbSession, Join, SelectSQL
26 from wsatools.SystemConstants import DepCodes, SystemConstants
27 import wsatools.Utilities as utils
28
29
31 """ Exception to denote that programme set up has not been successfully.
32 """
33 pass
34
35
36
38 """
39 Deprecate old detections from a given programme's detection table.
40
41 @param db: Connection to the database where detections are to be
42 deprecated.
43 @type db: DbSession
44 @param tableName: The name of the detection table in which to perform the
45 operation.
46 @type tableName: str
47
48 @todo: Make this a general propagate deprecations function like the one
49 used in the Automator? That way we don't get the double deprecation
50 of WFAU reprocessed detections - first they are deprecated by CU4
51 with deprecation+128, then they are set to 255 by Automator.
52 """
53 return db.update(tableName,
54 "deprecated=deprecated+%s" % DepCodes.reprocCASU,
55 where="deprecated<%s AND multiframeID IN (%s)" % (DepCodes.reprocCASU,
56 SelectSQL("multiframeID", "Multiframe", "deprecated>=%s" %
57 DepCodes.reprocCASU)))
58
59
60
63 """
64 Find required products of all types
65
66 @param programme: Programme table for current database with the current
67 row set to that of the programme to be curated.
68 @type programme: DataFactory.ProgrammeTable
69 @param minProdID: Restrict to product IDs with this minimum value.
70 @type minProdID: int
71 @param maxProdID: Restrict to product IDs with this maximum value.
72 @type maxProdID: int
73 @param reqProdIDs: Restrict to product IDs in this list.
74 @type reqProdIDs: sequence(int)
75 @param fieldIDs: List of fieldIDs to curate.
76 @type fieldIDs: list(str)
77
78 @return: A dictionary of product ID lists required to be created for this
79 programme referenced by product type.
80 @rtype: dict(str: list(int))
81
82 """
83 productTypes = list(reversed(programme.getProductTypes()))
84 try:
85 pType = productTypes[0]
86 except IndexError:
87 raise ProgrammeNotSetUpError(
88 "SetupProgramme.py has not been run on this programme.")
89
90 reqProdsOfType = defaultdict(list)
91 reqProdsOfType[pType] = \
92 getRequiredProductIDs(programme, minProdID, maxProdID, reqProdIDs,
93 fieldIDs, pType)
94 for pType, higherLayer in zip(productTypes[1:], productTypes):
95
96 if not reqProdsOfType[higherLayer]:
97 del reqProdsOfType[higherLayer]
98 break
99
100
101 prodLinkIDs = SelectSQL("intProductID", "ProductLinks",
102 where="programmeID=%s AND combiProductID in (%s)"
103 " AND combiProdType='%s' AND intProdType='%s'"
104 % (programme.getAttr("programmeID"),
105 ','.join(map(str, reqProdsOfType[higherLayer])),
106 higherLayer, pType))
107
108 reqProdsOfType[pType] = getRequiredProductIDs(programme,
109 productType=pType, whereStr="productID IN (%s)" % prodLinkIDs)
110
111 return reqProdsOfType
112
113
114
115 -def getAllDBs(serverName, includeTestDBs=False, archive="ALL"):
116 """
117 Retrieves the list of all released databases on a given server.
118
119 @param serverName: Catalogue server to query.
120 @type serverName: str
121 @param includeTestDBs: Include test databases as well.
122 @type includeTestDBs: str
123 @param archive: The archive for which databases are looked up.
124 @type archive: str
125
126 @return: List of names for all released databases found on given server.
127 @rtype: list(str)
128
129 @todo: Move into DbSession module as an independent function, and rename to
130 getReleaseDbNames().
131
132 """
133 includeDBs = []
134 excludeDBs = []
135
136 if archive in ["WSA", "ALL"]:
137 includeDBs.extend(["name LIKE 'U%'",
138 "name LIKE 'WORLD%'",
139 "name LIKE 'WFCAM%'",
140 "name LIKE 'WSA'",
141 "name LIKE 'TRANSIT'",
142 "name LIKE 'CALIBRATION%'",
143 "name LIKE 'WSERV%'"])
144 if archive in ["VSA", "ALL"]:
145 includeDBs.extend(["name LIKE 'V%'",
146 "name LIKE 'SV%'",
147 "name LIKE 'ULTRA%'",
148 "name LIKE 'N%v20%'"])
149 if archive in ["OSA", "ALL"]:
150 includeDBs.extend(["name LIKE 'OSA%'",
151 "name like 'ATLAS%'"])
152 if archive in ["SSA", "ALL"]:
153 includeDBs.extend(["name LIKE '%SSA'",
154 "name LIKE 'F287'"])
155 if archive in ["GES", "ALL"]:
156 includeDBs.extend(["name LIKE 'GES%'"])
157
158 if archive in ["EXT", "ALL"]:
159 includeDBs.extend(["name LIKE 'BestDR%'",
160 "name LIKE 'COMBO17'",
161 "name LIKE 'DENIS'",
162 "name LIKE 'EROS'",
163 "name LIKE 'FIRST'",
164 "name LIKE 'GalexGR%'",
165 "name LIKE 'GLIMPSE'",
166 "name LIKE 'IRAS'",
167 "name LIKE 'MACHO'",
168 "name LIKE 'MCPS'",
169 "name LIKE 'MGC'",
170 "name LIKE 'NVSS'",
171 "name LIKE 'ROSAT'",
172 "name LIKE 'SEGUE%'",
173 "name LIKE 'SIXDF%'",
174 "name LIKE 'SPITZER'",
175 "name LIKE 'Stripe82%'",
176 "name LIKE 'TWO%'",
177 "name LIKE 'WISE'"])
178
179 if includeTestDBs:
180 includeDBs.append("name LIKE 'TEST%'")
181
182 includeDBsStr = "(" + " OR ".join(includeDBs) + ")"
183
184 if archive not in ["VSA", "ALL"]:
185 excludeDBs = ["name NOT LIKE 'ULTRA%'"]
186
187 excludeDBs.extend(["name != 'USNOB'",
188 "name NOT LIKE 'usernames%'",
189 "name != 'userinterface'",
190 "name != 'tempdb'",
191 "name != 'VESPA'",
192 "filename NOT LIKE '%Program Files%'",
193 "("+" OR ".join([
194 "filename LIKE '%" + serverName + "\%' ",
195 "filename LIKE '%:%'"])+")"])
196
197 excludeDBsStr = " AND " + " AND ".join(excludeDBs)
198
199 return DbSession(serverName + ".master").query("name", "sysdatabases",
200 whereStr=includeDBsStr + excludeDBsStr, orderBy="name")
201
202
203
204 -def getAllFieldObs(db, ra, dec, dlRaDec, filterID, programmeID, frameType,
205 dateRange=SystemConstants().obsCal.dateRange(), nustep=None,
206 offsetPos= -1, deprecated=DepCodes.selectNonDeprecated,
207 noDeeps=False, onlyTileComps=False, getConfToo=False):
208 """
209 Selects a list of multiframeIDs of given frame type in a certain direction,
210 through a certain filter and in a certain programmeID.
211
212 @param db: A connection to the database to query.
213 @type db: DbSession
214 @param ra: The expected right ascension of the product in degrees.
215 @type ra: float
216 @param dec: The expected declination of the product in degrees.
217 @type dec: float
218 @param dlRaDec: The matching radius in arcmin
219 @type dlRaDec: float
220 @param filterID: Only observations with this filter ID.
221 @type filterID: int
222 @param programmeID: The programmeID.
223 @type programmeID: int
224 @param frameType: Include these frame types.
225 @type frameType: str
226 @param dateRange: Include data observed between these nights only.
227 @type dateRange: DateRange
228 @param nustep: Select level of microstepping.
229 @type nustep: int
230 @param offsetPos: Position of pawprint in the tile
231 @type offsetPos: int
232 @param deprecated: Deprecation string
233 @type deprecated: str
234 @param noDeeps: Exclude deep products?
235 @type noDeeps: bool
236 @param onlyTileComps: Only use stacks that are components of tiles
237 @type onlyTileComps: bool
238
239 @return: Set of multiframe IDs.
240 @rtype: set(int)
241
242 @todo: Make a method of a Stacker class that CU13 and CU6 derive from?
243
244 """
245 dateRange = "m1.utDate BETWEEN '%s' AND '%s'" % dateRange
246 offsetSel = ''
247 if offsetPos >= 0:
248 xPos = int(offsetPos / 3)
249 yPos = offsetPos % 3
250 if xPos == 0:
251 offsetSel += " AND m1.offsetX<0"
252 elif xPos == 1:
253 offsetSel += " AND m1.offsetX>0"
254 if yPos == 0:
255 offsetSel += " AND m1.offsetY<0"
256 elif yPos == 1:
257 offsetSel += " AND m1.offsetY=0"
258 else:
259 offsetSel += " AND m1.offsetY>0"
260
261 frameTypes = getFrameSelection(frameType, noDeeps, alias='m1',
262 selType='%stack%' if getConfToo else '%stack')
263 whereStr = ("m1.%s AND programmeID=%s AND m1.filterID=%s AND %s AND %s"
264 % (deprecated, programmeID, filterID, dateRange, frameTypes))
265
266 tables = [("Multiframe", "m1"), ("ProgrammeFrame", "p")]
267 posStr = "(15.*m1.raBase),m1.decBase"
268 fromStr = ""
269 if 'tile' in frameType:
270 tables.append(("CurrentAstrometry", "c"))
271 whereStr += " AND extNum=2"
272 posStr = "centralRa,centralDec"
273 offsetSel = ""
274
275 elif onlyTileComps:
276 tables.append(("Provenance", "v"))
277 fromStr = "Multiframe as m2,"
278 whereStr += (" AND combiFrameID=m2.multiframeID AND m2.frameType LIKE "
279 "'%%tile%%stack' AND m2.deprecated=0")
280
281 join = Join(tables, ["multiframeID"])
282 fromStr += join.fromStr
283 whereStr += " AND " + join.whereStr
284 whereStr += (" AND dbo.fGreatCircleDist(%s,%s,%s)<=%s"
285 % (posStr, ra, dec, dlRaDec)
286 + (" AND m1.nustep=%s" % nustep if nustep is not None else '')
287 + offsetSel)
288
289 return set(db.query("m1.multiframeID", fromStr, whereStr))
290
291
292
297 """
298 Selects a list of multiframeIDs of given frame type in a certain direction,
299 through a certain filter and in a certain programmeID.
300
301 @param db: A connection to the database to query.
302 @type db: DbSession
303 @param productList: A list of products
304 @type productList: list(int)
305 @param programmeID: The programmeID.
306 @type programmeID: int
307 @param frameType: Include these frame types.
308 @type frameType: str
309 @param dateRange: Include data observed between these nights only.
310 @type dateRange: DateRange
311 @param nustep: Select level of microstepping.
312 @type nustep: int
313 @param offsetPos: Position of pawprint in the tile
314 @type offsetPos: int
315 @param deprecated: Deprecation string
316 @type deprecated: str
317 @param noDeeps: Exclude deep products?
318 @type noDeeps: bool
319 @param onlyTileComps: Only use stacks that are components of tiles
320 @type onlyTileComps: bool
321
322 @return: Set of multiframe IDs.
323 @rtype: set(int)
324
325 @todo: Make a method of a Stacker class that CU13 and CU6 derive from?
326
327 """
328 reqTable = 'RequiredStack'
329 dist = '60.*stackRadius'
330 if 'mosaic' in frameType:
331 reqTable = 'RequiredMosaic'
332 dist = '60.*sqrt(raExtent*raExtent+decExtent*decExtent)'
333 elif 'tile' in frameType:
334 reqTable = 'RequiredTile'
335
336 dateRange = "m1.utDate BETWEEN '%s' AND '%s'" % dateRange
337 offsetSel = ''
338 if frameType == 'stack' and db.sysc.hasOffsetPos:
339 offsetSel = " AND r.offsetPos=dbo.fOffsetPos(m1.offsetX,m1.offsetY)"
340 nuStepSel = " AND m1.nustep=r.nustep" if db.sysc.hasMicroStep else ""
341 frameTypes = getFrameSelection(frameType, noDeeps, alias='m1',
342 selType='%stack%' if getConfToo else '%stack')
343 whereStr = ("m1.%s AND r.programmeID=p.programmeID AND p.programmeID=%s "
344 "AND m1.filterID=r.filterID AND %s AND %s"
345 % (deprecated, programmeID, dateRange, frameTypes))
346 whereStr += " AND r.productID in (%s)" % ','.join(map(str, productList)) if productList else ""
347 tables = [("Multiframe", "m1"), ("ProgrammeFrame", "p")]
348 posStr = "(15.*m1.raBase),m1.decBase"
349 fromStr = "%s as r," % reqTable
350 if 'tile' in frameType:
351 tables.append(("CurrentAstrometry", "c"))
352 whereStr += " AND extNum=2"
353 posStr = "centralRa,centralDec"
354 offsetSel = ""
355
356 elif onlyTileComps:
357 tables.append(("Provenance", "v"))
358 fromStr += "Multiframe as m2,"
359 whereStr += (" AND combiFrameID=m2.multiframeID AND m2.frameType LIKE "
360 "'%%tile%%stack' AND m2.deprecated=0")
361
362 join = Join(tables, ["multiframeID"])
363 fromStr += join.fromStr
364 whereStr += " AND " + join.whereStr
365 whereStr += (" AND dbo.fGreatCircleDist(%s,r.ra,r.dec)<=(%s)"
366 % (posStr, dist) + nuStepSel + offsetSel)
367 orderBy = "r.productID,m1.multiframeID"
368 productMatches = db.query("r.productID,m1.multiframeID", fromStr, whereStr,
369 orderBy=orderBy)
370 dictPIDMfID = defaultdict(list)
371 if not productList:
372 productList = db.query("productID", reqTable, "programmeID=%s" %
373 programmeID)
374 for prodID in productList:
375 dictPIDMfID[prodID] = [mfID for pID, mfID in productMatches
376 if pID == prodID]
377 return dictPIDMfID
378
379
381 """
382 Retrieves broad band filter that overlaps the given narrow band filter.
383
384 @param db: Connection to database to query.
385 @type db: DbSession
386 @param nbFilterName: Short name of the narrow band filter.
387 @type nbFilterName: str
388 @param programmeID: UID of the programme that uses this filter.
389 @type programmeID: int
390
391 @return: Short name of the broad band filter that matches the wavelength
392 range of this narrow band filter.
393 @rtype: str
394
395 """
396 progFilters = db.query("Filter.filterID, shortName, cutOn, cutOff",
397 Join(["Filter", "RequiredFilters"], ["filterID"]),
398 "programmeID=%s" % programmeID)
399
400 nbFilterName = nbFilterName.lower()
401 try:
402 nbCutOn, nbCutOff = \
403 dict((filt.shortName.lower(), (filt.cutOn, filt.cutOff))
404 for filt in progFilters)[nbFilterName]
405 except KeyError:
406 raise KeyError("getBBFilter: nbFilterName=%s does not correspond to a "
407 "Filter.shortName in the database" % nbFilterName)
408
409 bbFilters = [filt.shortName for filt in progFilters
410 if filt.filterID <= 5 and filt.cutOn < nbCutOn
411 and filt.cutOff > nbCutOff]
412
413 if len(bbFilters) != 1:
414
415
416 if len(progFilters) <= 1:
417
418 return
419 else:
420 nbFilterID = dict((filt.shortName.lower(), filt.filterID)
421 for filt in progFilters)[nbFilterName]
422
423 fIDs = [filt.filterID for filt in progFilters
424 if filt.filterID < nbFilterID]
425 if fIDs:
426 filtID = max(fIDs)
427 else:
428 filtID = min(filt.filterID for filt in progFilters
429 if filt.filterID > nbFilterID)
430
431 return dict((f.filterID, f.shortName) for f in progFilters)[filtID]
432
433 return bbFilters[0]
434
435
436
438 """ Find fieldID of given frame set.
439 """
440 passbands = df.PassbandList(progTable).getMfCols()
441 programmeID = progTable.getAttr("programmeID")
442 for band in passbands:
443 prodID, frameType = db.query("productID, frameType",
444 fromStr="ProgrammeFrame AS P, Multiframe AS M, %s AS L"
445 % progTable.getMergeLogTable(),
446 whereStr="L.%s=P.multiframeID AND L.%s=M.multiframeID AND L.%s>0"
447 " AND L.frameSetID=%s AND programmeID=%s"
448 % ((band,) * 3 + (frameSetID, programmeID)),
449 firstOnly=True)
450
451 if prodID:
452 tableName = "Required" \
453 + db.sysc.productTypeOfFrame(frameType).title()
454
455 return db.query("fieldID", tableName,
456 "productID=%s AND programmeID=%s" % (prodID, programmeID),
457 firstOnly=True, default=dbc.intDefault())
458
459 return dbc.intDefault()
460
461
462
484
485
486
487 -def getFrameSelection(imageType, noDeeps=False, deepOnly=False, alias='',
488 selType='%stack'):
489 """
490 @return: Frame type selections for image types.
491 @rtype: tuple(str, list(str))
492
493 """
494 if deepOnly and noDeeps:
495 return
496
497 if deepOnly:
498 selType = '%deep' + selType
499
500 rejectTypes = ['%deep%'] if noDeeps else []
501 if imageType == 'stack':
502 rejectTypes += ['%tile%', '%mosaic%']
503
504 elif imageType == 'tile':
505 selType = '%tile' + selType
506 rejectTypes.append('%mosaic%')
507
508 elif imageType == 'mosaic':
509 selType = '%mosaic' + selType
510
511 whereClause = "frameType LIKE '%s' AND " % selType if selType != '%' else ''
512 whereClause += \
513 "frameType NOT LIKE '%s'" % "' AND frameType NOT LIKE '".join(rejectTypes)
514
515 if alias:
516 return whereClause.replace('frameType', alias + '.frameType')
517
518 return whereClause
519
520
521
552
553
554
556 """
557 This function uses the frameSets in MergeLog to derive grouping
558 positions for a survey. This is better than using RequiredStack, since
559 RequiredStack only has deep stack positions and does not work in shallow
560 surveys.
561
562 @todo: Is this function being used? CU9?
563
564 """
565 db = programme._db if not otherDB else otherDB
566 passbands = df.PassbandList(programme, isSynoptic=True).getMfCols()
567 selString = ""
568 frmString = ""
569 whrString = ""
570 for index, pMF in enumerate(passbands):
571 selString += "(15.*m%d.rabase),m%d.decbase," % ((index + 1), (index + 1))
572 frmString += "Multiframe as m%d," % (index + 1)
573 whrString += "m%d.multiframeID=ml.%s AND " % ((index + 1), pMF)
574
575
576 fieldCentreInfo = db.query(
577 selectStr=selString[:-1],
578 fromStr="%s %s as ml" % (frmString, programme.getMergeLogTable()),
579 whereStr="%s frameSetID>0" % whrString)
580
581 fieldCentres = []
582 for fieldPos in fieldCentreInfo:
583 raL, decL = [], []
584 for bpassInd in range(len(passbands)):
585 ra, dec = fieldPos[bpassInd * 2], fieldPos[bpassInd * 2 + 1]
586 if ra > 0. and dec >= -90.:
587 raL.append(ra)
588 decL.append(dec)
589 raM = numpy.median(ra)
590 decM = numpy.median(dec)
591 fieldCentres.append((raM, decM))
592
593 return sorted(set(fieldCentres))
594
595
596
597 -def getIntStacks(db, progID, reqProdIDs, prodType, dateRange, allNuStep=True,
598 intFrameType='stack', returnAsProdIDDict=False, getConfToo=False):
599 """
600 Retrieves multiframe IDs for the intermediate stacks that will form the
601 given range of deep products over the given time frame.
602
603 @param db: A connection to the database to query.
604 @type db: DbSession
605 @param progID: Select just deep products with this programme ID.
606 @type progID: int
607 @param reqProdIDs: The list of productIDs
608 @type reqProdIDs: list(int)
609 @param prodType: Product type
610 @type prodType: str
611 @param dateRange: Include data observed between these nights only.
612 @type dateRange: ObsCalendar.DateRange
613 @param allNuStep: If True, select all micro step values.
614 @type allNuStep: bool
615 @param intFrameType: Data type of intermediate frames
616 @type intFrameType: str
617 @param returnAsProdIDDict: Return as dictionary of multiframeIDs for each productID
618 @type returnAsProdIDDict: boolean
619
620
621 @return: The set of intermediate stack multiframe IDs that will go into the
622 given set of deep products.
623 @rtype: set(int)
624
625 """
626 intermediateStacks = set()
627 mfIDprodIDDict = dict()
628 for prod in getProductInfo(db, progID, prodType, reqProdIDs):
629 maxDist = 60 * (prod.stackRadius if prodType != 'mosaic' else
630 max(prod.raExtent, prod.decExtent))
631 mfIDs = getAllFieldObs(db, prod.ra, prod.dec,
632 maxDist, prod.filterID, progID, intFrameType, dateRange,
633 nustep=(prod.nustep if not allNuStep and db.sysc.hasMicroStep else None),
634 offsetPos=(prod.offsetPos if db.sysc.hasOffsetPos and
635 prodType == 'stack' else None), noDeeps=True,
636 getConfToo=getConfToo)
637 intermediateStacks.update(mfIDs)
638 mfIDprodIDDict.update(dict((mfID, prod.productID) for mfID in mfIDs))
639 return mfIDprodIDDict if returnAsProdIDDict else intermediateStacks
640
641
642
646 """
647 Returns SQL query to select multiframe IDs of images of a particular type
648 from a given programme, with the option of restricting to certain field IDs.
649
650 @param programme: Programme to query (object set to required programme).
651 @type programme: DataFactory.ProgrammeTable
652 @param dateRange: Include data observed between these nights only.
653 @type dateRange: ObsCalendar.DateRange
654 @param fieldIDs: Optional number range string of unique field IDs used to
655 restrict selection to certain pointings of the source
656 product type.
657 @type fieldIDs: str
658 @param deprecated: Deprecation clause to use in SQL statement.
659 @type deprecated: str
660 @param prodType: Type of frames to be selected.
661 @type prodType: str
662 @param useDeeps: If True, include deep images.
663 @type useDeeps: bool
664 @param otherDB: Database connection to use if not the programme class
665 @type otherDB DbSession()
666 @return: SQL query that provides the list of multiframeIDs.
667 @rtype: SelectSQL
668
669 """
670 db = programme._db if not otherDB else otherDB
671 programmeID = programme.getAttr("programmeID")
672 sourceProdType = programme.getAttr("sourceProdType")
673 dateSel = "utDate BETWEEN '%s' AND '%s'" % dateRange
674 selType = '%'
675 if prodType != 'all':
676 productTypes = [prodType]
677 selType = '%stack'
678 elif sourceProdType == 'mosaic' and db.sysc.hasOffsetPos:
679 productTypes = ['mosaic', 'tile', 'stack']
680 elif sourceProdType == 'mosaic':
681 productTypes = ['mosaic', 'stack']
682 elif sourceProdType == 'tile':
683 productTypes = ['tile', 'stack']
684 else:
685 productTypes = ['stack']
686
687 fieldIDSel = ("R.fieldID IN (%s) AND " % utils.expandNumberRange(fieldIDs)
688 if fieldIDs else "")
689
690 fGreatCircleDist = \
691 db.tablePath("fGreatCircleDist").replace(db.server + '.', '')
692
693 whereStr = "frameType NOT IN ('dark', 'flat', 'sky') AND "
694 if sourceProdType != 'mosaic':
695 fromStr = "%s AS M, %s AS P, %s AS R" \
696 % (db.tablePath("Multiframe"), db.tablePath("ProgrammeFrame"),
697 db.tablePath("Required" + sourceProdType.title()))
698
699 whereStr += "R.programmeID=%s AND %s%s AND %s AND " \
700 % (programmeID, fieldIDSel, dateSel, deprecated) + \
701 "P.programmeID=R.programmeID AND P.multiframeID=M.multiframeID AND ("
702
703 for ii, productType in enumerate(productTypes):
704 frameTypeSel = getFrameSelection(productType, noDeeps=not useDeeps,
705 selType=selType)
706
707 if productType == 'stack':
708 if productType == sourceProdType:
709 alias = 'R'
710 else:
711 fromStr += ", %s AS S, %s AS L" \
712 % (db.tablePath("RequiredStack"),
713 db.tablePath("ProductLinks"))
714
715 alias = 'S'
716 whereStr = ("R.productID=L.combiProductID AND "
717 "L.combiProdType='%s'" % sourceProdType + " AND "
718 "L.intProdType='stack' AND L.intProductID=S.productID"
719 " AND S.programmeID=L.programmeID AND " + whereStr)
720
721 whereStr += (frameTypeSel + " AND %s.filterID=M.filterID AND "
722 "%s(%s.ra, %s.dec, 15*M.rabase, M.decbase)"
723 " < 60*%s.stackRadius" % (alias, fGreatCircleDist, alias,
724 alias, alias))
725
726 elif productType == 'tile':
727 fromStr += ", %s AS C" % db.tablePath("CurrentAstrometry")
728 whereStr = "C.multiframeID=M.multiframeID AND " + whereStr
729 whereStr += (frameTypeSel + " AND R.filterID=M.filterID AND "
730 "%s(R.ra, R.dec, C.centralRa, C.centralDec)"
731 " < 60*R.stackRadius" % fGreatCircleDist)
732
733 if ii < len(productTypes) - 1:
734 whereStr += " OR "
735 else:
736 whereStr += ")"
737
738 return SelectSQL("DISTINCT M.multiframeID", fromStr, whereStr)
739
740
741
742
743 requiredMosaics = db.query("*", "RequiredMosaic",
744 whereStr=fieldIDSel.replace(" AND ", '').replace("R.", ''))
745
746 mosaicCorners = astro.calcMosaicProductCorners(requiredMosaics)
747 filterOfProduct = dict((mos.productID, mos.filterID)
748 for mos in requiredMosaics)
749
750 whereStr += "programmeID=%s AND %s AND %s AND ("
751 whereStr %= (programmeID, deprecated, dateSel)
752
753
754 filterPositions = []
755 for productID in mosaicCorners:
756 corners = list(mosaicCorners[productID])
757 corners.append(corners[0])
758 halfS = \
759 astro.calcHSpaces(corners, xrange(len(corners) - 1), nCoordInHSDef=2)
760
761 halfSpaces = [halfS[4 * i:4 * (i + 1)] for i in xrange(len(halfS) // 4)]
762
763 posClause = "filterID=%s" % filterOfProduct[productID]
764 for x, y, z, c in halfSpaces:
765
766 posClause += " AND cx*%s+cy*%s+cz*%s<=%s" % (x, y, z, c)
767
768 filterPositions.append(posClause)
769
770 whereStr += " OR ".join(filterPositions) + ")"
771
772 return SelectSQL("DISTINCT M.multiframeID",
773 table=Join([(db.tablePath("ProgrammeFrame"), 'P'),
774 (db.tablePath("Multiframe"), 'M'),
775 (db.tablePath("CurrentAstrometry"), 'A')], "multiframeID"),
776 where=whereStr)
777
778
779
781 """
782 Returns SQL query to select all frame set IDs of a given programme, with
783 the option of restricting to certain field IDs.
784
785 @param programme: Programme to query (object set to required programme).
786 @type programme: DataFactory.ProgrammeTable
787 @param fieldIDs: Optional number range string of unique field IDs used to
788 restrict selection to certain pointings only.
789 @type fieldIDs: str
790
791 @return: SQL query that provides the list of frameSetIDs.
792 @rtype: SelectSQL
793
794 """
795 db = programme._db if not otherDB else otherDB
796 whereStr = ("t.programmeID=%s%s AND t.programmeID=p.programmeID AND "
797 "t.productID=p.productID" % (programme.getAttr("programmeID"),
798 " AND t.fieldID IN (%s)" % utils.expandNumberRange(fieldIDs)
799 if fieldIDs else ""))
800
801 whereList = []
802 for filterID, mfCol in df.PassbandList(programme).getFilterIDsWithMfCols():
803 whereList.append("(t.filterID=%s and p.multiframeID=l.%s)" %
804 (filterID, mfCol))
805
806 whereStr += " AND (" + " or ".join(whereList) + ")"
807
808 tables = tuple(list(map(db.tablePath,
809 ("Required" + programme.getAttr("sourceProdType").title(),
810 "ProgrammeFrame", programme.getMergeLogTable()))))
811
812 return SelectSQL("DISTINCT frameSetID",
813 table="%s AS t, %s AS p, %s AS l" % tables,
814 where=whereStr)
815
816
817
819 """
820 Returns SQL query to select all source IDs of a given programme, with
821 the option of restricting to certain field IDs.
822
823 @param programme: Programme to query (object set to required programme).
824 @type programme: DataFactory.ProgrammeTable
825 @param fieldIDs: Optional number range string of unique field IDs used to
826 restrict selection to certain pointings only.
827 @type fieldIDs: str
828
829 @return: SQL query that provides the list of sourceIDs.
830 @rtype: SelectSQL
831
832 """
833 db = programme._db if not otherDB else otherDB
834 return SelectSQL("sourceID",
835 table=db.tablePath(programme.getSourceTable()),
836 where="frameSetID IN (%s)" % getFrameSetIDs(programme, fieldIDs))
837
838
839
841 """
842 Returns SQL query to select all synoptic frame set IDs of a given programme,
843 with the option of restricting to certain field IDs.
844
845 @param programme: Programme to query (object set to required programme).
846 @type programme: DataFactory.ProgrammeTable
847 @param dateRange: Include data observed between these nights only.
848 @type dateRange: ObsCalendar.DateRange
849 @param fieldIDs: Optional number range string of unique field IDs used to
850 restrict selection to certain pointings only.
851 @type fieldIDs: str
852
853 @return: SQL query that provides the list of synoptic frame set IDs.
854 @rtype: SelectSQL
855
856 """
857
858
859 db = programme._db if not otherDB else otherDB
860 productType = programme.getAttr("epochFrameType")
861 if programme.getAttr("sourceProdType") != productType:
862 raise Exception("Cases where sourceProdType = %s and productType = %s"
863 " are not supported"
864 % (programme.getAttr("sourceProdType"), productType))
865
866 mjdRange = (dateRange.begin.mjd, dateRange.end.mjd)
867 tables = tuple(list(map(db.tablePath,
868 ("Required" + productType.title(), "ProgrammeFrame",
869 programme.getSynopticMergeLogTable()))))
870
871 fGreatCircleDist = \
872 db.tablePath("fGreatCircleDist").replace(db.server + '.', '')
873
874 return SelectSQL("DISTINCT synFrameSetID",
875 table="%s AS t, %s AS p, %s AS l" % tables,
876 where=fGreatCircleDist + "(t.ra,t.dec,l.ra,l.dec) < 60 * t.stackRadius"
877 " AND meanMjdObs BETWEEN %s AND %s" % mjdRange
878 + (" AND t.fieldID IN (%s)" % utils.expandNumberRange(fieldIDs)
879 if fieldIDs else ""))
880
881
882
884 """ ?
885
886 @param db: A connection to the database to query.
887 @type db: DbSession
888 @param reqProdIDs: A sequence of the required productIDs.
889 @type reqProdIDs: sequence(int)
890
891 """
892 if reqProdIDs:
893 subRanges = []
894 indProdIDs = []
895 for subRange in utils.numberRange(reqProdIDs).split(", "):
896 if '-' in subRange:
897 subRange = tuple(subRange.split('-'))
898 subRanges.append("productID BETWEEN %s AND %s" % subRange)
899 else:
900 indProdIDs.append(int(subRange))
901
902 if indProdIDs:
903 indProdIDs = ','.join(map(str, indProdIDs))
904 subRanges.append("productID IN (%s)" % indProdIDs)
905
906 reqProdIDs = " AND (" + " OR ".join(subRanges) + ")"
907
908 return db.query('*', "Required%s" % productType.title(),
909 whereStr="programmeID=%s" % programmeID + reqProdIDs, orderBy="productID")
910
911
912
914 """
915 Selects provenence multiframeIDs.
916
917 @param db: A connection to the database to query.
918 @type db: DbSession
919 @param multiframeID: Multiframe ID to be checked for provenance.
920 @type multiframeID: int
921 @param componentType: type of basic frame
922 @type componentType: str
923
924 @return: A tuple containing the set of all files that combine to give
925 the input image and the set of those which are deprecated
926 @rtype: tuple(set, set)
927
928 """
929 compFrames = []
930 combMfIDs = [multiframeID]
931 while combMfIDs:
932
933
934 compFrames += db.query(
935 selectStr="m.multiframeID",
936 fromStr="Provenance as pv,Multiframe as m",
937 whereStr="pv.multiframeID=m.multiframeID and frameType='%s' and "
938 "combiframeID in ('%s')"
939 % (componentType, ','.join(map(str, combMfIDs))))
940
941 combMfIDs = db.query(
942 selectStr="m.multiframeID",
943 fromStr="Provenance as pv,Multiframe as m",
944 whereStr="pv.multiframeID=m.multiframeID and frameType!='%s' and "
945 "combiframeID in ('%s')"
946 % (componentType, ','.join(map(str, combMfIDs))))
947
948 depComps = set(db.query("multiframeID", "Multiframe", "deprecated>0 and "
949 "multiframeID in (%s)" % ','.join(map(str, compFrames))))
950
951 return compFrames, depComps
952
953
954
956 """
957 Selects provenence multiframeIDs.
958
959 @param db: A connection to the database to query.
960 @type db: DbSession
961 @param deepMfID: Multiframe ID to be checked for provenance.
962 @type deepMfID: int
963 @return: A tuple containing the set of all files that combine to give
964 the input image and the set of those which are deprecated
965 @rtype: tuple(set, set)
966
967 """
968 inputStacks = []
969 areAllBasic = False
970 combMfIDs = [deepMfID]
971 while not areAllBasic:
972
973
974 deepComp = db.query(
975 selectStr="m.multiframeID",
976 fromStr="Provenance as pv,Multiframe as m",
977 whereStr="pv.multiframeID=m.multiframeID and frameType like "
978 "'%%deep%%' and combiframeID in ('%s')"
979 % ','.join(str(mfID) for mfID in combMfIDs))
980
981 basicComp = db.query(
982 selectStr="m.multiframeID",
983 fromStr="Provenance as pv,Multiframe as m",
984 whereStr="pv.multiframeID=m.multiframeID and frameType not like "
985 "'%%deep%%' and combiframeID in ('%s')"
986 % ','.join(str(mfID) for mfID in combMfIDs))
987 inputStacks.extend(basicComp)
988 combMfIDs = deepComp
989 if not combMfIDs:
990 areAllBasic = True
991
992 depComps = set(db.query("multiframeID", "Multiframe", "deprecated>0 and "
993 "multiframeID in (%s)" % ','.join(str(mfID) for mfID in inputStacks)))
994
995 return set(inputStacks), depComps
996
997
998
1000 """
1001 Works out the redo status, based on the list of products to be redone and
1002 their descendants.
1003
1004 @param db: A connection to the database to query.
1005 @type db: DbSession
1006 @param programmeID: Query the programme with this unique programmeID.
1007 @type programmeID: int
1008 @param redoProducts: List of product types to redo.
1009 @type redoProducts: list(str)
1010
1011 @return: A dictionary giving the redo status for a given product type.
1012 @rtype: dict(str: bool)
1013
1014 """
1015 if not redoProducts:
1016 return dict((pType, False) for pType in db.sysc.productTypes)
1017
1018 plDict = dict(db.query("DISTINCT combiProdType, intProdType",
1019 "ProductLinks", "programmeID=%s" % programmeID))
1020
1021 redoStatus = {}
1022 for prodType in db.sysc.productTypes:
1023 if prodType in redoProducts:
1024 redoStatus[prodType] = True
1025 elif plDict.get(prodType) in redoProducts:
1026 redoStatus[prodType] = True
1027
1028
1029 redoProducts.append(prodType)
1030 else:
1031 redoStatus[prodType] = False
1032
1033 return redoStatus
1034
1035
1036
1037 -def getRequiredProductIDs(programme, minProdID=None, maxProdID=None,
1038 reqProdIDs=None, fieldIDs=None, productType=None,
1039 whereStr="", otherDB=None):
1040 """
1041 Find required products of given type. If no type given then just the
1042 products that contribute towards the source table are returned.
1043
1044 @param programme: Programme table for current database with the current
1045 row set to that of the programme to be curated.
1046 @type programme: DataFactory.ProgrammeTable
1047 @param minProdID: Restrict to product IDs with this minimum value.
1048 @type minProdID: int
1049 @param maxProdID: Restrict to product IDs with this maximum value.
1050 @type maxProdID: int
1051 @param reqProdIDs: Restrict to product IDs in this list.
1052 @type reqProdIDs: sequence(int)
1053 @param fieldIDs: List of fieldIDs to curate.
1054 @type fieldIDs: list(str)
1055 @param productType: Optional product type for which to return product IDs,
1056 defaults to programme's sourceProdType.
1057 @type productType: str
1058 @param whereStr: Optional additional selection criteria.
1059 @type whereStr: str
1060
1061 @return: A list of product IDs to create of this product type for this
1062 programme.
1063 @rtype: list(int)
1064
1065 """
1066 db = programme._db if not otherDB else otherDB
1067 productType = productType or programme.getAttr("sourceProdType")
1068 if whereStr:
1069 whereStr += " AND "
1070
1071 whereStr += "programmeID=%s" % programme.getAttr("programmeID")
1072
1073 if reqProdIDs:
1074 whereStr += " AND productID IN (%s)" % ','.join(reqProdIDs)
1075
1076 if minProdID and maxProdID:
1077 whereStr += " AND productID BETWEEN %s AND %s" % (minProdID, maxProdID)
1078
1079 elif minProdID:
1080 whereStr += " AND productID >= %s" % minProdID
1081
1082 elif maxProdID:
1083 whereStr += " AND productID <= %s" % maxProdID
1084
1085 if fieldIDs:
1086 whereStr += " AND fieldID IN (%s)" % ','.join(fieldIDs)
1087
1088 return db.query("productID", "Required%s" % productType.title(), whereStr,
1089 orderBy=("ra, dec" if productType == 'mosaic' else "productID"))
1090
1091
1092
1093 -def getStacks(db, programmeID, inputImages, prodID,
1094 onlyMfIDs=False, number=None, nonDepOnly=True, productMfID=None):
1095 """
1096 Queries database for combined stacks for all the given normal images.
1097
1098 @param db: A connection to the database to query.
1099 @type db: DbSession
1100 @param programmeID: The programmeID that contains the product
1101 @type programmeID: int
1102 @param inputImages: The input images that match the selection criteria
1103 @type inputImages: set(int)
1104 @param prodID: ProductID of deep stack. If None, do not use
1105 @type prodID: int
1106 @param onlyMfIDs: If True, only return the multiframe IDs of the stacks.
1107 @type onlyMfIDs: bool
1108 @param number: Optionally restrict the number of intermediate stacks
1109 per deep stack to a specified number.
1110 @type number: int
1111 @param nonDepOnly: If True, only select stacks where all extensions
1112 are non-deprecated.
1113 @type nonDepOnly: bool
1114 @param doNotUpdate: If True, do not update the database
1115 @type doNotUpdate: bool
1116
1117 @return: Stack multiframe IDs and optionally also the ID of the confidence
1118 images, the file names, the frame types and the totalExpTime,
1119 together with a set of multiframe IDs that were not found.
1120 @rtype: set(int) or tuple(set(tuple), set(int))
1121
1122 """
1123 finalImages = set()
1124 if prodID:
1125 if not productMfID:
1126 frameTypeSel = getFrameSelection('stack', deepOnly=True)
1127 productMfID = db.query(
1128 selectStr="m.multiframeID",
1129 fromStr="ProgrammeFrame as p,Multiframe as m, RequiredStack as s",
1130 whereStr="s.productID=%s AND s.programmeID=p.programmeID AND "
1131 "m.multiframeID=p.multiframeID AND %s AND "
1132 "deprecated IN (%s,%s) AND s.programmeID=%d AND "
1133 "m.filterID=s.filterID AND dbo.fGreatCircleDist(s.ra, "
1134 "s.dec, 15*rabase, decbase) < 60*s.stackRadius"
1135 % (prodID, frameTypeSel, DepCodes.nonDep,
1136 DepCodes.reprocWFAU, programmeID),
1137 orderBy="m.multiframeID DESC",
1138 firstOnly=True, default=0)
1139
1140
1141
1142 if productMfID > 0:
1143 components, depComp = getProvenence(db, productMfID)
1144
1145 extras = components.difference(inputImages)
1146 if productMfID == 0 or extras or depComp:
1147
1148
1149 finalImages.update(inputImages)
1150 else:
1151 newImages = inputImages.difference(components)
1152 finalImages = list(newImages)
1153 finalImages.append(productMfID)
1154 finalImages = set(finalImages)
1155 db.update("Multiframe",
1156 entryList=[("deprecated", 0)],
1157 where="multiframeID=%s and deprecated=%s"
1158 % (productMfID, DepCodes.reprocWFAU))
1159 db.update("MultiframeDetector",
1160 entryList=[("deprecated", 0)],
1161 where="multiframeID=%s and deprecated=%s"
1162 % (productMfID, DepCodes.reprocWFAU))
1163 db.update("Multiframe",
1164 entryList=[("deprecated", 0)],
1165 where="multiframeID in (select confID from Multiframe "
1166 "where multiframeID=%s) and deprecated=%s"
1167 % (productMfID, DepCodes.reprocWFAU))
1168 db.update("MultiframeDetector",
1169 entryList=[("deprecated", 0)],
1170 where="multiframeID in (select confID from Multiframe "
1171 "where multiframeID=%s) and deprecated=%s"
1172 % (productMfID, DepCodes.reprocWFAU))
1173 else:
1174 finalImages.update(inputImages)
1175
1176
1177 imgList = ','.join(str(img) for img in finalImages)
1178 top = ("TOP %s " % number if number else "")
1179 attrs = "mf.multiframeID as multiframeID" + ("" if onlyMfIDs else
1180 ", mf.fileName as fileName, mf2.fileName as confName, "
1181 "mf.catName as catName")
1182 grpby = "mf.multiframeID" + ("" if onlyMfIDs else
1183 ", mf.fileName, mf2.fileName, mf.catName")
1184
1185
1186 bestImages = set(db.query(
1187 selectStr=top + attrs,
1188 fromStr="Multiframe AS mf, CurrentAstrometry AS ca, "
1189 "MultiframeDetector AS mfd,Multiframe as mf2",
1190 whereStr="mf.multiframeID=mfd.multiframeID AND "
1191 "ca.multiframeID=mfd.multiframeID AND ca.extNum=mfd.extNum "
1192 "AND mf.frameType LIKE '%%stack' AND mf.multiframeID IN (%s) "
1193 "AND mf2.multiframeID=mf.confID "
1194 "GROUP BY %s" % (imgList, grpby) +
1195 (" HAVING MAX(mfd.deprecated)=0" if nonDepOnly else "") +
1196 (" ORDER BY MAX(mfd.deprecated),AVG(ca.xPixSize*mfd.seeing)"
1197 if number else '')))
1198
1199 if onlyMfIDs:
1200 return bestImages
1201 else:
1202 problemImages = finalImages - set(img[0] for img in bestImages)
1203 return bestImages, problemImages
1204
1205
1206