def _db_select_query(name=None, nativeid=None, server=None, provider=None):
    """Column order: `id`, `nativeid`, `name`, `properties`, `server_name`,
    `feed`, `provider_name`, `ems_ref`, `hostname`"""
    t_ms = cfmedb()['middleware_servers']
    t_mds = cfmedb()['middleware_datasources']
    t_ems = cfmedb()['ext_management_systems']
    query = cfmedb().session.query(
        t_mds.id,
        t_mds.nativeid,
        t_mds.name,
        t_mds.properties,
        t_ms.name.label('server_name'),
        t_ms.feed,
        t_ems.name.label('provider_name'),
        t_ms.hostname,
        t_mds.ems_ref)\
        .join(t_ms, t_mds.server_id == t_ms.id).join(t_ems, t_mds.ems_id == t_ems.id)
    if name:
        query = query.filter(t_mds.name == name)
    if nativeid:
        query = query.filter(t_mds.nativeid == nativeid)
    if server:
        query = query.filter(t_ms.name == server.name)
        if server.feed:
            query = query.filter(t_ms.feed == server.feed)
    if provider:
        query = query.filter(t_ems.name == provider.name)
    return query

 def num_host_db(self):
     ext_management_systems = cfmedb()["ext_management_systems"]
     hosts = cfmedb()["hosts"]
     hostlist = list(cfmedb().session.query(hosts.name)
                     .join(ext_management_systems, hosts.ems_id == ext_management_systems.id)
                     .filter(ext_management_systems.name == self.name))
     return len(hostlist)

def _db_select_query(name=None, nativeid=None, server=None, provider=None):
    """Column order: `id`, `nativeid`, `name`, `properties`, `server_name`,
    `feed`, `provider_name`, `ems_ref`, `messaging_type`"""
    t_ms = cfmedb()["middleware_servers"]
    t_mm = cfmedb()["middleware_messagings"]
    t_ems = cfmedb()["ext_management_systems"]
    query = (
        cfmedb()
        .session.query(
            t_mm.id,
            t_mm.nativeid,
            t_mm.name,
            t_mm.properties,
            t_ms.name.label("server_name"),
            t_ms.feed,
            t_ems.name.label("provider_name"),
            t_mm.messaging_type,
            t_mm.ems_ref,
        )
        .join(t_ms, t_mm.server_id == t_ms.id)
        .join(t_ems, t_mm.ems_id == t_ems.id)
    )
    if name:
        query = query.filter(t_mm.name == name)
    if nativeid:
        query = query.filter(t_mm.nativeid == nativeid)
    if server:
        query = query.filter(t_ms.name == server.name)
        if server.feed:
            query = query.filter(t_ms.feed == server.feed)
    if provider:
        query = query.filter(t_ems.name == provider.name)
    return query

 def get_tags_db(self):
     """
     Gets tags detail from database
     Column order: `tag_id`, `db_id`, `category`, `tag_name`, `single_value`
     """
     # Some times object of db_id might changed in database, when we do CRUD operations,
     # do update now
     self.load_details(refresh=True)
     if not self.db_id or not self.taggable_type:
         raise KeyError("'db_id' and/or 'taggable_type' not set")
     t_cls1 = aliased(cfmedb()['classifications'])
     t_cls2 = aliased(cfmedb()['classifications'])
     t_tgg = aliased(cfmedb()['taggings'])
     query = cfmedb().session.query(t_cls1.tag_id, t_tgg.taggable_id.label('db_id'),
                                    t_cls2.description.label('category'),
                                    t_cls1.description.label('tag_name'), t_cls1.single_value)\
         .join(t_cls2, t_cls1.parent_id == t_cls2.id)\
         .join(t_tgg, t_tgg.tag_id == t_cls1.tag_id)\
         .filter(t_tgg.taggable_id == self.db_id)\
         .filter(t_tgg.taggable_type == self.taggable_type)
     tags = []
     for tag in query.all():
         tags.append(Tag(category=Category(display_name=tag.category,
                                           single_value=tag.single_value),
                         display_name=tag.tag_name))
     return tags

