http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/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 1824f49..ac7602c 100755 --- a/server/src/com/cloud/configuration/DefaultComponentLibrary.java +++ b/server/src/com/cloud/configuration/DefaultComponentLibrary.java @@ -25,6 +25,7 @@ import com.cloud.agent.manager.ClusteredAgentManagerImpl; import com.cloud.alert.AlertManagerImpl; import com.cloud.alert.dao.AlertDaoImpl; import com.cloud.api.query.QueryManagerImpl; +import com.cloud.api.query.dao.AccountJoinDaoImpl; import com.cloud.api.query.dao.DomainRouterJoinDaoImpl; import com.cloud.api.query.dao.InstanceGroupJoinDaoImpl; import com.cloud.api.query.dao.ProjectAccountJoinDaoImpl; @@ -32,6 +33,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.UserAccountJoinDaoImpl; import com.cloud.api.query.dao.UserVmJoinDaoImpl; import com.cloud.api.query.dao.HostJoinDaoImpl; import com.cloud.api.query.dao.VolumeJoinDaoImpl; @@ -195,7 +197,6 @@ import com.cloud.user.DomainManagerImpl; import com.cloud.user.dao.AccountDaoImpl; import com.cloud.user.dao.SSHKeyPairDaoImpl; import com.cloud.user.dao.UserAccountDaoImpl; -import com.cloud.user.dao.UserAccountJoinDaoImpl; import com.cloud.user.dao.UserDaoImpl; import com.cloud.user.dao.UserStatisticsDaoImpl; import com.cloud.user.dao.UserStatsLogDaoImpl; @@ -244,6 +245,7 @@ public class DefaultComponentLibrary extends ComponentLibraryBase implements Com addDao("ProjectInvitationJoinDao", ProjectInvitationJoinDaoImpl.class); addDao("HostJoinDao", HostJoinDaoImpl.class); addDao("VolumeJoinDao", VolumeJoinDaoImpl.class); + addDao("AccountJoinDao", AccountJoinDaoImpl.class); ComponentInfo<? extends GenericDao<?, ? extends Serializable>> info = addDao("ServiceOfferingDao", ServiceOfferingDaoImpl.class); info.addParameter("cache.size", "50"); info.addParameter("cache.time.to.live", "600");
http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/server/src/com/cloud/resourcelimit/ResourceLimitManagerImpl.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/resourcelimit/ResourceLimitManagerImpl.java b/server/src/com/cloud/resourcelimit/ResourceLimitManagerImpl.java index 7f446d7..92be18b 100755 --- a/server/src/com/cloud/resourcelimit/ResourceLimitManagerImpl.java +++ b/server/src/com/cloud/resourcelimit/ResourceLimitManagerImpl.java @@ -243,6 +243,36 @@ public class ResourceLimitManagerImpl implements ResourceLimitService, Manager { } @Override + public long findCorrectResourceLimitForAccount(short accountType, Long limit, ResourceType type) { + + long max = Resource.RESOURCE_UNLIMITED; // if resource limit is not found, then we treat it as unlimited + + // No limits for Root Admin accounts + if (_accountMgr.isRootAdmin(accountType)) { + return max; + } + + + // Check if limit is configured for account + if (limit != null) { + max = limit.longValue(); + } else { + // If the account has an no limit set, then return global default account limits + Long value = null; + if (accountType == Account.ACCOUNT_TYPE_PROJECT) { + value = projectResourceLimitMap.get(type); + } else { + value = accountResourceLimitMap.get(type); + } + if (value != null) { + return value; + } + } + + return max; + } + + @Override public long findCorrectResourceLimitForDomain(Domain domain, ResourceType type) { long max = Resource.RESOURCE_UNLIMITED; http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/server/src/com/cloud/user/AccountManagerImpl.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/user/AccountManagerImpl.java b/server/src/com/cloud/user/AccountManagerImpl.java index 593d295..6a498c2 100755 --- a/server/src/com/cloud/user/AccountManagerImpl.java +++ b/server/src/com/cloud/user/AccountManagerImpl.java @@ -46,6 +46,7 @@ import com.cloud.acl.ControlledEntity; import com.cloud.acl.SecurityChecker; import com.cloud.acl.SecurityChecker.AccessType; import com.cloud.api.ApiDBUtils; +import com.cloud.api.query.dao.UserAccountJoinDao; import com.cloud.api.query.vo.ControlledViewEntity; @@ -110,7 +111,6 @@ import com.cloud.template.VirtualMachineTemplate; import com.cloud.user.Account.State; import com.cloud.user.dao.AccountDao; import com.cloud.user.dao.UserAccountDao; -import com.cloud.user.dao.UserAccountJoinDao; import com.cloud.user.dao.UserDao; import com.cloud.utils.NumbersUtil; import com.cloud.utils.Pair; @@ -2021,125 +2021,6 @@ public class AccountManagerImpl implements AccountManager, AccountService, Manag return null; } - @Override - public Pair<List<? extends Account>, Integer> searchForAccounts(ListAccountsCmd cmd) { - Account caller = UserContext.current().getCaller(); - Long domainId = cmd.getDomainId(); - Long accountId = cmd.getId(); - String accountName = cmd.getSearchName(); - boolean isRecursive = cmd.isRecursive(); - boolean listAll = cmd.listAll(); - Boolean listForDomain = false; - - if (accountId != null) { - Account account = _accountDao.findById(accountId); - if (account == null || account.getId() == Account.ACCOUNT_ID_SYSTEM) { - throw new InvalidParameterValueException("Unable to find account by id " + accountId); - } - - checkAccess(caller, null, true, account); - } - - if (domainId != null) { - Domain domain = _domainMgr.getDomain(domainId); - if (domain == null) { - throw new InvalidParameterValueException("Domain id=" + domainId + " doesn't exist"); - } - - checkAccess(caller, domain); - - if (accountName != null) { - Account account = _accountDao.findActiveAccount(accountName, domainId); - if (account == null || account.getId() == Account.ACCOUNT_ID_SYSTEM) { - throw new InvalidParameterValueException("Unable to find account by name " + accountName + " in domain " + domainId); - } - checkAccess(caller, null, true, account); - } - } - - if (accountId == null) { - if (isAdmin(caller.getType()) && listAll && domainId == null) { - listForDomain = true; - isRecursive = true; - if (domainId == null) { - domainId = caller.getDomainId(); - } - } else if (isAdmin(caller.getType()) && domainId != null) { - listForDomain = true; - } else { - accountId = caller.getAccountId(); - } - } - - Filter searchFilter = new Filter(AccountVO.class, "id", true, cmd.getStartIndex(), cmd.getPageSizeVal()); - - Object type = cmd.getAccountType(); - Object state = cmd.getState(); - Object isCleanupRequired = cmd.isCleanupRequired(); - Object keyword = cmd.getKeyword(); - - SearchBuilder<AccountVO> sb = _accountDao.createSearchBuilder(); - sb.and("accountName", sb.entity().getAccountName(), SearchCriteria.Op.EQ); - sb.and("domainId", sb.entity().getDomainId(), SearchCriteria.Op.EQ); - sb.and("id", sb.entity().getId(), SearchCriteria.Op.EQ); - sb.and("type", sb.entity().getType(), SearchCriteria.Op.EQ); - sb.and("state", sb.entity().getState(), SearchCriteria.Op.EQ); - sb.and("needsCleanup", sb.entity().getNeedsCleanup(), SearchCriteria.Op.EQ); - sb.and("typeNEQ", sb.entity().getType(), SearchCriteria.Op.NEQ); - sb.and("idNEQ", sb.entity().getId(), SearchCriteria.Op.NEQ); - - if (listForDomain && isRecursive) { - SearchBuilder<DomainVO> domainSearch = _domainDao.createSearchBuilder(); - domainSearch.and("path", domainSearch.entity().getPath(), SearchCriteria.Op.LIKE); - sb.join("domainSearch", domainSearch, sb.entity().getDomainId(), domainSearch.entity().getId(), JoinBuilder.JoinType.INNER); - } - - SearchCriteria<AccountVO> sc = sb.create(); - - sc.setParameters("idNEQ", Account.ACCOUNT_ID_SYSTEM); - - if (keyword != null) { - SearchCriteria<AccountVO> ssc = _accountDao.createSearchCriteria(); - ssc.addOr("accountName", SearchCriteria.Op.LIKE, "%" + keyword + "%"); - ssc.addOr("state", SearchCriteria.Op.LIKE, "%" + keyword + "%"); - sc.addAnd("accountName", SearchCriteria.Op.SC, ssc); - } - - if (type != null) { - sc.setParameters("type", type); - } - - if (state != null) { - sc.setParameters("state", state); - } - - if (isCleanupRequired != null) { - sc.setParameters("needsCleanup", isCleanupRequired); - } - - if (accountName != null) { - sc.setParameters("accountName", accountName); - } - - // don't return account of type project to the end user - sc.setParameters("typeNEQ", 5); - - if (accountId != null) { - sc.setParameters("id", accountId); - } - - if (listForDomain) { - DomainVO domain = _domainDao.findById(domainId); - if (isRecursive) { - sc.setJoinParameters("domainSearch", "path", domain.getPath() + "%"); - } else { - sc.setParameters("domainId", domainId); - } - } - - Pair<List<AccountVO>, Integer> result = _accountDao.searchAndCount(sc, searchFilter); - return new Pair<List<? extends Account>, Integer>(result.first(), result.second()); - } @Override http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/server/src/com/cloud/user/dao/UserAccountJoinDao.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/user/dao/UserAccountJoinDao.java b/server/src/com/cloud/user/dao/UserAccountJoinDao.java deleted file mode 100644 index 0072d33..0000000 --- a/server/src/com/cloud/user/dao/UserAccountJoinDao.java +++ /dev/null @@ -1,40 +0,0 @@ -// 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.user.dao; - -import java.util.List; - -import org.apache.cloudstack.api.response.UserResponse; - -import com.cloud.api.query.vo.UserAccountJoinVO; -import com.cloud.user.User; -import com.cloud.user.UserAccount; -import com.cloud.utils.db.GenericDao; - -public interface UserAccountJoinDao extends GenericDao<UserAccountJoinVO, Long> { - - UserResponse newUserResponse(UserAccountJoinVO usr); - - UserAccountJoinVO newUserView(User usr); - - UserAccountJoinVO newUserView(UserAccount usr); - - List<UserAccountJoinVO> searchByIds(Long... ids); - - List<UserAccountJoinVO> searchByAccountId(Long accountId); - -} http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/server/src/com/cloud/user/dao/UserAccountJoinDaoImpl.java ---------------------------------------------------------------------- diff --git a/server/src/com/cloud/user/dao/UserAccountJoinDaoImpl.java b/server/src/com/cloud/user/dao/UserAccountJoinDaoImpl.java deleted file mode 100644 index 4cad00b..0000000 --- a/server/src/com/cloud/user/dao/UserAccountJoinDaoImpl.java +++ /dev/null @@ -1,139 +0,0 @@ -// 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.user.dao; - -import java.util.List; - -import javax.ejb.Local; - -import org.apache.log4j.Logger; - -import com.cloud.api.ApiDBUtils; -import com.cloud.api.ApiResponseHelper; -import com.cloud.api.query.vo.InstanceGroupJoinVO; -import com.cloud.api.query.vo.UserAccountJoinVO; - -import org.apache.cloudstack.api.response.InstanceGroupResponse; -import org.apache.cloudstack.api.response.UserResponse; - -import com.cloud.user.Account; -import com.cloud.user.User; -import com.cloud.user.UserAccount; -import com.cloud.utils.db.GenericDaoBase; -import com.cloud.utils.db.SearchBuilder; -import com.cloud.utils.db.SearchCriteria; -import com.cloud.vm.InstanceGroup; - - -@Local(value={UserAccountJoinDao.class}) -public class UserAccountJoinDaoImpl extends GenericDaoBase<UserAccountJoinVO, Long> implements UserAccountJoinDao { - public static final Logger s_logger = Logger.getLogger(UserAccountJoinDaoImpl.class); - - private SearchBuilder<UserAccountJoinVO> vrSearch; - - private SearchBuilder<UserAccountJoinVO> vrIdSearch; - - private SearchBuilder<UserAccountJoinVO> vrAcctIdSearch; - - - protected UserAccountJoinDaoImpl() { - - vrSearch = createSearchBuilder(); - vrSearch.and("idIN", vrSearch.entity().getId(), SearchCriteria.Op.IN); - vrSearch.done(); - - vrIdSearch = createSearchBuilder(); - vrIdSearch.and("id", vrIdSearch.entity().getId(), SearchCriteria.Op.EQ); - vrIdSearch.done(); - - vrAcctIdSearch = createSearchBuilder(); - vrAcctIdSearch.and("accountid", vrAcctIdSearch.entity().getAccountId(), SearchCriteria.Op.EQ); - vrAcctIdSearch.done(); - - - this._count = "select count(distinct id) from user_view WHERE "; - } - - - - @Override - public UserResponse newUserResponse(UserAccountJoinVO usr) { - UserResponse userResponse = new UserResponse(); - userResponse.setAccountId(usr.getAccountUuid()); - userResponse.setAccountName(usr.getAccountName()); - userResponse.setAccountType(usr.getAccountType()); - userResponse.setCreated(usr.getCreated()); - userResponse.setDomainId(usr.getDomainUuid()); - userResponse.setDomainName(usr.getDomainName()); - userResponse.setEmail(usr.getEmail()); - userResponse.setFirstname(usr.getFirstname()); - userResponse.setId(usr.getUuid()); - userResponse.setLastname(usr.getLastname()); - userResponse.setState(usr.getState().toString()); - userResponse.setTimezone(usr.getTimezone()); - userResponse.setUsername(usr.getUsername()); - userResponse.setApiKey(usr.getApiKey()); - userResponse.setSecretKey(usr.getSecretKey()); - userResponse.setObjectName("user"); - - return userResponse; - } - - - @Override - public UserAccountJoinVO newUserView(User usr) { - SearchCriteria<UserAccountJoinVO> sc = vrIdSearch.create(); - sc.setParameters("id", usr.getId()); - List<UserAccountJoinVO> users = searchIncludingRemoved(sc, null, null, false); - assert users != null && users.size() == 1 : "No user found for user id " + usr.getId(); - return users.get(0); - } - - - - - @Override - public UserAccountJoinVO newUserView(UserAccount usr) { - SearchCriteria<UserAccountJoinVO> sc = vrIdSearch.create(); - sc.setParameters("id", usr.getId()); - List<UserAccountJoinVO> users = searchIncludingRemoved(sc, null, null, false); - assert users != null && users.size() == 1 : "No user found for user id " + usr.getId(); - return users.get(0); - } - - - - @Override - public List<UserAccountJoinVO> searchByIds(Long... ids) { - SearchCriteria<UserAccountJoinVO> sc = vrSearch.create(); - sc.setParameters("idIN", ids); - return searchIncludingRemoved(sc, null, null, false); - } - - - - @Override - public List<UserAccountJoinVO> searchByAccountId(Long accountId) { - SearchCriteria<UserAccountJoinVO> sc = vrAcctIdSearch.create(); - sc.setParameters("accountId", accountId); - return searchIncludingRemoved(sc, null, null, false); - } - - - - -} http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/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 9f1b4b5..7402cea 100644 --- a/server/test/com/cloud/api/ListPerfTest.java +++ b/server/test/com/cloud/api/ListPerfTest.java @@ -109,7 +109,7 @@ public class ListPerfTest extends APITest { @Test public void testListVolumes(){ - // issue list Hosts calls + // issue list Volumes calls HashMap<String, String> params = new HashMap<String, String>(); params.put("response", "json"); params.put("listAll", "true"); @@ -120,4 +120,32 @@ public class ListPerfTest extends APITest { System.out.println("Time taken to list Volumes: " + (after - before) + " ms"); } + + @Test + public void testListAccounts(){ + // issue list Accounts 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("listAccounts", params); + long after = System.currentTimeMillis(); + System.out.println("Time taken to list Accounts: " + (after - before) + " ms"); + + } + + @Test + public void testListUsers(){ + // issue list Users 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("listUsers", params); + long after = System.currentTimeMillis(); + System.out.println("Time taken to list Users: " + (after - before) + " ms"); + + } } http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/server/test/com/cloud/user/MockAccountManagerImpl.java ---------------------------------------------------------------------- diff --git a/server/test/com/cloud/user/MockAccountManagerImpl.java b/server/test/com/cloud/user/MockAccountManagerImpl.java index 29c9f6f..4a4196e 100644 --- a/server/test/com/cloud/user/MockAccountManagerImpl.java +++ b/server/test/com/cloud/user/MockAccountManagerImpl.java @@ -301,13 +301,6 @@ public class MockAccountManagerImpl implements Manager, AccountManager, AccountS return null; } - @Override - public Pair<List<? extends Account>, Integer> searchForAccounts(ListAccountsCmd cmd) { - // TODO Auto-generated method stub - return null; - } - - @Override public boolean enableAccount(long accountId) { // TODO Auto-generated method stub http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/server/test/com/cloud/vpc/MockResourceLimitManagerImpl.java ---------------------------------------------------------------------- diff --git a/server/test/com/cloud/vpc/MockResourceLimitManagerImpl.java b/server/test/com/cloud/vpc/MockResourceLimitManagerImpl.java index cb052ed..a0c7b70 100644 --- a/server/test/com/cloud/vpc/MockResourceLimitManagerImpl.java +++ b/server/test/com/cloud/vpc/MockResourceLimitManagerImpl.java @@ -70,6 +70,13 @@ public class MockResourceLimitManagerImpl implements ResourceLimitService, Manag return 0; } + + @Override + public long findCorrectResourceLimitForAccount(short accountType, Long limit, ResourceType type) { + // TODO Auto-generated method stub + return 0; + } + /* (non-Javadoc) * @see com.cloud.user.ResourceLimitService#findCorrectResourceLimitForDomain(com.cloud.domain.Domain, com.cloud.configuration.Resource.ResourceType) */ @@ -85,7 +92,7 @@ public class MockResourceLimitManagerImpl implements ResourceLimitService, Manag @Override public void incrementResourceCount(long accountId, ResourceType type, Long... delta) { // TODO Auto-generated method stub - + } /* (non-Javadoc) @@ -94,7 +101,7 @@ public class MockResourceLimitManagerImpl implements ResourceLimitService, Manag @Override public void decrementResourceCount(long accountId, ResourceType type, Long... delta) { // TODO Auto-generated method stub - + } /* (non-Javadoc) @@ -103,7 +110,7 @@ public class MockResourceLimitManagerImpl implements ResourceLimitService, Manag @Override public void checkResourceLimit(Account account, ResourceType type, long... count) throws ResourceAllocationException { // TODO Auto-generated method stub - + } /* (non-Javadoc) http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/setup/db/create-schema.sql ---------------------------------------------------------------------- diff --git a/setup/db/create-schema.sql b/setup/db/create-schema.sql index 4068cc6..34ac66f 100755 --- a/setup/db/create-schema.sql +++ b/setup/db/create-schema.sql @@ -2900,7 +2900,7 @@ left join projects on projects.project_account_id = instance_group.account_id; DROP VIEW IF EXISTS `cloud`.`user_view`; CREATE VIEW user_view AS -select +select user.id, user.uuid, user.username, @@ -2917,17 +2917,25 @@ user.timezone, user.registration_token, user.is_registered, user.incorrect_login_attempts, -account.id account_id, +account.id account_id, account.uuid account_uuid, account.account_name account_name, account.type account_type, -domain.id domain_id, +domain.id domain_id, domain.uuid domain_uuid, -domain.name domain_name, -domain.path domain_path -from user +domain.name domain_name, +domain.path domain_path, +async_job.id job_id, +async_job.uuid job_uuid, +async_job.job_status job_status, +async_job.account_id job_account_id +from user inner join account on user.account_id = account.id -inner join domain on account.domain_id=domain.id; +inner join domain on account.domain_id=domain.id +left join async_job on async_job.instance_id = user.id and async_job.instance_type = "User" and async_job.job_status = 0; + + + DROP VIEW IF EXISTS `cloud`.`project_view`; CREATE VIEW project_view AS @@ -3145,3 +3153,91 @@ left join vm_template on volumes.template_id = vm_template.id left join resource_tags on resource_tags.resource_id = volumes.id and resource_tags.resource_type = "Volume" left join async_job on async_job.instance_id = volumes.id and async_job.instance_type = "Volume" and async_job.job_status = 0; +DROP VIEW IF EXISTS `cloud`.`account_netstats_view`; +CREATE VIEW account_netstats_view AS +SELECT account_id, +sum(net_bytes_received)+ sum(current_bytes_received) as bytesReceived, +sum(net_bytes_sent)+ sum(current_bytes_sent) as bytesSent +FROM user_statistics +group by account_id; + + +DROP VIEW IF EXISTS `cloud`.`account_vmstats_view`; +CREATE VIEW account_vmstats_view AS +SELECT account_id, state, count(*) as vmcount +from vm_instance +group by account_id, state; + +DROP VIEW IF EXISTS `cloud`.`free_ip_view`; +CREATE VIEW free_ip_view AS +select count(user_ip_address.id) free_ip +from user_ip_address +inner join vlan on vlan.id = user_ip_address.vlan_db_id and vlan.vlan_type = "VirtualNetwork" +where state = "Free" + +DROP VIEW IF EXISTS `cloud`.`account_view`; +CREATE VIEW account_view AS +select +account.id, +account.uuid, +account.account_name, +account.type, +account.state, +account.removed, +account.cleanup_needed, +account.network_domain, +domain.id domain_id, +domain.uuid domain_uuid, +domain.name domain_name, +domain.path domain_path, +data_center.id data_center_id, +data_center.uuid data_center_uuid, +data_center.name data_center_name, +account_netstats_view.bytesReceived, +account_netstats_view.bytesSent, +vmlimit.max vmLimit, +vmcount.count vmTotal, +runningvm.vmcount runningVms, +stoppedvm.vmcount stoppedVms, +iplimit.max ipLimit, +ipcount.count ipTotal, +free_ip_view.free_ip ipFree, +volumelimit.max volumeLimit, +volumecount.count volumeTotal, +snapshotlimit.max snapshotLimit, +snapshotcount.count snapshotTotal, +templatelimit.max templateLimit, +templatecount.count templateTotal, +vpclimit.max vpcLimit, +vpccount.count vpcTotal, +projectlimit.max projectLimit, +projectcount.count projectTotal, +networklimit.max networkLimit, +networkcount.count networkTotal, +async_job.id job_id, +async_job.uuid job_uuid, +async_job.job_status job_status, +async_job.account_id job_account_id +from free_ip_view, account +inner join domain on account.domain_id=domain.id +left join data_center on account.default_zone_id = data_center.id +left join account_netstats_view on account.id = account_netstats_view.account_id +left join resource_limit vmlimit on account.id = vmlimit.account_id and vmlimit.type = "user_vm" +left join resource_count vmcount on account.id = vmcount.account_id and vmcount.type = "user_vm" +left join account_vmstats_view runningvm on account.id = runningvm.account_id and runningvm.state = "Running" +left join account_vmstats_view stoppedvm on account.id = stoppedvm.account_id and stoppedvm.state = "Stopped" +left join resource_limit iplimit on account.id = iplimit.account_id and iplimit.type = "public_ip" +left join resource_count ipcount on account.id = ipcount.account_id and ipcount.type = "public_ip" +left join resource_limit volumelimit on account.id = volumelimit.account_id and volumelimit.type = "volume" +left join resource_count volumecount on account.id = volumecount.account_id and volumecount.type = "volume" +left join resource_limit snapshotlimit on account.id = snapshotlimit.account_id and snapshotlimit.type = "snapshot" +left join resource_count snapshotcount on account.id = snapshotcount.account_id and snapshotcount.type = "snapshot" +left join resource_limit templatelimit on account.id = templatelimit.account_id and templatelimit.type = "template" +left join resource_count templatecount on account.id = templatecount.account_id and templatecount.type = "template" +left join resource_limit vpclimit on account.id = vpclimit.account_id and vpclimit.type = "vpc" +left join resource_count vpccount on account.id = vpccount.account_id and vpccount.type = "vpc" +left join resource_limit projectlimit on account.id = projectlimit.account_id and projectlimit.type = "project" +left join resource_count projectcount on account.id = projectcount.account_id and projectcount.type = "project" +left join resource_limit networklimit on account.id = networklimit.account_id and networklimit.type = "network" +left join resource_count networkcount on account.id = networkcount.account_id and networkcount.type = "network" +left join async_job on async_job.instance_id = account.id and async_job.instance_type = "Account" and async_job.job_status = 0; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-cloudstack/blob/ebfb90e2/setup/db/db/schema-40to410.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-40to410.sql b/setup/db/db/schema-40to410.sql index fc3ab6f..c862641 100644 --- a/setup/db/db/schema-40to410.sql +++ b/setup/db/db/schema-40to410.sql @@ -482,7 +482,7 @@ left join projects on projects.project_account_id = instance_group.account_id; DROP VIEW IF EXISTS `cloud`.`user_view`; CREATE VIEW user_view AS -select +select user.id, user.uuid, user.username, @@ -499,17 +499,23 @@ user.timezone, user.registration_token, user.is_registered, user.incorrect_login_attempts, -account.id account_id, +account.id account_id, account.uuid account_uuid, account.account_name account_name, account.type account_type, -domain.id domain_id, +domain.id domain_id, domain.uuid domain_uuid, -domain.name domain_name, -domain.path domain_path -from user +domain.name domain_name, +domain.path domain_path, +async_job.id job_id, +async_job.uuid job_uuid, +async_job.job_status job_status, +async_job.account_id job_account_id +from user inner join account on user.account_id = account.id -inner join domain on account.domain_id=domain.id; +inner join domain on account.domain_id=domain.id +left join async_job on async_job.instance_id = user.id and async_job.instance_type = "User" and async_job.job_status = 0; + DROP VIEW IF EXISTS `cloud`.`project_view`; CREATE VIEW project_view AS @@ -727,4 +733,91 @@ left join vm_template on volumes.template_id = vm_template.id left join resource_tags on resource_tags.resource_id = volumes.id and resource_tags.resource_type = "Volume" left join async_job on async_job.instance_id = volumes.id and async_job.instance_type = "Volume" and async_job.job_status = 0; +DROP VIEW IF EXISTS `cloud`.`account_netstats_view`; +CREATE VIEW account_netstats_view AS +SELECT account_id, +sum(net_bytes_received)+ sum(current_bytes_received) as bytesReceived, +sum(net_bytes_sent)+ sum(current_bytes_sent) as bytesSent +FROM user_statistics +group by account_id; + +DROP VIEW IF EXISTS `cloud`.`account_vmstats_view`; +CREATE VIEW account_vmstats_view AS +SELECT account_id, state, count(*) as vmcount +from vm_instance +group by account_id, state; + +DROP VIEW IF EXISTS `cloud`.`free_ip_view`; +CREATE VIEW free_ip_view AS +select count(user_ip_address.id) free_ip +from user_ip_address +inner join vlan on vlan.id = user_ip_address.vlan_db_id and vlan.vlan_type = "VirtualNetwork" +where state = "Free" + +DROP VIEW IF EXISTS `cloud`.`account_view`; +CREATE VIEW account_view AS +select +account.id, +account.uuid, +account.account_name, +account.type, +account.state, +account.removed, +account.cleanup_needed, +account.network_domain, +domain.id domain_id, +domain.uuid domain_uuid, +domain.name domain_name, +domain.path domain_path, +data_center.id data_center_id, +data_center.uuid data_center_uuid, +data_center.name data_center_name, +account_netstats_view.bytesReceived, +account_netstats_view.bytesSent, +vmlimit.max vmLimit, +vmcount.count vmTotal, +runningvm.vmcount runningVms, +stoppedvm.vmcount stoppedVms, +iplimit.max ipLimit, +ipcount.count ipTotal, +free_ip_view.free_ip ipFree, +volumelimit.max volumeLimit, +volumecount.count volumeTotal, +snapshotlimit.max snapshotLimit, +snapshotcount.count snapshotTotal, +templatelimit.max templateLimit, +templatecount.count templateTotal, +vpclimit.max vpcLimit, +vpccount.count vpcTotal, +projectlimit.max projectLimit, +projectcount.count projectTotal, +networklimit.max networkLimit, +networkcount.count networkTotal, +async_job.id job_id, +async_job.uuid job_uuid, +async_job.job_status job_status, +async_job.account_id job_account_id +from free_ip_view, account +inner join domain on account.domain_id=domain.id +left join data_center on account.default_zone_id = data_center.id +left join account_netstats_view on account.id = account_netstats_view.account_id +left join resource_limit vmlimit on account.id = vmlimit.account_id and vmlimit.type = "user_vm" +left join resource_count vmcount on account.id = vmcount.account_id and vmcount.type = "user_vm" +left join account_vmstats_view runningvm on account.id = runningvm.account_id and runningvm.state = "Running" +left join account_vmstats_view stoppedvm on account.id = stoppedvm.account_id and stoppedvm.state = "Stopped" +left join resource_limit iplimit on account.id = iplimit.account_id and iplimit.type = "public_ip" +left join resource_count ipcount on account.id = ipcount.account_id and ipcount.type = "public_ip" +left join resource_limit volumelimit on account.id = volumelimit.account_id and volumelimit.type = "volume" +left join resource_count volumecount on account.id = volumecount.account_id and volumecount.type = "volume" +left join resource_limit snapshotlimit on account.id = snapshotlimit.account_id and snapshotlimit.type = "snapshot" +left join resource_count snapshotcount on account.id = snapshotcount.account_id and snapshotcount.type = "snapshot" +left join resource_limit templatelimit on account.id = templatelimit.account_id and templatelimit.type = "template" +left join resource_count templatecount on account.id = templatecount.account_id and templatecount.type = "template" +left join resource_limit vpclimit on account.id = vpclimit.account_id and vpclimit.type = "vpc" +left join resource_count vpccount on account.id = vpccount.account_id and vpccount.type = "vpc" +left join resource_limit projectlimit on account.id = projectlimit.account_id and projectlimit.type = "project" +left join resource_count projectcount on account.id = projectcount.account_id and projectcount.type = "project" +left join resource_limit networklimit on account.id = networklimit.account_id and networklimit.type = "network" +left join resource_count networkcount on account.id = networkcount.account_id and networkcount.type = "network" +left join async_job on async_job.instance_id = account.id and async_job.instance_type = "Account" and async_job.job_status = 0;
