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