def _test_adql_mysql_translation_parsing(self,
query,
columns=None,
keywords=None,
functions=None,
display_columns=None):
adt = ADQLQueryTranslator()
qp = MySQLQueryProcessor()
adt.set_query(query)
qp.set_query(adt.to_mysql())
qp.process_query()
qp_columns = [
'.'.join([str(j) for j in i]) for i in qp.columns
if i[0] is not None and i[1] is not None
]
qp_display_columns = [
'%s: %s' % (str(i[0]), '.'.join([str(j) for j in i[1]]))
for i in qp.display_columns
]
if columns:
self.assertSetEqual(set(columns), set(qp_columns))
if keywords:
self.assertSetEqual(set(keywords), set(qp.keywords))
if functions:
self.assertSetEqual(set(functions), set(qp.functions))
if display_columns:
self.assertSetEqual(set(display_columns), set(qp_display_columns))
def _test_adql_mysql_translation_parsing(self, query, columns=None,
keywords=None, functions=None,
display_columns=None):
adt = ADQLQueryTranslator()
qp = MySQLQueryProcessor()
adt.set_query(query)
qp.set_query(adt.to_mysql())
qp.process_query()
qp_columns = ['.'.join([str(j) for j in i]) for i in qp.columns
if i[0] is not None and i[1] is not None]
qp_display_columns = ['%s: %s' % (str(i[0]),
'.'.join([str(j) for j in i[1]]))
for i in qp.display_columns]
if columns:
self.assertSetEqual(set(columns), set(qp_columns))
if keywords:
self.assertSetEqual(set(keywords), set(qp.keywords))
if functions:
self.assertSetEqual(set(functions), set(qp.functions))
if display_columns:
self.assertSetEqual(set(display_columns), set(qp_display_columns))
def f1():
query = """
SELECT DISTANCE(
POINT('ICRS', ra, dec),
POINT('ICRS', 266.41683, -29.00781)
) AS dist
FROM gaiadr1.gaia_source
WHERE 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333)
)
AND x < 1
OR 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 66.41683, -29.00781, 0.08333333)
)
ORDER BY dist ASC
"""
query = """
SELECT *
FROM gdr1.gaia_source
WHERE 1=CONTAINS(
POINT('ICRS',ra,dec),
CIRCLE('ICRS',266.41683,-29.00781, 0.08333333)
)
AND phot_g_mean_mag>=10 AND phot_g_mean_mag<15
ORDER BY phot_g_mean_mag ASC
"""
query = """
SELECT gmag * 0.1 AS gmag_bin, COUNT(gmag) AS number
FROM
(
SELECT FLOOR(phot_g_mean_mag * 10) AS gmag
FROM gdr1.gaia_source
) AS gmag_tab
GROUP BY gmag;
"""
adt = ADQLQueryTranslator(query)
pgq = adt.to_postgresql()
print(pgq)
qp = PostgreSQLQueryProcessor()
qp.set_query(pgq)
qp.process_query()
print(qp.columns)
print(qp.display_columns)
print(qp.tables)
print(qp.functions)
def f1():
query = """
SELECT DISTANCE(
POINT('ICRS', ra, dec),
POINT('ICRS', 266.41683, -29.00781)
) AS dist
FROM gaiadr1.gaia_source
WHERE 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333)
)
AND x < 1
OR 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 66.41683, -29.00781, 0.08333333)
)
ORDER BY dist ASC
"""
query = """
SELECT *
FROM gdr1.gaia_source
WHERE 1=CONTAINS(
POINT('ICRS',ra,dec),
CIRCLE('ICRS',266.41683,-29.00781, 0.08333333)
)
AND phot_g_mean_mag>=10 AND phot_g_mean_mag<15
ORDER BY phot_g_mean_mag ASC
"""
query = """
SELECT gmag * 0.1 AS gmag_bin, COUNT(gmag) AS number
FROM
(
SELECT FLOOR(phot_g_mean_mag * 10) AS gmag
FROM gdr1.gaia_source
) AS gmag_tab
GROUP BY gmag;
"""
adt = ADQLQueryTranslator(query)
pgq = adt.to_postgresql()
print(pgq)
qp = PostgreSQLQueryProcessor()
qp.set_query(pgq)
qp.process_query()
print(qp.columns)
print(qp.display_columns)
print(qp.tables)
print(qp.functions)
def test_adql_translation(qs):
adt = ADQLQueryTranslator()
for q in qs:
adt.set_query(q)
s = time.time()
try:
translated_query = adt.to_mysql()
syntax_errors = []
except QuerySyntaxError as e:
syntax_errors = e.syntax_errors
s = time.time() - s
if len(syntax_errors):
print(syntax_errors)
print('Done in %.4fs' % s)
print('input: ', q)
print('output: ', translated_query)
print()
def f4():
query = """SELECT ra FROM gdr2.gaia_source AS gaia WHERE 1=CONTAINS(POINT('ICRS', gaia.ra, gaia.dec), CIRCLE('ICRS', 245.8962, -26.5222, 0.5))"""
adt = ADQLQueryTranslator(query)
pgq = adt.to_postgresql()
iob = {
'spoint': ((('gdr2', 'gaia_source', 'ra'), ('gdr2', 'gaia_source',
'dec'), 'pos'), )
}
qp = PostgreSQLQueryProcessor()
qp.set_query(pgq)
qp.process_query(indexed_objects=iob)
print('iob', iob)
print('oq', query)
print('tq', pgq)
print(' q', qp.query)
def test_translated_mysql_parsing(qs):
adt = ADQLQueryTranslator()
qp = MySQLQueryProcessor()
for q in qs:
s = time.time()
adt.set_query(q[0])
translated_query = adt.to_mysql()
print(translated_query)
qp.set_query(translated_query)
try:
qp.process_query()
syntax_errors = []
except QuerySyntaxError as e:
syntax_errors = e.syntax_errors
s = time.time() - s
# cols, keys, funcs = qp.columns, qp.keywords, qp.functions
# s = 0.0
# not_so_pretty_print(translated_query, cols, keys, funcs, s,
# qp.syntax_errors)
cols, keys, funcs, dispcols= qp.columns, qp.keywords, qp.functions, \
qp.display_columns
pretty_print(q, cols, keys, funcs, dispcols, s, syntax_errors,
show_diff=False)
def translate_query(query_language, query):
# get the adapter
adapter = DatabaseAdapter()
# translate adql -> mysql string
if query_language == 'adql-2.0':
try:
translator = cache.get_or_set('translator', ADQLQueryTranslator(),
3600)
translator.set_query(query)
if adapter.database_config['ENGINE'] == 'django.db.backends.mysql':
return translator.to_mysql()
elif adapter.database_config[
'ENGINE'] == 'django.db.backends.postgresql':
return translator.to_postgresql()
else:
raise Exception('Unknown database engine')
except QuerySyntaxError as e:
raise ValidationError({
'query': {
'messages': [
_('There has been an error while translating your query.'
)
],
'positions':
json.dumps(e.syntax_errors),
}
})
except QueryError as e:
raise ValidationError({'query': {
'messages': e.messages,
}})
else:
return query
def submit(self,
query_language,
query,
queue,
table_name,
user,
sync=False):
"""
Submit a query to the job management and the query backend.
"""
# check if a table with that name already exists
errors = self._check_table(table_name)
if errors:
raise TableError(errors)
# translate adql -> mysql string
if query_language == 'adql':
try:
adt = ADQLQueryTranslator(query)
actual_query = adt.to_mysql()
except RuntimeError as e:
raise ADQLSyntaxError(str(e))
else:
actual_query = query.strip(';')
# parse the query
qp = MySQLQueryProcessor(actual_query)
qp.process_query()
# check for syntax errors
if qp.syntax_errors:
raise MySQLSyntaxError(qp.syntax_errors)
# check permissions on keywords, databases, tables, columns, and functions
errors = self._check_permissions(user, qp)
if errors:
raise PermissionError(errors)
# store statistics/meta information
# todo
job = self.model(query_language=query_language,
query=query,
actual_query=actual_query,
owner=user,
database_name=get_user_database_name(user.username),
table_name=table_name,
queue=queue,
phase=PHASE_QUEUED,
creation_time=now(),
job_type=Job.JOB_TYPE_QUERY)
job.save()
# start the submit_query task in a syncronous or asuncronous way
job_id = str(job.id)
if not settings.ASYNC or sync:
submit_query.apply((job_id, ), task_id=job_id)
else:
submit_query.apply_async((job_id, ),
task_id=job_id,
queue=job.queue)
return job.id
def f2():
query = """
SELECT TOP 100 ra, dec
FROM "gdr1".tgas_source AS tgas
WHERE 1=CONTAINS( POINT('ICRS', tgas.ra, tgas.dec),
POLYGON('ICRS', 21.480, -47.354, 21.697,-47.229, 21.914,-47.354,
21.914,-47.604, 21.697,-47.729, 21.480, -47.604) )
"""
query = """
SELECT ra, dec, DISTANCE( POINT('ICRS', gaia.ra, dec),
POINT('ICRS', 200, 45) ) AS dist
FROM gdr1.gaia_source AS gaia
WHERE 1 = CONTAINS( POINT('ICRS', ra, dec), CIRCLE('ICRS', 200, 45, 60) )
"""
query = """
select gaia.source_id,
gaia.parallax
from gdr1.tgas_source as gaia
where 1=contains(point('ICRS',gaia.ra,gaia.dec),circle('ICRS',56.75,24.12,5))
and sqrt(power(gaia.pmra-20.5,2)+power(gaia.pmdec+45.5,2)) < 6.0
"""
query = """
select t1.source_id,t1.ra, t1.dec, t1."phot_rp_mean_mag", t1.bp_rp, t1.bp_g ,
t1."radial_velocity" ,t1."teff_val",
t2."mean_obs_time_g_fov",t2."mean_mag_g_fov",t2."mean_mag_bp",
t2."time_duration_rp",t2."num_selected_rp"
FROM "gdr2"."gaia_source" as t1, "gdr2"."vari_time_series_statistics" as t2
WHERE t1."source_id" = t2."source_id"
ORDER BY t1.source_id;
"""
query = """
SELECT source_id
FROM gaiadr2.aux_allwise_agn_gdr2_cross_id
JOIN gaiadr2.gaia_source USING (source_id);
"""
query = """
SELECT gaia2.source_id
FROM gdr2.gaia_source AS gaia2, gdr2.sdssdr9_best_neighbour AS grd2_rv5
WHERE gaia2.source_id = grd2_rv5.source_id
AND 1 = CONTAINS(POINT('ICRS', gaia2.ra, gaia2.dec), CIRCLE('ICRS' ,080.8942, -69.7561, 0.5))
"""
query = """
SELECT a FROM db.tab WHERE p = 'AAA';
"""
query = """
-- multidimensional matrices can be parsed too
SELECT arr[1:3][1][2][3][4] FROM db.phot;
"""
# query='SELECT * FROM db.c, db.d'
query = """SELECT ra FROM gdr2.gaia_source AS gaia
WHERE 1=CONTAINS(POINT('ICRS', gaia.ra, gaia.dec),
CIRCLE('ICRS', 245.8962, -26.5222, 0.5))"""
query = """
SELECT gaia.source_id, gaia.ra, gaia.dec, gd.r_est
FROM gdr2.gaia_source gaia, gdr2_contrib.geometric_distance gd
WHERE 1 = CONTAINS(POINT('ICRS', gaia.ra, gaia.dec),
CIRCLE('ICRS',245.8962, -26.5222, 0.5))
AND gaia.phot_g_mean_mag < 15
AND gd.r_est > 1500 AND gd.r_est < 2300
AND gaia.source_id = gd.source_id
"""
print(query)
adt = ADQLQueryTranslator(query)
# st = time.time()
pgq = adt.to_postgresql()
# st = time.time()
# print(pgq)
iob = {
'spoint': ((('gdr2', 'gaia_source', 'ra'), ('gdr2', 'gaia_source',
'dec'), 'pos'), )
}
# (('gdr2', 'gaia_source', 'ra'),
# ('gdr1', 'gaia_source', 'dec'), 'pos'))}
print('iob', iob)
# qp = PostgreSQLQueryProcessor()
# qp.set_query(query)
# qp.process_query()
# st = time.time()
# pgq = qp.query
qp = PostgreSQLQueryProcessor()
qp.set_query(pgq)
qp.process_query(indexed_objects=iob)
# qp.process_query()
print('iob', iob)
print('q', qp.query)
def f1():
query = """
SELECT DISTANCE(
POINT('ICRS', ra, dec),
POINT('ICRS', 266.41683, -29.00781)
) AS dist
FROM gaiadr1.gaia_source
WHERE 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333)
)
AND x < 1
OR 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 66.41683, -29.00781, 0.08333333)
)
ORDER BY dist ASC
"""
query = """
SELECT *
FROM gdr1.gaia_source
WHERE 1=CONTAINS(
POINT('ICRS',ra,dec),
CIRCLE('ICRS',266.41683,-29.00781, 0.08333333)
)
AND phot_g_mean_mag>=10 AND phot_g_mean_mag<15
ORDER BY phot_g_mean_mag ASC
"""
query = """
SELECT gmag * 0.1 AS gmag_bin, COUNT(gmag) AS number
FROM
(
SELECT FLOOR(phot_g_mean_mag * 10) AS gmag
FROM gdr1.gaia_source
) AS gmag_tab
GROUP BY gmag;
"""
query = '''
SELECT gaia.source_id, gaia.ra, gaia.dec, gd.r_est
FROM gdr2.gaia_source gaia, gdr2_contrib.geometric_distance gd
WHERE 1 = CONTAINS(POINT('ICRS', gaia.ra, gaia.dec),
CIRCLE('ICRS',245.8962, -26.5222, 0.5))
AND gaia.phot_g_mean_mag < 15
AND gd.r_est > 1500 AND gd.r_est < 2300
AND gaia.source_id = gd.source_id
'''
adt = ADQLQueryTranslator(query)
pgq = adt.to_postgresql()
print(pgq)
return
qp = PostgreSQLQueryProcessor()
# qp.set_query(pgq)
qp.set_query(query)
qp.process_query()
print(qp.columns)
print(qp.display_columns)
print(qp.tables)
print(qp.functions)
print(qp.keywords)
def _test_adql_postgresql_translation(self, query, adql_query=None):
adt = ADQLQueryTranslator(query)
if adql_query:
self.assertEqual(adql_query.strip(), adt.to_postgresql())
def _test_adql_mysql_translation(self, query, adql_query=None):
adt = ADQLQueryTranslator(query)
if adql_query:
self.assertEqual(adql_query.strip(), adt.to_mysql())
def test_query_error_001(self):
adt = ADQLQueryTranslator()
with self.assertRaises(QueryError):
adt.to_mysql()
def f2():
query = """
SELECT TOP 100 ra, dec
FROM "gdr1".tgas_source AS tgas
WHERE 1=CONTAINS( POINT('ICRS', tgas.ra, tgas.dec),
POLYGON('ICRS', 21.480, -47.354, 21.697,-47.229, 21.914,-47.354,
21.914,-47.604, 21.697,-47.729, 21.480, -47.604) )
"""
query = """
SELECT ra, dec, DISTANCE( POINT('ICRS', gaia.ra, dec),
POINT('ICRS', 200, 45) ) AS dist
FROM gdr1.gaia_source AS gaia
WHERE 1 = CONTAINS( POINT('ICRS', ra, dec), CIRCLE('ICRS', 200, 45, 60) )
"""
query = """
select gaia.source_id,
gaia.parallax
from gdr1.tgas_source as gaia
where 1=contains(point('ICRS',gaia.ra,gaia.dec),circle('ICRS',56.75,24.12,5))
and sqrt(power(gaia.pmra-20.5,2)+power(gaia.pmdec+45.5,2)) < 6.0
"""
query = """
select t1.source_id,t1.ra, t1.dec, t1."phot_rp_mean_mag", t1.bp_rp, t1.bp_g ,
t1."radial_velocity" ,t1."teff_val",
t2."mean_obs_time_g_fov",t2."mean_mag_g_fov",t2."mean_mag_bp",
t2."time_duration_rp",t2."num_selected_rp"
FROM "gdr2"."gaia_source" as t1, "gdr2"."vari_time_series_statistics" as t2
WHERE t1."source_id" = t2."source_id"
ORDER BY t1.source_id;
"""
query = """
SELECT source_id
FROM gaiadr2.aux_allwise_agn_gdr2_cross_id
JOIN gaiadr2.gaia_source USING (source_id);
"""
query = """
SELECT gaia2.source_id
FROM gdr2.gaia_source AS gaia2, gdr2.sdssdr9_best_neighbour AS grd2_rv5
WHERE gaia2.source_id = grd2_rv5.source_id
AND 1 = CONTAINS(POINT('ICRS', gaia2.ra, gaia2.dec), CIRCLE('ICRS' ,080.8942, -69.7561, 0.5))
"""
query = """
SELECT a FROM db.tab WHERE p = 'AAA';
"""
# query = """SELECT ra FROM gdr2.gaia_source AS gaia
# WHERE 1=CONTAINS(POINT('ICRS', gaia.ra, gaia.dec),
# CIRCLE('ICRS', 245.8962, -26.5222, 0.5))"""
adt = ADQLQueryTranslator(query)
st = time.time()
pgq = adt.to_postgresql()
st = time.time()
# print(pgq)
iob = {'spoint': ((('gdr2', 'gaia_source', 'ra'),
('gdr2', 'gaia_source', 'dec'), 'pos'),
(('gdr1', 'gaia_source', 'ra'),
('gdr1', 'gaia_source', 'dec'), 'pos'))}
qp = PostgreSQLQueryProcessor(indexed_objects = iob)
qp.set_query(pgq)
print(pgq)
qp.process_query()
st = time.time()
pgq = qp.query
qp = PostgreSQLQueryProcessor()
qp.set_query(pgq)
qp.process_query()
print(qp.query)
print(qp.columns)
def test_query_error_001(self):
adt = ADQLQueryTranslator()
with self.assertRaises(QueryError):
adt.to_mysql()