def reviewPreExecute(request):
'''内容预审核'''
try:
databaseid = int(request.POST.get('id',None))
content = request.POST.get('content',None)
sql_id = request.POST.get('sqlid',None)
sql_database_info = '''select host,user,passwd,port,db_name,creator from tb_databases_config where id={0}'''.format(databaseid)
cursor = connections['default'].cursor()
cursor.execute(sql_database_info)
row = cursor.fetchone()
logging.debug(sql_database_info)
audit_sql = preAuditExecute(user=row[1],password=row[2],host=row[0],port=row[3],dbname=row[4],content=content)
if request.method == "POST" and request.user.is_staff == 1:
result = inceptionQuery(audit_sql)
flag = []
for row in result:
flag.append(row[2])
rep_str = row[5]
strs = rep_str.replace("'","\\'")
strss = strs.replace('"', "\\'")
sql = '''REPLACE into tb_review_detail(tid,stage,errlevel,stagestatus,errormessage,`sql`,Affected_rows,sequence,backup_dbname,execute_time,sqlsha1,sql_id) values({0},"{1}",{2},"{3}","{4}",'{5}',{6},"{7}","{8}","{9}","{10}",{11}) ;''' .format(row[0],row[1],row[2],row[3],row[4],strss,row[6],row[7],row[8],row[9],row[10],sql_id)
print sql
cursor = connections['default'].cursor()
cursor.execute(sql)
cursor.close()
return write(1)
else:
return write(0)
except Exception,e:
logging.error(e)
def sqlReportActive(request):
try:
if request.method == "POST" and request.user.is_staff == 0:
# 1、获取上报内容
select = request.POST.getlist('select[]',None)
content = request.POST.get('text',None)
bak = request.POST.get('text2',None)
userid = request.user.id
leng = len(select)
title = '''SQL审核通知！'''
message = '''用户{0}已提交新的SQL，请尽快审核！'''.format(request.user.username)
if leng > 1:
for i in range(len(select)):
review_report = tb_review(database_id=select[i], content=content,remark_user=bak, creator=userid, flag=0,
review_id=0)
review_report.save(using='default')
#em = sendEmail(title=title, message=message, from_email=from_email,to_email=to_email)
#em.send()
return write(1)
elif leng == 1:
review_report = tb_review(database_id=select[0],content=content,remark_user=bak,creator=userid,flag=0,review_id=0)
review_report.save(using='default')
#em = sendEmail(title=title, message=message, from_email=from_email, to_email=to_email)
#em.send()
return write(1)
else:
return write(0)
except Exception, e:
logging.error(e)
def update_sql(request):
try:
id = request.POST.get('id',None)
sql = request.POST.get('sql',None)
SqlAuditExecute.updateSql(sql_id=id,cont=sql)
return write(1)
except Exception as e:
print (e)
return write(0)
def reviewTempActive(request):
try:
if request.method == "POST" and request.user.is_staff == 1:
# 1、获取审核内容
select = request.POST.getlist('select',None)
content = request.POST.get('text',None)
userid = request.user.id
#2、获取审核目标数据库信息
sql_database_info = '''select host,user,passwd,port,db_name,creator from tb_databases_config where id={0}'''.format(
select[0])
cursor = connections['default'].cursor()
cursor.execute(sql_database_info)
row = cursor.fetchone()
cursor.close()
#3、执行审核
target_sql = '''/*--user={0};--password={1};--host={2};--execute=1;--enable-ignore-warnings;--port={3};*/'''.format(
row[1], row[2], row[0], row[3])
db_sql = '''use {0};'''.format(row[4])
ct = content.replace('"', '\'')
ddl_dml_sql = '''{0}'''.format(ct)
main_sql = '''{0}inception_magic_start;{1}{2} inception_magic_commit;'''.format(target_sql, db_sql,
ddl_dml_sql)
conn = MySQLdb.connect(host='172.16.16.20', user='******', passwd='', db='', port=6669)
cur = conn.cursor()
cur.execute(main_sql.encode('utf-8'))
results = cur.fetchall()
cur.close()
# print results
# column_name_max_size = max(len(i[0]) for i in cur.description)
# row_num = 0
# # stagestatus = ''
# res = ''
# for result in results:
# row_num = row_num + 1
# print '*'.ljust(27, '*'), row_num, '.row', '*'.ljust(27, '*')
# rows = map(lambda x, y: (x, y), (i[0] for i in cur.description), result)
# for each_column in rows:
# if each_column[0] != 'errormessage':
# q = each_column[0].rjust(column_name_max_size),":",each_column[1]
#
# else:
# q = res+each_column[0].rjust(column_name_max_size), ':', each_column[1].replace('\n', '\n'.ljust(
# column_name_max_size + 4))
cur.close()
conn.close()
return write(results)
else:
return render(request, 'review/404.html', context=None)
except Exception, e:
logging.error(e)
req = 0
return write(req)
def reviewExecute(request):
'''审核内容执行'''
try:
databaseid = int(request.POST.get('id',None))
content = request.POST.get('content',None)
creatorid = request.POST.get('creator', None)
sql_id = request.POST.get('sqlid',None)
sql_database_info = '''select host,user,passwd,port,db_name,creator from tb_databases_config where id={0}'''.format(databaseid)
cursor = connections['default'].cursor()
cursor.execute(sql_database_info)
row = cursor.fetchone()
logging.debug(sql_database_info)
audit_sql = Execute(user=row[1],password=row[2],host=row[0],port=row[3],dbname=row[4],content=content)
if request.method == "POST" and request.user.is_staff == 1:
result = inceptionQuery(audit_sql)
flag = []
for row in result:
flag.append(row[2])
rep_str = row[5]
strs = rep_str.replace("'", "\\'")
strss = strs.replace('"', "\\'")
sql = '''REPLACE into tb_review_detail(tid,stage,errlevel,stagestatus,errormessage,`sql`,Affected_rows,sequence,backup_dbname,execute_time,sqlsha1,sql_id) values({0},"{1}",{2},"{3}","{4}","{5}",{6},"{7}","{8}","{9}","{10}",{11}) ;''' .format(row[0],row[1],row[2],row[3],row[4],strss,row[6],row[7],row[8],row[9],row[10],sql_id)
cursor = connections['default'].cursor()
cursor.execute(sql)
cursor.close()
title = '''SQL审核通知！'''
falis_message = '''sqlid为{0}的审核未通过！''' .format(sql_id)
suss_message = '''sqlid为{0}的已审核通过！''' .format(sql_id)
sql_to_eamil = '''select username from auth_user where id= (select creator from tb_review where id={0})''' .format(sql_id)
cursor = connections['default'].cursor()
cursor.execute(sql_to_eamil)
re_to_uname = cursor.fetchone()
to_email = '*****@*****.**' % re_to_uname
if 2 in flag:
sql_flag ='''update tb_review set flag=1,review_id={0},review_time=now() where id={1}''' .format(request.user.id,sql_id)
cursor = connections['default'].cursor()
cursor.execute(sql_flag)
em = sendEmail(title=title, message=falis_message, from_email=from_email, to_email=to_email)
em.send()
return write(0)
else:
sql_flag_02 = '''update tb_review set flag=2,review_id={0},review_time=now() where id={1}'''.format(request.user.id,sql_id)
cursor = connections['default'].cursor()
cursor.execute(sql_flag_02)
em = sendEmail(title=title, message=suss_message, from_email=from_email, to_email=to_email)
em.send()
return write(1)
else:
return write(0)
except Exception,e:
logging.error(e)
def reviewPostHistory(request):
'''所有审核内容上报列表'''
try:
if request.method == "GET" and request.user.is_staff == 1:
start = 0
length = 0
sumcnt = 0
draw = request.GET.get('draw',None)
start = request.GET.get('start',None)
length = request.GET.get('length',None)
sql_sum ='''select count(*) from tb_review where create_time <date_format(now(),'%Y-%m-%d 00:00:00')'''
cursor = connections['default'].cursor()
cursor.execute(sql_sum)
sum_row = cursor.fetchone()
sql_arry = '''SELECT t.id,t.name,t.db_name,t.content,t.flag,t.review_time, uu.username,t.remark_user FROM (SELECT u.username as name, d.db_name, r.id,r.content, r.flag, r.remarks, r.review_time, r.review_id,r.remark_user FROM auto_database.tb_review r, tb_databases_config d, auth_user u WHERE r.database_id = d.id AND r.creator = u.id and r.create_time <date_format(now(),'%Y-%m-%d 00:00:00')) AS t LEFT JOIN auth_user uu ON t.review_id = uu.id order by t.id desc limit {0},{1}''' .format(start,length)
cursor = connections['default'].cursor()
cursor.execute(sql_arry)
all_row = cursor.fetchall()
dict = collections.OrderedDict()
dict['draw'] = draw
dict['recordsTotal'] = sum_row[0]
dict['recordsFiltered'] = sum_row[0]
dict['data'] = all_row
json_object = json.dumps(dict,cls=DateEncoder)
return write(json_object)
else:
return render(request, 'review/404.html', context=None)
except Exception,e:
logging.error(e)
return render(request, 'review/500.html',context=None)
def reviewPost(request):
'''所有审核内容上报列表'''
try:
if request.method == "GET" and request.user.is_staff == 1:
start = 0
length = 0
sumcnt = 0
draw = request.GET.get('draw',None)
start = request.GET.get('start',None)
length = request.GET.get('length',None)
sql_sum ='''select count(*) from tb_review where create_time >date_format(now(),'%Y-%m-%d 00:00:00')'''
# print sql_sum
cursor = connections['default'].cursor()
cursor.execute(sql_sum)
sum_row = cursor.fetchone()
sql_arry = '''select t.id,d.db_name,t.database_id,t.content,t.flag,t.create_time,t.username,t.remarks,t.remark_user from (SELECT r.id as id,database_id,content,create_time ,u.username,r.flag,r.remarks,r.remark_user FROM tb_review as r left join auth_user as u ON r.creator=u.id where r.create_time>date_format(now(),'%Y-%m-%d 00:00:00') ) as t left join tb_databases_config d on t.database_id=d.id order by t.id desc limit {0},{1};''' .format(start,length)
cursor = connections['default'].cursor()
cursor.execute(sql_arry)
all_row = cursor.fetchall()
dict = collections.OrderedDict()
dict['draw'] = draw
dict['recordsTotal'] = sum_row[0]
dict['recordsFiltered'] = sum_row[0]
dict['data'] = all_row
json_object = json.dumps(dict,cls=DateEncoder)
return write(json_object)
else:
return render(request, 'review/404.html', context=None)
except Exception,e:
logging.error(e)
return render(request, 'review/500.html',context=None)
def index_char_pie_ddl(request):
try:
if request.user.is_staff == 1 and request.method == "POST":
sql_stat = '''select
SUM(altertable) AS altab,
SUM(renaming) AS renm,
SUM(createindex) AS cidx,
SUM(dropindex) AS didx,
SUM(addcolumn) AS addc,
SUM(changecolumn) AS chgc,
SUM(createtable) AS ctab,
SUM(truncating) AS tcat
from
statistic;'''
cursor = connections['data_backup'].cursor()
cursor.execute(sql_stat)
sum_stat = cursor.fetchone()
print sum_stat
dict = collections.OrderedDict()
dict['stat'] = sum_stat
json_object = json.dumps(dict, cls=DecimalEncoder)
return write(json_object)
else:
return render(request, 'review/404.html', context=None)
except Exception, e:
logging.info(e)
def userPofileUpdate(request):
try:
print request.method
if request.method == 'POST':
password = request.POST.get('password', None)
email = request.POST.get('email', None)
confirm = request.POST.get('confirm', None)
print "%s,%s,%s" % (confirm, email, password)
if confirm == password:
print '1'
hash_passwd = make_password(confirm)
print hash_passwd
id = request.user.id
print id
sql = '''update auth_user set email="{0}",password="******" where id={2}'''.format(
email, hash_passwd, id)
print sql
cursor = connections['default'].cursor()
cursor.execute(sql)
sql_sum = '''select count(*) from tb_review where flag=0 and create_time >date_format(now(),'%Y-%m-%d 00:00:00')'''
cursor = connections['default'].cursor()
cursor.execute(sql_sum)
sum_row = cursor.fetchone()
sql_database = '''select id,db_tag from tb_databases_config'''
cursor = connections['default'].cursor()
cursor.execute(sql_database)
data_row = cursor.fetchall()
name = request.user.username
dict = {}
dict['flag'] = 1
dict['user'] = name
dict['taskCount'] = sum_row
dict['database'] = data_row
return render(request,
'review/user_profile_update.html',
context=dict)
else:
sql_sum = '''select count(*) from tb_review where flag=0 and create_time >date_format(now(),'%Y-%m-%d 00:00:00')'''
cursor = connections['default'].cursor()
cursor.execute(sql_sum)
sum_row = cursor.fetchone()
sql_database = '''select id,db_tag from tb_databases_config'''
cursor = connections['default'].cursor()
cursor.execute(sql_database)
data_row = cursor.fetchall()
name = request.user.username
dict = {}
dict['flag'] = 0
dict['user'] = name
dict['taskCount'] = sum_row
dict['database'] = data_row
return render(request,
'review/user_profile_update.html',
context=dict)
else:
return render(request, 'review/500.html', context=None)
except Exception, e:
logging.info(e)
return write(0)
def get_queryset(self):
try:
sid = self.request.GET.get('sqlid', None)
sql_result = SqlAuditExecute.executeResultList(sql_id=sid)
return sql_result
except Exception as e:
return write(e)
def index_char_line(request):
try:
if request.user.is_staff == 1 and request.method == "POST":
sql_stat = '''SELECT
*
FROM
(SELECT
amount, DATE_FORMAT(data_date, '%Y-%m-%d') dt
FROM
auto_database.tb_review_stat
ORDER BY create_date DESC
LIMIT 7) t
ORDER BY t.dt ASC'''
print sql_stat
cursor = connections['default'].cursor()
cursor.execute(sql_stat)
sum_stat = cursor.fetchall()
dict = collections.OrderedDict()
dict['stat'] = sum_stat
json_object = json.dumps(dict, cls=DecimalEncoder)
return write(json_object)
else:
return render(request, 'review/404.html', context=None)
except Exception, e:
logging.info(e)
def sql_rollback(request):
try:
if request.method == 'POST':
x_id = request.POST.get('xlh', None)
db_bak = request.POST.get('db_bak', None)
sql_id = request.POST.get('sql_id', None)
sql_1 = '''select tablename from {0}.$_$Inception_backup_information$_$ where opid_time={1};'''.format(
db_bak, x_id)
cursor = connections['data_backup'].cursor()
cursor.execute(sql_1)
row_1 = cursor.fetchone()
sql_2 = '''select rollback_statement from {0}.{1} where opid_time={2};'''.format(db_bak, row_1[0], x_id)
cursor = connections['data_backup'].cursor()
cursor.execute(sql_2)
row_2 = cursor.fetchall()
SqlAuditExecute().rollback(sqlid=sql_id, content=row_2[0])
return write(1)
else:
return write(0)
except Exception as e:
print (e)
return write(0)
def sql_pre_audit(request):
try:
dbid = request.POST.get('dbid',None)
ct = request.POST.get('content',None)
sql_id = request.POST.get('sqlid',None)
row = SqlAuditExecute.get_db_info(db_id=dbid)
audit_sql = Inception().preAuditExecute(user=row[1],password=row[2],host=row[0],port=row[3],dbname=row[4],content=ct)
if request.method == "POST":
result = Inception().inceptionQuery(audit_sql)
flag = []
for row in result:
flag.append(row[2])
rep_str = row[5]
strs = rep_str.replace("'","\\'")
strss = strs.replace('"', "\\'")
Inception().insert_audit_res(row=row,strss=strss,sql_id=sql_id )
Inception().update_audit_flag(sql_id=sql_id)
return write(1)
else:
return write(0)
except Exception as e:
return write(2)
def reviewBak(request):
try:
if request.method == 'POST' and request.user.is_staff == 1:
sql_id = request.POST.get('id',None)
bak = request.POST.get('bak',None)
res = tb_review.objects.get(id=sql_id)
res.remarks=bak
res.save()
return write(1)
else:
return render(request, 'review/500.html', context=None)
except Exception,e:
logging.info(e)
return render(request, 'review/404.html', context=None)
def reviewRollBack(request):
try:
if request.method == 'POST':
x_id = request.POST.get('xlh', None)
db_bak = request.POST.get('db_bak', None)
sql_id = request.POST.get('sql_id', None)
sql_1 = '''select tablename from {0}.$_$Inception_backup_information$_$ where opid_time={1};'''.format(
db_bak, x_id)
cursor = connections['data_backup'].cursor()
cursor.execute(sql_1)
row_1 = cursor.fetchone()
sql_2 = '''select rollback_statement from {0}.{1} where opid_time={2};'''.format(
db_bak, row_1[0], x_id)
cursor = connections['data_backup'].cursor()
cursor.execute(sql_2)
row_2 = cursor.fetchall()
rollback(sqlid=sql_id, content=row_2[0])
return write(1)
else:
return render(request, 'review/500.html', context=None)
except Exception, e:
logging.info(e)
return write(0)
def index_char_pie(request):
try:
user_type = getUserType(request)
if user_type == 1 and request.method == "POST":
sql_stat = '''select sum(deleting) del,sum(inserting) as ins,sum(updating) as upd from statistic'''
cursor = connections['data_backup'].cursor()
cursor.execute(sql_stat)
sum_stat = cursor.fetchone()
dict = collections.OrderedDict()
dict['stat'] = sum_stat
json_object = json.dumps(dict, cls=DecimalEncoder)
return write(json_object)
else:
return render(request, 'review/404.html', context=None)
except Exception,e:
logging.info(e)
def sqlUpdateActive(request):
'''审核内容更新'''
try:
print request.method
if request.method == "POST" and request.user.is_staff == 0:
sid = request.POST.get('sid', None)
content = request.POST.get('text', None)
bak = request.POST.get('bak', None)
ct = tb_review.objects.get(id=sid)
ct.content = content
ct.remark_user = bak
ct.flag = 0
ct.save()
return write(1)
else:
return render(request, 'review/404.html', context=None)
except Exception, e:
logging.error(e)
def get_sql_history_list(request):
try:
sql = SqlAuditExecute()
return write(sql.sql_alter_history_list(request=request))
except Exception as e:
return write(e)
def get_sql_list(self, request):
try:
return write(SqlAuditExecute.sql_alter_list(request=request))
except Exception as e:
return write(e)
