def get_ip_from_db(request):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "SELECT DISTINCT IP FROM Info_DatabaseInfo"
infos = connection.select_sql(sql)
result = json.dumps(infos)
return HttpResponse(result)
def get_account_with_ip(request, ip):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select account from Info_DbAccount where ip = '%s'" % ip
infos = connection.select_sql(sql)
result = json.dumps(infos)
return HttpResponse(result)
def get_databases_with_ip(request, ip):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select dbname from Info_DatabaseInfo where ip = '%s'" % ip
infos = connection.select_sql(sql)
result = json.dumps(infos)
return HttpResponse(result)
def get_log(request, t):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
if t == 'account':
sql = "select * from Info_ToolsLog where actiontype in (2,4)"
info = connection.select_sql(sql)
logs = []
for i in info:
log = {
'action_ip': i[1],
'type': i[2],
'account': i[3],
'action_time': str(i[5])[:-7]
}
logs.append(log)
elif t == 'databases':
sql = "select * from Info_ToolsLog where actiontype in (1,3)"
info = connection.select_sql(sql)
logs = []
for i in info:
log = {
'action_ip': i[1],
'type': i[2],
'database': i[4],
'action_time': str(i[5])[:-7]
}
logs.append(log)
else:
sql = "select * from Info_ToolsLog where actiontype in (0,10)"
info = connection.select_sql(sql)
logs = []
for i in info:
log = {
'action_ip': i[1],
'type': i[2],
'account': i[3],
'database': i[4],
'action_time': str(i[5])[:-7]
}
logs.append(log)
logs = json.dumps(logs)
return HttpResponse(logs)
def get_accounts(request):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select ip,account,info from Info_DbAccount"
infos = connection.select_sql(sql)
accounts = []
for i in infos:
account_info = {
'ip': i[0],
'account': i[1],
'info': i[2]
}
accounts.append(account_info)
result = json.dumps(accounts)
return HttpResponse(result)
def get_databases(request):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select ip,dbname,info from Info_DatabaseInfo"
infos = connection.select_sql(sql)
databases = []
for i in infos:
database_info = {
'ip': i[0],
'dbname': i[1],
'info': i[2]
}
databases.append(database_info)
result = json.dumps(databases)
return HttpResponse(result)
def login(request):
if request.method == 'POST':
# 获取账号密码
user_name = request.POST.get('account')
password = request.POST.get('password')
# 获取IP
action_ip = db_register_view.get_action_ip(request)
# 域认证
if check_api.ldap_api(user_name, password):
# 认证成功在系统内注册用户，更新登录时间
user_id = ldap_users.login_control(action_ip, user_name)
# user_id存入session
request.session['user_id'] = user_id
return HttpResponseRedirect('/welcome')
else:
return HttpResponseRedirect('/')
else:
return HttpResponseRedirect('/')
def get_plan_info(request):
user_id = request.session['user_id']
action_ip = dbregister_views.get_action_ip(request)
conn = dbconnecter.Connection(action_ip)
sql = "SELECT top 25 [IP],[DbID],[AccountID],[StartTime],[EndTime],[IsDone],[ID] FROM [dbo].[Exec_PlanInfo] " \
"where [UserID] = %d order by IsDone ,StartTime desc,EndTime DESC" % user_id
result = list(conn.select_sql(sql))
column = ['IP', 'DbID', 'AccountID', 'StartTime', 'EndTime', 'IsDone', 'ID']
plan_info = []
for i in result:
i = list(i)
i[1] = get_database_with_id(action_ip, i[1])['DbName']
i[2] = get_account_with_id(action_ip, i[2])['AccountName']
i[3], i[4] = str(i[3])[:-7], str(i[4])[:-7]
if not i[5]:
i[5] = u"未完成"
else:
i[5] = u"执行成功"
plan_info.append(dict(zip(column, i)))
return HttpResponse(json.dumps(plan_info))
def index(request):
action_ip = db_reister.get_action_ip(request)
if action_ip == '127.0.0.1':
return render(request, 'Log/Base.html')
else:
return render(request, 'Log/NotPermit.html')
def exec_sql(request):
# request method 检测
if request.method == 'POST':
sql_files = request.FILES.getlist('sqlFiles')
ip = request.POST['exec_ip']
account = request.POST['exec_account']
databases = request.POST.getlist('exec_db')
# 读取配置文件
conf = config.Configer()
# 获取用户IP
action_ip = db_register_view.get_action_ip(request)
# 检测脚本存放路径
if not os.path.exists(conf.get_script_path()):
# 不存在则创建脚本路径
print "SCRIPT_PATH is not existed. Create"
try:
os.mkdir(conf.get_script_path())
except Exception, e:
# 创建失败返回错误
s = "Create SCRIPT_PATH error.<br>" + str(e)
return HttpResponse(s)
# 错误队列
all_error_info = []
# 审核脚本内容
for sql_file in sql_files:
check_result = sqlcheck.check_sql_content(sql_file)
if not check_result['result']:
all_error_info.append({
'errorInfo': check_result['error'],
'fileName': sql_file.name
})
# 无错误信息则落地文本
if all_error_info.__len__() == 0:
# 为脚本生成一个基于时间戳+随机数的UUID目录
script_path = os.path.join(
conf.get_script_path(),
str(action_ip).replace('.', '_') + '_' +
str(uuid.uuid1()).replace('-', ''))
# 如果就是巧了重复，那就再随一个，随到没有重复为止
while os.path.exists(script_path):
script_path = os.path.join(
conf.get_script_path(),
str(action_ip).replace('.', '_') + '_' +
str(uuid.uuid1()).replace('-', ''))
# 创建脚本存放目录
try:
os.mkdir(script_path)
except Exception, e:
s = u'创建目录%s失败，错误详情：%S' % (script_path, e)
return HttpResponse(s)
# 脚本落盘
for f in sql_files:
file_to_modify.save_file_to_disk(
f, os.path.join(script_path, f.name))
# 将script_path下的所有文件编码转换成utf8
file_to_modify.change_file_to_utf8(script_path)
# 将script_path下的脚本去除USE
file_to_modify.remove_use(script_path)
# 生成执行计划
owner_id = request.session['user_id']
for db_name in databases:
sqlplan.init_exec_plan(action_ip, ip, db_name, account,
script_path, owner_id)
for db_name in databases:
sqlplan.exec_exec_plan(action_ip, ip, db_name, account,
script_path)
return HttpResponseRedirect('/welcome')