def run_query(): with pg_connection(*current_user.get_config()) as (con, cur, err): if err: return json.dumps({'success': False, 'error-msg': str(err)}) warning = None try: t1 = time.time() cur.execute(request.form['query']) columns = [desc[0] for desc in cur.description] t2 = time.time() data = cur.fetchall() t3 = time.time() except psycopg2.Warning as warn: # TODO: display warning = str(warn) except psycopg2.Error as err: return json.dumps({'success': False, 'error-msg': err.pgerror}) except Exception as err: # TODO: Display errors only in dev mode? return json.dumps({'success': False, 'error-msg': str(err)}) return json.dumps({ 'success': True, 'warning': warning, 'columns': columns, 'data': data, 'execution-time': (t2 - t1), 'fetching-time': (t3 - t2) })
def get_data(): schemas, tables = [], [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('list schemas and tables'): cur.execute(query('list-schemas')) schemas = cur.fetchall() cur.execute(query('list-tables')) tables = cur.fetchall() schema_cols = ['', 'tables', 'views', 'foreign tables', 'temporary tables', 'functions', 'sequences'] table_cols = ['Column name', 'Column type', 'Column default', 'Column is nullable'] table_data = {'public': OrderedDict()} for table in tables: if table[0] not in table_data: table_data[table[0]] = OrderedDict() table_data[table[0]][table[1]] = { 'column-data': OrderedDict([ ('colum-names', parse_pg_array(table[2])), ('column-types', parse_pg_array(table[3])), ('column-defaults', parse_pg_array(table[4])), ('column-is-nullable', parse_pg_array(table[5])), ]), 'table-size': table[6], } return (schemas, schema_cols, table_data, table_cols)
def run_query(): with pg_connection(*current_user.get_config()) as (con, cur, err): if err: return json.dumps({'success': False, 'error-msg': str(err)}) warning = None try: t1 = time.time() cur.execute(request.form['query']) columns = [desc[0] for desc in cur.description] t2 = time.time() data = cur.fetchall() t3 = time.time() except psycopg2.Warning as warn: # TODO: display warning = str(warn) except psycopg2.Error as err: return json.dumps({'success': False, 'error-msg': err.pgerror}) except Exception as err: # TODO: Display errors only in dev mode? return json.dumps({'success': False, 'error-msg': str(err)}) return json.dumps({'success': True, 'warning': warning, 'columns': columns, 'data': data, 'execution-time': (t2 - t1), 'fetching-time': (t3 - t2)})
def Storage(params=None): handle_params(params) data = [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('list storage query'): cur.execute(query('list-storage')) data = cur.fetchall() display_schemas = params.getlist('schema') schemas = set() treemap_data = {'name': 'root', 'children': []} for (schema_name, table_name, t_size, t_tuples) in data: schemas.add(schema_name) if schema_name in display_schemas or len(display_schemas) == 0: treemap_data['children'].append({'schema_name': schema_name, 'name': table_name, 'size': int(t_size), 'rows': int(t_tuples)}) p = Page() # Header p.add_page(Header(title='Storage', js=['static/pages/storage.js', 'static/lib/d3/d3.js', 'static/lib/d3/lib/colorbrewer/colorbrewer.js'], css=['static/pages/storage.css'])) p.add_page(Navigation(page='storage')) with p.div({'class': 'container-fluid'}): with p.div({'class': 'row form-row'}): with p.div({'class': 'col-md-10'}): with p.form({'id': 'schema-form', 'class': 'form-inline'}): for schema in schemas: with p.label({'class': 'checkbox-inline'}): checked = (schema in display_schemas or len(display_schemas) == 0) and 'checked' or '' with p.input({'type': 'checkbox', 'name': 'schema', 'value': '%s' % schema}, args=[checked]): p.content(schema) with p.div({'class': 'col-md-2'}): with p.form({'id': 'mode-form', 'class': 'form-inline'}): with p.label({'class': 'radio-inline'}): with p.input({'type': 'radio', 'name': 'mode', 'value': 'size'}, args=['checked']): p.content('Size') with p.label({'class': 'radio-inline'}): with p.input({'type': 'radio', 'name': 'mode', 'value': 'rows'}): p.content('Rows (est.)') with p.div({'class': 'row'}): with p.div({'class': 'col-md-12'}): with p.div({'id': 'treemap-chart'}): pass with p.script(): p.content('PGUI.STORAGE.mk_treemap_chart(%s);' % json.dumps(treemap_data)) p.add_page(Footer()) return p
def get_col_size(): if 'table-schema' in request.args and 'table-name' in request.args: params = {'table-schema': escape(request.args['table-schema']), 'table-name': escape(request.args['table-name'])} data = [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('fetching column size for %s' % params): cur.execute(query('get-column-size'), params) data = cur.fetchall() return json.dumps(data) return 'Please specify a table!'
def run_explain(): with pg_connection(*current_user.get_config()) as (con, cur, err): if err: return json.dumps({'success': False, 'error-msg': str(err)}) warning = None try: query = 'EXPLAIN (format json) %s' % request.form['query'] cur.execute(query) data = cur.fetchall() plan = json.loads(data[0][0]) except psycopg2.Warning as warn: # TODO: display warning = str(warn) except psycopg2.Error as err: return json.dumps({'success': False, 'error-msg': err.pgerror}) except Exception as err: return json.dumps({'success': False, 'error-msg': str(err)}) return json.dumps({'success': True, 'warning': warning, 'data': plan})
def Storage(params=None): handle_params(params) data = [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('list storage query'): cur.execute(query('list-storage')) data = cur.fetchall() display_schemas = params.getlist('schema') schemas = set() treemap_data = {'name': 'root', 'children': []} for (schema_name, table_name, t_size, t_tuples) in data: schemas.add(schema_name) if schema_name in display_schemas or len(display_schemas) == 0: treemap_data['children'].append({ 'schema_name': schema_name, 'name': table_name, 'size': int(t_size), 'rows': int(t_tuples) }) p = Page() # Header p.add_page( Header(title='Storage', js=[ 'static/pages/storage.js', 'static/lib/d3/d3.js', 'static/lib/d3/lib/colorbrewer/colorbrewer.js' ], css=['static/pages/storage.css'])) p.add_page(Navigation(page='storage')) with p.div({'class': 'container-fluid'}): with p.div({'class': 'row form-row'}): with p.div({'class': 'col-md-10'}): with p.form({'id': 'schema-form', 'class': 'form-inline'}): for schema in schemas: with p.label({'class': 'checkbox-inline'}): checked = (schema in display_schemas or len(display_schemas) == 0) and 'checked' or '' with p.input( { 'type': 'checkbox', 'name': 'schema', 'value': '%s' % schema }, args=[checked]): p.content(schema) with p.div({'class': 'col-md-2'}): with p.form({'id': 'mode-form', 'class': 'form-inline'}): with p.label({'class': 'radio-inline'}): with p.input( { 'type': 'radio', 'name': 'mode', 'value': 'size' }, args=['checked']): p.content('Size') with p.label({'class': 'radio-inline'}): with p.input({ 'type': 'radio', 'name': 'mode', 'value': 'rows' }): p.content('Rows (est.)') with p.div({'class': 'row'}): with p.div({'class': 'col-md-12'}): with p.div({'id': 'treemap-chart'}): pass with p.script(): p.content('PGUI.STORAGE.mk_treemap_chart(%s);' % json.dumps(treemap_data)) p.add_page(Footer()) return p
def Index(params=None): """ Renders the index page which displays some generic connections information and links to all the activated modules. """ handle_params(params) p = Page() # Header p.add_page(Header(title='Index')) p.add_page(Navigation(page='index')) with p.div({'class': 'container-fluid'}): # Connection information data, params = [], defaultdict(str) param_keys = ('server_version', 'server_encoding', 'client_encoding', 'is_superuser', 'TimeZone') with pg_connection(*current_user.get_config()) as (con, cur, err): for k in param_keys: params[k] = con.get_parameter_status(k) with pg_log_err('list databases'): cur.execute(query('list-databases')) data = cur.fetchall() with p.div({'class': 'row'}): with p.div({'class': 'col-md-2'}): with p.div({'class': 'btn-group'}): with p.button({'class': 'btn btn-default dropdown-toggle', 'data-toggle': 'dropdown', 'aria-expanded': 'false'}): p.content('Switch database <span class="caret"></span>') with p.ul({'class': 'dropdown-menu', 'role': 'menu'}): for n, d in enumerate(data): with p.li(): with p.a({'href': 'index?database=%s' % d[0]}): p.content(d[0]) if n < len(data) - 1: with p.li({'class': 'divider'}): pass with p.div({'class': 'col-md-4 small'}): p.content('<strong>%s</strong>' % current_user.database) p.content('<br>%s@%s:%s' % (current_user.name, current_user.host, current_user.port)) with p.div({'class': 'col-md-6 small'}): with p.ul({'class': 'list-inline'}): for k, v in sorted(params.items()): with p.li(): p.content('%s: %s' % (k, v)) with p.hr(): pass # Modules cols = 12 col_size = 4 col = 0 for page in PAGES[1:]: if col == 0: with p.div({'class': 'row'}, close=False): pass with p.div({'class': 'col-md-%s' % col_size}): with p.a({'href': '/%s' % page['name']}): with p.div({'class': 'page-link'}): with p.span({'class': 'page-icon glyphicon glyphicon-%s' % page['icon']}): pass with p.h3(): p.content(page['caption']) p.content(page['desc']) col = (col + col_size) % cols if col == 0: p.close('div') p.add_page(Footer()) return p
def Index(params=None): """ Renders the index page which displays some generic connections information and links to all the activated modules. """ handle_params(params) p = Page() # Header p.add_page(Header(title='Index')) p.add_page(Navigation(page='index')) with p.div({'class': 'container-fluid'}): # Connection information data, params = [], defaultdict(str) param_keys = ('server_version', 'server_encoding', 'client_encoding', 'is_superuser', 'TimeZone') with pg_connection(*current_user.get_config()) as (con, cur, err): for k in param_keys: params[k] = con.get_parameter_status(k) with pg_log_err('list databases'): cur.execute(query('list-databases')) data = cur.fetchall() with p.div({'class': 'row'}): with p.div({'class': 'col-md-2'}): with p.div({'class': 'btn-group'}): with p.button({ 'class': 'btn btn-default dropdown-toggle', 'data-toggle': 'dropdown', 'aria-expanded': 'false' }): p.content( 'Switch database <span class="caret"></span>') with p.ul({'class': 'dropdown-menu', 'role': 'menu'}): for n, d in enumerate(data): with p.li(): with p.a({'href': 'index?database=%s' % d[0]}): p.content(d[0]) if n < len(data) - 1: with p.li({'class': 'divider'}): pass with p.div({'class': 'col-md-4 small'}): p.content('<strong>%s</strong>' % current_user.database) p.content( '<br>%s@%s:%s' % (current_user.name, current_user.host, current_user.port)) with p.div({'class': 'col-md-6 small'}): with p.ul({'class': 'list-inline'}): for k, v in sorted(params.items()): with p.li(): p.content('%s: %s' % (k, v)) with p.hr(): pass # Modules cols = 12 col_size = 4 col = 0 for page in PAGES[1:]: if col == 0: with p.div({'class': 'row'}, close=False): pass with p.div({'class': 'col-md-%s' % col_size}): with p.a({'href': '/%s' % page['name']}): with p.div({'class': 'page-link'}): with p.span({ 'class': 'page-icon glyphicon glyphicon-%s' % page['icon'] }): pass with p.h3(): p.content(page['caption']) p.content(page['desc']) col = (col + col_size) % cols if col == 0: p.close('div') p.add_page(Footer()) return p