def save(self, *args, **kwargs):
super(Table, self).save(*args, **kwargs)
try:
get_adapter('metadata').store_table_metadata(self.database.name, self.name, {
'order': self.order,
'name': self.name,
'description': self.description,
'type': self.type,
'utype': self.utype
})
except ProgrammingError:
pass
def list(self, request, *args, **kwargs):
# get database and table from the querystring
database_name = self.request.GET.get('database')
table_name = self.request.GET.get('table')
# get database adapter
user_database_name = get_user_database_name(self.request.user.username)
if database_name == user_database_name:
# get database adapter and fetch the columns
columns = get_adapter('data').fetch_columns(
database_name, table_name)
else:
# check permissions on the database
database = Database.permissions.get(self.request.user,
database_name=database_name)
if not database:
raise NotFound()
# check permissions on the table
table = Table.permissions.get(self.request.user,
database=database,
table_name=table_name)
if not table:
raise NotFound()
# get columns for this table
columns = Column.permissions.all(self.request.user, table=table)
return Response(ColumnSerializer(columns, many=True).data)
def create(self, request, *args, **kwargs):
serializer = self.get_serializer(data=request.data)
serializer.is_valid(raise_exception=True)
database = serializer.save()
if request.data.get('discover'):
adapter = get_adapter('metadata')
for table_metadata in adapter.fetch_tables(database.name):
table_metadata['database'] = database.id
table_metadata['groups'] = request.data['groups']
table_serializer = TableSerializer(data=table_metadata)
if table_serializer.is_valid():
table = table_serializer.save()
for column_metadata in adapter.fetch_columns(database.name, table.name):
column_metadata['table'] = table.id
column_metadata['groups'] = request.data['groups']
column_serializer = ColumnSerializer(data=column_metadata)
if column_serializer.is_valid():
column_serializer.save()
headers = self.get_success_headers(serializer.data)
return Response(serializer.data, status=status.HTTP_201_CREATED, headers=headers)
def fetch_user_database_metadata(jobs, username):
adapter = get_adapter('data')
database_name = get_user_database_name(username)
database = {
'order': sys.maxsize,
'name': database_name,
'query_string': adapter.escape_identifier(database_name),
'description': _('Your personal database'),
'tables': []
}
for job in jobs:
if job.phase == PHASE_COMPLETED:
table = job.metadata
table['query_string'] = '%(database)s.%(table)s' % {
'database': adapter.escape_identifier(database_name),
'table': adapter.escape_identifier(table['name'])
}
for column in table['columns']:
column['query_string'] = adapter.escape_identifier(
column['name'])
database['tables'].append(table)
return database
def discover(self, request):
database_name = request.GET.get('database')
table_name = request.GET.get('table')
if database_name and table_name:
return Response([get_adapter('metadata').fetch_table(database_name, table_name)])
else:
return Response([])
def rename_table(self, new_table_name):
if self.table_name != new_table_name:
try:
adapter = get_adapter('data')
# self.table is still the old name since Job is updated first
adapter.rename_table(self.database_name, self.table_name,
new_table_name)
except OperationalError as e:
raise TableError(e.args[1])
def save(self, *args, **kwargs):
super(Column, self).save(*args, **kwargs)
try:
get_adapter('metadata').store_column_metadata(self.table.database.name, self.table.name, self.name, {
'order': self.order,
'name': self.name,
'description': self.description,
'unit': self.unit,
'ucd': self.ucd,
'utype': self.utype,
'datatype': self.datatype,
'size': self.size,
'principal': self.principal,
'indexed': self.indexed,
'std': self.std
})
except ProgrammingError:
pass
def _check_permissions(self, user, qp):
errors = []
# check keywords against whitelist
for keywords in qp.keywords:
pass
# check permissions on databases/tables/columns
for column in qp.columns:
try:
database_name, table_name, column_name = column.split('.')
# check permission on database
database = Database.permissions.get(
user, database_name=database_name)
if not database:
errors.append(_('Database %s not found.') % database_name)
continue
# check permission on table
table = Table.permissions.get(user,
database_name=database_name,
table_name=table_name)
if not table:
errors.append(_('Table %s not found.') % table_name)
continue
# check permission on column
column = Column.permissions.get(user,
database_name=database_name,
table_name=table_name,
column_name=column_name)
if not column:
errors.append(_('Column %s not found.') % column_name)
continue
except ValueError:
errors.append(_('No database given for column %s') % column)
# check permissions on functions
for function_name in qp.functions:
if function_name.upper() in get_adapter('data').functions:
continue
else:
# check permission on function
function = Function.permissions.get(
user, function_name=function_name)
if not function:
errors.append(_('Function %s not found.') % function_name)
continue
# return the error stack
return errors
def drop_table(self):
adapter = get_adapter('data')
# drop the corresponding database table, but fail silently
if self.phase == PHASE_COMPLETED:
try:
adapter.drop_table(self.database_name, self.table_name)
except ProgrammingError:
pass
self.nrows = None
self.size = None
self.save()
def kill(self):
phase = self.phase
self.phase = PHASE_ABORTED
self.save()
if phase == PHASE_QUEUED:
revoke(str(self.id))
elif phase == PHASE_EXECUTING:
# kill the job on the database
try:
adapter = get_adapter('data')
adapter.kill_query(self.pid)
except OperationalError:
# the query was probably killed before
pass
def query_string(self):
return get_adapter('data').escape_identifier(self.name)
def query_string(self):
adapter = get_adapter('data')
return '%(database)s.%(table)s' % {
'database': adapter.escape_identifier(self.database.name),
'table': adapter.escape_identifier(self.name)
}
def handle(self, *args, **options):
sql = []
meta = settings.DATABASES['metadata']
data = settings.DATABASES['data']
# check if the permissions for the metadata adapter are ok
try:
metadata_adapter = get_adapter('metadata')
except OperationalError:
sql.append(
'CREATE USER \'%(USER)s\'@\'localhost\' IDENTIFIED BY \'%(PASSWORD)s\';'
% meta)
# check if the permissions for the data adapter are ok
try:
data_adapter = get_adapter('data')
except OperationalError:
sql.append(
'CREATE USER \'%(USER)s\'@\'localhost\' IDENTIFIED BY \'%(PASSWORD)s\';'
% data)
# check if the permissions for the science databases are ok
databases = Database.objects.all()
try:
for database in databases:
meta.update({'NAME': database.name})
data.update({'NAME': database.name})
try:
metadata_adapter.fetch_tables(meta['NAME'])
except OperationalError:
sql.append(
'GRANT SELECT, ALTER ON `%(NAME)s`.* TO \'%(USER)s\'@\'localhost\';'
% meta)
try:
data_adapter.fetch_tables(meta['NAME'])
except OperationalError:
sql.append(
'GRANT SELECT ON `%(NAME)s`.* TO \'%(USER)s\'@\'localhost\';'
% data)
except ProgrammingError:
pass
# check if the permissions for the user databases are ok
try:
user = User.objects.first()
if user:
database_name = get_user_database_name(user.username)
data.update({'NAME': get_user_database_name(database_name)})
try:
data_adapter.fetch_tables(data['NAME'])
except OperationalError:
data.update({'NAME': get_user_database_name('%')})
sql.append(
'GRANT ALL ON `%(NAME)s`.* TO \'%(USER)s\'@\'localhost\';'
% data)
except ProgrammingError:
pass
if sql:
print('Some permissions on the database are missing. Please run:')
print('')
for line in sql:
print(' ' + line)
print('')
def submit_query(job_id):
from daiquiri.core.adapter import get_adapter
from daiquiri.query.models import QueryJob
from daiquiri.uws.settings import PHASE_EXECUTING, PHASE_COMPLETED, PHASE_ERROR, PHASE_ABORTED
# get the job object from the database
job = QueryJob.objects.get(pk=job_id)
# get the adapter with the database specific functions
adapter = get_adapter('data')
# create the database of the user if it not already exists
try:
adapter.create_user_database_if_not_exists(job.database_name)
except OperationalError as e:
job.phase = PHASE_ERROR
job.error_summary = str(e)
job.save()
return job.phase
# set database and start time
job.start_time = now()
job.pid = adapter.fetch_pid()
job.actual_query = adapter.build_query(job.database_name, job.table_name,
job.actual_query)
job.phase = PHASE_EXECUTING
job.start_time = now()
job.save()
# get the actual query and submit the job to the database
try:
# this is where the work ist done (and the time is spend)
adapter.execute(job.actual_query)
except ProgrammingError as e:
job.phase = PHASE_ERROR
job.error_summary = str(e)
except OperationalError as e:
# load the job again and check if the job was killed
job = QueryJob.objects.get(pk=job_id)
if job.phase != PHASE_ABORTED:
job.phase = PHASE_ERROR
job.error_summary = str(e)
except SoftTimeLimitExceeded:
job.phase = PHASE_ERROR
job.error_summary = _(
'The query exceeded the timelimit for this queue.')
else:
# get additional information about the completed job
job.phase = PHASE_COMPLETED
finally:
# get timing and save the job object
job.end_time = now()
job.execution_duration = (job.end_time - job.start_time).seconds
# get additional information about the completed job
if job.phase == PHASE_COMPLETED:
job.nrows, job.size = adapter.fetch_stats(job.database_name,
job.table_name)
job.metadata = adapter.fetch_table(job.database_name,
job.table_name)
job.metadata['columns'] = adapter.fetch_columns(
job.database_name, job.table_name)
job.save()
return job.phase
def list(self, request, *args, **kwargs):
# get database and table from the querystring
database_name = self.request.GET.get('database')
table_name = self.request.GET.get('table')
# get the ordering
ordering = self.request.GET.get('ordering')
# get the search string
filter_string = self.request.GET.get('filter')
# get the page from the querystring and make sure it is an int
page = self._get_page()
# get the page_size from the querystring and make sure it is an int
page_size = self._get_page_size()
# get database adapter
adapter = get_adapter('data')
# get query backend adapter
user_database_name = get_user_database_name(self.request.user.username)
if database_name == user_database_name:
# get database adapter and fetch the columns
columns = adapter.fetch_columns(database_name, table_name)
column_names = [column['name'] for column in columns]
else:
# check permissions on the database
database = Database.permissions.get(self.request.user,
database_name=database_name)
if not database:
raise NotFound()
# check permissions on the table
table = Table.permissions.get(self.request.user,
database=database,
table_name=table_name)
if not table:
raise NotFound()
# get columns for this table
columns = Column.permissions.all(self.request.user, table=table)
column_names = [column.name for column in columns]
# query the database for the total number of rows
count = adapter.count_rows(database_name, table_name, column_names,
filter_string)
# query the paginated rowset
results = adapter.fetch_rows(database_name, table_name, column_names,
ordering, page, page_size, filter_string)
# get the previous and next url
next = self._get_next_url(page, page_size, count)
previous = self._get_previous_url(page)
# return ordered dict to be send as json
return Response(
OrderedDict((('count', count), ('next', next),
('previous', previous), ('results', results))))
def create_download_file(self, format):
adapter = get_adapter('data')
return adapter.dump_table(self.database_name, self.table_name,
self.owner.username, format)