def test_metrics_collection(handle_provider, provider_key, provider_crud, enable_candu):
    """check the db is gathering collection data for the given provider

    Metadata:
        test_flag: metrics_collection
    """
    metrics_tbl = db.cfmedb()['metrics']
    mgmt_systems_tbl = db.cfmedb()['ext_management_systems']

    logger.info("Fetching provider ID for {}".format(provider_key))
    mgmt_system_id = db.cfmedb().session.query(mgmt_systems_tbl).filter(
        mgmt_systems_tbl.name == conf.cfme_data.get('management_systems', {})[provider_key]['name']
    ).first().id

    logger.info("ID fetched; testing metrics collection now")
    start_time = time.time()
    metric_count = 0
    timeout = 900.0  # 15 min
    while time.time() < start_time + timeout:
        last_metric_count = metric_count
        logger.info("name: {}, id: {}, metrics: {}".format(
            provider_key, mgmt_system_id, metric_count))
        # count all the metrics for the provider we're testing
        metric_count = db.cfmedb().session.query(metrics_tbl).filter(
            metrics_tbl.parent_ems_id == mgmt_system_id
        ).count()

        # collection is working if increasing
        if metric_count > last_metric_count and last_metric_count > 0:
            return
        else:
            time.sleep(15)

    if time.time() > start_time + timeout:
        raise Exception("Timed out waiting for metrics to be collected")

def _db_select_query(name=None, feed=None, provider=None, server_group=None, product=None):
    """column order: `id`, `name`, `hostname`, `feed`, `product`,
    `provider_name`, `ems_ref`, `properties`, `server_group_name`"""
    t_ms = cfmedb()["middleware_servers"]
    t_msgr = cfmedb()["middleware_server_groups"]
    t_ems = cfmedb()["ext_management_systems"]
    query = (
        cfmedb()
        .session.query(
            t_ms.id,
            t_ms.name,
            t_ms.hostname,
            t_ms.feed,
            t_ms.product,
            t_ems.name.label("provider_name"),
            t_ms.ems_ref,
            t_ms.properties,
            t_msgr.name.label("server_group_name"),
        )
        .join(t_ems, t_ms.ems_id == t_ems.id)
        .outerjoin(t_msgr, t_ms.server_group_id == t_msgr.id)
    )
    if name:
        query = query.filter(t_ms.name == name)
    if feed:
        query = query.filter(t_ms.feed == feed)
    if provider:
        query = query.filter(t_ems.name == provider.name)
    if server_group:
        query = query.filter(t_msgr.name == server_group.name)
    if product:
        query = query.filter(t_ms.product == product)
    return query

def get_vm_object(vm_name):
    """Looks up the CFME database for the VM.

    Args:
        vm_name: VM name
    Returns:
        If found, :py:class:`utils.miq_soap.MiqVM` for 5.4 and :py:class:`utils.api.Entity` for 5.5
        If not, `None`
    """
    if current_version() < "5.5":
        vm_table = cfmedb()['vms']
        for vm in cfmedb().session.query(vm_table.name, vm_table.guid)\
                .filter(vm_table.template == False):  # NOQA
            # Previous line is ok, if you change it to `is`, it won't work!
            if vm.name == vm_name:
                return MiqVM(vm.guid)
        else:
            return None
    else:
        rest_api = pytest.store.current_appliance.rest_api
        results = rest_api.collections.vms.find_by(name=vm_name)
        if len(results) > 0:
            return results[0]
        else:
            return None

def _db_select_query(name=None, server=None, provider=None):
    """Column order: `id`, `nativeid`, `name`, `server_name`,
    `feed`, `provider_name`, `host_name`, `status`"""
    t_ems = cfmedb()["ext_management_systems"]
    t_ms = cfmedb()["middleware_servers"]
    t_md = cfmedb()["middleware_deployments"]
    query = (
        cfmedb()
        .session.query(
            t_md.id,
            t_md.nativeid.label("nativeid"),
            t_md.name,
            t_ms.name.label("server_name"),
            t_ms.feed.label("feed"),
            t_ems.name.label("provider_name"),
            t_ms.hostname.label("host_name"),
            t_md.status.label("status"),
        )
        .join(t_ms, t_md.server_id == t_ms.id)
        .join(t_ems, t_md.ems_id == t_ems.id)
    )
    if name:
        query = query.filter(t_md.name == name)
    if server:
        query = query.filter(t_ms.name == server.name)
        if server.feed:
            query = query.filter(t_ms.feed == server.feed)
    if provider:
        query = query.filter(t_ems.name == provider.name)
    return query

