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_mysql_parsing(self, query, columns=None, keywords=None,
functions=None, display_columns=None, tables=None,
replace_schema_name=None):
if replace_schema_name is None:
qp = MySQLQueryProcessor(query)
else:
qp = MySQLQueryProcessor()
qp.set_query(query)
qp.process_query(replace_schema_name=replace_schema_name)
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]
qp_tables = ['.'.join([str(j) for j in i]) for i in qp.tables
if i[0] is not None and i[1] is not None]
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))
if tables:
self.assertSetEqual(set(tables), set(qp_tables))
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 f3():
query = """
SELECT COUNT(*) AS n, id, mra, mlem AS qqq, blem
FROM (
SELECT inner1.id, mra, mlem,
inner2.col3 + inner2.parallax AS blem
FROM (
SELECT qwerty.id, MAX(ra) AS mra, inner1.parallax,
qwerty.mlem mlem
FROM db.tab dbt
JOIN (
SELECT rekt AS parallax, id, mlem
FROM db.bar
) AS qwerty USING (id)
) AS inner1
JOIN (
SELECT qqq, col2 AS ra2, parallax, subsub.col3
FROM (
SELECT ra AS qqq, col2, col3, parallax
FROM db.gaia AS gaia
WHERE col5 > 5
) AS subsub
) AS inner2
ON inner1.parallax = inner2.parallax
) AS subq
GROUP BY id;
"""
qp = MySQLQueryProcessor()
qp.set_query(query)
qp.process_query()
print(qp.query)
print(qp.columns)
print(qp.display_columns)
print(qp.tables)
print(qp.keywords)
print(qp.functions)
def _test_mysql_parsing(self,
query,
columns=None,
keywords=None,
functions=None,
display_columns=None,
tables=None,
replace_schema_name=None):
if replace_schema_name is None:
qp = MySQLQueryProcessor(query)
else:
qp = MySQLQueryProcessor()
qp.set_query(query)
qp.process_query(replace_schema_name=replace_schema_name)
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
]
qp_tables = [
'.'.join([str(j) for j in i]) for i in qp.tables
if i[0] is not None and i[1] is not None
]
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))
if tables:
self.assertSetEqual(set(tables), set(qp_tables))
def f3():
query = """
SELECT COUNT(*) AS n, id, mra, mlem AS qqq, blem
FROM (
SELECT inner1.id, mra, mlem,
inner2.col3 + inner2.parallax AS blem
FROM (
SELECT qwerty.id, MAX(ra) AS mra, inner1.parallax,
qwerty.mlem mlem
FROM db.tab dbt
JOIN (
SELECT rekt AS parallax, id, mlem
FROM db.bar
) AS qwerty USING (id)
) AS inner1
JOIN (
SELECT qqq, col2 AS ra2, parallax, subsub.col3
FROM (
SELECT ra AS qqq, col2, col3, parallax
FROM db.gaia AS gaia
WHERE col5 > 5
) AS subsub
) AS inner2
ON inner1.parallax = inner2.parallax
) AS subq
GROUP BY id;
"""
query = """
SELECT t1.a, t2.b, t3.c, t4.z
FROM d.tab t1, `db2`.`tab` t2, foo.tab t3, x.y t4
"""
qp = MySQLQueryProcessor()
qp.set_query(query)
qp.process_query(replace_schema_name={'d': 'foo', 'db2': 'bar', 'foo': 'bas'})
print(qp.query)
for i in qp.columns:
print(i)
print(qp.keywords)
print(qp.functions)
print(qp.display_columns)
print(qp.tables)
def test_mysql_parsing(qs):
qp = MySQLQueryProcessor()
for q in qs:
qp.set_query(q[0])
s = time.time()
try:
qp.process_query()
# qp.process_query()
syntax_errors = []
except QuerySyntaxError as e:
syntax_errors = e.syntax_errors
s = time.time() - s
# continue
qm = list(q)
qm[0] = '\n' + qp.query + '\n'
cols, keys, funcs, dispcols= qp.columns, qp.keywords, qp.functions, \
qp.display_columns
# not_so_pretty_print(q[0], cols, keys, funcs, s, qp.syntax_errors)
pretty_print(qm, cols, keys, funcs, dispcols, s, syntax_errors,
show_diff=True)
print(qp.tables)
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 f3():
query = """
SELECT COUNT(*) AS n, id, mra, mlem AS qqq, blem
FROM (
SELECT inner1.id, mra, mlem,
inner2.col3 + inner2.parallax AS blem
FROM (
SELECT qwerty.id, MAX(ra) AS mra, inner1.parallax,
qwerty.mlem mlem
FROM db.tab dbt
JOIN (
SELECT rekt AS parallax, id, mlem
FROM db.bar
) AS qwerty USING (id)
) AS inner1
JOIN (
SELECT qqq, col2 AS ra2, parallax, subsub.col3
FROM (
SELECT ra AS qqq, col2, col3, parallax
FROM db.gaia AS gaia
WHERE col5 > 5
) AS subsub
) AS inner2
ON inner1.parallax = inner2.parallax
) AS subq
GROUP BY id;
"""
qp = MySQLQueryProcessor()
qp.set_query(query)
qp.process_query()
print(qp.query)
print(qp.columns)
print(qp.display_columns)
print(qp.tables)
print(qp.keywords)
print(qp.functions)
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 process_query(query):
# get the adapter
adapter = DatabaseAdapter()
try:
if adapter.database_config['ENGINE'] == 'django.db.backends.mysql':
from queryparser.mysql import MySQLQueryProcessor
processor = MySQLQueryProcessor(query)
elif adapter.database_config[
'ENGINE'] == 'django.db.backends.postgresql':
from queryparser.postgresql import PostgreSQLQueryProcessor
if settings.QUERY_PROCESSOR_CACHE:
processor = cache.get_or_set('processor',
PostgreSQLQueryProcessor(), 3600)
else:
processor = PostgreSQLQueryProcessor()
# first run to replace with get_indexed_objects
processor.set_query(query)
processor.process_query(indexed_objects=get_indexed_objects(),
replace_schema_name={
'TAP_SCHEMA': settings.TAP_SCHEMA,
'tap_schema': settings.TAP_SCHEMA,
'TAP_UPLOAD': settings.TAP_UPLOAD,
'tap_upload': settings.TAP_UPLOAD,
})
# second run
processor.set_query(processor.query)
processor.process_query()
else:
raise Exception('Unknown database engine')
except QuerySyntaxError as e:
raise ValidationError({
'query': {
'messages':
[_('There has been an error while parsing your query.')],
'positions':
json.dumps(e.syntax_errors),
}
})
except QueryError as e:
raise ValidationError({'query': {
'messages': e.messages,
}})
return processor