| // 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.usage.dao; | |
| import com.cloud.usage.BucketStatisticsVO; | |
| import com.cloud.usage.UsageVO; | |
| import com.cloud.user.AccountVO; | |
| import com.cloud.user.UserStatisticsVO; | |
| import com.cloud.user.VmDiskStatisticsVO; | |
| import com.cloud.utils.DateUtil; | |
| import com.cloud.utils.Pair; | |
| import com.cloud.utils.db.Filter; | |
| import com.cloud.utils.db.GenericDaoBase; | |
| import com.cloud.utils.db.QueryBuilder; | |
| import com.cloud.utils.db.SearchBuilder; | |
| import com.cloud.utils.db.SearchCriteria; | |
| import com.cloud.utils.db.Transaction; | |
| import com.cloud.utils.db.TransactionCallback; | |
| import com.cloud.utils.db.TransactionLegacy; | |
| import com.cloud.utils.db.TransactionStatus; | |
| import com.cloud.utils.exception.CloudRuntimeException; | |
| import org.apache.cloudstack.acl.RoleType; | |
| import org.apache.commons.lang3.time.DateUtils; | |
| import org.springframework.stereotype.Component; | |
| import java.sql.PreparedStatement; | |
| import java.sql.ResultSet; | |
| import java.sql.SQLException; | |
| import java.sql.Timestamp; | |
| import java.sql.Types; | |
| import java.util.ArrayList; | |
| import java.util.Date; | |
| import java.util.List; | |
| import java.util.TimeZone; | |
| @Component | |
| public class UsageDaoImpl extends GenericDaoBase<UsageVO, Long> implements UsageDao { | |
| private static final String DELETE_ALL = "DELETE FROM cloud_usage"; | |
| private static final String DELETE_ALL_BY_ACCOUNTID = "DELETE FROM cloud_usage WHERE account_id = ?"; | |
| private static final String INSERT_ACCOUNT = "INSERT INTO cloud_usage.account (id, account_name, uuid, type, role_id, domain_id, removed, cleanup_needed) VALUES (?,?,?,?,?,?,?,?)"; | |
| private static final String INSERT_USER_STATS = "INSERT INTO cloud_usage.user_statistics (id, data_center_id, account_id, public_ip_address, device_id, device_type, network_id, net_bytes_received," | |
| + " net_bytes_sent, current_bytes_received, current_bytes_sent, agg_bytes_received, agg_bytes_sent) VALUES (?,?,?,?,?,?,?,?,?,?, ?, ?, ?)"; | |
| private static final String UPDATE_ACCOUNT = "UPDATE cloud_usage.account SET account_name=?, removed=? WHERE id=?"; | |
| private static final String UPDATE_USER_STATS = "UPDATE cloud_usage.user_statistics SET net_bytes_received=?, net_bytes_sent=?, current_bytes_received=?, current_bytes_sent=?, agg_bytes_received=?, agg_bytes_sent=? WHERE id=?"; | |
| private static final String GET_LAST_ACCOUNT = "SELECT id FROM cloud_usage.account ORDER BY id DESC LIMIT 1"; | |
| private static final String GET_LAST_USER_STATS = "SELECT id FROM cloud_usage.user_statistics ORDER BY id DESC LIMIT 1"; | |
| private static final String GET_PUBLIC_TEMPLATES_BY_ACCOUNTID = "SELECT id FROM cloud.vm_template WHERE account_id = ? AND public = '1' AND removed IS NULL"; | |
| private static final String GET_LAST_VM_DISK_STATS = "SELECT id FROM cloud_usage.vm_disk_statistics ORDER BY id DESC LIMIT 1"; | |
| private static final String INSERT_VM_DISK_STATS = "INSERT INTO cloud_usage.vm_disk_statistics (id, data_center_id, account_id, vm_id, volume_id, net_io_read, net_io_write, current_io_read, " | |
| + "current_io_write, agg_io_read, agg_io_write, net_bytes_read, net_bytes_write, current_bytes_read, current_bytes_write, agg_bytes_read, agg_bytes_write) " | |
| + " VALUES (?,?,?,?,?,?,?,?,?,?, ?, ?, ?, ?,?, ?, ?)"; | |
| private static final String UPDATE_VM_DISK_STATS = "UPDATE cloud_usage.vm_disk_statistics SET net_io_read=?, net_io_write=?, current_io_read=?, current_io_write=?, agg_io_read=?, agg_io_write=?, " | |
| + "net_bytes_read=?, net_bytes_write=?, current_bytes_read=?, current_bytes_write=?, agg_bytes_read=?, agg_bytes_write=? WHERE id=?"; | |
| private static final String INSERT_USAGE_RECORDS = "INSERT INTO cloud_usage.cloud_usage (zone_id, account_id, domain_id, description, usage_display, " | |
| + "usage_type, raw_usage, vm_instance_id, vm_name, offering_id, template_id, " | |
| + "usage_id, type, size, network_id, start_date, end_date, virtual_size) VALUES (?,?,?,?,?,?,?,?,?, ?, ?, ?,?,?,?,?,?,?)"; | |
| protected final static TimeZone s_gmtTimeZone = TimeZone.getTimeZone("GMT"); | |
| private static final String LIST_ACCOUNT_RESOURCES_IN_PERIOD = "SELECT zone.uuid as zone_uuid, domain.uuid as domain_uuid\n " | |
| + "FROM cloud_usage.cloud_usage cloud_usage\n " | |
| + "INNER JOIN cloud.data_center zone ON (zone.id = cloud_usage.zone_id)\n " | |
| + "INNER JOIN cloud.domain domain ON (domain.id = cloud_usage.domain_id)\n " | |
| + "WHERE cloud_usage.usage_type = ? AND cloud_usage.account_id = ? AND cloud_usage.start_date >= ? AND cloud_usage.end_date <= ? " | |
| + "GROUP BY cloud_usage.usage_id "; | |
| private static final String GET_LAST_BUCKET_STATS_ID = "SELECT id FROM cloud_usage.bucket_statistics ORDER BY id DESC LIMIT 1"; | |
| private static final String INSERT_BUCKET_STATS = "INSERT INTO cloud_usage.bucket_statistics (id, account_id, bucket_id, size) VALUES (?,?,?,?)"; | |
| private static final String UPDATE_BUCKET_STATS = "UPDATE cloud_usage.bucket_statistics SET size=? WHERE id=?"; | |
| protected SearchBuilder<UsageVO> endDateLessThanSearch; | |
| public UsageDaoImpl() { | |
| endDateLessThanSearch = createSearchBuilder(); | |
| endDateLessThanSearch.and("endDate", endDateLessThanSearch.entity().getEndDate(), SearchCriteria.Op.LT); | |
| endDateLessThanSearch.done(); | |
| } | |
| @Override | |
| public void deleteRecordsForAccount(Long accountId) { | |
| String sql = ((accountId == null) ? DELETE_ALL : DELETE_ALL_BY_ACCOUNTID); | |
| TransactionLegacy txn = TransactionLegacy.open(TransactionLegacy.USAGE_DB); | |
| PreparedStatement pstmt = null; | |
| try { | |
| txn.start(); | |
| pstmt = txn.prepareAutoCloseStatement(sql); | |
| if (accountId != null) { | |
| pstmt.setLong(1, accountId.longValue()); | |
| } | |
| pstmt.executeUpdate(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("Error retrieving usage Instances for Account ID: " + accountId, ex); | |
| } finally { | |
| txn.close(); | |
| } | |
| } | |
| @Override | |
| public Pair<List<UsageVO>, Integer> searchAndCountAllRecords(SearchCriteria<UsageVO> sc, Filter filter) { | |
| return listAndCountIncludingRemovedBy(sc, filter); | |
| } | |
| @Override | |
| public void saveAccounts(List<AccountVO> accounts) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = INSERT_ACCOUNT; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (AccountVO acct : accounts) { | |
| pstmt.setLong(1, acct.getId()); | |
| pstmt.setString(2, acct.getAccountName()); | |
| pstmt.setString(3, acct.getUuid()); | |
| pstmt.setInt(4, acct.getType().ordinal()); | |
| //prevent autoboxing NPE by defaulting to User role | |
| if(acct.getRoleId() == null){ | |
| pstmt.setLong(5, RoleType.User.getId()); | |
| }else{ | |
| pstmt.setLong(5, acct.getRoleId()); | |
| } | |
| pstmt.setLong(6, acct.getDomainId()); | |
| Date removed = acct.getRemoved(); | |
| if (removed == null) { | |
| pstmt.setString(7, null); | |
| } else { | |
| pstmt.setString(7, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), acct.getRemoved())); | |
| } | |
| pstmt.setBoolean(8, acct.getNeedsCleanup()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error saving account to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void updateAccounts(List<AccountVO> accounts) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = UPDATE_ACCOUNT; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (AccountVO acct : accounts) { | |
| pstmt.setString(1, acct.getAccountName()); | |
| Date removed = acct.getRemoved(); | |
| if (removed == null) { | |
| pstmt.setString(2, null); | |
| } else { | |
| pstmt.setString(2, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), acct.getRemoved())); | |
| } | |
| pstmt.setLong(3, acct.getId()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error updating account to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void saveUserStats(List<UserStatisticsVO> userStats) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = INSERT_USER_STATS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (UserStatisticsVO userStat : userStats) { | |
| pstmt.setLong(1, userStat.getId()); | |
| pstmt.setLong(2, userStat.getDataCenterId()); | |
| pstmt.setLong(3, userStat.getAccountId()); | |
| pstmt.setString(4, userStat.getPublicIpAddress()); | |
| if (userStat.getDeviceId() != null) { | |
| pstmt.setLong(5, userStat.getDeviceId()); | |
| } else { | |
| pstmt.setNull(5, Types.BIGINT); | |
| } | |
| pstmt.setString(6, userStat.getDeviceType()); | |
| if (userStat.getNetworkId() != null) { | |
| pstmt.setLong(7, userStat.getNetworkId()); | |
| } else { | |
| pstmt.setNull(7, Types.BIGINT); | |
| } | |
| pstmt.setLong(8, userStat.getNetBytesReceived()); | |
| pstmt.setLong(9, userStat.getNetBytesSent()); | |
| pstmt.setLong(10, userStat.getCurrentBytesReceived()); | |
| pstmt.setLong(11, userStat.getCurrentBytesSent()); | |
| pstmt.setLong(12, userStat.getAggBytesReceived()); | |
| pstmt.setLong(13, userStat.getAggBytesSent()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error saving user stats to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void updateUserStats(List<UserStatisticsVO> userStats) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = UPDATE_USER_STATS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (UserStatisticsVO userStat : userStats) { | |
| pstmt.setLong(1, userStat.getNetBytesReceived()); | |
| pstmt.setLong(2, userStat.getNetBytesSent()); | |
| pstmt.setLong(3, userStat.getCurrentBytesReceived()); | |
| pstmt.setLong(4, userStat.getCurrentBytesSent()); | |
| pstmt.setLong(5, userStat.getAggBytesReceived()); | |
| pstmt.setLong(6, userStat.getAggBytesSent()); | |
| pstmt.setLong(7, userStat.getId()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error updating user stats to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public Long getLastAccountId() { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| PreparedStatement pstmt = null; | |
| String sql = GET_LAST_ACCOUNT; | |
| try { | |
| pstmt = txn.prepareAutoCloseStatement(sql); | |
| ResultSet rs = pstmt.executeQuery(); | |
| if (rs.next()) { | |
| return Long.valueOf(rs.getLong(1)); | |
| } | |
| } catch (Exception ex) { | |
| logger.error("error getting last account id", ex); | |
| } | |
| return null; | |
| } | |
| @Override | |
| public Long getLastUserStatsId() { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| PreparedStatement pstmt = null; | |
| String sql = GET_LAST_USER_STATS; | |
| try { | |
| pstmt = txn.prepareAutoCloseStatement(sql); | |
| ResultSet rs = pstmt.executeQuery(); | |
| if (rs.next()) { | |
| return Long.valueOf(rs.getLong(1)); | |
| } | |
| } catch (Exception ex) { | |
| logger.error("error getting last user stats id", ex); | |
| } | |
| return null; | |
| } | |
| @Override | |
| public Long getLastBucketStatsId() { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| PreparedStatement pstmt = null; | |
| String sql = GET_LAST_BUCKET_STATS_ID; | |
| try { | |
| pstmt = txn.prepareAutoCloseStatement(sql); | |
| ResultSet rs = pstmt.executeQuery(); | |
| if (rs.next()) { | |
| return Long.valueOf(rs.getLong(1)); | |
| } | |
| } catch (Exception ex) { | |
| logger.error("error getting last bucket stats id", ex); | |
| } | |
| return null; | |
| } | |
| @Override | |
| public void saveBucketStats(List<BucketStatisticsVO> bucketStats) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = INSERT_BUCKET_STATS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (BucketStatisticsVO bucketStat : bucketStats) { | |
| pstmt.setLong(1, bucketStat.getId()); | |
| pstmt.setLong(2, bucketStat.getAccountId()); | |
| pstmt.setLong(3, bucketStat.getBucketId()); | |
| pstmt.setLong(4, bucketStat.getSize()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error saving bucket stats to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void updateBucketStats(List<BucketStatisticsVO> bucketStats) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = UPDATE_BUCKET_STATS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (BucketStatisticsVO bucketStat : bucketStats) { | |
| pstmt.setLong(1, bucketStat.getSize()); | |
| pstmt.setLong(2, bucketStat.getId()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error updating bucket stats to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public List<Long> listPublicTemplatesByAccount(long accountId) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| PreparedStatement pstmt = null; | |
| String sql = GET_PUBLIC_TEMPLATES_BY_ACCOUNTID; | |
| List<Long> templateList = new ArrayList<Long>(); | |
| try { | |
| pstmt = txn.prepareAutoCloseStatement(sql); | |
| pstmt.setLong(1, accountId); | |
| ResultSet rs = pstmt.executeQuery(); | |
| if (rs.next()) { | |
| templateList.add(Long.valueOf(rs.getLong(1))); | |
| } | |
| } catch (Exception ex) { | |
| logger.error("Error listing public Templates", ex); | |
| } | |
| return templateList; | |
| } | |
| @Override | |
| public Long getLastVmDiskStatsId() { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| PreparedStatement pstmt = null; | |
| String sql = GET_LAST_VM_DISK_STATS; | |
| try { | |
| pstmt = txn.prepareAutoCloseStatement(sql); | |
| ResultSet rs = pstmt.executeQuery(); | |
| if (rs.next()) { | |
| return Long.valueOf(rs.getLong(1)); | |
| } | |
| } catch (Exception ex) { | |
| logger.error("error getting last vm disk stats id", ex); | |
| } | |
| return null; | |
| } | |
| @Override | |
| public void updateVmDiskStats(List<VmDiskStatisticsVO> vmDiskStats) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = UPDATE_VM_DISK_STATS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (VmDiskStatisticsVO vmDiskStat : vmDiskStats) { | |
| pstmt.setLong(1, vmDiskStat.getNetIORead()); | |
| pstmt.setLong(2, vmDiskStat.getNetIOWrite()); | |
| pstmt.setLong(3, vmDiskStat.getCurrentIORead()); | |
| pstmt.setLong(4, vmDiskStat.getCurrentIOWrite()); | |
| pstmt.setLong(5, vmDiskStat.getAggIORead()); | |
| pstmt.setLong(6, vmDiskStat.getAggIOWrite()); | |
| pstmt.setLong(7, vmDiskStat.getNetBytesRead()); | |
| pstmt.setLong(8, vmDiskStat.getNetBytesWrite()); | |
| pstmt.setLong(9, vmDiskStat.getCurrentBytesRead()); | |
| pstmt.setLong(10, vmDiskStat.getCurrentBytesWrite()); | |
| pstmt.setLong(11, vmDiskStat.getAggBytesRead()); | |
| pstmt.setLong(12, vmDiskStat.getAggBytesWrite()); | |
| pstmt.setLong(13, vmDiskStat.getId()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error updating vm disk stats to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void saveVmDiskStats(List<VmDiskStatisticsVO> vmDiskStats) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = INSERT_VM_DISK_STATS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (VmDiskStatisticsVO vmDiskStat : vmDiskStats) { | |
| pstmt.setLong(1, vmDiskStat.getId()); | |
| pstmt.setLong(2, vmDiskStat.getDataCenterId()); | |
| pstmt.setLong(3, vmDiskStat.getAccountId()); | |
| if (vmDiskStat.getVmId() != null) { | |
| pstmt.setLong(4, vmDiskStat.getVmId()); | |
| } else { | |
| pstmt.setNull(4, Types.BIGINT); | |
| } | |
| if (vmDiskStat.getVolumeId() != null) { | |
| pstmt.setLong(5, vmDiskStat.getVolumeId()); | |
| } else { | |
| pstmt.setNull(5, Types.BIGINT); | |
| } | |
| pstmt.setLong(6, vmDiskStat.getNetIORead()); | |
| pstmt.setLong(7, vmDiskStat.getNetIOWrite()); | |
| pstmt.setLong(8, vmDiskStat.getCurrentIORead()); | |
| pstmt.setLong(9, vmDiskStat.getCurrentIOWrite()); | |
| pstmt.setLong(10, vmDiskStat.getAggIORead()); | |
| pstmt.setLong(11, vmDiskStat.getAggIOWrite()); | |
| pstmt.setLong(12, vmDiskStat.getNetBytesRead()); | |
| pstmt.setLong(13, vmDiskStat.getNetBytesWrite()); | |
| pstmt.setLong(14, vmDiskStat.getCurrentBytesRead()); | |
| pstmt.setLong(15, vmDiskStat.getCurrentBytesWrite()); | |
| pstmt.setLong(16, vmDiskStat.getAggBytesRead()); | |
| pstmt.setLong(17, vmDiskStat.getAggBytesWrite()); | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error saving vm disk stats to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void saveUsageRecords(List<UsageVO> usageRecords) { | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try { | |
| txn.start(); | |
| String sql = INSERT_USAGE_RECORDS; | |
| PreparedStatement pstmt = null; | |
| pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection | |
| for (UsageVO usageRecord : usageRecords) { | |
| pstmt.setLong(1, usageRecord.getZoneId()); | |
| pstmt.setLong(2, usageRecord.getAccountId()); | |
| pstmt.setLong(3, usageRecord.getDomainId()); | |
| pstmt.setString(4, usageRecord.getDescription()); | |
| pstmt.setString(5, usageRecord.getUsageDisplay()); | |
| pstmt.setInt(6, usageRecord.getUsageType()); | |
| pstmt.setDouble(7, usageRecord.getRawUsage()); | |
| if (usageRecord.getVmInstanceId() != null) { | |
| pstmt.setLong(8, usageRecord.getVmInstanceId()); | |
| } else { | |
| pstmt.setNull(8, Types.BIGINT); | |
| } | |
| pstmt.setString(9, usageRecord.getVmName()); | |
| if (usageRecord.getOfferingId() != null) { | |
| pstmt.setLong(10, usageRecord.getOfferingId()); | |
| } else { | |
| pstmt.setNull(10, Types.BIGINT); | |
| } | |
| if (usageRecord.getTemplateId() != null) { | |
| pstmt.setLong(11, usageRecord.getTemplateId()); | |
| } else { | |
| pstmt.setNull(11, Types.BIGINT); | |
| } | |
| if (usageRecord.getUsageId() != null) { | |
| pstmt.setLong(12, usageRecord.getUsageId()); | |
| } else { | |
| pstmt.setNull(12, Types.BIGINT); | |
| } | |
| pstmt.setString(13, usageRecord.getType()); | |
| if (usageRecord.getSize() != null) { | |
| pstmt.setLong(14, usageRecord.getSize()); | |
| } else { | |
| pstmt.setNull(14, Types.BIGINT); | |
| } | |
| if (usageRecord.getNetworkId() != null) { | |
| pstmt.setLong(15, usageRecord.getNetworkId()); | |
| } else { | |
| pstmt.setNull(15, Types.BIGINT); | |
| } | |
| pstmt.setString(16, DateUtil.getDateDisplayString(s_gmtTimeZone, usageRecord.getStartDate())); | |
| pstmt.setString(17, DateUtil.getDateDisplayString(s_gmtTimeZone, usageRecord.getEndDate())); | |
| if (usageRecord.getVirtualSize() != null) { | |
| pstmt.setLong(18, usageRecord.getVirtualSize()); | |
| } else { | |
| pstmt.setNull(18, Types.BIGINT); | |
| } | |
| pstmt.addBatch(); | |
| } | |
| pstmt.executeBatch(); | |
| txn.commit(); | |
| } catch (Exception ex) { | |
| txn.rollback(); | |
| logger.error("error saving usage records to cloud_usage db", ex); | |
| throw new CloudRuntimeException(ex.getMessage()); | |
| } | |
| } | |
| @Override | |
| public void expungeAllOlderThan(int days, long limitPerQuery) { | |
| SearchCriteria<UsageVO> sc = endDateLessThanSearch.create(); | |
| Date limit = DateUtils.addDays(new Date(), -days); | |
| sc.setParameters("endDate", limit); | |
| TransactionLegacy txn = TransactionLegacy.open(TransactionLegacy.USAGE_DB); | |
| try { | |
| logger.debug("Removing all cloud_usage records older than [{}].", limit); | |
| int totalExpunged = batchExpunge(sc, limitPerQuery); | |
| logger.info("Removed a total of [{}] cloud_usage records older than [{}].", totalExpunged, limit); | |
| } finally { | |
| txn.close(); | |
| } | |
| } | |
| public UsageVO persistUsage(final UsageVO usage) { | |
| return Transaction.execute(TransactionLegacy.USAGE_DB, new TransactionCallback<UsageVO>() { | |
| @Override | |
| public UsageVO doInTransaction(final TransactionStatus status) { | |
| return persist(usage); | |
| } | |
| }); | |
| } | |
| @Override | |
| public Pair<List<UsageVO>, Integer> listUsageRecordsPendingForQuotaAggregation(long accountId, long domainId) { | |
| logger.debug(String.format("Retrieving pending usage records for accountId [%s] and domainId [%s].", accountId, domainId)); | |
| return Transaction.execute(TransactionLegacy.USAGE_DB, (TransactionCallback<Pair<List<UsageVO>, Integer>>) status -> { | |
| Filter usageFilter = new Filter(UsageVO.class, "startDate", true, null, null); | |
| QueryBuilder<UsageVO> qb = QueryBuilder.create(UsageVO.class); | |
| if (accountId != -1) { | |
| qb.and(qb.entity().getAccountId(), SearchCriteria.Op.EQ, accountId); | |
| } | |
| if (domainId != -1) { | |
| qb.and(qb.entity().getDomainId(), SearchCriteria.Op.EQ, domainId); | |
| } | |
| qb.and(qb.entity().getQuotaCalculated(), SearchCriteria.Op.NEQ, 1); | |
| qb.and(qb.entity().getRawUsage(), SearchCriteria.Op.GT, 0); | |
| return searchAndCountAllRecords(qb.create(), usageFilter); | |
| }); | |
| } | |
| @Override | |
| public List<Pair<String, String>> listAccountResourcesInThePeriod(long accountId, int usageType, Date startDate, Date endDate) { | |
| String startDateString = DateUtil.getOutputString(startDate); | |
| String endDateString = DateUtil.getOutputString(endDate); | |
| logger.debug(String.format("Retrieving account resources between [%s] and [%s] for accountId [%s] and usageType [%s].", startDateString, endDateString, accountId, | |
| usageType)); | |
| TransactionLegacy txn = TransactionLegacy.currentTxn(); | |
| try (PreparedStatement pstmt = txn.prepareStatement(LIST_ACCOUNT_RESOURCES_IN_PERIOD)) { | |
| List<Pair<String, String>> accountResourcesOfTheLastDay = new ArrayList<>(); | |
| pstmt.setInt(1, usageType); | |
| pstmt.setLong(2, accountId); | |
| pstmt.setTimestamp(3, new Timestamp(startDate.getTime())); | |
| pstmt.setTimestamp(4, new Timestamp(endDate.getTime())); | |
| try (ResultSet rs = pstmt.executeQuery()) { | |
| while (rs.next()) { | |
| String zoneUuid = rs.getString("zone_uuid"); | |
| String domainUuid = rs.getString("domain_uuid"); | |
| accountResourcesOfTheLastDay.add(new Pair<>(zoneUuid, domainUuid)); | |
| } | |
| } | |
| return accountResourcesOfTheLastDay; | |
| } catch (SQLException e) { | |
| logger.error(String.format("Failed to retrieve account resources between [%s] and [%s] for accountId [%s] and usageType [%s] due to [%s]. Returning an empty list of" | |
| + " resources.", startDateString, endDateString, accountId, usageType, e.getMessage()), e); | |
| return new ArrayList<>(); | |
| } | |
| } | |
| } |