def _db_select_query(name=None, server=None, provider=None):
    """Column order: `id`, `nativeid`, `name`, `server_name`,
    `feed`, `provider_name`, `host_name`, `status`"""
    t_ems = cfmedb()['ext_management_systems']
    t_ms = cfmedb()['middleware_servers']
    t_md = cfmedb()['middleware_deployments']
    query = cfmedb().session.query(
        t_md.id,
        t_md.nativeid.label('nativeid'),
        t_md.name,
        t_ms.name.label('server_name'),
        t_ms.feed.label('feed'),
        t_ems.name.label('provider_name'),
        t_ms.hostname.label('host_name'),
        t_md.status.label('status')) \
        .join(t_ms, t_md.server_id == t_ms.id).join(t_ems, t_md.ems_id == t_ems.id)
    if name:
        query = query.filter(t_md.name == name)
    if server:
        query = query.filter(t_ms.name == server.name)
        if server.feed:
            query = query.filter(t_ms.feed == server.feed)
    if provider:
        query = query.filter(t_ems.name == provider.name)
    return query

 def exists(self):
     ems = cfmedb()['ext_management_systems']
     provs = (prov[0] for prov in cfmedb().session.query(ems.name))
     if self.name in provs:
         return True
     else:
         return False

 def num_vm(self):
     """ Returns the providers number of instances, as shown on the Details page."""
     ext_management_systems = cfmedb()["ext_management_systems"]
     vms = cfmedb()["vms"]
     vmlist = list(cfmedb().session.query(vms.name)
                   .join(ext_management_systems, vms.ems_id == ext_management_systems.id)
                   .filter(ext_management_systems.name == self.name)
                   .filter(vms.template == False))  # NOQA
     return len(vmlist)

def _db_select_query(name=None, type=None):
    """column order: `id`, `name`, `type`"""
    t_ems = cfmedb()['ext_management_systems']
    query = cfmedb().session.query(t_ems.id, t_ems.name, t_ems.type)
    if name:
        query = query.filter(t_ems.name == name)
    if type:
        query = query.filter(t_ems.type == type)
    return query

 def num_cluster_db(self):
     """ Returns the providers number of templates, as shown on the Details page."""
     ext_management_systems = cfmedb()["ext_management_systems"]
     clusters = cfmedb()["ems_clusters"]
     clulist = list(cfmedb().session.query(clusters.name)
                    .join(ext_management_systems,
                          clusters.ems_id == ext_management_systems.id)
                    .filter(ext_management_systems.name == self.name))
     return len(clulist)

 def num_host(self, db=True):
     """ Returns the providers number of instances, as shown on the Details page."""
     if db:
         ext_management_systems = cfmedb()["ext_management_systems"]
         hosts = cfmedb()["hosts"]
         hostlist = list(cfmedb().session.query(hosts.name)
                         .join(ext_management_systems, hosts.ems_id == ext_management_systems.id)
                         .filter(ext_management_systems.name == self.name))
         return len(hostlist)
     else:
         return int(self.get_detail("Relationships", "host.png", use_icon=True))

 def num_cluster(self, db=True):
     """ Returns the providers number of templates, as shown on the Details page."""
     if db:
         ext_management_systems = cfmedb()["ext_management_systems"]
         clusters = cfmedb()["ems_clusters"]
         clulist = list(cfmedb().session.query(clusters.name)
                        .join(ext_management_systems,
                              clusters.ems_id == ext_management_systems.id)
                        .filter(ext_management_systems.name == self.name))
         return len(clulist)
     else:
         return int(self.get_detail("Relationships", "cluster.png", use_icon=True))

 def num_vm(self, db=True):
     """ Returns the providers number of instances, as shown on the Details page."""
     if db:
         ext_management_systems = cfmedb()["ext_management_systems"]
         vms = cfmedb()["vms"]
         falsey = False  # This is to prevent a lint error with ==False
         vmlist = list(cfmedb().session.query(vms.name)
                       .join(ext_management_systems, vms.ems_id == ext_management_systems.id)
                       .filter(ext_management_systems.name == self.name)
                       .filter(vms.template == falsey))
         return len(vmlist)
     return int(self.get_detail("Relationships", "Instances"))

