cloudstack-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From alena1...@apache.org
Subject [25/72] [abbrv] [partial] Moved most of the VOs and DAOs from server package into engine-schema as well
Date Tue, 07 May 2013 22:01:41 GMT
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/ExternalPublicIpStatisticsDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/ExternalPublicIpStatisticsDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/ExternalPublicIpStatisticsDaoImpl.java
new file mode 100644
index 0000000..bbce5f8
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/ExternalPublicIpStatisticsDaoImpl.java
@@ -0,0 +1,77 @@
+// 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 java.util.List;
+
+import javax.ejb.Local;
+
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.ExternalPublicIpStatisticsVO;
+import com.cloud.user.UserStatisticsVO;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.SearchBuilder;
+import com.cloud.utils.db.SearchCriteria;
+import com.cloud.vm.dao.DomainRouterDao;
+
+@Component
+@Local(value = { ExternalPublicIpStatisticsDao.class })
+public class ExternalPublicIpStatisticsDaoImpl extends GenericDaoBase<ExternalPublicIpStatisticsVO, Long> implements ExternalPublicIpStatisticsDao {
+
+	private final SearchBuilder<ExternalPublicIpStatisticsVO> AccountZoneSearch;
+	private final SearchBuilder<ExternalPublicIpStatisticsVO> SingleRowSearch;    
+    
+    public ExternalPublicIpStatisticsDaoImpl() {
+    	AccountZoneSearch = createSearchBuilder();
+    	AccountZoneSearch.and("accountId", AccountZoneSearch.entity().getAccountId(), SearchCriteria.Op.EQ);
+    	AccountZoneSearch.and("zoneId", AccountZoneSearch.entity().getZoneId(), SearchCriteria.Op.EQ);
+    	AccountZoneSearch.done();
+
+    	SingleRowSearch = createSearchBuilder();
+    	SingleRowSearch.and("accountId", SingleRowSearch.entity().getAccountId(), SearchCriteria.Op.EQ);
+    	SingleRowSearch.and("zoneId", SingleRowSearch.entity().getZoneId(), SearchCriteria.Op.EQ);
+    	SingleRowSearch.and("publicIp", SingleRowSearch.entity().getPublicIpAddress(), SearchCriteria.Op.EQ);
+    	SingleRowSearch.done();
+    }
+	
+	public ExternalPublicIpStatisticsVO lock(long accountId, long zoneId, String publicIpAddress) {
+		SearchCriteria<ExternalPublicIpStatisticsVO> sc = getSingleRowSc(accountId, zoneId, publicIpAddress);
+		return lockOneRandomRow(sc, true);
+	}
+
+    public ExternalPublicIpStatisticsVO findBy(long accountId, long zoneId, String publicIpAddress) {
+    	SearchCriteria<ExternalPublicIpStatisticsVO> sc = getSingleRowSc(accountId, zoneId, publicIpAddress);
+        return findOneBy(sc);
+    }
+    
+    private SearchCriteria<ExternalPublicIpStatisticsVO> getSingleRowSc(long accountId, long zoneId, String publicIpAddress) {
+    	SearchCriteria<ExternalPublicIpStatisticsVO> sc = SingleRowSearch.create();
+    	sc.setParameters("accountId", accountId);
+		sc.setParameters("zoneId", zoneId);
+		sc.setParameters("publicIp", publicIpAddress);
+		return sc;
+    }
+	
+	public List<ExternalPublicIpStatisticsVO> listBy(long accountId, long zoneId) {
+		SearchCriteria<ExternalPublicIpStatisticsVO> sc = AccountZoneSearch.create();
+        sc.setParameters("accountId", accountId);
+        sc.setParameters("zoneId", zoneId);
+        return search(sc, null);
+	}
+	
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageDao.java b/engine/schema/src/com/cloud/usage/dao/UsageDao.java
new file mode 100644
index 0000000..6d0c162
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageDao.java
@@ -0,0 +1,39 @@
+// 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 java.util.List;
+
+import com.cloud.usage.UsageVO;
+import com.cloud.user.AccountVO;
+import com.cloud.user.UserStatisticsVO;
+import com.cloud.utils.db.Filter;
+import com.cloud.utils.db.GenericDao;
+import com.cloud.utils.db.SearchCriteria;
+
+public interface UsageDao extends GenericDao<UsageVO, Long> {
+    void deleteRecordsForAccount(Long accountId);
+    List<UsageVO> searchAllRecords(SearchCriteria<UsageVO> sc, Filter filter);
+
+    void saveAccounts(List<AccountVO> accounts);
+    void updateAccounts(List<AccountVO> accounts);
+    void saveUserStats(List<UserStatisticsVO> userStats);
+    void updateUserStats(List<UserStatisticsVO> userStats);
+    Long getLastAccountId();
+    Long getLastUserStatsId();
+    List<Long> listPublicTemplatesByAccount(long accountId);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageDaoImpl.java
new file mode 100644
index 0000000..a5867f0
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageDaoImpl.java
@@ -0,0 +1,273 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.TimeZone;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageVO;
+import com.cloud.user.AccountVO;
+import com.cloud.user.UserStatisticsVO;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.db.Filter;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.SearchCriteria;
+import com.cloud.utils.db.Transaction;
+import com.cloud.utils.exception.CloudRuntimeException;
+
+@Component
+@Local(value={UsageDao.class})
+public class UsageDaoImpl extends GenericDaoBase<UsageVO, Long> implements UsageDao {
+	public static final Logger s_logger = Logger.getLogger(UsageDaoImpl.class.getName());
+	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, type, 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";
+
+    protected final static TimeZone s_gmtTimeZone = TimeZone.getTimeZone("GMT");
+
+    public UsageDaoImpl () {}
+
+	@Override
+    public void deleteRecordsForAccount(Long accountId) {
+	    String sql = ((accountId == null) ? DELETE_ALL : DELETE_ALL_BY_ACCOUNTID);
+        Transaction txn = Transaction.open(Transaction.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();
+            s_logger.error("error retrieving usage vm instances for account id: " + accountId);
+        } finally {
+            txn.close();
+        }
+	}
+
+	@Override
+	public List<UsageVO> searchAllRecords(SearchCriteria<UsageVO> sc, Filter filter) {
+	    return listIncludingRemovedBy(sc, filter);
+	}
+
+	@Override
+    public void saveAccounts(List<AccountVO> accounts) {
+	    Transaction txn = Transaction.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.setShort(3, acct.getType());
+	            pstmt.setLong(4, acct.getDomainId());
+
+	            Date removed = acct.getRemoved();
+	            if (removed == null) {
+	                pstmt.setString(5, null);
+	            } else {
+	                pstmt.setString(5, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), acct.getRemoved()));
+	            }
+
+	            pstmt.setBoolean(6, acct.getNeedsCleanup());
+
+	            pstmt.addBatch();
+	        }
+	        pstmt.executeBatch();
+	        txn.commit();
+	    } catch (Exception ex) {
+	        txn.rollback();
+	        s_logger.error("error saving account to cloud_usage db", ex);
+            throw new CloudRuntimeException(ex.getMessage());
+	    }
+	}
+
+    @Override
+    public void updateAccounts(List<AccountVO> accounts) {
+        Transaction txn = Transaction.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();
+            s_logger.error("error saving account to cloud_usage db", ex);
+            throw new CloudRuntimeException(ex.getMessage());
+        }
+    }
+
+	@Override
+    public void saveUserStats(List<UserStatisticsVO> userStats) {
+        Transaction txn = Transaction.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();
+            s_logger.error("error saving user stats to cloud_usage db", ex);
+            throw new CloudRuntimeException(ex.getMessage());
+        }
+	}
+
+    @Override
+    public void updateUserStats(List<UserStatisticsVO> userStats) {
+        Transaction txn = Transaction.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();
+            s_logger.error("error saving user stats to cloud_usage db", ex);
+            throw new CloudRuntimeException(ex.getMessage());
+        }
+    }
+
+	@Override
+    public Long getLastAccountId() {
+        Transaction txn = Transaction.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) {
+            s_logger.error("error getting last account id", ex);
+        }
+        return null;
+    }
+
+    @Override
+    public Long getLastUserStatsId() {
+        Transaction txn = Transaction.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) {
+            s_logger.error("error getting last user stats id", ex);
+        }
+        return null;
+    }
+
+    @Override
+    public List<Long> listPublicTemplatesByAccount(long accountId) {
+        Transaction txn = Transaction.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) {
+            s_logger.error("error listing public templates", ex);
+        }
+        return templateList;
+    }
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDao.java b/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDao.java
new file mode 100644
index 0000000..55f6641
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDao.java
@@ -0,0 +1,28 @@
+// 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 java.util.Date;
+import java.util.List;
+
+import com.cloud.usage.UsageIPAddressVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageIPAddressDao extends GenericDao<UsageIPAddressVO, Long> {
+	public void update(UsageIPAddressVO usage);
+	public List<UsageIPAddressVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDaoImpl.java
new file mode 100644
index 0000000..9af4a26
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageIPAddressDaoImpl.java
@@ -0,0 +1,145 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.TimeZone;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageIPAddressVO;
+import com.cloud.user.Account;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.Transaction;
+
+@Component
+@Local(value={UsageIPAddressDao.class})
+public class UsageIPAddressDaoImpl extends GenericDaoBase<UsageIPAddressVO, Long> implements UsageIPAddressDao {
+	public static final Logger s_logger = Logger.getLogger(UsageIPAddressDaoImpl.class.getName());
+
+	protected static final String UPDATE_RELEASED = "UPDATE usage_ip_address SET released = ? WHERE account_id = ? AND public_ip_address = ? and released IS NULL";
+    protected static final String GET_USAGE_RECORDS_BY_ACCOUNT = "SELECT id, account_id, domain_id, zone_id, public_ip_address, is_source_nat, is_system, assigned, released " +
+                                                                 "FROM usage_ip_address " +
+                                                                 "WHERE account_id = ? AND ((released IS NULL AND assigned <= ?) OR (assigned BETWEEN ? AND ?) OR " +
+                                                                 "      (released BETWEEN ? AND ?) OR ((assigned <= ?) AND (released >= ?)))";
+    protected static final String GET_USAGE_RECORDS_BY_DOMAIN = "SELECT id, account_id, domain_id, zone_id, public_ip_address, is_source_nat, is_system, assigned, released " +
+                                                                "FROM usage_ip_address " +
+                                                                "WHERE domain_id = ? AND ((released IS NULL AND assigned <= ?) OR (assigned BETWEEN ? AND ?) OR " +
+                                                                "      (released BETWEEN ? AND ?) OR ((assigned <= ?) AND (released >= ?)))";
+    protected static final String GET_ALL_USAGE_RECORDS = "SELECT id, account_id, domain_id, zone_id, public_ip_address, is_source_nat, is_system, assigned, released " +
+                                                          "FROM usage_ip_address " +
+                                                          "WHERE (released IS NULL AND assigned <= ?) OR (assigned BETWEEN ? AND ?) OR " +
+                                                          "      (released BETWEEN ? AND ?) OR ((assigned <= ?) AND (released >= ?))";
+
+	public UsageIPAddressDaoImpl() {}
+
+	public void update(UsageIPAddressVO usage) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			if (usage.getReleased() != null) {
+				pstmt = txn.prepareAutoCloseStatement(UPDATE_RELEASED);
+				pstmt.setString(1, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), usage.getReleased()));
+				pstmt.setLong(2, usage.getAccountId());
+				pstmt.setString(3, usage.getAddress());
+			}
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error updating usageIPAddressVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+    @Override
+	public List<UsageIPAddressVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate) {
+        List<UsageIPAddressVO> usageRecords = new ArrayList<UsageIPAddressVO>();
+
+        Long param1 = null;
+        String sql = null;
+        if (accountId != null) {
+            sql = GET_USAGE_RECORDS_BY_ACCOUNT;
+            param1 = accountId;
+        } else if (domainId != null) {
+            sql = GET_USAGE_RECORDS_BY_DOMAIN;
+            param1 = domainId;
+        } else {
+            sql = GET_ALL_USAGE_RECORDS;
+        }
+
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        PreparedStatement pstmt = null;
+
+        try {
+            int i = 1;
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            if (param1 != null) {
+                pstmt.setLong(i++, param1);
+            }
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+
+            ResultSet rs = pstmt.executeQuery();
+            while (rs.next()) {
+                //account_id, domain_id, zone_id, address, assigned, released
+                Long id = Long.valueOf(rs.getLong(1));
+                Long acctId = Long.valueOf(rs.getLong(2));
+                Long dId = Long.valueOf(rs.getLong(3));
+                Long zId = Long.valueOf(rs.getLong(4));
+                String addr = rs.getString(5);
+                Boolean isSourceNat = Boolean.valueOf(rs.getBoolean(6));
+                Boolean isSystem = Boolean.valueOf(rs.getBoolean(7));
+                Date assignedDate = null;
+                Date releasedDate = null;
+                String assignedTS = rs.getString(8);
+                String releasedTS = rs.getString(9);
+
+                if (assignedTS != null) {
+                    assignedDate = DateUtil.parseDateString(s_gmtTimeZone, assignedTS);
+                }
+                if (releasedTS != null) {
+                    releasedDate = DateUtil.parseDateString(s_gmtTimeZone, releasedTS);
+                }
+
+                usageRecords.add(new UsageIPAddressVO(id, acctId, dId, zId, addr, isSourceNat, isSystem, assignedDate, releasedDate));
+            }
+        } catch (Exception e) {
+            txn.rollback();
+            s_logger.warn("Error getting usage records", e);
+        } finally {
+            txn.close();
+        }
+
+        return usageRecords;
+	}
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageJobDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageJobDao.java b/engine/schema/src/com/cloud/usage/dao/UsageJobDao.java
new file mode 100644
index 0000000..9ec3918
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageJobDao.java
@@ -0,0 +1,34 @@
+// 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 java.util.Date;
+
+import com.cloud.usage.UsageJobVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageJobDao extends GenericDao<UsageJobVO, Long> {
+    Long checkHeartbeat(String hostname, int pid, int aggregationDuration);
+    void createNewJob(String hostname, int pid, int jobType);
+    UsageJobVO getLastJob();
+    UsageJobVO getNextImmediateJob();
+    long getLastJobSuccessDateMillis();
+    Date getLastHeartbeat();
+    UsageJobVO isOwner(String hostname, int pid);
+
+    void updateJobSuccess(Long jobId, long startMillis, long endMillis, long execTime, boolean success);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageJobDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageJobDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageJobDaoImpl.java
new file mode 100644
index 0000000..783300f
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageJobDaoImpl.java
@@ -0,0 +1,200 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Date;
+import java.util.List;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageJobVO;
+import com.cloud.utils.db.Filter;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.SearchCriteria;
+import com.cloud.utils.db.Transaction;
+import com.cloud.utils.exception.CloudRuntimeException;
+
+@Component
+@Local(value={UsageJobDao.class})
+public class UsageJobDaoImpl extends GenericDaoBase<UsageJobVO, Long> implements UsageJobDao {
+    private static final Logger s_logger = Logger.getLogger(UsageJobDaoImpl.class.getName());
+
+    private static final String GET_LAST_JOB_SUCCESS_DATE_MILLIS = "SELECT end_millis FROM cloud_usage.usage_job WHERE end_millis > 0 and success = 1 ORDER BY end_millis DESC LIMIT 1";
+
+    @Override
+    public long getLastJobSuccessDateMillis() {
+        Transaction txn = Transaction.currentTxn();
+        PreparedStatement pstmt = null;
+        String sql = GET_LAST_JOB_SUCCESS_DATE_MILLIS;
+        try {
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            ResultSet rs = pstmt.executeQuery();
+            if (rs.next()) {
+                return rs.getLong(1);
+            }
+        } catch (Exception ex) {
+            s_logger.error("error getting last usage job success date", ex);
+        } finally {
+            txn.close();
+        }
+        return 0L;
+    }
+
+    @Override
+    public void updateJobSuccess(Long jobId, long startMillis, long endMillis, long execTime, boolean success) {
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        try {
+            txn.start();
+
+            UsageJobVO job = lockRow(jobId, Boolean.TRUE);
+            UsageJobVO jobForUpdate = createForUpdate();
+            jobForUpdate.setStartMillis(startMillis);
+            jobForUpdate.setEndMillis(endMillis);
+            jobForUpdate.setExecTime(execTime);
+            jobForUpdate.setStartDate(new Date(startMillis));
+            jobForUpdate.setEndDate(new Date(endMillis));
+            jobForUpdate.setSuccess(success);
+            update(job.getId(), jobForUpdate);
+
+            txn.commit();
+        } catch (Exception ex) {
+            txn.rollback();
+            s_logger.error("error updating job success date", ex);
+            throw new CloudRuntimeException(ex.getMessage());
+        } finally {
+            txn.close();
+        }
+    }
+
+    @Override
+    public Long checkHeartbeat(String hostname, int pid, int aggregationDuration) {
+        UsageJobVO job = getNextRecurringJob();
+        if (job == null) {
+            return null;
+        }
+
+        if (job.getHost().equals(hostname) && (job.getPid() != null) && (job.getPid().intValue() == pid)) {
+            return job.getId();
+        }
+
+        Date lastHeartbeat = job.getHeartbeat();
+        if (lastHeartbeat == null) {
+            return null;
+        }
+
+        long sinceLastHeartbeat = System.currentTimeMillis() - lastHeartbeat.getTime();
+
+        // TODO:  Make this check a little smarter..but in the mean time we want the mgmt
+        //        server to monitor the usage server, we need to make sure other usage
+        //        servers take over as the usage job owner more aggressively.  For now
+        //        this is hardcoded to 5 minutes.
+        if (sinceLastHeartbeat > (5 * 60 * 1000)) {
+            return job.getId();
+        }
+        return null;
+    }
+
+    @Override
+    public UsageJobVO isOwner(String hostname, int pid) {
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        try {
+            if ((hostname == null) || (pid <= 0)) {
+                return null;
+            }
+
+            UsageJobVO job = getLastJob();
+            if (job == null) {
+                return null;
+            }
+
+            if (hostname.equals(job.getHost()) && (job.getPid() != null) && (pid == job.getPid().intValue())) {
+                return job;
+            }
+        } finally {
+            txn.close();
+        }
+        return null;
+    }
+
+    @Override
+    public void createNewJob(String hostname, int pid, int jobType) {
+        UsageJobVO newJob = new UsageJobVO();
+        newJob.setHost(hostname);
+        newJob.setPid(pid);
+        newJob.setHeartbeat(new Date());
+        newJob.setJobType(jobType);
+        persist(newJob);
+    }
+
+    @Override
+    public UsageJobVO getLastJob() {
+        Filter filter = new Filter(UsageJobVO.class, "id", false, Long.valueOf(0), Long.valueOf(1));
+        SearchCriteria<UsageJobVO> sc = createSearchCriteria();
+        sc.addAnd("endMillis", SearchCriteria.Op.EQ, Long.valueOf(0));
+        List<UsageJobVO> jobs = search(sc, filter);
+
+        if ((jobs == null) || jobs.isEmpty()) {
+            return null;
+        }
+        return jobs.get(0);
+    }
+
+    private UsageJobVO getNextRecurringJob() {
+        Filter filter = new Filter(UsageJobVO.class, "id", false, Long.valueOf(0), Long.valueOf(1));
+        SearchCriteria<UsageJobVO> sc = createSearchCriteria();
+        sc.addAnd("endMillis", SearchCriteria.Op.EQ, Long.valueOf(0));
+        sc.addAnd("jobType", SearchCriteria.Op.EQ, Integer.valueOf(UsageJobVO.JOB_TYPE_RECURRING));
+        List<UsageJobVO> jobs = search(sc, filter);
+
+        if ((jobs == null) || jobs.isEmpty()) {
+            return null;
+        }
+        return jobs.get(0);
+    }
+
+    @Override
+    public UsageJobVO getNextImmediateJob() {
+        Filter filter = new Filter(UsageJobVO.class, "id", false, Long.valueOf(0), Long.valueOf(1));
+        SearchCriteria<UsageJobVO> sc = createSearchCriteria();
+        sc.addAnd("endMillis", SearchCriteria.Op.EQ, Long.valueOf(0));
+        sc.addAnd("jobType", SearchCriteria.Op.EQ, Integer.valueOf(UsageJobVO.JOB_TYPE_SINGLE));
+        sc.addAnd("scheduled", SearchCriteria.Op.EQ, Integer.valueOf(0));
+        List<UsageJobVO> jobs = search(sc, filter);
+
+        if ((jobs == null) || jobs.isEmpty()) {
+            return null;
+        }
+        return jobs.get(0);
+    }
+
+    @Override
+    public Date getLastHeartbeat() {
+        Filter filter = new Filter(UsageJobVO.class, "heartbeat", false, Long.valueOf(0), Long.valueOf(1));
+        SearchCriteria<UsageJobVO> sc = createSearchCriteria();
+        List<UsageJobVO> jobs = search(sc, filter);
+
+        if ((jobs == null) || jobs.isEmpty()) {
+            return null;
+        }
+        return jobs.get(0).getHeartbeat();
+    }
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDao.java b/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDao.java
new file mode 100644
index 0000000..45f6a7e
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDao.java
@@ -0,0 +1,29 @@
+// 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 java.util.Date;
+import java.util.List;
+
+import com.cloud.usage.UsageLoadBalancerPolicyVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageLoadBalancerPolicyDao extends GenericDao<UsageLoadBalancerPolicyVO, Long> {
+	public void removeBy(long userId, long id);
+	public void update(UsageLoadBalancerPolicyVO usage);
+	public List<UsageLoadBalancerPolicyVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDaoImpl.java
new file mode 100644
index 0000000..fa63223
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageLoadBalancerPolicyDaoImpl.java
@@ -0,0 +1,169 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.TimeZone;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageLoadBalancerPolicyVO;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.Transaction;
+
+@Component
+@Local(value={UsageLoadBalancerPolicyDao.class})
+public class UsageLoadBalancerPolicyDaoImpl extends GenericDaoBase<UsageLoadBalancerPolicyVO, Long> implements UsageLoadBalancerPolicyDao {
+	public static final Logger s_logger = Logger.getLogger(UsageLoadBalancerPolicyDaoImpl.class.getName());
+
+	protected static final String REMOVE_BY_USERID_LBID = "DELETE FROM usage_load_balancer_policy WHERE account_id = ? AND id = ?";
+	protected static final String UPDATE_DELETED = "UPDATE usage_load_balancer_policy SET deleted = ? WHERE account_id = ? AND id = ? and deleted IS NULL";
+    protected static final String GET_USAGE_RECORDS_BY_ACCOUNT = "SELECT id, zone_id, account_id, domain_id, created, deleted " +
+                                                                 "FROM usage_load_balancer_policy " +
+                                                                 "WHERE account_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                 "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_USAGE_RECORDS_BY_DOMAIN = "SELECT id, zone_id, account_id, domain_id, created, deleted " +
+                                                                "FROM usage_load_balancer_policy " +
+                                                                "WHERE domain_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_ALL_USAGE_RECORDS = "SELECT id, zone_id, account_id, domain_id, created, deleted " +
+                                                          "FROM usage_load_balancer_policy " +
+                                                          "WHERE (deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                          "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?))";
+
+	public UsageLoadBalancerPolicyDaoImpl() {}
+
+	public void removeBy(long accountId, long lbId) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			String sql = REMOVE_BY_USERID_LBID;
+			pstmt = txn.prepareAutoCloseStatement(sql);
+			pstmt.setLong(1, accountId);
+			pstmt.setLong(2, lbId);
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error removing UsageLoadBalancerPolicyVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+	public void update(UsageLoadBalancerPolicyVO usage) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			if (usage.getDeleted() != null) {
+				pstmt = txn.prepareAutoCloseStatement(UPDATE_DELETED);
+				pstmt.setString(1, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), usage.getDeleted()));
+				pstmt.setLong(2, usage.getAccountId());
+				pstmt.setLong(3, usage.getId());
+			}
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error updating UsageLoadBalancerPolicyVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+    @Override
+	public List<UsageLoadBalancerPolicyVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page) {
+        List<UsageLoadBalancerPolicyVO> usageRecords = new ArrayList<UsageLoadBalancerPolicyVO>();
+
+        Long param1 = null;
+        String sql = null;
+        if (accountId != null) {
+            sql = GET_USAGE_RECORDS_BY_ACCOUNT;
+            param1 = accountId;
+        } else if (domainId != null) {
+            sql = GET_USAGE_RECORDS_BY_DOMAIN;
+            param1 = domainId;
+        } else {
+            sql = GET_ALL_USAGE_RECORDS;
+        }
+
+        if (limit) {
+            int startIndex = 0;
+            if (page > 0) {
+                startIndex = 500 * (page-1);
+            }
+            sql += " LIMIT " + startIndex + ",500";
+        }
+
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        PreparedStatement pstmt = null;
+
+        try {
+            int i = 1;
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            if (param1 != null) {
+                pstmt.setLong(i++, param1);
+            }
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+
+            ResultSet rs = pstmt.executeQuery();
+            while (rs.next()) {
+                //id, zone_id, account_id, domain_id, created, deleted
+            	Long lbId = Long.valueOf(rs.getLong(1));
+            	Long zoneId = Long.valueOf(rs.getLong(2));
+                Long acctId = Long.valueOf(rs.getLong(3));
+                Long dId = Long.valueOf(rs.getLong(4));
+                Date createdDate = null;
+                Date deletedDate = null;
+                String createdTS = rs.getString(5);
+                String deletedTS = rs.getString(6);
+                
+
+                if (createdTS != null) {
+                	createdDate = DateUtil.parseDateString(s_gmtTimeZone, createdTS);
+                }
+                if (deletedTS != null) {
+                	deletedDate = DateUtil.parseDateString(s_gmtTimeZone, deletedTS);
+                }
+
+                usageRecords.add(new UsageLoadBalancerPolicyVO(lbId, zoneId, acctId, dId, createdDate, deletedDate));
+            }
+        } catch (Exception e) {
+            txn.rollback();
+            s_logger.warn("Error getting usage records", e);
+        } finally {
+            txn.close();
+        }
+
+        return usageRecords;
+	}
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageNetworkDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageNetworkDao.java b/engine/schema/src/com/cloud/usage/dao/UsageNetworkDao.java
new file mode 100644
index 0000000..0f7c771
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageNetworkDao.java
@@ -0,0 +1,27 @@
+// 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 java.util.Map;
+
+import com.cloud.usage.UsageNetworkVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageNetworkDao extends GenericDao<UsageNetworkVO, Long> {
+    Map<String, UsageNetworkVO> getRecentNetworkStats();
+    void deleteOldStats(long maxEventTime);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageNetworkDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageNetworkDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageNetworkDaoImpl.java
new file mode 100644
index 0000000..d64fd80
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageNetworkDaoImpl.java
@@ -0,0 +1,98 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.HashMap;
+import java.util.Map;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageNetworkVO;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.Transaction;
+
+@Component
+@Local(value={UsageNetworkDao.class})
+public class UsageNetworkDaoImpl extends GenericDaoBase<UsageNetworkVO, Long> implements UsageNetworkDao {
+	private static final Logger s_logger = Logger.getLogger(UsageVMInstanceDaoImpl.class.getName());
+	private static final String SELECT_LATEST_STATS = "SELECT u.account_id, u.zone_id, u.host_id, u.host_type, u.network_id, u.bytes_sent, u.bytes_received, u.agg_bytes_received, u.agg_bytes_sent, u.event_time_millis " +
+	                                                    "FROM cloud_usage.usage_network u INNER JOIN (SELECT netusage.account_id as acct_id, netusage.zone_id as z_id, max(netusage.event_time_millis) as max_date " +
+	                                                                                                 "FROM cloud_usage.usage_network netusage " +
+	                                                                                                 "GROUP BY netusage.account_id, netusage.zone_id " +
+	                                                                                                 ") joinnet on u.account_id = joinnet.acct_id and u.zone_id = joinnet.z_id and u.event_time_millis = joinnet.max_date";
+	private static final String DELETE_OLD_STATS = "DELETE FROM cloud_usage.usage_network WHERE event_time_millis < ?";
+
+	public UsageNetworkDaoImpl() {
+	}
+
+	@Override
+	public Map<String, UsageNetworkVO> getRecentNetworkStats() {
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        String sql = SELECT_LATEST_STATS;
+        PreparedStatement pstmt = null;
+        try {
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            ResultSet rs = pstmt.executeQuery();
+            Map<String, UsageNetworkVO> returnMap = new HashMap<String, UsageNetworkVO>();
+            while (rs.next()) {
+                long accountId = rs.getLong(1);
+                long zoneId = rs.getLong(2);
+                Long hostId = rs.getLong(3);
+                String hostType = rs.getString(4);
+                Long networkId = rs.getLong(5);
+                long bytesSent = rs.getLong(6);
+                long bytesReceived = rs.getLong(7);
+                long aggBytesReceived = rs.getLong(8);
+                long aggBytesSent = rs.getLong(9);
+                long eventTimeMillis = rs.getLong(10);
+                if(hostId != 0){
+                    returnMap.put(zoneId + "-" + accountId+ "-Host-" + hostId, new UsageNetworkVO(accountId, zoneId, hostId, hostType, networkId, bytesSent, bytesReceived, aggBytesReceived, aggBytesSent, eventTimeMillis));
+                } else {
+                    returnMap.put(zoneId + "-" + accountId, new UsageNetworkVO(accountId, zoneId, hostId, hostType, networkId, bytesSent, bytesReceived, aggBytesReceived, aggBytesSent, eventTimeMillis));
+                }
+            }
+            return returnMap;
+        } catch (Exception ex) {
+            s_logger.error("error getting recent usage network stats", ex);
+        } finally {
+            txn.close();
+        }
+        return null;
+	}
+
+    @Override
+	public void deleteOldStats(long maxEventTime) {
+        Transaction txn = Transaction.currentTxn();
+        String sql = DELETE_OLD_STATS;
+        PreparedStatement pstmt = null;
+        try {
+            txn.start();
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            pstmt.setLong(1, maxEventTime);
+            pstmt.executeUpdate();
+            txn.commit();
+        } catch (Exception ex) {
+            txn.rollback();
+            s_logger.error("error deleting old usage network stats", ex);
+        }
+	}
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDao.java b/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDao.java
new file mode 100644
index 0000000..ca9f963
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDao.java
@@ -0,0 +1,28 @@
+// 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 java.util.Date;
+import java.util.List;
+
+import com.cloud.usage.UsageNetworkOfferingVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageNetworkOfferingDao extends GenericDao<UsageNetworkOfferingVO, Long> {
+	public void update(UsageNetworkOfferingVO usage);
+	public List<UsageNetworkOfferingVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDaoImpl.java
new file mode 100644
index 0000000..c3fc5a6
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageNetworkOfferingDaoImpl.java
@@ -0,0 +1,153 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.TimeZone;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageNetworkOfferingVO;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.Transaction;
+
+@Component
+@Local(value={UsageNetworkOfferingDao.class})
+public class UsageNetworkOfferingDaoImpl extends GenericDaoBase<UsageNetworkOfferingVO, Long> implements UsageNetworkOfferingDao {
+	public static final Logger s_logger = Logger.getLogger(UsageNetworkOfferingDaoImpl.class.getName());
+
+	protected static final String UPDATE_DELETED = "UPDATE usage_network_offering SET deleted = ? WHERE account_id = ? AND vm_instance_id = ? AND network_offering_id = ? and deleted IS NULL";
+    protected static final String GET_USAGE_RECORDS_BY_ACCOUNT = "SELECT zone_id, account_id, domain_id, vm_instance_id, network_offering_id, nic_id, is_default, created, deleted " +
+                                                                 "FROM usage_network_offering " +
+                                                                 "WHERE account_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                 "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_USAGE_RECORDS_BY_DOMAIN = "SELECT zone_id, account_id, domain_id, vm_instance_id, network_offering_id, nic_id, is_default, created, deleted " +
+                                                                "FROM usage_network_offering " +
+                                                                "WHERE domain_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_ALL_USAGE_RECORDS = "SELECT zone_id, account_id, domain_id, vm_instance_id, network_offering_id, nic_id, is_default, created, deleted " +
+                                                          "FROM usage_network_offering " +
+                                                          "WHERE (deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                          "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?))";
+
+	public UsageNetworkOfferingDaoImpl() {}
+
+	public void update(UsageNetworkOfferingVO usage) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			if (usage.getDeleted() != null) {
+				pstmt = txn.prepareAutoCloseStatement(UPDATE_DELETED);
+				pstmt.setString(1, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), usage.getDeleted()));
+				pstmt.setLong(2, usage.getAccountId());
+				pstmt.setLong(3, usage.getVmInstanceId());
+				pstmt.setLong(4, usage.getNetworkOfferingId());
+			}
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error updating UsageNetworkOfferingVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+    @Override
+	public List<UsageNetworkOfferingVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page) {
+        List<UsageNetworkOfferingVO> usageRecords = new ArrayList<UsageNetworkOfferingVO>();
+
+        Long param1 = null;
+        String sql = null;
+        if (accountId != null) {
+            sql = GET_USAGE_RECORDS_BY_ACCOUNT;
+            param1 = accountId;
+        } else if (domainId != null) {
+            sql = GET_USAGE_RECORDS_BY_DOMAIN;
+            param1 = domainId;
+        } else {
+            sql = GET_ALL_USAGE_RECORDS;
+        }
+
+        if (limit) {
+            int startIndex = 0;
+            if (page > 0) {
+                startIndex = 500 * (page-1);
+            }
+            sql += " LIMIT " + startIndex + ",500";
+        }
+
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        PreparedStatement pstmt = null;
+
+        try {
+            int i = 1;
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            if (param1 != null) {
+                pstmt.setLong(i++, param1);
+            }
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+
+            ResultSet rs = pstmt.executeQuery();
+            while (rs.next()) {
+                //zoneId, account_id, domain_id, vm_instance_id, network_offering_id, is_default, created, deleted
+            	Long zoneId = Long.valueOf(rs.getLong(1));
+                Long acctId = Long.valueOf(rs.getLong(2));
+                Long dId = Long.valueOf(rs.getLong(3));
+                long vmId = Long.valueOf(rs.getLong(4));
+                long noId = Long.valueOf(rs.getLong(5));
+                long nicId = Long.valueOf(rs.getLong(6));
+                boolean isDefault = Boolean.valueOf(rs.getBoolean(6));
+                Date createdDate = null;
+                Date deletedDate = null;
+                String createdTS = rs.getString(7);
+                String deletedTS = rs.getString(8);
+                
+
+                if (createdTS != null) {
+                	createdDate = DateUtil.parseDateString(s_gmtTimeZone, createdTS);
+                }
+                if (deletedTS != null) {
+                	deletedDate = DateUtil.parseDateString(s_gmtTimeZone, deletedTS);
+                }
+
+                usageRecords.add(new UsageNetworkOfferingVO(zoneId, acctId, dId, vmId, noId, nicId, isDefault, createdDate, deletedDate));
+            }
+        } catch (Exception e) {
+            txn.rollback();
+            s_logger.warn("Error getting usage records", e);
+        } finally {
+            txn.close();
+        }
+
+        return usageRecords;
+	}
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDao.java b/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDao.java
new file mode 100644
index 0000000..778aea3
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDao.java
@@ -0,0 +1,29 @@
+// 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 java.util.Date;
+import java.util.List;
+
+import com.cloud.usage.UsagePortForwardingRuleVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsagePortForwardingRuleDao extends GenericDao<UsagePortForwardingRuleVO, Long> {
+	public void removeBy(long userId, long id);
+	public void update(UsagePortForwardingRuleVO usage);
+	public List<UsagePortForwardingRuleVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDaoImpl.java
new file mode 100644
index 0000000..2afc50a
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsagePortForwardingRuleDaoImpl.java
@@ -0,0 +1,169 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.TimeZone;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsagePortForwardingRuleVO;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.Transaction;
+
+@Component
+@Local(value={UsagePortForwardingRuleDao.class})
+public class UsagePortForwardingRuleDaoImpl extends GenericDaoBase<UsagePortForwardingRuleVO, Long> implements UsagePortForwardingRuleDao {
+	public static final Logger s_logger = Logger.getLogger(UsagePortForwardingRuleDaoImpl.class.getName());
+
+	protected static final String REMOVE_BY_USERID_PFID = "DELETE FROM usage_port_forwarding WHERE account_id = ? AND id = ?";
+	protected static final String UPDATE_DELETED = "UPDATE usage_port_forwarding SET deleted = ? WHERE account_id = ? AND id = ? and deleted IS NULL";
+    protected static final String GET_USAGE_RECORDS_BY_ACCOUNT = "SELECT id, zone_id, account_id, domain_id, created, deleted " +
+                                                                 "FROM usage_port_forwarding " +
+                                                                 "WHERE account_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                 "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_USAGE_RECORDS_BY_DOMAIN = "SELECT id, zone_id, account_id, domain_id, created, deleted " +
+                                                                "FROM usage_port_forwarding " +
+                                                                "WHERE domain_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_ALL_USAGE_RECORDS = "SELECT id, zone_id, account_id, domain_id, created, deleted " +
+                                                          "FROM usage_port_forwarding " +
+                                                          "WHERE (deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                          "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?))";
+
+	public UsagePortForwardingRuleDaoImpl() {}
+
+	public void removeBy(long accountId, long pfId) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			String sql = REMOVE_BY_USERID_PFID;
+			pstmt = txn.prepareAutoCloseStatement(sql);
+			pstmt.setLong(1, accountId);
+			pstmt.setLong(2, pfId);
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error removing UsagePortForwardingRuleVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+	public void update(UsagePortForwardingRuleVO usage) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			if (usage.getDeleted() != null) {
+				pstmt = txn.prepareAutoCloseStatement(UPDATE_DELETED);
+				pstmt.setString(1, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), usage.getDeleted()));
+				pstmt.setLong(2, usage.getAccountId());
+				pstmt.setLong(3, usage.getId());
+			}
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error updating UsagePortForwardingRuleVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+    @Override
+	public List<UsagePortForwardingRuleVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page) {
+        List<UsagePortForwardingRuleVO> usageRecords = new ArrayList<UsagePortForwardingRuleVO>();
+
+        Long param1 = null;
+        String sql = null;
+        if (accountId != null) {
+            sql = GET_USAGE_RECORDS_BY_ACCOUNT;
+            param1 = accountId;
+        } else if (domainId != null) {
+            sql = GET_USAGE_RECORDS_BY_DOMAIN;
+            param1 = domainId;
+        } else {
+            sql = GET_ALL_USAGE_RECORDS;
+        }
+
+        if (limit) {
+            int startIndex = 0;
+            if (page > 0) {
+                startIndex = 500 * (page-1);
+            }
+            sql += " LIMIT " + startIndex + ",500";
+        }
+
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        PreparedStatement pstmt = null;
+
+        try {
+            int i = 1;
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            if (param1 != null) {
+                pstmt.setLong(i++, param1);
+            }
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+
+            ResultSet rs = pstmt.executeQuery();
+            while (rs.next()) {
+                //id, zone_id, account_id, domain_id, created, deleted
+            	Long pfId = Long.valueOf(rs.getLong(1));
+            	Long zoneId = Long.valueOf(rs.getLong(2));
+                Long acctId = Long.valueOf(rs.getLong(3));
+                Long dId = Long.valueOf(rs.getLong(4));
+                Date createdDate = null;
+                Date deletedDate = null;
+                String createdTS = rs.getString(5);
+                String deletedTS = rs.getString(6);
+                
+
+                if (createdTS != null) {
+                	createdDate = DateUtil.parseDateString(s_gmtTimeZone, createdTS);
+                }
+                if (deletedTS != null) {
+                	deletedDate = DateUtil.parseDateString(s_gmtTimeZone, deletedTS);
+                }
+
+                usageRecords.add(new UsagePortForwardingRuleVO(pfId, zoneId, acctId, dId, createdDate, deletedDate));
+            }
+        } catch (Exception e) {
+            txn.rollback();
+            s_logger.warn("Error getting usage records", e);
+        } finally {
+            txn.close();
+        }
+
+        return usageRecords;
+	}
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDao.java b/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDao.java
new file mode 100644
index 0000000..d48ab94
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDao.java
@@ -0,0 +1,28 @@
+// 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 java.util.Date;
+import java.util.List;
+
+import com.cloud.usage.UsageSecurityGroupVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageSecurityGroupDao extends GenericDao<UsageSecurityGroupVO, Long> {
+	public void update(UsageSecurityGroupVO usage);
+	public List<UsageSecurityGroupVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page);
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDaoImpl.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDaoImpl.java b/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDaoImpl.java
new file mode 100644
index 0000000..7564bf0
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageSecurityGroupDaoImpl.java
@@ -0,0 +1,151 @@
+// 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 java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.TimeZone;
+
+import javax.ejb.Local;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Component;
+
+import com.cloud.usage.UsageSecurityGroupVO;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.db.GenericDaoBase;
+import com.cloud.utils.db.Transaction;
+
+@Component
+@Local(value={UsageSecurityGroupDao.class})
+public class UsageSecurityGroupDaoImpl extends GenericDaoBase<UsageSecurityGroupVO, Long> implements UsageSecurityGroupDao {
+	public static final Logger s_logger = Logger.getLogger(UsageSecurityGroupDaoImpl.class.getName());
+
+	protected static final String UPDATE_DELETED = "UPDATE usage_security_group SET deleted = ? WHERE account_id = ? AND vm_instance_id = ? AND security_group_id = ? and deleted IS NULL";
+    protected static final String GET_USAGE_RECORDS_BY_ACCOUNT = "SELECT zone_id, account_id, domain_id, vm_instance_id, security_group_id, created, deleted " +
+                                                                 "FROM usage_security_group " +
+                                                                 "WHERE account_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                 "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_USAGE_RECORDS_BY_DOMAIN = "SELECT zone_id, account_id, domain_id, vm_instance_id, security_group_id, created, deleted " +
+                                                                "FROM usage_security_group " +
+                                                                "WHERE domain_id = ? AND ((deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                                "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?)))";
+    protected static final String GET_ALL_USAGE_RECORDS = "SELECT zone_id, account_id, domain_id, vm_instance_id, security_group_id, created, deleted " +
+                                                          "FROM usage_security_group " +
+                                                          "WHERE (deleted IS NULL) OR (created BETWEEN ? AND ?) OR " +
+                                                          "      (deleted BETWEEN ? AND ?) OR ((created <= ?) AND (deleted >= ?))";
+
+	public UsageSecurityGroupDaoImpl() {}
+
+	public void update(UsageSecurityGroupVO usage) {
+	    Transaction txn = Transaction.open(Transaction.USAGE_DB);
+		PreparedStatement pstmt = null;
+		try {
+		    txn.start();
+			if (usage.getDeleted() != null) {
+				pstmt = txn.prepareAutoCloseStatement(UPDATE_DELETED);
+				pstmt.setString(1, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), usage.getDeleted()));
+				pstmt.setLong(2, usage.getAccountId());
+				pstmt.setLong(3, usage.getVmInstanceId());
+				pstmt.setLong(4, usage.getSecurityGroupId());
+			}
+			pstmt.executeUpdate();
+			txn.commit();
+		} catch (Exception e) {
+			txn.rollback();
+			s_logger.warn("Error updating UsageSecurityGroupVO", e);
+		} finally {
+		    txn.close();
+		}
+	}
+
+    @Override
+	public List<UsageSecurityGroupVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page) {
+        List<UsageSecurityGroupVO> usageRecords = new ArrayList<UsageSecurityGroupVO>();
+
+        Long param1 = null;
+        String sql = null;
+        if (accountId != null) {
+            sql = GET_USAGE_RECORDS_BY_ACCOUNT;
+            param1 = accountId;
+        } else if (domainId != null) {
+            sql = GET_USAGE_RECORDS_BY_DOMAIN;
+            param1 = domainId;
+        } else {
+            sql = GET_ALL_USAGE_RECORDS;
+        }
+
+        if (limit) {
+            int startIndex = 0;
+            if (page > 0) {
+                startIndex = 500 * (page-1);
+            }
+            sql += " LIMIT " + startIndex + ",500";
+        }
+
+        Transaction txn = Transaction.open(Transaction.USAGE_DB);
+        PreparedStatement pstmt = null;
+
+        try {
+            int i = 1;
+            pstmt = txn.prepareAutoCloseStatement(sql);
+            if (param1 != null) {
+                pstmt.setLong(i++, param1);
+            }
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), startDate));
+            pstmt.setString(i++, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), endDate));
+
+            ResultSet rs = pstmt.executeQuery();
+            while (rs.next()) {
+                //zoneId, account_id, domain_id, vm_instance_id, security_group_id, created, deleted
+            	Long zoneId = Long.valueOf(rs.getLong(1));
+                Long acctId = Long.valueOf(rs.getLong(2));
+                Long dId = Long.valueOf(rs.getLong(3));
+                long vmId = Long.valueOf(rs.getLong(4));
+                long sgId = Long.valueOf(rs.getLong(5));
+                Date createdDate = null;
+                Date deletedDate = null;
+                String createdTS = rs.getString(6);
+                String deletedTS = rs.getString(7);
+                
+
+                if (createdTS != null) {
+                	createdDate = DateUtil.parseDateString(s_gmtTimeZone, createdTS);
+                }
+                if (deletedTS != null) {
+                	deletedDate = DateUtil.parseDateString(s_gmtTimeZone, deletedTS);
+                }
+
+                usageRecords.add(new UsageSecurityGroupVO(zoneId, acctId, dId, vmId, sgId, createdDate, deletedDate));
+            }
+        } catch (Exception e) {
+            txn.rollback();
+            s_logger.warn("Error getting usage records", e);
+        } finally {
+            txn.close();
+        }
+
+        return usageRecords;
+	}
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/d015fb35/engine/schema/src/com/cloud/usage/dao/UsageStorageDao.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/usage/dao/UsageStorageDao.java b/engine/schema/src/com/cloud/usage/dao/UsageStorageDao.java
new file mode 100644
index 0000000..30f5c4d
--- /dev/null
+++ b/engine/schema/src/com/cloud/usage/dao/UsageStorageDao.java
@@ -0,0 +1,31 @@
+// 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 java.util.Date;
+import java.util.List;
+
+import com.cloud.usage.UsageStorageVO;
+import com.cloud.utils.db.GenericDao;
+
+public interface UsageStorageDao extends GenericDao<UsageStorageVO, Long> {
+	public void removeBy(long userId, long id, int storage_type);
+	public void update(UsageStorageVO usage);
+	public List<UsageStorageVO> getUsageRecords(Long accountId, Long domainId, Date startDate, Date endDate, boolean limit, int page);
+    List<UsageStorageVO> listById(long accountId, long id, int type);
+    List<UsageStorageVO> listByIdAndZone(long accountId, long id, int type, long dcId);
+}


Mime
View raw message