Create DB view for ListStoragePools API performance optimization. Signed-off-by: Min Chen <[email protected]>
Project: http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/commit/74c15a22 Tree: http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/tree/74c15a22 Diff: http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/diff/74c15a22 Branch: refs/heads/javelin Commit: 74c15a22a05023d639dc24329f1ea4c4a6c8932f Parents: 0a25884 Author: Min Chen <[email protected]> Authored: Tue Jan 8 12:33:18 2013 -0800 Committer: Min Chen <[email protected]> Committed: Tue Jan 8 12:33:18 2013 -0800 ---------------------------------------------------------------------- api/src/com/cloud/server/ManagementService.java | 8 - .../command/admin/storage/ListStoragePoolsCmd.java | 12 +- .../org/apache/cloudstack/query/QueryService.java | 4 + server/src/com/cloud/api/ApiDBUtils.java | 19 + server/src/com/cloud/api/ApiResponseHelper.java | 46 +-- server/src/com/cloud/api/ApiServer.java | 2 + .../src/com/cloud/api/query/QueryManagerImpl.java | 100 +++++ .../com/cloud/api/query/ViewResponseHelper.java | 21 + .../cloud/api/query/dao/StoragePoolJoinDao.java | 37 ++ .../api/query/dao/StoragePoolJoinDaoImpl.java | 185 ++++++++ server/src/com/cloud/api/query/vo/BaseViewVO.java | 2 - .../com/cloud/api/query/vo/StoragePoolJoinVO.java | 341 +++++++++++++++ .../configuration/DefaultComponentLibrary.java | 2 + .../src/com/cloud/server/ManagementServerImpl.java | 17 - server/test/com/cloud/api/ListPerfTest.java | 17 + setup/db/create-schema-view.sql | 37 ++ setup/db/db/schema-40to410.sql | 40 ++ 17 files changed, 812 insertions(+), 78 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/api/src/com/cloud/server/ManagementService.java ---------------------------------------------------------------------- diff --git a/api/src/com/cloud/server/ManagementService.java b/api/src/com/cloud/server/ManagementService.java index e547954..fb8af1a 100755 --- a/api/src/com/cloud/server/ManagementService.java +++ b/api/src/com/cloud/server/ManagementService.java @@ -243,14 +243,6 @@ public interface ManagementService { */ List<? extends DiskOffering> searchForDiskOfferings(ListDiskOfferingsCmd cmd); - /** - * List storage pools that match the given criteria - * - * @param cmd - * the command that wraps the search criteria (zone, pod, name, IP address, path, and cluster id) - * @return a list of storage pools that match the given criteria - */ - Pair<List<? extends StoragePool>, Integer> searchForStoragePools(ListStoragePoolsCmd cmd); /** * List system VMs by the given search criteria http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/api/src/org/apache/cloudstack/api/command/admin/storage/ListStoragePoolsCmd.java ---------------------------------------------------------------------- diff --git a/api/src/org/apache/cloudstack/api/command/admin/storage/ListStoragePoolsCmd.java b/api/src/org/apache/cloudstack/api/command/admin/storage/ListStoragePoolsCmd.java index 3723f8b..9c5c584 100644 --- a/api/src/org/apache/cloudstack/api/command/admin/storage/ListStoragePoolsCmd.java +++ b/api/src/org/apache/cloudstack/api/command/admin/storage/ListStoragePoolsCmd.java @@ -29,6 +29,7 @@ import org.apache.cloudstack.api.response.ClusterResponse; import org.apache.cloudstack.api.response.ListResponse; import org.apache.cloudstack.api.response.PodResponse; import org.apache.cloudstack.api.response.StoragePoolResponse; +import org.apache.cloudstack.api.response.VolumeResponse; import org.apache.cloudstack.api.response.ZoneResponse; import com.cloud.async.AsyncJob; import com.cloud.storage.StoragePool; @@ -116,16 +117,7 @@ public class ListStoragePoolsCmd extends BaseListCmd { @Override public void execute(){ - Pair<List<? extends StoragePool>, Integer> pools = _mgr.searchForStoragePools(this); - ListResponse<StoragePoolResponse> response = new ListResponse<StoragePoolResponse>(); - List<StoragePoolResponse> poolResponses = new ArrayList<StoragePoolResponse>(); - for (StoragePool pool : pools.first()) { - StoragePoolResponse poolResponse = _responseGenerator.createStoragePoolResponse(pool); - poolResponse.setObjectName("storagepool"); - poolResponses.add(poolResponse); - } - - response.setResponses(poolResponses, pools.second()); + ListResponse<StoragePoolResponse> response = _queryService.searchForStoragePools(this); response.setResponseName(getCommandName()); this.setResponseObject(response); } http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/api/src/org/apache/cloudstack/query/QueryService.java ---------------------------------------------------------------------- diff --git a/api/src/org/apache/cloudstack/query/QueryService.java b/api/src/org/apache/cloudstack/query/QueryService.java index 480eb31..b03a7fc 100644 --- a/api/src/org/apache/cloudstack/query/QueryService.java +++ b/api/src/org/apache/cloudstack/query/QueryService.java @@ -20,6 +20,7 @@ import java.util.List; import org.apache.cloudstack.api.command.admin.host.ListHostsCmd; import org.apache.cloudstack.api.command.admin.router.ListRoutersCmd; +import org.apache.cloudstack.api.command.admin.storage.ListStoragePoolsCmd; import org.apache.cloudstack.api.command.admin.user.ListUsersCmd; import org.apache.cloudstack.api.command.user.account.ListAccountsCmd; import org.apache.cloudstack.api.command.user.account.ListProjectAccountsCmd; @@ -44,6 +45,7 @@ import org.apache.cloudstack.api.response.ProjectInvitationResponse; import org.apache.cloudstack.api.response.ProjectResponse; import org.apache.cloudstack.api.response.ResourceTagResponse; import org.apache.cloudstack.api.response.SecurityGroupResponse; +import org.apache.cloudstack.api.response.StoragePoolResponse; import org.apache.cloudstack.api.response.UserResponse; import org.apache.cloudstack.api.response.UserVmResponse; import org.apache.cloudstack.api.response.VolumeResponse; @@ -85,6 +87,8 @@ public interface QueryService { public ListResponse<VolumeResponse> searchForVolumes(ListVolumesCmd cmd); + public ListResponse<StoragePoolResponse> searchForStoragePools(ListStoragePoolsCmd cmd); + public ListResponse<AccountResponse> searchForAccounts(ListAccountsCmd cmd); public ListResponse<AsyncJobResponse> searchForAsyncJobs(ListAsyncJobsCmd cmd); http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/ApiDBUtils.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/ApiDBUtils.java b/server/src/com/cloud/api/ApiDBUtils.java index 343dad9..5e8a044 100755 --- a/server/src/com/cloud/api/ApiDBUtils.java +++ b/server/src/com/cloud/api/ApiDBUtils.java @@ -35,6 +35,7 @@ import org.apache.cloudstack.api.response.ProjectInvitationResponse; import org.apache.cloudstack.api.response.ProjectResponse; import org.apache.cloudstack.api.response.ResourceTagResponse; import org.apache.cloudstack.api.response.SecurityGroupResponse; +import org.apache.cloudstack.api.response.StoragePoolResponse; import org.apache.cloudstack.api.response.UserResponse; import org.apache.cloudstack.api.response.UserVmResponse; import org.apache.cloudstack.api.response.VolumeResponse; @@ -49,6 +50,7 @@ import com.cloud.api.query.dao.ProjectInvitationJoinDao; import com.cloud.api.query.dao.ProjectJoinDao; import com.cloud.api.query.dao.ResourceTagJoinDao; import com.cloud.api.query.dao.SecurityGroupJoinDao; +import com.cloud.api.query.dao.StoragePoolJoinDao; import com.cloud.api.query.dao.UserAccountJoinDao; import com.cloud.api.query.dao.UserVmJoinDao; import com.cloud.api.query.dao.VolumeJoinDao; @@ -63,6 +65,7 @@ import com.cloud.api.query.vo.ProjectInvitationJoinVO; import com.cloud.api.query.vo.ProjectJoinVO; import com.cloud.api.query.vo.ResourceTagJoinVO; import com.cloud.api.query.vo.SecurityGroupJoinVO; +import com.cloud.api.query.vo.StoragePoolJoinVO; import com.cloud.api.query.vo.UserAccountJoinVO; import com.cloud.api.query.vo.UserVmJoinVO; import com.cloud.api.query.vo.VolumeJoinVO; @@ -183,6 +186,7 @@ import com.cloud.storage.Snapshot; import com.cloud.storage.SnapshotVO; import com.cloud.storage.Storage.ImageFormat; import com.cloud.storage.StorageManager; +import com.cloud.storage.StoragePool; import com.cloud.storage.StoragePoolVO; import com.cloud.storage.StorageStats; import com.cloud.storage.UploadVO; @@ -324,6 +328,7 @@ public class ApiDBUtils { private static ProjectInvitationJoinDao _projectInvitationJoinDao; private static HostJoinDao _hostJoinDao; private static VolumeJoinDao _volJoinDao; + private static StoragePoolJoinDao _poolJoinDao; private static AccountJoinDao _accountJoinDao; private static AsyncJobJoinDao _jobJoinDao; @@ -414,6 +419,7 @@ public class ApiDBUtils { _projectInvitationJoinDao = locator.getDao(ProjectInvitationJoinDao.class); _hostJoinDao = locator.getDao(HostJoinDao.class); _volJoinDao = locator.getDao(VolumeJoinDao.class); + _poolJoinDao = locator.getDao(StoragePoolJoinDao.class); _accountJoinDao = locator.getDao(AccountJoinDao.class); _jobJoinDao = locator.getDao(AsyncJobJoinDao.class); @@ -1358,6 +1364,19 @@ public class ApiDBUtils { return _volJoinDao.newVolumeView(vr); } + public static StoragePoolResponse newStoragePoolResponse(StoragePoolJoinVO vr) { + return _poolJoinDao.newStoragePoolResponse(vr); + } + + public static StoragePoolResponse fillStoragePoolDetails(StoragePoolResponse vrData, StoragePoolJoinVO vr){ + return _poolJoinDao.setStoragePoolResponse(vrData, vr); + } + + public static List<StoragePoolJoinVO> newStoragePoolView(StoragePool vr){ + return _poolJoinDao.newStoragePoolView(vr); + } + + public static AccountResponse newAccountResponse(AccountJoinVO ve) { return _accountJoinDao.newAccountResponse(ve); } http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/ApiResponseHelper.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/ApiResponseHelper.java b/server/src/com/cloud/api/ApiResponseHelper.java index d5cbb71..37be83e 100755 --- a/server/src/com/cloud/api/ApiResponseHelper.java +++ b/server/src/com/cloud/api/ApiResponseHelper.java @@ -53,6 +53,7 @@ import com.cloud.api.query.vo.ProjectInvitationJoinVO; import com.cloud.api.query.vo.ProjectJoinVO; import com.cloud.api.query.vo.ResourceTagJoinVO; import com.cloud.api.query.vo.SecurityGroupJoinVO; +import com.cloud.api.query.vo.StoragePoolJoinVO; import com.cloud.api.query.vo.UserAccountJoinVO; import com.cloud.api.query.vo.UserVmJoinVO; import com.cloud.api.query.vo.VolumeJoinVO; @@ -868,49 +869,12 @@ public class ApiResponseHelper implements ResponseGenerator { @Override public StoragePoolResponse createStoragePoolResponse(StoragePool pool) { - StoragePoolResponse poolResponse = new StoragePoolResponse(); - poolResponse.setId(pool.getUuid()); - poolResponse.setName(pool.getName()); - poolResponse.setState(pool.getStatus()); - poolResponse.setPath(pool.getPath()); - poolResponse.setIpAddress(pool.getHostAddress()); - DataCenter zone = ApiDBUtils.findZoneById(pool.getDataCenterId()); - if ( zone != null ){ - poolResponse.setZoneId(zone.getUuid()); - poolResponse.setZoneName(zone.getName()); - } - if (pool.getPoolType() != null) { - poolResponse.setType(pool.getPoolType().toString()); - } - if (pool.getPodId() != null) { - HostPodVO pod = ApiDBUtils.findPodById(pool.getPodId()); - if (pod != null) { - poolResponse.setPodId(pod.getUuid()); - poolResponse.setPodName(pod.getName()); - } - } - if (pool.getCreated() != null) { - poolResponse.setCreated(pool.getCreated()); - } + List<StoragePoolJoinVO> viewPools = ApiDBUtils.newStoragePoolView(pool); + List<StoragePoolResponse> listPools = ViewResponseHelper.createStoragePoolResponse(viewPools.toArray(new StoragePoolJoinVO[viewPools.size()])); + assert listPools != null && listPools.size() == 1 : "There should be one storage pool returned"; + return listPools.get(0); - StorageStats stats = ApiDBUtils.getStoragePoolStatistics(pool.getId()); - long allocatedSize = ApiDBUtils.getStorageCapacitybyPool(pool.getId(), Capacity.CAPACITY_TYPE_STORAGE_ALLOCATED); - poolResponse.setDiskSizeTotal(pool.getCapacityBytes()); - poolResponse.setDiskSizeAllocated(allocatedSize); - if (stats != null) { - Long used = stats.getByteUsed(); - poolResponse.setDiskSizeUsed(used); - } - - if (pool.getClusterId() != null) { - ClusterVO cluster = ApiDBUtils.findClusterById(pool.getClusterId()); - poolResponse.setClusterId(cluster.getUuid()); - poolResponse.setClusterName(cluster.getName()); - } - poolResponse.setTags(ApiDBUtils.getStoragePoolTags(pool.getId())); - poolResponse.setObjectName("storagepool"); - return poolResponse; } @Override http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/ApiServer.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/ApiServer.java b/server/src/com/cloud/api/ApiServer.java index 6209171..e2dc9ce 100755 --- a/server/src/com/cloud/api/ApiServer.java +++ b/server/src/com/cloud/api/ApiServer.java @@ -91,6 +91,7 @@ import org.apache.log4j.Logger; import org.apache.cloudstack.api.command.admin.host.ListHostsCmd; import org.apache.cloudstack.api.command.admin.router.ListRoutersCmd; +import org.apache.cloudstack.api.command.admin.storage.ListStoragePoolsCmd; import org.apache.cloudstack.api.command.admin.user.ListUsersCmd; import org.apache.cloudstack.api.command.user.project.ListProjectInvitationsCmd; import org.apache.cloudstack.api.command.user.project.ListProjectsCmd; @@ -476,6 +477,7 @@ public class ApiServer implements HttpRequestHandler { && !(cmdObj instanceof ListVolumesCmd) && !(cmdObj instanceof ListUsersCmd) && !(cmdObj instanceof ListAccountsCmd) + && !(cmdObj instanceof ListStoragePoolsCmd) ) { buildAsyncListResponse((BaseListCmd) cmdObj, caller); } http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/query/QueryManagerImpl.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/query/QueryManagerImpl.java b/server/src/com/cloud/api/query/QueryManagerImpl.java index ceec932..b61f10a 100644 --- a/server/src/com/cloud/api/query/QueryManagerImpl.java +++ b/server/src/com/cloud/api/query/QueryManagerImpl.java @@ -26,6 +26,7 @@ import javax.naming.ConfigurationException; import org.apache.cloudstack.api.command.admin.host.ListHostsCmd; import org.apache.cloudstack.api.command.admin.router.ListRoutersCmd; +import org.apache.cloudstack.api.command.admin.storage.ListStoragePoolsCmd; import org.apache.cloudstack.api.command.admin.user.ListUsersCmd; import org.apache.cloudstack.api.command.user.account.ListAccountsCmd; import org.apache.cloudstack.api.command.user.account.ListProjectAccountsCmd; @@ -50,6 +51,7 @@ import org.apache.cloudstack.api.response.ProjectInvitationResponse; import org.apache.cloudstack.api.response.ProjectResponse; import org.apache.cloudstack.api.response.ResourceTagResponse; import org.apache.cloudstack.api.response.SecurityGroupResponse; +import org.apache.cloudstack.api.response.StoragePoolResponse; import org.apache.cloudstack.api.response.UserResponse; import org.apache.cloudstack.api.response.UserVmResponse; import org.apache.cloudstack.api.response.VolumeResponse; @@ -66,6 +68,7 @@ import com.cloud.api.query.dao.ProjectInvitationJoinDao; import com.cloud.api.query.dao.ProjectJoinDao; import com.cloud.api.query.dao.ResourceTagJoinDao; import com.cloud.api.query.dao.SecurityGroupJoinDao; +import com.cloud.api.query.dao.StoragePoolJoinDao; import com.cloud.api.query.dao.UserAccountJoinDao; import com.cloud.api.query.dao.UserVmJoinDao; import com.cloud.api.query.dao.VolumeJoinDao; @@ -80,6 +83,7 @@ import com.cloud.api.query.vo.ProjectInvitationJoinVO; import com.cloud.api.query.vo.ProjectJoinVO; import com.cloud.api.query.vo.ResourceTagJoinVO; import com.cloud.api.query.vo.SecurityGroupJoinVO; +import com.cloud.api.query.vo.StoragePoolJoinVO; import com.cloud.api.query.vo.UserAccountJoinVO; import com.cloud.api.query.vo.UserVmJoinVO; import com.cloud.api.query.vo.VolumeJoinVO; @@ -100,6 +104,8 @@ import com.cloud.projects.ProjectManager; import com.cloud.projects.dao.ProjectAccountDao; import com.cloud.projects.dao.ProjectDao; import com.cloud.server.Criteria; +import com.cloud.storage.StoragePool; +import com.cloud.storage.StoragePoolVO; import com.cloud.storage.Volume; import com.cloud.user.Account; import com.cloud.user.AccountManager; @@ -200,6 +206,9 @@ public class QueryManagerImpl implements QueryService, Manager { private AsyncJobJoinDao _jobJoinDao; @Inject + private StoragePoolJoinDao _poolJoinDao; + + @Inject private HighAvailabilityManager _haMgr; @Override @@ -1789,4 +1798,95 @@ public class QueryManagerImpl implements QueryService, Manager { return _jobJoinDao.searchAndCount(sc, searchFilter); } + @Override + public ListResponse<StoragePoolResponse> searchForStoragePools(ListStoragePoolsCmd cmd) { + Pair<List<StoragePoolJoinVO>, Integer> result = searchForStoragePoolsInternal(cmd); + ListResponse<StoragePoolResponse> response = new ListResponse<StoragePoolResponse>(); + + List<StoragePoolResponse> poolResponses = ViewResponseHelper.createStoragePoolResponse(result.first().toArray(new StoragePoolJoinVO[result.first().size()])); + response.setResponses(poolResponses, result.second()); + return response; + } + + public Pair<List<StoragePoolJoinVO>, Integer> searchForStoragePoolsInternal(ListStoragePoolsCmd cmd) { + + Long zoneId = _accountMgr.checkAccessAndSpecifyAuthority(UserContext.current().getCaller(), cmd.getZoneId()); + Object id = cmd.getId(); + Object name = cmd.getStoragePoolName(); + Object path = cmd.getPath(); + Object pod = cmd.getPodId(); + Object cluster = cmd.getClusterId(); + Object address = cmd.getIpAddress(); + Object keyword = cmd.getKeyword(); + Long startIndex = cmd.getStartIndex(); + Long pageSize = cmd.getPageSizeVal(); + + + Filter searchFilter = new Filter(StoragePoolJoinVO.class, "id", Boolean.TRUE, startIndex, pageSize); + + SearchBuilder<StoragePoolJoinVO> sb = _poolJoinDao.createSearchBuilder(); + sb.select(null, Func.DISTINCT, sb.entity().getId()); // select distinct ids + sb.and("id", sb.entity().getId(), SearchCriteria.Op.EQ); + sb.and("name", sb.entity().getName(), SearchCriteria.Op.LIKE); + sb.and("path", sb.entity().getPath(), SearchCriteria.Op.EQ); + sb.and("dataCenterId", sb.entity().getZoneId(), SearchCriteria.Op.EQ); + sb.and("podId", sb.entity().getPodId(), SearchCriteria.Op.EQ); + sb.and("clusterId", sb.entity().getClusterId(), SearchCriteria.Op.EQ); + sb.and("hostAddress", sb.entity().getHostAddress(), SearchCriteria.Op.EQ); + + + SearchCriteria<StoragePoolJoinVO> sc = sb.create(); + + + if (keyword != null) { + SearchCriteria<StoragePoolJoinVO> ssc = _poolJoinDao.createSearchCriteria(); + ssc.addOr("name", SearchCriteria.Op.LIKE, "%" + keyword + "%"); + ssc.addOr("poolType", SearchCriteria.Op.LIKE, "%" + keyword + "%"); + + sc.addAnd("name", SearchCriteria.Op.SC, ssc); + } + + if (id != null) { + sc.setParameters("id", id); + } + + if (name != null) { + sc.setParameters("name", SearchCriteria.Op.LIKE, "%" + name + "%"); + } + + if (path != null) { + sc.setParameters("path", SearchCriteria.Op.EQ, path); + } + if (zoneId != null) { + sc.setParameters("dataCenterId", SearchCriteria.Op.EQ, zoneId); + } + if (pod != null) { + sc.setParameters("podId", SearchCriteria.Op.EQ, pod); + } + if (address != null) { + sc.setParameters("hostAddress", SearchCriteria.Op.EQ, address); + } + if (cluster != null) { + sc.setParameters("clusterId", SearchCriteria.Op.EQ, cluster); + } + + // search Pool details by ids + Pair<List<StoragePoolJoinVO>, Integer> uniquePoolPair = _poolJoinDao.searchAndCount(sc, searchFilter); + Integer count = uniquePoolPair.second(); + if (count.intValue() == 0) { + // empty result + return uniquePoolPair; + } + List<StoragePoolJoinVO> uniquePools = uniquePoolPair.first(); + Long[] vrIds = new Long[uniquePools.size()]; + int i = 0; + for (StoragePoolJoinVO v : uniquePools) { + vrIds[i++] = v.getId(); + } + List<StoragePoolJoinVO> vrs = _poolJoinDao.searchByIds(vrIds); + return new Pair<List<StoragePoolJoinVO>, Integer>(vrs, count); + + } + + } http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/query/ViewResponseHelper.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/query/ViewResponseHelper.java b/server/src/com/cloud/api/query/ViewResponseHelper.java index 0849586..39c108e 100644 --- a/server/src/com/cloud/api/query/ViewResponseHelper.java +++ b/server/src/com/cloud/api/query/ViewResponseHelper.java @@ -34,6 +34,7 @@ import org.apache.cloudstack.api.response.ProjectInvitationResponse; import org.apache.cloudstack.api.response.ProjectResponse; import org.apache.cloudstack.api.response.ResourceTagResponse; import org.apache.cloudstack.api.response.SecurityGroupResponse; +import org.apache.cloudstack.api.response.StoragePoolResponse; import org.apache.cloudstack.api.response.UserResponse; import org.apache.cloudstack.api.response.UserVmResponse; import org.apache.cloudstack.api.response.VolumeResponse; @@ -51,6 +52,7 @@ import com.cloud.api.query.vo.ProjectInvitationJoinVO; import com.cloud.api.query.vo.ProjectJoinVO; import com.cloud.api.query.vo.ResourceTagJoinVO; import com.cloud.api.query.vo.SecurityGroupJoinVO; +import com.cloud.api.query.vo.StoragePoolJoinVO; import com.cloud.api.query.vo.UserAccountJoinVO; import com.cloud.api.query.vo.UserVmJoinVO; import com.cloud.api.query.vo.VolumeJoinVO; @@ -238,6 +240,25 @@ public class ViewResponseHelper { return new ArrayList<VolumeResponse>(vrDataList.values()); } + public static List<StoragePoolResponse> createStoragePoolResponse(StoragePoolJoinVO... pools) { + Hashtable<Long, StoragePoolResponse> vrDataList = new Hashtable<Long, StoragePoolResponse>(); + // Initialise the vrdatalist with the input data + for (StoragePoolJoinVO vr : pools) { + StoragePoolResponse vrData = vrDataList.get(vr.getId()); + if ( vrData == null ){ + // first time encountering this vm + vrData = ApiDBUtils.newStoragePoolResponse(vr); + } + else{ + // update tags + vrData = ApiDBUtils.fillStoragePoolDetails(vrData, vr); + } + vrDataList.put(vr.getId(), vrData); + } + return new ArrayList<StoragePoolResponse>(vrDataList.values()); + } + + public static List<AccountResponse> createAccountResponse(AccountJoinVO... accounts) { List<AccountResponse> respList = new ArrayList<AccountResponse>(); for (AccountJoinVO vt : accounts){ http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/query/dao/StoragePoolJoinDao.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/query/dao/StoragePoolJoinDao.java b/server/src/com/cloud/api/query/dao/StoragePoolJoinDao.java new file mode 100644 index 0000000..bbb0242 --- /dev/null +++ b/server/src/com/cloud/api/query/dao/StoragePoolJoinDao.java @@ -0,0 +1,37 @@ +// 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.api.query.dao; + +import java.util.List; + +import org.apache.cloudstack.api.response.StoragePoolResponse; + +import com.cloud.api.query.vo.StoragePoolJoinVO; +import com.cloud.storage.StoragePool; +import com.cloud.utils.db.GenericDao; + +public interface StoragePoolJoinDao extends GenericDao<StoragePoolJoinVO, Long> { + + StoragePoolResponse newStoragePoolResponse(StoragePoolJoinVO host); + + StoragePoolResponse setStoragePoolResponse(StoragePoolResponse response, StoragePoolJoinVO host); + + List<StoragePoolJoinVO> newStoragePoolView(StoragePool group); + + List<StoragePoolJoinVO> searchByIds(Long... spIds); + +} http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/query/dao/StoragePoolJoinDaoImpl.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/query/dao/StoragePoolJoinDaoImpl.java b/server/src/com/cloud/api/query/dao/StoragePoolJoinDaoImpl.java new file mode 100644 index 0000000..44ed130 --- /dev/null +++ b/server/src/com/cloud/api/query/dao/StoragePoolJoinDaoImpl.java @@ -0,0 +1,185 @@ +// 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.api.query.dao; + +import java.util.ArrayList; +import java.util.List; +import javax.ejb.Local; + +import org.apache.log4j.Logger; + +import com.cloud.api.ApiDBUtils; +import com.cloud.api.query.vo.StoragePoolJoinVO; +import com.cloud.configuration.dao.ConfigurationDao; +import org.apache.cloudstack.api.response.StoragePoolResponse; + +import com.cloud.storage.StoragePool; +import com.cloud.storage.StorageStats; +import com.cloud.utils.component.Inject; +import com.cloud.utils.db.GenericDaoBase; +import com.cloud.utils.db.SearchBuilder; +import com.cloud.utils.db.SearchCriteria; + + +@Local(value={StoragePoolJoinDao.class}) +public class StoragePoolJoinDaoImpl extends GenericDaoBase<StoragePoolJoinVO, Long> implements StoragePoolJoinDao { + public static final Logger s_logger = Logger.getLogger(StoragePoolJoinDaoImpl.class); + + @Inject + private ConfigurationDao _configDao; + + private SearchBuilder<StoragePoolJoinVO> spSearch; + + private SearchBuilder<StoragePoolJoinVO> spIdSearch; + + + protected StoragePoolJoinDaoImpl() { + + spSearch = createSearchBuilder(); + spSearch.and("idIN", spSearch.entity().getId(), SearchCriteria.Op.IN); + spSearch.done(); + + spIdSearch = createSearchBuilder(); + spIdSearch.and("id", spIdSearch.entity().getId(), SearchCriteria.Op.EQ); + spIdSearch.done(); + + this._count = "select count(distinct id) from storage_pool_view WHERE "; + } + + + + + + @Override + public StoragePoolResponse newStoragePoolResponse(StoragePoolJoinVO pool) { + StoragePoolResponse poolResponse = new StoragePoolResponse(); + poolResponse.setId(pool.getUuid()); + poolResponse.setName(pool.getName()); + poolResponse.setState(pool.getStatus()); + poolResponse.setPath(pool.getPath()); + poolResponse.setIpAddress(pool.getHostAddress()); + poolResponse.setZoneId(pool.getZoneUuid()); + poolResponse.setZoneName(pool.getZoneName()); + if (pool.getPoolType() != null) { + poolResponse.setType(pool.getPoolType().toString()); + } + poolResponse.setPodId(pool.getPodUuid()); + poolResponse.setPodName(pool.getPodName()); + poolResponse.setCreated(pool.getCreated()); + + + long allocatedSize = pool.getUsedCapacity() + pool.getReservedCapacity(); + poolResponse.setDiskSizeTotal(pool.getCapacityBytes()); + poolResponse.setDiskSizeAllocated(allocatedSize); + + //TODO: StatsCollector does not persist data + StorageStats stats = ApiDBUtils.getStoragePoolStatistics(pool.getId()); + if (stats != null) { + Long used = stats.getByteUsed(); + poolResponse.setDiskSizeUsed(used); + } + + poolResponse.setClusterId(pool.getClusterUuid()); + poolResponse.setClusterName(pool.getClusterName()); + poolResponse.setTags(pool.getTag()); + + // set async job + poolResponse.setJobId(pool.getJobUuid()); + poolResponse.setJobStatus(pool.getJobStatus()); + + poolResponse.setObjectName("storagepool"); + return poolResponse; + } + + + + + + @Override + public StoragePoolResponse setStoragePoolResponse(StoragePoolResponse response, StoragePoolJoinVO sp) { + String tag = sp.getTag(); + if (tag != null) { + if ( response.getTags() != null && response.getTags().length() > 0){ + response.setTags(response.getTags() + "," + tag); + } + else{ + response.setTags(tag); + } + } + return response; + } + + + + @Override + public List<StoragePoolJoinVO> newStoragePoolView(StoragePool host) { + SearchCriteria<StoragePoolJoinVO> sc = spIdSearch.create(); + sc.setParameters("id", host.getId()); + return searchIncludingRemoved(sc, null, null, false); + + } + + + + @Override + public List<StoragePoolJoinVO> searchByIds(Long... spIds) { + // set detail batch query size + int DETAILS_BATCH_SIZE = 2000; + String batchCfg = _configDao.getValue("detail.batch.query.size"); + if ( batchCfg != null ){ + DETAILS_BATCH_SIZE = Integer.parseInt(batchCfg); + } + // query details by batches + List<StoragePoolJoinVO> uvList = new ArrayList<StoragePoolJoinVO>(); + // query details by batches + int curr_index = 0; + if ( spIds.length > DETAILS_BATCH_SIZE ){ + while ( (curr_index + DETAILS_BATCH_SIZE ) <= spIds.length ) { + Long[] ids = new Long[DETAILS_BATCH_SIZE]; + for (int k = 0, j = curr_index; j < curr_index + DETAILS_BATCH_SIZE; j++, k++) { + ids[k] = spIds[j]; + } + SearchCriteria<StoragePoolJoinVO> sc = spSearch.create(); + sc.setParameters("idIN", ids); + List<StoragePoolJoinVO> vms = searchIncludingRemoved(sc, null, null, false); + if (vms != null) { + uvList.addAll(vms); + } + curr_index += DETAILS_BATCH_SIZE; + } + } + if (curr_index < spIds.length) { + int batch_size = (spIds.length - curr_index); + // set the ids value + Long[] ids = new Long[batch_size]; + for (int k = 0, j = curr_index; j < curr_index + batch_size; j++, k++) { + ids[k] = spIds[j]; + } + SearchCriteria<StoragePoolJoinVO> sc = spSearch.create(); + sc.setParameters("idIN", ids); + List<StoragePoolJoinVO> vms = searchIncludingRemoved(sc, null, null, false); + if (vms != null) { + uvList.addAll(vms); + } + } + return uvList; + } + + + + +} http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/query/vo/BaseViewVO.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/query/vo/BaseViewVO.java b/server/src/com/cloud/api/query/vo/BaseViewVO.java index 604f459..6b1ddd6 100644 --- a/server/src/com/cloud/api/query/vo/BaseViewVO.java +++ b/server/src/com/cloud/api/query/vo/BaseViewVO.java @@ -16,8 +16,6 @@ // under the License. package com.cloud.api.query.vo; -import javax.persistence.Column; - public abstract class BaseViewVO { public abstract long getId(); http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/api/query/vo/StoragePoolJoinVO.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/api/query/vo/StoragePoolJoinVO.java b/server/src/com/cloud/api/query/vo/StoragePoolJoinVO.java new file mode 100644 index 0000000..fd837bd --- /dev/null +++ b/server/src/com/cloud/api/query/vo/StoragePoolJoinVO.java @@ -0,0 +1,341 @@ +// 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.api.query.vo; + +import java.util.Date; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.EnumType; +import javax.persistence.Enumerated; +import javax.persistence.Id; +import javax.persistence.Table; +import com.cloud.org.Cluster; +import com.cloud.storage.StoragePoolStatus; +import com.cloud.storage.Storage.StoragePoolType; +import com.cloud.utils.db.GenericDao; + +import org.apache.cloudstack.api.Identity; +import org.apache.cloudstack.api.InternalIdentity; + +/** + * Storage Pool DB view. + * @author minc + * + */ +@Entity +@Table(name="storage_pool_view") +public class StoragePoolJoinVO extends BaseViewVO implements InternalIdentity, Identity { + + @Id + @Column(name="id") + private long id; + + @Column(name="uuid") + private String uuid; + + @Column(name="name") + private String name; + + @Column(name="path") + private String path; + + @Column(name="host_address") + private String hostAddress; + + + @Column(name="status") + @Enumerated(value=EnumType.STRING) + private StoragePoolStatus status; + + @Column(name="pool_type") + @Enumerated(value=EnumType.STRING) + private StoragePoolType poolType; + + @Column(name=GenericDao.CREATED_COLUMN) + private Date created; + + @Column(name=GenericDao.REMOVED_COLUMN) + private Date removed; + + @Column(name="capacity_bytes") + private long capacityBytes; + + @Column(name="cluster_id") + private long clusterId; + + @Column(name="cluster_uuid") + private String clusterUuid; + + @Column(name="cluster_name") + private String clusterName; + + @Column(name="cluster_type") + @Enumerated(value=EnumType.STRING) + private Cluster.ClusterType clusterType; + + @Column(name="data_center_id") + private long zoneId; + + @Column(name="data_center_uuid") + private String zoneUuid; + + @Column(name="data_center_name") + private String zoneName; + + @Column(name="pod_id") + private long podId; + + @Column(name="pod_uuid") + private String podUuid; + + @Column(name="pod_name") + private String podName; + + + @Column(name="tag") + private String tag; + + @Column(name="disk_used_capacity") + private long usedCapacity; + + @Column(name="disk_reserved_capacity") + private long reservedCapacity; + + + @Column(name="job_id") + private long jobId; + + @Column(name="job_uuid") + private String jobUuid; + + @Column(name="job_status") + private int jobStatus; + + @Override + public long getId() { + return id; + } + + @Override + public void setId(long id) { + this.id = id; + } + + @Override + public String getUuid() { + return uuid; + } + + public void setUuid(String uuid) { + this.uuid = uuid; + } + + public String getName() { + return name; + } + + public void setName(String name) { + this.name = name; + } + + public String getPath() { + return path; + } + + public void setPath(String path) { + this.path = path; + } + + public String getHostAddress() { + return hostAddress; + } + + public void setHostAddress(String hostAddress) { + this.hostAddress = hostAddress; + } + + public StoragePoolStatus getStatus() { + return status; + } + + public void setStatus(StoragePoolStatus status) { + this.status = status; + } + + public StoragePoolType getPoolType() { + return poolType; + } + + public void setPoolType(StoragePoolType poolType) { + this.poolType = poolType; + } + + public Date getCreated() { + return created; + } + + public void setCreated(Date created) { + this.created = created; + } + + public Date getRemoved() { + return removed; + } + + public void setRemoved(Date removed) { + this.removed = removed; + } + + public long getCapacityBytes() { + return capacityBytes; + } + + public void setCapacityBytes(long capacityBytes) { + this.capacityBytes = capacityBytes; + } + + public long getClusterId() { + return clusterId; + } + + public void setClusterId(long clusterId) { + this.clusterId = clusterId; + } + + public String getClusterUuid() { + return clusterUuid; + } + + public void setClusterUuid(String clusterUuid) { + this.clusterUuid = clusterUuid; + } + + public String getClusterName() { + return clusterName; + } + + public void setClusterName(String clusterName) { + this.clusterName = clusterName; + } + + public Cluster.ClusterType getClusterType() { + return clusterType; + } + + public void setClusterType(Cluster.ClusterType clusterType) { + this.clusterType = clusterType; + } + + public long getZoneId() { + return zoneId; + } + + public void setZoneId(long zoneId) { + this.zoneId = zoneId; + } + + public String getZoneUuid() { + return zoneUuid; + } + + public void setZoneUuid(String zoneUuid) { + this.zoneUuid = zoneUuid; + } + + public String getZoneName() { + return zoneName; + } + + public void setZoneName(String zoneName) { + this.zoneName = zoneName; + } + + public long getPodId() { + return podId; + } + + public void setPodId(long podId) { + this.podId = podId; + } + + public String getPodUuid() { + return podUuid; + } + + public void setPodUuid(String podUuid) { + this.podUuid = podUuid; + } + + public String getPodName() { + return podName; + } + + public void setPodName(String podName) { + this.podName = podName; + } + + public String getTag() { + return tag; + } + + public void setTag(String tag) { + this.tag = tag; + } + + public long getUsedCapacity() { + return usedCapacity; + } + + public void setUsedCapacity(long usedCapacity) { + this.usedCapacity = usedCapacity; + } + + public long getReservedCapacity() { + return reservedCapacity; + } + + public void setReservedCapacity(long reservedCapacity) { + this.reservedCapacity = reservedCapacity; + } + + public long getJobId() { + return jobId; + } + + public void setJobId(long jobId) { + this.jobId = jobId; + } + + public String getJobUuid() { + return jobUuid; + } + + public void setJobUuid(String jobUuid) { + this.jobUuid = jobUuid; + } + + public int getJobStatus() { + return jobStatus; + } + + public void setJobStatus(int jobStatus) { + this.jobStatus = jobStatus; + } + + +} http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/configuration/DefaultComponentLibrary.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/configuration/DefaultComponentLibrary.java b/server/src/com/cloud/configuration/DefaultComponentLibrary.java index 8d8ee9a..8c6d35f 100755 --- a/server/src/com/cloud/configuration/DefaultComponentLibrary.java +++ b/server/src/com/cloud/configuration/DefaultComponentLibrary.java @@ -34,6 +34,7 @@ import com.cloud.api.query.dao.ProjectInvitationJoinDaoImpl; import com.cloud.api.query.dao.ProjectJoinDaoImpl; import com.cloud.api.query.dao.ResourceTagJoinDaoImpl; import com.cloud.api.query.dao.SecurityGroupJoinDaoImpl; +import com.cloud.api.query.dao.StoragePoolJoinDaoImpl; import com.cloud.api.query.dao.UserAccountJoinDaoImpl; import com.cloud.api.query.dao.UserVmJoinDaoImpl; import com.cloud.api.query.dao.HostJoinDaoImpl; @@ -388,6 +389,7 @@ public class DefaultComponentLibrary extends ComponentLibraryBase implements Com addDao("VolumeJoinDao", VolumeJoinDaoImpl.class); addDao("AccountJoinDao", AccountJoinDaoImpl.class); addDao("AsyncJobJoinDao", AsyncJobJoinDaoImpl.class); + addDao("StoragePoolJoinDao", StoragePoolJoinDaoImpl.class); } @Override http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/src/com/cloud/server/ManagementServerImpl.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/server/ManagementServerImpl.java b/server/src/com/cloud/server/ManagementServerImpl.java index 22ae179..dcecaf4 100755 --- a/server/src/com/cloud/server/ManagementServerImpl.java +++ b/server/src/com/cloud/server/ManagementServerImpl.java @@ -2335,23 +2335,6 @@ public class ManagementServerImpl implements ManagementServer { } } - @Override - public Pair<List<? extends StoragePool>, Integer> searchForStoragePools(ListStoragePoolsCmd cmd) { - - Long zoneId = _accountMgr.checkAccessAndSpecifyAuthority(UserContext.current().getCaller(), cmd.getZoneId()); - Criteria c = new Criteria("id", Boolean.TRUE, cmd.getStartIndex(), cmd.getPageSizeVal()); - c.addCriteria(Criteria.ID, cmd.getId()); - c.addCriteria(Criteria.NAME, cmd.getStoragePoolName()); - c.addCriteria(Criteria.CLUSTERID, cmd.getClusterId()); - c.addCriteria(Criteria.ADDRESS, cmd.getIpAddress()); - c.addCriteria(Criteria.KEYWORD, cmd.getKeyword()); - c.addCriteria(Criteria.PATH, cmd.getPath()); - c.addCriteria(Criteria.PODID, cmd.getPodId()); - c.addCriteria(Criteria.DATACENTERID, zoneId); - - Pair<List<StoragePoolVO>, Integer> result = searchForStoragePools(c); - return new Pair<List<? extends StoragePool>, Integer>(result.first(), result.second()); - } @Override public Pair<List<StoragePoolVO>, Integer> searchForStoragePools(Criteria c) { http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/server/test/com/cloud/api/ListPerfTest.java ---------------------------------------------------------------------- diff --git a/server/test/com/cloud/api/ListPerfTest.java b/server/test/com/cloud/api/ListPerfTest.java index 7402cea..eb98d91 100644 --- a/server/test/com/cloud/api/ListPerfTest.java +++ b/server/test/com/cloud/api/ListPerfTest.java @@ -148,4 +148,21 @@ public class ListPerfTest extends APITest { System.out.println("Time taken to list Users: " + (after - before) + " ms"); } + + @Test + public void testListStoragePools(){ + // issue list Storage pool calls + HashMap<String, String> params = new HashMap<String, String>(); + params.put("response", "json"); + params.put("listAll", "true"); + params.put("sessionkey", sessionKey); + long before = System.currentTimeMillis(); + String result = this.sendRequest("listStoragePools", params); + long after = System.currentTimeMillis(); + System.out.println("Time taken to list StoragePools: " + (after - before) + " ms"); + + } + + + } http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/setup/db/create-schema-view.sql ---------------------------------------------------------------------- diff --git a/setup/db/create-schema-view.sql b/setup/db/create-schema-view.sql index f502c88..bf04c1c 100755 --- a/setup/db/create-schema-view.sql +++ b/setup/db/create-schema-view.sql @@ -782,3 +782,40 @@ left join autoscale_policies on async_job.instance_id = autoscale_policies.id left join autoscale_vmprofiles on async_job.instance_id = autoscale_vmprofiles.id left join autoscale_vmgroups on async_job.instance_id = autoscale_vmgroups.id; +DROP VIEW IF EXISTS `cloud`.`storage_pool_view`; +CREATE VIEW storage_pool_view AS +select +storage_pool.id, +storage_pool.uuid, +storage_pool.name, +storage_pool.status, +storage_pool.path, +storage_pool.pool_type, +storage_pool.host_address, +storage_pool.created, +storage_pool.removed, +storage_pool.capacity_bytes, +cluster.id cluster_id, +cluster.uuid cluster_uuid, +cluster.name cluster_name, +cluster.cluster_type, +data_center.id data_center_id, +data_center.uuid data_center_uuid, +data_center.name data_center_name, +host_pod_ref.id pod_id, +host_pod_ref.uuid pod_uuid, +host_pod_ref.name pod_name, +storage_pool_details.name tag, +op_host_capacity.used_capacity disk_used_capacity, +op_host_capacity.reserved_capacity disk_reserved_capacity, +async_job.id job_id, +async_job.uuid job_uuid, +async_job.job_status job_status, +async_job.account_id job_account_id +from storage_pool +left join cluster on storage_pool.cluster_id = cluster.id +left join data_center on storage_pool.data_center_id = data_center.id +left join host_pod_ref on storage_pool.pod_id = host_pod_ref.id +left join storage_pool_details on storage_pool_details.pool_id = storage_pool.id and storage_pool_details.value = 'true' +left join op_host_capacity on storage_pool.id = op_host_capacity.host_id and op_host_capacity.capacity_type = 3 +left join async_job on async_job.instance_id = storage_pool.id and async_job.instance_type = "StoragePool" and async_job.job_status = 0; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/74c15a22/setup/db/db/schema-40to410.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-40to410.sql b/setup/db/db/schema-40to410.sql index 1142320..bf3fb30 100644 --- a/setup/db/db/schema-40to410.sql +++ b/setup/db/db/schema-40to410.sql @@ -143,6 +143,8 @@ UPDATE `cloud`.`conditions` set uuid=id WHERE uuid is NULL; INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', '"detail.batch.query.size"', '2000', 'Default entity detail batch query size for listing'); --- DB views for list api --- +use cloud; + DROP VIEW IF EXISTS `cloud`.`user_vm_view`; CREATE VIEW `cloud`.`user_vm_view` AS select @@ -905,3 +907,41 @@ left join conditions on async_job.instance_id = conditions.id left join autoscale_policies on async_job.instance_id = autoscale_policies.id left join autoscale_vmprofiles on async_job.instance_id = autoscale_vmprofiles.id left join autoscale_vmgroups on async_job.instance_id = autoscale_vmgroups.id; + +DROP VIEW IF EXISTS `cloud`.`storage_pool_view`; +CREATE VIEW storage_pool_view AS +select +storage_pool.id, +storage_pool.uuid, +storage_pool.name, +storage_pool.status, +storage_pool.path, +storage_pool.pool_type, +storage_pool.host_address, +storage_pool.created, +storage_pool.removed, +storage_pool.capacity_bytes, +cluster.id cluster_id, +cluster.uuid cluster_uuid, +cluster.name cluster_name, +cluster.cluster_type, +data_center.id data_center_id, +data_center.uuid data_center_uuid, +data_center.name data_center_name, +host_pod_ref.id pod_id, +host_pod_ref.uuid pod_uuid, +host_pod_ref.name pod_name, +storage_pool_details.name tag, +op_host_capacity.used_capacity disk_used_capacity, +op_host_capacity.reserved_capacity disk_reserved_capacity, +async_job.id job_id, +async_job.uuid job_uuid, +async_job.job_status job_status, +async_job.account_id job_account_id +from storage_pool +left join cluster on storage_pool.cluster_id = cluster.id +left join data_center on storage_pool.data_center_id = data_center.id +left join host_pod_ref on storage_pool.pod_id = host_pod_ref.id +left join storage_pool_details on storage_pool_details.pool_id = storage_pool.id and storage_pool_details.value = 'true' +left join op_host_capacity on storage_pool.id = op_host_capacity.host_id and op_host_capacity.capacity_type = 3 +left join async_job on async_job.instance_id = storage_pool.id and async_job.instance_type = "StoragePool" and async_job.job_status = 0;