def _db_select_query(name=None, server=None, provider=None):
    t_ms = cfmedb()['middleware_servers']
    t_ems = cfmedb()['ext_management_systems']
    query = cfmedb().session.query(t_ms.name, t_ms.feed,
                                   t_ms.product, t_ems.name).join(t_ems, t_ms.ems_id == t_ems.id)
    if name:
        query = query.filter(t_ms.name == name)
    if server:
        query = query.filter(t_ms.nativeid.like('%{}%'.format(server)))
    if provider:
        query = query.filter(t_ems.name == provider)
    return query

def test_metrics_collection(handle_provider, provider, enable_candu):
    """check the db is gathering collection data for the given provider

    Metadata:
        test_flag: metrics_collection
    """
    metrics_tbl = db.cfmedb()['metrics']
    mgmt_systems_tbl = db.cfmedb()['ext_management_systems']

    logger.info("Fetching provider ID for %s", provider.key)
    mgmt_system_id = db.cfmedb().session.query(mgmt_systems_tbl).filter(
        mgmt_systems_tbl.name == conf.cfme_data.get('management_systems', {})[provider.key]['name']
    ).first().id

    logger.info("ID fetched; testing metrics collection now")
    start_time = time.time()
    host_count = 0
    vm_count = 0
    host_rising = False
    vm_rising = False
    timeout = 900.0  # 15 min
    while time.time() < start_time + timeout:
        last_host_count = host_count
        last_vm_count = vm_count
        logger.info("name: %s, id: %s, vms: %s, hosts: %s",
            provider.key, mgmt_system_id, vm_count, host_count)
        # count host and vm metrics for the provider we're testing
        host_count = db.cfmedb().session.query(metrics_tbl).filter(
            metrics_tbl.parent_ems_id == mgmt_system_id).filter(
            metrics_tbl.resource_type == "Host"
        ).count()
        vm_count = db.cfmedb().session.query(metrics_tbl).filter(
            metrics_tbl.parent_ems_id == mgmt_system_id).filter(
            metrics_tbl.resource_type == "VmOrTemplate"
        ).count()

        if (host_count > last_host_count) and (last_host_count > 0):
            host_rising = True
        if (vm_count > last_vm_count) and (last_vm_count > 0):
            vm_rising = True

        # only vms are collected for cloud
        if provider.category == "cloud" and vm_rising:
            return
        # both vms and hosts must be collected for infra
        elif provider.category == "infra" and vm_rising and host_rising:
            return
        else:
            time.sleep(15)

    if time.time() > start_time + timeout:
        raise Exception("Timed out waiting for metrics to be collected")

 def num_template(self, db=True):
     """ Returns the providers number of templates, as shown on the Details page."""
     if db:
         ext_management_systems = cfmedb()["ext_management_systems"]
         vms = cfmedb()["vms"]
         truthy = True  # This is to prevent a lint error with ==True
         temlist = list(cfmedb().session.query(vms.name)
                        .join(ext_management_systems, vms.ems_id == ext_management_systems.id)
                        .filter(ext_management_systems.name == self.name)
                        .filter(vms.template == truthy))
         return len(temlist)
     else:
         return int(self.get_detail("Relationships", "Images"))

 def get_pxe_image_type(self, image_name):
     pxe_i = cfmedb()["pxe_images"]
     pxe_s = cfmedb()["pxe_servers"]
     pxe_t = cfmedb()["pxe_image_types"]
     hosts = list(cfmedb().session.query(pxe_t.name)
                  .join(pxe_i, pxe_i.pxe_image_type_id == pxe_t.id)
                  .join(pxe_s, pxe_i.pxe_server_id == pxe_s.id)
                  .filter(pxe_s.name == self.name)
                  .filter(pxe_i.name == image_name))
     if hosts:
         return hosts[0][0]
     else:
         return None