def mon_session(db):
if db not in appconf.con:
connect_db(db)
if not appconf.mon[db]:
mon, mon_key = create_mon_session(db)
else:
mon_key = next(reversed(appconf.mon[db]))
mon = appconf.mon[db][mon_key]
return template('mon', db=db, mon_template='./incmon/mn_database.tpl', mon=mon, mon_key=mon_key, data=mon.db)
def ddl_new_table(db, table):
if db not in appconf.con:
connect_db(db)
if request.method == 'GET':
return template('ddl_column', db=db, table=table)
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}})
def script(db):
if db not in appconf.con:
connect_db(db)
if request.method == 'GET':
prms = request.GET
_sql = ''
_refresh_obj = ''
if 'sql' in prms:
_sql = prms.sql
if 'encoded' in prms:
_sql = base64.urlsafe_b64decode(_sql.encode("ascii")[2:-1]).decode("utf-8")
if 'ddl' in prms:
_ddl = prms.ddl
_typ = prms.typ
_name = prms.name
if _ddl == 'drop':
_refresh_obj = _typ + 's'
if _typ == 'function':
_sql = appconf.con[db].schema.get_function(_name).get_sql_for('drop') + ';'
elif _typ == 'column':
_sql = "alter table %s drop %s;" %(prms.table, _name)
_refresh_obj = 'tables'
elif _typ == 'constraint':
_sql = "alter table %s drop constraint %s;" %(prms.table, _name)
else:
_sql = 'drop %s %s;' %(_typ, _name)
elif _ddl == 'edit':
if _typ == 'table':
_obj = appconf.con[db].schema.get_table(_name)
_sql = _obj.get_sql_for('recreate') + ';'
#todo: not sure if I should add these to the script ?
"""
for _cns in _obj.constraints:
if not _cns.isnotnull():
_sql += '\n' + _cns.get_sql_for('create') + ';'
for _ndx in _obj.indices:
_sql += '\n' + _ndx.get_sql_for('create') + ';'
"""
elif _typ == 'view':
_sql = appconf.con[db].schema.get_view(_name).get_sql_for('recreate')
elif _typ == 'procedure':
_sql = appconf.con[db].schema.get_procedure(_name).get_sql_for('create_or_alter')
elif _typ == 'trigger':
_sql = appconf.con[db].schema.get_trigger(_name).get_sql_for('recreate')
elif _typ == 'exception':
_sql = appconf.con[db].schema.get_exception(_name).get_sql_for('recreate')
elif _typ == 'sequence':
_sql = appconf.con[db].schema.get_sequence(_name).get_sql_for('create')
elif _typ == 'index':
_sql = appconf.con[db].schema.get_index(_name).get_sql_for('create')
elif _typ == 'domain':
_obj = appconf.con[db].schema.get_domain(_name)
_sql = _obj.get_sql_for('alter', default=_obj.default) + ';\n'
_sql += _obj.get_sql_for('alter', check=_obj.validation) + ';\n'
_sql += _obj.get_sql_for('alter', datatype=_obj.datatype) + ';\n'
_sql += _obj.get_sql_for('alter', name=_obj.name) + ';\n'
elif _ddl == 'create':
_refresh_obj = _typ + 's'
if _typ == 'view':
_sql = template('./sqls/view')
if _typ == 'trigger':
_sql = template('./sqls/trigger')
elif _typ == 'sequence':
_sql = 'create sequence NEW_SEQUENCE;'
elif _typ == 'exception':
_sql = "create exception NEW_EXCEPTION '<exception_message>';"
elif _typ == 'role':
_sql = "create role NEW_ROLE"
elif _ddl == 'trigger_disable' or _ddl == 'trigger_enable':
_refresh_obj = 'triggers'
if _typ == 'table':
_objs = appconf.con[db].schema.get_table(_name).triggers
elif _typ == 'trigger':
_objs = [appconf.con[db].schema.get_trigger(_name)]
else:
_objs = appconf.con[db].triggers
_opr = 'inactive' if _ddl == 'trigger_disable' else 'active'
for k in _objs:
_sql += 'alter trigger %s %s;\n' % (k.name, _opr)
elif _ddl == 'index_disable' or _ddl == 'index_enable':
_refresh_obj = 'indices'
if _typ == 'table':
_objs = appconf.con[db].schema.get_table(_name).indices
else:
_objs = appconf.con[db].indices
_opr = 'inactive' if _ddl == 'index_disable' else 'active'
for k in _objs:
if not k.isenforcer():
_sql += 'alter index %s %s;\n' % (k.name, _opr)
elif _ddl == 'index_recompute':
_refresh_obj = 'indices'
if _typ == 'table':
_objs = appconf.con[db].schema.get_table(_name).indices
else:
_objs = appconf.con[db].indices
for k in _objs:
_sql += 'SET statistics INDEX %s;\n' % k.name
elif _ddl == 'description':
if _typ == 'function':
_sql += "comment on external %s %s is '%s'" %(_typ, _name, prms.description)
else:
_sql += "comment on %s %s is '%s'" %(_typ, _name, prms.description)
if _typ == 'column':
_refresh_obj = 'tables'
elif _typ == 'index':
_refresh_obj = 'indices'
else:
_refresh_obj = '%ss' %(_typ)
return template('sql_script', db=db, sql=_sql, refresh_object=_refresh_obj, extyp='script')
else:
prms = request.POST
script = prms.sql
auto_commit = int(prms.auto_commit)
trn = None
trn_id = None
if 'action' in prms:
try:
trn_id = int(prms.trn_id)
trn = appconf.trn[trn_id]
except:
response.status = 500
return 'Associated transaction ID is invalid !'
try:
if prms.action == 'commit':
trn.commit()
trn.close()
appconf.trn.pop(trn_id)
if prms.refresh:
refresh_db(db, prms.refresh)
return json.dumps({'errors': [], 'result': 'Transaction committed', 'trn_id': None})
else:
trn.rollback()
trn.close()
appconf.trn.pop(trn_id)
return json.dumps({'errors': [], 'result': 'Transaction rolled back', 'trn_id': None})
except Exception as e:
response.status = 500
return e.args[0].replace('\n', '<br/>')
trn = appconf.con[db].trans()
if not auto_commit:
trn.begin()
trn_id = trn.trans_info(fdb.isc_info_tra_id)
appconf.trn[trn_id] = trn
error_log = []
try:
# very very dirty hack for sql statement parsing for identfying procedure and trigger blocks !!
# a decent sql parser would be better
# match procedure / trigger / execute blocks
p = re.compile(r"(execute(\s)block|create(\s)+(procedure|trigger))(?s).*?(.*?)end(\s*);", re.IGNORECASE)
block_num = 0
blocks = []
iterator = p.finditer(script)
for match in iterator:
blocks.append(match.group())
block_text = ';__block:%d;' % block_num
script = script.replace(match.group(), block_text, 1)
block_num += 1
# replace block comments
p = re.compile(r"/\*(?s).*?\*/")
script = p.sub("", script)
sqls = []
lines = script.split(';')
for ndx, ln in enumerate(lines):
ln = ln.strip()
if ln[0:7] == '__block':
block_num = int(ln.split(':')[1])
sql = blocks[int(ln.split(':')[1])]
elif ln[0:2] == '--':
_new_ln = '\n'.join(ln.splitlines()[1:]).strip()
if _new_ln:
lines.insert(ndx + 1, _new_ln)
sql = None
else:
sql = ln
if sql:
sqls.append(sql)
for sql in sqls:
trn.execute_immediate(sql)
if auto_commit:
trn.commit()
trn.close()
if prms.refresh:
refresh_db(db, prms.refresh)
except Exception as e:
if auto_commit:
trn.rollback()
trn.close()
_error = e.args[0].replace('\n', '<br/>') + '<br/>' + sql
error_log.append(_error)
if error_log != []:
result = 'Script executed with errors'
else:
result = 'Script executed successfully'
return json.dumps({'errors': error_log, 'result': result, 'trn_id': trn_id})
def mon_info(db, info, mon_key):
prms = request.GET
if db not in appconf.con:
connect_db(db)
try:
mon = appconf.mon[db][mon_key]
except KeyError:
HTTPError(500, 'Monitor key is invalid. Create new monitor key.')
if info == 'database':
data = mon.db
elif info == 'attachments':
data = mon.attachments
elif info == 'transactions':
if 'attachment' in prms:
data = mon.get_attachment(int(prms.attachment)).transactions
else:
data = mon.transactions
elif info == 'statements':
if 'attachment' in prms:
data = mon.get_attachment(int(prms.attachment)).statements
elif 'transaction' in prms:
data = mon.get_transaction(int(prms.transaction)).statements
else:
data = mon.statements
elif info == 'callstack':
"""
if 'attachment' in prms:
data = mon.get_attachment(int(prms.attachment)).callstack
elif 'transaction' in prms:
data = mon.get_transaction(int(prms.transaction)).iostats
"""
if 'statement' in prms:
data = mon.get_statement(int(prms.statement)).callstack
else:
data = mon.callstack
elif info == 'variables':
if 'attachment' in prms:
data = mon.get_attachment(int(prms.attachment)).variables
elif 'transaction' in prms:
data = mon.get_transaction(int(prms.transaction)).variables
else:
data = mon.variables
elif info == 'iostats':
if 'attachment' in prms:
data = mon.get_attachment(int(prms.attachment)).iostats
elif 'transaction' in prms:
data = mon.get_transaction(int(prms.transaction)).iostats
elif 'statement' in prms:
data = mon.get_statement(int(prms.statement)).iostats
elif 'callstack' in prms:
data = mon.get_call(int(prms.callstack)).iostats
else:
data = mon.iostats
return template('mon',
db=db,
mon_template='./incmon/mn_%s.tpl' % info,
mon=mon,
mon_key=mon_key,
data=data,
collapsed=False,
table_id=info)