def get_brief_list(self, **cond):
''' 集群详情中获取主机列表
'''
arg = []
extra = ''
if len(cond) > 1: # 暂时保留cluster_id，但实际没用到
extra = 'WHERE '
e = []
if cond.get('provider'):
e.append(get_in_formats(field='i.provider', contents=cond['provider']))
if isinstance(cond['provider'], list):
arg.extend(cond['provider'])
else:
arg.append(cond['provider'])
if cond.get('region'):
e.append(get_in_formats(field='i.region_name', contents=cond['region']))
if isinstance(cond['region'], list):
arg.extend(cond['region'])
else:
arg.append(cond['region'])
if cond.get('cluster_id'):
e.append('s.cluster_id=%s')
arg.append(cond['cluster_id'])
if cond.get('lord'):
e.append('s.lord=%s')
arg.append(cond['lord'])
if cond.get('form'):
e.append('s.form=%s')
arg.append(cond['form'])
extra += ' AND '.join(e)
sql = """
SELECT s.id, s.name, s.public_ip, s.cluster_id, i.instance_id, i.provider, i.instance_name, i.region_name AS address, i.status AS machine_status
FROM server s
JOIN instance i USING(instance_id)
"""+"""
{where}
ORDER BY i.provider
""".format(where=extra)
cur = yield self.db.execute(sql, arg)
data = cur.fetchall()
# 添加最新上报信息
report_info = self.redis.hgetall(SERVERS_REPORT_INFO)
for d in data:
info = fake_report_info() if is_faker(d['instance_id']) else json_loads(report_info.get(d['public_ip']))
d.update(info)
data = sorted(data, key=lambda x: x['name'], reverse=True)
return data
def _get_performance(self, table, params):
"""
:param table: cpu/memory/disk
:param params: {'public_ip': str, 'start_time': timestamp, 'end_time': timestamp}
"""
id_sql = """
SELECT id FROM {table}
WHERE public_ip=%s AND created_time>=%s AND created_time<%s
ORDER BY created_time DESC
""".format(table=table)
cur = yield self.db.execute(id_sql, [params['public_ip'], params['start_time'], params['end_time']])
ids = [i['id'] for i in cur.fetchall()]
if not ids:
return []
step = len(ids)//7
choose_id = ids
if step:
choose_id = [ids[i] for i in range(0, len(ids), step)]
ids = get_in_formats(field='id', contents=choose_id)
sql = """
SELECT created_time,content FROM {table}
WHERE {ids}
""".format(table=table, ids=ids)
cur = yield self.db.execute(sql, choose_id)
data = []
for x in cur.fetchall():
created_time = {'created_time': x['created_time']}
content = json.loads(x['content'])
content.update(created_time)
data.append(content)
return data
def _get_container_performance(self, params):
sql = """
SELECT id from {table}
WHERE public_ip=%s AND container_name=%s
AND created_time>= %s AND created_time < %s
ORDER BY created_time DESC
""".format(table='docker_stat')
cur = yield self.db.execute(sql, [params['public_ip'], params['container_name'],
params['start_time'], params['end_time']])
ids = [i['id'] for i in cur.fetchall()]
if not ids:
return {}
step = len(ids)//7
choose_id = ids
if step:
choose_id = [ids[i] for i in range(0, len(ids), step)]
ids = get_in_formats(field='id', contents=choose_id)
sql = """
SELECT created_time, content FROM {table}
WHERE {ids}
""".format(table='docker_stat', ids=ids)
cur = yield self.db.execute(sql, choose_id)
data = {
'cpu': [],
'memory': [],
'net': [],
'block': []
}
for x in cur.fetchall():
content = json.loads(x['content'])
cpu = {
'percent': content['cpu'],
'created_time': x['created_time'],
}
data['cpu'].append(cpu)
memory = {
'percent': content['mem_percent'],
'created_time': x['created_time'],
}
data['memory'].append(memory)
net = {
'input': content['net_input'],
'output': content['net_output'],
'created_time': x['created_time'],
}
data['net'].append(net)
block = {
'input': content['block_input'],
'output': content['block_output'],
'created_time': x['created_time'],
}
data['block'].append(block)
return data
def check_exist(self, table, ids, extra=''):
if not ids:
return ''
ids = [i for i in ids.split(',')]
conds = get_in_formats('id', ids)
sql = """
SELECT id FROM {table} WHERE {conds} {extra}
""".format(table=table, conds=conds, extra=extra)
cur = yield self.db.execute(sql, ids)
data = ','.join([str(i['id']) for i in cur.fetchall()])
return data
def make_pair(self, args=None):
''' 根据args生成conds, params
:param args: {'name': 'foo', 'age': [20, 30]}
:return ['name=%s', 'status in (%s, %s)'], ['foo', 20, 30]
'''
if args is None: args = {}
conds, params = [], []
for k, v in args.items():
if isinstance(v, list):
flag, k = self._has_neg(k)
c = get_not_in_formats(k, v) if flag else get_in_formats(k, v)
conds.append(c)
params.extend(v)
else:
flag, k = self._has_neg(k)
c = k + '!=%s' if flag else k + '=%s'
conds.append(c)
params.append(v)
return conds, params
def _to_del(self, instances):
for table in ['server', 'instance']:
sql = 'DELETE FROM ' + table + ' WHERE ' + get_in_formats(
'instance_id', instances)
self.cur.execute(sql, instances)