def count_ip_total(subnet_ids=None): """ :param subnet_ids: :return: counts """ params = [] result = {} db = dbpools.get_pool(dbpools.NEUTRON_DB) try: sql = "select subnet_id, first_ip,last_ip from ipallocationpools where 1=1 " if subnet_ids: sql += " and subnet_id in %s " params.append(tuple(subnet_ids)) cur = yield db.execute(sql, params) ippool_all = cur.fetchall() for item in ippool_all: ip_start = int(netaddr.IPAddress(item["first_ip"])) ip_end = int(netaddr.IPAddress(item["last_ip"])) ip_total = ip_end - ip_start + 1 if item["subnet_id"] in result: result[item["subnet_id"]] += ip_total else: result[item["subnet_id"]] = ip_total except Exception, e: raise e
def get_securitygrouprules_by_db(rule_id=None, rule_info=None, tenant_id=None): """get securitygroups from database""" try: db = dbpools.get_pool(dbpools.NEUTRON_DB) sql = "select sgr.tenant_id, sgr.id, sgr.security_group_id, sgr.remote_group_id, " \ "sgr.direction, sgr.ethertype, sgr.protocol, sgr.port_range_min, " \ "sgr.port_range_max, sgr.remote_ip_prefix " \ "from securitygroups sg, securitygrouprules sgr " \ "where sgr.security_group_id = sg.id and sg.name = 'default'" if tenant_id: sql += " and sg.tenant_id='%s' " % tenant_id if rule_id: sql += " and sgr.id='%s' " % rule_id if rule_info: if rule_info.get("direction"): sql += " and sgr.direction='%s' " % rule_info.get("direction") if rule_info.get("cidr"): sql += " and sgr.remote_ip_prefix='%s' " % rule_info.get( "cidr") if rule_info.get("cidr"): sql += " and sgr.protocol='%s' " % rule_info.get("protocol") if rule_info.get("to_port"): sql += " and sgr.port_range_max='%s' " % rule_info.get( "to_port") if rule_info.get("from_port"): sql += " and port_range_min='%s' " % rule_info.get("from_port") if rule_info.get("from_port"): sql += " and sgr.ethertype='%s' " % rule_info.get("ethertype") cur = yield db.execute(sql) all_data = cur.fetchall() except Exception as e: LOG.error("get securitygrouprules error %s" % e) raise SecGroupRuleOperationFailed() raise gen.Return(all_data)
def get_resource(woid): """ """ db = dbpools.get_pool(dbpools.COMMON_DB) sql = "select * from resource where wo_id = %s" resources = [] cur = yield db.execute(sql, [woid]) if not cur: raise WorkOrderIdNotFoundException rs = cur.fetchall() for r in rs: res = dict() res["name"] = r["name"] res["displayname"] = r["displayname"] res["status"] = r["status"] res["id"] = r["id"] res["type"] = r["type"] res["uuid"] = r["uuid"] res["remark"] = r["remark"] pros = yield pro.get_pros(db, r["id"]) for p in pros: if p['name'] in __VM_INT_PRO: res[p['name']] = int(p['value']) elif p['name'] in __VM_DICT_PRO: res[p['name']] = json.loads(p['value']) else: res[p['name']] = p['value'] resources.append(res) raise gen.Return(resources)
def query_subnet_tenant_all(subnet_id=None, tenant_id=None): """ query ips of vlan :param subnet_id: vlan id :param tenant_id: tenant id :return: vlan ips """ db = dbpools.get_pool(dbpools.LOCAL_DB) try: if tenant_id and subnet_id: sql = "select * from vlan_subnet_tenant where subnet_id = '%s' and tenant_id = '%s'" % ( subnet_id, tenant_id) elif not tenant_id and subnet_id: sql = "select * from vlan_subnet_tenant where subnet_id = '%s'" % subnet_id elif tenant_id and not subnet_id: sql = "select * from vlan_subnet_tenant where tenant_id = '%s'" % tenant_id else: sql = "select * from vlan_subnet_tenant" cur = yield db.execute(sql) ips = cur.fetchall() if not ips: ips = [] except Exception as e: LOG.error(e) raise e raise gen.Return(ips)
def get_subnet_db(network_id=None, subnet_ids=None, name=None, cidr=None): """ Get network info with sub_net info from neutron-network :param subnet_ids: :param network_id: network id list :param name: network name """ try: if subnet_ids and isinstance(subnet_ids, basestring): subnet_ids = [subnet_ids] params = [] db = dbpools.get_pool(dbpools.NEUTRON_DB) sql = "select a.id, a.network_id, a.`name`, a.cidr, a.gateway_ip as gateway ,b.`name` as network_name from subnets as a left join networks as b on a.network_id = b.id where 1=1" if subnet_ids: sql += " and a.id in %s" params.append(tuple(subnet_ids)) if cidr: sql += " and a.cidr like %s " cidr = cidr[:cidr.index("/")] + "%" params.append(cidr) if name: sql += " and a.name = %s" params.append(name) if network_id: sql += " and a.network_id = %s" params.append(network_id) cur = yield db.execute(sql, params) networks = cur.fetchall() except Exception, e: LOG.error("Get network info with sub_net info error: %s" % e) raise e
def count_vlan_ip_used(subnet_ids=None, dhcp=False): """ Query used ips count of vlan :param subnet_ids: subnet_ids :return: """ params = [] result = {} db = dbpools.get_pool(dbpools.NEUTRON_DB) try: sql = "select ipl.subnet_id, count(*) as total from ipallocations ipl " \ "left join ports p on p.id = ipl.port_id " \ "where 1=1 " if subnet_ids: sql += "and subnet_id in %s " params.append(tuple(subnet_ids)) if dhcp: sql += "and p.device_owner='network:dhcp' " sql += "group by subnet_id " cur = yield db.execute(sql, params) ips = cur.fetchall() for item in ips: result[item["subnet_id"]] = item["total"] except Exception, e: LOG.error("count vlan ips used error: %s" % e) raise e
def query_subnet_tenants(subnet_ids): """ Query tenants of subnet :param subnet_ids: str or list :return: [{ "tenant_id":"xxx", "subnet_id":"xxx" },{ "tenant_id":"xxx", "subnet_id":"xxx" },...] """ subnets = [] if isinstance(subnet_ids, str): subnets = [subnet_ids] try: db = dbpools.get_pool(dbpools.LOCAL_DB) params = [] sql = "select tenant_id, subnet_id from vlan_subnet_tenant where 1=1" if subnets: sql += " and subnet_id in %s" params.append(tuple(subnets)) cur = yield db.execute(sql, params) tenants = cur.fetchall() except Exception, e: LOG.error("=====ERROR= query_subnet_tenants query error: %s" % e) raise e
def __update_vm_pros(res, rs): LOG.debug("__update_vm_pros") db = dbpools.get_pool(dbpools.COMMON_DB) try: tx = yield db.begin() if not str(res['host']): yield pro.set_pro(tx, res['id'], 'host', str(rs['host']['name'])) if not str(res['sys_volume']): yield pro.set_pro(tx, res['id'], 'sys_volume', str(rs['metadata']['sys_volume']['type'])) network = list() for n in res['network']: nw = dict() nw['vlan'] = { "id": str(n['vlan']['id']), "name": str(n['vlan']['name']) } if n["ip"]: nw["ip"] = str(n["ip"]) else: nw["ip"] = str(rs['network'][str(n['vlan']['name'])][0]) network.append(nw) yield pro.set_pro(tx, res['id'], 'network', json.dumps(network)) yield tx.commit() LOG.debug("__update_vm_pros: success") except Exception, e: LOG.debug("__update_vm_pros: failed") yield tx.rollback() raise e
def get_time(target, msg): db = dbpools.get_pool(dbpools.LOCAL_DB) cur = yield db.execute( "select id, create_at, update_at from alarm where target = %s and message = %s", (target, msg)) rs = cur.fetchone() raise gen.Return(rs)
def servers_metadata(vm_ids): """ :param vm_ids: list :return: """ if isinstance(vm_ids, basestring): vm_ids = [vm_ids] if not vm_ids: raise gen.Return([]) params = [] try: db = dbpools.get_pool(dbpools.NOVA_DB) sql = "SELECT im.instance_uuid as server_id, im.key as meta_key, im.value as meta_value " \ "FROM instance_metadata im WHERE deleted=0 and im.instance_uuid " if isinstance(vm_ids, basestring): sql += " =%s " params.append(vm_ids) else: sql += " in %s " params.append(tuple(vm_ids)) cur = yield db.execute(sql, params) metadatas = cur.fetchall() except Exception, e: LOG.error("instance metadata error: %s" % e) raise e
def get_network_db(network_ids=None, network_name=None): """ Get networks info from neutron-network :param network_ids: network id list :param network_name: network name :return: [{ "network_id":"xxx", "network_name":"xxx", "vlan_id":"xxx", },...] """ try: if network_ids and isinstance(network_ids, basestring): network_ids = [network_ids] params = [] db = dbpools.get_pool(dbpools.NEUTRON_DB) sql = "SELECT n.id as network_id, n.name as network_name, n.`status`, " \ "ml.segmentation_id as vlan_id,ml.network_type as vlan_type, ml.physical_network as physical_network " \ "FROM networks n left join ml2_network_segments ml on n.id = ml.network_id " sql += " where 1=1 " if network_ids: sql += " and n.id in %s" params.append(tuple(network_ids)) if network_name: sql += " and n.name = %s" params.append(network_name) cur = yield db.execute(sql, params) networks = cur.fetchall() except Exception, e: LOG.error("Get network info error: %s" % e) raise e
def add_network_hosts(network_id, network_name, hosts): """ add hosts to vlan :param network_id:id of network :param network_name: Network={"id":"uuid","name":"vlan140","hosts":[host1,host2]} :param hosts: the hosts assign to network """ vlan_hosts = { "vlan_id": network_id, "vlan_name": network_name, "hosts": hosts } try: if vlan_hosts and isinstance(vlan_hosts, dict): hosts = vlan_hosts["hosts"] new_hosts = [(str(uuid4()), vlan_hosts["vlan_id"], vlan_hosts["vlan_name"], host) for host in hosts if host] for new_host in new_hosts: yield dbpools.execute_commit( dbpools.get_pool(dbpools.LOCAL_DB), sql= "insert into vlan_hosts (id, vlan_id, vlan_name, host_id) VALUES (%s,%s,%s,%s)", param=new_host) except Exception, e: LOG.error("insert vlan hosts error: %s" % e) raise e
def delete_instance_info(vm_id): try: db = dbpools.get_pool(dbpools.NOVA_DB) sql = "update instances set deleted=1, updated_at=utc_timestamp(), deleted_at=utc_timestamp(), vm_state='deleted' where uuid=%s" yield dbpools.update(db, sql, [vm_id]) except Exception, e: LOG.error("delete server error: %s" % e) raise ServerOperationFailed()
def list_simple_network(): try: db = dbpools.get_pool(dbpools.NEUTRON_DB) sql = "select a.id , a.`name`, a.`status` ,b.physical_network ,b.segmentation_id as vlan_id from networks as a LEFT JOIN ml2_network_segments as b on a.id = b.network_id" cur = yield db.execute(sql) rst = cur.fetchall() except Exception, e: LOG.error("list simple network from db error: %s" % e) raise e
def volume_type_list(): try: db = dbpools.get_pool(dbpools.CINDER_DB) sql = "SELECT vt.id as id, vt.name as name FROM volume_types vt WHERE vt.deleted=0" cur = yield db.execute(sql) volume_types = cur.fetchall() except Exception, e: LOG.error("Volume type list error: %s" % e) raise e
def server_state_count(): try: db = dbpools.get_pool(dbpools.NOVA_DB) sql = "SELECT count(id) count, vm_state state " \ "FROM instances WHERE vm_state='active' or vm_state='stopped' group by vm_state" cur = yield db.execute(sql) rst = cur.fetchall() except Exception, e: LOG.error("Server state count error: %s" % e) raise e
def get_count_alarm(target): try: db = dbpools.get_pool(dbpools.LOCAL_DB) sql = "select count(*) as count from alarm where 1 = 1 " if target: sql += " and target='%s'" % target cur = yield db.execute(sql) vm_alarm_count = cur.fetchone() except Exception, e: LOG.error("get alarm failed: %s" % e)
def __activate_user(token_id, user_id): db = dbpools.get_pool(dbpools.COMMON_DB) cur = yield db.execute("select token from token where user_id = %s ", (user_id,)) old_token = cur.fetchone() if old_token: yield __delete_token(old_token['token']) yield dbpools.execute_commit( db, "insert into token (token, user_id) values (%s, %s)", (token_id, user_id) )
def volume_real_used(tenant_id): try: db = dbpools.get_pool(dbpools.CINDER_DB) sql = "SELECT COUNT(id) as count, SUM(size) as used_size " \ "FROM volumes " \ "WHERE display_description = 0 and project_id=%s and status != 'deleted'" cur = yield db.execute(sql, [tenant_id]) volume_used = cur.fetchone() except Exception, e: LOG.error("volume real used error: %s" % e) raise e
def update_tenant_hosts(tenant_id, tenant_hosts): db = dbpools.get_pool(dbpools.LOCAL_DB) tenant_hosts = json.dumps(tenant_hosts) try: sql = "UPDATE tenant_hosts SET hosts=%s " \ "WHERE tenant_id=%s" yield db.execute(sql, (tenant_hosts, tenant_id)) except Exception, e: LOG.error("update the tenant_hosts error :%s" % e) raise e
def summary(): """首页显示故障 告警 注意""" rs = dict() level = ['notice', 'warning', 'fatal'] db = dbpools.get_pool(dbpools.LOCAL_DB) for l in level: sql = "select count(*) from alarm where level = %s" cur = yield db.execute(sql, (l, )) trs = cur.fetchone() rs[l] = trs['count(*)'] raise gen.Return(rs)
def query_instance_nova(device_ids): try: subnet_vm = [] if device_ids: db = dbpools.get_pool(dbpools.NOVA_DB) sql = "SELECT uuid,display_name as vm " \ "from instances WHERE uuid in %s " cur = yield db.execute(sql, (device_ids,)) subnet_vm = cur.fetchall() except Exception, e: LOG.error("Query used vm of subnet from nava error: %s" % e) raise e
def __update_vm_id(res_id, vm_id): LOG.debug("__update_vm_id: begin") db = dbpools.get_pool(dbpools.COMMON_DB) try: tx = yield db.begin() yield pro.set_pro(tx, res_id, 'vm_id', vm_id) yield tx.commit() LOG.debug("__update_vm_id: success") except Exception, e: LOG.debug("__update_vm_id: failed") yield tx.rollback() raise e
def get_tenant_hosts(tenant_id): db = dbpools.get_pool(dbpools.LOCAL_DB) try: cur = yield db.execute( "select hosts from tenant_hosts where tenant_id=%s", [tenant_id]) hosts = cur.fetchone() hosts = hosts and hosts["hosts"] and json.loads(hosts["hosts"]) if not hosts: hosts = [] except Exception, e: LOG.error("query hosts of tenant error: %s" % e) raise e
def is_active(token_id, user_id): db = dbpools.get_pool(dbpools.COMMON_DB) try: cur = yield db.execute( "select token from token where user_id = %s and token = %s ", ( user_id, token_id, )) old_token = cur.fetchone() if not old_token: raise DialogueTimeOut except Exception: raise DialogueTimeOut
def get_drive_image_id(): """ get drive image id :return: image_id """ try: db = dbpools.get_pool(dbpools.GLANCE_DB) sql = "SELECT image_id FROM image_properties WHERE NAME = 'ecloud_image_type' AND VALUE = '2'" cur = yield db.execute(sql) result = cur.fetchall() except Exception, e: LOG.error("Get drive image id error: %s" % e) raise e
def volume_state_count(): """ :return: """ try: db = dbpools.get_pool(dbpools.CINDER_DB) sql = "SELECT count(*) count, sum(size) size, display_description as v_type FROM volumes " \ "WHERE deleted=0 and display_description in (0, 2) GROUP BY display_description" cur = yield db.execute(sql) metadatas = cur.fetchall() except Exception, e: LOG.error("Volume state count error: %s" % e) raise e
def update(id_, utime): LOG.debug("alarmAPI.update") db = dbpools.get_pool(dbpools.LOCAL_DB) sql = "update alarm set times = times + 1, update_at = %s where id = %s" try: tx = yield db.begin() yield tx.execute(sql, (utime, id_)) yield tx.commit() LOG.debug("alarmAPI.update success") except Exception, e: yield tx.rollback() LOG.error("update alarm failed: %s" % e) raise e
def get_security_groups_from_db(tenant_id=None): try: db = dbpools.get_pool(dbpools.NEUTRON_DB) sql = "select tenant_id, id, name ,description from securitygroups where 1=1" if tenant_id: sql += " and tenant_id='%s' " % tenant_id from_db = yield db.execute(sql) all_data = from_db.fetchall() except Exception as e: LOG.error("get get_security_groups_from_db error %s" % e) raise SecGroupRuleOperationFailed() raise gen.Return(all_data)
def insert(target, typ, level, msg, create_time): LOG.debug("alarmAPI.insert") db = dbpools.get_pool(dbpools.LOCAL_DB) sql = "insert into alarm (target, type, times, message, level, create_at) values (%s, %s, 1, %s, %s, %s)" vals = (target, typ, msg, level, create_time) tx = yield db.begin() try: yield tx.execute(sql, vals) yield tx.commit() LOG.debug("alarmAPI.insert: success") except Exception, e: yield tx.rollback() LOG.error("insert alarm failed: %s" % e) raise e