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 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 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")