def get_database(self):
sql = """ select schema_name, default_character_set_name, default_collation_name
from information_schema.schemata
where schema_name not in ('sys', 'test', 'information_schema', 'performance_schema', 'mysql')"""
if self.cur:
functions.Logger().log('{0}--开始收集库的基础数据'.format(self.host))
result = self.cur.conn_query(sql)
for item in result:
md_str = item[0] + item[1] + item[2]
md5_str = functions.py_password(md_str)
db_flag = models.MetaDataDatabase.objects.filter(
host_ip=self.host, schema_name=item[0])
try:
if db_flag:
md5_flag = models.MetaDataDatabase.objects.filter(
db_md5=md5_str)
if not md5_flag:
models.MetaDataDatabase.objects.update(
default_character_set_name=item[1],
default_collation_name=item[2],
db_md5=md5_str)
else:
models.MetaDataDatabase.objects.create(
host_ip=self.host,
schema_name=item[0],
default_character_set_name=item[1],
default_collation_name=item[2],
db_md5=md5_str)
except Exception as e:
functions.Logger().log(
'{0}--收集库的基础数据失败--{1}'.format(self.host, str(e)),
False)
def post(self, request):
reg_form = forms.RegisterForm(request.POST)
ret = reg_form.is_valid()
if ret:
r = models.UserInfo.objects.create(
user_name=request.POST['username'],
user_pass=functions.py_password(request.POST['password']),
email=request.POST['email'],
lock_flag=0)
models.UserRoleRelationship.objects.create(role_id=2, user=r)
models.UserGroupRelationship.objects.create(user=r, group_id=2)
return redirect('login.html')
else:
return render(request, 'register.html', {'RegForm': reg_form})
def login(request):
if request.method == 'POST':
username = request.POST.get('username', None)
pwd = request.POST.get('password', None)
pass_str = functions.py_password(pwd)
user = cmdb_models.UserInfo.objects.filter(user_name=username,
user_pass=pass_str)
if user:
res = redirect('/dbms/index.html')
res.set_signed_cookie('userinfo', username, salt='adfsfsdfsd')
return res
else:
return render(request, 'login.html', {'user_error': '用户名或密码错误'})
else:
return render(request, 'login.html')
def register(request):
if request.method == 'POST':
user = request.POST.get('username', None)
pwd = request.POST.get('password', None)
email = request.POST.get('Email', None)
user_info = cmdb_models.UserInfo.objects.filter(user_name=user)
pass_str = functions.py_password(pwd)
if user_info:
# username and password is existe
# 通过 ajax 返回账号已经存在的信息
return render(request, 'register.html')
else:
cmdb_models.UserInfo.objects.create(user_name=user,
user_pass=pass_str,
user_emails=email,
role_id=2,
user_group_id=2,
permission_id=2)
return redirect('/cmdb/login')
else:
return render(request, 'register.html')
def post(self, request):
LoginForm = forms.LoginForm(request.POST)
if LoginForm.is_valid():
username = request.POST.get("username", None)
password = functions.py_password(request.POST.get(
"password", None))
user_info = models.UserInfo.objects.filter(
(Q(user_name=username) | Q(email=username)),
user_pass=password).all()
if user_info:
rem_flag = request.POST.get('remember', None)
sess = functions.OpSession()
if rem_flag:
sess.login(request, username, 1)
else:
sess.login(request, username)
return redirect('/index.html')
else:
return render(request, 'login.html', {
'login_form': LoginForm,
"form_error": "用户名或者密码错误"
})
else:
return render(request, 'login.html', {'login_form': LoginForm})
models.AppType.objects.create(id=2, app_name='Java')
# 初始化权限
models.AuthPermissions.objects.create(id=1,
select_host=1,
update_host=1,
insert_host=1,
delete_host=1,
select_user=1,
update_user=1,
delete_user=1,
insert_user=1)
models.AuthPermissions.objects.create(id=2,
select_host=1,
update_host=0,
insert_host=0,
delete_host=0,
select_user=1,
update_user=0,
delete_user=0,
insert_user=0)
# 初始化管理员用户
pass_str = functions.py_password('123456')
models.UserInfo.objects.create(id=1,
user_name='admin',
user_pass=pass_str,
user_emails='*****@*****.**',
role_id=1,
user_group_id=1,
permission_id=1)
def get_columns(self):
sql = """
SELECT
table_schema,
table_name,
column_name,
column_type,
collation_name,
is_nullable,
column_key,
column_default,
extra,
PRIVILEGES,
column_comment
FROM information_schema.columns
where table_schema not in ('sys', 'test', 'information_schema', 'performance_schema', 'mysql')
"""
if self.cur:
functions.Logger().log('{0}--开始收集列的基础数据'.format(self.host))
result = self.cur.conn_query(sql)
for item in result:
collation_name = item[4] if item[4] else '---'
column_key = item[6] if item[6] else '---'
column_default = item[7] if item[7] else '---'
extra = item[8] if item[8] else '---'
column_comment = item[10] if item[10] else '---'
md_str = self.host + item[0] + item[1] + item[2] + item[3] + collation_name + str(item[5]) + column_key\
+ column_default + extra + item[9] + column_comment
column_md5 = functions.py_password(md_str)
try:
column_flag = models.MetaDataColumns.objects.filter(
host_ip=self.host,
table_schema=item[0],
table_name=item[1],
column_name=item[2],
)
if column_flag:
md5_flag = models.MetaDataColumns.objects.filter(
column_md5=column_md5)
if not md5_flag:
models.MetaDataColumns.objects.update(
column_type=item[3],
collation_name=collation_name,
is_nullable=item[5],
column_key=column_key,
column_default=column_default,
extra=extra,
PRIVILEGES=item[9],
column_comment=column_comment,
column_md5=column_md5)
structure_result = self.cur.conn_query(
'show create table {0}.{1}'.format(
item[0], item[1]))
for row in structure_result:
models.MetaDataTableStructure.objects.filter(
table__host_ip=self.host).update(
content=row[1])
else:
models.MetaDataColumns.objects.create(
host_ip=self.host,
table_schema=item[0],
table_name=item[1],
column_name=item[2],
column_type=item[3],
collation_name=collation_name,
is_nullable=item[5],
column_key=column_key,
column_default=column_default,
extra=extra,
PRIVILEGES=item[9],
column_comment=column_comment,
column_md5=column_md5)
except Exception as e:
functions.Logger().log(
'{0}--收集列的基础数据失败--{1}'.format(self.host, str(e)),
False)
def get_indexs(self):
sql = """
select
table_schema,
table_name,
column_name,
non_unique,
index_name,
seq_in_index,
cardinality,
nullable,
index_type,
index_comment
from information_schema.statistics
where table_schema not in ('sys', 'test', 'information_schema', 'performance_schema', 'mysql') """
if self.cur:
functions.Logger().log('{0}--开始收集索引的基础数据'.format(self.host))
result = self.cur.conn_query(sql)
for item in result:
cardinality = item[6] if item[6] else 0
md_str = self.host + item[0] + item[1] + item[2] + str(item[3]) + item[4] + str(item[5]) +\
str(cardinality) + str(item[7]) + item[8] + item[9]
md5_str = functions.py_password(md_str)
try:
index_flag = models.MetaDataIndexs.objects.filter(
host_ip=self.host,
table_schema=item[0],
table_name=item[1],
column_name=item[2],
).all()
if index_flag:
md5_flag = models.MetaDataIndexs.objects.filter(
index_md5=md5_str)
if not md5_flag:
models.MetaDataIndexs.objects.update(
non_unique=item[3],
index_name=item[4],
seq_in_index=item[5],
cardinality=cardinality,
nullable=item[7],
index_type=item[8],
index_comment=item[9],
index_md5=md5_str)
else:
models.MetaDataIndexs.objects.create(
host_ip=self.host,
table_schema=item[0],
table_name=item[1],
column_name=item[2],
non_unique=item[3],
index_name=item[4],
seq_in_index=item[5],
cardinality=cardinality,
nullable=item[7],
index_type=item[8],
index_comment=item[9],
index_md5=md5_str)
except Exception as e:
functions.Logger().log(
'{0}--收集索引的基础数据失败--{1}'.format(self.host, str(e)),
False)
def get_tables(self):
sql = """SELECT table_schema, table_name, engine, row_format, table_rows, avg_row_length,
data_length, max_data_length, index_length, data_free, auto_increment,
table_collation, table_comment, create_time, update_time, check_time
FROM information_schema.tables
where table_schema not in ('sys', 'test', 'information_schema', 'performance_schema', 'mysql')"""
if self.cur:
functions.Logger().log('{0}--开始收集表的基础数据'.format(self.host))
result = self.cur.conn_query(sql)
for item in result:
c_time = u_time = check_time = None
if item[13]:
c_time = time.strftime(
item[13].strftime('%Y-%m-%d %H:%M:%S'))
if item[14]:
u_time = time.strftime(
item[14].strftime('%Y-%m-%d %H:%M:%S'))
if item[15]:
check_time = time.strftime(
item[15].strftime('%Y-%m-%d %H:%M:%S'))
table_schema = item[0]
table_name = item[1]
engine = item[2] if item[2] else '---'
row_format = item[3] if item[3] else '---'
table_rows = item[4] if item[4] else 0
avg_row_length = item[5] if item[5] else 0
max_data_length = item[7] if item[7] else 0
data_length = item[6] if item[6] else 0
index_length = item[8] if item[8] else 0
data_free = item[9] if item[9] else 0
auto_increment = item[10] if item[10] else 0
table_collation = item[11] if item[11] else '---'
table_comment = item[12] if item[12] else '---'
md_str = item[0] + item[1] + engine + row_format + str(table_rows) + str(avg_row_length) +\
str(max_data_length) + str(data_length) + str(index_length) + str(data_free) +\
str(auto_increment) + table_collation + table_comment
md5_str = functions.py_password(md_str)
try:
filter_flag = models.MetaDataTables.objects.filter(
host_ip=self.host,
table_schema=table_schema,
table_name=table_name).all()
if filter_flag:
md5_flag = models.MetaDataTables.objects.filter(
table_md5=md5_str).all()
if not md5_flag:
models.MetaDataTables.objects.update(
engine=engine,
row_format=row_format,
table_rows=table_rows,
avg_row_length=avg_row_length,
max_data_length=max_data_length,
data_length=data_length,
index_length=index_length,
data_free=data_free,
auto_increment=auto_increment,
table_collation=table_collation,
create_time=c_time,
update_time=u_time,
check_time=check_time,
table_comment=table_comment)
else:
r = models.MetaDataTables.objects.create(
host_ip=self.host,
table_schema=table_schema,
table_name=table_name,
engine=engine,
row_format=row_format,
table_rows=table_rows,
avg_row_length=avg_row_length,
max_data_length=max_data_length,
data_length=data_length,
index_length=index_length,
data_free=data_free,
auto_increment=auto_increment,
table_collation=table_collation,
create_time=c_time,
update_time=u_time,
check_time=check_time,
table_comment=table_comment,
chip_size=0,
table_md5=md5_str)
structure_result = self.cur.conn_query(
'show create table {0}.{1}'.format(
item[0], item[1]))
for row in structure_result:
models.MetaDataTableStructure.objects.create(
table=r, content=row[1])
except Exception as e:
functions.Logger().log(
'{0}--收集表的基础数据失败--{1}'.format(self.host, str(e)),
False)