def get_hbase_columns(p_sync):
print('p_sync=', p_sync)
p_ds = get_ds_by_dsid(p_sync['sour_db_server'])
n_len = len(p_sync['sync_hbase_rowkey'].split(',')) - 1
db = get_connection_ds(p_ds)
cr = db.cursor()
print('get_hbase_columns.para=', p_sync['sour_db_name'],
p_sync['sour_tab_name'], p_sync['sour_tab_cols'],
p_sync['sync_hbase_rowkey'])
sql = """
SELECT
CONCAT('
{{
"index":',cast((@rowNum:=@rowNum+1) as char),',
"name": "info:',column_name,'",
"type": "string"
}},') AS json
FROM information_schema.columns a,(SELECT (@rowNum:={0})) b
WHERE a.table_schema='{1}'
AND a.table_name='{2}'
AND instr('{3}',a.column_name)>0
ORDER BY a.ordinal_position
""".format(str(n_len), p_sync['sour_db_name'], p_sync['sour_tab_name'],
p_sync['sour_tab_cols'])
print('get_hbase_columns.sql=', sql)
cr.execute(sql)
rs = cr.fetchall()
print('rs=', rs)
v = ''
for i in rs:
v = v + str(i[0])
print('------------------------------')
print(v)
return v[0:-1]
def check_sql(p_dbid,p_cdb,p_sql,desc,logon_user,type):
result = {}
result['code'] = '0'
result['message'] = '发布成功！'
try:
if p_dbid == '':
result['code'] = '1'
result['message'] = '请选择数据源!'
return result
p_ds = get_ds_by_dsid(p_dbid)
if p_ds['db_type'] == '0':
val = check_mysql_ddl(p_dbid,p_cdb, p_sql,logon_user,type)
if val == False:
result['code'] = '1'
result['message'] = '发布失败!'
return result
return result
except:
e_str = traceback.format_exc()
print(e_str)
result['code'] = '-1'
result['message'] = '发布失败！'+e_str
return result
def check_virtual_column(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
v_sql_check = ''
if get_obj_type(p_sql) == 'TABLE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select count(0) from information_schema.columns
where upper(table_schema)='{0}'
and upper(table_name)='{1}'
and extra='VIRTUAL GENERATED'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(p_sql)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '表中不能含有虚拟列！')
return result
else:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
return result
def check_column_len(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
v_sql_check = ''
if get_obj_type(p_sql) == 'TABLE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select IFNULL(SUM(character_maximum_length),0)
from information_schema.columns
where upper(table_schema)='{0}'
and upper(table_name)='{1}'
and data_type='VARCHAR'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(p_sql)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 8000:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '表中字符列不能超过8000个字符！')
return result
else:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
return result
def exe_query(p_dbid,p_sql,curdb):
result = {}
# 查询校验
val = check_sql(p_dbid, p_sql,curdb)
if val['status'] != '0':
return val
p_ds = get_ds_by_dsid(p_dbid)
print('exe_query=>p_ds:',p_ds)
#查询MySQL数据源
if p_ds['db_type']=='0':
if p_ds['proxy_status'] == '1':
result = get_mysql_proxy_result(p_ds,p_sql,curdb)
else:
result = get_mysql_result(p_ds,p_sql,curdb)
# 查询MSQLServer数据源
if p_ds['db_type'] == '2':
if p_ds['proxy_status'] == '1':
result = get_sqlserver_proxy_result(p_ds, p_sql, curdb)
else:
result = get_sqlserver_result(p_ds, p_sql, curdb)
return result
def get_tab_incr_col(dbid, db_name, tab_name):
try:
result = {}
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = """
SELECT column_name,column_comment
FROM information_schema.columns
WHERE table_schema='{0}' AND table_name='{1}'
AND data_type IN('timestamp','datetime','date')
ORDER BY ordinal_position
""".format(db_name, tab_name)
cr.execute(sql)
rs = cr.fetchall()
v_list = []
for r in rs:
v_list.append(r)
cr.close()
result['code'] = '0'
result['message'] = v_list
except Exception as e:
print('get_db_name.err:', str(e))
result['code'] = '-1'
result['message'] = '获取数据库列名失败！'
return result
def query_ds(dsid):
try:
result = {}
result['code'] = '0'
result['message'] = get_ds_by_dsid(dsid)
except Exception as e:
print('get_tab_keys.err:', str(e))
result['code'] = '-1'
result['message'] = '获取数据源信息失败！'
return result
def get_tree_by_dbid_mssql_proxy(dbid):
try:
result = {}
v_html = ''
p_ds = get_ds_by_dsid(dbid)
if p_ds['service'] == '':
sql1 = """ SELECT name FROM Master..SysDatabases ORDER BY Name"""
else:
sql1 = """ SELECT name FROM Master..SysDatabases where name= DB_NAME() ORDER BY Name"""
sql2 = """use {};SELECT OBJECT_SCHEMA_NAME(id)+'.'+Name FROM SysObjects Where XType='U' order by name"""
ret1 = get_sqlserver_proxy_result(p_ds, sql1, p_ds['service'])
if ret1['status'] == '1':
result['code'] = '-1'
result['message'] = '加载失败！'
result['desc'] = ''
result['db_url'] = ''
return result
rs1 = ret1['data']
for i in range(len(rs1)):
ret2 = get_sqlserver_proxy_result(p_ds, sql2.format(rs1[i][0]),
p_ds['service'])
if ret1['status'] == '1':
result['code'] = '-1'
result['message'] = '加载失败！'
result['desc'] = ''
result['db_url'] = ''
return result
rs2 = ret2['data']
v_node = """<li><span class="folder">{0}</span><ul>""".format(
rs1[i][0])
v_html = v_html + v_node
for j in range(len(rs2)):
v_node = """<li><span class="file">{0}<div style="display:none">{1}</div></span></li>
""".format(rs2[j][0], rs2[j][0])
v_html = v_html + "\n" + v_node
v_html = v_html + "\n" + "</ul></li>" + "\n"
result['code'] = '0'
result['message'] = v_html
result['desc'] = p_ds['db_desc']
result['db_url'] = p_ds['db_desc']
except Exception as e:
print('get_tree_by_dbid_mssql_proxy=>error:', str(e))
result['code'] = '-1'
result['message'] = '加载失败！'
result['desc'] = ''
result['db_url'] = ''
return result
def get_tree_by_dbid_mssql(dbid):
print('get_tree_by_dbid_mssql=', dbid)
try:
db = ''
sql1 = ''
result = {}
v_html = ''
p_ds = get_ds_by_dsid(dbid)
print('p_ds=', p_ds)
if p_ds['proxy_status'] == '0':
db = get_connection_ds_sqlserver(p_ds)
else:
p_ds['ip'] = p_ds['proxy_server'].split(':')[0]
p_ds['port'] = p_ds['proxy_server'].split(':')[1]
db = get_connection_ds_sqlserver(p_ds)
print('db=', db)
cr = db.cursor()
if p_ds['service'] == '':
sql1 = """ SELECT name FROM Master..SysDatabases ORDER BY Name"""
else:
sql1 = """ SELECT name FROM Master..SysDatabases where name= DB_NAME() ORDER BY Name"""
sql2 = """SELECT OBJECT_SCHEMA_NAME(id)+'.'+Name FROM SysObjects Where XType='U' ORDER BY Name
"""
cr.execute(sql1)
rs1 = cr.fetchall()
for i in range(len(rs1)):
cr.execute('use {}'.format(rs1[i][0]))
cr.execute(sql2.format(rs1[i][0]))
rs2 = cr.fetchall()
v_node = """<li><span class="folder">{0}</span><ul>""".format(
rs1[i][0])
v_html = v_html + v_node
for j in range(len(rs2)):
v_node = """<li><span class="file">{0}<div style="display:none">{1}</div></span></li>""".format(
rs2[j][0], rs2[j][0])
v_html = v_html + "\n" + v_node
v_html = v_html + "\n" + "</ul></li>" + "\n"
cr.close()
result['code'] = '0'
result['message'] = v_html
result['desc'] = p_ds['db_desc']
result['db_url'] = p_ds['db_desc']
#print(v_html)
except Exception as e:
print('get_tree_by_instid_mssql=>error:', str(e))
result['code'] = '-1'
result['message'] = '加载失败！'
result['desc'] = ''
result['db_url'] = ''
return result
def get_tree_by_dbid_proxy(dbid):
try:
result = {}
v_html = ''
p_ds = get_ds_by_dsid(dbid)
sql1 = "SELECT schema_name FROM information_schema.SCHEMATA order by 1"
sql2 = "SELECT table_name FROM information_schema.tables WHERE table_schema='{0}' order by 1"
ret1 = get_mysql_proxy_result(p_ds, sql1, p_ds['service'])
print('ret1=', ret1)
if ret1['status'] == '1':
result['code'] = '-1'
result['message'] = '加载失败！'
result['desc'] = ''
result['db_url'] = ''
return result
rs1 = ret1['data']
print('rs1=', rs1, type(rs1))
for i in range(len(rs1)):
print('i=', i)
ret2 = get_mysql_proxy_result(p_ds, sql2.format(rs1[i][0]),
p_ds['service'])
print('ret2=', ret2)
if ret1['status'] == '1':
result['code'] = '-1'
result['message'] = '加载失败！'
result['desc'] = ''
result['db_url'] = ''
return result
rs2 = ret2['data']
v_node = """<li><span class="folder">{0}</span><ul>""".format(
rs1[i][0])
v_html = v_html + v_node
for j in range(len(rs2)):
v_node = """<li><span class="file">{0}<div style="display:none">{1}</div></span></li>
""".format(rs2[j][0], rs2[j][0])
v_html = v_html + "\n" + v_node
v_html = v_html + "\n" + "</ul></li>" + "\n"
result['code'] = '0'
result['message'] = v_html
result['desc'] = p_ds['db_desc']
result['db_url'] = p_ds['db_desc']
print(v_html)
except Exception as e:
print('get_tree_by_dbid_proxy.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '加载失败！'
return result
def check_proc_valid(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
if get_obj_type(
p_sql) == 'PROCEDURE' and p_sql.upper().count("CREATE") > 0:
#创建存储过程在UAT环境中
cr_uat.execute(p_sql)
#从数据字典中读取过程定义，并将过程中的语句拆分出来，保存至list中
v_sql_proc = '''select routine_definition from information_schema.`routines`
where upper(routine_schema)='{0}'
and upper(routine_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
v_sql_drop = 'drop procedure {0}'.format(get_obj_name(p_sql))
cr_uat.execute(v_sql_proc)
rs_uat = cr_uat.fetchone()
v_list = rs_uat[0].upper().replace('\n',
'').replace('BEGIN', '').replace(
'END', '').split(';')
#逐个验证每一个语句是否满足规则
for i in range(len(v_list)):
print(i, v_list[i])
result = check_proc_valid_ddl(v_list[i])
if result['code'] != '0':
cr_uat.execute(v_sql_drop)
db_uat.commit()
cr_uat.close()
return result
cr_uat.execute(v_sql_drop)
db_uat.commit()
cr_uat.close()
return result
def save_sql(p_dbid,p_cdb,p_sql,desc,p_user,ver,type):
result = {}
try:
# #get write timeout
# write_timeout = int(get_audit_rule('switch_ddl_timeout')['rule_value'])
# print('write_timeout=', write_timeout)
db = get_connection()
cr = db.cursor()
if check_validate(p_dbid,p_cdb,p_sql,desc,p_user,type)['code']!='0':
return check_validate(p_dbid,p_cdb,p_sql,desc,p_user,ver,type)
p_ds = get_ds_by_dsid(p_dbid)
if p_ds['db_type'] == '0':
val = check_mysql_ddl(p_dbid,p_cdb, p_sql,p_user,type)
if val == False:
result['code'] = '1'
result['message'] = '发布失败!'
return result
sql="""insert into t_sql_release(id,dbid,sqltext,status,message,creation_date,creator,last_update_date,updator,version,type)
values('{0}','{1}',"{2}",'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')
""".format(get_sqlid(),p_dbid,p_sql,'0',desc,current_time(),p_user['userid'],current_time(),p_user['userid'],ver,type);
print(sql)
cr.execute(sql)
cr.close()
db.commit()
db.close()
result={}
result['code']='0'
result['message']='发布成功！'
return result
except:
e_str = traceback.format_exc()
print(e_str)
result['code'] = '-1'
result['message'] = '发布失败！'+e_str
return result
def get_tree_by_dbid_layui(dbid):
try:
result = {}
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds_dict(p_ds)
cr1 = db.cursor()
cr2 = db.cursor()
sql1 = """SELECT schema_name FROM information_schema.SCHEMATA order by 1"""
sql2 = """SELECT table_name FROM information_schema.tables WHERE table_schema='{0}' order by 1"""
cr1.execute(sql1)
rs1 = cr1.fetchall()
v_list = []
for r in rs1:
v_db = {}
v_db['title'] = r['schema_name']
#print('sql2=', sql2.format(r['schema_name']))
cr2.execute(sql2.format(r['schema_name']))
rs2 = cr2.fetchall()
#print('rs2=', rs2)
v_children = []
for c in rs2:
v_children.append({"title": c['table_name']})
v_db['children'] = v_children
#print('v_children=', v_children)
#print('v_db=', v_db)
v_list.append(v_db)
#print('v_list=', v_list)
cr1.close()
cr2.close()
db.commit()
result['code'] = '0'
result['message'] = json.dumps(v_list)
# result['desc'] = p_ds['db_desc']
# result['db_url'] = p_ds['db_desc']
except Exception as e:
print('get_tree_by_dbid.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '加载失败！'
return result
def get_es_columns(p_sync):
print('p_sync=', p_sync)
p_ds = get_ds_by_dsid(p_sync['sour_db_server'])
db = get_connection_ds(p_ds)
cr = db.cursor()
print('get_es_columns.para=', p_sync['sour_db_name'],
p_sync['sour_tab_name'], p_sync['sour_tab_cols'])
sql = """
SELECT
CASE WHEN column_name = 'doc_id' THEN
CONCAT('
{{
"name": "',column_name,'",
"type": "id"
}},')
ELSE
CONCAT('
{{
"name": "',column_name,'",
"type": "keyword"
}},')
END AS json
FROM information_schema.columns a
WHERE a.table_schema='{0}'
AND a.table_name='{1}'
AND instr('{2}',a.column_name)>0
ORDER BY CASE WHEN column_name = 'doc_id' THEN 0 ELSE a.ordinal_position END
""".format(p_sync['sour_db_name'], p_sync['sour_tab_name'],
p_sync['sour_tab_cols'])
print('get_es_columns.sql=', sql)
cr.execute(sql)
rs = cr.fetchall()
print('rs=', rs)
v = ''
for i in rs:
v = v + str(i[0])
print('------------------------------')
print(v)
return v[0:-1]
def get_tab_idx_by_tname(dbid, tab, cur_db):
try:
result = {}
p_ds = get_ds_by_dsid(dbid)
p_ds['service'] = cur_db
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = '''SHOW INDEXES FROM {0}'''.format(tab)
cr.execute(sql)
rs = cr.fetchall()
print('get_tab_idx_by_tname=', rs)
v_idx_sql = ''
v_idx_pks = ''
for i in rs:
print('r=', i)
v_idx_name = i[2]
v_idx_type = i[10]
v_idx_cols = i[4]
print('v_idx_name=', v_idx_name, 'v_idx_type=', v_idx_type,
'v_idx_cols=', v_idx_cols)
if v_idx_name == 'PRIMARY':
v_idx_pks = v_idx_pks + v_idx_cols + ','
else:
v_idx_sql = v_idx_sql + 'create index {0} on {1}({2}) using {3}'.format(
v_idx_name, tab, v_idx_cols, v_idx_type) + ';\n'
print('get_tab_idx_by_tname=', v_idx_sql)
if v_idx_pks != '':
v_idx_sql = 'alter table {0} add primary key({1});\n'.format(
tab, v_idx_pks[0:-1]) + v_idx_sql[0:-1]
cr.close()
result['code'] = '0'
result['message'] = v_idx_sql
print('rs=', rs, rs[1])
except Exception as e:
print('get_tab_idx_by_tname.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '未找到索引定义!'
return result
def get_db_name(dbid):
try:
result = {}
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = """SELECT schema_name FROM information_schema.`SCHEMATA`
WHERE schema_name NOT IN('information_schema','mysql','performance_schema')
ORDER BY schema_name"""
cr.execute(sql)
rs = cr.fetchall()
v_list = []
for r in rs:
v_list.append(r[0])
cr.close()
result['code'] = '0'
result['message'] = v_list
except Exception as e:
print('get_db_name.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '获取数据库名失败！'
return result
def get_tab_keys(dbid, db_name, tab_name):
try:
result = {}
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = """
SELECT GROUP_CONCAT(column_name)
FROM information_schema.columns
WHERE table_schema='{0}' AND table_name='{1}'
AND column_key='PRI'
ORDER BY ordinal_position
""".format(db_name, tab_name)
cr.execute(sql)
rs = cr.fetchone()
result['code'] = '0'
result['message'] = rs[0]
except Exception as e:
print('get_tab_keys.err:', str(e))
result['code'] = '-1'
result['message'] = '获取数据库列名失败！'
return result
def get_tab_name(dbid, db_name):
try:
result = {}
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = """SELECT table_name FROM information_schema.tables
WHERE table_schema='{0}'
ORDER BY table_name""".format(db_name)
cr.execute(sql)
rs = cr.fetchall()
v_list = []
for r in rs:
v_list.append(r[0])
cr.close()
result['code'] = '0'
result['message'] = v_list
except Exception as e:
print('get_db_name.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '获取数据库名失败！'
return result
def get_tab_ddl_by_tname(dbid, tab, cur_db):
try:
result = {}
v_node = ""
v_html = ""
p_ds = get_ds_by_dsid(dbid)
p_ds['service'] = cur_db
print('get_tab_ddl_by_tname(p_ds)=>', p_ds)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = """show create table {0}""".format(tab)
cr.execute(sql)
rs = cr.fetchone()
cr.close()
result['code'] = '0'
result['message'] = rs[1]
print('rs=', rs, rs[1])
except Exception as e:
print('get_tab_ddl_by_tname.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '获取表定义失败！'
return result
def get_tree_by_dbid(dbid):
try:
result = {}
v_node = ""
v_html = ""
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql1 = """SELECT schema_name FROM information_schema.SCHEMATA order by 1"""
sql2 = """SELECT table_name
FROM information_schema.tables WHERE table_schema='{0}' order by 1
"""
cr.execute(sql1)
rs1 = cr.fetchall()
for i in range(len(rs1)):
cr.execute(sql2.format(rs1[i][0]))
rs2 = cr.fetchall()
v_node = """<li><span class="folder">{0}</span><ul>""".format(
rs1[i][0])
v_html = v_html + v_node
for j in range(len(rs2)):
v_node = """<li><span class="file">{0}<div style="display:none">{1}</div></span></li>""".format(
rs2[j][0], rs2[j][0])
v_html = v_html + "\n" + v_node
v_html = v_html + "\n" + "</ul></li>" + "\n"
cr.close()
result['code'] = '0'
result['message'] = v_html
result['desc'] = p_ds['db_desc']
result['db_url'] = p_ds['db_desc'] #p_ds['url']
print(v_html)
except Exception as e:
print('get_tree_by_dbid.ERROR:', str(e))
result['code'] = '-1'
result['message'] = '加载失败！'
return result
def post(self):
dbid = self.get_argument("dbid")
p_ds = get_ds_by_dsid(dbid)
result = {}
if p_ds['db_type'] == '0':
if p_ds['proxy_status'] == '1':
result = get_tree_by_dbid_proxy(dbid)
else:
result = get_tree_by_dbid(dbid)
#result = get_tree_by_dbid_layui(dbid)
elif p_ds['db_type'] == '2':
if p_ds['proxy_status'] == '1':
result = get_tree_by_dbid_mssql_proxy(dbid)
else:
result = get_tree_by_dbid_mssql(dbid)
self.write({
"code": result['code'],
"message": result['message'],
"url": result['db_url'],
"desc": result['desc']
})
def get(self):
dsid=self.get_argument("dsid")
d_ds =get_ds_by_dsid(dsid)
self.render("./ds_clone.html",
market_id = d_ds['market_id'],
inst_type = d_ds['inst_type'],
db_type = d_ds['db_type'],
db_env = d_ds['db_env'],
dm_db_type = get_dmm_from_dm('02'),
dm_db_env = get_dmm_from_dm('03'),
dm_inst_type = get_dmm_from_dm('07'),
dm_proj_type = get_dmm_from_dm('05'),
dm_ds_proxy = get_dmm_from_dm('26'),
db_desc = d_ds['db_desc'],
ip = d_ds['ip'],
port = d_ds['port'],
service = d_ds['service'],
user = d_ds['user'],
password = d_ds['password'],
status = d_ds['status'],
proxy_status = d_ds['proxy_status'],
proxy_server = d_ds['proxy_server'],
)
def exe_sql(p_dbid, p_db_name,p_sql_id,p_user):
result = {}
try:
upd_run_status(p_sql_id,p_user,'before')
p_ds = get_ds_by_dsid(p_dbid)
p_ds['service'] = p_db_name
db = get_connection_ds(p_ds)
sql = get_sql_by_sqlid(p_sql_id)
cr = db.cursor()
cr.execute(sql)
db.commit()
cr.close()
upd_run_status(p_sql_id, p_user, 'after')
result['code'] = '0'
result['message'] = '执行成功!'
return result
except Exception as e:
error = str(e).split(',')[1][:-1].replace("\\","\\\\").replace("'","\\'").replace('"','')+'!'
print('exe_sql=',error)
result['code'] = '-1'
result['message'] = '执行失败!{}'.format(error)
upd_run_status(p_sql_id, p_user, 'error', error)
return result
def get_tab_structure(dbid, db_name, tab_name):
result = {}
p_ds = get_ds_by_dsid(dbid)
db = get_connection_ds(p_ds)
cr = db.cursor()
sql = """SELECT c.column_name,
c.column_comment,
c.data_type,
CASE WHEN c.extra='auto_increment' THEN '自增' ELSE '' END AS col_incr,
CASE WHEN c.column_key='PRI' THEN '主键' ELSE '' END AS col_pk,
CASE WHEN c.is_nullable='NO' THEN '非空' ELSE '' END AS col_null
FROM information_schema.columns c
WHERE c.table_schema='{0}'
AND c.table_name='{1}'
ORDER BY c.ordinal_position
""".format(db_name, tab_name)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def save_sql(p_dbid,p_sql,desc,logon_user):
result = {}
try:
db = get_connection()
cr = db.cursor()
#发布校验
if p_dbid == '':
result['code'] = '1'
result['message'] = '请选择数据源!'
return result
p_ds = get_ds_by_dsid(p_dbid)
if p_ds['db_type'] == '0':
val = check_mysql_ddl(p_dbid, p_sql,logon_user)
if val['code']!='0':
return val
sql="""insert into t_sql_release(id,dbid,sqltext,status,message,creation_date,creator,last_update_date,updator)
values('{0}','{1}',"{2}",'{3}','{4}','{5}','{6}','{7}','{8}')""".format(get_sqlid(),p_dbid,p_sql,'0',desc,current_time(),'DBA',current_time(),'DBA');
print(sql)
cr.execute(sql)
cr.close()
db.commit()
db.close()
result={}
result['code']='0'
result['message']='发布成功！'
return result
except:
e_str = traceback.format_exc()
print(e_str)
result['code'] = '-1'
result['message'] = '发布失败！'+e_str
return result
def query_monitor_image(proj_id, period, type):
ds = get_ds_by_dsid(proj_id)
db = get_connection()
cr = db.cursor()
if period == "0":
if type == "CPU":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(cpu_usage_rate,'') as cpu_usage_rate
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Memory":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(memory_usage_rate,'') as memory_usage_rate
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Disk(R)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(disk_read_bytes,'') as disk_read_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Disk(W)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(disk_write_bytes,'') as disk_write_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Network(In)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(net_recv_bytes,'') as net_recv_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Network(Out)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(net_send_bytes,'') as net_send_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND now() order by rq
""".format(ds['ip'], ds['port'])
else:
sql = """"""
if period == "1":
if type == "CPU":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(cpu_usage_rate,'') as cpu_usage_rate
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Memory":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(memory_usage_rate,'') as memory_usage_rate
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Disk(R)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(disk_read_bytes,'') as disk_read_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Disk(W)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(disk_write_bytes,'') as disk_write_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Network(In)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(net_recv_bytes,'') as net_recv_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND now() order by rq
""".format(ds['ip'], ds['port'])
elif type == "Network(Out)":
sql = """SELECT DATE_FORMAT(rq,'%H:%i:%s') AS rq,
concat(net_send_bytes,'') as net_send_bytes
FROM t_sys_usage
WHERE ip='{0}' and port='{1}'
AND rq BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND now() order by rq
""".format(ds['ip'], ds['port'])
else:
sql = """"""
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
print("query_monitor_image=", v_list)
return v_list
def post(self):
self.set_header("Content-Type", "application/json; charset=UTF-8")
dsid = self.get_argument("dsid")
v_list = get_ds_by_dsid(dsid)
v_json = json.dumps(v_list)
self.write(v_json)
def check_obj_exists(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
db_prod = get_connection_ds(p_ds)
cr_uat = db_uat.cursor()
cr_prod = db_prod.cursor()
v_sql_check = ''
if get_obj_type(p_sql) == 'TABLE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='BASE TABLE'
and upper(table_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '表已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='BASE TABLE'
and upper(table_name)='{1}'
'''.format(p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '表已经存在！')
return result
if get_obj_type(p_sql) == 'VIEW' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='VIEW'
and upper(table_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '视图已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='VIEW'
and upper(table_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '视图已经存在！')
return result
if get_obj_type(p_sql) == 'INDEX' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.statistics
where upper(table_schema)='{0}'
and upper(index_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '索引已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.statistics
where upper(table_schema)='{0}'
and upper(index_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '索引已经存在！')
return result
if get_obj_type(p_sql) == 'FUNCTION' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='FUNCTION'
and upper(routine_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '函数已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='FUNCTION'
and upper(routine_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '函数已经存在！')
return result
if get_obj_type(
p_sql) == 'PROCEDURE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='PROCEDURE'
and upper(routine_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '过程已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='PROCEDURE'
and upper(routine_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '过程已经存在！')
return result
if get_obj_type(p_sql) == 'TRIGGER' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.triggers
where upper(trigger_schema)='{0}'
and upper(trigger_name)='{1}'
'''.format(
p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '触发器已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.triggers
where upper(trigger_schema)='{0}'
and upper(trigger_schema)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '触发器已经存在！')
return result
cr_uat.close()
cr_prod.close()
return result
def check_ddl_syntax(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
v_sql_check = ''
v_sql_roll = ''
if p_sql.upper().count("CREATE") > 0 and get_obj_type(p_sql) == 'TABLE':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop table ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if p_sql.upper().count("CREATE") > 0 and get_obj_type(p_sql) == 'VIEW':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop view ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if p_sql.upper().count("CREATE") > 0 and get_obj_type(p_sql) == 'INDEX':
try:
v_tab_name = p_sql.upper().split('ON')[1].split('(')[0].replace(
' ', '')
v_sql_check = p_sql
v_sql_roll = 'drop index ' + get_obj_name(
p_sql) + ' on ' + v_tab_name
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if get_obj_type(p_sql) == 'FUNCTION':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop function ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if get_obj_type(p_sql) == 'PROCEDURE':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop procedure ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if get_obj_type(p_sql) == 'TRIGGER':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop trigger ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
return result
