def db_register():
if request.method == 'GET':
_reg = {
'db_server': '',
'db_path': '',
'db_user': '',
'db_pass': '',
'db_role': '',
'dialect': '',
'charset': ''
}
if 'db' in request.GET:
_db = formval_to_utf8(request.GET['db'])
for k in _reg:
_reg[k] = appconf.db_config[_db][k]
_reg['db_alias'] = _db
else:
_reg['db_alias'] = ''
return render(tpl='db_register', reg=_reg)
else:
prms = request.POST
if prms.old_alias:
appconf.db_config.pop(prms.old_alias, None)
appconf.db_config[prms.db_alias] = {
'db_server': prms.db_server,
'db_path': prms.db_path,
'db_user': prms.db_user,
'db_pass': prms.db_pass,
'db_role': prms.db_role,
'dialect': prms.dialect,
'charset': prms.charset
}
with open('%s/dbconfig.ini' % appconf.basepath, 'w+', encoding='utf-8') as f:
appconf.db_config.write(f)
redirect('/db/list')
def query(db):
if db not in appconf.con:
connect_db(db)
if request.method == 'GET':
_col = None
_prms_str = ''
_refresh_obj = ''
if 'table' in request.GET:
_table_name = request.GET.table
_col = appconf.con[db].schema.get_table(_table_name).columns
elif 'view' in request.GET:
_table_name = request.GET.view
_col = appconf.con[db].schema.get_view(_table_name).columns
elif 'procedure' in request.GET:
_table_name = request.GET.procedure
_col = appconf.con[db].schema.get_procedure(_table_name).output_params
_prm = appconf.con[db].schema.get_procedure(_table_name).input_params
_prms_str = ', '.join([':' + t.name for t in _prm])
_prms_str = '(' + _prms_str + ')'
_select_sql_str = ""
if _col:
_select_sql_str += 'select\n'
_select_sql_str += ',\n'.join([' ' + t.name for t in _col])
_select_sql_str += '\nfrom ' + _table_name + _prms_str
else:
if 'procedure' in request.GET:
_select_sql_str += 'execute procedure'
_select_sql_str += '\n ' + _table_name + _prms_str
return template('sql_script', db=db, sql=_select_sql_str, refresh_object = _refresh_obj, extyp='query')
else:
sql = request.POST.sql
exec_typ = request.POST.exec_typ
try:
crs = appconf.con[db].cursor()
_params = {}
for _pp in request.POST:
if _pp[0:7] == 'params[':
_params[_pp[7:-1]] = formval_to_utf8(request.POST[_pp])
nsql, nprms = parse_params(sql, **_params)
pst = crs.prep(nsql)
if exec_typ == 'plan':
return json.dumps({'plan':pst.plan})
else:
crs.execute(pst, nprms)
except Exception as e:
response.status = 500
return e.args[0].replace('\n', '<br/>')
columns = []
for _fld in crs.description:
columns.append(
{'field': _fld[0],
'title': _fld[0].replace('_', ' ').title(),
'sortable': True,
'filterControl': 'input'
}
)
"""
_fld_def['type_code'] = _fld[1]
_fld_def['display_size'] = _fld[2]
_fld_def['internal_size'] = _fld[3]
_fld_def['precision'] = _fld[4]
_fld_def['scale'] = _fld[5]
_fld_def['null_ok'] = _fld[6]
fields.append(_fld_def)
"""
data = []
for row in crs.fetchallmap():
jrw = {}
for k in row:
#print(k, row[k], type(row[k]).__name__, type(row[k]))
json.dumps({k: process_val(row[k])})
jrw[k] = process_val(row[k])
data.append(jrw)
return json.dumps({'plan': pst.plan,
'tdata': {'columns': columns,
'data': data}})
