def findNClosest(alpha, delta, tableDef, n, fields, searchRadius=5):
"""returns the n objects closest around alpha, delta in table.
n is the number of items returned, with the closest ones at the
top, fields is a sequence of desired field names, searchRadius
is a radius for the initial q3c search and will need to be
lowered for dense catalogues and possibly raised for sparse ones.
The last item of each row is the distance of the object from
the query center in degrees.
The query depends on postgastro extension (and should be changed to
use pgsphere). It also requires the q3c extension.
"""
with base.AdhocQuerier(base.getTableConn) as q:
raField = tableDef.getColumnByUCDs("pos.eq.ra;meta.main",
"POS_EQ_RA_MAIN").name
decField = tableDef.getColumnByUCDs("pos.eq.dec;meta.main",
"POS_EQ_RA_MAIN").name
res = q.query(
"SELECT %s,"
" celDistDD(%s, %s, %%(alpha)s, %%(delta)s) as dist_"
" FROM %s WHERE"
" q3c_radial_query(%s, %s, %%(alpha)s, %%(delta)s,"
" %%(searchRadius)s)"
" ORDER BY dist_ LIMIT %%(n)s" %
(",".join(fields), raField, decField, tableDef.getQName(), raField,
decField), locals()).fetchall()
return res
def printTableSize(self):
try:
tableName = self.dd.makes[0].table.getQName()
with base.AdhocQuerier(base.getAdminConn) as q:
itemsInDB = list(q.query("SELECT count(*) from %s"%tableName))[0][0]
print "Items currently in assumed database table: %d\n"%itemsInDB
except (base.DBError, IndexError):
pass
def localquery():
"""run the argument as an ADQL query.
"""
from gavo import rscdesc #noflake: cache registration
from gavo import formats
q = sys.argv[1]
with base.AdhocQuerier() as querier:
table = query(querier, q, timeout=1000)
formats.formatData("votable", table, sys.stdout)
def updateRegistryTimestamp():
"""edits the dateupdated field for the registry service in servicelist.
"""
with base.AdhocQuerier(base.getAdminConn) as q:
regSrv = common.getRegistryService()
q.query("UPDATE services SET dateupdated=%(now)s"
" WHERE sourcerd=%(rdId)s AND resId=%(sId)s", {
"rdId": regSrv.rd.sourceId,
"sId": regSrv.id,
"now": datetime.datetime.utcnow(),
})
common.getServicesRD().touchTimestamp()
def run(self, service, inputTable, queryMeta): fragment, pars = self._getSQLWhere(inputTable, queryMeta) with base.AdhocQuerier(base.getTableConn) as querier: if fragment: fragment = " WHERE "+fragment else: fragment = "" try: return self._makeTable( querier.queryDicts(self.query%fragment, pars, timeout=queryMeta["timeout"]), self.outputTable, queryMeta) except: mapDBErrors(*sys.exc_info())
def getTableDef(tableName):
"""returns a tableDef instance for the schema-qualified tableName.
If no such table is known to the system, a NotFoundError is raised.
"""
with base.AdhocQuerier(base.getTableConn) as q:
res = list(q.query("SELECT tableName, sourceRD FROM dc.tablemeta WHERE"
" LOWER(tableName)=LOWER(%(tableName)s)", {"tableName": tableName}))
if len(res)!=1:
raise base.NotFoundError(tableName, what="table",
within="data center table listing.", hint="The table is missing from"
" the dc.tablemeta table. This gets filled at gavoimp time.")
tableName, rdId = res[0]
return base.caches.getRD(rdId).getById(basename(tableName))
def run(self, service, inputTable, queryMeta): if self.writable: connFactory = base.getWritableTableConn else: connFactory = base.getTableConn with base.AdhocQuerier(connFactory) as querier: try: cursor = querier.query( self.rd.expand(self.query), timeout=self.timeout) if cursor.description is None: return self._parseOutput([], queryMeta) else: return self._parseOutput(list(cursor), queryMeta) except: mapDBErrors(*sys.exc_info())
def _do_dropRD(opts, rdId, selectedIds=()):
"""drops the data and services defined in the RD selected by rdId.
"""
try:
rd = api.getReferencedElement(rdId, forceType=api.RD)
except api.NotFoundError:
if opts.force:
rd = None
else:
raise
with base.AdhocQuerier(base.getWritableAdminConn) as querier:
if rd is not None:
if opts.dropAll:
dds = rd.dds
else:
dds = common.getPertainingDDs(rd, selectedIds)
parseOptions = api.getParseOptions(systemImport=opts.systemImport)
for dd in dds:
api.Data.drop(dd,
connection=querier.connection,
parseOptions=parseOptions)
if not selectedIds or opts.dropAll:
from gavo.registry import servicelist
servicelist.cleanServiceTablesFor(rd, querier.connection)
tap.unpublishFromTAP(rd, querier.connection)
try:
with querier.connection.savepoint():
querier.query("drop schema %s" % rd.schema)
except Exception, msg:
api.ui.notifyWarning(
"Cannot drop RD %s's schema %s because:"
" %s." % (rd.sourceId, rd.schema, utils.safe_str(msg)))
else:
def run(self, service, inputTable, queryMeta):
inRow = inputTable.getParamDict()
queryString = inRow["query"]
base.ui.notifyInfo("Incoming ADQL query: %s" % queryString)
try:
with base.AdhocQuerier(base.getUntrustedConn) as querier:
res = query(querier,
queryString,
timeout=queryMeta["timeout"],
hardLimit=100000)
# XXX Warning: We're returning the db connection to the connection
# pool here while we still have a named cursor on it. This is
# risky because someone might fuzz with our connection later.
# However, postponing the return of the connection isn't nice
# either because then the renderer would have to manage the core's
# connections, which is ugly, too.
# I'm a bit at a loss for a good solution here. Let's see how
# well the "don't care" scheme works out. Maybe we need a "renderer closes
# connection" plan for this kind of streaming?
res.noPostprocess = True
queryMeta["Matched"] = len(res.rows)
return res
except:
mapADQLErrors(*sys.exc_info())
def main():
with base.AdhocQuerier(base.getWritableAdminConn) as querier:
args = makeParser(globals()).parse_args()
args.subAction(querier, args)