gt:         SearchCriteria<VolumeVO> sc = TemplateZoneSearch.create();
context: // Licensed to the Apache Software Foundation (ASF) under one
  // or more contributor license agreements.  See the NOTICE file
  // distributed with this work for additional information
  // regarding copyright ownership.  The ASF licenses this file
  // to you under the Apache License, Version 2.0 (the
  // "License"); you may not use this file except in compliance
  // with the License.  You may obtain a copy of the License at
  //
  //   http://www.apache.org/licenses/LICENSE-2.0
  //
  // Unless required by applicable law or agreed to in writing,
  // software distributed under the License is distributed on an
  // "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  // KIND, either express or implied.  See the License for the
  // specific language governing permissions and limitations
  // under the License.
  package com.cloud.storage.dao;
  
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;
  
  import javax.inject.Inject;
  
  import org.apache.log4j.Logger;
  import org.springframework.stereotype.Component;
  
  import com.cloud.exception.InvalidParameterValueException;
  import com.cloud.hypervisor.Hypervisor.HypervisorType;
  import com.cloud.server.ResourceTag.ResourceObjectType;
  import com.cloud.storage.ScopeType;
  import com.cloud.storage.Storage.ImageFormat;
  import com.cloud.storage.Volume;
  import com.cloud.storage.Volume.Event;
  import com.cloud.storage.Volume.State;
  import com.cloud.storage.Volume.Type;
  import com.cloud.storage.VolumeVO;
  import com.cloud.tags.dao.ResourceTagDao;
  import com.cloud.utils.Pair;
  import com.cloud.utils.db.DB;
  import com.cloud.utils.db.GenericDaoBase;
  import com.cloud.utils.db.GenericSearchBuilder;
  import com.cloud.utils.db.SearchBuilder;
  import com.cloud.utils.db.SearchCriteria;
  import com.cloud.utils.db.SearchCriteria.Func;
  import com.cloud.utils.db.SearchCriteria.Op;
  import com.cloud.utils.db.TransactionLegacy;
  import com.cloud.utils.db.UpdateBuilder;
  import com.cloud.utils.exception.CloudRuntimeException;
  
  @Component
  public class VolumeDaoImpl extends GenericDaoBase<VolumeVO, Long> implements VolumeDao {
      private static final Logger s_logger = Logger.getLogger(VolumeDaoImpl.class);
      protected final SearchBuilder<VolumeVO> DetachedAccountIdSearch;
      protected final SearchBuilder<VolumeVO> TemplateZoneSearch;
      protected final GenericSearchBuilder<VolumeVO, SumCount> TotalSizeByPoolSearch;
      protected final GenericSearchBuilder<VolumeVO, SumCount> TotalVMSnapshotSizeByPoolSearch;
      protected final GenericSearchBuilder<VolumeVO, Long> ActiveTemplateSearch;
      protected final SearchBuilder<VolumeVO> InstanceStatesSearch;
      protected final SearchBuilder<VolumeVO> AllFieldsSearch;
      protected GenericSearchBuilder<VolumeVO, Long> CountByAccount;
      protected GenericSearchBuilder<VolumeVO, SumCount> primaryStorageSearch;
      protected GenericSearchBuilder<VolumeVO, SumCount> primaryStorageSearch2;
      protected GenericSearchBuilder<VolumeVO, SumCount> secondaryStorageSearch;
      @Inject
      ResourceTagDao _tagsDao;
  
      protected static final String SELECT_VM_SQL = "SELECT DISTINCT instance_id from volumes v where v.host_id = ? and v.mirror_state = ?";
      // need to account for zone-wide primary storage where storage_pool has
      // null-value pod and cluster, where hypervisor information is stored in
      // storage_pool
      protected static final String SELECT_HYPERTYPE_FROM_CLUSTER_VOLUME =
          "SELECT c.hypervisor_type from volumes v, storage_pool s, cluster c where v.pool_id = s.id and s.cluster_id = c.id and v.id = ?";
      protected static final String SELECT_HYPERTYPE_FROM_ZONE_VOLUME = "SELECT s.hypervisor from volumes v, storage_pool s where v.pool_id = s.id and v.id = ?";
      protected static final String SELECT_POOLSCOPE = "SELECT s.scope from storage_pool s, volumes v where s.id = v.pool_id and v.id = ?";
  
      private static final String ORDER_POOLS_NUMBER_OF_VOLUMES_FOR_ACCOUNT =
          "SELECT pool.id, SUM(IF(vol.state='Ready' AND vol.account_id = ?, 1, 0)) FROM `cloud`.`storage_pool` pool LEFT JOIN `cloud`.`volumes` vol ON pool.id = vol.pool_id WHERE pool.data_center_id = ? "
              + " AND pool.pod_id = ? AND pool.cluster_id = ? " + " GROUP BY pool.id ORDER BY 2 ASC ";
      private static final String ORDER_ZONE_WIDE_POOLS_NUMBER_OF_VOLUMES_FOR_ACCOUNT =
          "SELECT pool.id, SUM(IF(vol.state='Ready' AND vol.account_id = ?, 1, 0)) FROM `cloud`.`storage_pool` pool LEFT JOIN `cloud`.`volumes` vol ON pool.id = vol.pool_id WHERE pool.data_center_id = ? "
              + " AND pool.scope = 'ZONE' AND pool.status='Up' " + " GROUP BY pool.id ORDER BY 2 ASC ";
  
      @Override
      public List<VolumeVO> findDetachedByAccount(long accountId) {
          SearchCriteria<VolumeVO> sc = DetachedAccountIdSearch.create();
          sc.setParameters("accountId", accountId);
          sc.setParameters("destroyed", Volume.State.Destroy);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByAccount(long accountId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("accountId", accountId);
          sc.setParameters("state", Volume.State.Ready);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByInstance(long id) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("instanceId", id);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByInstanceAndDeviceId(long instanceId, long deviceId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("instanceId", instanceId);
          sc.setParameters("deviceId", deviceId);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByPoolId(long poolId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("poolId", poolId);
          sc.setParameters("notDestroyed", Volume.State.Destroy);
          sc.setParameters("vType", Volume.Type.ROOT.toString());
          return listBy(sc);
      }
  
      @Override
      public VolumeVO findByPoolIdName(long poolId, String name) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("poolId", poolId);
          sc.setParameters("name", name);
          return findOneBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByPoolId(long poolId, Volume.Type volumeType) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("poolId", poolId);
          sc.setParameters("notDestroyed", Volume.State.Destroy);
  
          if (volumeType != null) {
              sc.setParameters("vType", volumeType.toString());
          }
  
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findCreatedByInstance(long id) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("instanceId", id);
          sc.setParameters("state", Volume.State.Ready);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findUsableVolumesForInstance(long instanceId) {
          SearchCriteria<VolumeVO> sc = InstanceStatesSearch.create();
          sc.setParameters("instance", instanceId);
          sc.setParameters("states", Volume.State.Creating, Volume.State.Ready, Volume.State.Allocated);
  
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByInstanceAndType(long id, Type vType) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("instanceId", id);
          sc.setParameters("vType", vType.toString());
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByInstanceIdDestroyed(long vmId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("instanceId", vmId);
          sc.setParameters("destroyed", Volume.State.Destroy);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findReadyRootVolumesByInstance(long instanceId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("instanceId", instanceId);
          sc.setParameters("state", Volume.State.Ready);
          sc.setParameters("vType", Volume.Type.ROOT);
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByPod(long podId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("pod", podId);
  
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByDc(long dcId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("dcId", dcId);
  
          return listBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByAccountAndPod(long accountId, long podId) {
          SearchCriteria<VolumeVO> sc = AllFieldsSearch.create();
          sc.setParameters("accountId", accountId);
          sc.setParameters("pod", podId);
          sc.setParameters("state", Volume.State.Ready);
  
          return listIncludingRemovedBy(sc);
      }
  
      @Override
      public List<VolumeVO> findByTemplateAndZone(long templateId, long zoneId) {
  