package org.jivesoftware.openfire.plugin.gojara.database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;
import org.apache.log4j.Logger;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.JiveGlobals;
/**
* @author Holger Bergunde
* @author Axel-Frederik Brand
*
* This class is used to store logs in the database. A log entry is representated by {@link LogEntry}
*/
public class DatabaseManager {
private static Logger Log = Logger.getLogger(DatabaseManager.class);
private List<LogEntry> logbuffer;
private static volatile DatabaseManager _myself;
// Logging
private static final String COUNT_LOG_ENTRIES = "SELECT count(*) FROM ofGojaraStatistics";
private static final String COUNT_PACKAGES_ODLER = "SELECT count(*) FROM ofGojaraStatistics WHERE messageType like ? AND component = ? AND messageDate > ?";
private static final String GET_ALL_LOGS = "SELECT * FROM ofGojaraStatistics ORDER BY logID desc LIMIT 100";
// private static final String MOST_ACTIVE =
// "SELECT toJID, count(logID) AS counter FROM `ofGojaraStatistics` GROUP by toJID ORDER BY counter DESC";
private static final String ADD_NEW_LOG = "INSERT INTO ofGojaraStatistics(messageDate, messageType, fromJID, toJId, component) VALUES(?,?,?,?,?)";
private static final String CLEAN_OLD_DATA = "DELETE FROM ofGojaraStatistics WHERE messageDate < ?";
private static final String GET_LOGS_DATE_LIMIT_COMPONENT = "SELECT * FROM ofGojaraStatistics WHERE messageDate > ? AND component = ? ORDER BY messageDate DESC LIMIT ?";
private final int _dbCleanMinutes;
// Session
private static final String ADD_SESSION_ENTRY = "INSERT INTO ofGojaraSessions(username, transport, lastActivity) VALUES (?,?,?)";
private static final String UPDATE_SESSION_ENTRY = "UPDATE ofGojaraSessions SET lastActivity = ? WHERE username = ? AND transport = ?";
private static final String GET_SESSION_ENTRIES_FOR_USERNAME = "SELECT * FROM ofGojaraSessions WHERE username = ? ORDER BY lastActivity DESC";
private static final String DELETE_SESSION_ENTRY = "DELETE FROM ofGojaraSessions WHERE username = ? AND transport = ?";
private static final String GET_SESSION_COUNT = "SELECT count(*) FROM ofGojaraSessions";
private static final String GET_SESSION_COUNT_FOR_TRANSPORT = "SELECT count(*) FROM ofGojaraSessions WHERE transport = ?";
private DatabaseManager() {
/*
* Load time from globals if it is set. It represents the minutes the log entries stay in database until they
* will get deleted
*/
// TODO: Use PropertyEventListener to check if cleaner.minutes have
// changed
_dbCleanMinutes = JiveGlobals.getIntProperty("plugin.remoteroster.log.cleaner.minutes", 60);
logbuffer = Collections.synchronizedList(new ArrayList<LogEntry>(20));
startDatabaseCleanLoop();
}
private void startDatabaseCleanLoop() {
/*
* Database Cleaner thread and check for old log entries every 2 minute
*/
TimerTask task = new TimerTask() {
@Override
public void run() {
cleanOldLogEntries();
}
};
Timer timer = new Timer();
timer.schedule(task, 2 * 60 * 1000, 2 * 60 * 1000);
}
/**
* Singleton for Databasemanager, because we only need one.
*
* @return the Databasemanager
*/
public static DatabaseManager getInstance() {
if (_myself == null) {
synchronized (DatabaseManager.class) {
if (_myself == null)
_myself = new DatabaseManager();
}
}
return _myself;
}
/**
* Returns a list of LogEntry's ordered by date desc
*
* @param olderThan
* unix timestamp in ms
* @param limit
* num of rows max
* @param component
* the specified subdomain of the logged component
* @return Collection of {@link LogEntry}
*/
public Collection<LogEntry> getLogsByDateAndLimit(long olderThan, int limit, String component) {
List<LogEntry> result = new ArrayList<LogEntry>();
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_LOGS_DATE_LIMIT_COMPONENT);
pstmt.setLong(1, olderThan);
pstmt.setString(2, component);
pstmt.setInt(3, limit);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String from = rs.getString(4);
String to = rs.getString(5);
String type = rs.getString(3);
long date = rs.getLong(2);
LogEntry res = new LogEntry(from, to, type, date, component);
result.add(res);
}
pstmt.close();
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return result;
}
/*
* Cleans log entries older than 60 minutes if plugin.remoteroster.log.cleaner.minutes is not set
*/
private void cleanOldLogEntries() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(CLEAN_OLD_DATA);
pstmt.setLong(1, System.currentTimeMillis() - _dbCleanMinutes * 60 * 1000);
int rows = pstmt.executeUpdate();
Log.debug("Cleaned statistic database. Affected rows: " + rows);
pstmt.close();
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
}
/**
* Adds new log entry for specified component. Buffers upto 20 Logs, then writes in batch.
*
* @param component
* subdomain of the external component. e.g. icq.myjabberserver.com
* @param type
* string representation of the class. normaly it is like {@link org.xmpp.packet}
* @param from
* full qualified JID of user or component this packet was from
* @param to
* full qualified JID of user or component this packet was adressed to
*/
public void addNewLogEntry(String component, String type, String from, String to) {
if (logbuffer.size() < 20)
logbuffer.add(new LogEntry(from, to, type, System.currentTimeMillis(), component));
else {
synchronized (logbuffer) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
for (LogEntry log : logbuffer) {
pstmt = con.prepareStatement(ADD_NEW_LOG);
pstmt.setLong(1, log.getDate());
pstmt.setString(2, log.getType());
pstmt.setString(3, log.getFrom());
pstmt.setString(4, log.getTo());
pstmt.setString(5, log.getComponent());
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
logbuffer.clear();
}
}
}
}
/**
* This method return the last 100 log entries. Every entry is one string and added to a ArrayList
*
* @return each log as string in a list
* */
public List<String> getAllLogs() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<String> _result = new ArrayList<String>();
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_ALL_LOGS);
rs = pstmt.executeQuery();
while (rs.next()) {
String from = rs.getString(4);
String to = rs.getString(5);
String type = rs.getString(3);
String component = rs.getString(6);
Timestamp date = rs.getTimestamp(2);
String res = "From: " + from + " To: " + to + " Type: " + type + " Timestamp: " + date.toString() + "Component: "
+ component;
_result.add(res);
}
}
catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return _result;
}
/**
* Returns the size of the ofGoJaraStatistics table
*
* @return number rows in database as int or -1 if an error occurred
*/
public int getLogSize() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(COUNT_LOG_ENTRIES);
rs = pstmt.executeQuery();
rs.next();
return rs.getInt(1);
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return -1;
}
/**
* Counts the number of log entries in the databse
*
* @param subdomain
* subdomain of the component the packages were flown by
* @param packetClass
* the class the packet was instance of
* @return number of rows found in database or -1 if there was an error
*/
public int getPacketCount(String subdomain, @SuppressWarnings("rawtypes") Class packetClass) {
return getPacketCountOlderThan(subdomain, packetClass, _dbCleanMinutes);
}
/**
* Counts the number of log entries in the databse that are older than specified value
*
* @param component
* subdomain of the component the packages were flown by
* @param packetClass
* the class the packet was instance of
* @param minutes
* the log entry should not be older than (timestamp should be smaller than currentTime - minutes)
* @return number of rows found in database or -1 if there was an error
*/
public int getPacketCountOlderThan(String component, @SuppressWarnings("rawtypes") Class packetClass, int minutes) {
String classname = packetClass.getName();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(COUNT_PACKAGES_ODLER);
pstmt.setString(1, "%" + classname + "");
pstmt.setString(2, component);
pstmt.setLong(3, System.currentTimeMillis() - minutes * 60 * 1000);
rs = pstmt.executeQuery();
rs.next();
return rs.getInt(1);
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return -1;
}
/**
* Trys to update SessionEntry for given user/transport combination. If update does not work due to no record being
* there, it inserts record.
*/
public void insertOrUpdateSession(String transport, String user, long time) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(UPDATE_SESSION_ENTRY);
pstmt.setLong(1, time);
pstmt.setString(2, user);
pstmt.setString(3, transport);
if (pstmt.executeUpdate() == 0) {
pstmt.close();
pstmt = con.prepareStatement(ADD_SESSION_ENTRY);
pstmt.setString(1, user);
pstmt.setString(2, transport);
pstmt.setLong(3, time);
pstmt.executeUpdate();
Log.debug("I have inserted " + user + " with " + transport + " at " + time);
} else {
Log.debug("I have updated " + user + " with " + transport + " at " + time);
}
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
}
public int removeSessionEntry(String transport, String user) {
int result = 0;
Log.info("Removing registration for: " + user + " from gateway: " + transport);
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(DELETE_SESSION_ENTRY);
pstmt.setString(1, user);
pstmt.setString(2, transport);
result = pstmt.executeUpdate();
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return result;
}
public ArrayList<SessionEntry> getSessionEntriesFor(String username) {
ArrayList<SessionEntry> result = new ArrayList<SessionEntry>();
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION_ENTRIES_FOR_USERNAME);
pstmt.setString(1, username);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String user = rs.getString(1);
String transport = rs.getString(2);
long lastActivity = rs.getLong(3);
SessionEntry res = new SessionEntry(user, transport, lastActivity);
result.add(res);
}
pstmt.close();
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return result;
}
public ArrayList<SessionEntry> getAllSessionEntries(String orderAttr, String order) {
String allowedAttr = "username transport lastActivity";
String allowedOrder = "ASC DESC";
if ((orderAttr == null || order == null) || (!allowedAttr.contains(orderAttr) || !allowedOrder.contains(order))) {
// Use default case if sorting attributes are not correct.
orderAttr = "username";
order = "DESC";
}
ArrayList<SessionEntry> result = new ArrayList<SessionEntry>();
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
String sql = "SELECT * FROM ofGojaraSessions ORDER BY " + orderAttr + " " + order + ";";
pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String user = rs.getString(1);
String transport = rs.getString(2);
long lastActivity = rs.getLong(3);
SessionEntry res = new SessionEntry(user, transport, lastActivity);
result.add(res);
}
pstmt.close();
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return result;
}
public int getNumberOfRegistrations() {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION_COUNT);
ResultSet rs = pstmt.executeQuery();
rs.next();
result = rs.getInt(1);
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return result;
}
public int getNumberOfRegistrationsForTransport(String transport) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION_COUNT_FOR_TRANSPORT);
pstmt.setString(1, transport);
ResultSet rs = pstmt.executeQuery();
rs.next();
result = rs.getInt(1);
} catch (SQLException sqle) {
Log.error(sqle);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return result;
}
}