def file_import(request, repo_base, repo):
try:
login = get_login(request)
res = DataHubManager.has_repo_privilege(login, repo_base, repo, 'CREATE')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
file_name = request.GET['file']
delimiter = str(request.GET['delimiter'])
if delimiter == '':
delimiter = str(request.GET['other_delimiter'])
header = True if request.GET['has_header'] == "true" else False
quote_character = request.GET['quote_character']
if quote_character == '':
quote_character = request.GET['other_quote_character']
delimiter = delimiter.decode('string_escape')
repo_dir = '/user_data/%s/%s' %(repo_base, repo)
file_path = '%s/%s' %(repo_dir, file_name)
table_name, _ = os.path.splitext(file_name)
table_name = clean_str(table_name, 'table')
dh_table_name = '%s.%s.%s' %(repo_base, repo, table_name)
f = codecs.open(file_path, 'r', 'ISO-8859-1')
data = csv.reader(f, delimiter=delimiter)
cells = data.next()
columns = [clean_str(str(i), 'col') for i in range(0, len(cells))]
if header:
columns = map(lambda x: clean_str(x, 'col'), cells)
columns = rename_duplicates(columns)
query = 'CREATE TABLE %s (%s text' % (dh_table_name, columns[0])
for i in range(1, len(columns)):
query += ', %s %s' %(columns[i], 'text')
query += ')'
manager = DataHubManager(user=repo_base)
manager.execute_sql(query=query)
manager.import_file(
repo_base=repo_base,
table_name=dh_table_name,
file_path=file_path,
delimiter=delimiter,
header=header,
quote_character=quote_character)
return HttpResponseRedirect('/browse/%s/%s' %(repo_base, repo))
except Exception, e:
return HttpResponse(
json.dumps(
{'error': str(e)}),
mimetype="application/json")
def get_cache(username):
try:
manager = DataHubManager(user=username)
manager.execute_sql(
"create table _dbwipes_cache(key varchar, val text)")
except:
pass
def _f(self, *args, **kwargs):
try:
key = str(
map(str, (f.__name__, self.repo, self.tablename, self.where,
self.nbuckets, map(str, args))))
query = 'select val from {}.dbwipes_cache where key = %s'.format(
dbwipes_repo)
manager = DataHubManager(user=self.repo_base)
vals = manager.execute_sql(query, (key, ))['tuples']
if len(vals) > 0:
return json.loads(vals[0][0])
except Exception as e:
print(e)
res = f(self, *args, **kwargs)
if key:
value = json.dumps(res, default=json_handler)
params = (key, value)
q = 'insert into ' + dbwipes_repo + '.dbwipes_cache values(%s, %s)'
manager = DataHubManager(user=self.repo_base)
manager.execute_sql(q, params)
return res
def get_cache(username):
try:
manager = DataHubManager(user=username)
manager.execute_sql(
"create table _dbwipes_cache(key varchar, val text)")
except:
pass
def table(request, repo_base, repo, table, page='1'):
try:
login = get_login(request)
dh_table_name = '%s.%s.%s' %(repo_base, repo, table)
res = DataHubManager.has_table_privilege(
login, repo_base, dh_table_name, 'SELECT')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
manager = DataHubManager(user=repo_base)
res = manager.execute_sql(
query='SELECT count(*) from %s' %(dh_table_name))
limit = 50
count = res['tuples'][0][0]
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(page)
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
res = manager.execute_sql(
query='SELECT * from %s LIMIT %s OFFSET %s'
%(dh_table_name, limit, (current_page -1) * limit))
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
return render_to_response("table.html", {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'table': table,
'column_names': column_names,
'tuples': tuples,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)})
except Exception, e:
return HttpResponse(json.dumps(
{'error': str(e)}),
mimetype="application/json")
def file_import(request, repo_base, repo, file_name):
try:
login = get_login(request)
res = DataHubManager.has_repo_privilege(login, repo_base, repo, 'CREATE')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
delimiter = str(request.GET['delimiter'])
if delimiter == '':
delimiter = str(request.GET['other_delimiter'])
header = True if request.GET['has_header'] == "true" else False
quote_character = request.GET['quote_character']
if quote_character == '':
quote_character = request.GET['other_quote_character']
delimiter = delimiter.decode('string_escape')
repo_dir = '/user_data/%s/%s' %(repo_base, repo)
file_path = '%s/%s' %(repo_dir, file_name)
table_name, _ = os.path.splitext(file_name)
table_name = clean_str(table_name, 'table')
dh_table_name = '%s.%s.%s' %(repo_base, repo, table_name)
f = codecs.open(file_path, 'r', 'ISO-8859-1')
data = csv.reader(f, delimiter=delimiter)
cells = data.next()
columns = [clean_str(str(i), 'col') for i in range(0, len(cells))]
if header:
columns = map(lambda x: clean_str(x, 'col'), cells)
columns = rename_duplicates(columns)
query = 'CREATE TABLE %s (%s text' % (dh_table_name, columns[0])
for i in range(1, len(columns)):
query += ', %s %s' %(columns[i], 'text')
query += ')'
manager = DataHubManager(user=repo_base)
manager.execute_sql(query=query)
manager.import_file(
repo_base=repo_base,
table_name=dh_table_name,
file_path=file_path,
delimiter=delimiter,
header=header,
quote_character=quote_character)
return HttpResponseRedirect('/browse/%s/%s' %(repo_base, repo))
except Exception, e:
return HttpResponse(
json.dumps(
{'error': str(e)}),
mimetype="application/json")
def create_cache(username):
""" DBWipes stores some metadata about the table in a schema in the owner's
database. Note that this is not necessarily the current user's DB
"""
try:
query = ('create table if not exists %s.dbwipes_cache'
'(key varchar, val text)') % dbwipes_repo
manager = DataHubManager(user=username)
manager.create_repo(dbwipes_repo)
manager.execute_sql(query)
return True
except Exception as e:
print(e)
return False
def api_query(request):
ret = {}
jsonstr = request.GET.get("json", None)
if not jsonstr:
print "query: no json string. giving up"
return ret
args = json.loads(jsonstr)
username = args.get("username")
dbname = args.get("db")
table = args.get("table")
args["table"] = "%s.%s" % (dbname, table)
o, params = create_sql_obj(None, args)
o.limit = 10000
query = str(o)
print query
print params
if not dbname or not table or not query:
print "query: no db/table/query. giving up"
return ret
manager = DataHubManager(user=username)
res = manager.execute_sql(query, params)
rows = res["tuples"]
cols = pick(res["fields"], "name")
data = [dict(zip(cols, vals)) for vals in rows]
ret["data"] = data
ret["schema"] = get_schema(dbname, table, username)
print "%d points returned" % len(ret.get("data", []))
return ret
def api_query(request):
ret = {}
jsonstr = request.GET.get('json', None)
if not jsonstr:
print "query: no json string. giving up"
return ret
args = json.loads(jsonstr)
username = args.get('username')
dbname = args.get('db')
table = args.get('table')
args['table'] = "%s.%s" % (dbname, table)
o, params = create_sql_obj(None, args)
o.limit = 10000
query = str(o)
print query
print params
if not dbname or not table or not query:
print "query: no db/table/query. giving up"
return ret
manager = DataHubManager(user=username)
res = manager.execute_sql(query, params)
rows = res['tuples']
cols = pick(res['fields'], 'name')
data = [dict(zip(cols, vals)) for vals in rows]
ret['data'] = data
ret['schema'] = get_schema(dbname, table, username)
print "%d points returned" % len(ret.get('data', []))
return ret
def execute_sql(self, con, query, query_params=None):
try:
manager = DataHubManager(user=con.user, repo_base=con.repo_base)
res = manager.execute_sql(query=query, params=query_params)
return construct_result_set(res)
except Exception, e:
raise DBException(message=str(e))
def api_query(request):
ret = { }
jsonstr = request.GET.get('json', None)
if not jsonstr:
print "query: no json string. giving up"
return ret
args = json.loads(jsonstr)
username = args.get('username')
dbname = args.get('db')
table = args.get('table')
args['table'] = "%s.%s" % (dbname, table)
o, params = create_sql_obj(None, args)
o.limit = 10000;
query = str(o)
print query
print params
if not dbname or not table or not query:
print "query: no db/table/query. giving up"
return ret
manager = DataHubManager(user=username)
res = manager.execute_sql(query, params)
rows = res['tuples']
cols = pick(res['fields'], 'name')
data = [dict(zip(cols, vals)) for vals in rows]
ret['data'] = data
ret['schema'] = get_schema(dbname, table, username)
print "%d points returned" % len(ret.get('data', []))
return ret
def execute_sql(self, con, query, query_params=None):
try:
manager = DataHubManager(user=con.repo_base,
repo_base=con.repo_base)
res = manager.execute_sql(query=query, params=query_params)
return construct_result_set(res)
except Exception, e:
raise DBException(message=str(e))
def table_delete(request, repo_base, repo, table_name):
try:
login = get_login(request)
dh_table_name = '%s.%s.%s' % (repo_base, repo, table_name)
res = DataHubManager.has_table_privilege(login, repo_base,
dh_table_name, 'DELETE')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
manager = DataHubManager(user=repo_base)
query = '''DROP TABLE %s''' % (dh_table_name)
manager.execute_sql(query=query)
return HttpResponseRedirect('/browse/%s/%s' % (repo_base, repo))
except Exception, e:
return HttpResponse(json.dumps({'error': str(e)}),
content_type="application/json")
def table_delete(request, repo_base, repo, table_name):
try:
login = get_login(request)
dh_table_name = '%s.%s.%s' %(repo_base, repo, table_name)
res = DataHubManager.has_table_privilege(
login, repo_base, dh_table_name, 'DELETE')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
manager = DataHubManager(user=repo_base)
query = '''DROP TABLE %s''' %(dh_table_name)
manager.execute_sql(query=query)
return HttpResponseRedirect('/browse/%s/%s' %(repo_base, repo))
except Exception, e:
return HttpResponse(
json.dumps(
{'error': str(e)}),
content_type="application/json")
def _f(self, *args, **kwargs):
try:
key = str(map(str, (f.__name__, self.dbname, self.tablename, self.where, self.nbuckets, map(str, args))))
manager = DataHubManager(user=self.username)
vals = manager.execute_sql(
'select val from _dbwipes_cache where key = %s',
params = (key,))['tuples']
if len(vals):
return json.loads(vals[0][0])
except Exception as e:
print e
pdb.set_trace()
res = f(self, *args, **kwargs)
if key:
manager = DataHubManager(user=self.username)
manager.execute_sql(
'insert into _dbwipes_cache values(%s, %s)',
params = (key, json.dumps(res, default=json_handler)))
return res
def _f(self, *args, **kwargs):
try:
key = str(
map(str, (f.__name__, self.dbname, self.tablename, self.where,
self.nbuckets, map(str, args))))
manager = DataHubManager(user=self.username)
vals = manager.execute_sql(
'select val from _dbwipes_cache where key = %s',
params=(key, ))['tuples']
if len(vals):
return json.loads(vals[0][0])
except Exception as e:
print e
pdb.set_trace()
res = f(self, *args, **kwargs)
if key:
manager = DataHubManager(user=self.username)
manager.execute_sql('insert into _dbwipes_cache values(%s, %s)',
params=(key,
json.dumps(res, default=json_handler)))
return res
def aggregate(request, repo, table, agg_type, col_name):
username = get_login(request)
manager = DataHubManager(username)
repos = get_repos(manager)
# Ensure that the repo exists.
if repos is not None and repo in repos:
tables = get_tables(manager, repo)
# Ensure that the table exists.
if tables is not None and table in tables:
schema = manager.get_schema(repo + "." + table)
# Ensure that the schema for the repo.table exists.
if schema is not None and 'tuples' in schema:
for c_name, c_type in schema["tuples"]:
if c_name == col_name and can_apply(agg_type, col_name):
result = manager.execute_sql("SELECT %s(%s) FROM %s.%s" % (agg_type.lower(), col_name.lower(), repo, table))
if "tuples" in result and len(result["tuples"]) > 0 and len(result["tuples"][0]) > 0:
return json_response({"value": result["tuples"][0]})
return error_response()
def aggregate(request, repo, table, agg_type, col_name):
username = request.user.get_username()
manager = DataHubManager(username)
repos = get_repos(manager)
# Ensure that the repo exists.
if repos is not None and repo in repos:
tables = get_tables(manager, repo)
# Ensure that the table exists.
if tables is not None and table in tables:
schema = manager.get_schema(repo, table)
# Ensure that the schema for the repo.table exists.
if len(schema) > 0:
for c_name, c_type in schema:
if c_name == col_name and can_apply(agg_type, col_name):
result = manager.execute_sql(
"SELECT %s(%s) FROM %s.%s" % (agg_type.lower(), col_name.lower(), repo, table))
if "tuples" in result and len(result["tuples"]) > 0 and len(result["tuples"][0]) > 0:
return json_response({"value": result["tuples"][0]})
return error_response()
def api_tuples(request):
ret = {}
jsonstr = request.GET.get('json')
if not jsonstr:
# print("query: no json string. giving up")
return ret
args = json.loads(jsonstr)
username = request.user.get_username()
repo_base = args.get('username')
repo = args.get('db')
table = args.get('table')
where = args.get('where', []) or []
full_tablename = "%s.%s" % (repo, table)
where, params = where_to_sql(where)
if where:
where = 'AND %s' % where
# print(where)
# print(params)
query = ("WITH XXXX as (select count(*) from %s WHERE 1 = 1 %s) "
"SELECT * FROM %s "
"WHERE random() <= 50.0 / (select * from XXXX) %s "
"LIMIT 50")
query = query % (full_tablename, where, full_tablename, where)
params = params + params
manager = DataHubManager(user=username, repo_base=repo_base)
res = manager.execute_sql(query, params=params)
rows = res['tuples']
cols = [field['name'] for field in res['fields']]
data = [dict(zip(cols, vals)) for vals in rows]
ret['data'] = data
ret['schema'] = get_schema(repo, table, username, repo_base)
# print("%d points returned" % len(ret.get('data', [])))
return(ret)
def api_tuples(request):
ret = { }
jsonstr = request.GET.get('json')
if not jsonstr:
print "query: no json string. giving up"
return ret
args = json.loads(jsonstr)
username = args.get('username')
dbname = repo = args.get('db')
table = args.get('table')
where = args.get('where', []) or []
full_tablename = "%s.%s" % (dbname, table)
where, params = where_to_sql(where)
if where:
where = 'AND %s' % where
print where
print params
query = """WITH XXXX as (select count(*) from %s WHERE 1 = 1 %s)
SELECT * FROM %s
WHERE random() <= 50.0 / (select * from XXXX) %s
LIMIT 50"""
query = query % (full_tablename, where, full_tablename, where)
params = params + params
manager = DataHubManager(user=username)
res = manager.execute_sql(query, params=params)
rows = res['tuples']
cols = [field['name'] for field in res['fields']]
data = [dict(zip(cols, vals)) for vals in rows]
ret['data'] = data
ret['schema'] = get_schema(dbname, table, username)
print "%d points returned" % len(ret.get('data', []))
return ret
def query(self, q, *args): """ Summaries using other engines only need to override this method """ manager = DataHubManager(user=self.username) return manager.execute_sql(q, params=args)['tuples']
def reset_cache(self): q = """delete from cache where key like '%%%%%s%%%%%s%%%%'""" % (str(self.engine), self.tablename) manager = DataHubManager(user=self.username) manager.execute_sql(q)
def card(request, repo_base, repo, card_name):
try:
login = get_login(request)
card = Card.objects.get(repo_base=repo_base,
repo_name=repo,
card_name=card_name)
query = card.query
manager = DataHubManager(user=repo_base)
res = manager.execute_sql(query='EXPLAIN %s' % (query))
limit = 50
num_rows = re.match(r'.*rows=(\d+).*', res['tuples'][0][0]).group(1)
count = int(num_rows)
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(request.REQUEST['page'])
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
# wrap query in another select statement, to allow the
# user's LIMIT statements to still work
db_query = 'select * from (' + query + ') as BXCQWVPEMWVKFBEBNKZSRPYBSB'
db_query = '%s LIMIT %s OFFSET %s' % (db_query, limit,
(current_page - 1) * limit)
res = manager.execute_sql(query=db_query)
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
annotation_text = None
url_path = '/browse/%s/%s/card/%s' % (repo_base, repo, card_name)
try:
annotation = Annotation.objects.get(url_path=url_path)
annotation_text = annotation.annotation_text
except:
pass
data = {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'card_name': card_name,
'annotation': annotation_text,
'query': query,
'column_names': column_names,
'tuples': tuples,
'url_path': url_path,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)
}
data.update(csrf(request))
return render_to_response("card-browse.html", data)
except Exception, e:
return HttpResponse(json.dumps({'error': str(e)}),
content_type="application/json")
def card(request, repo_base, repo, card_name):
try:
login = get_login(request)
card = Card.objects.get(repo_base=repo_base, repo_name=repo, card_name=card_name)
query = card.query
manager = DataHubManager(user=repo_base)
res = manager.execute_sql(
query='EXPLAIN %s' %(query))
limit = 50
num_rows = re.match(r'.*rows=(\d+).*', res['tuples'][0][0]).group(1)
count = int(num_rows)
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(request.REQUEST['page'])
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
# wrap query in another select statement, to allow the
# user's LIMIT statements to still work
db_query = 'select * from (' + query + ') as BXCQWVPEMWVKFBEBNKZSRPYBSB'
db_query = '%s LIMIT %s OFFSET %s' %(db_query, limit, (current_page -1) * limit)
res = manager.execute_sql(query=db_query)
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
annotation_text = None
url_path = '/browse/%s/%s/card/%s' %(repo_base, repo, card_name)
try:
annotation = Annotation.objects.get(url_path=url_path)
annotation_text = annotation.annotation_text
except:
pass
data = {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'card_name': card_name,
'annotation': annotation_text,
'query': query,
'column_names': column_names,
'tuples': tuples,
'url_path': url_path,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)}
data.update(csrf(request))
return render_to_response("card-browse.html", data)
except Exception, e:
return HttpResponse(
json.dumps(
{'error': str(e)}),
content_type="application/json")
def query(request, repo_base, repo):
try:
login = get_login(request)
data = {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'select_query': False,
'query': None}
data.update(csrf(request))
if 'q' in request.REQUEST:
query = request.REQUEST['q']
query = query.strip().rstrip(';')
manager = DataHubManager(user=repo_base)
select_query = False
if (query.split()[0]).lower() == 'select':
select_query = True
count = 0
limit = 50
if select_query:
res = manager.execute_sql(query='EXPLAIN %s' %(query))
num_rows = re.match(r'.*rows=(\d+).*', res['tuples'][0][0]).group(1)
count = int(num_rows)
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(request.REQUEST['page'])
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
db_query = query
if select_query:
# wrap query in another select statement, to allow the
# user's LIMIT statements to still work
db_query = 'select * from (' + query + ') as BXCQWVPEMWVKFBEBNKZSRPYBSB'
# wrap in datahub limit and offset statements, to support pagination
db_query = '%s LIMIT %s OFFSET %s' %(
db_query, limit, (current_page -1) * limit)
res = manager.execute_sql(query=db_query)
if select_query or res['row_count'] > 0:
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
else:
column_names = ['status']
tuples = [['success' if res['status'] else res['error']]]
url_path = '/browse/%s/%s/query' %(repo_base, repo)
data.update({
'select_query': select_query,
'query': query,
'column_names': column_names,
'tuples': tuples,
'url_path': url_path,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)})
return render_to_response("query-browse-results.html", data)
else:
return render_to_response("query.html", data)
except Exception, e:
return HttpResponse(
json.dumps(
{'error': str(e)}),
content_type="application/json")
def table(request, repo_base, repo, table):
try:
login = get_login(request)
dh_table_name = '%s.%s.%s' % (repo_base, repo, table)
res = DataHubManager.has_table_privilege(login, repo_base,
dh_table_name, 'SELECT')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
manager = DataHubManager(user=repo_base)
res = manager.execute_sql(query='EXPLAIN SELECT * FROM %s' %
(dh_table_name))
limit = 50
num_rows = re.match(r'.*rows=(\d+).*', res['tuples'][0][0]).group(1)
count = int(num_rows)
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(request.REQUEST['page'])
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
print "-------------"
res = manager.execute_sql(query='SELECT * from %s LIMIT %s OFFSET %s' %
(dh_table_name, limit,
(current_page - 1) * limit))
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
annotation_text = None
url_path = '/browse/%s/%s/table/%s' % (repo_base, repo, table)
try:
annotation = Annotation.objects.get(url_path=url_path)
annotation_text = annotation.annotation_text
except:
pass
data = {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'table': table,
'column_names': column_names,
'tuples': tuples,
'annotation': annotation_text,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'url_path': url_path,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)
}
data.update(csrf(request))
return render_to_response("table-browse.html", data)
except Exception, e:
return HttpResponse(json.dumps({'error': str(e)}),
content_type="application/json")
def table(request, repo_base, repo, table):
try:
login = get_login(request)
dh_table_name = '%s.%s.%s' %(repo_base, repo, table)
res = DataHubManager.has_table_privilege(
login, repo_base, dh_table_name, 'SELECT')
if not (res and res['tuples'][0][0]):
raise Exception('Access denied. Missing required privileges.')
manager = DataHubManager(user=repo_base)
res = manager.execute_sql(
query='EXPLAIN SELECT * FROM %s' %(dh_table_name))
limit = 50
num_rows = re.match(r'.*rows=(\d+).*', res['tuples'][0][0]).group(1)
count = int(num_rows)
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(request.REQUEST['page'])
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
print "-------------"
res = manager.execute_sql(
query='SELECT * from %s LIMIT %s OFFSET %s'
%(dh_table_name, limit, (current_page -1) * limit))
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
annotation_text = None
url_path = '/browse/%s/%s/table/%s' %(repo_base, repo, table)
try:
annotation = Annotation.objects.get(url_path=url_path)
annotation_text = annotation.annotation_text
except:
pass
data = {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'table': table,
'column_names': column_names,
'tuples': tuples,
'annotation': annotation_text,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'url_path': url_path,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)}
data.update(csrf(request))
return render_to_response("table-browse.html", data)
except Exception, e:
return HttpResponse(json.dumps(
{'error': str(e)}),
content_type="application/json")
def query(self, q, *args):
"""
Summaries using other engines only need to override this method
"""
manager = DataHubManager(user=self.username)
return manager.execute_sql(q, params=args)['tuples']
def reset_cache(self):
q = """delete from cache where key like '%%%%%s%%%%%s%%%%'""" % (str(
self.engine), self.tablename)
manager = DataHubManager(user=self.username)
manager.execute_sql(q)
def query(request, repo_base, repo):
try:
login = get_login(request)
data = {
'login': get_login(request),
'repo_base': repo_base,
'repo': repo,
'select_query': False,
'query': None
}
data.update(csrf(request))
if 'q' in request.REQUEST:
query = request.REQUEST['q']
query = query.strip().rstrip(';')
manager = DataHubManager(user=repo_base)
select_query = False
if (query.split()[0]).lower() == 'select':
select_query = True
count = 0
limit = 50
if select_query:
res = manager.execute_sql(query='EXPLAIN %s' % (query))
num_rows = re.match(r'.*rows=(\d+).*',
res['tuples'][0][0]).group(1)
count = int(num_rows)
total_pages = 1 + (int(count) / limit)
current_page = 1
try:
current_page = int(request.REQUEST['page'])
except:
pass
if current_page < 1:
current_page = 1
start_page = current_page - 5
if start_page < 1:
start_page = 1
end_page = start_page + 10
if end_page > total_pages:
end_page = total_pages
db_query = query
if select_query:
# wrap query in another select statement, to allow the
# user's LIMIT statements to still work
db_query = 'select * from (' + query + ') as BXCQWVPEMWVKFBEBNKZSRPYBSB'
# wrap in datahub limit and offset statements, to support pagination
db_query = '%s LIMIT %s OFFSET %s' % (db_query, limit,
(current_page - 1) *
limit)
res = manager.execute_sql(query=db_query)
if select_query or res['row_count'] > 0:
column_names = [field['name'] for field in res['fields']]
tuples = res['tuples']
else:
column_names = ['status']
tuples = [['success' if res['status'] else res['error']]]
url_path = '/browse/%s/%s/query' % (repo_base, repo)
data.update({
'select_query': select_query,
'query': query,
'column_names': column_names,
'tuples': tuples,
'url_path': url_path,
'current_page': current_page,
'next_page': current_page + 1,
'prev_page': current_page - 1,
'total_pages': total_pages,
'pages': range(start_page, end_page + 1)
})
return render_to_response("query-browse-results.html", data)
else:
return render_to_response("query.html", data)
except Exception, e:
return HttpResponse(json.dumps({'error': str(e)}),
content_type="application/json")
